Make A Stored Procedure

Jan 7, 2007

Hi

In my table i have a datetime field
now i want to delete all records there are more than 1 hour old
can someone help me with this

Alvin

View 1 Replies


ADVERTISEMENT

Make A Call To AS/400 Stored Procedure From DTS

Feb 15, 2000

Can DTS make a call to a stored procedure on an AS/400 and accept data from that call. I need to access the AS/400 through OLE/DB for AS/400, execute the call to a stored procedure (the AS/400 stored procedure gets the data from DB2/400, executes some business logic, then presents the record set), and grab the record set returned and dump it into a SQL 7.0 table.

View 1 Replies View Related

How To Make BAT File Of Stored Procedure

Mar 29, 2013

I have a bulk insert script. I have a stored procedure. I saved the stored procedure as a .sql file in another folder too. I have another program that can "run external program" and the only files it will run is a .bat or .exe. I want my other program to be able to trigger the stored procedure to run. I think this means I need a .bat file.

Here is my stored procedure:

USE [EricaTraining]
GO
/****** Object: StoredProcedure [dbo].[LoadDailyAdjReport] Script Date: 03/29/2013 10:56:42 ******/
SET ANSI_NULLS ON
GO

[code]....

View 10 Replies View Related

Make The Stored Procedure To Run Daily

Jan 22, 2008



Hi,

I have created a stored procedure that will read the content of the text files of a particular folder. I need to make the stored procedure to run daily so that it will read the new files that is present in that folder. I have written a stored procedure to make the process of reading the file. But i need to know how to make the stored procedure to run daily so that it will automatically read all the files. I have got the information that it can be made possible using dts package. As i dont have any knowledge about dts package can anyone help me how to make this possible.

Thanks in advance for any help.

Regards,
Sangeetha

View 6 Replies View Related

Stored Procedure To Make Backup Of Database...

Dec 11, 2006

Can anyone point me to the right direction with the stored procedure on making a backup of the database. I am not looking for a scheduled backup. I'm looking for when the stored procedure get executed, the backup start right away. I believe it also require a username and password as well.

Thanks...

View 1 Replies View Related

Stored Procedure - How To Make View Name Dynamic

May 8, 2012

Basically, I'm working on a stored procedure which will retrieve data based on study parameter passed. The datasource is 'Views'. The name of the view is same for every study except that there is corresponding study name included. For example the views names are something like this for study abc 'v_abc_form' and for study def 'v_def_form'.

Below is the select statement I'm trying to use by declaring @study variable but not able to succeed. I'm not sure how to make the table name dynamic.

Select C1, C2, C3
From v_@study_form

View 7 Replies View Related

I Need To Make This Stored Procedure 2005 Compatible

Jun 22, 2006

Hello.

I need to quickly make this proc compatible with SQL 2005 and am struggling. I have alot of catching up to do.

Basically, it checks for Foreign Key dependencies in a database. There might be a better way to do this in SQL 2005 but for know I really need to get this working. Any help is verry much appreciated!

--------------------------------------------------------

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

ALTER Procedure aes.Check_Dependent_Rows_Exist
(@RowID int,
@has_rows int OUTPUT
)
AS
BEGIN
DECLARE @Colname varchar(200), @Tablename varchar(200)
DECLARE @cnt int
DECLARE @temp_row int
DECLARE @owner varchar(25)
DECLARE @ownerid int
DECLARE @lstrSql nvarchar(2000)
-- #1: declare cursor for maximum performance
DECLARE lcur CURSOR LOCAL FORWARD_ONLY KEYSET READ_ONLY FOR

SELECT syscolumns.Name, OBJECT_NAME(fkeyid) AS FkeyTableName
FROM sysreferences
INNER JOIN syscolumns ON sysreferences.fkeyid=syscolumns.id AND fkey1=syscolumns.colid
WHERE OBJECT_NAME(rkeyid)= 'customer'

OPEN lcur
CREATE TABLE #Temp (DependentRows int)
-- #2: only return a bit indicating if dependant rows exist or not

SET @has_rows = 0


FETCH NEXT FROM lcur INTO @Colname,@Tablename

WHILE @@FETCH_STATUS = 0
BEGIN
SET @temp_row = 0

