473,545 Members | 1,617 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

How to exec stored proc dynamically

Hello
I have 2 procedures setup in master database, sp_RebuildIndex esMain and
sp_RebuildIndex esSub

The Sub just shows and execute DBCC commands for passed database
context

sp_RebuildIndex esSub(@listOnly bit=0, @maxfrag Decimal=30.0)

This runs fine if I do pubs..sp_Rebuil dIndexesSub
However when run thru. the Main proc, I get Incorrect syntax near
'pubs'.
The main proc is

Create Proc sp_RebuildIndex esMain(@dbName sysname, @listOnly bit=0,
@maxFrag Decimal=30.0)
As
Begin
Set NOCOUNT ON

Declare crDbs CURSOR For
Select CATALOG_NAME From INFORMATION_SCH EMA.SCHEMATA
Where CATALOG_NAME NOT IN ('tempdb', 'master', 'msdb', 'model',
'distribution', 'Northwind', 'pubs')
And CATALOG_NAME Like @dbName

Declare @execstr nvarchar(2000)

Open crDbs
Fetch crDbs INTO @dbName
If (@@FETCH_STATUS <>0) --Then no matching databases
Begin
Close crDbs
Deallocate CrDbs
Print 'No databases were found that match ''' + @dbName + ''''
Return -1
End

While(@@FETCH_S TATUS=0)
Begin
Print Char(13) + 'Rebuilding indexes on ' + @dbName
Print Char(13)
Set @execstr = @dbName + '..sp_RebuildIn dexesSub '
EXEC sp_executesql @execstr, N'@listOnly bit, @maxFrag Decimal',
@listOnly, @maxFrag
Fetch crDbs INTO @dbName
End
Close crDbs
Deallocate CrDbs
Return 0
End

thanks
Sunit
su********@netz ero.com

Jul 23 '05 #1
5 6107
I believe if you change:
Set @execstr = @dbName + '..sp_RebuildIn dexesSub '
to
Set @execstr = '[' + @dbName + '..sp_RebuildIn dexesSub] '

it should work.

Personally, instead of creating sp_RebuildIndex esSub in each database,
you should just create it in the master database. Then run a job like
so:

sp_msforeachdb 'USE ? if db_id(''?'') > 4
BEGIN
Print Char(13) + 'Rebuilding indexes on ' + ?
exec sp_RebuildIndex esSub 0, 30.0
END'

Be sure not to run "exec master..sp_Rebu ildIndexesSub 0, 30.0" or else
it will only run the master database during each loop.

Modify to your heart's content.

Jul 23 '05 #2
Now it says
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'SPlant5_MODEL. .sp_RebuildInde xesSub'.

The stored procedure are setup in the master db. That's why I'm using
the dbname..spname to change db context.

thanks
Sunit

*** Sent via Developersdex http://www.developersdex.com ***
Jul 23 '05 #3
Don't use sp_executesql. The problem stems from you trying to run a
stored procedure through a stored procedure. So instead, build your
string first and run it by using EXEC(@execstr).

SET @execstr = 'USE ' + @dbname + ' exec sp_RebuildIndex esSub ' +
RTRIM(@listOnly ) + ',' + RTRIM(@maxFrag)
EXEC (@execstr)

Jul 23 '05 #4
Got it. Had to change to this

Set @execstr = @dbName + '..sp_RebuildIn dexesSub'
Exec @execstr @listOnly, @maxFrag

thanks
Sunit

Jul 23 '05 #5
You are right. Your code is much cleaner :)

Jul 23 '05 #6

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
19995
by: aaj | last post by:
Hi all I have a stored procedure that has the line EXEC master..xp_cmdshell 'dtsrun /Stestjob1 /N testdts /E' If I run the SP from an access front end as a trusted user or from a scheduled job it runs fine and exectues the dts. If I run the stored procedure using VB6 as a standard connection the dtsjob
2
10506
by: hubert.trzewik | last post by:
Hello, Is it possible to EXEC stored procedure from a query? I want to execute stored procedure for every line of SELECT result table. I guess it's possible with cursors, but maybe it's possible to make it easier. Give an example, please.
5
2126
by: Rhino | last post by:
This question relates to DB2 Version 6 on OS/390. Can a (COBOL) stored procedure on this platform do file I/O, i.e. write to a sequential file? I am trying to debug a stored procedure. As far as I know, DB2 stored procedures cannot do terminal I/O on any operating system but I know that (Java) stored procedures in Windows/Linux/Unix can...
1
1539
by: VM | last post by:
I'm working on a win appication that is constantly querying a small-sized DB. Until now, I've been using Selects from within my app but, all of a sudden I remembered of an application I was working on a year ago (with PowerBuilder) that queried the DB by invoking stored procedures that return the data to the application. One of the main...
2
1712
by: Mike Hutton | last post by:
I have a rather odd problem. I have a SP which uses temp. tables along the way, and then returns a table of results: CREATE PROCEDURE dbo.usp_myproc( @pNameList VARCHAR(6000) ) AS
28
72361
by: mooreit | last post by:
The purpose for my questions is accessing these technologies from applications. I develop both applications and databases. Working with Microsoft C#.NET and Microsoft SQL Server 2000 Production and 2005 Test Environments. What is the purpose of a view if I can just copy the vode from a view and put it into a stored procedure? Should I be...
4
27476
by: daz_oldham | last post by:
Hi All I am trying to execute a stored procedure that does a very simple lookup and returns a text field. However, when I try to execute it, I am getting a rather strange error that I can't seem to fix! There is defiantely information coming back as I have tested this in Query Analyzer. The error actuall comes on my...
3
6381
by: satkins | last post by:
Hello everyone. I've been trying to find if this is even possible but nothing I've found has really helped. I've got a stored proc with a few input parameters and one output parameter. I would like to call this proc from another proc and have proc 2 return the result of proc1 as a column. Some thing like this create procedure Proc1 ...
15
2260
by: Burt | last post by:
I'm a stored proc guy, but a lot of people at my company use inline sql in their apps, sometimes putting the sql in a text file, sometimes hardcoding it. They don't see much benefit from procs, and say anyway they're are db specific- what if we change from SQL Server to Oracle one day? What say you experts? Is this one of those "no right...
0
7459
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main...
0
7393
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language...
0
7803
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...
0
7749
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the...
1
5322
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes...
0
4942
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert...
0
3444
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in...
1
1012
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
0
695
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.