Accepting Dynamic Size Of Parameters In Stored Procs

Jul 6, 2001

Hello,
Does anyone have any experience or knowledge of being able to send an
unlimited amount of parameters to a stored proc and having the stored proc
detect how many parameters were sent?

Example:
1) calling the stored proc with 2 strings:
exec sp_myproc "a","b"

2) calling the stored proc with 7 strings:
exec sp_myproc "a","b","c","d","e","f","g"

The following sp_myproc would need to be able to support an unlimited amount
of parameters as well as detect how many were sent...

Thanks in advance for any help and/or code samples given!

-- Al

View 12 Replies

Accepting Dynamic Size Of Parameters In Stored Procs

Jul 6, 2001

Hello,

Does anyone have any experience or knowledge of being able to send an
unlimited amount of parameters to a stored proc and having the stored proc
detect how many parameters were sent?

Example:
1) calling the stored proc with 2 strings:
exec sp_myproc "a","b"

2) calling the stored proc with 7 strings:
exec sp_myproc "a","b","c","d","e","f","g"

The following sp_myproc would need to be able to support an unlimited amount
of parameters as well as detect how many were sent...

Thanks in advance for any help and/or code samples given!

-- Al

View 2 Replies View Related

Accepting Dynamic Size Of Parameters In Stored Procs

Jul 6, 2001

Hello,

Does anyone have any experience or knowledge of being able to send an
unlimited amount of parameters to a stored proc and having the stored proc
detect how many parameters were sent?

Example:
1) calling the stored proc with 2 strings:
exec sp_myproc "a","b"

2) calling the stored proc with 7 strings:
exec sp_myproc "a","b","c","d","e","f","g"

The following sp_myproc would need to be able to support an unlimited amount
of parameters as well as detect how many were sent...

Thanks in advance for any help and/or code samples given!

-- Al

View 4 Replies View Related

Using Dynamic SQL In Stored Procs

Dec 5, 2007

Hi,

I have read in many articles that Dynamic SQL should not be used in Stored Procedures as it is not a Best Practice. So Can anyone tell me how to handle the following scenario without using Dynamic SQL in Procs. The scenario is as follows

I have to write a Procedure which will generate a Report from a Table. For this purpose I have to Select about 20 columns from a Table (lets say Table A). Now the report has 5 filters which are completely optional. I mean the user might fill few of the filters or all the filters or none of the Filters

So can anyone help me out in this scenario because my "where" clause is completely Dynamic and I am unable to understand how to handle my where clause without writing Dynamic Queries. Also I am trying to write this query on SQL Server 2000.

If anyone can give me some examples or articles which can be of help it would be great. Please help

Regards

Vineed

View 6 Replies View Related

Stored Procs And Parameters

Sep 10, 2001

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.

View 6 Replies View Related

Building Dynamic SQL In Stored Procs

Oct 24, 2000

We are migrating from a file-server Access Database to a SQL server backend and Access front end system. I'm using ADO to access the data off the server but am implementing most of the business logic in stored procedures. All logic was coded in VBA earlier but i'm having to move that to T-SQL for performance issues. In many cases I have dynamically constructed SQL statements in code but I'm having some trouble in T-SQL. How can I do this in T-SQL?

' This is some VB code that shows how the query differs based on a parameter.
If Me![Sorting] = 1 Then
Me![ControlNumber].RowSource = "SELECT [DVD_Projects_Table].[DVD_Number], [Title] & "" : "" & [ID_Number] AS Display,__
[DVD_Projects_Table].Date FROM [DVD_Projects_Table] __
WHERE ((([DVD_Projects_Table].System) = IIf([Forms]![DVD_Projects_Form]![SystemFilter] = 1, ""525"", ""625""))) And __
(([DVD_Projects_Table].Active) = IIf([Forms]![DVD_Projects_Form].[ActiveOnly] = True, Yes, __
[DVD_Projects_Table].[Active]))) ORDER BY [DVD_Projects_Table].Title;"
Else
Me![ControlNumber].RowSource = "SELECT [DVD_Projects_Table].[DVD_Number], [ID_Number] & "" : "" & [Title] AS Display,__
[DVD_Projects_Table].Date FROM [DVD_Projects_Table]__
WHERE ((([DVD_Projects_Table].System) = IIf([Forms]![DVD_Projects_Form]![SystemFilter] = 1, ""525"", ""625""))) And __
(([DVD_Projects_Table].Active) = IIf([Forms]![DVD_Projects_Form].[ActiveOnly] = True, Yes, __
[DVD_Projects_Table].[Active]))) ORDER BY Int(Right([ID_Number],Len([ID_Number])-4));"
End If

