Capture A Fresh Copy Of The Stored Proc
Sep 18, 2007How do I go about if I want to capture a fresh copy of the stored procedure?
Nishi
ADVERTISEMENT
If Exists Capture Value Returned From Stored Proc
Feb 26, 2006I have a stored proc with a query which checks whether an identical
value is already in the database table. If so, it returns a value of 1.
How do I caputure this value in an asp.net page in order to display a
message accordingly? (using ASP.NET 1.1)
Currently my stored proc looks something like this (snippet only):
If Exists(
SELECT mydoc WHERE....
)
Return 1
Else
...INSERT INTO.... code here.
Capture Stored Proc Output To File
Jan 8, 2001I am running a stored proc that does many updates. I want to capture the results of the stored proc in a text file. Results returned such as, 18 rows affected (the stuff that shows in results pane of query analyzer). Do I have to run the stored proc in batch mode to do this and capture via the output file in OSQL? I also want to capture error messages in case the stored proc aborts in the middle.
I am running SQL Server 7.0 SP2 on Win2K. I am running the stored proc in a DTS package.
Thanks.
Tim
How Does The .net App Capture Raiserror Values From A Stored Proc
Oct 28, 2006I have been using return values to check the status of my stored procedures called from an application. But how does one read the new raiserror values that are passed with sql server 2005 and using ado.net 2.0. Are they returned as parameters or as a dataset or what?
Thanks
smHaig
Exec SQL Task: Capture Return Code Of Stored Proc Not Working
May 19, 2006I am just trying to capture the return code from a stored proc as follows and if I get a 1 I want the SQL Task to follow a failure(red) constrainst workflow and send a SMTP mail task warning the customer. How do I achieve the Exec SQL Task portion of this, i get a strange error message [Execute SQL Task] Error: There is an invalid number of result bindings returned for the ResultSetType: "ResultSetType_SingleRow".
Using OLEDB connection, I utilize SQL: EXEC ? = dbo.CheckCatLog
EXEC SQL Task Editer settings:
RESULTSET: Single Row
PARAMETER MAPPING: User::giBatchID
DIRECTION: OUTPUT
DATATYPE: LONG
PARAMETER NAME: 0
PS-Not sure if I need my variable giBatchID which is an INT32 but I thought it is a good idea to feed the output into here just in case there is no way that the EXEC SQL TASK can chose the failure constrainst workflow if I get a 1 returned or success constraint workflow if I get a 0 returned from stored proceedure
CREATE PROCEDURE CheckCatLog
@OutSuccess INT
AS
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON
DECLARE @RowCountCAT INT
DECLARE @RowCountLOG INT
---these totals should match
SELECT @RowCountCAT = (SELECT Count(*) FROM mydb_Staging.dbo.S_CAT)
SELECT @RowCountLOG = (SELECT Count(*) FROM mydb_Staging.dbo.S_LOG)
--PRINT @RowCountCAT
--PRINT @RowCountLOG
BEGIN
IF @RowCountCAT <> @RowCountLOG
--PRINT 'Volume of jobs from the CAT file does not match volume of jobs from the LOG file'
--RETURN 1
SET @OutSuccess = 1
END
GO
Thanks in advance
Dave
Stored Proc To Copy Unnormalized To Normalized Table
Jul 20, 2005I have a "source" table that is being populated by a DTS bulk importof a text file. I need to scrub the source table after the importstep by running appropriate stored proc(s) to copy the source data to2 normalized tables. The problem is that table "Companies" needs tobe populated first in order to generate the Identity ID and then usethat as the foreign key in the other table.Here is the DDL:CREATE TABLE [dbo].[OriginalList] ([FirstName] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,[LastName] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,[Company] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,[Addr1] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,[City] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,[State] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,[Zip] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,[Phone] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL) ON [PRIMARY]GOCREATE TABLE [dbo].[Companies] ([ID] [int] IDENTITY (1, 1) NOT NULL ,[Name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL) ON [PRIMARY]GOCREATE TABLE [dbo].[CompanyLocations] ([ID] [int] IDENTITY (1, 1) NOT NULL ,[CompanyID] [int] NOT NULL ,[Addr1] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,[City] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,[State] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,[Zip] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,[Phone] [varchar] (14) COLLATE SQL_Latin1_General_CP1_CI_AS NULL) ON [PRIMARY]GOThis is the stored proc I have at this time that does NOT work. Ituses the last Company insert for all the CompanyLocations which is notcorrect.CREATE PROCEDURE DataScrubSP ASBegin Transactioninsert Companies (Name) select Company from OriginalListIF @@Error <> 0GOTO ErrorHandlerdeclare @COID intselect @COID=@@identityinsert CompanyLocations (CompanyID, Addr1, City, State, Zip) select@COID, Addr1, City, State, Zip from OriginalListIF @@Error <> 0GOTO ErrorHandlerCOMMIT TRANSACTIONErrorHandler:IF @@TRANCOUNT > 0ROLLBACK TRANSACTIONRETURNGOThanks for any help.Alex.
View 3 Replies View RelatedSQL Server 2008 :: Extended Events Capture A Specific Proc Parameter
Feb 19, 2015I know very little about Extended Events, but I know it is supposed to be more powerful than Profiler. The SQL Instance involved is 2008R2. I was asked whether we could capture when a stored proc was called with a certain parameter. So for example, if dbo.usp_mystoredproc is called and is passed a value of '12345a' for @customer, can that be captured? I would want to know the time it was called, the parameter and parameter value. Probably would be interested in the SPID or LoginName as well.
View 4 Replies View RelatedCan You Trace Into A Stored Proc? Also Does RAISERROR Terminate The Stored Proc Execution.
Feb 13, 2008I am working with a large application and am trying to track down a bug. I believe an error that occurs in the stored procedure isbubbling back up to the application and is causing the application not to run. Don't ask why, but we do not have some of the sourcecode that was used to build the application, so I am not able to trace into the code.
So basically I want to examine the stored procedure. If I run the stored procedure through Query Analyzer, I get the following error message:
Msg 2758, Level 16, State 1, Procedure GetPortalSettings, Line 74RAISERROR could not locate entry for error 60002 in sysmessages.
(1 row(s) affected)
(1 row(s) affected)
I don't know if the error message is sufficient enough to cause the application from not running? Does anyone know? If the RAISERROR occursmdiway through the stored procedure, does the stored procedure terminate execution?
Also, Is there a way to trace into a stored procedure through Query Analyzer?
-------------------------------------------As a side note, below is a small portion of my stored proc where the error is being raised:
SELECT @PortalPermissionValue = isnull(max(PermissionValue),0)FROM Permission, PermissionType, #GroupsWHERE Permission.ResourceId = @PortalIdAND Permission.PartyId = #Groups.PartyIdAND Permission.PermissionTypeId = PermissionType.PermissionTypeId
IF @PortalPermissionValue = 0BEGIN RAISERROR (60002, 16, 1) return -3END
Stored Proc - Calling A Remote Stored Proc
Aug 24, 2006I am having trouble executing a stored procedure on a remote server. On my
local server, I have a linked server setup as follows:
Server1.abcd.myserver.comSQLServer2005,1563
This works fine on my local server:
Select * From [Server1.abcd.myserver.comSQLServer2005,1563].DatabaseName.dbo.TableName
This does not work (Attempting to execute a remote stored proc named 'Data_Add':
Exec [Server1.abcd.myserver.comSQLServer2005,1563].DatabaseName.Data_Add 1,'Hello Moto'
When I attempt to run the above, I get the following error:
Could not locate entry in sysdatabases for database 'Server1.abcd.myserver.comSQLServer2005,1563'.
No entry found with that name. Make sure that the name is entered correctly.
Could anyone shed some light on what I need to do to get this to work?
Thanks - Amos.
Stored Proc Question : Why If Exisits...Drop...Create Proc?
Jun 15, 2006Hi All,Quick question, I have always heard it best practice to check for exist, ifso, drop, then create the proc. I just wanted to know why that's a bestpractice. I am trying to put that theory in place at my work, but they areasking for a good reason to do this before actually implementing. All Icould think of was that so when you're creating a proc you won't get anerror if the procedure already exists, but doesn't it also have to do withCompilation and perhaps Execution. Does anyone have a good argument fordoing stored procs this way? All feedback is appreciated.TIA,~CK
View 3 Replies View RelatedASP Cannot Run Stored Proc Until The Web User Has Run The Proc In Query Analyzer
Feb 23, 2007I have an ASP that has been working fine for several months, but itsuddenly broke. I wonder if windows update has installed some securitypatch that is causing it.The problem is that I am calling a stored procedure via an ASP(classic, not .NET) , but nothing happens. The procedure doesn't work,and I don't get any error messages.I've tried dropping and re-creating the user and permissions, to noavail. If it was a permissions problem, there would be an errormessage. I trace the calls in Profiler, and it has no complaints. Thedatabase is getting the stored proc call.I finally got it to work again, but this is not a viable solution forour production environment:1. response.write the SQL call to the stored procedure from the ASPand copy the text to the clipboard.2. log in to QueryAnalyzer using the same user as used by the ASP.3. paste and run the SQL call to the stored proc in query analyzer.After I have done this, it not only works in Query Analyzer, but thenthe ASP works too. It continues to work, even after I reboot themachine. This is truly bizzare and has us stumped. My hunch is thatwindows update installed something that has created this issue, but Ihave not been able to track it down.
View 1 Replies View RelatedCapture Stored Procedure Along With Parameters
Jan 28, 2008Is there a way to capture Stored Procedure Name along with the Parameter passed in SQL 2005 or SQL2000 when a stored procedure is executed?
Thanks !
How Do I Capture @@ROWCOUNT From A Stored Procedure?
Dec 5, 2006Greetings,
I have a stored procedure that does updates. The sproc is called from an €œExec SQL Task€?. The sproc returns the number of rows that were updated using this technique:
RETURN @@ROWCOUNT
How do I capture the sproc€™s return value (number of rows) in a package-level variable? I eventually want to log this value. The sproc is not returning a result set. I€™m new to SSIS so any general guidance would be appreciated.
Thanks,
BCB
Calling A Stored Proc From Within Another Stored Proc
Feb 20, 2003I have seen this done by viewing code done by a SQL expert and would like to learn this myself. Does anyone have any examples that might help.
I guess I should state my question to the forum !
Is there a way to call a stored proc from within another stored proc?
Thanks In Advance.
Tony
Stored Proc Calls Another Stored Proc
Jan 13, 2006Hi all,
I have a stored procedure "uspX" that calls another stored procedure "uspY" and I need to retrieve the return value from uspY and use it within uspX. Does anyone know the syntax for this?
Thanks for your help!
Cat
Calling Stored Proc B From Stored Proc A
Jan 20, 2004Hi all
I have about 5 stored procedures that, among other things, execute exactly the same SELECT statement
Instead of copying the SELECT statement 5 times, I'd like each stored proc to call a single stored proc that executes the SELECT statement and returns the resultset to the calling stored proc
The SELECT statement in question retrieves a single row from a table containing 10 columns.
Is there a way for a stored proc to call another stored proc and gain access to the resultset of the called stored proc?
I know about stored proc return values and about output parameters, but I think I am looking for something different.
Thanks
Calling T SQL Stored Proc From CLR Stored Proc
Aug 30, 2007I would like to know if the following is possible/permissible:
myCLRstoredproc (or some C# stored proc)
{
//call some T SQL stored procedure spSQL and get the result set here to work with
INSERT INTO #tmpCLR EXECUTE spSQL
}
spSQL
(
INSERT INTO #tmpABC EXECUTE spSQL2
)
spSQL2
(
// some other t-sql stored proc
)
Can we do that? I know that doing this in SQL server would throw (nested EXECUTE not allowed). I dont want to go re-writing the spSQL in C# again, I just want to get whatever spSQL returns and then work with the result set to do row-level computations, thereby avoiding to use cursors in spSQL.
Is It Possible To Capture An OUT Type Parameter From A PL/SQL Stored Procedure?
Dec 19, 2007When a stored PL/SQL procedure in my Oracle database is called from ASP.NET, is it possible to retrieve the OUT parameter from the PL/SQL procedure? For example, if I have a simple procedure as below to insert a row into the database. Ideally I would like it to return back the parameter named NewId to my ASP.NET server. I'd like to capture this in the VB.NET code. 1 create or replace procedure WriteName(FirstName in varchar2, LastName in varchar2, NewId out pls_integer) is2 3 NameId pls_integer;4 5 begin6 7 select name_seq.nextval into NameId from dual;8 9 insert into all_names(id, first_name, last_name)10 values(NameId, FirstName, LastName);11 12 NewId := NameId;13 14 end WriteName; 1 <asp:SqlDataSource 2 ID="SqlDataSaveName" 3 runat="server" 4 ConnectionString="<%$ ConnectionStrings:ConnectionString %>"5 ProviderName="<%$ ConnectionStrings:ConnectionString.ProviderName %>" 6 SelectCommand="WRITENAME"7 SelectCommandType="StoredProcedure">8 <SelectParameters>9 <asp:ControlParameter ControlID="TextBoxFirstName" Name="FIRSTNAME" PropertyName="Text" Type="String" />10 <asp:ControlParameter ControlID="TextBoxLastName" Name="LASTNAME" PropertyName="text" Type="String" />11 </SelectParameters>12 </asp:SqlDataSource>This is then called in the VB.NET code as below. It is in this section that I would like to capture the PL/SQL OUT parameter NewId returned from Oracle. 1 SqlDataSaveName.Select(DataSourceSelectArguments.Empty) If anybody can help me with the code I need to add to the VB.NET section to capture and then use the returned OUT parameter then I'd be very grateful.
View 2 Replies View RelatedHow Do I Capture The Error Message From A Stored Procedure?
May 30, 2007Greetings,
I am creating a package that has many SQL tasks. Each task executes a stored procedure. I need to capture any error messages returned by the stored procedures. Eventually, the error messages will be logged so that we can audit the package and know if individual tasks succeeded or failed.
I'm not sure where or how I can access a stored procedure message. What is the best way?
Thanks,
BCB
Copy? Export? How To Copy Stored Procedures
Aug 30, 2005How do I transfer/copy the stored procedures in my Test DB to my LIVE DB? IT won't allow me to export keeps giving me an error.
View 4 Replies View RelatedCapture Return Value From Stored Procedure, Use Same In Code Behind Page
Apr 18, 2007My stored procedure works and codes is working except I need to capture the return value from the stored procedure and use that value in my code behind page to indicate that a duplicate record entry was attempted. In my code behind file (VB) how would I capture the value "@myERROR" then display in the label I have that a duplicate entry was attempted.
Stored ProcedureCREATE PROCEDURE dbo.usp_InsertNew @IDNumber nvarchar(25), @ID nvarchar(50), @LName varchar(50), @FName varchar(50)
AS
DECLARE @myERROR int -- local @@ERROR , @myRowCount int --local @@rowcountBEGIN -- See if a contact with the same name and zip code exists IF EXISTS (Select * FROM Info WHERE ID = @ID) BEGIN RETURN 1END ELSEBEGIN TRAN INSERT INTO Info(IDNumber, ID, LName, FName) VALUES (@IDNumber, @ID, @LName, @FName) SELECT @myERROR = @@ERROR, @myRowCount = @@ROWCOUNT If @myERROR !=0 GOTO HANDLE_ERROR
COMMIT TRAN RETURN 0 HANDLE_ERROR: ROLLBACK TRAN RETURN @myERROR ENDGO
asp.net page<asp:SqlDataSource ID="ContactDetailDS" runat="server" ConnectionString="<%$ ConnectionStrings:EssPerLisCS %>" SelectCommand="SELECT * FROM TABLE_One" UpdateCommand="UPDATE TABLE_One WHERE ID = @ID" InsertCommand="usp_InsertNew" InsertCommandType="StoredProcedure"> <SelectParameters> <asp:ControlParameter ControlID="GridView1" Name="ID" PropertyName="SelectedValue" /> </SelectParameters> </asp:SqlDataSource>
How To Capture The ResultSet Of EXEC Command In Stored Procedure
Oct 1, 2007
Hi All,
I have created a dynamic SQL STATEMENT , but the result of the execution of that statement matters to me. within stored procedure.
Note: If run the statement using EXEC() command, the result gets displayed on the SQL Editor.
But I DO NOT KNOW HOW to Capture that value.
Any idea how to capture as I would like capture the result and stored it in a table.
Thank you.
--Israr
Is There A Way To Capture All Bulk Insert Errors From Within A Stored Procedure?
Sep 28, 2007Hi all!!
I have a stored procedure that dynamically bulk loads several tables from several text files. If I encounter an error bulk loading a table in the stored procedure, all I get is the last error code produced, but if I run the actual bulk load commands through SQL Management Studio, it gives much more usable errors, which can include the column that failed to load. We have tables that exceed 150 columns (don't ask), and having this information cuts troubleshooting load errors from hours down to minutes. Onto my question..., is there any way to capture all of the errors produced by the bulk load from within a stored procedure (see examples below)?
Running this...
BULK INSERT Customers
FROM 'c: estcustomers.txt'
WITH (TabLock, MaxErrors = 0, ErrorFile = 'c: estcustomers.txt.err')
Produces this (notice column name at the end of the first error)...
Msg 4864, Level 16, State 1, Line 1
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 1, column 1 (CustId).
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "BULK" for linked server "(null)" reported an error. The provider did not give any information about the error.
Msg 7330, Level 16, State 2, Line 1
Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".
Running this (similar to code in my stored procedure)...
BEGIN TRY
BULK INSERT Customers
FROM 'c: estcustomers.txt'
WITH (TabLock, MaxErrors = 0, ErrorFile = 'c: estcustomers.txt.err')
END TRY
BEGIN CATCH
SELECT
ERROR_NUMBER() AS ErrorNumber,
ERROR_SEVERITY() AS ErrorSeverity,
ERROR_STATE() AS ErrorState,
ERROR_PROCEDURE() AS ErrorProcedure,
ERROR_LINE() AS ErrorLine,
ERROR_MESSAGE() AS ErrorMessage;
END CATCH
Produces something similar to this (which is useless)...
...Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".
SQL Server 2012 :: How To Capture CPU And Memory Usage For A Stored Procedure
Jan 19, 2015I have around 100 packages (all [packages run at same time) each package calls a stored Procedure once the Stored proc Execution is completed the package will write the log information into a log table, here how can i capture the CPU and Memory usage for execution of each stored proc.
View 2 Replies View RelatedHow To Capture SQL Server Does Not Exist Type Errors Within Stored Procs
Nov 16, 2007Hi, I have a stored procedure running on our monitoring server to monitor the backup jobs on other sql servers running on our network. This sp loops thorugh the list of server names and connects remotely to other servers using linked servers. However, if one of the servers is down, the sp stops after raising 42000 error ("SQL Server does not exist") and fails to carry on processing the next server in the list.
Is there any way to capture this "SQL Server does not exist" error within the sp so that the sp can carry on with the processing?
I am using SQL Server 2000 SP4.
Thanks in advance for any replies.
Opal
Execute Stored Procedure Y Asynchronously From Stored Proc X Using SQL Server 2000
Oct 14, 2007I am calling a stored procedure (say X) and from that stored procedure (i mean X) i want to call another stored procedure (say Y)asynchoronoulsy. Once stored procedure X is completed then i want to return execution to main program. In background, Stored procedure Y will contiue his work. Please let me know how to do that using SQL Server 2000 and ASP.NET 2.
View 3 Replies View RelatedHow Can I Call One Or More Stored Procedures Into Perticular One Stored Proc ?
Apr 23, 2008Hello friends......How are you ? I want to ask you all that how can I do the following ?
I want to now that how many ways are there to do this ?
How can I call one or more stored procedures into perticular one Stored Proc ? in MS SQL Server 2000/05.
Calling A Stored Procedure From Within A Stored Proc
Dec 18, 2007Hi Peeps
I have a SP that returns xml
I have writen another stored proc in which I want to do something like this:Select FieldOne, FieldTwo, ( exec sp_that_returns_xml ( @a, @b) ), FieldThree from TableName
But it seems that I cant call the proc from within a select.
I have also tried
declare @v xml
set @v = exec sp_that_returns_xml ( @a, @b)
But this again doesn't work
I have tried changing the statements syntax i.e. brackets and no brackets etc...,
The only way Ive got it to work is to create a temp table, insert the result from the xml proc into it and then set @v as a select from the temp table -
Which to be frank is god awful way to do it.
Any and all help appreciated.
Kal
Fresh SQL 2000 To SP4 Gives Error
Jan 5, 2007Hi,
I'm trying to Service Pack 4 a fresh installation of SQL 2000 (a named instance called 'SQLSERVER2000') and get the error "Installation of the Microsoft Full-Text Search Engine package failed. (-2147467259) 0x80004005 Unspecified Error" towards the end of the process thus I cannot service Pack my fresh installation of SQL 2000. I have done this many times before on other machines, but its not working for me this time.
At http://www.dbforums.com/ ( http://www.dbforums.com/showthread.php?t=1198047 ) someone ran into this problem and mentioned to re-install Microsoft Full-Text Search. I have followed the article http://support.microsoft.com/kb/827449 but run into problems at step 2C. At step 2C it says to start the Microsoft Search service via the Services console, but Microsoft Search was nowhere to be found.
Going back a step to Step 2B it says that all registry keys that were previously-deleted are now available and the previously-deleted 'MSSearch' folder is recreated. I took a look, but both the keys and folder WERE NOT there.
It is a fresh OS installation of MS Windows 2003 Standard SP1, 768MB RAM and is running fine.
Can you please point me in the right direction here?
Brad.
Wipeout Old Database And Start Fresh
Jun 23, 2007Hi everyone I have been trying hard to unserstand the way my isp does things sort of a communication gap I guess. The problem is I want to use the personal website... yes I know this is a SQL forum and that is what I am going to get at. I having a problems with the database aspect of the whole situtation, I can't creat a aspnetdb.mdf on their server because that name aready exists and I am assuming that it is the system and not a user that ows it. Also I can't name the other database personal.mdf either for the same reasons, so someone at the isp has had me creat a different name database during the install process of the application and that worked fine for a little while and now it does not work no more and I am getting old faster than I'd like. So here is my plan my plan is to wipe out the current database for the application on my workstation but first make a list of tables and then create a new database for that web application. So I ask is it feasible and what do I have to look out for and if it is not to much to ask what steps might take to accomplish such a task. I want to thank anybody that is willing to help me in this matter.
DKB
Fresh Installation Of Sql Server 200 Enterprise
Oct 24, 2007Can anybody provide me with the guidelines to install a fresh copy of SQL Server 2000 Enterprise Edition? The previous installation included a system account and it was on the same drive as the operating system. I remember reading this was not a good practice. Am I wrong?
thanks in advance
I Need Fresh Eyes On A Simple Sql Query
Apr 20, 2006greets, im coding a few queries to a table. im storing sets of records into the table, each set of records will haev a different batch id. so basically 2 sets of records can occupy this table at the same time, and their batch id is the main key (with 2 other fields also being PKs). i want to compare the 2 sets in the same table and get the differences:
1. records that were added
2. records that were updated
3. records that were deleted
ive written queries for the added records, and the updated records but i cant get the query for finding deleted records. the logic looks good to me but im obviously missing something so i could use a fresh pair of eyes.
here is the table def:
Code:
CREATE TABLE [dbo].[UPCXREF_BATCH] (
[BATCH_ID] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[CHAIN_CODE] [char] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[UPC] [char] (16) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[ITM_CODE] [char] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ITM_CATEGORY] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CREATE_DATE] [datetime] NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[UPCXREF_BATCH] WITH NOCHECK ADD
CONSTRAINT [PK_UPCXREF_BATCH] PRIMARY KEY CLUSTERED
(
[BATCH_ID],
[CHAIN_CODE],
[UPC]
) ON [PRIMARY]
GO
and here are the queries ive gotten so far
Code:
/* This section retrieves all UPDATED records */
SELECT 'U' as FLAG, b2.CHAIN_CODE as CHAIN_CODE, b2.UPC, b2.ITM_CODE, b2.ITM_CATEGORY
FROM UPCXREF_BATCH b2 INNER JOIN UPCXREF_BATCH b1
ON b2.CHAIN_CODE=b1.CHAIN_CODE
AND b2.UPC=b1.UPC
AND (b2.ITM_CODE<>b1.ITM_CODE OR b2.ITM_CATEGORY <> b1.ITM_CATEGORY )
WHERE b2.BATCH_ID='BTC0002' AND b1.BATCH_ID='BTC0001'
/* This section retrieves all NEW records */
SELECT 'A' as FLAG, CHAIN_CODE, UPC, ITM_CODE, ITM_CATEGORY
FROM UPCXREF_BATCH
WHERE BATCH_ID='BTC0002'
AND (CHAIN_CODE NOT IN (SELECT CHAIN_CODE FROM UPCXREF_BATCH WHERE BATCH_ID='BTC0001')
OR UPC NOT IN (SELECT UPC FROM UPCXREF_BATCH WHERE BATCH_ID='BTC0001'))
here was my attempt to get deleted records which looks like it makes sense but isnt working
Code:
SELECT 'D' as FLAG, CHAIN_CODE, UPC, ITM_CODE, ITM_CATEGORY
FROM UPCXREF_BATCH
WHERE BATCH_ID='BTC0001'
AND CHAIN_CODE NOT IN (SELECT CHAIN_CODE FROM UPCXREF_BATCH WHERE BATCH_ID='BTC0002')
AND UPC NOT IN (SELECT UPC FROM UPCXREF_BATCH WHERE BATCH_ID='BTC0002')
here batch 'BTC0001' is the older set of records that already existed and batch 'BTC0002' is the new set we just inserted into the table. am i missing something else?
Fresh 2005 Install Best Practices
Oct 16, 2007I have just completed a default install of MS SQL Server 2005 x64 edition on a 1 processor Quad core, 8GB, Windows Server 2003 Standard x64 R2 machine in an Active Directory Domain. It has 2 arrays configured with 2) 36Gb drives mirrored for the OS as C: and 3) 147Gb drives in a RAID5 array as E: drive.
I am simply the server guy who was asked to install SQL server for some projects in the future. The company will be bring in people to to the dev.
I wanted to setup the server as best as I can and so I've been searching around for "best practices" for fresh installs.
Anyone have any suggestions on things to configure/setup to improve the performance of the default install?
In addition, I want to redirect the databases to the E: drive and separate them from the OS drive (C:). Does anyone have any information on doing this? I have searched and found a few articles, but thought I would ask first.
It is probably noticeable that I have limited SQL configuration skills.
Thanks,
A simple IT guy trying to build a system right