SQL Server Cursor Names - Missing From Dm_exec_cursors? API_CURSOR0000000000000003435 ?
Aug 30, 2007
Hi,
We have an application that uses a large number of cursors, and I can run sys.dm_exec_cursors (0) to view them. However, the 'name' column always appears to be NULL no matter when I run the query.
I see, when tracking some blocked processes, that the blocked processes usually have in inputbuffer that looks something like FETCH API_CURSOR00000000000003435 - which I'm trying to map back to the cursor itself. However, I can't seem to do so based on the name - since it isn't in the DMV.
I have set up a query where I am using a cursor to pass result from onequery to use as select parameters in another query. The problem I amhaving is that when I pass the results into the second query I get thecolumn headers?How do you suppress the column headers from showing in the query? Isee how you select options - print headers.ThanksJason
So I have an MDX query in an SSRS data set. Here is my MDX query:
Code SnippetSELECT { [Measures].[Gross Sales Amount USD], [Measures].[Net Sales Amount USD] } ON COLUMNS, { ([Promotion].[Media Property].[Promo Code Description].ALLMEMBERS) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM ( SELECT ( STRTOMEMBER(@BeginDateConvert, CONSTRAINED) : STRTOMEMBER(@EndDateConvert, CONSTRAINED) ) ON COLUMNS FROM ( SELECT ( STRTOSET(@PromotionMediaProperty, CONSTRAINED) ) ON COLUMNS FROM ( SELECT ( { [Promotion].[Campaigns].[Campaign].&[Paid Partner] } ) ON COLUMNS FROM ( SELECT ( { [Products].[Product Line].[Line].&[Merchandise] } ) ON COLUMNS FROM ( SELECT ( { [BusinessUnit].[Business Unit].[Product Business Unit].&[40] } ) ON COLUMNS FROM [Net Sales]))))) WHERE ( [BusinessUnit].[Business Unit].[Product Business Unit].&[40], [Products].[Product Line].[Line].&[Merchandise], [Promotion].[Campaigns].[Campaign].&[Paid Partner] ) CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS
This query returns 4 fields. Media Property, Promo Code Description, Gross Sales, and Net Sales. For the given query the measures are empty or null. I do not want any data to show up when the measures are null so I put in NON EMPTY clauses before the COLUMNS and before the ROWS. So now my query looks like this: (I only added the NON EMPTY clauses)
Code Snippet SELECT NON EMPTY { [Measures].[Gross Sales Amount USD], [Measures].[Net Sales Amount USD] } ON COLUMNS, NON EMPTY{ ([Promotion].[Media Property].[Promo Code Description].ALLMEMBERS) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM ( SELECT ( STRTOMEMBER(@BeginDateConvert, CONSTRAINED) : STRTOMEMBER(@EndDateConvert, CONSTRAINED) ) ON COLUMNS FROM ( SELECT ( STRTOSET(@PromotionMediaProperty, CONSTRAINED) ) ON COLUMNS FROM ( SELECT ( { [Promotion].[Campaigns].[Campaign].&[Paid Partner] } ) ON COLUMNS FROM ( SELECT ( { [Products].[Product Line].[Line].&[Merchandise] } ) ON COLUMNS FROM ( SELECT ( { [BusinessUnit].[Business Unit].[Product Business Unit].&[40] } ) ON COLUMNS FROM [Net Sales]))))) WHERE ( [BusinessUnit].[Business Unit].[Product Business Unit].&[40], [Products].[Product Line].[Line].&[Merchandise], [Promotion].[Campaigns].[Campaign].&[Paid Partner] ) CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS
Adding the NON EMPTY returns nothing... not even field names. This is a problem because, I have a table in the report that looks at this data set and when there are no fields, the report can't run.
How can I still have NON EMPTY functionality and still show the fields? Is this a problem in SSRS?
I'm going to be setting up DB mirroring between two SQL Server 2005 boxes. Since these are on two different servers, can the instance names be the same? Is there any reason NOT to do so if the mirror server is going to be used exclusively for DB mirroring?
For example: if the my primary DB is located on SERVER1INSTANCE1, can the mirror be SERVER2INSTANCE1 or do the instance names have to be different even though they're on different boxes.
Hello there. I'm trying to populate a drop down box with a list of all available instances of SQL server (express or not) available on a network. To list all instances I'm using EnumAvailableServers() in SmoApplication.
The issue is that the Instance column in the returned data table is always empty, and both the Name and the Server columns contain the same string -- the name of the computer the SQL server is installed. Locally I have SSE installed, the instance name is <computer_name>SQLEXPRESS, however, using Smo I can't seem to get to the either full instance name or just the instance name. I can't assume SQLEXPRESS, since there may be more than one installed.
Alternately I used EnumRegisteredServers() in SmoApplication.SqlServerRegistrations and that, while returning instance information (can be retrieved from the RegisteredServer in the returned collection), doesn't always return the remote SQL servers in the list.
Any idea how can this be done? Maybe I'm going at it the wrong way, maybe there's another class / method I need to use to retrieve all instances?
I am designing a package to export staging tables into a flat file.The names of the tables will be: TableAStaging_YYYYMM and TableBStaging_YYYYMM. As you can see the names of the tables will be changing each month.
The flat files will have similar naming: C:MyPathFlatFileTableAStaging__YYYYMM and C:MyPathFlatFileTableAStaging__YYYYMM.I want to run the package as an sql job in two steps, one for each table.I need to dynamically pass the table names and file names (together with the path) to the IS package.
As part if a recent requirement I have to export Chinese/Singaporean names in a CSV file. The data in the tables is a NVARCHAR(256).
I am using a FlatFile Connection manager where all the present columns from the table are exported as NVARCHARs. My understanding was that the Chinese/Singaporean names would blend seamlessly with NVARCHARs in place. But, they get garbled when pushed to the CSV.
Here is the connection manager setup
There are a lot of suggestions of fixing this by copying/pasting to a notepad file and changing the formatting... But I cant do that since the file is generated using a schedules SSIS package. How can I tweak the process to fix the issue?
I was wondering if anyone has an idea of how we could find the table names and column names of the tables in our Sql server database at runtime/dynamically given our connection string? Please let me know.
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.
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
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
DECLARE DBCur CURSOR FOR SELECT U_OB_DB FROM [@OB_TB04_COMPDATA]
OPEN DBCur FETCH NEXT FROM DBCur INTO @DBNAME
WHILE @@FETCH_STATUS = 0 BEGIN
SELECT @SQLCMD = 'SELECT T0.CARDCODE, T0.U_OB_TID AS TRANSID, T0.DOCNUM AS INV_NO, ' + + 'T0.DOCDATE AS INV_DATE, T0.DOCTOTAL AS INV_AMT, T0.U_OB_DONO AS DONO ' + + 'FROM ' + @DBNAME + '.dbo.OINV T0 WHERE T0.U_OB_TID IS NOT NULL' EXEC(@SQLCMD) PRINT @SQLCMD FETCH NEXT FROM DBCur INTO @DBNAME
END
CLOSE DBCur DEALLOCATE DBCur
Part 2
SELECT T4.U_OB_PCOMP AS PARENTCOMP, T0.CARDCODE, T0.CARDNAME, ISNULL(T0.U_OB_TID,'') AS TRANSID, T0.DOCNUM AS SONO, T0.DOCDATE AS SODATE, SUM(T1.QUANTITY) AS SOQTY, T0.DOCTOTAL - T0.TOTALEXPNS AS SO_AMT, T3.DOCNUM AS DONO, T3.DOCDATE AS DO_DATE, SUM(T2.QUANTITY) AS DOQTY, T3.DOCTOTAL - T3.TOTALEXPNS AS DO_AMT INTO #MAIN FROM ORDR T0 JOIN RDR1 T1 ON T0.DOCENTRY = T1.DOCENTRY LEFT JOIN DLN1 T2 ON T1.DOCENTRY = T2.BASEENTRY AND T1.LINENUM = T2.BASELINE AND T2.BASETYPE = T0.OBJTYPE LEFT JOIN ODLN T3 ON T2.DOCENTRY = T3.DOCENTRY LEFT JOIN OCRD T4 ON T0.CARDCODE = T4.CARDCODE WHERE ISNULL(T0.U_OB_TID,0) <> 0 GROUP BY T4.U_OB_PCOMP, T0.CARDCODE,T0.CARDNAME, T0.U_OB_TID, T0.DOCNUM, T0.DOCDATE, T3.DOCNUM, T3.DOCDATE, T0.DOCTOTAL, T3.DOCTOTAL, T3.TOTALEXPNS, T0.TOTALEXPNS
my question is, how to join the part 1 n part 2? is there posibility?
Are there any problems w/ installing SQL Server 7.0 on a computer with a name such as 05005sql? I know that SQL will take the computer name as the SQL Server name, however, I am concerned about the numeric beginning of the computer name. Any suggestions?
I am using ADO controls for MSSQL Database. The server name on my machine is different from the server name on the machine where i am running my application. When i make changes to my application, i have to rebuild the connection on my ADO Control. Is there a way to create a server name on my developement machine to match the server name where i run my application?
Trying to learn how to use Sql Server CE, Tutorial says to open server explorer, add connection, new connection, select data source as .net sql ce (words to that effect). On my visual studio, it ain't there and I can't figure out how to get it there. I have uninstall all of the Sql Ce stuff and reinstalled it. So I'm missing some key link. What are the magic incantations to get to first base?
Hi I am stuck while doing synchronization between SQL Server 2000 and SQL Server CE 2.0. getting an Error "Header information is either corrupted or missing."
My Specification is follow : Windows XP with SP2 Visual Studio 2005 Enterprise edition SQL Server 2000 (SP3a) SQL Server CE 2.0 Application is Smart Device application (Pocket PC 2003) using C#. Microsoft ActiveSync 4.5 IIS 5.1
I followed steps as per given in this URL : http://msdn2.microsoft.com/en-us/library/ms839425.aspx#sql_serverce_replication_net_topic2I performed eeach steps successfully, My code is as per given below.
I am not getting whats going wrong. My Firewall is disable and i have not installed any antivirus softwares (including Norton internet Security....in many forums i got this suggestion but i think its not an issue,even though for solving this issue i performed new installation of Windows XP SP-2 )
I am building an SSIS package that is hitting about 50 remote servers. I have a for each loop container that i am using to build the list of servers to run.
The problem is...sometimes networks go down, sometimes connections time out and this causes my entire package to fail. I inserted a task that will run select @@ServerName on the remote server inside the loop. My thought was, if this returns an error I could ignore it and move on to the next server in the list and log somewhere that server x threw an error. Logging is easy, the problem is, how do I then tell it to move on to the next server in the loop?
If anyone has any suggestions on how better to do this, I am open for alternatives...
hi... can someone pls tell me the sql statement to 1)Select all the tables in SQL server(only the ones created by me) 2)Select all the column names from a given table
2 node active/passive Microsoft cluster running SQL 2005 Enterprise Ed w/ SP2. The SQL setup is configured as a named instance, servernameinstancename. When I tried to install SCOM 2007, I'm getting this error, "Could not get SQL server instances names." What could be the cause of this error? Any help would be appreciated!!
I changed the name of a server that runs SQL Server 2005 and Reporting Services web site. I can't connect back to reporting services now using the new server name. I had to do some things to get SQL Server and Analysis Services to work, but I can't find any information on getting Reporting Services to run, I found something about changing config files, but no direction on what those files are.
how to retrieve names of the users between start time and end time using stored procedure in sql server??
i have a table which stores user names along with other details. can anybody tell me how to get the names of the users between start time and end time??
ho w do i get all the database names from my local machine which are present in the sql sevrer 2005 i also need how do i get the names of all the tables,views,stored procedure names in the particular database
Hi. I have a cursor in my SQL Server 2000 usp that doesn't seem to be working. It returns 0's for all counts within the cursor. I assigned a value to one of the variables after the cursor and the assigned value came back to my asp.net code. This leads me to believe the cursor variables lose their value once the cursor is done. What am I doing wrong?
-------------------------------------- DECLARE @numFPrintNA int DECLARE @txtPrintSuit varchar(1)
DECLARE curs_Count CURSOR FOR SELECT numFPrintNA, txtPrintSuit FROM tblRecords WHERE txtLocationCode = @txtLocationCode AND (dtmOffDate >= @dtmFrom AND dtmOffDate <= @dtmTo)
How do I create a "Cursor" in SQL Server 7.0 that compares an imported table against a exsisting table? Ex. Table1 is my existing table(Destination), Table2 is my imported table(source). I would like to update records in the Destination from the Source but I have to be aware of these three scenarios.
1. If the record exist in the Destination and the Source I will do nothing. 2. If the record exist in the Source and not in the Destination then I will add the record to the destination. 3. If the record exist in the Destination and not in the Source then I will write to the transaction Log.
Note: I am only concerned about updating one column in Destination which matches the only column in the source.
Cursor to linked server: ----------------------------------------------- Declare Cursor_Loop_serverName Cursor for select cast(name as varchar(30)) name, cast(dbID as varchar(5)) dbID, cast(crdate as varchar(25)) crdate from ServerName_A.master.dbo.sysdatabases
***How could I pass @serverName to change the from to from @RemoteServer.master.dbo.sysdatabases? I have tried dynamic sql, it did not work after the Declare Cursor for... ERROR: Server: Msg 154, Level 15, State 3, Line 48 variable assignment is not allowed in a cursor declaration.
I am not fimilar with cursors at all but need some assistance/direction on how to re-create the following.
The result set will need to give me a list of new customers with sign dates of last year and this current year plus their sales for each year. If the customer has a date signed of 2007 I need to see the "year of sales" for both 2007 and 2008, whether or not they actually had sales.
I'm needing to convert an oracle cursor to Sql Server 2005. If at all possible I would like to stay away from the cursor and create this some other way. Any help would be greatly appreciated.
Customer Date Signed Year of Sale Amount 1111 7/1/07 2007 $50,000.0 2008 0.0
Below is the oracle cursor that was used to calculate this information in the past.
DECLARE MBLDRN BAV_BUILDER_NEW.BLDRN%TYPE; MYR_SIGNED BAV_BUILDER_NEW.YEAR%TYPE; MCOMPANY BAV_BUILDER_NEW.COMPANY%TYPE; MSHORTNAME BAV_BUILDER_NEW.SHORTNAME%TYPE; MDSIGNED BAV_BUILDER_NEW.DSIGNED%TYPE; MDCANCELED BAV_BUILDER_NEW.DCANCELED%TYPE; MDMTERR BAV_BUILDER_NEW.DMTERR%TYPE; MDMNAME BAV_BUILDER_NEW.DMNAME%TYPE; MENGR_TEAM BAV_BUILDER_NEW.ENGR_TEAM%TYPE; MSALESREGON BAV_BUILDER_NEW.SALESREGON%TYPE; MDIVCODE BAV_BUILDER_NEW.DIVCODE%TYPE; MYRLOOP NUMBER; MLSTYR NUMBER; MSYEAR DATE; MEYEAR DATE; CURSOR CA IS SELECT YEAR,BLDRN,COMPANY,SHORTNAME,DSIGNED,DCANCELED, DMTERR,DMNAME,ENGR_TEAM,SALESREGON,DIVCODE FROM BAV_BUILDER_NEW ORDER BY YEAR; BEGIN SELECT YEAR INTO MLSTYR FROM CURRENT_DATES; OPEN CA; LOOP FETCH CA INTO MYR_SIGNED,MBLDRN,MCOMPANY,MSHORTNAME,MDSIGNED,MDCANCELED, MDMTERR,MDMNAME,MENGR_TEAM,MSALESREGON,MDIVCODE; EXIT WHEN CA%NOTFOUND; MYRLOOP := MYR_SIGNED; WHILE MYRLOOP <= MLSTYR LOOP SELECT MIN(SYEAR) INTO MSYEAR FROM UDBDATES WHERE YEAR=MYRLOOP; SELECT MAX(EYEAR) INTO MEYEAR FROM UDBDATES WHERE YEAR=MYRLOOP; BEGIN INSERT INTO BAV_BUILDER_NEW_DATA (YR_SIGNED,DIVCODE,SALESREGON, BLDRN,COMPANY,SHORTNAME,DSIGNED,DCANCELED,ENGR_TEAM, DMTERR,DMNAME,YR_SALES) VALUES (MYR_SIGNED,MDIVCODE,MSALESREGON,MBLDRN,MCOMPANY,MSHORTNAME, MDSIGNED,MDCANCELED,MENGR_TEAM,MDMTERR, MDMNAME,MYRLOOP); END; MYRLOOP := MYRLOOP + 1; END LOOP; COMMIT WORK; END LOOP; COMMIT WORK; CLOSE CA; END; / COMMIT WORK;
I've recently upgraded my PC from ME to XP and now have a multi-user setting in my computer. I've tried reinstalling SQL 7; it did install but the Server Manager is missing, so I couldn't start the server. (By the way, what I have is the stand-alone type). Whenever I try to start the query analyzer, I get this error message: [Microsoft][ODBC SQL Server Driver][Shared Memory]SQL Server does not exist or access denied. Whenever I try looking for the server manager in the start-up menu, it always says a .exe file (if I'm not mistaken the sqlmangr.exe) is missing or has been moved. What could be the problem? I'm just beginning to learn SQL so if anybody can help me, please.