SELECT @ownerid = uid from sysobjects where name = @Tablename
SELECT @owner = [name] from sysusers where uid = @ownerid

SET @lstrSql= 'insert into #Temp Select DependentRows = Count(' + @Colname + ') from ' + @owner + '.' + @TableName + ' where ' +
@Colname + ' =' + CAST(@RowID AS VARCHAR(16)) + ''
--print @lstrSql
EXEC (@lstrSql)
SELECT @temp_row = ISNULL(DependentRows,0) FROM #Temp
IF @temp_row > 0
BEGIN
-- #3: stop processing as soon as dependant rows are found to exist
SET @has_rows = 1
BREAK
END

FETCH NEXT FROM lcur INTO @Colname,@TableName

END
deallocate lcur
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

-----------------------------------------------------------------------

error
Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'dbo.order_detail'.
Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'dbo.invoice_header'.
Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'dbo.order_header'.
Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'dbo.payment'.
Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'dbo.cash_on_account'.

(1 row(s) affected)

Cannot grant, deny, or revoke permissions to sa, dbo, information_schema, sys, or yourself.

View 6 Replies View Related

How To Make A Stored Procedure Into SSIS Package

May 21, 2008



hi,
I need to convert a stored procedure in to a SSIS package, do any body have an idea on this. thanks in advance

View 1 Replies View Related

Make A Dynamic Cursor In A Stored Procedure

Jul 9, 2006

I need im my aplication to meke a "Cursor" in a execution of a stored procedure.

For sample:

In a table with a report definition I have the "Fileds, From, Group, Order " clausulas and I need make a cursor with a contents of this fileds.

How can I do ???

My code:

Declare @idRelat int, @cmd_FROM nvarchar(1024), @cmd_Det nvarchar(50)
SELECT @idRelat = idRelat, @cmd_Det = cmd_DET
FROM Relatórios WHERE Nome = @p_Relat

Declare @Tot_Col smallint, @Tot_Lin smallint, @Campos smallint,
@Aux_Select nvarchar(1024), @Aux_Group nvarchar(1024), @Aux_Order nvarchar(1024)

Select @Tot_Col = 0
Select @Tot_Lin = 0
Select @Campos = 0
Select @Aux_Select = "SELECT " + @cmd_DET + "AS Soma"
Select @Aux_Group = "GROUP BY "
Select @Aux_Order = "ORDER BY "
Declare @a_Local char(1), @a_Linha smallint, @a_Campo nvarchar(50)
Declare cur_Aux insensitive cursor for
SELECT Local, Linha, Campo
From Relatórios_Margens
WHERE (idRelat = @idRelat)
ORDER BY Local, Linha
Open cur_Aux
Fetch cur_Aux into @a_Local, @a_Linha, @a_Campo
While @@FETCH_status = 0 begin
If @a_Local = "C"
Select @Tot_Col = @Tot_Col + 1
Else
Select @Tot_Lin = @Tot_Lin + 1
Select @Campos = @Campos + 1
If @Aux_Group <> "GROUP BY " begin
Select @Aux_Group = @Aux_Group + ", "
If @Aux_Order <> "ORDER BY " begin
Select @Aux_Order = @Aux_Order + ", "
Select @Aux_Select = sSelect + ", " + @a_Campo + " AS Campo" + @Campos
Select @Aux_Group = @Aux_Group + @a_Campo
Select @Aux_Order = @Aux_Order + @a_Campo
Fetch cur_Aux into @a_Local, @a_Linha, @a_Campo
End
Select @Aux_Select = @Aux_Select
-- <<<< MONTA COMANDO SQL
Select @Aux_Select = @Aux_Select + " " + @cmd_FROM + " " + @p_Filtro + " " + @Aux_Group + " " + @Aux_Order
Declare @Cursor_Aux cursor
Set @Cursor_Aux = cursor for @Aux_Select
Open @Cursor_Aux

Not working !!!!

View 1 Replies View Related

Mssql 2005. How To Make Update Stored Procedure ?

May 1, 2008

