Exec A Stored Procedure And Not Wait For Response
Sep 20, 2005
Is there a way to execute a stored procedure and have it move on without waiting for a response from the stored procedure. I am trying to create a button on a webpage that will execute a stored procedure but the procedure takes to long to run and my page times out. Instead I would like the button to start the procedure and the webpage look at a table of data. When the table of data is empty then I will know the stored procedure is complete. Is this possible?
View 1 Replies
ADVERTISEMENT
Jun 11, 2007
Hi. We are migrating a mainframe datacom database to SQL Server. One of our client-server applications already uses SQL Server. This application uses a middleware product to query and update the datacom database being migrated. We are considering using Service Broker to replace the middleware.
In many cases the client does not need a response provided the message is queued and will eventually get delivered. However, in some cases the client would like to wait for the message to be processed before proceeding. Is there an easy way to both submit and optionally wait for a response - with data - in a single stored procedure? If client does not want to continue to wait, is there a way to use a procedure to check for the returned message later?
We have not used Service Broker before and are doing for a "sanity" check before proceeding. We do not want to tightly couple the two databases at this time.
View 4 Replies
View Related
Dec 14, 2007
I am tring to fiqure out how i can run a TSQL and the have it start again 60 sec after it compleats, without me have to push the button. I just need it to loop over and over until my data is deleted. I have to do it this way so my site will still allow customers to login and I need the break so they can. Any help would be great.
View 3 Replies
View Related
Nov 6, 2007
I am building a stored procedure that changes based on the data that is available to the query. See below.
The query fails on line 24, I have the line highlighted like this.
Can anyone point out any problems with the sql?
------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------
This is the error...
Msg 8114, Level 16, State 5, Procedure sp_SearchCandidatesAdvanced, Line 24
Error converting data type varchar to numeric.
------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------
This is the exec point...
EXEC [dbo].[sp_SearchCandidatesAdvanced]
@LicenseType = 4,
@PositionType = 4,
@BeginAvailableDate = '10/10/2006',
@EndAvailableDate = '10/31/2007',
@EmployerLatitude = 29.346675,
@EmployerLongitude = -89.42251,
@Radius = 50
GO
------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------
This is the STORED PROCEDURE...
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [dbo].[sp_SearchCandidatesAdvanced]
@LicenseType int = 0,
@PositionType int = 0,
@BeginAvailableDate DATETIME = NULL,
@EndAvailableDate DATETIME = NULL,
@EmployerLatitude DECIMAL(10, 6),
@EmployerLongitude DECIMAL(10, 6),
@Radius INT
AS
SET NOCOUNT ON
DECLARE @v_SQL NVARCHAR(2000)
DECLARE @v_RadiusMath NVARCHAR(1000)
DECLARE @earthRadius DECIMAL(10, 6)
SET @earthRadius = 3963.191
-- SET @EmployerLatitude = 29.346675
-- SET @EmployerLongitude = -89.42251
-- SET @radius = 50
SET @v_RadiusMath = 'ACOS((SIN(PI() * ' + @EmployerLatitude + ' / 180 ) * SIN(PI() * p.CurrentLatitude / 180)) + (COS(PI() * ' + @EmployerLatitude + ' / 180) * COS(PI() * p.CurrentLatitude / 180) * COS(PI()* p.CurrentLongitude / 180 - PI() * ' + @EmployerLongitude + ' / 180))) * ' + @earthRadius
SELECT @v_SQL = 'SELECT p.*, p.CurrentLatitude, p.CurrentLongitude, ' +
'Round(' + @v_RadiusMath + ', 0) AS Distance ' +
'FROM ProfileTable_1 p INNER JOIN CandidateSchedule c on p.UserId = c.UserId ' +
'WHERE ' + @v_RadiusMath + ' <= ' + @Radius
IF @LicenseType <> 0
BEGIN
SELECT @v_SQL = @v_SQL + ' AND LicenseTypeId = ' + @LicenseType
END
IF @PositionType <> 0
BEGIN
SELECT @v_SQL = @v_SQL + ' AND Position = ' + @PositionType
END
IF LEN(@BeginAvailableDate) > 0
BEGIN
SELECT @v_SQL = @v_SQL + ' AND Date BETWEEN ' + @BeginAvailableDate + ' AND ' + @EndAvailableDate
END
--SELECT @v_SQL = @v_SQL + 'ORDER BY CandidateSubscriptionEmployerId DESC, CandidateFavoritesEmployerId DESC, Distance'
PRINT(@v_SQL)
EXEC(@v_SQL)
-----------------------------------------------------------------------------------------------------------------
View 4 Replies
View Related
Nov 5, 2007
Hello
Which is faster :
to write a a big stored procedure with if conditions, or to separate them and call them using exec??
i.e:
if @id=1 insert into ....else if @id=2 update...-----------------------orif @id=1 exec InsertProcedureelse if @id=2 exec UpdateProcedurePlease help
View 8 Replies
View Related
Jan 10, 2006
Hi,
Hope someone can help me out here - I'm trying to design a data flow in SSIS which relies in part on a stored procedure as a data source - I'm running it through OLE DB.
Sure Exec MystoredProc works fine on preview and on parsing, but it refuses to acknowledge any of the columns, when I go to Edit-->Columns everything is blank.
Just out of interest - the reason I am using a stored procedure is because I dump the data into a temp table and then amend a couple of the columns to make it the same as my other database (for example where len(field) = 6 then field = '0000' + field).
Possibly I'm better off taking the raw data through the OLE connection and then transforming it through SSIS, but my gut feeling is I should minimise what I'm dumping into SSIS and offload the processing onto the local DB. Any thoughts?
Thanks
Rich
View 6 Replies
View Related
Aug 20, 2007
Hi,
I'm new to SSIS and SQL Server 2005 and this is now driving me very mad!!
I have an OLE DB Command in my data flow task that I want to update a table with. I have looked round this forum and on Google and just can not find a solution or what I am doing wrong. So any help would be great!
The ole db command calls a stored procedure with two input variables:
exec stp_updedgrsholds status, temp_cr_num
from debugging the ssis it says it has updated 4 rows and also from doing a data view, the data it is updating seems all correct.
but nothing gets updated in the database.
If I call the stored procedure the following way
exec stp_updedgrsholds 'C', 87
It updates fine! I have tried a number of different way with @ symbols and assignment p_status = @status
but nothing seems to work.
Any ideas are much appreciated.
Ninder Bassi
View 4 Replies
View Related
May 23, 2007
hi,
how do I exec stored procedure that accept parameter and return a single value?
here is example of report
stu_id = ******
stu_name = ****
subject | marks
aa****** | call sp_mark and return student mark for that particular student id and subject
bb****** | call sp_mark and return student mark for that particular student id and subject
cc****** | call sp_mark and return student mark for that particular student id and subject
thks,
View 3 Replies
View Related
Apr 29, 2004
Hi there
i have a stored procedure like this:
CREATE PROCEDURE SP_Main
AS
SET NOCOUNT ON
BEGIN TRANSACTION
exec SP_Sub_One output
exec SP_Sub_Two output
IF @@ERROR = 0
BEGIN
-- Success. Commit the transaction.
Commit Tran
END
ELSE
Rollback Tran
return
GO
now the problem is, when i execute the stored procedure's inside the main stored procedure, and these sub sp's has an error on it, the main stored procedure doesnt rollback the transation.
now i can put the "begin transation" in the two sub stored procedure's. The problem is
what if the first "SP_Sub_One" executed successfully, and there was error in "SP_Sub_Two"
now the "SP_Sub_One" has been executed and i cant rollback it... the error occured in the
"SP_Sub_Two" stored procedure ... so it wont run ... so there will be error in the data
how can i make a mian "BEGIN TRANSACTION" , that even it include the execution of stored procedure in it.
Thanks in advance
Mahmoud Manasrah
View 1 Replies
View Related
Oct 2, 2007
I have this code in a stored procedure: DECLARE @SQLString VarChar(200)
SET @SQLString = 'SELECT ' + @LookupField + ' FROM ' + @DBTable + ' WHERE (' + @IDField + ' = ''' + @IDValue + ''')'
Exec (@SQLString)
it works fine - with just one issue - I must grant select permission on the table.
Is there a way to do this WITHOUT granting the select permissions?
View 7 Replies
View Related
Mar 12, 2008
I Have a problem When I execute a stored procedure from query analyzer
(Exec storedname @parameter1='', @Parameter2='') it take 7 min. and I stop running
If I copy stored procedure , past it in Query analyzer and declare parameters
it take 3 sec.
View 3 Replies
View Related
Feb 12, 2008
Hello,
If I grant execute permissions on stored procedures in a database and the proc in turn creates tables in the DB, and if the user is not a db_owner, will the procedure be allowed to create those tables? or will the stored procs fail?
Thanks
Arun
View 4 Replies
View Related
Feb 21, 2004
Hi everybody, I would like to know if it's possible to execute a stored procedure, passing it parameters, using not CommandType.StoredProcedure value of sqlcommand, but CommandType.Text.
I tried to use this:
sqlCmd.CommandType = CommandType.Text
sqlCmd.Parameters.Add(sqlPar)
sqlCmd.ExecuteNonQuery()
With this sql command:
"exec sp ..."
I wasn't able to make it to work, and I don't know if it's possible.
Another question:
if it's not possible, how can I pass a Null value to stored procedure?
This code:
sqlPar = new SqlParameter("@id", SqlDbType.Int)
sqlPar.Direction = ParameterDirection.Output
cmd.Parameters.Add(sqlPar)
sqlPar = new SqlParameter("@parent_id", DBNull)
cmd.Parameters.Add(sqlPar)
doesn't work, 'cause I get this error:
BC30684: 'DBNull' is a type and cannot be used as an expression.
How can I solve this?
Bye and thanks in advance.
P.S. I would prefer first method to call a stored procedure ('cause I could call it with 'exec sp null' sql command, solving the other problem), but obviusly if it's possible...=)
Sorry for grammatical mistakes.
View 9 Replies
View Related
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
View 4 Replies
View Related
Feb 13, 2008
Hi,
Our report is working fine with data loaded from a stored procedure (#1) that contains a fairly simple Select statement. We need the same report to work with a dataset loaded from a stored procedure (#2) that uses 'Exec sp_executesql @queryString'. Unfortunately, attempts to call the latter cause an error in the report. From everything that I've read, there should be no difference between datasets created using either method. Any ideas what could be getting in the way of the latter?
I have doublechecked that the dynamic query is returning a valid dataset and that all the columns are in the same format as sp #1. The designer shows the dataset and the report with the data loaded, but the live system produces an error.
Any help is much appreciated.
Debbie
View 4 Replies
View Related
Feb 8, 2008
Hi
I've used a temporary table in the stored procedure
I've created it as DECLARE @Temp TABLE (id INT)
in select clause I've used this temp table through the joins..
But I've also used a varchar variable to store my criteria...
which I'm building in the stored procedure
so inorder to use it in the where clause I used
EXEC ('SELECT ....................
FROM @Temp T
LEFT OUTER JOIN ...
LEFT OUTER JOIN ...
WHERE ' + @Criteria )
Unfortunately this is not working.
Giving the errror
Must declare the variable '@TempT'.
I had to use Temporary table using #, which I feel is really waste of memory...
Is there a way by which I can use my Table variable in the EXEC statement.
Thanks In advance
View 4 Replies
View Related
Jul 23, 2005
Hello,Is it possible to EXEC stored procedure from a query?I want to execute stored procedure for every line of SELECT resulttable.I guess it's possible with cursors, but maybe it's possible to make iteasier.Give an example, please.Thank you in advance.Hubert
View 2 Replies
View Related
Apr 26, 2008
I have a temporary table with multiple records and a Stored Procedure requiring a value. In a Stored Procedure, I want to loop through records in the table and use the value from each record read as input to another Stored Procedure. How do I do this?
View 7 Replies
View Related
May 14, 2008
Hello,
I have a stored procedure where I run an insert statement. I want to knwo if it is possible to do it using a variable for the table name (either in-line or with an EXEC statement without building a string first and executing that string. See examples of what I am talking about in both cases below:
I want to be able to do this (with or without the EXEC) :
------------------------------------------------------------------------------------
DECLARE @NewTableNameOut as varchar(100)
Set @NewTableNameOut = 'TableToInsertInto'
EXEC(
Insert Into @NewTableNameOut
Select * From tableToSelectFrom
)
------------------------------------------------------------------------------------
I can not do the above because it says I need to declare/set the @NewTableNameOut variable (assuming it is only looking at this for the specific insert statement and not at the variable I set earlier in the stored procedure.
I can do it like this by creating a string with the variable built into the string and then executing the string but I want to know if I can do it like I have listed above.
------------------------------------------------------------------------------------
DECLARE @NewTableNameOut as varchar(100)
Set @NewTableNameOut = 'TableToInsertInto'
EXEC(
'Insert Into ' + @NewTableNameOut + ' ' +
'Select * From tableToSelectFrom'
)
------------------------------------------------------------------------------------
It is not an issue for my simple example above but I have some rather large queries that I am building and I want to run as described above without having to build it into a string.
Is this possible at all?
If you need more info please let me know.
View 1 Replies
View Related
Jan 17, 2008
Hi all,
In my SQL Server Management Studio Express (SSMSE), I executed the following sql code suuccessfully:
--insertNewRocord.sql--
USE shcDB
GO
CREATE PROC sp_insertNewRecord @procPersonID int,
@procFirstName nvarchar(20),
@procLastName nvarchar(20),
@procAddress nvarchar(50),
@procCity nvarchar(20),
@procState nvarchar(20),
@procZipCode nvarchar(20),
@procEmail nvarchar(50)
AS INSERT INTO MyFriends
VALUES (@procPersonID, @procFirstName, @procLastName, @procAddress,
@procCity, @procState, @procZipCode, @procEmail)
GO
EXEC sp_insertNewRecord 7, 'Peter', 'Wang', '678 Old St', 'Detroit',
'Michigon', '67899', 'PeterWang@yahoo.com'
GO
=======================================================================
Now, I want to insert a new record into the dbo.Friends table of my shcDB by executing the following T-SQL and Visual Basic 2005 codes that are programmed in a VB2005 Express project "CallshcDBspWithAdoNet":
--Form1.vb--
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.SqlTypes
Public Class Form1
Public Sub InsertNewFriend()
Dim connectionString As String = "Integrated Security-SSPI;Persist Security Info=False;" + _
"Initial Catalog=shcDB;Data Source=.SQLEXPRESS"
Dim connection As SqlConnection = New SqlConnection(connectionString)
connection.Open()
Try
Dim command As SqlCommand = New SqlCommand("sp_InsertNewRecord", connection)
command.CommandType = CommandType.StoredProcedure
EXEC sp_insertNewRecord 6, 'Craig', 'Utley', '5577 Baltimore Ave',
'Ellicott City', 'MD', '21045', 'CraigUtley@yahoo.com'
Console.WriteLine("Row inserted: " + _
command.ExecuteNonQuery().ToString)
Catch ex As Exception
Console.WriteLine(ex.Message)
Throw
Finally
connection.Close()
End Try
End Sub
End Class
===========================================================
I ran the above project in VB 2005 Express and I got the following 5 errors:
1. Name 'EXEC' is not declared (in Line 16 of Form1.vb)
2. Method arguments must be enclosed in parentheses (in Line 16 of Form1.vb)
3. Name 'sd-insertNewRecord' is not declared. (in Line 16 of Form1.vb)
4.Comma, ')', or a valid expression continuation expected (in Line 16 of Form1.vb)
5. Expression expected (in Line 16 of Form1.vb)
============================================================
I know that "EXEC sp_insertNewRecord 6, 'Craig', 'Utley', '5577 Baltimore Ave',
'Ellicott City', 'MD', '21045', 'CraigUtley@yahoo.com' "in Line 16 of Form1.vb is grossly in error.
But I am new in doing the programming of T-SQL in VB 2005 Express and I am not able to change it.
Please help and advise me how to correct these problems.
Thanks in advance,
Scott Chang
View 22 Replies
View Related
Aug 10, 2007
Hello! I am new poster and I hope I am posting this question in the right forum.
I have an application (winform written in c#) that has tons of interaction (lots of triggers and stored procs) with sql server (SQL Express, SQL 2000 & SQL Server 2005).
I want to monitor when a stored procedure starts executing and then display something until the execution ends. I was thinking of displaying a modal form or if not a non-modal form and just play around with the Z-order. I thought of utilizing the Query Notification service but I believe it is only available in SQL Server 2005. My guess is that a CLR will be the best way to go.
Also, is it possible to put this in a dll and then just add the dll to the application?
I would appreciate it if I could get some input/advice on this matter (or some sample codes if someone has done something similar).
Thanks a lot!
View 2 Replies
View Related
Jan 14, 2007
Hi all,
This is a problem for that I lost a weekend to reach to this conclusions:
Here is the scenario:
I need a file downloaded from a FTP server on my SQL 2005 DB Server to process it in several ways. This file is tens of MBs (let's say 50MB). I set up a CLR stored procedure to bring it locally on the machine.
Inside this procedure, call it getFtpFile, I use the FtpWebRequest to issue the commands I need to the FTP server (get the file size and download the file). For reading and writing I use a StreamReader sr and a StreamWriter. sw
I read the remote file in block with:
sr.ReadBlock(buf, 0, blockSizeBytes);
Everything work file until the last block that, most probably will not be of size blockSizeBytes, but smaller. So, what happens is that when I read this last block there is an exception raised from the call saying:
The underlying connection was closed: An unexpected error occurred on a receive.
This happens only if:
-> the code runs inside SQL (calling the SP) (it runs perfect on normal tet environment where I have another executable and call the getFtpFile method in the CLR dll);
-> 100 seconds (100000 miliseconds) (exactly) have passed (small files do not experiencing this problem)
I thought the reader writer scheme is not perfect and implemented again with a simple FileStream.WriteByte(response.GetResponseStream().Read());
This approach, even if very slow, still has the problem after 100 seconds, in a CLR SQL environment, when it reads the at the last character.
I have used all the combinations of KeepAlive and response.Closose(). Problems still there.
Conclusion: There must be a 100 seconds timeout that immediately after my last byte has been received, closes the connection and the stream of the response crashes during this last call.
Thank you all for the help!
View 2 Replies
View Related
Mar 1, 2008
hi i have given you the ado.net code to retrive a image from the database.
I have tried using linq and i am getting a error in
Response.BinaryWrite(rs.image) in LINQ.
so i have used the ado.net which i dont want to use.
can you pls help me how to correct the error.it says cannot convert linq.binary data...
thanks
Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim MyReader As SqlDataReaderDim MySqlConnection As SqlConnection
Dim MySqlCommand As SqlCommandMySqlConnection = New SqlConnection("server=(local)SQLExpress;Integrated Security=SSPI;database=customs")MySqlCommand = New SqlCommand("select * from tbl_temp_importer_owner where TIN=555", MySqlConnection)
MySqlConnection.Open()
MyReader = MySqlCommand.ExecuteReader()Do While (MyReader.Read())
Response.ContentType = MyReader.Item("PersonImageType")Response.BinaryWrite(MyReader.Item("PersonImage"))///Error come here in linq
Loop
MySqlConnection.Close()Response.Write("Person info successfully retrieved!")
End Sub
View 4 Replies
View Related
Jul 20, 2005
HelloI need to find out what stored procedure was executed when I click ona button in a third party application, is there a way to find thisinformation out in some sort of log?Some kind of profiling to see what procedure was executed and withwhat parameters.Kind RegardsMattwww.fruitsalad.org
View 1 Replies
View Related
Nov 9, 2000
Hi,
My DTS package performs the following:
1. 4 transformations to transform data from Sybase tables A,B,C,D to the temp tables in MSSQL
tmpA, tmpB,tmpC,tmpD
2. Next, I have a task to run 4 stored procedures to load the 4 tmp* tables to actual tables A,B,C,D
(so the task is "exec spA, exec spB, exec spC, exec spD".
3. There are 9/26/280/10000 records in the tables A,B,C,D
4. each stored procedure basically checks whether the record in the tmp* tables exist in the actual
table baesd on the primary key and then perform an insert/update.
The strange thing is:
1. All 9 records in the tmpA is loaded to A. Only 17 records from tmpB is loaded to B
2. The same codes "exec spA, exec spB, exec spC, exec spD", if copied to query analyzer,
they all run to completion, ie all 10000+ records are loaded. so there is no pbm with the data.
3. If I "split" the task such that task1 loads A and task2 loads B,C,D (and task2 runs after task1)
now again all data for A is loaded, but 25 records from B is loaded
4. I tried to catch the @@error in the stored procedure for the insert/update statements
but there's no error. Most importantly, the stored procedures run fine in query analyzer
Is there some sort of timeout or buffer issue here that is causing this strange behaviour.
All help is appreciated.
Thanx
Jes
View 3 Replies
View Related
Aug 8, 2007
Hi: I would like to know if I invoke a stored procedure using "EXEC [database].[user].[StoredProcedure] param1, param2..." is just like to use the ADO.NET objects: SqlCommand myCommand = new SqlCommand("StoredProcedure", myConnection);myCommand.CommandType = CommandType.StoredProcedure;myCommand.Parameters.Add("@param1", param1);myCommand.Parameters.Add("@param2", param2); ..... Thanks
View 3 Replies
View Related
Mar 21, 2008
I've been bangging my head against the wall on this one and its probably something really easy but for the love of god, I can't figure it out.
I have the following stored procedure
ALTER PROCEDURE [dbo].[InsertUserProjectPhase]
(
@ProjectPhaseID int,
@ProductionEngineerUserID int,
@QuantityCompleted decimal(18, 2),
@BillableHours decimal(18, 2),
@TotalWorkingHours decimal(18, 2),
@DateUpdated datetime,
@UpdatedByUserID int,
@IsDeleted bit
)
AS
SET NOCOUNT OFF;
INSERT INTO User_Project_Phase
(ProjectPhaseID, ProductionEngineerUserID, QuantityCompleted, BillableHours, TotalWorkingHours, DateUpdated, UpdatedByUserID, IsDeleted)
VALUES (@ProjectPhaseID,@ProductionEngineerUserID,@QuantityCompleted,@BillableHours,@TotalWorkingHours,@DateUpdated,@UpdatedByUserID,@IsDeleted);
SELECT ID FROM User_Project_Phase WHERE (ID = SCOPE_IDENTITY())
How can I call the above stored procedure (from within another stored procedure) AND save get the return value (the ID). I tried the following:
exec @newProjectPhaseID = dbo.InsertProjectPhase @ProjectID=3796,@PhaseID=@PhaseID,@EstimatedQuantity=@EstimatedQuantity,
@EstimatedDuration=@EstimatedDuration,@TotalQuantityCompleted=@TotalQuantityCompleted,
@TotalBilledQuantity=@TotalBilledQuantity,@QuantityType=@QuantityTypeID,@StatusID=@StatusID,
@CreatedByUserID=18569
Where @newProjectPhaseID was declared at the top as int. The numbers you see in there will be replaced with variables. They are there now because I'm executing the sp directly in management studio.
Of course, the above doesn't work. I was hoping that the return value form InsertProjectPhase would be saved in @newProjectPhaseID but I get an error.
Any help will be appreciated. THANKS A MILLION!
-Psion
View 2 Replies
View Related
Sep 30, 2004
I need some help with the following store proc, something is wrong but I just dont see it.
Thanks
btw I am no expert at sp so something might be complety wrong.
ALTER PROCEDURE dbo.GetSearchByDateRange
(
@strColumnNamenvarchar (50),
@dtDate1 Date,
@dtDate2 Date
)
as
EXEC ('SELECT * FROM Customers WHERE ' + @strColumnName + ' BETWEEN ' + ''' + @dtDate1 + ''' + ' AND ' + ''' + @dtDate2 + '''')
View 7 Replies
View Related
Aug 7, 2007
Hi Guys, I created the following stored proc which calls another stored procs as shown below.
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------
Create Procedure [dbo].[rpt_CompareDB](@tbl_name varchar(400),@DB_name1 varchar(400),@DB_name2 varchar(400))
AS
declare @result1 varchar(8000),
@result2 varchar(8000),
@table_name varchar (400),
@DB1 varchar(400),
@DB2 varchar(400),
@table_final1 varchar(400),
@table_final2 varchar(400),
@DB_detail1 varchar(400),
@DB_detail2 varchar(400)
set @table_name = @tbl_name
set @DB1 = @DB_name1
set @DB2 = @DB_name2
set @DB_detail1 = @DB1 + '.mfgq_live'
set @DB_detail2 = @DB2 + '.mfgq_live'
set @table_final1 = @DB_detail1 + '.dbo.' +@table_name
set @table_final2 = @DB_detail2 + '.dbo.' +@table_name
exec GetColumnNamesString @DB_detail1,@table_name,@result1 output
exec GetColumnNamesString @DB_detail2,@table_name,@result2 output
exec CompareTables @table_final1,@table_final2,@result1,@result2
-------------------------------------------------------------------------------------------------------------------------------------------------------------------
but, when I tried to excute it, I am getting an error.
Here is how I'm trying to exec the stored proc.
execute dbo.rpt_CompareDB
@tbl_name = 'Attribute'
,@DB_name1 = 'Green'
,@DB_name2 = 'devlue02'
FYI, It works fine if you run the query itself instead of executing the stored proc.
Any idea is appreciated
View 3 Replies
View Related
Jul 22, 2015
I use new query to execute my store procedure but didnt return any value is that any error for my sql statement??
USE [Pharmacy_posicnet]
GO
/****** Object: StoredProcedure [dbo].[usp_sysconf] Script Date: 22/07/2015 4:01:38 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[usp_sysconf]
[Code] ....
View 6 Replies
View Related
Jul 10, 2006
I have the following stored proc that is providing the following error - Server: Msg 156, Level 15, State 1, Line 79[Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near the keyword 'ORDER'.
The sp works fine if the DateOfBirth elements are removed. I think it is something to do with the way in which the date variable is incorporated into the string that will be executed by the EXEC command but I'm unsure as to what is wrong.
CREATE PROCEDURE dbo.GetPersonsByName (@FirstName varchar(50)=NULL, @FamilyName varchar(50)=NULL, @DateOfBirth datetime=NULL)
AS
EXEC ('SELECT PersonId, Title, FirstName, FamilyName , AltFamilyName, Sex, DateOfBirth, Age, DateOfDeath, CauseOfDeath, Height, Weight, ABO, RhD, Comments, LocalIdNo, NHSNo, CHINo, Hospital, HospitalNo, AltHospital, AltHospitalNo, EthnicGroup, Citizenship, NHSEntitlement, HomePhoneNo, WorkPhoneNo, MobilePhoneNo, CreatedBy, DateCreated, UpdatedBy, DateLastUpdated
FROM vw_GetPersons WHERE FirstName LIKE ''%' + @FirstName + '%'' AND FamilyName LIKE ''%' + @FamilyName + '%'' AND DateOfBirth = '+ @DateOfBirth +' ORDER BY FamilyName, FirstName')
GO
View 12 Replies
View Related
Apr 17, 2001
I have a set of stored procedures copied into several databases (similar table structures but different data). I would like to avoid maintaining so many versions of the same procedures, so want to put the stored procs in a common database and execute it from there, e.g.
exec common_db..the_procedure
However, when you do that, the procedure executes in the context of common_db, not in the context of the calling proc's database. Is there a way of calling a procedure so that the calling proc's context is used?
View 1 Replies
View Related
Dec 4, 2000
I am working on a project to re-code SQL Anywhere to T-SQL (SQL Server 2000) and am encountering many instances where the original code contained a SELECT statement that calls a stored procedure. T-SQL does not allow calling a procedure from a SELECT statement as far as I can see. Does anyone have any good ideas on working around this??
Thanks
View 1 Replies
View Related
