[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

Procedure create_cmd

Posted on 2010-04-08
4
Medium Priority
?
357 Views
Last Modified: 2012-05-09
Hello,

I try to create cmd automatically :

CREATE PROCEDURE [dbo].[create_cmd]
@Dbname varchar(30),
@Schemaname varchar(30),
@Tbname varchar(30)
AS

SET NOCOUNT ON

declare @sql3 nvarchar(max);                              
set @sql3 = select 'call d:\solutions\[+ @Dbname + ]\env.bat '
UNION
SELECT 'D:\solutions\[+ @Dbname + ]\valfil -r  ' + TABLE_SCHEMA + '.' + TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = ['+ @Schemaname + '];
exec sp_executesql @sql3      

I have an error in the select.

Thanks

Regards

bibi
print (@sql3)
0
Comment
Question by:bibi92
  • 2
4 Comments
 
LVL 33

Expert Comment

by:knightEknight
ID: 30117544
you might be missing a single-quote somewhere in here:

SELECT 'D:\solutions\[
0
 
LVL 33

Expert Comment

by:knightEknight
ID: 30117636
SELECT 'D:\solutions\['+ @Dbname + ']\valfil -r  '
0
 
LVL 75

Accepted Solution

by:
Aneesh Retnakaran earned 2000 total points
ID: 30118805

SET @sql3 = '
select ''call d:\solutions\['+ @Dbname+']\env.bat ''
UNION
SELECT ''D:\solutions\['+ @Dbname +' ]\valfil -r '' + TABLE_SCHEMA + ''.'' + TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = '''+ @Schemaname + '''; '
print @sql3
exec sp_executesql @sql3
0
 

Author Comment

by:bibi92
ID: 30144150
Thanks a lot aneeshattingal, your script help me I have modified this for having the result I need :
 PROCEDURE [dbo].[create_cmd]
@Dbname varchar(30),
@Schemaname varchar(30)
AS

SET NOCOUNT ON

declare @sql3 nvarchar(max);                              

SET @sql3 = '
select ''call D:\SAGE\SOLUTIONS\'+ @Dbname+'\Runtime\bin\env.bat ''
SELECT ''D:\SAGE\SOLUTIONS\'+ @Dbname +'\Runtime\bin\valfil -r '' + TABLE_SCHEMA + ''.'' + TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = '''+ @Schemaname + ''' ;'

print @sql3
exec sp_executesql @sql3

Thanks

bibi
0

Featured Post

Learn to develop an Android App

Want to increase your earning potential in 2018? Pad your resume with app building experience. Learn how with this hands-on course.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
Microsoft provides a rich set of technologies for High Availability and Disaster Recovery solutions.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

607 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question