|
I have a problem of inserting a record which includes data type conversion. There are two databases with the same data structure. One database is ok to insert that record, but another is not ok for data type conversion. I've checked all the data fields.Please give me any suggestion to solve it.
|
|
|
|
|
What type is it you're trying to insert, and what type is the field?
What DB are you using?
|
|
|
|
|
I'm using MSSQL2005. Inserting includes code such as 'X001' which is varchar and it needs to be incremented automatically. So that I converted varchar to numeric. It's ok for one database, but it's not ok for another one. I've checked all the data fields. It's all the same. Any solution for this?
Thanks a lot.
|
|
|
|
|
Are both databases having the same globalization settings?
|
|
|
|
|
Do u mean same database property? Actually, the database is restored but some changes had been made. But any changes had not been made to that data table which I'm trying to insert a record.
Thanks for your help..
|
|
|
|
|
vanillaET wrote: There are two databases with the same data structure. One database is ok to insert that record, but another is not ok for data type conversion. I've checked all the data fields.Please give me any suggestion to solve it.
Check the data-fields again. You're inserting something that cannot be converted, might be a text in a bit or a datetime field. Try logging all values that are going to be inserted, and have the app mail the offending values using a try-catch block.
Next, simply try the offending values using your original insert-statement trough the Sql Management Studio.
Bastard Programmer from Hell 
|
|
|
|
|
Hi friends. i have a table that has relation to it self. how can i write a select query that shows it's data sequentially from the most top parent to the last child? This table has n levels.
|
|
|
|
|
|
I am new to SQL Server, but not to Programming or Databases, and am stuck, though I have an inkling what is wrong and I don't know how to correct it !!! I'll describe things in detail below ...
I am running this software on a stand-alone PC with myself as the only user.
When I try to add a diagram to my Database I get a message telling me that "This database does not have a valid dbo user, or you do not have permissions to impersonate the dbo user, so database diagramming is not available. Do you want to make yourself the dbo of this database in order to use database diagramming ?"
If I then reply "Yes" it says "This server does not have one or more of the database objects required to use database diagramming. Do you wish to create them ?"
Again, I reply "Yes" and this is where I get the error, "Could not obtain information about Windows NT group/user 'Gary-PC\Gary', error code 0x534."
If I then try to do similar in MS SQL Server Management Studio I get a different message - "Database diagram support objects cannot be installed because this database does not have a valid owner. To continue, first use the Files page of the Database Properties dialog box or the ALTER AUTHORIZATION statement to set the database owner to a valid login, then add the database diagram support objects."
Now, I think this is something to do with my PC being renamed, because it is called, and has been for ages, MeDesktop, but at one point I seem to recall it was called Gary-PC, so it could be that something just needs a pointer changed, but I don't know where or how to do that !!!
In MS SQL Server Management Studio, Object Explorer, the Connection Name is "MEDESKTOP (MEDESKTOP\Gary)", Display Name is "MEDESKTOP", Login Name is "MEDESKTOP\Gary" & Server Name is MEDESKTOP.
I have 4 System Databases, "master", "model", "msdb" & "tempdb", all of which have a Status of "Normal" and an Owner of "sa" ... I seem to remember something about "sa" from when I first installed this stuff, but it was a long time ago !!!
I then have my own new (and very small) database, called "myKADB" and this is where I have the problem. I can add and amend Tables & Columns, and I can access the database via my C# program, so most things appear to be working just fine, bar these error messages above. Now, if I look at the Properties of myKADB, I see that again the Status is "Normal", but the Owner is "Gary-PC\Gary", so I think this needs to be changed ... though I could of course be wrong !!!
In the Files option of Properties, there looks to be the option of amending the Owner, so I click on the little button with the 3 dots and I get a Window that says "Select Database Owner", The top button says "Object Types" and there is just the single option of "Logins", and then there is a Window that says "Enter the Object names to select", but this Window is empty. If I then click the Browse button, it says "7 objects were found matching the types you selected" and the 7 are, "##MS_PolicyEventProcessingLogin##", "##MS_PolicyTsqlExecutionLogin##", "Gary-PC\Gary", "NT AUTHORITY\SYSTEM", "NT SERVICE\MSSQLSERVER", "NT SERVICE\SQLSERVERAGENT" & "sa".
Hopefully this is everything I need to tell you, what do I need to do ?!?!?
|
|
|
|
|
|
Thanks, but I've uninstalled and re-installed SQL Server now and it's all working fine.
|
|
|
|
|
How we can find out Relationship of two system tables using database diagram in system databases
|
|
|
|
|
AFAIK, it's not possible to make database diagrams in the system databases in Sql Express. I don't have a full version here to verify whether it's supported there, but I doubt it.
FWIW, you can get a full map for the systables of Sql2k5 here[^].
Bastard Programmer from Hell
|
|
|
|
|
You could look at the constaints on the foreign key. Does it allow duplicates, does it allow nulls?
|
|
|
|
|
I have one procedure that's timing out when it's called through the user interface, yet when I run it on the database itself it takes 7 seconds at most and the time out is set to 60 seconds. Every other procedure I can run works just fine through the UI, including those that take considerably longer.
When run on the server itself it peaks for a moment at about 20% CPU use, and is over within 7 seconds maximum. Execution plans aren't showing me anything interesting, I'm not seeing anything special on any of the diagnostic tools I have available.
Running it through a function call it makes a nice Plateau at a bit over 20% CPU usage, and sits there until it times out. Nothing I can check other than that is significantly different when it's called programmatically, at least nothing any different than any other procedure.
Any thoughts on what this could be? Or do I finally have a case to get them to upgrade the server so I can run SQL Profiler on it?
|
|
|
|
|
Erland Sommarskog has an article[^] about how to troubleshoot this kind of problems.
|
|
|
|
|
Just a thought. Do you use the same credentials to run the SP from the UI and on the Database directly? If not, you may want to investigate this option.
|
|
|
|
|
Check that it is not a deadlock condition. I believe SQL server will terminate (pick a victim) and terminate a session so that others can continue.
|
|
|
|
|
Do you have return statement somewhere in the code?
RETURN by itself I think is okay but RETURN someValue may not
|
|
|
|
|
Did u check the amount of data the procedure is returning. In case the procedure is returning too many rows of data, then that might be the cause of timeout when you run the procedure programmatically. But when you run the same procedure from database itself which i guess is Sql Management studio, only top 500 or 1000 rows would be returned depending on the SSMS setting. Hence the result comes up without time out.
|
|
|
|
|
I was working on a chunk of SQL in management studio. Finally got it to return the results I wanted. Then I converted it to a stored procedure, where it promptly stopped working. Upon tracking the bug down I found that SQL was handling Null variables differently in dynamic SQL vs my stored procedure.
I had this bit of code:
if (@Replace != '')
set @MaterialNumber = @Replace
In dynamic SQL if @Replace was null, we did not enter the if block. In a stored procedure when @replace was null we entered the if block and set @MaterialNumber to null. 
To the best of my knowledge, this should never happen. Is this a bug in SQL Server or is there a setting hidden somewhere that could account for this difference?
|
|
|
|
|
Whenever I check for NULL, I avoid doing so with comparison operators. There should be some IS NULL check available to use instead. 
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]
|
|
|
|
|
That seems correct to me, but I'm unsure what you mean by "dynamic SQL".
|
|
|
|
|
By dynamic I meant SQL run directly in Enterprise Manager. When run in a stored procedure the SQL code behaved one way, when run directly in Enterprise Manager it behaved another way. This is the first instance I've discovered where there was a difference between the two.
|
|
|
|
|
In SQL-server '' is not the same as null, it's a zero length string.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