Hi ~
I made simple stored procedure that is to update user information following as...
ALTER PROCEDURE UpdateUserProfile(  @user_id uniqueidentifier,  @user_firstname nvarchar(50),  @user_lastname nvarchar(50),  @user_birth nvarchar(20),   @user_gender nvarchar(20)  )
AS 
 UPDATE user_profile    SET         user_firstname = @user_firstname,     user_lastname = @user_lastname,     user_birth = @user_birth,     user_gender =  @user_gender  WHERE user_id = @user_id  RETURN
When I tried to save this procedure, I faced on "Invalid Object : UpdateUserProfile" error message.
What's the problem ? 
 

View 2 Replies View Related

Stored Procedure Make String From Table Field

Jul 20, 2005

Hallo !I have a Table with a column "ordernumber"ordernumberA12A45A77A88Is it possible to create a stored procedure which makes a string of these column ?Result: string = ('A12','A45','A77','A88')Thanks !aaapaul

View 3 Replies View Related

Make SQL Server Distinguish Between Uppercase And Lowercase Characters In A Stored Procedure?

May 10, 2007

I would like SQL Server 2000 to distinguish between uppercase and lowercase letters, but only within a single stored procedure. Also, at the end of the sp, I want the original collation to be restored. How will I implement this in my sp?

View 3 Replies View Related

Calling A Stored Procedure Inside Another Stored Procedure (or Nested Stored Procedures)

Nov 1, 2007