This is the ideal sp that would do what I want but it is obviously incorrect. How can I get this logic implemented. I need to construct an SQL query based on the input parameters in a stored procedure.

CREATE PROCEDURE [procDVDProjectsList]
@SortBy as bit,
@ActiveOnly as bit,
@SysFilter as integer
AS
SELECT
CASE @SortBy
/* Display Title first */
WHEN 0 THEN [DVD_Number], [Title] + " : " + [ID_Number] AS Display, [DVD_Projects_Table].[Date]
/* Display Number first */
WHEN 1 THEN [DVD_Number], [ID_Number] + " : " + [Title] AS Display, [DVD_Projects_Table].[Date]
END
FROM
[DVD_Projects_Table]
WHERE
CASE @SysFilter
/* List all */
WHEN 0 THEN
/* List only NTSC */
WHEN 1 THEN [DVD_Projects_Table].[System] = "525"
/* List only PAL */
WHEN 2 THEN [DVD_Projects_Table].[System] = "625"
END
AND
CASE @ActiveOnly
/* List All */
WHEN 0 THEN
/* List only active */
WHEN 1 THEN [DVD_Projects_Table].Active = True
END
ORDER BY
CASE @Sortby
/* Sort Alpha */
WHEN 0 THEN [DVD_Projects_Table].Title
/* Sort Numeric */
WHEN 1 THEN Right([ID_Number],Len([ID_Number])-4)
END

Thanks for your help,

-Sumit Malik

View 1 Replies View Related

Dynamic Code In Stored Procs.

Mar 3, 2006

Hey all,

So here's my deal, I've got a bunch of tables set up like so.

CaseName_Class
CaseName_Address
CaseName_Mailing

The casename obviously changes based on the name of the case, but all table share the "_Class", "_Address" etc.

Now, I have built apps for these to do various things, handling mail, logging claims in, whatever. And at this point in time each case has a new directory with the files inside that directory that all do the same thing. Reason why this sucks is that if I change an app in one place, I should be hanging it in about 100 other places which causes...

So, what I am attempting to do is created a stored proc in SQL to handle one of the applications, pass in a few variables and make all of the cases run off of that one proc...yay! go me.

Now...due to the fact I have to pull the case prefix name everytime the proc runs, I am kind of shooting myself in the foot being that the proc has to recompile everytime to get that casename.

The code that is currently there and works, looks like this

Code:

SET @dynSQLBox = N'SELECT @BoxCount1 = COUNT(MailingID) ' + char(10) +
'FROM ' + @Client + '_Mailing' + char(10) +
'WHERE MailEventID = ' + CAST( @MailEventID as varchar(20) ) + char(10) +
'AND MailTypeID = ' + CAST( @MailTypeID as varchar(20) ) + char(10) +
'AND box = ' + @Box

Just as an example that is looking for the max box count inside of a box inside of the specific case.

I am wondering if there is a better way to go about getting data that has to be dynamic inside of a stored proc.

I hope this makes sense, and look forward to talking about it.

Thanks!
Caden

View 1 Replies View Related

SQLDatasource, Stored Procs && Parameters

Mar 1, 2006

Hi,
I'm having an issue trying to send parameters to a SQLDatasource object. I"m creating it in code (and I need it to stay there) I have determined that the stored procs called with no parameters work fine. I start having problems when I have any parameters. Interestingly, it doesn't crash. It just fails to run (the onselected event never fires).
dsUser is a protected SQLDatasource property
repMain is a repeater on the aspx sheet
lstUser is a stored proc with one varchar(10) parameter called cSort
Here is my current code that will result in no called to StatementCompleted and no data. Pull cSort parameter from the proc and it works just fine. Obviously I'm setting it wrong.
string cSelect = "dbo.lstUser";
dsUser = new SqlDataSource();
dsUser .ConnectionString = GetConStr();
dsUser .SelectCommand = cSelect;

