May 8, 2014 at 6:40 am
Below dynamic sql giving me an error Incorrect syntax near '.'.
Please help me for this issue.
I am passing database name in this variable @DATABASENAME
BEGIN
DECLARE @DATABASENAME NVARCHAR(100);
DECLARE @sql NVARCHAR(2000);
SET @DATABASENAME =?
SELECT @DATABASENAME AS DATABASENAME
SELECT @Sql = 'CREATE SYNONYM ' + b.NAME + '.' + a.NAME + ' FOR ' + A.base_object_name + CHAR(10) + ';' + CHAR(10)
FROM @DATABASENAME.sys.synonyms a
JOIN @DATABASENAME.sys.schemas b ON a.schema_id = b.schema_id
WHERE type_desc = N'SYNONYM'
AND type IN (N'SN')
EXEC sp_executesql @sql;
END
GO
May 8, 2014 at 7:05 am
It's unclear to me what you want to achieve.
If you're trying to generate the CREATE SYNONYM statements, you can do it this way:
DECLARE @DATABASENAME sysname;
DECLARE @sql NVARCHAR(max);
SET @DATABASENAME = 'master'
SET @sql = N'
SELECT ''CREATE SYNONYM '' + b.NAME + ''.'' + a.NAME + '' FOR '' + A.base_object_name + CHAR(10) + '';'' + CHAR(10)
FROM sys.synonyms a
JOIN sys.schemas b
ON a.schema_id = b.schema_id
WHERE type_desc = N''SYNONYM''
AND type IN (N''SN'')'
DECLARE @s-2 nvarchar(max);
SET @s-2 = 'EXEC '+ QUOTENAME(@databasename) +'.sys.sp_executesql @sql; '
EXEC sp_executesql @s-2, N'@sql nvarchar(max)', @sql
Unfortunately, the @variables cannot be used in object names.
The only option is the use of dynamic sql.
-- Gianluca Sartori
May 8, 2014 at 8:30 am
Thanks Sir,
its worked for me but its created synonym but I have to execute that created synonym scripts as well
Ho to do this Sir?
May 8, 2014 at 8:38 am
kiran.rajenimbalkar (5/8/2014)
I have to execute that created synonym scripts as well
It won't work: the synonyms are already there.
Where do you want to create them? A different database? A different server?
-- Gianluca Sartori
May 9, 2014 at 12:23 am
I had droped that synonyms and creted synonym scripts using above sql scripts but I have to execute that create synonym script dynamically.
How to do this Sir?
May 9, 2014 at 12:59 am
you mean u need to execute the dynamic script u do not need the select statement, its is right?
May 9, 2014 at 1:15 am
You will have a set of statements to execute, then you need a cursor to iterate them and execute one at a time.
DECLARE @DATABASENAME sysname;
DECLARE @sql NVARCHAR(max);
SET @DATABASENAME = 'master'
SET @sql = N'
SELECT ''CREATE SYNONYM '' + b.NAME + ''.'' + a.NAME + '' FOR '' + A.base_object_name + CHAR(10) + '';'' + CHAR(10)
FROM sys.synonyms a
JOIN sys.schemas b
ON a.schema_id = b.schema_id
WHERE type_desc = N''SYNONYM''
AND type IN (N''SN'')'
DECLARE @s-2 nvarchar(max);
SET @s-2 = 'EXEC '+ QUOTENAME(@databasename) +'.sys.sp_executesql @sql; '
DECLARE @statements TABLE (
statement nvarchar(max)
)
INSERT @statements
EXEC sp_executesql @s-2, N'@sql nvarchar(max)', @sql
DECLARE c CURSOR STATIC LOCAL FORWARD_ONLY READ_ONLY
FOR
SELECT statement
FROM @statements
OPEN c
FETCH NEXT FROM c INTO @sql
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC sp_executesql @s-2, N'@sql nvarchar(max)', @sql
FETCH NEXT FROM c INTO @sql
END
CLOSE c
DEALLOCATE c
However this code will fail, because the synonyms are already there.
I have no idea what you're trying to do, but this should put you in the right direction.
-- Gianluca Sartori
May 9, 2014 at 1:48 am
actually Sir i am merging one database objects to another database,
so i have to generate create synonym script dynamicaley and then execute that create synonym script.
First SQL that u provided me yesterdy it was creating the synonym script then I have to execute that synonym script in the perticular database.
Just provide me how to execute that synonym script after creating in the same SQL which you have provided me yesterday
May 9, 2014 at 1:53 am
kiran.rajenimbalkar (5/9/2014)
First SQL that u provided me yesterdy it was creating the synonym script then I have to execute that synonym script in the perticular database.Just provide me how to execute that synonym script after creating in the same SQL which you have provided me yesterday
You have everything you need in my last post.
-- Gianluca Sartori
May 9, 2014 at 2:48 am
I will give an example Sir,
I hav three databases A,B,C
I have table called ABC which I have created in database A
after that I have created synonym for table ABC in database C,
But now I have to create all the synonym whatever exists in C need to create in database B.
means that the synonym for ABC which is exist in database C I have to execute that create synonym script in database B
May 9, 2014 at 2:57 am
-- READ SYNONYMS FROM DATABASE 'C'
DECLARE @DATABASENAME sysname;
DECLARE @sql NVARCHAR(max);
SET @DATABASENAME = 'C'
SET @sql = N'
SELECT ''CREATE SYNONYM '' + b.NAME + ''.'' + a.NAME + '' FOR '' + A.base_object_name + CHAR(10) + '';'' + CHAR(10)
FROM sys.synonyms a
JOIN sys.schemas b
ON a.schema_id = b.schema_id
WHERE type_desc = N''SYNONYM''
AND type IN (N''SN'')'
DECLARE @s-2 nvarchar(max);
SET @s-2 = 'EXEC '+ QUOTENAME(@databasename) +'.sys.sp_executesql @sql; '
DECLARE @statements TABLE (
statement nvarchar(max)
)
INSERT @statements
EXEC sp_executesql @s-2, N'@sql nvarchar(max)', @sql
-- SET THE TARGET DATABASE NAME 'B' AND EXECUTE THE SCRIPTS
SET @DATABASENAME = 'B'
SET @s-2 = 'EXEC '+ QUOTENAME(@databasename) +'.sys.sp_executesql @sql; '
DECLARE c CURSOR STATIC LOCAL FORWARD_ONLY READ_ONLY
FOR
SELECT statement
FROM @statements
OPEN c
FETCH NEXT FROM c INTO @sql
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC sp_executesql @s-2, N'@sql nvarchar(max)', @sql
FETCH NEXT FROM c INTO @sql
END
CLOSE c
DEALLOCATE c
-- Gianluca Sartori
May 9, 2014 at 3:19 am
I added the IF exist condition in the script
DECLARE @DATABASENAME sysname;
DECLARE @sql NVARCHAR(max);
SET @DATABASENAME = 'SFM_QA03'
SET @sql = N'SELECT '+'''IF NOT EXISTS (SELECT TOP 1 NULL FROM sys.synonyms a
JOIN sys.schemas b
ON a.schema_id = b.schema_id
WHERE type_desc = N''''SYNONYM''''
AND type IN (N''''SN'''') AND b.Name='''''' + b.NAME + '''''' AND a.Name='''''' + a.NAME + '''''')'+ 'BEGIN '+CHAR(10)+
+'CREATE SYNONYM '' + b.NAME + ''.'' + a.NAME + '' FOR '' + A.base_object_name + CHAR(10) + '''' + CHAR(10)+'''+
'END;''
FROM sys.synonyms a
JOIN sys.schemas b
ON a.schema_id = b.schema_id
WHERE type_desc = N''SYNONYM''
AND type IN (N''SN'') '
DECLARE @s-2 nvarchar(max);
SET @s-2 = 'EXEC '+ QUOTENAME(@databasename) +'.sys.sp_executesql @sql; '
DECLARE @statements TABLE (
statement nvarchar(max)
)
INSERT @statements
EXEC sp_executesql @s-2, N'@sql nvarchar(max)', @sql
select * from @statements
DECLARE c CURSOR STATIC LOCAL FORWARD_ONLY READ_ONLY
FOR
SELECT statement
FROM @statements
OPEN c
FETCH NEXT FROM c INTO @sql
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC sp_executesql @s-2, N'@sql nvarchar(max)', @sql
FETCH NEXT FROM c INTO @sql
END
CLOSE c
DEALLOCATE c
Regards,
Mitesh OSwal
+918698619998
May 9, 2014 at 3:19 am
Many Many thanks Sir....
its worked heartly thanks 🙂
May 9, 2014 at 7:19 am
I believe this is what you need - dynamic SQL syntax is tricky. Each line needs a statement of
SET @SQL = @SQL + ':
DECLARE @DATABASENAME NVARCHAR(100);
DECLARE @SQL NVARCHAR(MAX);
SET @DATABASENAME = 'master'
SET @SQL = 'SELECT * FROM ' + @DATABASENAME + '.sys.synonyms a '
SET @SQL = @SQL + ' JOIN ' + @DATABASENAME + '.sys.schemas b ON a.schema_id = b.schema_id '
SET @SQL = @SQL + ' WHERE type_desc = ''SYNONYM'' '
SET @SQL = @SQL + ' AND type IN (''SN'')'
PRINT(@SQL)
--EXEC(@SQL)
May 9, 2014 at 7:29 am
RHut 21288 (5/9/2014)
I believe this is what you need - dynamic SQL syntax is tricky. Each line needs a statement ofSET @SQL = @SQL + ':
No you don't have to make each line a separate statement. I don't know why this particular myth continues to live. It is not true and painfully simple to disprove. Take a look at this. First is your code followed by concatenating strings in a single line. The second version is a LOT easier to read and the output is 100% identical.
DECLARE @DATABASENAME NVARCHAR(100);
DECLARE @SQL NVARCHAR(MAX);
SET @DATABASENAME = 'master'
SET @SQL = 'SELECT * FROM ' + @DATABASENAME + '.sys.synonyms a '
SET @SQL = @SQL + ' JOIN ' + @DATABASENAME + '.sys.schemas b ON a.schema_id = b.schema_id '
SET @SQL = @SQL + ' WHERE type_desc = ''SYNONYM'' '
SET @SQL = @SQL + ' AND type IN (''SN'')'
PRINT(@SQL)
SET @SQL = 'SELECT * FROM ' + @DATABASENAME + '.sys.synonyms a '
+ ' JOIN ' + @DATABASENAME + '.sys.schemas b ON a.schema_id = b.schema_id '
+ ' WHERE type_desc = ''SYNONYM'' '
+ ' AND type IN (''SN'')'
PRINT(@SQL)
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply