Hi,
we've got this problem with some particular jobs: they look as they
ran correctly, but actually they didn't made it all through their
duties.
The problem is that this job is calling a sequence of DTS, where there
is a DTS with an ActiveX control which modifies another DTS before
launching and some other tasks: the error happens there, .
Launched from the DTS we get the error, from the job no...any idea how
we could get the correct job information?
Thank you
Daniele
I have a cube that has a Dimension set up with several values some of which are bools. While Browsing in Excel or SSMS, two new values, when used as a filter shows (All) (Blank) and (True) for selections instead of (All) (True) and (False).Â
Seems that parameter @Sucursales is always the same number. When the query loops in the while statement does it set @Sucursales to the result in the select statement (in red)? Or continue looping from the while statement?
DECLARE @Sucursales INT
SET @Sucursales = (SELECT COUNT(*) FROM CatSucursales)
BEGIN
WHILE @Sucursales > 0
INSERT INTO Compras(Fecha,NumOC,CveProveedor,Importe,CveDepartamento)
SELECT
Fecha,
NumOC,
CveProveedor,
CONVERT(money,SUM(Cantidad * Costo)) AS Importe,
CD.CveDepartamento
FROM
ComprasDetalle CD
INNER JOIN
( SELECT
CveProveedor,
Nombre,
CveDepartamento
FROM
CatProveedores CP
INNER JOIN CatDepartamentos CD ON
CP.CveSucursal = CD.CveSucursal
WHERE
CP.CveSucursal = @Sucursales
)VirCP ON
CD.Proveedor = VirCP.Nombre
AND CD.CveDepartamento = VirCP.CveDepartamento
WHERE
Fecha = DATEADD(dd, DATEDIFF(dd, 0, GETDATE())+0, -1)
GROUP BY Fecha, NumOC, CveProveedor, Nombre, CD.CveDepartamento
I have a .Net application that calls an stored procedure. When it does, the execution goes and never ends (I have to kill the windows process). When I call the sp from within the Management Studio, it also never ends executing and I have to cancel the query. But, when I call it immediately after, it takes 45 seconds to complete.
Now, the sp has several parts and I have made that it prints a message at the end of each part so that I can read where it stops. Strange enough, it completes all parts except the last one, which has the form INSERT INTO myLocalTable SELECT * FROM MyRemoteTable. But if I execute the Select independetly, I discover that it brings no rows! Now, many of the @@rowcount printed after the execution of the other parts shows zero rows involved or just a few. I am not using cursors, each part is an UPDATE statement or an INSERT.
TestMachine1 runs SQL2005 SP2 and has as linked server myRemoteServer (SQL2000) server. The stored procedure in TestMachine1 inserts rows to a table in myRemoteServer and brings back some rows.
I have strange situation with one stored procedure on MS SQL Server 2000, sp3: - It's some old procedure that worked well for several years - It still works at other locations (even on MS SQL Server 7) - Now sometimes this procedure just stops working over ODBC and OLE-DB connection. From Query Analyzer it works always ok! Over ODBC looks like client doesn't receive information, that procedure has completed.
I put some insert at the end of the procedure (for debbuging): procedure executes insert (it's the last statement in procedure), I can select inserted values from table, but client just doesn't get answer from server. I looked all processes at SQL Server and none of them is blocked, there are no locks that prevents procedure execution,...
I found out that recreating procedure makes it working again (for all clients). Well, at least for few days or even weeks.
I have a delete query on a linked server that never ends. I can do a select where ID = x and it returns lickity split but when I do a delete where id = x it never comes back? Any Clues???
I have a question about how Query Notification works and the objects and data created when a new subscription is created.
Here is what we are doing:
- When the application starts, we call the SqlDependency start method:
SqlDependency.Start( <connection string> )
- For each query we want to monitor, we run the following:
SqlConnection oConnection = new SqlConnection( <connection string> ); oConnection.Open(); SqlCommand oCommand = new SqlCommand( <sql statement> , oConnection); SqlDependency oDependency = new SqlDependency(oCommand); oDependency.OnChange += new OnChangeEventHandler( <handler delegate> ); SqlDataReader objReader = oCommand.ExecuteReader(); objReader.Close();
- When the application shuts down, we call the SqlDependency stop method:
SqlDependency.Stop( <connection string> )
We have observed that when we create a new subscription, the following things happen: 1. A row is added to sys.DM_QN_Subscriptions. 2. query_notification_% internal table is created. 3. SQLQueryNotificationStoredProcedure% stored procedure is created. 4. SQLQueryNotificationService% Service Queue is created. 5. queue_messages% internal table is created.
When the application ends, the stored procedure, service queue and queue messages internal table are dropped. The row in sys.DM_QN_Subscriptions and the query_notification_% persist.
When the application is restarted, a new row is added to sys.DM_QN_Subscriptions, but the existing query_notification_% internal table is used.
Is this normal behavior? Or, is there something we should be doing to clean up?
What I am wondering is if there is a threshold where these persistent items become a problem with locking, performance, other?
I now support a SQL Server "master" wrapper package that runs several other packages that simply never ends. When I run it in Visual Studio all the tasks complete, but the status stays running. I'm am trying to get this package to run via TWS/OPC and it doesn't end there either.
Does anyone know what the problem is? Has anyone heard of this?
We have a database that's using merge replication between two servers, and we need to insert a lot (about 1GB) of data into it.
The servers, however, are separated by a 192k WAN connection, so it's impractical to rely on the merge replication to send the data across to the subscriber.
Is there a way to insert the data at both ends? I can get the data out there on a DVD or a laptop easily enough. Can I load the data into both copies of the database and tell the merge agent that it's not to be replicated?
I have a problem concerning the execution status of a data driven subscription. The problem is that I´m creating a datamart and rendering several thousands of pdf based reports after that. I´ve found out how to start the subscription using stored procedures with DTS.
Now I want to take further actions after the report building is done. But how do I find that out?
I've created a simple package that contains only one task that is an execute sql task. When I run only this single task from Business Intelligence development studio it runs successfully. But when I run the whole package (also from Business intlligence studio), the package fails.
The data source I access is ODBC. I'm sure the real reason for the error is the bad ODBC driver of the data source but this can't be changed. So I need to know what is different from running only a task in a package to running the whole package. If I knew that I might be able to adjust some setting and make it work.
So in a script task for one of my packages I have a connection manager to an dtsConfig OLE DB.
This is the code
Dim ConnectionString As String = Dts.Connections("db_stage").ConnectionString Dim sqlConnection As SqlConnection = New SqlConnection(ConnectionString )
I get a login failed for user...But if I hardcode the connectionString, including the password this works.
1) Why is it that the ConnectionString from the connection manager omits this password? 2) Since this is an OLE DB, is there anyway to set the Data Source Designer to omit the "Provider=ABCDED.1" section?
Setting up a test AlwaysOn Availability Group for one database.
However, whenever I restore the database to the replica server and join it, it ends up with my user account as the owner of the database.
Obviously I do not want a user account as the database owner, but since it is read-only I cannot modify it directly. If I were able to fail the AG over to the replica, I could change the owner then, but I cannot due to business requirements. this AG is to essentially serve as a replacement to log shipping.
I tried doing the backups and restores using EXECUTE AS login = 'sa', and yet it still shows up as my user account.
I have to send updated Employee list from employee master table to a particular email ID on every last date of Month and when a new employee is added / deleted / edited. Also need to send this as an Excel fileÂ
I tried the following but "Invalid Object name dbo.tbl_EmployeeMaster" error coming while inserting a new employee.
USE [eXact] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER TRIGGER [dbo].[trg_Email]
How can a tell if a tak completed succesfully from a stored procedure?
I have a task which is executed from a stored procedure. The sp_runtask only returns whether the task started successfully. How can I tell if it completed successfully?
I have a scheduled job to run daily at 4 am. the job imports data from client side from text files and puts data in our sql table.It takes around 1 hour. the problem is it doesnt stops after the import process completes.so after one hour i can see the data is imported into my sql table so thats fine but the job keeps running. I tried observing that job's spid in activity monitor in sql 2005 but after one hour i cant even see that spid but still job runs. its weird.and after that when i stop the job manually then it stops saying job completed succcessfully. that step is a last step and it uses windows cmd.my understanding is the job step doesnt understands that it got finished. what should i do in here?? any ideas r appreciated we are running the same job for another servers and its fine.
2007-03-22 09:36:52.39 server Microsoft SQL Server 2000 - 8.00.194 (Intel X86) Aug 6 2000 00:57:48 Copyright (c) 1988-2000 Microsoft Corporation Standard Edition on Windows NT 5.0 (Build 2195: )
2007-03-22 09:36:52.45 server Copyright (C) 1988-2000 Microsoft Corporation. 2007-03-22 09:36:52.45 server All rights reserved. 2007-03-22 09:36:52.45 server Server Process ID is 1516. 2007-03-22 09:36:52.45 server Logging SQL Server messages in file 'd:Program FilesMicrosoft SQL ServerMSSQLlogERRORLOG'. 2007-03-22 09:36:52.57 server SQL Server is starting at priority class 'normal'(1 CPU detected). 2007-03-22 09:36:53.26 server SQL Server configured for thread mode processing. 2007-03-22 09:36:53.48 server Using dynamic lock allocation. [2500] Lock Blocks, [5000] Lock Owner Blocks. 2007-03-22 09:36:54.31 server Attempting to initialize Distributed Transaction Coordinator. 2007-03-22 09:36:56.46 spid3 Warning ****************** 2007-03-22 09:36:56.46 spid3 SQL Server started in single user mode. Updates allowed to system catalogs. 2007-03-22 09:36:56.60 spid3 Starting up database 'master'. 2007-03-22 09:36:57.26 spid3 Server name is 'PRECISE'. 2007-03-22 09:36:57.26 server Using 'SSNETLIB.DLL' version '8.0.194'. 2007-03-22 09:36:57.26 spid5 Starting up database 'model'. 2007-03-22 09:36:57.60 spid5 Clearing tempdb database. 2007-03-22 09:36:57.74 spid7 Starting up database 'msdb'. 2007-03-22 09:36:57.85 server SuperSocket Info: Bind failed on TCP port 1433. 2007-03-22 09:36:57.89 server SuperSocket Info: Bind failed on TCP port 1433. 2007-03-22 09:36:57.89 server SuperSocket Info: Bind failed on TCP port 1433. 2007-03-22 09:36:57.89 server SuperSocket Info: Bind failed on TCP port 1433. 2007-03-22 09:36:58.31 server SQL server listening on TCP, Shared Memory, Named Pipes. 2007-03-22 09:36:58.31 server SQL server listening on 195.195.195.2:1433, 127.0.0.1:1433. 2007-03-22 09:36:58.31 server SQL Server is ready for client connections 2007-03-22 09:36:58.35 spid7 Starting up database 'pubs'. 2007-03-22 09:36:58.64 spid7 Starting up database 'Northwind'. 2007-03-22 09:37:00.18 spid5 Starting up database 'tempdb'. 2007-03-22 09:37:00.32 spid3 Recovery complete. 2007-03-22 09:37:00.32 spid3 Warning: override, autoexec procedures skipped. 2007-03-22 09:37:07.95 spid3 SQL Server is terminating due to 'stop' request from Service Control Manager.
hi, i have a message queue system using sql 2005 service broker. the code and setup is the same on both dev and live database. but soon after i restored a live backup to dev. the queue stopped working on dev, live is ok thou. after some trouble shooting, i found that the server is not sending the message at all, but it says "Command(s) completed successfully" without any error messages.
setup:
-----------------------
create message type TestQueryMessage validation = none
create contract TestQueryContract (TestQueryMessage sent by initiator)
create queue TestSenderQueue
create service TestSenderService on queue TestSenderQueue
create queue TestQueueReceiver
create service TestServiceReceiver on queue TestQueueReceiver (TestQueryContract)
send message:
-------------------------
declare @conversationhandle uniqueidentifier;
begin dialog @conversationhandle
from service [TestSenderService]
to service 'TestServiceReceiver'
on contract [TestQueryContract]
with encryption = off;
send on conversation @conversationhandle
message type [TestQueryMessage] ('blah blah blah');
result:
----------------------------------
Command(s) completed successfully.
but when i do "select * from TestQueueReceiver", there's nothing. and i sure nothing else had picked up the messages.
Currently, I'm developing an ETL program using Microsoft DTS on SQL Server 2000.
I've completed unit testing & component testing for all inner packages containing various types of executions - SQL task, ActiveX Script task, Error Handler, Transform Data task, Data Driven Query & Dynamic Properties task.
When unit & component tested, all inner package properties-"Limit the maximum number of tasks executed in parallel" is defaulted to 4.
However, the integration testing is failing because I've yet to successfully execute any of the Package tasks in the outer package, which runs & controls all inner packages from the outer package.
The error message encountered was: --------------------------------------------------------------- | Error source: microsoft dts | | Error description: execution was cancelled by another user. | ---------------------------------------------------------------
Please explain what might be the cause to this problem when I'm executing the outer package?
I have a program that is automatically ran through a job. The program gets the most recent files that have been uploaded to a server. I would like to be able to query the database to see when the last time this job was ran successfully and set this date as the date to look for files newer than the last successful run date.
Could someone point me in the right direction to what tables this data is stored in on a 2005 SQL Server Database?
I have this data in a flat file and I'm doing a data dump to a SQL table. I created a SSIS package which ran successfully but only exporting 2 rows out of 4 rows to the SQL table. For some reason my package ignore two other rows where "col4", "col5" and "col6" do not have any data. No conditional component was used in this package to not to load those records.
col1 col2 col3 col4 col4 col5 col6
849833 02/15/06 841 VS 791 49 7 849819 02/15/06 212 NA
I'm in the process of installing Hotfix kb934458 (9.0.3054) on a Windows 2003 R2 SP2 Server. This is for SQL 2005 on a Active/Passive cluster with 2 instances. I'm only getting this error on the Database Engine and Analysis Services. It was successful for Integration Services, Reporting Services and Notification Services. I've tried taking the 2 instances offline as well as tried installing from the Active then again on the Passive nodes. No luck... Any suggestions? Does this work for R2 release of Windows 2003 SP2? This is the log:
Time: 02/29/2008 14:02:38.088 KB Number: KB934458 Machine: OS Version: Microsoft Windows Server 2003 family, Enterprise Edition Service Pack 2 (Build 3790) Package Language: 1033 (ENU) Package Platform: x64 Package SP Level: 2 Package Version: 3054 Command-line parameters specified: Cluster Installation: Yes Log Location on Passive Nodes: C:Program FilesMicrosoft SQL Server90Setup BootstrapLOGHotfix C:Program FilesMicrosoft SQL Server90Setup BootstrapLOGHotfix C:Program FilesMicrosoft SQL Server90Setup BootstrapLOGHotfix C:Program FilesMicrosoft SQL Server90Setup BootstrapLOGHotfix C:Program FilesMicrosoft SQL Server90Setup BootstrapLOGHotfix C:Program FilesMicrosoft SQL Server90Setup BootstrapLOGHotfix C:Program FilesMicrosoft SQL Server90Setup BootstrapLOGHotfix C:Program FilesMicrosoft SQL Server90Setup BootstrapLOGHotfix ********************************************************************************** Prerequisites Check & Status SQLSupport: Passed ********************************************************************************** Products Detected Language Level Patch Level Platform Edition SQL Server Database Services 2005 (BIZTALK) ENU SP2 2005.090.3050.00 x64 ENTERPRISE SQL Server Analysis Services 2005 (BIZTALK) ENU SP2 2005.090.3050.00 x64 ENTERPRISE SQL Server Reporting Services 2005 (BIZTALK) ENU SP2 9.00.3054.00 x64 ENTERPRISE SQL Server Database Services 2005 (SHAREPOINT) ENU SP2 2005.090.3050.00 x64 ENTERPRISE SQL Server Analysis Services 2005 (SHAREPOINT) ENU SP2 2005.090.3050.00 x64 ENTERPRISE SQL Server Reporting Services 2005 (SHAREPOINT) ENU SP2 9.00.3054.00 x64 ENTERPRISE SQL Server Notification Services 2005 ENU SP2 9.00.3054.00 x64 ENTERPRISE SQL Server Integration Services 2005 ENU SP2 9.00.3054.00 x64 ENTERPRISE SQL Server Tools and Workstation Components 2005 ENU SP2 9.2.3054 x64 ENTERPRISE ********************************************************************************** Products Disqualified & Reason Product Reason ********************************************************************************** Processes Locking Files Process Name Feature Type User Name PID MSSQL$BIZTALK SQL Server Database Services Service 4756 MSSQL$SHAREPOINT SQL Server Database Services Service 4636 MSOLAP$BIZTALK Analysis Services Service 7976 MSOLAP$SHAREPOINT Analysis Services Service 7148 ********************************************************************************** Product Installation Status Product : SQL Server Database Services 2005 (BIZTALK) Product Version (Previous): 3050 Product Version (Final) : Status : Failure Log File : Error Number : 11009 Error Description : No passive nodes were successfully patched ---------------------------------------------------------------------------------- Product : SQL Server Analysis Services 2005 (BIZTALK) Product Version (Previous): 3050 Product Version (Final) : Status : Failure Log File : Error Number : 11009 Error Description : No passive nodes were successfully patched ---------------------------------------------------------------------------------- Product : SQL Server Reporting Services 2005 (BIZTALK) Product Version (Previous): 3054 Product Version (Final) : Status : Not Selected Log File : Error Number : 0 Error Description : ---------------------------------------------------------------------------------- Product : SQL Server Database Services 2005 (SHAREPOINT) Product Version (Previous): 3050 Product Version (Final) : Status : Failure Log File : Error Number : 11009 Error Description : No passive nodes were successfully patched ---------------------------------------------------------------------------------- Product : SQL Server Analysis Services 2005 (SHAREPOINT) Product Version (Previous): 3050 Product Version (Final) : Status : Failure Log File : Error Number : 11009 Error Description : No passive nodes were successfully patched ---------------------------------------------------------------------------------- Product : SQL Server Reporting Services 2005 (SHAREPOINT) Product Version (Previous): 3054 Product Version (Final) : Status : Not Selected Log File : Error Number : 0 Error Description : ---------------------------------------------------------------------------------- Product : SQL Server Notification Services 2005 Product Version (Previous): 3054 Product Version (Final) : Status : Not Selected Log File : Error Description : ---------------------------------------------------------------------------------- Product : SQL Server Integration Services 2005 Product Version (Previous): 3054 Product Version (Final) : Status : Not Selected Log File : Error Description : ---------------------------------------------------------------------------------- Product : SQL Server Tools and Workstation Components 2005 Product Version (Previous): 3054 Product Version (Final) : Status : Not Selected Log File : Error Description : ---------------------------------------------------------------------------------- ********************************************************************************** Summary No passive nodes were successfully patched Exit Code Returned: 11009
I have had a serious issue with a production AlwaysOn cluster whereby the service did not successfully transition to the secondary node and I cannot find the root cause of the issue.
Some details: It is a 2 node cluster (same datacenter) with a shared disk quorum, Windows Server 2012, both are virtual machines running on VMWare vSphere  5.5. SQL Server version is 2012 Enterprise SP2 CU6
The failover occurred because of a network incident (a spanning tree recalculation caused a connection timeout between both nodes). Initial entries in the SQL Log look normal for this event, for example:
05/08/2015 11:18:06:Â A connection timeout has occurred on a previously established connection to availability replica 'FIN-IE-PA078' with id [6910F4A9-87E7-4836-BA79-0F41BE90266D]. Â Either a networking or a firewall issue exists or the availability replica has transitioned to the resolving role. 05/08/2015 11:18:06:Â AlwaysOn Availability Groups connection with secondary database terminated for primary database 'UserManagement' on the availability replica with Replica ID: {6910f4a9-87e7-4836-ba79-0f41be90266d}. This is an informational message only. No user action is required.
[code]....
My interpretation of this is that the cluster failover attempts failed, because the network condition still persisted. The network interruption lasted approximately 2 minutes, and I would have expected the cluster to come back online at this point, after the restart delay period as suggested in the last entry in the error log. However this did not happen.
I have a page that runs a transaction correctly after a button click. I want to allow someone to click a button that rolls back the transaction, after the transaction runs on the first button click. I can also successfully roll back within the first button click. I'm getting a NullReference error when trying to access SqlTransaction.Rollback() outside the button click. If SqlTransaction.Commit() completes without error can SqlTransaction.Rollback() be called after? I tried making 'trans' a more global variable and it still gave me the error. Button Click 1: Dim trans As SqlTransaction trans = connection.BeginTransaction() try 'run SQL Statement trans.Commit() Catch e As Exception trans.Rollback() throw e end try
Currently I am running a SSIS package scheduled daily at 7 A.M. It expects two feed files from two different folders. The first step in my package will rename the input files in those folders to names which the package can understand. I have created two variables in my package to read the files with those file names. I used these variables in connection managers.
If any of these folders doesn't have input files when the package runs, the package will fail.how to make the package the run successfully even there is no input feed?
I have a database cluster which I am trying to upgrade to SP1+hotfixes. I was able to successfully upgrade to SP1, however when I try to apply the hotfix "sql2005-kb918222-x86-enu.exe". The install fails and generates the following error log; Can anyone please help? Thanks a lot.
10/16/2007 09:35:50.058 ================================================================================ 10/16/2007 09:35:50.058 Hotfix package launched 10/16/2007 09:35:52.495 Product discovery successfully completed during the install process for MSSQLSERVER 10/16/2007 09:35:52.495 SP Level check successfully completed during the install process for MSSQLSERVER 10/16/2007 09:35:52.527 Product language check successfully completed during the install process for MSSQLSERVER 10/16/2007 09:35:52.542 Product version check successfully completed during the install process for MSSQLSERVER 10/16/2007 09:35:52.558 Command-line instance name check completed during the install process 10/16/2007 09:35:52.573 Baseline build check completed during the install process 10/16/2007 09:36:15.028 Attempting to install instance: MSSQLSERVER 10/16/2007 09:36:15.059 Enumerating passive cluster nodes 10/16/2007 09:36:15.090 Patching available passive node: SNTCCASGDB006 10/16/2007 09:36:15.106 Waiting for first successfully completed passive node 10/16/2007 09:36:15.121 Attempting to patch running passive node: SNTCCASGDB006 10/16/2007 09:36:15.200 Successfully created remote folder for product instance target \SNTCCASGDB006 10/16/2007 09:36:15.809 Successfully transferred payload to remote product instance target \SNTCCASGDB006 10/16/2007 09:36:15.825 Successfully transferred payload to remote product instance target \SNTCCASGDB006 10/16/2007 09:36:16.981 Successfully created new scheduled task for product instance target \SNTCCASGDB006 10/16/2007 09:36:17.840 Successfully saved new scheduled task for product instance target \SNTCCASGDB006 10/16/2007 09:36:17.872 Successfully created scheduled task for product instance target \SNTCCASGDB006 10/16/2007 09:36:17.887 Successfully started scheduled task for product instance target \SNTCCASGDB006 10/16/2007 09:36:17.903 Successfully started scheduled task for product instance target \SNTCCASGDB006 10/16/2007 09:36:20.934 Scheduled task for product instance target has completed 10/16/2007 09:36:20.950 Waiting for exit code from scheduled task for product instance target \SNTCCASGDB006 10/16/2007 09:36:25.966 Received exit code 11201 from scheduled task for product instance target \SNTCCASGDB006 10/16/2007 09:36:25.981 Result code for scheduled task for product instance target has been received 10/16/2007 09:36:25.997 Removed scheduled task for product instance target \SNTCCASGDB006 10/16/2007 09:36:26.169 Successfully removed remote folder for product instance target \SNTCCASGDB006 10/16/2007 09:36:26.185 Remote process completed for product instance target 10/16/2007 09:36:26.200 Exit code for passive node: SNTCCASGDB006 = 11201 10/16/2007 09:36:30.122 The following exception occurred: No passive nodes were successfully patched Date: 10/16/2007 09:36:30.122 File: depotsqlvaultstablesetupmainl1setupsqlsesqlsedllinstance.cpp Line: 2583 10/16/2007 09:36:30.169 10/16/2007 09:36:30.200 Product Status Summary: 10/16/2007 09:36:30.232 Product: SQL Server Database Services 2005 10/16/2007 09:36:30.263 MSSQLSERVER - Failure 10/16/2007 09:36:30.310 Details: No passive nodes were successfully patched 10/16/2007 09:36:30.341
Error 0x80070005 while loading package file "Package.dtsx". Access is denied. .
The package is executed from the main package via an 'Execute Package Task'. The strange thing is that the error comes after 'Package.dtsx' has run successfully. I am logging the PackageStart/PackageEnd and error events and I see that Package.dtsx ends successfully and then the "Access is denied" error occurs. The main package is launched by executing dtsexec via a SQL Server Agent Job. The packages have 'SaveCheckpoints' set to True. Any ideas are welcome. Thanks.
Is there anyone who was able to successfully retrieve a full result set? I'm really having troubles getting the result after executing my query. Its really even hard to get sample codes over the net.
I have a script contains multiple statements to update multiple tables. How can I make sure that either all statements get executed successfully or no changes apply to the tables (in case one or more errors occur)? I've been searching on Internet and it seems like I need to use Rollback and begin transaction.
I am running a DTS Package.I have a temp table with 1494 records. I am inserting a 'Y' or'N'into a temp table #HasClaims.The TempTable name with the Provider Id's(PRPR_ID) is#TempFACETSNODupesThe @identityID is an identity field counting back from 1494 to 1I count back from the Max value of the identityid (1494) in the Whileloop until I get through all the records. The idea is to check for theexistance of a claims and authorization record and put a 'Y' or 'N'record in the temptable #HasClaims.This is running in the Execute SQL Task object of the DTS Package.The Package runs successfully but only inserts 200 rows into the newtemp table. There should be a row for each provider. Each time itruns, the number of rows it returns is different. Sometimes it is 205,then 185, then 210, before it completes the DTS package.Has anyone run into While looping problems within an Execute SQL taskin a DTS package(SQL 2000)like this--------------------------------------------------------------SELECT @identityID = MAX(IDENTITYID) FROM #TempFACETSNODupesWhile @identityID >= 1BEGIN@PRPRID is the placeholder for the PRPR_ID (Provider)SELECT @PRPRID = PRPR_ID FROM #TempFACETSNODupes WHERE IDENTITYID =@identityIDIF exists( SELECT CLCL_ID FROM dbo.CMC_CLCL_CLAIM CLCL WHERECLCL.PRPR_ID = @PRPRID)BEGININSERT INTO #HasClaims (PRPR_ID, HasClaims, IdentityID)VALUES( @PRPRID, 'Y', @identityID)ENDELSE INSERT INTO #HasClaims (PRPR_ID, HasClaims, IdentityID)VALUES( @PRPRID, 'N', @identityID)------------------------------------------------------------SELECT @identityID = @identityID - 1END
