| Author |
 Topic  |
|
Podge
Support Moderator
Ireland
3775 Posts |
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
|
|
Podge
Support Moderator
Ireland
3775 Posts |
 Posted - 06 September 2005 : 05:28:30
|
They appear to work as I get "command completed successfully" but in reality it doesn't do anything. If I try to insert a row (containing the row ID) I get a error stating that identity insert is off.
The second two examples don't work in a stored procedure either which is where I really want to use it. I'm passing the table name as a parameter and using it to build a sql statement (which is why I cannot use the first example) e.g.
this works in the stored procedure & query analyser
exec ('select * from ' + @tablename + ')
but this doesn't
exec 'SET IDENTITY_INSERT [' + @tablename + '] ON' |
Podge.
The Hunger Site - Click to donate free food | My Blog | Snitz 3.4.05 AutoInstall (Beta!)
My Mods: CAPTCHA Mod | GateKeeper Mod
Tutorial: Enable subscriptions on your board
Warning: The post above or below may contain nuts. |
 |
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
Posted - 06 September 2005 : 05:32:25
|
I tried each of the situations in Query Analizer and had no problems doing it. I did inserts specifying the ID value and it all worked.
Haven't tested in a stored proc, but I can give it a go. What Service Pack have you installed? |
Snitz 3.4 Readme | Like the support? Support Snitz too |
|
|
|
Podge
Support Moderator
Ireland
3775 Posts |
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
|
|
Podge
Support Moderator
Ireland
3775 Posts |
Posted - 06 September 2005 : 06:09:05
|
Assume that tablename and database name are exactly the same
CREATE PROCEDURE [DBO].[move_table]
@tablename varchar(100)
as
exec ('SET IDENTITY_INSERT [snitzforum].[dbo].[' + @tablename +'_AVATAR] ON')
exec ('INSERT INTO [snitzforum].[dbo].[' + @tablename + '_AVATAR]([A_ID], [A_URL], [A_NAME], [A_MEMBER_ID]) SELECT [A_ID], [A_URL], [A_NAME], [A_MEMBER_ID] FROM [' + @tablename + '].[dbo].[' + @tablename + '_AVATAR]')
exec ('SET IDENTITY_INSERT [snitzforum].[dbo].[' + @tablename +'_AVATAR] OFF') |
Podge.
The Hunger Site - Click to donate free food | My Blog | Snitz 3.4.05 AutoInstall (Beta!)
My Mods: CAPTCHA Mod | GateKeeper Mod
Tutorial: Enable subscriptions on your board
Warning: The post above or below may contain nuts. |
|
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
|
|
Podge
Support Moderator
Ireland
3775 Posts |
Posted - 06 September 2005 : 07:13:09
|
That usually happens if the table doesn't exist. I got it once or twice but I had made a mistake with the table name. After I resolved it I got the identity insert error.
I know you probably know this already but;
You have to create a database called snitzforum
Add the above stored procedure to it.
Run the avatar dbs file having demo_ (for example) as the prefix for the table name.
Create a database called demo
Run the avatar dbs file having demo_ as the prefix for the table name
Put some sample data in this table
Run the stored procedure
exec [snitzforum].[dbo].[move_table] 'demo'
|
Podge.
The Hunger Site - Click to donate free food | My Blog | Snitz 3.4.05 AutoInstall (Beta!)
My Mods: CAPTCHA Mod | GateKeeper Mod
Tutorial: Enable subscriptions on your board
Warning: The post above or below may contain nuts. |
|
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
Posted - 06 September 2005 : 07:17:05
|
It's the exec thing. If you try to set the IDENTITY_INSERT outside the exec it works. Now we need to find out if this can be overcome.
Stupid thing, the code works in Query Analizer...
No, it does not work either. |
Snitz 3.4 Readme | Like the support? Support Snitz too |
Edited by - ruirib on 06 September 2005 07:18:45 |
|
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
Posted - 06 September 2005 : 07:26:46
|
I think I've found it. The problem is that the EXEC runs in its own batch. You need to execute all the statements in a single batch:
CREATE PROCEDURE [DBO].[move_table]
@tablename varchar(100)
as
exec ('SET IDENTITY_INSERT [snitzforum].[dbo].[' + @tablename +'_AVATAR] ON; INSERT INTO [snitzforum].[dbo].[' + @tablename + '_AVATAR]([A_ID], [A_URL], [A_NAME], [A_MEMBER_ID]) SELECT [A_ID], [A_URL], [A_NAME], [A_MEMBER_ID] FROM [' + @tablename + '].[dbo].[' + @tablename + '_AVATAR];SET IDENTITY_INSERT [snitzforum].[dbo].[' + @tablename +'_AVATAR] OFF')
This should work. It works with query analizer and as a stored proc. |
Snitz 3.4 Readme | Like the support? Support Snitz too |
Edited by - ruirib on 06 September 2005 07:28:24 |
|
|
|
Podge
Support Moderator
Ireland
3775 Posts |
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
Posted - 06 September 2005 : 07:48:31
|
Ok, let me know how it goes.
Worked for me, though I had no records in the database I used for testing, but the error message went away, so I expect it to work for you as well. |
Snitz 3.4 Readme | Like the support? Support Snitz too |
|
|
|
Podge
Support Moderator
Ireland
3775 Posts |
|
|
Topic |
|
Topic Locked
