Query Results Couble Spaced When Using Cursor
Feb 8, 2001
Just upgraded from 6.5 to 7 and in query analyzer I get double spaced results on queries that previously single-spaced. Installed 2000 QA and still get same results. Is there a setting that we are missing or a workaround to this?
Note ==> The cursor query returns single spaced results when ran alone.
Test code below:
DECLARE @pol char(10)
DECLARE testcursor CURSOR FOR
SELECT policy_no FROM policy_history
WHERE policy_chg_end_dt is null and policy_deleted_sw = 0
OPEN testcursor
FETCH NEXT FROM testcursor INTO @pol
WHILE @@FETCH_STATUS <> -1
BEGIN
SELECT @pol
FETCH NEXT FROM testcursor INTO @pol
END
CLOSE testcursor
DEALLOCATE testcursor
View 1 Replies
ADVERTISEMENT
Jun 22, 2004
I have a table column like "First Name". How do I select that from the table.
It gives me an error if I write query:
select "First Name" from table1.
Any clues?
Thanks
Vinnie
View 2 Replies
View Related
Apr 28, 2004
Is it possible to create a cursor out of the results of a stored procedure.
OR
Is it possible to get just one value.
EXEC sp_columns @table_name = 'tablename', @column_name='columnname'
--I want the type_name value
View 3 Replies
View Related
Aug 7, 2007
Need a little help here.
I have a set of product ids fed in as a delimited string and for each I need to extract the top 1 record from another query based on the id.
I need the results as one table.
Here is my code.
___________________________________
SET NOCOUNT ON
DECLARE @IdsString VARCHAR(255), @Id int
SELECT @IdsString = '918|808|1214|89|995|300|526|1207'
DECLARE GetData CURSOR
FOR Select s.ProductID FROM dbo.SplitProductIDs(@IdsString) as s
OPEN GetData
FETCH NEXT FROM GetData
INTO @Id
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT TOP 1 v.*
FROM dbo.GetProductRateView as v
WHERE v.[id] = @Id
FETCH NEXT FROM GetData
INTO @Id
END
CLOSE GetData
DEALLOCATE GetData
_____________________________________
Do I need to create a temp table and do an 'Insert Into(Select...' with each cusor result or is there a better way?
Any help would be much appreciated.
NB Database was not designed and the client will not tolerate any changes to structure of the tables :eek:
Regards
Shaun McGuile
View 14 Replies
View Related
Apr 4, 2004
Hi all
I want to put the fetch results of a cursor to a temporary DB for manipulation, Im selecting all columns from the table in the cursor and the number of total columns is unknow.
Please guide me on how this could be done...
Thanks in advance
Regards
Benny
View 1 Replies
View Related
Jun 8, 2006
I'm working on a procedure that needs to cycle through the records of some raw data and combine the the current record with the datetime field of the prior record. I have been able to write a script to do that with cursors and variables but my problem is it returns each record separately. How do I go about getting the procedure to return all the records as one set of data?
To see what I mean, the following script for the Pubs DB returns each pass through the data as a seperate query. Since I can't do a select *, what approach should I take? If you want the actual script, I would be happy to provide it.
DECLARE authors_cursor CURSOR
FOR SELECT * FROM authors
OPEN authors_cursor
FETCH NEXT FROM authors_cursor
WHILE @@FETCH_STATUS = 0
begin
FETCH NEXT FROM authors_cursor
end
Close Authors_cursor
deallocate authors_cursor
Thanks in advance
Tony Murunion
View 3 Replies
View Related
Sep 7, 2007
The problem I'm having is that I need a cursor to return multiple results into 1 .NET System.Data.DataTable, and I don't know if it's possible. Actually, the real problem I'm having is that whoever designed this database did a poor job and if it weren't for one small thing I could do this all with aggregates...
But more specifically, this is what I need to accomplish. I have written a cursor that I know works - when I run it in MS SQL Query Analyzer, I get the results I want, but it's returned in multiple tables. I am maintaining a .NET application with some pretty lousy performance and I'm trying to boost it up. I need to take the cursor's results, stuff them into a DataTable, and then return that table to my web application to render.
In my C# code, I am using a SqlDataAdapter to execute my query. This is what I'd like to do...
/*assume query is my working cursor, InvoiceDBConnection is not null, and dt is an empty, non-null DataTable*/
SqlDataAdapter sda = new SqlDataAdapter(query, InvoiceDBConnection);
sda.Fill(dt);
... but this doesn't work, and it makes sense, I guess...since the cursor returns multiple "tables" (not sure if that's actually how the data is returned).
So is there any way to accomplish what I want to do? Or do I have to do it the crappy way it's done now by running my aggregate query and then using a really time-consuming for loop on my results to make the necessary adjustment?
Any help is greatly appreciated
View 5 Replies
View Related
Oct 7, 2004
I want to send 1 email with all clientname records which the cursor gets for me.
My code however is sending 1 email for 1 record i.e clientname got from db. What's wrong? please help.
I ano table to understand here about the while if right.
thanks.
+++++++++++++++++++++++++++++++++++++++++
CREATE PROCEDURE test1
AS
declare @clientName varchar(1000)
declare myCursor CURSOR STATIC
for
select client_name
from clients
-------------------------
-- now prepare and send out the e-mails
declare @ToEmail varchar(255)
declare @FromEmail varchar(255)
declare @Subject varchar(255)
declare @Body varchar(2000)
declare @UserID numeric(38)
declare @UserName varchar(255)
declare @SMTPServer varchar(100)
set @SMTPServer = 'test.testserver.com'
-- loop for each record
open myCursor
fetch next from myCursor
into @clientName
--loop now:
while (@@fetch_status=0)
begin -- while(@@fetch_status=0)
-- check if valid "To" e-mail address was found
if ((@clientName is null) or (ltrim(@clientName) = ''))
begin
--should not come here anytime ideally
set @FromEmail = 'me@test.com'
set @ToEmail = 'me@test.com'
set @Subject = 'was emailed to wrong person'
set @Body = 'the client name got is : '+ @clientName + 'client is null or empty'
end --if
else
begin
set @FromEmail = 'me@test.com'
set @ToEmail = 'me@test.com'
set @Subject = '-testing'
set @Body =
'this will send
ClientName:'+ @clientName
end --end else
-- send the e-mail
--exec dbo.usp_SendCDOSysMailWithAuth @FromEmail, @ToEmail, @Subject, @Body, 0, @SMTPServer
--fetch next from myCursor into @clientName
fetch next from myCursor
into @clientName
end --while(@@fetch_status=0)
exec dbo.usp_SendCDOSysMailWithAuth @FromEmail, @ToEmail, @Subject, @Body, 0, @SMTPServer
close myCursor
deallocate myCursor
GO
View 1 Replies
View Related
Apr 9, 2015
i have one col in a nonclusted index which is bigint 8 bytes follwoing result shows min_record_size_in_bytes and max_record_size_in_bytes is 20 that is 12+8 page size is 8 kB. does that mean a record will use 20 bytes in a page or it will only use the space equalent to data stored in the col.what i have seen is page count is same weather i put some data in the col or null
select * From sys.dm_db_index_physical_stats(9,343,null,null,'DETAILED')
database_idobject_idindex_idpartition_numberindex_type_descalloc_unit_type_descindex_depthindex_
levelavg_fragmentation_in_percentfragment_countavg_fragment_size_in_pagespage_countavg_page_
space_used_in_percentrecord_countghost_record_countversion_ghost_record_countmin_
record_size_in_bytesmax_record_size_in_bytesavg_record_size_in_bytesforwarded_
record_countcompressed_page_count
999208304101HEAPIN_ROW_DATA102.9527559055118116253.187540514.196627131208358500016516516500
9992083041141NONCLUSTERED INDEXIN_ROW_DATA202553.21699.3544600938967585000202020NULL0
9992083041141NONCLUSTERED INDEXIN_ROW_DATA2101115.510254509513221600262626NULL0
View 1 Replies
View Related
Apr 1, 2007
hi, like, if i need to do delete some items with the id = 10000 then also need to update on the remaining items on the with the same idthen i will need to go through all the records to fetch the items with the same id right? so, is there something that i can use to hold those records so that i can do the delete and update just on those records and don't need to query twice? or is there a way to do that in one go ?thanks in advance!
View 1 Replies
View Related
Feb 12, 2008
Hello. I currently have a website that has a table on one webpage. When a record is clicked, the primary key of that record is transfered in the query string to another page and fed into an sql statement. In this case its selecting a project on the first page, and displaying all the scripts for that project on another page. I also have an additional dropdownlist on the second page that i use to filter the scripts by an attribute called 'testdomain'. At present this works to an extent. When i click a project, i am navigated to the scripts page which is empty except for the dropdownlist. i then select a 'testdomain' from the dropdownlist and the page populates with scripts (formview) for the particular test domain. what i would like is for all the scripts to be displayed using the formview in the first instance when the user arrives at the second page. from there, they can then filter the scripts using the dropdownlist.
My current SQL statement is as follows.
SelectCommand="SELECT * FROM [TestScript] WHERE (([ProjectID] = @ProjectID) AND ([TestDomain] = @TestDomain))"
So what is happening is when testdomain = a null value, it does not select any scripts. Is there a way i can achieve the behaivour of the page as i outlined above? Any help would be appreciated.
Thanks,
James.
View 1 Replies
View Related
May 28, 2008
ok can someone tell me why i get two different answers for the same query. (looking for last day of month for a given date)
SELECT DATEADD(ms, - 3, DATEADD(mm, DATEDIFF(m, 0, CAST('12/20/2006' AS datetime)) + 1, 0)) AS Expr1
FROM testsupplierSCNCR
I am getting the result of 01/01/2007
but in query analizer I get the result of
12/31/2006
Why the different dates
View 4 Replies
View Related
Sep 22, 2015
-- The 3rd query uses an incorrect column name in a sub-query and succeeds but rows are incorrectly qualified. This is very DANGEROUS!!!
-- The issue exists is in 2008 R2, 2012 and 2014 and is "By Design"
set nocount on
go
if object_id('tempdb.dbo.#t1') IS NOT NULL drop table #t1
if object_id('tempdb.dbo
[code]....
This succeeds when the invalid column name is a valid column name in the outer query. So in this situation the sub-query would fail when run by itself but succeed with an incorrectly applied filter when run as a sub-query. The danger here is that if a SQL Server user runs DML in a production database with such a sub-query which then the results are likely not the expected results with potentially unintended actions applied against the data. how many SQL Server users have had incorrectly applied DML or incorrect query results and don't even know it....?
View 2 Replies
View Related
Jul 30, 2015
For each customer, I want to add all of their telephone numbers to a different column. That is, multiple columns (depending on the number of telephone numbers) for each customer/row. How can I achieve that?
I want my output to be
CUSTOMER ID, FIRST NAME, LAST NAME, TEL1, TEL2, TEL3, ... etc
Each 'Tel' will relate to a one or more records in the PHONES table that is linked back to the customer.
I want to do it using SELECT. Is it possible?
View 13 Replies
View Related
Feb 12, 2008
When I run the following query from Query Analyzer in SQL Serer 2005, I get a message back that says.
Command(s) completed successfully.
What I really need it to do is to display the results of the query. Does anyone know how to do this?
declare @SniierId as uniqueidentifierset @SniierId = '85555560-AD5D-430C-9B97-FB0AC3C7DA1F'declare @SniierAlias as nvarchar(50)declare @AlwaysShowEditButton as bitdeclare @SniierName as nvarchar (128)/* Check access for Sniier */SELECT TOP 1 @SniierName = Sniiers.SniierName, @SniierAlias = Sniiers.SniierAlias, @AlwaysShowEditButton = Sniiers.AlwaysShowEditButtonFROM SniiersWHERE Sniiers.SniierId=@SniierId
View 3 Replies
View Related
Apr 13, 1999
hi, I created a cursor, can I use "select * from cursorname in the query window?
thanks
Ali
View 1 Replies
View Related
May 2, 2006
hi friends,
i want to get that row's startdatetime where sum of duration becomes equal
to or greater than 1000 without using cursor.
create table test
(
duration int,
startdatetime bigint primary key,
userid int
)
go
insert into practise
select 400, 500, 1
union all
select 500, 600, 1
union all
select 100, 650, 1
union all
select 100, 700, 1
go
thnks in adv.,
chakri
View 8 Replies
View Related
Aug 2, 2012
linking two tables together to get an end result
find below the code i have used
The first part of the query provides me with the info i need
SELECT sub.*,
case when rm_sales_band = '2M to 4M' then 'Kirsty' else RM end as rm
into #rmtmp
[Code].....
View 1 Replies
View Related
Jul 10, 2015
I have a query that performs a comparison between 2 different databases and returns the results of the comparison. It returns 2 columns. The 1st column is the value of the object being compared, and the 2nd column is a number representing any discrepancies.What I would like to do is use the results from this 1st query in the where clause of another separate query so that this 2nd query will only run for any primary values from the 1st query where a secondary value in the 1st query is not equal to zero.I was thinking of using an "IN" function in the 2nd query to pull data from the 1st column in the 1st query where the 2nd column in the 1st query != 0, but I'm having trouble ironing out the correct syntax, and conceptualizing this optimally.
While I would prefer to only return values from the 1st query where the comparison value != 0 in order to have a concise list to work with, I am having difficulty in that the comparison value is a mathematical calculation of 2 different tables in 2 different databases, and so far I've been forced to include it in the select criteria because the where clause does not accept it.Also, I am not a DBA by trade. I am a system administrator writing SQL code for reporting data from an application I support.
View 6 Replies
View Related
Oct 10, 2004
Please help me to get the required result:
For each IDS in table1
- change the ids to numbers (eg. for '1,2,3' get the numbers (IntValue) 1, 2 & 3)
- in table2, find the maxVal for each number
- disply the table1..ids, number, table2..maxVal & table1..idsDesc,
order by table1..ids, table2..maxVal & IntValue
I have 2 tables, over milin records each. The Simplified versions of the tables looks like that:
create table table1 (ids varchar(100), idsDesc varchar(100))
go
insert table1 select '1,2,3', 'Description 1'
union all select '2,3,4', 'Description 2'
union all select '1,7', 'Description 3'
union all select '16,3,8', 'Description 4'
union all select '2,5,6,1', 'Description 5'
go
create table table2 (ids int, maxVal int)
go
insert table2 select 1, 10
union all select 2, 6
union all select 3, 12
union all select 4, 11
union all select 5, 66
union all select 6, 4
union all select 7, 3
-- union all select 8, 5 -- no value for 8
union all select 9, 6
union all select 16, 12
go
I have also function that returns table variable of numbers delivered from given string:
create function dbo.fn_StrToIntValues ( @str varchar(1000) )
returns @numsTbl table (IntValue int not null)
The command
select * from dbo.fn_StrToIntValues('1,2,33')
Returns --> intValue
1
2
33
Can I use SQL query and not cursor to get the following result ?
Required Output :
ids IntValue maxVal idsDesc
--------- -------- ------- ---------------
'2,5,6,1' 5 66 'Description 5'
'16,3,8' 16 12 'Description 4'
'2,3,4,9' 3 12 'Description 2'
'1,2,3' 3 12 'Description 1'
'1,7' 1 10 'Description 3'
View 2 Replies
View Related
Sep 12, 2014
SELECT debit.ACCOUNT_NO, debit.Serviced_Amt,credit.Tran_Amt,credit.Serviced_Flag
FROM tbl_Interest_Debit as debit
inner join
tbl_Credit as credit
on debit.ACCOUNT_NO=credit.Account_No
order by credit.TRANSACTION_VALUE_DATE
[code]....
I want that service_amount should be subtracted from tran_amt until service_amount become zeroOnce service_amount becomes zero service_flag should be changed to 1.using with cursor.
View 2 Replies
View Related
Feb 29, 2008
i m new in sql...and i have this procedure..which have cursor inside..
1. i want to get all distinct date into #tempt table.
2. In the loop for each distinct date fetch all the date into another #temp
3. get max date from that #temp and use that date to get the data from original table
but i m getting 0 rows else all 8000 rows..which is wrong..can anyone help me plz...
create procedure procdate1
(@name varchar(50))
as
begin
SET NOCOUNT ON
DECLARE @MaxDate datetime
DECLARE @Date datetime
select id, title, dated,
CONVERT(CHAR(10), dated,101) as date,
CONVERT(CHAR(8), dated,114) as time from general
where name = @name AND dated = @MaxDate
DECLARE CUR1 CURSOR FOR
SELECT @DATE FROM #tempt
OPEN CUR1
FETCH NEXT FROM CUR1 INTO @Date
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT dated INTO #Date1 FROM general WHERE CONVERT(CHAR(10),dated,101) = @Date
SELECT @MaxDate = MAX(dated) FROM #date1
--DROP TABLE #Date1
FETCH NEXT FROM CUR1 INTO @Date
continue
CLOSE CUR1
DEALLOCATE CUR1
end
-- DROP TABLE #tempt
end
View 3 Replies
View Related
May 26, 2006
I'm a really beginner about sql2000.During my test I have created the following query. It's works ok until Ido't add the code included in section A, when I add it the i obtain theerror: Cursor not returned from queryAnyone can help me?Thanks Carlo M.set nocount onIF OBJECT_ID('storico_big') IS NULL --- section A begincreate table storico_big( data datetime,bcarrier varchar(20),bda CHAR(30),bzone char(50),bdur int) ;insert into storico_big --- section Aendselect top 10000adetdate,bcarrier,bda,bzone,bdurfrom pp_cdr (nolock)whereadetdate < :data_fin and adetdate > :data_in order by adetdateset nocount off------ end of query
View 3 Replies
View Related
Jul 20, 2005
Could someone help me by answering the questions below?What's a cursor?What's difference between Query and View?Is a RecordSet just part of a table? Can it be part of a query of view?If the content in a table changed, is it necessary for a old recordset torenew itself by do "Requery()"?Thanks for your help!
View 4 Replies
View Related
Dec 7, 2007
UPDATE SCORESET QUAL_SCORE = ((SCORE - average_score)/deviation_score)*(-0.25) +((accuracy_score - accuracy_average_score)/accuracy_deviation_score)*0.25))WHERE SCORES.DISABLEMENT_ZIP = v_disablement_zipAND SCORES.EQPMNT_CODE = v_eqpmnt_code;
is it possible to use this one query to update the QUAL_SCORE field without using cursor.if SCORE and deviation_score are 0, Then (SCORE - average_score)/deviation_score)*(-0.25) is 0,if accuracy_score and accuracy_deviation_score are 0, then (accuracy_score - accuracy_average_score)/accuracy_deviation_score)*0.25 is 0.
Thanks
View 2 Replies
View Related
Sep 18, 2006
Hi,
I am declaring the cursor based on a query which is generated dynamically. but it is not working
Declare @tempSQL varchar(1000)
--- This query will be generated based on my other conditon and will be stored in a variable
set @tempsql = 'select * from orders'
declare cursor test for @tempsql
open test
This code is not working.
please suggest
Nitin
View 12 Replies
View Related
Aug 12, 2015
In MSDN file I read about static cursor
STATIC
Defines a cursor that makes a temporary copy of the data to be used by the cursor. All requests to the cursor are answered from this temporary table in
tempdb; therefore, modifications made to base tables are not reflected in the data returned by fetches made to this cursor, and this cursor does not allow modifications
It say's that modifications is not allowed in the static cursor. I have a questions regarding that
Static Cursor
declare ll cursor global static
for select name, salary from ag
open ll
fetch from ll
while @@FETCH_STATUS=0
fetch from ll
update ag set salary=200 where 1=1
close ll
deallocate ll
In "AG" table, "SALARY" was 100 for all the entries. When I run the Cursor, it showed the salary value as "100" correctly.After the cursor was closed, I run the query select * from AG.But the result had updated to salary 200 as given in the cursor. file says modifications is not allowed in the static cursor.But I am able to update the data using static cursor.
View 3 Replies
View Related
Sep 25, 2015
I have a stored proc I want to convert it to either a Normal Query using A while loop or a set based operation/recursive cte as I want to run it for multiple CompanyNames. I get the error message as An INSERT EXEC statement cannot be nested when I execute if for Multiple Companies using another Cursor
If I convert it to a Function I get the below error message
Invalid use of a side-effecting operator 'EXECUTE STRING' within a function
converting this query to a normal query or let me know if there is any change which need to done to work with multiple companynames.
CREATE PROC [dbo].[USPT] @CompanyName varchar(50),@tablename varchar(50)
AS
BEGIN
-- EXEC [USPT] 'xyz corp','Sales Header'
DECLARE @str1 VARCHAR (MAX)
set @str1 = '
DECLARE @No VARCHAR (MAX)
[code]....
View 5 Replies
View Related
Oct 22, 2015
I am writing a custom query to determine if a legacy table exists or not. From My CMS Server I already have all the instances I have to query and I store the name of the instance in the @Instance variable. I cannot get those stubborn ticks to work right in my query. Below I am using the IF EXISTS statement to search the metadata for the legacy table.
DECLARE @Found tinyint
DECLARE @Instance varchar(100)
set @Instance = 'The Instance'
IF (EXISTS (SELECT a.*
FROM OPENROWSET('SQLNCLI', 'Server=' + @Instance + ';UID=DBAReader;PWD=DBAReader;','SELECT * FROM [DBA].INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = ''TheTable''') AS a))
SET @Found = 1
ELSE
SET @Found = 0
PRINT @Found
View 2 Replies
View Related
Mar 7, 2008
Hi i have a script works in sql query analyzer;
declare @id decimal
declare mycur CURSOR SCROLL for select myRowID from myTable order by myRowID
open mycur;
Fetch ABSOLUTE 30 from mycur into @id
close mycur;
deallocate mycur;
select @id
this script turns me a value.
i create a stored procedure from above script and its syntax is ok;
CREATE PROCEDURE SELECT_MyRow
AS
declare @cur cursor
declare @RowID decimal
set @cur = CURSOR SCROLL
for select myRowID from myTable order by myRowID
open @cur
Fetch ABSOLUTE 30 from @cur into @RowID
close @cur
deallocate @cur
select @RowID
GO
my c# code using stored procedure is below;
Code Snippet
try
{
OleDbCommand cmd = new OleDbCommand("SELECT_MyRow", myconnection);
cmd.CommandType = CommandType.StoredProcedure;
myconnection.Open();
OleDbDataReader reader = cmd.ExecuteReader();
MessageBox.Show(reader.GetName(0));//here fails
while (reader.Read())
{
MessageBox.Show(reader.GetDecimal(0).ToString());
}
reader.Close();
myconnection.Close();
}
catch(Exception ex)
{
MessageBox.Show(ex.Message);
}
The code above fails because reader reads no values, error message is "No data exists for the row/column"
but i know exists. Can anyone help me, what is the difference between stored procedure and script ?
View 4 Replies
View Related
Jul 5, 2007
Hi,
is there any update on the subject?
Jet has it for ages.
I want to use Scrollable/Updateable Cursors & OLEDB Rowsets opened via Select query.
Last time I have heard that MSFT is planning to add this in the future.
Is there a beta version that I can try?
Thanks,
Konstantin
View 8 Replies
View Related
Jul 20, 2005
Hello,I have a test database with table A containing 10,000 rows and a tableB containing 100,000 rows. Rows in B are "children" of rows in A -each row in A has 10 related rows in B (ie. B has a foreign key to A).Using ODBC I am executing the following loop 10,000 times, expressedbelow in pseudo-code:"select * from A order by a_pk option (fast 1)""fetch from A result set""select * from B where where fk_to_a = 'xxx' order by b_pk option(fast 1)""fetch from B result set" repeated 10 timesIn the above psueod-code 'xxx' is the primary key of the current Arow. NOTE: it is not a mistake that we are repeatedly doing the Aquery and retrieving only the first row.When the queries use fast-forward-only cursors this takes about 2.5minutes. When the queries use dynamic cursors this takes about 1 hour.Does anyone know why the dynamic cursor is killing performance?Because of the SQL Server ODBC driver it is not possible to havenested/multiple fast-forward-only cursors, hence I need to exploreother alternatives.I can only assume that a different query plan is getting constructedfor the dynamic cursor case versus the fast forward only cursor, but Ihave no way of finding out what that query plan is.All help appreciated.Kevin
View 1 Replies
View Related
Sep 20, 2007
I'm trying to implement a sp_MSforeachsp howvever when I call sp_MSforeach_worker
I get the following error can you please explain this problem to me so I can over come the issue.
Msg 16958, Level 16, State 3, Procedure sp_MSforeach_worker, Line 31
Could not complete cursor operation because the set options have changed since the cursor was declared.
Msg 16958, Level 16, State 3, Procedure sp_MSforeach_worker, Line 32
Could not complete cursor operation because the set options have changed since the cursor was declared.
Msg 16917, Level 16, State 1, Procedure sp_MSforeach_worker, Line 153
Cursor is not open.
here is the stored procedure:
Alter PROCEDURE [dbo].[sp_MSforeachsp]
@command1 nvarchar(2000)
, @replacechar nchar(1) = N'?'
, @command2 nvarchar(2000) = null
, @command3 nvarchar(2000) = null
, @whereand nvarchar(2000) = null
, @precommand nvarchar(2000) = null
, @postcommand nvarchar(2000) = null
AS
/* This procedure belongs in the "master" database so it is acessible to all databases */
/* This proc returns one or more rows for each stored procedure */
/* @precommand and @postcommand may be used to force a single result set via a temp table. */
declare @retval int
if (@precommand is not null) EXECUTE(@precommand)
/* Create the select */
EXECUTE(N'declare hCForEachTable cursor global for
SELECT QUOTENAME(SPECIFIC_SCHEMA)+''.''+QUOTENAME(ROUTINE_NAME)
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_TYPE = ''PROCEDURE''
AND OBJECTPROPERTY(OBJECT_ID(QUOTENAME(SPECIFIC_SCHEMA)+''.''+QUOTENAME(ROUTINE_NAME)), ''IsMSShipped'') = 0 '
+ @whereand)
select @retval = @@error
if (@retval = 0)
EXECUTE @retval = [dbo].sp_MSforeach_worker @command1, @replacechar, @command2, @command3, 0
if (@retval = 0 and @postcommand is not null)
EXECUTE(@postcommand)
RETURN @retval
GO
example useage:
EXEC sp_MSforeachsp @command1="PRINT '?' GRANT EXECUTE ON ? TO [superuser]"
GO
View 7 Replies
View Related