dsUser .SelectParameters.Add( "cSort", TypeCode.String, "" );

dsUser .Selected += StatementCompleted;
repMain.DataSource = dsUser ;

TIA,
Nick Hustak

View 6 Replies View Related

Stored Procs With Variable Number Of Parameters

Oct 26, 2006

I have received a change request for a project i am working on which im unsure if possible or not.

at the minute i have a very simple sp that simply selects data from a table by passing in one parameter

CREATE PROCEDURE PHAR_SelectGrade

@int_ID int

AS

SELECT GradeID as ID, Grade as Description, RequiresText, SendEmail, Timestamp
FROM tbl8Grade
WHERE Obsolete=0
AND GradeID = @int_ID
ORDER BY Grade

the user now wants the option to select more than one grade type (there are 100's of different grades)

so they might decide they want to see details for 2 grades, 22 grades etc. these would be selected and stored in an array (via an asp.net project) and then the stored procedure is called from within a web service

my question is how would i pass this array into the stored procedure??

i am assuming i would need to do something as follows for sp syntax, but im stumped on how i pass my array of values into the sp from the webservice and then for the sp to read the array in SQL??

CREATE PROCEDURE PHAR_SelectGrade

@myArray <what datatype?>

AS

SELECT GradeID as ID, Grade as Description, RequiresText, SendEmail, Timestamp
FROM tbl8Grade
WHERE Obsolete=0
AND GradeID IN (@myArray)
ORDER BY Grade

any ideas are greatly appreciated. or maybe its just not possible?!?

Cheers,
Craig

View 3 Replies View Related

DTS In SQL 2000 Not Accepting Parameters

Dec 6, 2001

Hi,

I am working on a DTS package in which one part is executing a SQL Task that's output is being stored in a global variable..or better yet, it WAS storing it. In the beginning it was working and I had an Active X task that would loop through the record set and write the contents in HTML to a flat file.

NOW, out of nowhere, when I press on the parameters button, I recieve the following error:

"The SQL Statement does not contain any parameters"

I made no changes and the funny thing about it is that I can still run the package and it still writes the output to the file, with different data if it applies ( it is calling a stored procedure ), I just cannot change it!

Has anybody ran into this? I have searched other discussion boards and there have been people with the same issue but I have not gotten a clear answer.

Any help would be very appreciative.

Jason...

View 1 Replies View Related

Stored Procs: Is There A Way To Divide Up (or Namespace) Groups Of Stored Procs

Jan 15, 2008

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.

View 1 Replies View Related

Newbi To SQL - Can Stored Procedure Parameters Be Dynamic?

Mar 30, 2005

I have only one table (Products) with 3 columns, PKID(int), CompanyID(int) and Product Name(nvarchar). The products table fills a CheckBoxList Control; the values of the ListItem's are the PKID of the Products Table.
When a user ticks the check boxes to search for products, I generate a comma deliminated string of ONLY CHECKED products. For example I now have a string "3,5,4,6" which represent the Product PKID's. We can assume the values are not in order.
My question is: Without knowing how many values are parsed to the stored procedure. How can I query the Products table for the CompanyID's?
I've been banging my head for days thinking how to structure such a DB, Stored Procedures and HTML Controls. Am I going about this the wrong way, some advice would be really appriciated.
Kind Regards.

View 5 Replies View Related

Do Stored Procedures Have A Limit On Number Of Parameters Or Byte Size Passed In?

Nov 21, 2007

Hi,I'm using c# with a tableadapter to call stored procedures. I'm running into a problem where if I have over a certain byte size or number of parameters being passed into my stored proc I get an exception that reads: "Cannot evaluate expression because a thread is stopped at a point where garbage collection is impossible, possibly because the code is optimized." If I remove one parameter, the problem goes away. Has anyone run into this before? Thanks,Mark

View 3 Replies View Related

Problem With Using Stored Procs As I/p To Another Stored Procs

May 7, 2004

HI,

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 ?

CREATE PROCEDURE PROC2
AS
BEGIN

CREATE TABLE #SAATHI(INTCUSTOMERID INT,CHREMAIL NVARCHAR(60),INTPREFERENCEID INT,CHRPREFERENCEDESC NVARCHAR(50))

INSERT INTO #SAATHI
EXEC PROC1

ST......1,
ST......2,

END.

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 .

CREATE PROCEDURE PROC2
AS
BEGIN

CREATE TABLE #SAATHI(INTCUSTOMERID INT,CHREMAIL NVARCHAR(60),INTPREFERENCEID INT,CHRPREFERENCEDESC NVARCHAR(50))

INSERT INTO #SAATHI
EXEC MY_PROC

ST......1,
ST......2,

END.

BUT, HERE I WANT TO USE FIRST DATASET ONLY , HOW TO USE IT ?

THANKS.

View 4 Replies View Related

SSRS 2005 - Email Report On Execution To Dynamic List With Dynamic Parameters = No Schedule

Nov 23, 2007

Hi,
I have a need to display on screen AND email a pdf report to email addresses specified at run time, executing the report with a parameter specified by the user. I have looked into data driven subscriptions, but it seems this is based on scheduling. Unfortunately for the majority of the project I will only have access to SQL 2005 Standard Edition (Production system is Enterprise), so I cannot investigate thoroughly.

So, is this possible using data driven subscriptions? Scenario is:

1. User enters parameter used for query, as well as email addresses.
2. Report is generated and displayed on screen.
3. Report is emailed to addresses specified by user.

Any tips on how to get this working?

Thanks

Mark Smith

View 3 Replies View Related

How To Search And List All Stored Procs In My Database. I Can Do This For Tables, But Need To Figure Out How To Do It For Stored Procedures

Apr 29, 2008

How do I search for and print all stored procedure names in a particular database? I can use the following query to search and print out all table names in a database. I just need to figure out how to modify the code below to search for stored procedure names. Can anyone help me out?
SELECT TABLE_SCHEMA + '.' + TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'

View 1 Replies View Related

Stored Procs Are BAD!! BAD, I Tell You!!!

Feb 5, 2005

almost choked when i read the following recent post on The Daily WTF (http://thedailywtf.com/) the other day --

Logical Tiers? That Makes No Sense! (http://thedailywtf.com/ShowPost.aspx?PostID=28959)

i don't think i can do justice to how utterly stupid that stored procedure is

read the comments and have a laugh

one of the points made was that stored procedures are bad

this twigged something in my memory, so i dug around in my bookmarks, and sure enough, here's another decent discussion about stored procs --

Stored procedures are bad, m'kay? (http://weblogs.asp.net/fbouma/archive/2003/11/18/38178.aspx)

enjoy!

View 6 Replies View Related

.exe From Stored Procs

Jun 28, 2007

Hey guys.

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.

Thanks
tibor

View 14 Replies View Related

I Need Help With Stored Procs And UDF

Apr 8, 2004

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)

RETURNS float

AS
BEGIN
RETURN (@fWidth * @fHeight)
END
GO

CREATE PROCEDURE usp_shapes_GetRectangularXSection

@fResult float OUTPUT

AS

declare @fWidth float, @fHeight float
SELECT @fWidth = 108, @fHeight = 10

SELECT @fResult = [dbo].[RECTANGULAR_XSECTION](@fWidth, @fHeight)
SELECT @fResult AS CalledProcedureOkHere

GO

CREATE PROCEDURE usp_shapes_GetXSection

@fResult float OUTPUT

AS

EXECUTE usp_shapes_GetRectangularXSection @fResult
SELECT @fResult AS CallingProcedure_NULL?
GO

declare @fResult float
EXECUTE usp_shapes_GetXSection @fResult
SELECT @fResult as OutsideCallingProcedure_NULL?
GO

DROP FUNCTION RECTANGULAR_XSECTION
GO
DROP PROCEDURE usp_shapes_GetRectangularXSection
GO
DROP PROCEDURE usp_shapes_GetXSection
GO

Mike B

View 3 Replies View Related

New To Stored Procs

Nov 13, 2007

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?

View 5 Replies View Related

SQL Stored Procs

Aug 8, 2006

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!!!!

View 10 Replies View Related

Transactions And Stored Procs

Mar 8, 2004

All,

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?

View 3 Replies View Related

IF Statement In Stored Procs... Help!

Apr 12, 2004

Hi all!

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.

Suggestions?

Any input is highly appreciated.

Thanks in advance,

Mili Skikic

View 8 Replies View Related

Security On Stored Procs On Dev Db

Mar 25, 2002

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?

this is sql7, sp3, development box.

thanks,

View 1 Replies View Related

Using Stored Procs In A DTS Package

Apr 27, 2001

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

View 1 Replies View Related

Not Normal Will Stored Procs Help

Jun 19, 2000

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

View 2 Replies View Related

Errors And Stored Procs

Jul 28, 2004

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.

DMW

View 2 Replies View Related

Views And Stored Procs.

Aug 31, 2004

Is there any performance loss by setting up a view and creating a stored procedure to return a recordset from a view versus setting up the stored procedure to return the recordset from the tables directly?

What way should I do this?

Mike B

View 2 Replies View Related

Help - Updating Stored Procs !!

Nov 23, 2006

Hi all.

First post on here, so please excuse any ignorant parts of my question :)

We've recently had to migrate an application from one server to another, and had lots of problems getting it to work (NT4/SS7 -> 2K3/SS2K5). After much digging around I found the info on SQL server 2k5 changing the SUSER_NAME to SUSER_SNAME (I think from reading the change was actually from SQL Server 2000 - that correct ?).

Anyway I updated some of our stored procedures to correct that issue, and things *seem* to be working again (<-- famous last words, I know :eek: ), but I've been trying to find details of any other changes, particularly command changes, that I may need to make to our stored procedures.

To be honest I've found the info on Microsoft's main site and technet site next to useless - all seems to discuss very generic high-level procedures for database migration etc.. I didn't even find the SUSER change on there.

Could anyone point me to some useful information on other changes to look out for (within the SP's) ??

Thanks in advance for your help, and for taking the time to read this :beer:

View 2 Replies View Related

Encrypted Stored Procs

Sep 7, 2007

Hey yall

I ran into something interesting today and was wondering how one would do this. I have some 3rd party stored procs and one was kicking out a truncate error so I took it upon myself to investigate the stored proc that was kicking out this error. So when I tried viewing the sp, I received an error:*****Encrypted object is not transferable, and script could not be generated.***** and then it brings up a blank editing screen.

First I believe this was a custom error message as it just doesnt seem like the way SQL Server would have presented it.

So how would someone prohibit viewing of a stored proc like this?

Thanks

View 3 Replies View Related

Concurrent Stored Procs?

Mar 8, 2004

Hi all

I have a stored proc that runs every 4 hours - as a job. The stored proc takes about 3-5 minutes to comple, depending on number of records.
To do testing we run that stored proc manually, also.
Sometimes 2 or more people may run the same stored proc without knowing that the other person is running. This may create duplicates entries once processed.

What I want to know is, Is there a way to check if that stored procedure is currently running. If so it wont run second time concurrently.
(May be semapohres,mutex or something like that?)

(I am trying not to use a table to store whether the stored proc is running or not)

Thanks in advance.

Rochana

View 10 Replies View Related

ARITHABORT AND STORED PROCS

May 1, 2007

I wrote a stored procedure and set it as a schedule. it has no problem when I execute this SP in query analyzer. But it will show error when I execute it in schedule job.
I think the main reason is I have created a DDL trigger that will fire when DDL_DATABASE_LEVEL_EVENTS happens. Any solution?

SELECT failed because the following SET options have incorrect settings: 'ARITHABORT'. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or query notifications and/or xml data type methods. [SQLSTATE 42000] (Error 1934). The step failed.

View 2 Replies View Related

Stored Procs Script

Jan 27, 2008

Can someone please point me in the right direction (guidance or url to a utility or article) as to how we can write a script to scan all of the stored procedures in our database for any reference to a field name were are about to change? Also, can we use such a script to do the actual change from the old field name to the new one?

Thanks!

View 3 Replies View Related