I have just installed a fresh copy of SQL Server 2005 on a new server. I transferred over one of my stored procs which has been working beautifully on another SQL Server 2005 server.
This time though when it's run, It runs but doesn't insert anything into the destination table. I recreated the tables that were on another server on this server by using the SQL query that you produce when you do a Right-click | Script Table As | Create To | New Query Editor Window and simply copied and pasted the table creation sql script on my new server, changed the database name and it created it fine.
What else could it be I went to the properties of the table on my new system and added permissions allowing users NT AUTHORITY\SYSTEM, public, and SQLAgentUser grant permissions to insert, update, and select.
I don't think this is it though since on my other server, I didn't have to do any of this and the stored proc ran just fine. Everything is setup the same, I don't see what the deal is here.
I'm also lost on all these permissions, there are so many levels now in SQL 2005 and also not sure of all system users and what their roles are (nt authority, etc.) well enough yet.
Stored Procedure runs but no records inserted
Ramazan Binarbasi
It ended up being a stupid forgettting to add values into one of my new lookup tables that my sp relies on.
thanks!
AzureBell
You claim that the procedure was "working beautifully on another SQL Server 2005 server", right
And I presume the procedure runs without any kind of error, right
If this is the case, I can think of two possible causes:
1. You have some conditional logic that is causing a different code path traversal at run time. In other words, the INSERT is just getting skipped. You could test this by stepping with a debugger or adding PRINT statements.
2. Your INSERT-ing into some other table than the one you think you're INSERT-ing into. You could test this adding an explicit 2-part name to the table you want to insert into.
galaxy_nus
hi DBA123,
There are new security implementations in sql server 2005
it could a Schema problem
DBO.table1 is different from user.table1
kindly check if the user is using the deafult schema
or if the SP is tageting table within the schema
regards,
joeydj