October 17, 2016 at 3:17 pm
Stumbled across the sp_MSForeachdb proc and thought I could use it for my problem. Trying to select off data sets across over 100 databases.. Thought this might work. however everything I've tried doesn't work.
declare @fromDate datetime
set @fromDate = '2016-09-15 00:00:00.000'
declare @toDate datetime
set @toDate = '2016-09-17 00:00:00.000'
declare @command nvarchar(1000)
Select @command ='SELECT PaymentNo, PaymentDate, TotalAmt from [PaymentHeader] WHERE Paymentdate between @fromDate AND @toDate'
EXEC sp_MSforeachdb @command
October 17, 2016 at 3:22 pm
This command runs in its own context. What that means is that you have to put all of the SQL - including the variable declarations - into your variable and then execute it as a single batch.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
October 17, 2016 at 3:23 pm
You should also be aware that it runs against the system databases (master, model etc.)
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
October 17, 2016 at 3:58 pm
Phil Parkin (10/17/2016)
This command runs in its own context. What that means is that you have to put all of the SQL - including the variable declarations - into your variable and then execute it as a single batch.
That's not completely true. The command can access temp tables--but not table variables--declared in the main query. I've used that to store the results in a temporary table.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply