Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
basicconfiguration
Constraint Violating Yak Guru
358 Posts |
 Posted - 2010-06-15 : 19:45:24
|
| I'm using a cursor to create the same SP in multiple database however the cursor doesn't like the GO statement.DECLARE @dbname VARCHAR(50), @query VARCHAR(8000) DECLARE cur_objc CURSOR FOR SELECT name FROM sys.databases WHERE name IN ('kkk','xxx','abc') OPEN cur_objc FETCH NEXT FROM cur_objc INTO @dbname WHILE @@FETCH_STATUS = 0 BEGIN SET @query = 'use ' + @dbname + 'if exists (select * from sys.procedures where name = ''TruncTbl'')drop proc TruncTblcreate PROC [dbo].[TruncTbl]@schema VARCHAR(30) = ''dbo'', @tbl VARCHAR(50)WITH EXECUTE AS OWNERASDECLARE @sql VARCHAR(100)SET @sql = ''TRUNCATE TABLE ['' + @schema + ''].['' + @tbl + '']''EXEC (@sql)--print (@sql)'exec (@query)print (@query) FETCH NEXT FROM cur_objc INTO @dbname END CLOSE cur_objcDEALLOCATE cur_objc Anyway to work around this? |
|
|
jezemine
Master Smack Fu Yak Hacker
2886 Posts |
Posted - 2010-06-16 : 01:10:48
|
prefer this:1. put script for your proc in a .sql file.2. execute the .sql file using SQLCMD via .bat or powershell script. in the script, loop over the databases you want the proc in
elsasoft.org |
 |
|
|
basicconfiguration
Constraint Violating Yak Guru
358 Posts |
Posted - 2010-06-16 : 11:23:11
|
| Thanks Jezemine I will give it a try in a little bit. |
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
jezemine
Master Smack Fu Yak Hacker
2886 Posts |
Posted - 2010-06-16 : 18:53:43
|
also the proc itself is questionable. (I didn't look at it when I first answered the question)A proc to truncate a table where you pass the tablename? why not just issue the truncate statement directly?It's generally frowned upon to be passing strings to procedures and then constructing dynamic sql from that input. Your code is vulnerable to sql injection.
elsasoft.org |
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
basicconfiguration
Constraint Violating Yak Guru
358 Posts |
Posted - 2010-06-17 : 00:16:57
|
| jezemine,i dont wwant to assign ddl_dbadmin server role. Thats why.x002548,no RI, this are reporting tables. |
|
|
|
|
|
|
cursor multiple database