I have a test publication of two tables. Both tables are supposed to use a custom update stored procedure
(you know, "Replace UPDATE commands with this stored procedure call:")
When updating Table1 the system calls my custom stored stored procedure.
Here is a small excerpt captured by the Profiler when updating Table1:
Looks good. I see that my custom update proc was used (usp_FNLC_MSupd_Table1).
Now, when updating Table2 (Table2 is pretty identical to Table1) Profiler sends this back and the update on the subscriber fails:
exec sp_executesql N'delete from "Table2" where "Dates3ID" = @P1 insert into "Table2" values (@P2, @P3) ', N'@P1 int,@P2 int,@P3 datetime', 8, 8, 'Mar 24 2006 12:00:00:000AM'
Then..
exec sp_executesql N'insert into "Table1" values (@P1, @P2) ', N'@P1 int,@P2 datetime', 8, 'Mar 24 2006 12:00:00:000AM'
Go
This is what the distribution agent throws back:
Insert Error: Column name or number of supplied values does not match table definition.
(Source: DFBPBSO (Data source); Error number: 213)
So, why isn't the article Table2 using my custom update proc? All I do is updates, no deletes or inserts. No matter what I do for this article, it will not use the custom update proc.
I'm looking at writing some customized insert, update and delete stored procs for a replication target. For various reasons I would like to write a "one size fits all" custom stored proc for each of these tasks.
It looks like I can get the data values passed as parameters just fine.
I was wondering if there's a way to also pass the source schema and table name as parameters, or to determine these on the fly in my all purpose stored procs. Some replication products refer to these types of values as "tokens" that can be included in the replication data stream sent to the target.
I can adjust the source database replication publications, and article definitions, but I cannot modify the actual source database tables to include these as values in data columns. It is possible a view that contains these elements as strings might fly, but I was hoping to avoid cluttering the source database.
I was looking to modify how the INSERT happens with regards to replication only to find my solution in the proc itself. When I edit the proc this is what I am displayed in SQL QA or EM:
create procedure "sp_MSins_dboMEETING";2 @c1 int,@c2 int,@c3 int,@c4 varchar(250),@c5 datetime,@c6 datetime,@c7 bit,@c8 char(1),@c9 datetime,@c10 bit,@c11 bit,@c12 bit,@c13 datetime,@c14 smallint,@c15 datetime,@c16 smallint,@c17 binary(8),@c18 bit,@c19 bit,@c20 bit,@c21 varchar(1000) as if exists ( select * from "dbo"."MEETING" where "MEETING_ID" = @c1 ) begin update "dbo"."MEETING" set "MEETING_TYPE_ID" = @c2,"MEETING_STATUS_ID" = @c3,"TITLE" = @c4,"START_DATE" = @c5,"END_DATE" = @c6,"PUBLISH_IND" = @c7,"GROUP_IND" = @c8,"PUBLISH_DATE" = @c9,"MY_ADVISORS" = @c10,"SUBMITTED_IND" = @c11,"ACTIVE_IND" = @c12,"CREATE_DATE" = @c13,"CREATED_BY" = @c14,"LAST_UPDATE_DATE" = @c15,"LAST_UPDATED_BY" = @c16,"DATE_INDEXED" = @c17,"ON_DEMAND_IND" = @c18,"NOT_REPORTED_IND" = @c19,"MAJOR_PROJECT_IND" = @c20,"MAJOR_PROJECT_COMMENT" = @c21 where "MEETING_ID" = @c1 end else begin insert into "dbo"."MEETING" ( "MEETING_ID","MEETING_TYPE_ID","MEETING_STATUS_ID","TITLE","START_DATE","END_DATE","PUBLISH_IND","GROUP_IND","PUBLISH_DATE","MY_ADVISORS","SUBMITTED_IND","ACTIVE_IND","CREATE_DATE","CREATED_BY","LAST_UPDATE_DATE","LAST_UPDATED_BY","DATE_INDEXED","ON_DEMAND_IND","NOT_REPORTED_IND","MAJOR_PROJECT_IND","MAJOR_PROJECT_COMMENT" ) values ( @c1,@c2,@c3,@c4,@c5,@c6,@c7,@c8,@c9,@c10,@c11,@c12 ,@c13,@c14,@c15,@c16,@c17,@c18,@c19,@c20,@c21 ) end
GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO
Is the second version listed at the bottom like a comment or can it actually get called? I am going to script out all of these procs and save them, and then remove the first version (listed at the top) and the use the second version since it does what I need. I just thought it was interesting to see two stored procedures in a single definition, never seen the "PROC_NAME";2 notation, have you? If so please tell me what it does, is it just a way to create a second version of the procedure in a comment type fashion or is it used another way?
Replication is failing while applying the snapshot as the stored procs are out of sync ( the objects referenced in the stored procedures are no more exists in the database) . Is there any easy way to identify the out of sync procedures so that I can exclude these stored procedures from the articles list. I am having around 1000 procs and is not possible to test them by executing.
I'm trying to create a custom resolver for merge replication exactly like in the MS example.
It seems to work, but only ONE time. If I change, insert or delete a record in a table the second time, the subscriber monitor comes with the following errors:
Error messages:
Attempted to read or write protected memory. This is often an indication that other memory is corrupt. (Source: MSSQL_REPL, Error number: MSSQL_REPL-2147199411)
The Merge Agent encountered an error when executing code in the 'UpdateHandler' method implemented in the business logic handler 'D:Program FilesMicrosoft SQL Server90COMMyResolver.dll'. Ensure that the overridden 'UpdateHandler' method has been properly implemented in the business logic handler. (Source: MSSQL_REPL, Error number: MSSQL_REPL-2147199411)
This last error is of course dependant on my action (update, delete, insert).
My code is -exactly- like the example (I just stripped out the log message).
Does anyone know why I am "trying to read or write protected memory" ?
The thing is that I'm trying to create an application that detects if a table changes. Is this the right way to do this anyway or are there better solutions?
I have gone through BOL and various online resources including this one and I can't find any examples on how to use custom conflict resolvers to generate a result row which is a combination of the rows to be merged.
BOL says it can be done, but suitable examples are missing. I have found various posts requesting examples from up to a year ago, but can see no replies with relevant information
In particular I would like to see examples of
1) A stored procedure based custom conflict resolver
I'm trying to create a custom bootstrapper for SQL Express SP 2 Which enables the replication features of SQL Express. I read an article telling me that I could use the same command line arguments in the bootstrapper as does SQL Server 2008 unattended installation
How to install SQL Server 2008 from the command prompt :
The following properties have been set: Property: [AdminUser] = true {boolean} Property: [ProcessorArchitecture] = Intel {string} Property: [VersionNT] = 5.1.2 {version} Running checks for package 'Windows Installer 3.1', phase BuildList The following properties have been set for package 'Windows Installer 3.1': Running checks for command 'WindowsInstaller3_1WindowsInstaller-KB893803-v2-x86.exe' Result of running operator 'VersionGreaterThanOrEqualTo' on property 'VersionMsi' and value '3.1': true Result of checks for command 'WindowsInstaller3_1WindowsInstaller-KB893803-v2-x86.exe' is 'Bypass' 'Windows Installer 3.1' RunCheck result: No Install Needed Running checks for package '.NET Framework 3.5', phase BuildList Reading value 'Install' of registry key 'HKLMSoftwareMicrosoftNET Framework SetupNDPv3.51033' Read integer value 1 Setting value '1 {int}' for property 'DotNet35InstallSuccess' The following properties have been set for package '.NET Framework 3.5': Property: [DotNet35InstallSuccess] = 1 {int} Running checks for command 'DotNetFX35dotNetFx35setup.exe' Result of running operator 'ValueEqualTo' on property 'DotNet35InstallSuccess' and value '1': true Result of checks for command 'DotNetFX35dotNetFx35setup.exe' is 'Bypass' '.NET Framework 3.5' RunCheck result: No Install Needed Running checks for package 'SQL Server 2005 Express Edition SP2 (x86) Build for Project Endeavor', phase BuildList Running external check with command 'C:DOCUME~1CarrollDLOCALS~1TempVSD381.tmpSQLEXPRESS_PROJECT_ENDEAVORSqlExpressChk.exe' and parameters '' Process exited with code 1 Setting value '1 {int}' for property 'SQLExpressInstalled' The following properties have been set for package 'SQL Server 2005 Express Edition SP2 (x86) Build for Project Endeavor': Property: [SQLExpressInstalled] = 1 {int} Running checks for command 'SQLEXPRESS_PROJECT_ENDEAVORSQLEXPR.EXE' Result of running operator 'ValueEqualTo' on property 'SQLExpressInstalled' and value '0': false Result of running operator 'VersionGreaterThanOrEqualTo' on property 'VersionNT' and value '5.1': true Result of checks for command 'SQLEXPRESS_PROJECT_ENDEAVORSQLEXPR.EXE' is 'Bypass' Running checks for command 'SQLEXPRESS_PROJECT_ENDEAVORSQLEXPR.EXE' Result of running operator 'ValueEqualTo' on property 'SQLExpressInstalled' and value '0': false Result of running operator 'VersionLessThan' on property 'VersionNT' and value '5.1': false Result of running operator 'ValueEqualTo' on property 'AdminUser' and value 'false': false Result of running operator 'ValueExists' on property 'Version9x': false Result of running operator 'VersionLessThan' on property 'VersionNT' and value '5.1.2': false Result of running operator 'ValueNotEqualTo' on property 'ProcessorArchitecture' and value 'Intel': false Result of checks for command 'SQLEXPRESS_PROJECT_ENDEAVORSQLEXPR.EXE' is 'Install' Running checks for command 'SQLEXPRESS_PROJECT_ENDEAVORSQLEXPR.EXE' Result of running operator 'ValueEqualTo' on property 'SQLExpressInstalled' and value '0': false Result of running operator 'VersionLessThan' on property 'VersionNT' and value '5.2': true Result of checks for command 'SQLEXPRESS_PROJECT_ENDEAVORSQLEXPR.EXE' is 'Bypass' 'SQL Server 2005 Express Edition SP2 (x86) Build for Project Endeavor' RunCheck result: Install Needed EULA for components 'SQL Server 2005 Express Edition SP2 (x86) Build for Project Endeavor' was accepted. Copying files to temporary directory "C:DOCUME~1CarrollDLOCALS~1TempVSD381.tmp" Copying from 'C: empEndeavorInstallerSQLEXPRESS_PROJECT_ENDEAVORSQLEXPR.EXE' to 'C:DOCUME~1CarrollDLOCALS~1TempVSD381.tmpSQLEXPRESS_PROJECT_ENDEAVORSQLEXPR.EXE' Verifying file integrity of C:DOCUME~1CarrollDLOCALS~1TempVSD381.tmpSQLEXPRESS_PROJECT_ENDEAVORSQLEXPR.EXE WinVerifyTrust returned 0 File trusted Running checks for package 'SQL Server 2005 Express Edition SP2 (x86) Build for Project Endeavor', phase BeforePackage Running external check with command 'C:DOCUME~1CarrollDLOCALS~1TempVSD381.tmpSQLEXPRESS_PROJECT_ENDEAVORSqlExpressChk.exe' and parameters '' Process exited with code 1 Setting value '1 {int}' for property 'SQLExpressInstalled' The following properties have been set for package 'SQL Server 2005 Express Edition SP2 (x86) Build for Project Endeavor': Property: [SQLExpressInstalled] = 1 {int} Running checks for command 'SQLEXPRESS_PROJECT_ENDEAVORSQLEXPR.EXE' Result of running operator 'ValueEqualTo' on property 'SQLExpressInstalled' and value '0': false Result of running operator 'VersionLessThan' on property 'VersionNT' and value '5.1': false Result of running operator 'ValueEqualTo' on property 'AdminUser' and value 'false': false Result of running operator 'ValueExists' on property 'Version9x': false Result of running operator 'VersionLessThan' on property 'VersionNT' and value '5.1.2': false Result of running operator 'ValueNotEqualTo' on property 'ProcessorArchitecture' and value 'Intel': false Result of checks for command 'SQLEXPRESS_PROJECT_ENDEAVORSQLEXPR.EXE' is 'Install' 'SQL Server 2005 Express Edition SP2 (x86) Build for Project Endeavor' RunCheck result: Install Needed Verifying file integrity of C:DOCUME~1CarrollDLOCALS~1TempVSD381.tmpSQLEXPRESS_PROJECT_ENDEAVORSQLEXPR.EXE WinVerifyTrust returned 0 File trusted Installing using command 'C:DOCUME~1CarrollDLOCALS~1TempVSD381.tmpSQLEXPRESS_PROJECT_ENDEAVORSQLEXPR.EXE' and parameters '-q /norebootchk /qn reboot=ReallySuppress addlocal=all SECURITYMODE=SQL SCCCHECKLEVEL=IncompatibleComponents:1;MDAC25Version:0 FEATURES=SQL SQLSVCACCOUNT="NT AUTHORITYSYSTEM" SQLSVCPASSWORD="Bade#doo" SQLSYSADMINACCOUNTS="sa" AGTSVCACCOUNT="NT AUTHORITYNetwork Service" INSTANCEDIR="C:Program FilesMicrosoft SQL Server" SQLBACKUPDIR="C:Program FilesMicrosoft SQL ServerBackup" INSTALLSQLDATADIR="C:Program FilesMicrosoft SQL Servermssql.1mssqldata" /instancename=SQLEXPRESS /SQLSVCStartuptype=2 SQLAUTOSTART=1 ERRORREPORTING=2 ADDUSERASADMIN=0' Process exited with code 110 Status of package 'SQL Server 2005 Express Edition SP2 (x86) Build for Project Endeavor' after install is 'InstallFailed'
Is there a way to namespace groups of stored procs to reduce confusion when using them?
For C# you can have ProjectName.ProjectSection.Classname when naming a class. I am just wondering if there is a way to do the same with SQL stored procs. I know Oracle has packages and the name of the package provides a namespace for all of the stored procs inside it.
I am creating a custom transformation component, and a custom user interface for that component.
In my custom UI, I want to show the custom properties, and allow users to edit these properties similar to how the advanced editor shows the properties.
I know in my UI I need to create a "Property Grid". In the properties of this grid, I can select the object I want to display data for, however, the only objects that appear are the objects that I have already created within this UI, and not the actual component object with the custom properties.
How do I go about getting the properties for my transformation component listed in this property grid?
CREATE PROCEDURE PROC1 AS BEGIN SELECT A.INTCUSTOMERID,A.CHREMAIL,B.INTPREFERENCEID,C.CHR PREFERENCEDESC FROM CUSTOMER A INNER JOIN CUSTOMERPREFERENCE B ON A.INTCUSTOMERID = B.INTCUSTOMERID INNER JOIN TMPREFERENCE C ON B.INTPREFERENCEID = C.INTPREFERENCEID WHERE B.INTPREFERENCEID IN (6,7,2,3,12,10) ORDER BY B.INTCUSTOMERID
END
IF I AM USING THIS PROC AS I/P TO ANOTHER PROC THEN IT GIVES NO PROBLEM AS I CAN USE ?
BUT IF , I USE ANOTHER PROC SIMILAR TO THE FIRST ONE WHICH GIVES SLIGHTLY DIFFERENT RESULTS AND GIVES TWO SETS OF RESULTS,THEN WE HAVE A PROBLEM,HO TO SOLVE THIS :-
CREATE PROCEDURE MY_PROC AS BEGIN SELECT A.INTCUSTOMERID,A.CHREMAIL,B.INTPREFERENCEID,C.CHR PREFERENCEDESC FROM CUSTOMER A INNER JOIN CUSTOMERPREFERENCE B ON A.INTCUSTOMERID = B.INTCUSTOMERID INNER JOIN TMPREFERENCE C ON B.INTPREFERENCEID = C.INTPREFERENCEID WHERE B.INTPREFERENCEID IN (23,12,10) ORDER BY B.INTCUSTOMERID
END
SELECT A.INTCUSTOMERID,MAX(case when A.intpreferenceid = 23 then '1' else '0' end) + MAX(case when A.intpreferenceid = 12 then '1' else '0' end) + MAX(case when A.intpreferenceid = 10 then '1' else '0' end) AS PREFER FROM CUSTOMER GROUP BY A.INTCUSTOMERID ORDER BY A.INTCUSTOMERID END
WHICH NOW GIVES ME TWO SETS OF DATA , BOTH REQUIRED THEN HOW TO USE ONE SET OF RESULTS AS I/P TO ANOTHER PROC AND OTHER SET OF RESULTS AS I/P TO YET ANOTHER PROC .
What I want to accomplish is that at design time the designer can enter a value for some custom property on my custom task and that this value is accessed at executing time.
I am writing a custom task that has some custom properties. I would like to parameterize these properties i.e. read from a varaible, so I can change these variables from a config file during runtime.
I read the documentation and it says if we set the ExpressionType to CPET_NOTIFY, it should work, but it does not seem to work. Not sure if I am missing anything. Can someone please help me?
In the Editor of my custom task, under custom properties section, I expected a button with 3 dots, to click & pop-up so we can specify the expression or at least so it evaluates the variables if we give @[User::VaraibleName]
I am wondering if it is possible to execute an .exe from a stored proc. I have an application that calls a stored proc and I am wanting for that stored proc to call up an app to show certain results. I have no way of doing this on the application itself as I dont have the source code. So, since I do have access to the SQL I am wondering if it could be done there.
Can somebody provide me an argument on how nicely formatted and indented procs perform better. I am currently formatting a 1000 line proc just to understand what it does ... Maybe your argument will help convince my development team to write neat procs :)
First off, this is a cross post which is also located here: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=34073
Now, with the following stored procs and UDF, the Result is NULL and I cannot figure out why.
What I am doing is calling a stored procedure pass in an OUTPUT variable of type float. This stored proceudre then calls a different stored procedure passing the same OUTPUT variable of type float. The second stored procedure then calls a UDF passing in two variables to be multiplied ans this should set the OUTPUT variable to the result.
The UDF does return the correct result to the interior stored procedure, but the interior stored procedure does not pass the value back to the original stored procudure resultint in a Result value = NULL.
The code is below, just copy / past, execute and you will see exactly what I mean.
Any thoughts?
USE NORTHWIND GO
CREATE FUNCTION RECTANGULAR_XSECTION (@fWidth float, @fHeight float)
I am trying to improve my SQL and reduce the number of connections my website is making to the server.
Currently I have a stored procedure that gets all the games (and their details) for a single user which works fine which uses a WHERE userid = @userid which is an Int value.
I now need to create a new procedure which brings back the to all their "friends" (again by their userid). How is it best to do this? I originally tried to do a WHERE userID IN (@userid) but was unable to work out what variable type to use for @userid.
Is it best to do a single query in this way or is there a way to use the existing SP, loop through the collection of userids and join the result together into a single set to return? If an IN is the best route, what is the correct datatype for the variable?
What is the procedure to create a extended stored procedure using C#.
Actually we have created a dll file using c# and it was added to the bin folder of mssql server. Also dll file is added to the sql server using "xp_extendedproc"
But while executing the extended proc we got this error "ODBC: Msg 0, Level 16, State 1 Cannot load the DLL xp_TestSql.dll, or one of the DLLs it references. Reason: 126(The specified module could not be found.)"
can anyone help me out.... i need to compare a date that was retrieved from the database and the system date then it must be coded in the stored procs of the database.. help!!!!
I donot know how to use SQL NOCOUNT . I have written store procs for select and insert , Below are the store procs ,Can anyone tell me how to use SQL NOCOUNT in this StoreProcs??? Select: if exists (select * from dbo.sysobjects where id = object_id('[dbo].[GetAllLinks]') )drop Procedure [dbo].[GetAllLinks] GO CREATE Procedure [dbo].[GetAllLinks] AS Select * from UsefulLink Go
Insert Storeproc: if exists (select * from dbo.sysobjects where id = object_id('[dbo].[InsertLink]') )drop Procedure [dbo].[InsertLink] GOCREATE Procedure [dbo].[InsertLink] @Title varchar(100), @Location Varchar(100),@ID int OUTPUT AS Set @ID = -1Begin Transaction Insert Into UsefulLink(Title, Location) Values (@Title,@Location)if @@error <> 0 RollBack Transaction Else BEGIN Commit Transaction Select @ID = @@identity
I'm relatively new to stored procs (not to SQL or SQL Server) and I am trying to get transactions to work within a stored proc. Here is the code:
( --define the parameters that are needed @userID char(32), @userName varchar(50), @status char(10), @type char(10), @password varchar(50), @firstName varchar(100), @lastName varchar(100), @email varchar(200), @domain varchar(50), @pwdExpiry int, @badLogin int, @lastLogin datetime, @full varchar(8000), @read varchar(8000), @noaccess varchar(8000) ) AS BEGIN TRAN tmp1 --First, insert the user into the system INSERT INTO ptsUsers(UserID, UserName, Status, Type, Password, FirstName, LastName, Email, DomainName, PasswordExpiry, BadLoginAttempts, LastLoginDate) values(@userID, @userName, @status, @type, @password, @firstName, @lastName, @email, @domain, @pwdExpiry, @badLogin, @lastLogin);
--Now, we need to add the function access edges for the user declare @arrayValue char(32) declare @rightID char(32) declare @sepPos int while patindex('%,%',@full)<>0 BEGIN select @sepPos = patindex('%,%' , @full) select @arrayValue = left(@full, @sepPos - 1) -- replace the value with an empty string select @full = stuff(@full, 1, @sepPos, '')
--create and parse the new id declare @strID char(32),@tmpStr varchar(40) set @tmpStr = newid(); set @strID = REPLACE(@tmpStr,'-','')
--get the access right id for full control select @rightID = (Select AccessRightID from ptsAccessRights where Name = 'Write') if(@rightID IS NOT NULL) --insert the records that are full access INSERT INTO ptsFunctionAccessEdges(FunctionAccessEdgeID, FunctionID, AccessRightID, UserID) VALUES(@strID, @arrayValue, @rightID, @userID) else RAISERROR(50100,15,1) END COMMIT TRAN tmp1 IF @@TRANCOUNT > 0 RAISERROR(50101,15,1) ROLLBACK TRAN tmp1
The transaction does not rollback when any errors occur at all. Any advice/help?
I am not an expert in Stored Procs. I would like to build one for a product list that would return a default value without using output parameters, if possible. Ultimately, I wouldn't be opposed to it.
It currently looks like this:
CREATE PROCEDURE ProductsByVendorNo ( @VendorNum nvarchar(24) ) AS
SELECT P.ProductID AS ProductID, P.ProductShortName AS ProductName, P.ProductDesc AS ProductDesc, U.UnitDesc AS Unit, P.VendorProductNumber AS VendorNumber, V.VendorName AS VendorName, P.Price AS Price, P.ImageThumb AS ImageThumb
FROM tblProducts AS P INNER JOIN tblUnitCodes AS U ON P.UnitCode=U.UnitCode INNER JOIN tblVendors AS V ON P.VendorID=V.VendorID
WHERE V.VendorsVendorNo = @VendorNum AND P.Inactive = 0
ORDER BY ProductName, VendorNumber
I would like for it to return a default, constant value for the URL in the ImageThumb field, if this one is empty. I could not find good documentation of how to use IF statements for this case, i.e. to alter the return of just one field.
I want to "deny" create, update,and delete access on the dbo stored procs that are in the database, but do not want take away dbo owner access. is this possible?
can i create a role and deny access on a particular table in msdb? or a system table in the user table. Thus preventing the developers on the box access to update any of the dbo owned sp's and have them create their own user-owned stored procs?
I am trying to set up a DTS package that selects data from one table on server A into another table on server B. I want to do a select statement that will do the following:
select store_name from store (server A) where date_created >= (select max(date_created) from store (server B)
I use EM 7.0 to manage all of my extracts, however, the data is moving from a Syabase (adaptive Server)onto another Syabase(adaptive server) machine. Unfortunately, there is no functionality for a linked server connection.
I tried the following, (which doesn't error out), but is not displaying the source columns in the destination tab of the DTS package when setting up the transformations.
declare @maxdate datetime exec serverB.dbo.sp_max_date_from_store @maxdate select store_name from store --(server A) where date_created >= @store
Any help or suggestions would be greatly appreciated! trevorb
DECLARE @object int DECLARE @hr int DECLARE @property varchar(255) DECLARE @return varchar(255) DECLARE @output varchar(255) DECLARE @source varchar(255) DECLARE @description varchar(255) --Instantiate a Network Object EXEC @hr = sp_OACreate 'WScript.Network', @object OUT IF @hr <> 0 BEGIN EXEC sp_displayoaerrorinfo @object, @hr RETURN END -- Get a property using an output parameter. --EXEC @hr = sp_OAMethod @object, 'userDomain' --,@property OUT EXEC @hr = sp_OAGetProperty @object, 'userDomain'--, @property OUT IF @hr <> 0 BEGIN EXEC sp_displayoaerrorinfo @object, @hr RETURN END --PRINT @property
-- Destroy the object. EXEC @hr = sp_OADestroy @object IF @hr <> 0 BEGIN EXEC sp_displayoaerrorinfo @object, @hr RETURN END
I get the same behavior stated in microsoft's support site related to BUG 55840 for Sql Server 7.0. I get an error message when trying to retrieve with @property OUT parameter. And I get an Empty record set when NO Output param is supplied.
I will be taking over a database that has almost no pk's or relations(this is not my choice, but a vendors) Management is looking at stored procs to improve performance, but I am wondering if the db is in this state will there really be a gain. I am pushing for normalization first, but if anybody has any ideas or opinions I would appreciate
I have a stored proc that is run periodically which I execute with a DTS package. The problem is that I have to update a field for each record selected by this stored proc with a specific date. I need to prompt the operator for this date so I can use this value.
Ok, I've read somewhere(which I'm looking for again : ) that said that there are errors like DeadLock that kills the execution of a stored proc and there are other errors that do not necessarily kill the rest of the execution of the stored proc. Is that true? If so does anyone have any links I can read. What I'm seeing is a bad id in the foreign key and I think what is happening is that there was a unique constraint error on the first insert but the stored proc continued executing and used the bad id later on in the stored proc.
I do know I can use the @@error and will start using it but I need more proof to agree or not agree with my theory.
Thanks ahead of time for any information you can give me either way.
