alito_procurement
asked on
Help needed to execute dynamic sql with sp_executesql
Spent far too long on this....
I need to create a view in data base b in the context of database a
so I tried...
use database_a
go
DECLARE @client nvarchar(100)
DECLARE @QrySql nvarchar(1000)
SELECT @client=N'database_b'
SET @QrySql =N'CREATE view ' + @client + '.[dbo].[vw_bcp_xml] AS SELECT ''bcp '' + TABLE_CATALOG + ''.dbo.'' + TABLE_NAME + '' format nul -x -f c:\reporting\bcp\'' + TABLE_CATALOG + ''\'' + TABLE_NAME + ''.xml -n -U reporting -P letmein -S db1\reporting'' AS bcp_xml FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME LIKE ''tbl_rpt%'''
PRINT @QrySql
EXEC (@QrySql)
which as you'd expect gives the error:
'CREATE/ALTER VIEW' does not allow specifying the database name as a prefix to the object name.
So i tried this....
DECLARE @client nvarchar(100)
DECLARE @QrySql nvarchar(1000)
DECLARE @QrySql2 nvarchar(1000)
SELECT @client=N'database_b'
SET @QrySql =N'CREATE view ' + @client + '.[dbo].[vw_bcp_xml] AS SELECT ''bcp '' + TABLE_CATALOG + ''.dbo.'' + TABLE_NAME + '' format nul -x -f c:\reporting\bcp\'' + TABLE_CATALOG + ''\'' + TABLE_NAME + ''.xml -n -U reporting -P letmein -S db1\reporting'' AS bcp_xml FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME LIKE ''tbl_rpt%'''
SET @QrySql2 = @Client + '.dbo.sp_executesql(' + @QrySql + ')
EXEC (@QrySql2)
I just can't get it right as I'm getting sytax errors when executing the string.
I guess I can't quite get my head around where to put the quotes etc...When (or if!!) I get it working I want to loop round a list of clients and create this view in all client databases.
I'd appreciate help in either:
1. Fixing the code so it works
or
2. An effective alternative method.
Thanks in advance
I need to create a view in data base b in the context of database a
so I tried...
use database_a
go
DECLARE @client nvarchar(100)
DECLARE @QrySql nvarchar(1000)
SELECT @client=N'database_b'
SET @QrySql =N'CREATE view ' + @client + '.[dbo].[vw_bcp_xml] AS SELECT ''bcp '' + TABLE_CATALOG + ''.dbo.'' + TABLE_NAME + '' format nul -x -f c:\reporting\bcp\'' + TABLE_CATALOG + ''\'' + TABLE_NAME + ''.xml -n -U reporting -P letmein -S db1\reporting'' AS bcp_xml FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME LIKE ''tbl_rpt%'''
PRINT @QrySql
EXEC (@QrySql)
which as you'd expect gives the error:
'CREATE/ALTER VIEW' does not allow specifying the database name as a prefix to the object name.
So i tried this....
DECLARE @client nvarchar(100)
DECLARE @QrySql nvarchar(1000)
DECLARE @QrySql2 nvarchar(1000)
SELECT @client=N'database_b'
SET @QrySql =N'CREATE view ' + @client + '.[dbo].[vw_bcp_xml] AS SELECT ''bcp '' + TABLE_CATALOG + ''.dbo.'' + TABLE_NAME + '' format nul -x -f c:\reporting\bcp\'' + TABLE_CATALOG + ''\'' + TABLE_NAME + ''.xml -n -U reporting -P letmein -S db1\reporting'' AS bcp_xml FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME LIKE ''tbl_rpt%'''
SET @QrySql2 = @Client + '.dbo.sp_executesql(' + @QrySql + ')
EXEC (@QrySql2)
I just can't get it right as I'm getting sytax errors when executing the string.
I guess I can't quite get my head around where to put the quotes etc...When (or if!!) I get it working I want to loop round a list of clients and create this view in all client databases.
I'd appreciate help in either:
1. Fixing the code so it works
or
2. An effective alternative method.
Thanks in advance
ASKER
Thought that was going to work but......
'CREATE VIEW' must be the first statement in a query batch.
Thanks anyway
'CREATE VIEW' must be the first statement in a query batch.
Thanks anyway
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
That worked...without any amendments
nice one
nice one
Try this, incorporating a USE statement into the dynamic SQL.:
use database_a
go
DECLARE @client nvarchar(100)
DECLARE @QrySql nvarchar(1000)
SELECT @client=N'database_b'
SET @QrySql =N'USE ' + @client + '; CREATE view [dbo].[vw_bcp_xml] AS SELECT ''bcp '' + TABLE_CATALOG + ''.dbo.'' + TABLE_NAME + '' format nul -x -f c:\reporting\bcp\'' + TABLE_CATALOG + ''\'' + TABLE_NAME + ''.xml -n -U reporting -P letmein -S db1\reporting'' AS bcp_xml FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME LIKE ''tbl_rpt%'''
PRINT @QrySql
EXEC (@QrySql)