Hi, here is my problem and hopefully somebody has an idea. What i am working on is an order tracking system. i insert the orders into sql then when the orders come in i check for the oldest order and apply the qty we recieved to that order...pretty simple. The problem is that some times we have 5 orders for the week and we then recieve them as one order so there will be 5 orders to fill. I can do this for 2 orders and was thinking about using a cursor to so this instead of a really drawn out procedure but am not sure how that would work if it would at all. Does anybody have any ideas?
Does anybody can tell me how to check if cursor exists ? I have a "try and catch" blocks. I want to destroy the cursor when error occurred and it jumps into "catch" block. Before I destroy the cursor I want to check if it exists, because the error could occurred before I have declared or opened the cursor.
I declare a cusror named crInv inside a loop. Since I open this cursor a lot of times I want to check if is already opened. I try to use the Cursor_status function but it always returns -3. The syntax is:
DECLARE crInv SCROL CURSOR FOR SELECT Val1, Val2 FROM TABLE1 WHERE Val3=450
If Cursor_Status('local','crInv')>0 BEGIN CLOSE crInv DEALLOCATE crInv END
This code is inside a loop. If I PRINT Cursor_Status('local','crInv') before and after the DECLARE statement it always returns -3. What is wrong??
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.
I wrote a stored procedure for SqlServer 2000 and i am using it for paging purpose. The procedure is as follows :
set ANSI_NULLS ON set QUOTED_IDENTIFIER ON go ALTER PROCEDURE [dbo].[OLAP_PagedRows] ( @SelectFields nVarchar(2000) =NULL,
@GroupByFields nvarchar(1000) =NULL,
@BaseTable varchar(100),
@KeyColumn nvarchar(200)=NULL ,
@JoinTables varchar(500) =NULL,
@ConditionalClause varchar(1000) =NULL,
@Pagesize int = 10,
@PageNumber int =1,
@SortExpression nvarchar(200)=NULL,
@SearchText nvarchar(200)=NULL
)
AS
BEGIN
DECLARE @SQLSTMT NVarchar(4000)
DECLARE @SQLSTMT1 NVarchar(4000)
SET @SQLSTMT1 = ''
--check whether page size is given null or not, if so set to default value
IF @Pagesize IS NULL OR @Pagesize = ''
BEGIN
SET @Pagesize =10
END
--check whether page number is given null or not, if so set to default value
IF @PageNumber IS NULL OR @PageNumber = ''
BEGIN
SET @PageNumber =1
END
--Start constructing the query --
SET @SQLSTMT = 'SELECT '
SET @SQLSTMT1 = 'DECLARE @CountValue INT SELECT @CountValue = count(*) From '+@BaseTable
SET @SQLSTMT = @SQLSTMT + @SelectFields + ' FROM '+@BaseTable
If @JoinTables Is Not Null
BEGIN
SET @SQLSTMT = @SQLSTMT + ' ' +@JoinTables
SET @SQLSTMT1 = @SQLSTMT1 + ' ' +@JoinTables
END
DECLARE @StmtWhereClause nvarchar(500)
SET @StmtWhereClause =''
--------------------- Get where conditional clause
If (@SearchText Is Not Null AND RTRIM(LTRIM(@SearchText))<>'')
BEGIN
SET @StmtWhereClause = @StmtWhereClause + ' WHERE ' + @SearchText
END
If @ConditionalClause Is Not Null AND RTRIM(LTRIM(@ConditionalClause))<>''
BEGIN
IF (@StmtWhereClause <> '')
BEGIN
SET @StmtWhereClause= @StmtWhereClause + 'AND ' +@ConditionalClause
END
ELSE
BEGIN
SET @StmtWhereClause = @StmtWhereClause + ' WHERE ' + @ConditionalClause
END
END
SET @SQLSTMT = @SQLSTMT + @StmtWhereClause
SET @SQLSTMT1 = @SQLSTMT1 + @StmtWhereClause
If @GroupByFields Is Not Null And RTRIM(LTRIM(@GroupByFields))<>''
BEGIN
SET @SQLSTMT = @SQLSTMT + ' Group By ' +@GroupByFields
SET @SQLSTMT1 = @SQLSTMT1 + ' Group By ' +@GroupByFields
END
IF @SortExpression Is Not Null AND RTRIM(LTRIM(@SortExpression))<>''
BEGIN
SET @SortExpression = LTRIM(RTRIM(' Order By '+ @SortExpression))
SET @SQLSTMT = @SQLSTMT +' '+ @SortExpression
SET @SQLSTMT1 = @SQLSTMT1 +' '+ @SortExpression
END
SET @SQLSTMT1= @SQLSTMT1+' SELECT @CountValue As MyRows '
--SELECT @SQLSTMT1
--SELECT @SQLSTMT
DECLARE @StartRow INT
SET @SQLSTMT = ' DECLARE temp_Cursor CURSOR SCROLL FOR '+@SQLSTMT
EXECUTE SP_EXECUTESQL @SQLSTMT
Open temp_Cursor
DECLARE @RowCount INT
SET @RowCount = 1
SET @startRow = (@PageSize * (@PageNumber-1))+@RowCount
--SELECT @startRow as 'Current Row'
WHILE @RowCount <= @PageSize
BEGIN
--Select @StartRow 'as @StartRow'
FETCH ABSOLUTE @startRow From temp_Cursor
SET @RowCount= @RowCount+1
SET @StartRow = @startRow + 1
END
deallocate temp_Cursor
EXECUTE SP_EXECUTESQL @SQLSTMT1
END
It is working fine but I have problem with this kind of paging. I need to load the whole data into the cursor and i have to fetch records. The problem is that my table's contains more than Half a million records in it. If I have to load each time this cursor it will be a very big problem on the server side.
Probably it may not be a best solution, but sqlserver 2000 cannot provide more help than this. If I use sub-query for this like using Top <Number> it adversly effecting the nature of the data retrieval.
One solution that I am thinking is Load cursor once and whenever some updations performed on those tables from which cursor is getting data should be automatically reflect the changes.
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
I defined a stored procedure with a cursor inside for updating data. When I call it from an MSAccess client, it fails. When I execute it directly in a ISQL/w windows, it doesn`t fail but it displays me the data (wich is the reason for failing from MSAccess). Do somebody know if I could do it without displaying data in the screen ??
Hi Guys, Need your help on this problem. Let say table name call tbl_range and 2 field call No1 and No2 I have this set of record :- No1 No2 1000 2000 2001 3000 5000 6000
My problem, i want to check if user insert another set no No1 : 1500 No2 : 2500. So, this means that range already clash with another range. If this happened it will return 2 record (1000 - 2000) and (2001 - 3000). Can it be done and how?
Hello Everybody!I have a POLINE table on a SQL Server 2000 DB. Before I update therecord I need to check that either field, STORELOC or WONUM has dataon it. If both fields are NULL I would like to send a message lettingthe user know that either fields needs data before they can save therecord. If any of the fields have data then, it is OK to save therecord.Could you please let me know how to accomplish this? An example willbe really helpful, I can do this in Access but I do not know how to doit in SQLServer. I was thinking using trigger but there are not reallygood examples.Thanks in Advance!Martin
I new here so hopefully I'm asking this question in the correct forum. I'm have a flat file that contains numbers that I need to verify that they begin with certain prefixes so they load to the correct client. For example in the flat file if I'm loading data for client A and their account numbers begin with 045XXXXXXX then it loads the data. But if there is a record that begins with 037XXXXXXX it should be loaded to client B instead and that records gets written to a error file.
So to summarize what I need , I'm looking for a check to kick out records if I'm loading client A's data versus if I'm loading client B's data.
Hi all! I need to check data changes in some tables from specified date. Can it be done without triggers for each table? SQL Server Management Studio always says "Data was changed" if another user updates data and you try to update old version. How it checks data modification date? I found only this:
USE [ScheduleDB]
GO
SELECT * FROM sys.objects WHERE object_id = OBJECT_ID('[dbo].[TTest]') AND type in ('U')
I have an SSIS pkg that automatically downloads financial extracts from an ftp site. Once the files are downloaded, I load the extract to a table. The table is first deleted before the insert, so that each time the table has "fresh" data (whatever was in the extract for that day).
Once the extract data is in the table, I load the data into yet another table that combines data from many tables. Simple enough.
Problem is, sometimes when I download the extract, it hasn't been updated yet, so I'm downloading an OLD extract. This old data then gets loaded into the first table. That's ok, because it doesn't really hurt anything. I can always delete the table and reload it if necessary.
The problem occurs when the old data goes from this table into the OTHER table. We don't want old data in this other table!
I need a way to check that I'm not loading the same data 2 days in a row into the OTHER table.
I realize that I might be able to solve this problem without using SSIS, but the solutions I've come up with so far aren't 100% satisfactory. I can use a query to check dates and that sort of thing, but it isn't foolproof, and would create problems if I need to manually force the process though, that is, if I need to override the date logic.
Anyways, I'm wondering if there's an SSIS approach to this problem... I can't rely on timestamps on the data files either. They're not accurate.
I was able to get this code to work but now I get a SQL error if you try to submit the same information twice. How can I add a message saying that the "email" already exists in database without the SQL error? protected void Button1_Click1(object sender, EventArgs e) { SqlConnection conn = new SqlConnection("Data Source=TECATE;Initial Catalog=subscribe_mainSQL; User Id=maindb Password=123456; Integrated Security=SSPI"); SqlCommand cmd = new SqlCommand("INSERT INTO [main] (, [userid], [fname], [lname], [degree]) VALUES (@email, @userid, @fname, @lname, @degree)", conn); conn.Open(); cmd.Parameters.AddWithValue("@email", email.Text); cmd.Parameters.AddWithValue("@userid", uscid.Text); cmd.Parameters.AddWithValue("@fname", fname.Text); cmd.Parameters.AddWithValue("@lname", lname.Text); cmd.Parameters.AddWithValue("@degree", degree.SelectedItem.Value); int i = cmd.ExecuteNonQuery(); conn.Dispose(); }
i have created a button to add details into a SQL database but i want to check the details before i insert the new values from the textboxes
can anyone help....... this is what i have to insert into the database........i just want some help to compare the user name eg... if user name exists a message will appear telling the user to change a different user name
I got 10 Tables with data in it for 100 Loans. The data can be Good and Bad .....
I had a Update Proc which Updates the 10 tables with the Good data what ever i pass...for this 100 Loans Tha Proc will update the existing data in all tables whether it is Good or Bad Data.
when the updating is completed I want to know what are the Loans that were updated where there is a Bad Data in atleast one Field of the 10tables.
I don't want to check field by field in each table.... if there is a bad data and my proc updated with a Good Data i want to know that Loan.
l have a customer text file.l have imported the file into an sql table. Now l want to validate the data starting with the customer_no and the format being xxl-0001 etc ,phone_no i.e 011 as the code and the number as 9028589 etc , say balance
l want to have ensure that the format of customer_no is XXL- then the number Check the phone number that it has the right number of characters and that it is an interger.
How ould l do this? Should l use a procedure to do this or triggers? How would the code look ? Is there a topic on data validation that can give me pointers? Please assist
How yould i loop trought all the records in a table and fetching a specic record that is flagged and sending for each record found a email with that records data to a mail recipient. this should be part of a step in a sql job. PLZ HELP
So far, I only deal with Import & Export data from one server to another.
Earlier today, I initiated a table import, from SQL Server AAA to SQL Server BBB.
I can access Windows Server BBB (and SQL Server BBB) from Computer CCC through Remote Desktop.
My question: Is there any way to check if the import was successful or not, from another computer? Is there a log that I can see? I open the Management>>SQL Server Log, but the import/export is not recorded.
I can see the new table that I imported on Server BBB, but not sure if the import was successful or stopped in the middle since I initiated the import from Computer/Server AAA (I don't have access to Server AAA at this moment). I guess, I need to see the time when it's all done/completely imported. I checked the table properties, but it listed the time of creation. I don't see the time of transfer completed.
i am asked to check data driven errors while importing data from an excel workbook to a sql server table. what are the data driven errors expected and how to introduce that in this sql code.
SELECT a.* FROM OPENROWSET(BULK 'Microsoft.Jet.OLEDB.4.0',
The following Client1 accounts have A1 value and a blank A2 field. Accounts: Ac1,Ac2,Ac3,Ac4, The following Client2 accounts have A1 value and a blank A2 field. Accounts: Ac1,Ac2,Ac3, The following Client3 accounts have A1 value and a blank A2 field. Accounts: Ac1,Ac2,Ac3, The following Client4 accounts have A1 value and a blank A2 field. Accounts:
Desired output (no trailing comma):
The following Client1 accounts have A1 value and a blank A2 field. Accounts: Ac1,Ac2,Ac3,Ac4 The following Client2 accounts have A1 value and a blank A2 field. Accounts: Ac1,Ac2,Ac3 The following Client3 accounts have A1 value and a blank A2 field. Accounts: Ac1,Ac2,Ac3 The following Client4 accounts have A1 value and a blank A2 field. Accounts:
Next, how do I call the stored procedure without doing it RBAR? Is that possible?
I have been trying to write a cursor to fetch required data from table but somehow its running forever and inserting duplicate records.
I have a temp table named getInvoice where I have five important columns
1. invoice number 2.group 3.invoice status 4. Invoice Expiration date 5. Creation date time
and some other columns.One invoice number can belong to one or more group and there can be one or more records for a particular invoice number and group.
An example is below :
InvoiceNumber Group InvoiceStatus InvoiceExpirationDate CreationDateTime
My query condition is complex and that is why Im facing problem retrieving the output.I need a cursor for getting distinct invoice number from the table and for each invoice number I need to get the latest record for each invoice number and suffix combination based on creationdateand time column and if that record has invoice status of 2 and also the invoice expiration date can be either null or greater than today's date, then I need to get that record and put it in a temp table.
The query I wrote is below
declare myData cursor for select distinct invoiceNumber from #getInvoice declare @invoiceNumber varchar(30) open myData fetch next from myData into @invoiceNumber while @@FETCH_STATUS = 0