Hi all - I'm trying to optimized my stored procedures to be a bit easier to maintain, and am sure this is possible, not am very unclear on the syntax to doing this correctly.  For example, I have a simple stored procedure that takes a string as a parameter, and returns its resolved index that corresponds to a record in my database. ie
exec dbo.DeriveStatusID 'Created'
returns an int value as 1
(performed by "SELECT statusID FROM statusList WHERE statusName= 'Created') 
but I also have a second stored procedure that needs to make reference to this procedure first, in order to resolve an id - ie:
exec dbo.AddProduct_Insert 'widget1'
which currently performs:SET @statusID = (SELECT statusID FROM statusList WHERE statusName='Created')INSERT INTO Products (productname, statusID) VALUES (''widget1', @statusID)
I want to simply the insert to perform (in one sproc):
SET @statusID = EXEC deriveStatusID ('Created')INSERT INTO Products (productname, statusID) VALUES (''widget1', @statusID)
This works fine if I call this stored procedure in code first, then pass it to the second stored procedure, but NOT if it is reference in the second stored procedure directly (I end up with an empty value for @statusID in this example).
My actual "Insert" stored procedures are far more complicated, but I am working towards lightening the business logic in my application ( it shouldn't have to pre-vet the data prior to executing a valid insert). 
Hopefully this makes some sense - it doesn't seem right to me that this is impossible, and am fairly sure I'm just missing some simple syntax - can anyone assist?
 

View 1 Replies View Related

How To Make JOB Without Xp_regread Procedure

Sep 20, 2005

Hi!!

I removed 'xp_regread' procedure for security

after that I can't make JOB in Enterprise Manager

How can I make JOB without 'xp_regread' procedure

thanks~

View 1 Replies View Related

Calling A Stored Procedure From ADO.NET 2.0-VB 2005 Express: Working With SELECT Statements In The Stored Procedure-4 Errors?

Mar 3, 2008

Hi all,

I have 2 sets of sql code in my SQL Server Management Stidio Express (SSMSE):

(1) /////--spTopSixAnalytes.sql--///

USE ssmsExpressDB

GO

CREATE Procedure [dbo].[spTopSixAnalytes]

AS

SET ROWCOUNT 6

SELECT Labtests.Result AS TopSixAnalytes, LabTests.Unit, LabTests.AnalyteName

FROM LabTests

ORDER BY LabTests.Result DESC

GO


(2) /////--spTopSixAnalytesEXEC.sql--//////////////


USE ssmsExpressDB

GO
EXEC spTopSixAnalytes
GO

I executed them and got the following results in SSMSE:
TopSixAnalytes Unit AnalyteName
1 222.10 ug/Kg Acetone
2 220.30 ug/Kg Acetone
3 211.90 ug/Kg Acetone
4 140.30 ug/L Acetone
5 120.70 ug/L Acetone
6 90.70 ug/L Acetone
/////////////////////////////////////////////////////////////////////////////////////////////
Now, I try to use this Stored Procedure in my ADO.NET-VB 2005 Express programming:
//////////////////--spTopSixAnalytes.vb--///////////

Public Class Form1

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

Dim sqlConnection As SqlConnection = New SqlConnection("Data Source = .SQLEXPRESS; Integrated Security = SSPI; Initial Catalog = ssmsExpressDB;")

Dim sqlDataAdapter As SqlDataAdapter = New SqlDataAdaptor("[spTopSixAnalytes]", sqlConnection)

sqlDataAdapter.SelectCommand.Command.Type = CommandType.StoredProcedure

'Pass the name of the DataSet through the overloaded contructor

'of the DataSet class.

Dim dataSet As DataSet ("ssmsExpressDB")

sqlConnection.Open()

sqlDataAdapter.Fill(DataSet)

sqlConnection.Close()

End Sub

End Class
///////////////////////////////////////////////////////////////////////////////////////////

I executed the above code and I got the following 4 errors:
Error #1: Type 'SqlConnection' is not defined (in Form1.vb)
Error #2: Type 'SqlDataAdapter' is not defined (in Form1.vb)
Error #3: Array bounds cannot appear in type specifiers (in Form1.vb)
Error #4: 'DataSet' is not a type and cannot be used as an expression (in Form1)

Please help and advise.

Thanks in advance,
Scott Chang

More Information for you to know:
I have the "ssmsExpressDB" database in the Database Expolorer of VB 2005 Express. But I do not know how to get the SqlConnection and the SqlDataAdapter into the Form1. I do not know how to get the Fill Method implemented properly.
I try to learn "Working with SELECT Statement in a Stored Procedure" for printing the 6 rows that are selected - they are not parameterized.




View 11 Replies View Related

Make Procedure For Stock-quantity

Sep 3, 2007

 helo alll...,this is my data:my table is item(productid,stock) ,order(customerid,no_order), and orderdetail (no_order,productid,quantity) example: order and orderdetail displayed in gridview....order is displayed like this:                 customerid            no_orderdetail                  A                        1detail                  B                        2  when i click detail in row 2, it's display orderdetail:no_order               productid              quantity       2                            c1                       2  2                            p1                       3 i have make all this is ok. but i want to decrease stok in item with quantity in orderdetail.my code in procedure like:CREATE PROCEDURE [dbo].[order_item](@productid AS varchar,@quantity AS INT)ASBEGINBEGIN TRANSACTIONDECLARE @no_order AS INTDECLARE @stock AS INTINSERT INTO [Orderdetail]([ProductId],[Quantity])VALUES(@productId,@quantity)SET @no_order = SCOPE_IDENTITY()SET @Stock = (SELECT [Stock] FROM [item] WHERE [ProductId] = @productId)UPDATE [item]SET[Stock] = @Stock - @quantityWHERE[ProductId] = @productIdCOMMIT TRANSACTIONENDreturn it can't work..how about his true code in store procedure?ok..., thx.. 

View 8 Replies View Related

Make Procedure To Check Balance &> Or &< Totalcost

Sep 9, 2007

 helo  all...,i have create procedure can decrease totalcost from order table(database:games.dbo) with balance in bill table(database:bank.dbo). my 2 database in same server is name "boy"
i have 2 database like: bank.dbo and games.dbo 
 in games.dbo, have a table name is order(user_id,no_order,date,totalcost)
in bank.dbo, have a table name like is bill(no_bill,balance)
this is a list of bill table
no_bill            balance
111222            200$
222444            10$ 
this is a list of order table 
user_id            no_order            date            totalcost
    a                     1                  1/1/07             50$
when customer insert no_bill(111222) in page and click a button,  then bill table became
no_bill            balance
111222            150$
222444            10$
when customer insert no_bill(222444) in page and click a button, then message "sorry, your balance is not enough" 
mystore procedure like:ALTER PROCEDURE [dbo].[pay](    @no_bill AS INT,    @no_order AS int,    @totalcost AS money)ASBEGIN    BEGIN TRANSACTION            DECLARE @balanc AS money                SET @balanc= (SELECT [balance] FROM Bank.dbo.bill WHERE [no_bill] = @no_bill)        UPDATE [bank.dbo.bill]        SET            [balance] = @balanc - @totalcost        WHERE            [no_bill] = @no_bill    COMMIT TRANSACTIONEND it can decrease money in bank, but i want it ceck money if balance > totalcost, so balance-totalcost,if balance<totalcost,so error message"sorry, your balance not enough"is it can make in procedure?thx... 

View 2 Replies View Related

T-SQL (SS2K8) :: How To Make Code Into Cursor Within Procedure

Feb 3, 2015

i wanna create a procedure for P& L cost sheet , i had done that procedure now include a cursor instead of replacing sql queries .

create procedure pl_test
@fmdate datetime,
@todate datetime,
@categ varchar(2000)
begin
create table #temp

[code]....

how to include cursor on if part and else part

View 2 Replies View Related

How To Make Stored Proc Changes

May 18, 2007

Hi,
Just moved to SQL2005 from SQL 2000. Thought I would drop some bad habits along the move. With SQL 2000, when I had stored proc changes to take to the client, I would open EnterpriseManager on my laptop while plugged into their network. I would open their database on their network, and the database on my laptop that had their changes, and cut and post the changes. Really want to stop that.

Now, with SQL2005, I see it wants me to modify the SP, then run it as a script.

What is the best way for me to take stored proc changes from my SQL2005 server in the office, and get them to the clients SQL2005 server?

Thanks

View 5 Replies View Related

T-SQL (SS2K8) :: One Stored Procedure Return Data (select Statement) Into Another Stored Procedure

Nov 14, 2014

I am new to work on Sql server,

I have One Stored procedure Sp_Process1, it's returns no of columns dynamically.

Now the Question is i wanted to get the "Sp_Process1" procedure return data into Temporary table in another procedure or some thing.

View 1 Replies View Related

SQL Server 2014 :: Embed Parameter In Name Of Stored Procedure Called From Within Another Stored Procedure?

Jan 29, 2015

I have some code that I need to run every quarter. I have many that are similar to this one so I wanted to input two parameters rather than searching and replacing the values. I have another stored procedure that's executed from this one that I will also parameter-ize. The problem I'm having is in embedding a parameter in the name of the called procedure (exec statement at the end of the code). I tried it as I'm showing and it errored. I tried googling but I couldn't find anything related to this. Maybe I just don't have the right keywords. what is the syntax?

CREATE PROCEDURE [dbo].[runDMQ3_2014LDLComplete]
@QQ_YYYY char(7),
@YYYYQQ char(8)
AS
begin
SET NOCOUNT ON;
select [provider group],provider, NPI, [01-Total Patients with DM], [02-Total DM Patients with LDL],

[Code] ....

View 9 Replies View Related

Connect To Oracle Stored Procedure From SQL Server Stored Procedure...and Vice Versa.

Sep 19, 2006

I have a requirement to execute an Oracle procedure from within an SQL Server procedure and vice versa.

How do I do that? Articles, code samples, etc???

View 1 Replies View Related

Make Stored Proc Faster

Jun 17, 2004

Anyone got any alternatives to the SQL Statement below that would make it run a bit faster:

ALTER PROCEDURE sproc_ReturnAvailability

@ExtractDate DateTime,
@DateFrom DateTime,
@DateTo DateTime,
@96hrPlusFlag int,
@AppointmentsCount int OUTPUT

AS

IF @96hrPlusFlag = 0

BEGIN
SELECT @AppointmentsCount = COUNT(tbl_SurgerySlot.SurgerySlotKey)
FROM tbl_SurgerySlot
INNER JOIN tbl_SurgerySlotDescription ON (tbl_SurgerySlot.Label = tbl_SurgerySlotDescription.Label AND tbl_SurgerySlot.PracticeCode = tbl_SurgerySlotDescription.PracticeCode)
AND tbl_SurgerySlot.ExtractDate = @ExtractDate
AND tbl_SurgerySlot.StartTime BETWEEN @DateFrom AND @DateTo
AND tbl_SurgerySlotDescription.NormalBookable = 1
AND tbl_SurgerySlot.SurgerySlotKey NOT IN(
SELECT tbl_Appointment.SurgerySlotKey
FROM tbl_Appointment
WHERE tbl_Appointment.ExtractDate = @ExtractDate
AND tbl_Appointment.Deleted = 0
AND tbl_Appointment.Cancelled = 0
)
END
ELSE

BEGIN
IF @96hrPlusFlag = 1

SELECT @AppointmentsCount = COUNT(tbl_SurgerySlot.SurgerySlotKey)
FROM tbl_SurgerySlot
INNER JOIN tbl_SurgerySlotDescription ON (tbl_SurgerySlot.Label = tbl_SurgerySlotDescription.Label AND tbl_SurgerySlot.PracticeCode = tbl_SurgerySlotDescription.PracticeCode)
AND tbl_SurgerySlot.ExtractDate = @ExtractDate
AND tbl_SurgerySlot.StartTime >@DateTo
AND tbl_SurgerySlotDescription.NormalBookable = 1
AND tbl_SurgerySlot.SurgerySlotKey NOT IN(
SELECT tbl_Appointment.SurgerySlotKey
FROM tbl_Appointment
WHERE tbl_Appointment.ExtractDate = @ExtractDate
AND tbl_Appointment.Deleted = 0
AND tbl_Appointment.Cancelled = 0
)

END

Cheers...

View 1 Replies View Related

Grab IDENTITY From Called Stored Procedure For Use In Second Stored Procedure In ASP.NET Page

Dec 28, 2005

I have a sub that passes values from my form to my stored procedure.  The stored procedure passes back an @@IDENTITY but I'm not sure how to grab that in my asp page and then pass that to my next called procedure from my aspx page.  Here's where I'm stuck:    Public Sub InsertOrder()        Conn.Open()        cmd = New SqlCommand("Add_NewOrder", Conn)        cmd.CommandType = CommandType.StoredProcedure        ' pass customer info to stored proc        cmd.Parameters.Add("@FirstName", txtFName.Text)        cmd.Parameters.Add("@LastName", txtLName.Text)        cmd.Parameters.Add("@AddressLine1", txtStreet.Text)        cmd.Parameters.Add("@CityID", dropdown_city.SelectedValue)        cmd.Parameters.Add("@Zip", intZip.Text)        cmd.Parameters.Add("@EmailPrefix", txtEmailPre.Text)        cmd.Parameters.Add("@EmailSuffix", txtEmailSuf.Text)        cmd.Parameters.Add("@PhoneAreaCode", txtPhoneArea.Text)        cmd.Parameters.Add("@PhonePrefix", txtPhonePre.Text)        cmd.Parameters.Add("@PhoneSuffix", txtPhoneSuf.Text)        ' pass order info to stored proc        cmd.Parameters.Add("@NumberOfPeopleID", dropdown_people.SelectedValue)        cmd.Parameters.Add("@BeanOptionID", dropdown_beans.SelectedValue)        cmd.Parameters.Add("@TortillaOptionID", dropdown_tortilla.SelectedValue)        'Session.Add("FirstName", txtFName.Text)        cmd.ExecuteNonQuery()        cmd = New SqlCommand("Add_EntreeItems", Conn)        cmd.CommandType = CommandType.StoredProcedure        cmd.Parameters.Add("@CateringOrderID", get identity from previous stored proc)   <-------------------------        Dim li As ListItem        Dim p As SqlParameter = cmd.Parameters.Add("@EntreeID", Data.SqlDbType.VarChar)        For Each li In chbxl_entrees.Items            If li.Selected Then                p.Value = li.Value                cmd.ExecuteNonQuery()            End If        Next        Conn.Close()I want to somehow grab the @CateringOrderID that was created as an end product of my first called stored procedure (Add_NewOrder)  and pass that to my second stored procedure (Add_EntreeItems)

View 9 Replies View Related

SQL Server 2012 :: Executing Dynamic Stored Procedure From A Stored Procedure?

Sep 26, 2014

I have a stored procedure and in that I will be calling a stored procedure. Now, based on the parameter value I will get stored procedure name to be executed. how to execute dynamic sp in a stored rocedure

at present it is like EXECUTE usp_print_list_full @ID, @TNumber, @ErrMsg OUTPUT

I want to do like EXECUTE @SpName @ID, @TNumber, @ErrMsg OUTPUT

View 3 Replies View Related

Transact SQL :: How To Make Procedure Work For Multiple Values In Parameter

Jun 7, 2015

Below is the stored procedure i have it works fine if i have 1 value passed to @invited_by but i want to modify but i want this code to be working for multiple inputs .Lets say if i do

exec [dbo].[sp_GetInvitationStatusTest] 'Test1 . I get the desired output but i want this procedure to work for
exec [dbo].[sp_GetInvitationStatusTest] 'Test1,Test2'.
USE [merck_acronyms]
GO

[code]....

View 2 Replies View Related

Make And Save A Blob On The Fly In Stored Proc

Jul 7, 2006

Is there a way to run query, make file out of query (txt,csv or xls -for example) and save it as a blob into the table column typeImage/Text?Any thoughts would be greatly appreciated.Thanks!

View 5 Replies View Related

How To Make Tables &&amp; Stored Procedures In MSDE?

Nov 18, 2005

Hello, I am a beginner using MSDE & Visual Basic.NET standard version (not Visual Studio.NET) . MSDE was downloaded from microsoft.com yesterday, which is sql2ksp3.exe.

View 15 Replies View Related

Make A Copy Of SQL DB And Update/change Stored Procedures

Oct 10, 2006

Can I make a copy of my development database DEV on same SQL SERVER machine, rename it to TEST and stored procedures to be updated automatically for statements likeUPDATE [DEV].[dbo].[Company]SET [company_name] = @company_nameto becomeUPDATE [TEST].[dbo].[Company]SET [company_name] = @company_namein order not to edit each individual stored procedure for updating it ?

View 2 Replies View Related

Make Sql Function To Modify Data Like Sql Procedure To Improve Code Structure And Readability.

Aug 27, 2007

Now Sql Function can not modify data as Sql Procedure like other database, it's very troublesome at most case!
in most case, Sql Function is used to improve code structure then it can be maintanced easy! BUT only because it can not modify data, there are 2 troublesome way:

1. Make all callers in the path from Sql Functions to Sql Procedure. and the coder will cry, the code will become very
confusional , and very difficult to maintance.

2. Divide the Sql Function into a thin CLR wrapper to call a Sql Procedure, can only use another connection, BUT can not be in the same transaction context And the code is ugly and slow.

You should not give limitation to Sql Function, should just limit the using context of Sql Function!

The sql code is more difficult to read and maintance than norm code(C#), then the improving code structure and readability should be one of your most important task, that's why microsoft!


View 6 Replies View Related

System Stored Procedure Call From Within My Database Stored Procedure

Mar 28, 2007

I have a stored procedure that calls a msdb stored procedure internally. I granted the login execute rights on the outer sproc but it still vomits when it tries to execute the inner. Says I don't have the privileges, which makes sense.

How can I grant permissions to a login to execute msdb.dbo.sp_update_schedule()? Or is there a way I can impersonate the sysadmin user for the call by using Execute As sysadmin some how?

Thanks in advance

View 9 Replies View Related

Ad Hoc Query Vs Stored Procedure Performance Vs DTS Execution Of Stored Procedure

Jan 23, 2008



Has anyone encountered cases in which a proc executed by DTS has the following behavior:
1) underperforms the same proc when executed in DTS as opposed to SQL Server Managemet Studio
2) underperforms an ad-hoc version of the same query (UPDATE) executed in SQL Server Managemet Studio

What could explain this?

Obviously,

All three scenarios are executed against the same database and hit the exact same tables and indices.

Query plans show that one step, a Clustered Index Seek, consumes most of the resources (57%) and for that the estimated rows = 1 and actual rows is 10 of 1000's time higher. (~ 23000).

The DTS execution effectively never finishes even after many hours (10+)
The Stored procedure execution will finish in 6 minutes (executed after the update ad-hoc query)
The Update ad-hoc query will finish in 2 minutes

View 1 Replies View Related

Is It Possible To Make An Installer Package(msi And Exe Files) For Stored Procedures,tables,views

Nov 9, 2007

Hi,

I've a doubt regarding deploying and creating package (MSI and EXE files) for SQL Stored procedures,views,tables,functions,triggers etc.,just like ASP.NET application by publishing and deploying..Is it possible in the same way for sql objects.If so,pls help me how to do it?

Thanks in advance.

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved