Dynamic Managemenr View Reset Required
Oct 26, 2007
Hi,
I have recently had to optimise a site. The DMVs are useful in establihing which queries are taking the longest to run but the data is skewed as it takes into account all queries than have run ever. This is not as useful as profiling a site which in fact I used.
Is there a way to clear all the data returned by the DMVs. This would be useful as all the data could be cleared and then I could just check the DMV every week.
Thanks in advance
James
View 1 Replies
ADVERTISEMENT
May 22, 2015
I have a table sorted by vendor, then item, then Status date with a QC Pass date and Fail date.
I need a SQL 2008 view that counts how many consecutive times a Vendor/Item has passed QC.
When it fails, the count resets to zero, then begins incrementing again.
I need to know how to generate the last column (Count).
I have tried using a ROW_Number() OVER(Partition BY, Order By...) command in the view, but I cannot seem to make it work right.
VendorItemStatusDatePassDate Faildate Count
10056322010-05-092010-05-091
10056322012-12-152012-12-152
10056322013-05-252013-05-253
10056322014-11-172014-11-174
[Code] ....
View 5 Replies
View Related
May 7, 2008
I got a situation in which I would like to create a Views which is using my Test database (Could use more then one test database), but when I test It I would like to create using my Production database (Which is different Database name). Is there any way so that I can pass a parameter at run time which hold a Database name and create those views using that parameter.
Any help really appreciated
Thanks in advance
View 2 Replies
View Related
Oct 9, 2007
I am using dynamic sql in my report. I cannot see the sql the report is generating in sql profiler. Is there somewhere else I can view it?
Thanks,
Linda
View 3 Replies
View Related
Oct 11, 2007
I have a procedure that creates a large dynamic view of several tables. The view is a union view of up to 15 tables. The table names are all <name>_DDMM where name is the standard table name and ddmm is the day and month of the tables data. The tables are created by a software supplied by another company, so I can not ensure that the tables will always have exactly the same fields or number of fields. Sometimes the company will add more fields to the tables in thier updates. So, I have to include the field names in the SQL exec command to create the query. This makes for a very long exec command and depending on the number of tables it needs to include, it can require upwards of a 16,000 character string. Obviously, this can't work, so I had to break up the variable in order to create the procedure. However, I'm wondering if there isn't a better method than creating three different 8000 varchar variables and having overflow write to the next variable in line. Especially if the number of tables needs to be expanded, it could be a problem. Is there a better way to run a create view exec command on a large number of characters?
EDIT: Changed the title to read Procedurally generating a large view.
View 14 Replies
View Related
Jan 23, 2008
I'm very new to SQL Server. Please help. I need to create a FUNCTION that creates a view. Then call this function in a SQL which is passed as a parameter to BCP. In Oracle, it would be something like:
create function CREATEVIEW
return number as
begin
create view SampleView as SELECT a,b,c from Mytable;
return 1;
when others then return 0; -- for exception handling
end:
create function DROPVIEW
return number as
begin
Drop view SampleView;
return 1;
when others then return 0; -- for exception handling
end:
Then my BCP will have something like:
BCP "select CREATEVIEW from dual"... QUERYOUT ..
then
BCP "select * from SampleView"... QUERYOUT ..
then drop the view again:
BCP "select DROPVIEW from dual"... QUERYOUT ..
I know there is no DUAL table in SQL SERVER. I just want to know how to code this in SQL Server.
The origin of my problem is that my SQL statement is too long to fit as BCP parameter, hence, am creating a view and reading there and dropping it again. If you can provide me with a better workaround, that would be great.
Thanks in advance.
View 7 Replies
View Related
May 8, 2012
Basically, I'm working on a stored procedure which will retrieve data based on study parameter passed. The datasource is 'Views'. The name of the view is same for every study except that there is corresponding study name included. For example the views names are something like this for study abc 'v_abc_form' and for study def 'v_def_form'.
Below is the select statement I'm trying to use by declaring @study variable but not able to succeed. I'm not sure how to make the table name dynamic.
Select C1, C2, C3
From v_@study_form
View 7 Replies
View Related
May 29, 2008
I would like to use a dynamic pivot table in my data source view. It seems that a named query can be only one sql statement. So, I cannot use my multi-statement procedure that creates a dynamic pivot table output.
What is the best course of action here? I could hard-code my pivot table query. I could maintain a redundant table in the pivot format. Do I have any good options?
KenS
Ken
View 1 Replies
View Related
Jul 20, 2005
I am trying to create a dynamic SQL statement to create a view.I have a stored procedure, which based on the parameters passed callsdifferent stored procedures. Each of this sub stored procedure createsa string of custom SQL statement and returns this string back to themain stored procedure.This SQL statements work fine on there own. The SQL returned from thesub stored procedure are returned fine. The datatype of the variablethat this sql is stored in Varchar(I have tried using nvarchar alsosame problem).If I have more that 6 SQL statements concated then the main SQL getscut off. It doesnt matter in what sequence I create the main SQL.Here is the Stored procedure/**********************************************//*Main Stored Procedure *//**********************************************/CREATE PROC sp_generate_invoice1 @prev_date NVarchar(1000) ,@prev_month NVarchar(32)ASDECLARE invoice_driver_cur CURSOR FORSelect driversid From Invoice_driversOpen invoice_driver_curDeclare@C VARCHAR(8000),@L_args Varchar(8000),@@sqlstmt Varchar(8000),@L_driverid Int,@L_rowcount IntSET QUOTED_IDENTIFIER ONSET TEXTSIZE 32768Set @L_rowcount = 0-- Drop the previous ViewIF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWSWHERE TABLE_NAME = 'custom_invoice')DROP VIEW custom_invoiceFetch Next From invoice_driver_curInto @L_driverid-- Create the new ViewSet @L_args = N'Create View custom_invoice As'--Select @L_driveridWHILE( @@FETCH_STATUS = 0)BEGINSet @L_rowcount = @L_rowcount + 1select @L_driveridIf @L_driverid = 2BeginExec sp_invoice_driver2 @prev_date, @prev_month, @@sqlstmt OutputIf @L_rowcount > 1BeginSet @C = @L_args + ' Union ' + @@sqlstmtEndElseBeginSet @C = @L_args + @@sqlstmtEndEndIf @L_driverid = 3BeginExec sp_invoice_driver3 @prev_date, @prev_month, @@sqlstmt OutputIf @L_rowcount > 1BeginSet @C = @C + ' Union ' + @@sqlstmtEndElseBeginSet @C = @L_args + @@sqlstmtEndEndIf @L_driverid = 4BeginExec sp_invoice_driver4 @prev_date, @prev_month, @@sqlstmt OutputIf @L_rowcount > 1BeginSet @C = @C + ' Union ' + @@sqlstmtEndElseBeginSet @C = @L_args + @@sqlstmtEndEndIf @L_driverid = 5BeginExec sp_invoice_driver5 @prev_date, @prev_month, @@sqlstmt OutputIf @L_rowcount > 1BeginSet @C = @C + ' Union ' + @@sqlstmtEndElseBeginSet @C = @L_args + @@sqlstmtEndEndIf @L_driverid = 6BeginExec sp_invoice_driver6 @prev_date, @prev_month, @@sqlstmt OutputIf @L_rowcount > 1BeginSet @C = @C + ' Union ' + @@sqlstmtEndElseBeginSet @C = @L_args + @@sqlstmtEndEndIf @L_driverid = 7BeginExec sp_invoice_driver7 @prev_date, @prev_month, @@sqlstmt OutputIf @L_rowcount > 1BeginSet @C = @C + ' Union ' + @@sqlstmtEndElseBeginSet @C = @L_args + @@sqlstmtEndEndIf @L_driverid = 8BeginExec sp_invoice_driver8 @prev_date, @prev_month, @@sqlstmt OutputIf @L_rowcount > 1BeginSet @C = @C + ' Union ' + @@sqlstmtEndElseBeginSet @C = @L_args + @@sqlstmtEndEndIf @L_driverid = 10BeginExec sp_invoice_driver_niku @prev_date, @prev_month, @L_driverid,@@sqlstmt OutputIf @L_rowcount > 1BeginSet @C = @C + ' Union ' + @@sqlstmtEndElseBeginSet @C = @L_args + @@sqlstmtEndEndPrint @CFetch Next From invoice_driver_curInto @L_driveridContinueEndClose invoice_driver_curDeAllocate invoice_driver_curExec (@C)--EXEC sp_executesql @CGO/**********************************************//*Sub Procedure sp_invoice_driver2 *//**********************************************/CREATE PROC sp_invoice_driver2 @args NVarchar(1000), @prev_monthNVarchar(100),@sqlstmt Varchar(8000) OutputASSET QUOTED_IDENTIFIER ONSET @sqlstmt = ' Select 1 SortOrder ,( SELECT Drivers.Description) Description,(BillingReport.Active_Accounts * Cast(Fee.fee_rate As decimal(4,2))) / 12 Amount,Drivers.CurrencyFROM BillingReport, Drivers, FeeWHERE ( Fee.Driverid = Drivers.Driversid ) andDrivers.Driversid = 2 andbillingreport.fromdate = ''' + Cast(@args As NVARCHAR(20)) + '''andfee.currentmonth = ''' + Cast(@prev_month As NVARCHAR(12)) +' '''GO/**********************************************/This is what the Print Statement give:/**********************************************/Create View custom_invoice As Select 1 SortOrder ,( SELECT Drivers.Description) Description,(BillingReport.Active_Accounts * Cast(Fee.fee_rate As decimal(4,2))) / 12 Amount,Drivers.CurrencyFROM BillingReport, Drivers, FeeWHERE ( Fee.Driverid = Drivers.Driversid ) andDrivers.Driversid = 2 andbillingreport.fromdate = '9/1/2004' andfee.currentmonth = 'September ' Union Select 2,(SELECT Drivers.Description),(BillingReport.Zero_Balance * Cast(Fee.fee_rate As decimal(9,2) ))/ 12 Amount,Drivers.CurrencyFROM BillingReport, Drivers, FeeWHERE ( Fee.Driverid = Drivers.Driversid ) andbillingreport.fromdate = '9/1/2004' andfee.currentmonth = 'September' andDrivers.Driversid = 3 Union Select 3,(Select Drivers.Description From Drivers Where DriversID = 4),Count(*) * Cast((select fee.fee_ratefrom fee, driverswhere Fee.Driverid = Drivers.Driversid andfee.currentmonth = 'September' andDrivers.Driversid = 4 )As decimal(6,2)) / 12,(Select Drivers.CurrencyFrom Drivers Where DriversID = 4)From Fund Union Select 4,(Select Drivers.Description From Drivers Where DriversID = 5),(((Sum(BillingReport.Man_Reg_Purch + BillingReport.Man_Reg_Red +BillingReport.Man_Reg_Transexch +BillingReport.Man_Allo_Purch +BillingReport.Man_Allo_Red +BillingReport.Man_Allo_Transexch +BillingReport.Man_Allo_Adj_Purch +BillingReport.Man_Allo_Adj_Red +BillingReport.Man_Allo_Adj_Transexch +BillingReport.Man_Adj_Purch +BillingReport.Man_Adj_Red +BillingReport.Man_Adj_Transexch ) ) +(Select Sum(Cast(satuscnt As int ))From Awd_stubWhere CurrentMonth = 'September'))) *(Cast((select fee.fee_ratefrom fee, driverswhere Fee.Driverid = Drivers.Driversid andfee.currentmonth = 'September' andDrivers.Driversid = 5 )As decimal(6,2))),(Select Drivers.Currency From Drivers Where DriversID = 5)FROM BillingReportWhere billingreport.fromdate = '9/1/2004' Union Select 5,(Select Drivers.Description From Drivers Where DriversID = 6),( Sum( BillingReport.Auto_Reg_Purch +BillingReport.Auto_Reg_Red +BillingReport.Auto_Reg_TRansexch +BillingReport.Auto_Allo_Purch+BillingReport.Auto_Allo_Red +BillingReport.Auto_Allo_Transexch+BillingReport.Auto_Allo_Adj_Purch+BillingReport.Auto_Allo_Adj_Red+BillingReport.Auto_Allo_Adj_transexch+BillingReport.Auto_Adj_Purch+BillingReport.Auto_Adj_Red+BillingReport.Auto_Adj_Transexch )+(Select Sum(Cast(Processed_msg As Int))From XML_messagingWhere CurrentMonth = 'September'))*(Cast((select fee.fee_ratefrom fee, driverswhere Fee.Driverid = Drivers.Driversid andfee.currentmonth = 'September' andDrivers.Driversid = 6 )As decimal(6,2))),(Select Drivers.Currency From Drivers Where DriversID = 6)FROM BillingReportWhere billingreport.fromdate = '9/1/2004' Union Select 6,(Select Drivers.Description From Drivers Where DriversID = 7),( ( a.Accountholder_Active_Accounts -(select accountholder_active_accounts from billingreport whereMonth(fromdate) = Month('9/1/2004')-1 ) )+( a.Accountholder_Zero_Balance -(select accountholder_zero_balance from billingreport whereMonth(fromdate) = Month('9/1/2004')-1 ))) *(Cast((select fee.fee_ratefrom fee, driverswhere Fee.Driverid = Drivers.Driversid andfee.currentmonth = 'September' andDrivers.Driversid = 7 )As decimal(6,2))),(Select Drivers.Currency From Drivers Where DriversID = 7)FROM BillingReport a Where a.fromdate = '9/1/2004' Union Select 7,(Select Drivers.Description From Drivers Where DriversID = 8),( Select telephone From cfxbill Where currentmonth = 'September') *(Cast((select feThanks for any helpMD
View 4 Replies
View Related
Jul 22, 2014
I have a view created from only one table.
VW_Stats ( Tab_Name,Load_Status,User,....)
Tab_Name & Load_Status columns provides the information about Name of table and status of data getting loaded to this table.
To this I would like to add a column to display the running count in the table, I mean how many records so far loaded, as below the recordCount coming from the same table tbl_collection
Table_name Load_Status RecordCount User...
tbl_collection Running 1244 XYZ
View 7 Replies
View Related
Sep 17, 2007
Hi Friends,
I have used some dynamic parameter of type string and integer.
When I hit my detail report from my menu report(Where I pass the values for these parameters from menu to detail). The Detail Report comes fine.
But when I click view report button of my detail report the vaues for all these parameters are set to default value.
So the parameters are not able to retain their pass value from the menu report.
This thing is happening when I m hiting the published report but the reports are working fine in Report designer.
Please help me.
Thanks
Novin
View 3 Replies
View Related
May 29, 2015
I have written a script to pivot a table into multiple columns.
The script works when run on its own but gives an error when i try to create a view or aprocedure from the same script. The temporary table #.... does not work so i have converted it to a cte.
Here is a copy of the script below
-- Dynamic PIVOT
IF OBJECT_ID('#External_Referrals') IS NULL
DROP TABLE #External_Referrals;
GO
DECLARE @T AS TABLE(y INT NOT NULL PRIMARY KEY);
[Code] ....
View 7 Replies
View Related
Nov 27, 2007
I have a table in my db that has an identity column as the primary key. I have deleted all the data and have tried to use truncate table on it to reset the identity column. The table has a number of foreign key constraints so it will not truncate.
Is there any way to reset the indentity column without removing the constraints?
View 1 Replies
View Related
Dec 10, 2007
My UDF
FUNCTION [dbo].[fn_concat_boxes](@item varchar(10), @week int, @type char(1))
RETURNS VARCHAR(100)
AS
BEGIN
DECLARE @Output varchar(100)
SELECT @Output = COALESCE(@Output + '/', '') +
CAST(quantity AS varchar(5))
FROM flexing_stock_transactions
WHERE item = @item AND week = @week AND firm_or_commission = @type
GROUP BY quantity
ORDER BY quantity
RETURN @Output
END
gives the same output every time it is called, despite different parameters passed. @Output does not change as expected.
Any ideas guys?
View 16 Replies
View Related
Nov 27, 2006
Hi,
consider my table,
s.no priority status
1 2 pending
2 3 pending
3 0 completed
4 1 pending
5 0 completed
Now, if I insert a record as,
6 1 pending
the priority must be reset as follows,
s.no priority status
1 3 pending
2 4 pending
3 0 completed
4 2 pending
5 0 completed
6 1 pending
Im using c# and sql server. How can i do it?
Thanks,
Jasmeeta.
View 2 Replies
View Related
Jul 8, 2007
I have a few SQL tables that use an auto incrementing integer key field, ie it has 'is identify' set to yes
The tables have been used for testing while the application was developed.
I plan to delete all data from these tables when the application goes live. Is there a way to start SQL counting from 1 again without deleting and re-creating thr tables?
View 2 Replies
View Related
Jul 17, 2007
Hi Forum, I have a .mdf that I have used to test SQL data app. I want to reuse the .mdf; delete all added data and reset Customer_ID primary colomn back to 1. All good info appreciated thanks Paul
View 1 Replies
View Related
Apr 18, 2008
I have a SQL data source triggered with this code: string searchstring = TextBox2.Text;
SqlDataSource1.SelectCommand = "mod_search_all_sel";
SqlDataSource1.SelectParameters.Add("search_str", searchstring);
SqlDataSource1.SelectCommandType = SqlDataSourceCommandType.StoredProcedure; The second time this fires however, the SelectParameters.Add function creates another paramter and the stored procedure blows up. How do I do a reset like in old Classic ASP where you would set the DS = Nothing?
View 2 Replies
View Related
Jun 16, 2008
I have a table where I delete all the records, then reload. can I reset the identity to (1,1) Is there a SQL command for that?
-smcirish
View 3 Replies
View Related
Mar 11, 2004
In my application , DB has a large table. I write a small program to clear the table whenever the size of table is over 50 MB. At that time , I want to reset identity as 1. How can I do that?
Currently , my program delete old table and generate a new one with the same schema when the table is too large.But this is kind of ugly.
View 1 Replies
View Related
Nov 1, 2000
Can I reset the Auto_ID column in a table to start from 1 again?
Thanks
View 1 Replies
View Related
Apr 7, 2003
How do reset identity seed on table, for example I have table that has gap in the identity such as (1,2,3,5,6, etc..) and I want reset the entire table.
Thank You,
John
View 2 Replies
View Related
Jun 27, 2001
Can a column of Identity type reset after it reaches its maximum value?
Thanks,
Ben
View 4 Replies
View Related
May 15, 2006
This morning I made a change to two tables in my live system. All I did was add a new column in each called 'suspense' with a data type of bit and a default value of '0'.
When the users started using the system they noticed that the record ID numbers had reset and were starting from 1 and going up 2, 3, 4 etc. The last good id number was about 20500. The id columns are set as primary key with identity and increment of 1.
This shouldn't happen should it? Is it a bug? I never touched the ID column, just added the new one.
View 2 Replies
View Related
Aug 9, 2006
I have a DTS package that deletes all the records in a table and repopulates from an ACCESS table. All's well except the primary key of the target table doesn't go back to 1.
What's the syntax in T-SQL to accomplish that?
I imagine it's dropping the column and adding it back - but since it's a primary key field, regular drop/add syntax isn't working for me.
Any help here is appreciated.
Thanks,
Peter
View 4 Replies
View Related
Mar 23, 2007
Hi all
I have build an application on top of SQL Server 2005 and i am using microsoft driver. My application stops periodically becoz of Database Connection Loss and I get the following exception
com.microsoft.sqlserver.jdbc.SQLServerException: An exception occurred during the DBComms.transmit operation. Exception:Socket closed. Context:(5) [Thread[Thread-55,5,main], IO:7e2dc, Dbc:null].
10:06:14:703 AM at com.microsoft.sqlserver.jdbc.SQLServerException.ma keFromDriverError(Unknown Source)
10:06:14:703 AM at com.microsoft.sqlserver.jdbc.DBComms.transmit(Unkn own Source)
10:06:14:703 AM at com.microsoft.sqlserver.jdbc.IOBuffer.sendCommand( Unknown Source)
10:06:14:703 AM at com.microsoft.sqlserver.jdbc.SQLServerStatement.se ndExecute(Unknown Source)
10:06:14:703 AM at com.microsoft.sqlserver.jdbc.SQLServerStatement.do ExecuteQuery(Unknown Source)
10:06:14:703 AM at com.microsoft.sqlserver.jdbc.SQLServerPreparedStat ement.executeQuery(Unknown Source)
Unable to find the exact reason for the above problem. Pl advice
Thanks & Rgds
Roshini
View 1 Replies
View Related
Jul 12, 2012
I'm trying to create a trigger that will clear the "checked" field when all items with the same classification have been checked.
The purpose of this is to do cycle counting of inventory, which is when you count the stock levels of a proportion of stock that have the same classification.
The checked field will be used to record the date it was lasted checked and will cleared once all stock have counted, putting back into the pool of stock to be counted.
Therefore, the trigger is suppose to count the number of rows that share the same classification as the amended row, and compare that with the number of rows in that classification, that have a date in the checked date field. When the two values are the same, reset all the checked dates to blank.
But when I tested it, nothing happened ...
ALTER TRIGGER [dbo].[UDEF_Last_Checked_Reset] ON [dbo].[ASC_PMA_TBL]
AFTER INSERT
AS
BEGIN
DECLARE @PartOnly varchar(16) -- Part Number
DECLARE @Rev varchar(4) -- Part Revision
DECLARE @PartCode as varchar(2) -- Part Classification
DECLARE @Last_Checked as datetime -- Checked Date
[Code]...
View 1 Replies
View Related
Oct 6, 2007
Hello All:
i am inserting a counter from a procedure based on the year/month.
I would like to reset the counter to 1 every month.
example of data:
count year/month
_______________
16200711
17200711
18200711
19200711
20200711
21200712 this counter should be reset to 1
22200712
23200712
MY PROCEDURE
____________
DECLARE @i INT
SET @i = 1
DECLARE @DateID INT,
@YMN CHAR(6)
DECLARE c CURSOR
FOR
SELECT DateID,YMN FROM TEST
WHERE WkDayIn = 'Yes' AND HolidIn = 'No'
OPEN c
FETCH NEXT FROM c INTO @DateID,@YMN
WHILE @@FETCH_STATUS = 0
BEGIN
UPDATE TEST
SET BusDay = @i
WHERE DateID = @DateID
SET @i = @i + 1
FETCH NEXT FROM c INTO @DateID,@YMN
END
CLOSE c
DEALLOCATE c
Thank You
F.
View 4 Replies
View Related
Nov 9, 2007
Hello everyone:
this procedure resets the business day to one for every month. It works fine except for the month of October. any idea or suggestions?
DECLARE @i INT
SET @i = 1
DECLARE @DateID INT,
@DtTimeD DATETIME,
@LASTDAY DATETIME
DECLARE c CURSOR
FOR
--
-- LASTDAY is the last day of the month
-- the counter will reset to 1 on the first of each month
--
SELECT DateID, DtTimeD, DATEADD(dd, -DAY(DATEADD(m,1,DtTimeD)), DATEADD(m,1,DtTimeD)) 'LASTDAY'
FROM D_DATE
WHERE WkDayIn = 'Yes' AND HolidIn = 'No'
OPEN c
FETCH NEXT FROM c INTO @DateID, @DtTimeD, @LASTDAY
WHILE @@FETCH_STATUS = 0
--
-- update the business day in D_DATE
--
BEGIN
UPDATE D_DATE
SET BusDay = @i
WHERE DateID = @DateID
--
-- reset the counter to 1 if it's the last day of the month
--
IF @DtTimeD = @LASTDAY
SET @i = 1
ELSE
SET @i = @i + 1
--
IF @@ROWCOUNT = 500
COMMIT
--
FETCH NEXT FROM c INTO @DateID, @DtTimeD, @LASTDAY
END
CLOSE c
DEALLOCATE c
GO
View 10 Replies
View Related
Dec 3, 2007
Hi experts, I would like to ask if there is a way to reset the SA password of SQL 2005 without having to know the current password?
Im using SQL 2005 Express
Thanks!
View 3 Replies
View Related
Dec 22, 2006
I
use Microsoft SQL Server and also I have several tables. One table has
ID like PK, and name. ID use autoincrement number mode, and now when I
want start form one but not from 156 or like that. I don't know how?
And also when I delete all data from table an run my apllication and
add something in table, it starts form last number, not from one.
Maybe can I do it from SQL menagement studio?
How I can reset table and start from 1?
View 5 Replies
View Related
Jul 4, 2007
We are about the deploy our project. We made many testing such as Random data insertion etc. Some tables have autoincrement column, and their numbers reached 3,000,000. After testing, we delete all data in database. Now question is how can we truncate all tables, and make fresh start. We may use TRUNCATE query but it is boring and giving errors some time. Is there way to reset whole database, and make a fresh start, like no data entered before.????
Thanks for help, Happy Coding
View 7 Replies
View Related
Aug 9, 2007
Sorry if this is a dumb question, but I am new to SQL Server and trying a simple cursor. Works fine the first time I execute, but then @@Fetch_Status stays at -1 and it will not work again. If I disconnect and then connect it will work once again. What I am doing wrong?
declare @entityid int, @datafrom nvarchar (255),
@datato nvarchar (255), @updatedate datetime
declare statustime1 cursor
local scroll static
for
select audit.entityid, audit.datafrom, audit.datato, audit.updatedate
from audit
, orders
where audit.entityid = orders.orderid
and audit.entitytypeid = 4
order by orders.orderid, audit.updatedate desc
open statustime1
While @@fetch_status = 0
Begin
fetch next from statustime1
into @entityid, @datafrom, @datato, @updatedate
print @entityid
end
close statustime1
deallocate statustime1
go
View 13 Replies
View Related
