|
Hi..
check your parameter is null or not.
in stored procedure
if(parameter is null or parameter ='')
begin
sql statements
end
else
begin
sql statements
end
or
in single query.
select * from tbl where parameter is null or parameter =''
|
|
|
|
|
Trying to install the following patch to fix a SqlBulkCopy/ADO.NET timeout issue when loading data
http://support.microsoft.com/kb/913177/[^]
However, when I run the installer "ndp20-kb916002-x86" (SQL 2008 on 32 bit Windows 2008 server), I got the following message:
"The upgrade patch cannot be installed by the Windows Installer service because the program to be upgraded may be missing , or the upgrade patch may update a different version of the program"
Has anyone run into this problem before?
Thanks
dev
|
|
|
|
|
Haven't seen that problem but could be related to the fact that the fix is older than your server environment. As a workaround, have you tried enabling MARS?
|
|
|
|
|
Dear all,
Please kindly advise this, my storeprocedure structure is as below.
The purpose is, While Insert in TableA if there is any error occour Then I want to ROLLBACK TRAN of TableB that related with the current Transaction of TableA, if not COMMIT TRAN.
When execute it I have got the error
Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 0, current count = 1159.
DECLARE Cur_1 Cursor LOCAL FOR
Select col1,col2,col3 from table1
OPEN Cur_1
FETCH NEXT FROM Cur_1
INTO @col1,@col2,@col3
WHILE @@FETCH_STATUS = 0
BEGIN
DELETE FROM FROM TableA Where match with @col1,@col2,@col3
DELETE FROM FROM TableB Where match with @col1,@col2,@col3
DECLARE Cur_2 LOCAL FOR
Select * from table2 Where match with @col1,@col2,@col3
OPEN Cur_2
FETCH NEXT FROM Cur_2
WHILE @@FETCH_STATUS = 0
BEGIN
BEGIN TRAN
INSERT INTO TableB (data from Cur_2 & Cur_1)
FETCH NEXT FROM Cur_2
END
CLOSE Cur_2
DEALLOCATE Cur_2
INSERT INTO TableA (data fromCur_1)
IF @@ERROR = 0
COMMIT TRAN
ELSE
ROLLBACK TRAN
FETCH NEXT FROM Cur_1
INTO @col1,@col2,@col3
END
CLOSE Cur_1
DEALLOCATE Cur_1
modified on Friday, September 2, 2011 12:35 AM
|
|
|
|
|
Your problem is that your BEGIN TRAN statement is inside a WHILE loop and hence it will be executed every time the loop code is executed. You need to move it outside the loop.
|
|
|
|
|
To add to Geoff's answer, it is always a best programming practise to enclose transactions inside a Try Catch block, like this:
BEGIN TRY
BEGIN TRANSACTION
COMMIT TRANSACTION
END TRY
BEGIN CATCH
IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
DECLARE @ErrMsg VARCHAR(8000), @ErrSeverity INT
SELECT @ErrMsg = ERROR_MESSAGE(), @ErrSeverity = ERROR_SEVERITY()
RAISERROR(@ErrMsg,@ErrSeverity,1)
END CATCH
|
|
|
|
|
try something like this.
start your transaction
begin transaction a
insert into tableA
if(@@rowcount > 0)
begin
insert into tableB
commit transaction a
end
else (@@error > 0)
begin
rollback transaction a
end
|
|
|
|
|
Hi Guys,
I would like to know if this is true for MS SQL Server. Lets say you have 1000 users in your MS SQL database. Does a person has to pay for each of those 1000 of users who uses your application which connects to the database to retrieve the users information.
Thanks
|
|
|
|
|
With different licensing this would be a case for CPU license.
|
|
|
|
|
Possibly - the art of deciphering the licencing framework for SQL Server is not one you can easily acquire.
We got to the point where we outlined our business model to the MS rep and asked for the best licencing model. I hope he gave us the right one. I'm sure there is a business opportunity there for someone to offer licence model consulting.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Mycroft Holmes wrote: Possibly - the art of deciphering the licencing framework for SQL Server is not one you can easily acquire.
So true. It took me quite some time to figure out what all the information meant. The MS rep in this case can help.
My response is based on the dollars per seat would defiantly be more than the CPU license. I do not remember exactly but the cutoff was around 150 seats (I think).
|
|
|
|
|
There is a break even point where the Cost Per Seat becomes more expensive than the Per CPU. I think the breakeven point is actually quite low at something like 100 Per-User licenses? though I could be wrong.. After this point its more cost effective to just license the box.
JC
|
|
|
|
|
Mycroft Holmes wrote: I'm sure there is a business opportunity there for someone to offer licence
model consulting.
There is already one[^] for Oracle, so there might as well be one for SQL Server.
Chris Meech
I am Canadian. [heard in a local bar]
In theory there is no difference between theory and practice. In practice there is. [Yogi Berra]
posting about Crystal Reports here is like discussing gay marriage on a catholic church’s website.[Nishant Sivakumar]
|
|
|
|
|
And there are also lots of training courses about Oracle licensing. Definitely isn't an easy-to-understand thing.
|
|
|
|
|
dont worry, they wont forget to add 10% on the top for being part of your business.
Common sense is admitting there is cause and effect and that you can exert some control over what you understand.
|
|
|
|
|
I am working in a VB6 Access project in which the DB is password protected. My DB is getting corrupted at the application run time.
Anybody have any solution
|
|
|
|
|
I would upgrade database to MS SQL Server 2005 and project to VS2010 at least
I Love T-SQL
"VB.NET is developed with C#.NET"
If my post helps you kindly save my time by voting my post.
www.cacttus.com
|
|
|
|
|
I suppose you are running Access 95 or something, there was a utility that would allow you to clean and compress the database. If you have not been using that then the database will have been deteriorating of the last decade or so and is probably dead by now.
You have gone well past the use by date for this application.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
sudheesh kumar s wrote: My DB is getting corrupted at the application run time.
What does that mean?
Do you have a database which is now corrupted?
Or you have an uncorrupted database and EVERY time you start the application then it becomes corrupted?
And what application (version) on what OS (version) are you using to determine that it is corrupted?
|
|
|
|
|
Are you accessing the database from multiple machines? Access isn't meant as a database-server, it's a "local" database format.
Do you still have a non-corrupt version around? Backups?
If so, open it in Access, and convert it to Sql-server format. MS Access has a conversion-wizard that can migrate everything in there to Sql Server. Next, you download Sql Server Express 2008, which is a free download. Next, you download Visual Basic Express 2010, which is also a free download.
If the app survived this long, there's bound to be some value in it. That would mean that it's probably worth updating the logic to something more modern. All the tools are available for free.
Happy programming
Bastard Programmer from Hell
|
|
|
|
|
I have been using MS Access as a backend with VB6 for over a decade. Our software works with Access or SQL Server depending on customer size and usage. 95% of the code base is the same. The only time I have seen Access (2000 or better) become corrupted is when more than about 6 or 7 users might hit it at the same time...combine that with a crappy network and it spells trouble. The bottom line is that Access does not perform will with multiple connections over a network. (permissions can also be a bi*ch since Access has to create a lock file)
If the database gets corrupted everytime you start the program, then obviously you will have to solve that issue first.
"Go forth into the source" - Neal Morse
|
|
|
|
|
Hello,
I have installed the MySQL database program on my computer. It came with 2 databases installed and I created another called "Test" for experimentation. I can access all the information from WITHIN the proggram using the internal commands, however, I am looking to be able to access table information from an outside program, so I need to be able to get table information into that program (C#) to create queries. I can successfully list the names of the databases in the program, however, I have an issue getting the table names. I have used both of the following which are supposed to list the tables:
And I have tried several variations of the above. All seem to either return only the names of the 3 installed databases or nothing at all. Can anyone supply me with an SQL statement that will simply return the names of the tables inside a specifically defined database (thisDb) and, as long as I am on the topic, if your know this answer, how about another script to do the same thing for the table columns of each table. Thank you for your input and assistance....Pat
|
|
|
|
|
This works for me:
USE thisDb;
SHOW TABLES;
|
|
|
|
|
Thank You Shameel....once again you have bailed me out. I was actually going to email you with this question as I remember that we used to be able to do that here, but I could no longer find a link for it. In any event, and to help any others that are suffering along and who find this question in a search, I am enclosing the final code that I used for my application. And once again Shameel, I have marked your answer as correct. For other interested parties, the C# code is mine (simple and clean for better or worse), so don't blame Shameel for it...LOL. Best Regards, Pat
string thisDb = listViewDatabases.SelectedItems[0].Text;
string Sql = "Use " + thisDb + ";" + " Show Tables; ";
try
{
MySqlCommand Comm = new MySqlCommand(Sql, Conn);
MySqlDataAdapter da = new MySqlDataAdapter(Comm);
DataSet ds = new DataSet();
da.Fill(ds);
int count = ds.Tables[0].Rows.Count;
string[] tables = new string[count];
for (int i = 0; i < count; i++)
{
tables[i] = ds.Tables[0].Rows[i][0].ToString();
listViewDbTables.Items.Add(tables[i].ToString());
}
}
catch (Exception ex)
{
MessageBox.Show("Error" + ex.Message, "Error");
}
|
|
|
|
|
Anytime, glad to be of help
|
|
|
|