How To Execute Stored Procedure Differently Based On SQL Server Version
Oct 16, 2007
My product was developed for and works correctly on SQL Server 2000. However, when we upgraded to 2005, we found that certain system stored procedures were different, causing our product to break.
We can easily change our stored procedures to work in 2005, but we have a large client base, some of whom will be using each version. Our current solution is to check the version of SQL Server during installation and choose which script to use at that time in order to have an appropriate stored procedure for that version, but we are concerned about users who install our product with SQL Server 2000 and then upgrade to SQL Server 2005.
How can I make a stored procedure that will run differently depending on the version? I tried something like:
if (select charindex('2000', @@version)) > 0
begin -- SQL Server 2000
SELECT
...
FROM
...
WHERE
end
else -- SQL Server 2005
SELECT
...
FROM
...
WHERE
end
Unfortunately, the system tables I'm selecting from have different stuctures in the different versions (one example is msdb.dbo.sysjobschedules and msdb.dbo.sysschedules), and even though the code never gets into the SQL Server 2000 section on 2005, it parses the whole procedure for errors before allowing it to be saves and will not allow this.
Any thoughts?
View 3 Replies
ADVERTISEMENT
Aug 13, 2007
Hi All,
I am trying to create a CLR based stored procedure in C#. When i tried printing simple "Hello" from it, it works fine.
Now requirement is to run an exe file from it. For that i use process.start. But when i try to execute the procedure i get all the security execptions. Can someone please help. Following is the code snippet.
-------------------------------------------------------------------------------------------------------------------------------------------------------------
public partial class StoredProcedures
{
[Microsoft.SqlServer.Server.SqlProcedure]
public static void RunProc(string arg)
{
SqlPipe pipe = SqlContext.Pipe;
pipe.Send("Hello");
Process.Start("E: est.exe");
}
}
CREATE ASSEMBLY [RunProcess]
FROM 'RunProcess.dll'
CREATE PROCEDURE dbo.sqlclr_RunProc
(
@arg nvarchar(1024)
)
AS EXTERNAL NAME [RunProcess].[StoredProcedures].[RunProc]
--------------------------------------------------------------------------------------------------------------------------------------------------------------
Thanks
Sid
View 6 Replies
View Related
Sep 18, 2013
I am writing a number of data migration scripts, like the (simplified version) below
Code:
INSERT INTO newDatabase.dbo.DaDestinationTable(col1, col2, col3)
SELECT col1,
col27,
col13
FROM OldDatabase.dbo.DaSourceTable
There are no validation rules defined in the database (don't ask why), only in the program code. Because of the absence of validations in the database, the data migration scripts could violate business rules (NOT NULL, FK violations, domain values in a column, ...).
A solution could be that both the program and the data migration call the same stored procedure to insert records in the destination table, so the validation rules must only be defined in one place (the stored procedure).
I have never used stored procedures in a "set-based" situation. Is this even possible? The only solution I see is to call those sp's in a loop (WHILE or CURSOR). Something like
Code:
SET @my_id = -1
SELECT @my_id = MIN(id)
FROM OldDatabase.dbo.DaSourceTable
WHERE id > @my_id
WHILE @my_id IS NOT NULL
[Code] ....
For each record, the table OldDatabase.dbo.DaSourceTable is hit twice, once to get the next id (@my_id), once to get the column values. I could combine both with one SELECT script by using ROW_NUMBER(), but I doubt that that will perform faster.
I only work set-based, I have barely ever used a WHILE loop, let alone a CURSOR. What the consequences will be on performance. There are millions of records that have to be migrated.
I was thinking of CROSS APPLY, but you can't use that in combination with a SP.
View 6 Replies
View Related
Jul 20, 2005
We have a product that we were experiencing high recompile rates do totemp tables, we have re-written them to use table variables to reducerecompile rate. The problem I am having is that we have somecustomers still on SQL 7 where the table variables were not valid. Iwould like to have one installation script that would create thestored procedure with table variables on SQL Server 2000 and withouton SQL Server 7, I have been unsuccessful do to the limitation thatCREATE PROCEDURE statement cannot be in a script with any otherstatements.Was wondering if anyone else has run into this and found a way aroundit.
View 1 Replies
View Related
Oct 14, 2007
I 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 Related
Sep 29, 2007
Hi,
Please help me in this problem...
i am new to sql server..
i am using sql server version 8...(doesnot support function with retun values..)
so i have created a procedure...
-----------procedure------------------(to find next monday after 6 months)-------------------
[code]
create proc next_Monday ( @myDate DATETIME )
as
BEGIN
set @myDate = dateadd(mm, 6, @myDate)
while datepart(dw,@myDate) <> 2
begin
set @myDate = dateadd(dd, 1, @myDate)
end
select @myDate
end
go
[/code]
--------------------------------------------------------
i can able to execute this procedure separately.... working well...
but don't know how to call it inside another query....
the following throws error....
select smaster.sname, smaster.Datex, 'xxx'=(execute next_monday smaster.Datex) from smaster
please help me... how to fix this problem...
View 16 Replies
View Related
Jul 11, 2007
Hello
The following code does not function if I use SQLOLEDB if I omit the provide and default to ODBC OLE DB it works correctly. I am assume I am coding something wrong for a SQLOLEDB provide. Any help is greatly appricated.
VB Code
Public Function SqlExecuteResult(xSQL As String, sServer As String, sDatabase As String, sUserName As String, sPassword As String, sCaller As String, Optional bLog As Boolean = False) As Object
Dim oDB As Object
Dim oRS As Object
Set oDB = CreateObject("adodb.connection")
Set oRS = CreateObject("adodb.recordset")
oDB.open "driver={SQL Server};provider=sqloledb;server=" & sServer & ";database=" & sDatabase & ";uid=" & sUserName & ";pwd=" & sPassword & ";"
oRS.CursorLocation = adUseClient
oRS.CursorType = adOpenStatic
Set oRS.ActiveConnection = oDB
oRS.open xSQL
Set oRS.ActiveConnection = Nothing
Set SqlExecuteResult = oRS
oDB.Close
Set oDB = Nothing
End Function
Private Sub Form_Load()
Dim rs As Object
Set rs = SqlExecuteResult("exec NextEntry 'SentMessages'", "surecomp-bob", "pmsureus33", "sa", "", "")
MsgBox rs.fields(0)
End Sub
SQL proceedure
CREATE PROCEDURE NextEntry @CounterName Varchar(20) AS
begin
declare @counter int
select @counter = counter from counters where countername = @counterName
select @counter = @counter + 1
update counters set counter = @counter where countername = @countername
select counter from counters where countername = @counterName
End
GO
Thanks
Bob Jenkin
View 1 Replies
View Related
Sep 20, 2015
I've established linked server but can't execute stored procedure of remote server .In bother end SQL Server 2014 is using.
View 4 Replies
View Related
Oct 7, 2013
I am search for coding criteria I need create a stored procedure with execute as and along with encryption. How can I use the same ? My main motive is to create proc with execute as a user also at the same time I need to encrypt the same from other users seeing the code.
The below query is getting errors:
Create procedure testproc
with execute as 'user' and with encryption
as truncate table some table
View 3 Replies
View Related
Jul 23, 2005
I've tried several different way to execute a oracle storedprocedure from a DTS package but to no avail.I have a Linked Server setup which does bring back Oracle tables from theserver when I click on the Tables icon.Here's my DTS statement:exec omsd..OMS_TECO.SP_Callback_Update_Pkg(116);omsd is the linked serveroms_teco is the owner of the oracle stored procedureSP_Callback_Update_Pkg is the oracle stored procedure(116) is the parameter passed to the oracle stored procedureI put the above exec statement in a DTS Execute SQL Task using a Connectionthat I tried using several OLE and ODBC Data Sources. I can't seem to findthe right combination.Please Help!!!!!!!!--Message posted via http://www.sqlmonster.com
View 1 Replies
View Related
May 11, 2015
I have create a batch file to execute a stored proc to import data.
When I run it from the server (Remote Desktop) it works fine, but if I share the folder and try to run it from my pc, it doesn't do anything. I don't get an error, it just doesn't do anything. My windows user has admin rights in SQL. Why is it not executing from my PC?
View 9 Replies
View Related
Sep 15, 2015
I'm seeing where previous developers have used a single stored procedure for multiple reports, where each report required different columns to be returned. They are structured like this:
CREATE PROCEDURE dbo.GetSomeData (@rptType INT, @customerID INT)
AS
BEGIN
IF @rptType = 1
BEGIN
SELECT LastName, FirstName, MiddleInitial
[Code] ....
As you can see, the output depends on the given report type. I've personally never done this, but that's more because it's the way I learned as opposed to any hard facts to support it.
So what I'm looking for is basically 2-fold.
View 5 Replies
View Related
Jan 21, 2015
I am trying to execute a stored procedure to update a table and I am getting Invalid Object Name. I am create a cte named Darin_Import_With_Key and I am trying to update table [dbo].[Darin_Address_File]. If I remove one of the update statements it works fine it just doesn't like trying to execute both. The message I am getting is Msg 208, Level 16, State 1, Line 58 Invalid object name 'Darin_Import_With_Key'.
BEGIN
SET NOCOUNT ON;
WITH Darin_Import_With_Key
AS
(
SELECT [pra_id]
,[pra_ClientPracID]
[code]....
View 2 Replies
View Related
Jul 15, 2014
I am writing a stored procedure that takes in a customer number, a current (most recent) sales order quote, a prior (to most current) sales order quote, a current item 1, and a prior item 1, all of these parameters are required.Then I have current item 2, prior item 2, current item 3, prior item 3, which are optional.
I added an IF to check for the value of current item 2, prior item 2, current item 3, prior item 3, if there are values, then variable tables are created and filled with data, then are retrieved. As it is, my stored procedure returns 3 sets of data when current item 1, prior item 1, current item 2, prior item 2, current item 3, prior item 3 are passed to it, and only one if 2, and 3 are omitted.I would like to learn how can I return this as a one data set, either using a full outer join, or a union all?I am including a copy of my stored procedure as it is.
View 6 Replies
View Related
Sep 18, 2007
Hi all,
I have a problem while i create a proxy account.The situation is like this...There is a user who has an login in to the server.He has a stored procedure which calls some on the SSIS packages and XP_cmdshell...so this stored procedure basically load some data in to the tables .So for the login in order to execute the stored procedure as he is not a Sys admin I have created a proxy account in my account as Iam an SA and then in the proxies and in principals I selected his login name and this way I have created a credential and a proxy account.
Now the problem is if he logins with his id and password and try to execute the stored procedure it gives an error message
Server: Msg 15153, Level 16, State 1, Procedure xp_cmdshell, Line 1
The xp_cmdshell proxy account information cannot be retrieved or is invalid. Verify that the '##xp_cmdshell_proxy_account##' credential exists and contains valid information.
....so this mean the login is not able to see the proxy account.So what I did is I created a job and then in the job owner tab I have selected his login and then created a step with the type operating system (CmdExec) as I need to just execute the stored procedure and used the proxy account that I have created.
so I gave the command -- exec <stored procedure> --.
But this job fails and gives the error message as
[298] SQLServer Error: 536, Invalid length parameter passed to the SUBSTRING function. [SQLSTATE 42000]....
So now ....first My question is am I doing in a right way....if its right then why Iam not able to execute the stored procedure.
If there is any other way through which I can execute the stored procedure using a proxy account for the logins who are not sys admins....please do let me know.....
Thanks
Raja.V
View 2 Replies
View Related
Sep 13, 2001
Is there anyone out there who has successfully setup version control of stored procedures with Source Safe and Visual Interdev?
View 1 Replies
View Related
Feb 8, 1999
When distributing a SQL Server database to customers we need to be able to
tell which version of a stored procedure is currently installed on the user's machine. Does 6.5 or 7.0 provide any way to monitor versions like this?
View 1 Replies
View Related
Sep 13, 2007
Hi all,
I am trying to debug stored procedure using visual studio. I right click on connection and checked 'Allow SQL/CLR debugging' .. the store procedure is not local and is on sql server.
Whenever I tried to right click stored procedure and select step into store procedure> i get following error
"User 'Unknown user' could not execute stored procedure 'master.dbo.sp_enable_sql_debug' on SQL server XXXXX. Click Help for more information"
I am not sure what needs to be done on sql server side
We tried to search for sp_enable_sql_debug but I could not find this stored procedure under master.
Some web page I came accross says that "I must have an administratorial rights to debug" but I am not sure what does that mean?
Please advise..
Thank You
View 3 Replies
View Related
May 16, 2008
Hello,
I am hoping there is a solution within SQL that work for this instead of making round trips from the front end. I have a stored procedure that is called from the front-end(USP_DistinctBalancePoolByCompanyCurrency) that accepts two parameters and returns one column of data possibly several rows. I have a second stored procedure(USP_BalanceDateByAccountPool) that accepts the previous stored procedures return values. What I would like to do is call the first stored procedure from the front end and return the results from the second stored procedure. Since it's likely I will have more than row of data, can I loop the second passing each value returned from the first?
The Stored Procs are:
CREATE PROCEDURE USP_DistinctBalancePoolByCompanyCurrency
@CID int,
@CY char(3)
AS
SELECT Distinct S.BalancePoolName
FROM SiteRef S
INNER JOIN Account A ON A.PoolId=S.ID
Inner JOIN AccountBalance AB ON A.Id = AB.AccountId
Inner JOIN AccountPool AP On AP.Id=A.PoolId
Where A.CompanyId=@CID And AB.Currency=@CY
CREATE PROCEDURE USP_BalanceDateByAccountPool
@PoolName varchar(50)
AS
Declare @DT datetime
Select @DT=
(Select MAX(AccountBalance.DateX) From Company Company
INNER JOIN Account Account ON Company.Id = Account.CompanyId
INNER JOIN SiteRef SiteRef ON Account.PoolId = SiteRef.ID
Inner JOIN AccountBalance AccountBalance ON Account.Id = AccountBalance.AccountId
WHERE SiteRef.BalancePoolName = @PoolName)
SELECT SiteRef.BalancePoolName, AccountBalance.DateX, AccountBalance.Balance
FROM Company Company
INNER JOIN Account Account ON Company.Id = Account.CompanyId
INNER JOIN SiteRef SiteRef ON Account.PoolId = SiteRef.ID
Inner JOIN AccountBalance AccountBalance ON Account.Id = AccountBalance.AccountId
WHERE SiteRef.BalancePoolName = @PoolName And AccountBalance.DateX = @DT
Order By AccountBalance.DateX DESC
Any assistance would be greatly appreciated.
Thank you,
Dave
View 6 Replies
View Related
Apr 25, 2006
Hi,
[I'm using VWD Express (ASP.NET 2.0)]
Please help me understand why the following code, containing an inline SQL SELECT query (in bold) works, while the one after it (using a Stored Procedure) doesn't:
<asp:DropDownList ID="ddlContacts" runat="server" AutoPostBack="True" DataSourceID="SqlDataSource2"
DataTextField="ContactNameNumber" DataValueField="ContactID" Font-Names="Tahoma"
Font-Size="10pt" OnDataBound="ddlContacts_DataBound" OnSelectedIndexChanged="ddlContacts_SelectedIndexChanged" Width="218px">
</asp:DropDownList> <asp:Button ID="btnImport" runat="server" Text="Import" />
<asp:Button ID="Button2" runat="server" OnClick="btnNewAccount_Click" Text="New" />
<asp:SqlDataSource ID="SqlDataSource2" runat="server" ConnectionString="<%$ ConnectionStrings:ICLConnectionString %>"
SelectCommand="SELECT Contacts.ContactID, Contacts.ContactLastName + ', ' + Contacts.ContactFirstName + ' - ' + Contacts.ContactNumber AS ContactNameNumber FROM Contacts INNER JOIN AccountContactLink ON Contacts.ContactID = AccountContactLink.ContactID WHERE (AccountContactLink.AccountID = @AccountID) ORDER BY ContactNameNumber">
<SelectParameters>
<asp:ControlParameter ControlID="ddlAccounts" Name="AccountID" PropertyName="SelectedValue" />
</SelectParameters>
</asp:SqlDataSource>
View 3 Replies
View Related
Apr 14, 2004
Hi,
Can anybody please tell me if it is possible to execute an url from stored procedure.
thanks in advance,
preeth
View 1 Replies
View Related
Aug 10, 2000
Hello:
I have created a stored procedure that will backup and restore a database.
I would like to be able to execute this sp using public rights - any ideas?
Thanks
View 1 Replies
View Related
Jun 14, 2008
How can I use in stored procedure the faction ‘’in’’ to select values, using execute and the values must not be specified before in the stored procedure
View 6 Replies
View Related
Jul 23, 2005
How do you execute a stored procedure in MS SQL Server?I can design and execute them from MS Access dev front end, but cannot seemto find how to run them in the Enterprise Manager.TIA.~ Duane Phillips.
View 2 Replies
View Related
Mar 29, 2006
I have setup a user which has execute rights on a stored procedure. The sp is owned by dbo. The user can execute the stored procedure, but it fails, because the stored procedure calls other tables and procedures that the user does not have rights to. Is there a way to allow those procedures to execute without allowing access to everything else for the user I setup? Thanks!
View 3 Replies
View Related
Oct 27, 2006
I need to disable and move orphaned computer objects in my Active Directory. The SQL Agent has permission to do this. I have created a stored procedure for the task with intentions of executing it with sp_start_job. However, I cannot execute it in SQL 2005. How can I grant permission to this (login) to execute sp_start_job? This is all run from a web page and NOT the Query Window.
View 1 Replies
View Related
Oct 17, 2007
Please anyone help me with my question
How do I execute a stored Procedure using SSIS ?
I have a stored procedure in SQL SERVER 2005 database that I need to execute using a SSIS package using Execute SQL Task from Toolbox in Visual studio 2005
Thanks,
George
View 2 Replies
View Related
Jan 22, 2008
Hi,
I am strugling to execute a insert stored procedure on a button click. The stored procedure is taking values from a temp table and inserting them into a identical table. The procedure is expecting 1 value from a Query string, the stored procedure works as expected when hard coded.
Im completely new to this and have no idea where to begin, i have been looking through the forums for several hours and am still none the wiser.
please can someone point me in the right direction
View 13 Replies
View Related
Aug 24, 2004
hi to all,
i have a stored procedure and i want to run a program from it..
i think that i need use api functions but how can i do that..
if there is a nother ways please tell me..
thanx..
View 3 Replies
View Related
Jul 17, 2005
I have a button in a form that I want to execute a simple stored procedure to insert a single value into a database table. The Stored Procedure is:=================CREATE PROCEDURE bplog_insert_invoice_detail (@invo_Id Int) ASINSERT INTO Invoice_Detail (Invo_Id) VALUES(@Invo_Id)GO=================How do i pass the value from and text field (@invo_id) and execute the stored procedure when a button is clicked.Regards.
View 1 Replies
View Related
Oct 31, 2005
Hello,I was wondering if it is possible to dynamically execute a stored procedure; for example, in SQL, you can do:insert into Table1( id, name)select id, namefrom Table2Can you do something like:exec spProc @id = id, @name = namefrom Table1Or something like that? I know I can select a row at a time and execute, or write a program, but I was looking to see if there was an easier way.Thanks.
View 1 Replies
View Related
Dec 20, 2005
Hello, I'm having trouble trying to execute a simple stored procedure. Could someone please take a look at this and let me know where I went wrong.
Dim cn As SqlConnection = New SqlConnection("Data Source=localhost;Initial Catalog=TEST;Persist Security Info=True;User ID=sa;Password=test")
Dim cmd As SqlCommand = cn.CreateCommand
cn.Open()
cmd.CommandText = "cssp_family"
cmd.CommandType = CommandType.StoredProcedure
cmd.ExecuteNonQuery()
cn.closed
View 6 Replies
View Related
Apr 30, 2001
Within a stored procedure, is it possible to call multiple other stored procedures asychronously? For example, I'd like to execute both local and remote stored procedures, but don't want/need to wait for the output while the original stored procedure continues to execute each subsequent command.
View 1 Replies
View Related
