Copying Store Procedure From One Server To All Other Servers

May 22, 2008



HI,

Actally we have different server and we work on only one server and end of the day i have to copy the list of newly or modified procedure to other databases of other server. i have create the following Sp. but i am hving problem int it. please any one can have a look..

Declare @ServerName nvarchar(max)
Declare @DatabaseName nvarchar(max)
Declare @Prod nvarchar(max)
declare @m nvarchar(max)
DECLARE @SERVER nvarchar(max)
Declare @String nvarchar(max)

DECLARE @sp VARCHAR(MAX)
SELECT @sp = OBJECT_DEFINITION(OBJECT_ID('SPNAMEMOD'))
SET @Prod = 'SPNAMEMOD'

DECLARE ProcedureScripingCursor CURSOR FOR
SELECT SQLServer,DatabaseName FROM databaseListName

-- where sqlserver is ip address and databasename is databasename on that server

OPEN ProcedureScripingCursor
FETCH NEXT FROM ProcedureScripingCursor
INTO @ServerName, @DatabaseName
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @sp = REPLACE(@sp,'''','''''')

Set @String = 'execute [' + @ServerName + '].' + @DatabaseName +'.sys.sp_executesql N'''
Set @M = 'execute [' + @ServerName + '].' + @DatabaseName +'.sys.sp_executesql N'''

SELECT @M = @M + ' IF EXISTS (SELECT * FROM sys.objects where NAME = '''''+ @Prod + ''''') DROP PROCEDURE ' + @Prod +' '''

select @string = @string + ' ' + @sp + ' '''

SELECT @M
select @string
PRINT @M
-- Print @string

EXEC sp_Executesql @M
exec sp_Executesql @string

FETCH NEXT FROM ProcedureScripingCursor
INTO @ServerName, @DatabaseName
END

CLOSE ProcedureScripingCursor
DEALLOCATE ProcedureScripingCursor



View 2 Replies


ADVERTISEMENT

Help!!! Copying Jobs Between Servers

Dec 22, 1999

I have two servers running SQLserver 7.0. I have a number of jobs that are configured to import files from an AS/400 each day. I do not want to recreate these JOBS on the second server, for it took quite sometime to set up only one. I know that I can set up multi server jobs, but I want these jobs to run independant of the Master server. In other words I just want to move a copy of the job over to the next server.

View 1 Replies View Related

Copying DTS Packages Across Servers

May 6, 2004

HI All,

Now my brain isn't quite where it should be right about now - so hopefully you will be able to put me at rest.

I have been building a new server with SQL2000 on it. Some of the stuff on our existing server is going to be migrated across - however I for the life of me cannot make any sense of how to move stuff (like DTS packages) from one server to another.

moving the Database's - no problems, but all the other guff in the tree (in Enterprise Manager) I cant seem to get.

Any assistance here is greatly appreciated.

Cheers
Troy

View 3 Replies View Related

Copying A Database Between Servers

Apr 10, 2008

I am attempting to move a User Database from the Production Server to a Training Server. What is the best/most simplistic way for me to accomplish this task and place this copying action on a schedule of say "every saturday morning @ 7am"

I tried the "Copy Database Wizard" within Enterprise Manager and it successfully copies the database, however when I try to schedule it to happen at a different time...it does not copy the database. It seems to only work when I tell it to perform this action now.

Please help.

Thanks,

JC

View 11 Replies View Related

Copying Files Between Servers

May 18, 2006

I'm trying to copy files between 2 servers on a local network from within aSQL Job (and Query Analyzer) using xp_cmdshell.xcopy but get an accessdenied message returned.I'm able to successfully do the copy from within a command window so thinkthe problem has something to do with using the default SQL Server accountbut as yet I don't know how to resolve.Any help/suggestions would be much appreciated.

View 1 Replies View Related

Copying Sprocs, DTS To Multiple DBs And Servers

May 3, 2007

We have a growing number of servers and databases on each server that all share the same (sub)set of sprocs and UDFs. DTS packages, which we use for data import, frequently need to be copied between the servers. What is the best way to maintain this? Ideally, I would like to be able to click a button and have a script creating or altering one or more sprocs automatically run aginst all DBs on all servers. Likewise, I'd like to be able to copy DTS packages to all servers.

We use SS2000 SP4 and plan to migrate to SS2005. We also use ASP.net 2.0 and VS 2005 SP1.

View 4 Replies View Related

Error 0x80004005 When Copying Tables Between Servers

Feb 23, 2007

hi
I've got a job which copy tables between different servers .
I am feeding the tables one by one and the process of copying is in a loop so I have cotrol over the copying process.
it works fine but sometimes randomly I am getting
Execution failed with the following error: "ERROR : errorCode=-1071636471 description=SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005.An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Invalid character value for cast specification".An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Invalid character value for cast specification". helpFile=dtsmsg.rll helpContext=0 idofInterfaceWithError={8BDFE893-E9D8-4D23-9739-DA807BCDC2AC}".

and the process fails and this might happen for in any point and on any table and sometimes it runs all the way successfully!!
any idea what the problem might be!
Thanks

View 5 Replies View Related

SQL Server - Store Procedure

May 23, 2008

 
 
 
Greetings mate, 
 
I'm new to sql server stored procedures
- I have 2 tables tasksTbl and staff_TasksTbl
- tasksTbl primary key is an identity column
- When inserting a row into tasksTbl I want to get the ID for the new inserted row using SCOPE_IDENTITY() and use it into a new insert command that will add a row into staff_TasksTbl
 
 
I need to know whats the problem in this procedure
 
 
 CREATE PROCEDURE insertTask
(@TaskName varchar(20),
@TaskDescription varchar(20),@Rating int,
@Status varchar(20),@StartAt varchar(20),
@EntAt varchar(20),@EventID varchar(20), @StaffID varchar(100)
)
AS declare @TaskID int
SET NOCOUNT ON
insert into TasksTbl(TaskName, TaskDescription, Rating, Status, StartAt, EntAt, EventID)values(@TaskName, @TaskDescription, @Rating, @Status, @StartAt, @EntAt, @EventID)
 select @TaskID = SCOPE_IDENTITY()
 
insert into staff_TasksTbl(TaskID,StaffID)values (@TaskID, @StaffID)
GO
 
 
Thanks in advance.

View 7 Replies View Related

Can't Seem To Connect To A Store Procedure On SQL Server

Dec 1, 2004

Hi,

I am trying to get a page to call on a store procedure in SQL server and can seem to get it right. Any advice would be appreiated




<%@ Page Language="vb" %>
<%@ import Namespace="System.Data" %>
<%@ import Namespace="System.Data.OleDb" %>
<script runat="server">

Dim Conn as new OleDbConnection("Provider=SQLOLEDB;Server=*;Database=WEST1;User ID=*;Password=*;Trusted_Connection=False")
Sub Submit(Sender as Object, e as EventArgs)
Dim objCmd as OleDbCommand = new OleDbCommand ("vendorPending 10031,85140109", Conn)

Dim objReader as OleDbDataReader

objCmd.CommandType = CommandType.StoredProcedure


Try
objCmd.Connection.Open()
response.write ("opening data")
objReader = objCmd.ExecuteReader

catch ex as OleDbException
Response.write ("Error retrieving data.")

end try

DataGrid1.DataSource = objReader
DataGrid1.DataBind()

objCmd.Connection.Close()

End Sub

</script>
<html>
<head>
<title></title>
</head>
<body>
<form method="post" runat="server">
<tbody>
<tr>
<td>
<br />
</td>
</tr>
<tr>
<td>
Login :
</td>
<td>
<asp:textbox id="user" runat="server"></asp:textbox>
</td>
</tr>
<tr>
<td>
Password :
</td>
<td>
<asp:textbox id="Pass" runat="server" textmode="Password" MaxLength="10"></asp:textbox>
<br />
</td>
</tr>
<tr>
<td>
</td>
<td>
<asp:button id="Button1" onclick="submit" runat="server" text="submit"></asp:button>
</td>
</tr>
<br />
<tr>
<td>
</td>
</tr>
</tbody>
<br />
<asp:DataGrid id="DataGrid1" runat="server" HorizontalAlign="Left" Font-Names="Arial" ItemStyle-BackColor="#FFFFFF" AlternatingItemStyle-BackColor="#CCCCCC" Font-Size="10pt" Font-Name="Arial" width="755px" cellpadding="2" gridlins="vertical" BorderColor="Black" AutoGenerateColumns="False">
<HeaderStyle font-size="Small" font-names="Arial" font-bold="True"></HeaderStyle>
<EditItemStyle horizontalalign="Left"></EditItemStyle>
<AlternatingItemStyle backcolor="#CCCCCC"></AlternatingItemStyle>
<ItemStyle horizontalalign="Right" backcolor="White"></ItemStyle>
<Columns>
<asp:BoundColumn DataField="invnum" HeaderText="&lt;b&gt;Invoice Number&lt;/b&gt;"></asp:BoundColumn>
<asp:BoundColumn DataField="invdate" HeaderText="Invoice Date" DataFormatString="{0:MM-dd-yyyy}"></asp:BoundColumn>
<asp:BoundColumn DataField="duedate" HeaderText="Due Date" DataFormatString="{0:MM-dd-yyyy}"></asp:BoundColumn>
<asp:BoundColumn DataField="invamt" HeaderText="Invoice Amount" DataFormatString="${0:N2}"></asp:BoundColumn>
<asp:BoundColumn DataField="payamt" HeaderText="Pay Amount" DataFormatString="${0:N2}"></asp:BoundColumn>
</Columns>
</asp:DataGrid>
</form>
</body>
</html>


The store proceudure takes 2 int parameters. I have coded it inline in order to test it. I also will implement validation once I get this working

Thanks

View 1 Replies View Related

Server.htmlencode In Sql Store Procedure?

Sep 21, 2006

hi, i save some text into my database which looks like this..
<ol>
<li><strong>adfadsf as </strong>
<li><strong>asf as </strong>
<li><strong>dfas </strong>
<li><strong>df sad </strong>
<li><strong>f as</strong> </li>
</ol>

how m i gg to display them as numbered list in my report?

i'm using store procedure to retrieve them out into pdf format..

View 6 Replies View Related

Store Procedure In Sql Server 2005 & Asp.net

Feb 13, 2007

I want to tutorials which guide practice store procedure in sql server 2005 & asp.net with beginner level.
Any one know ?
Thank you very much.

View 1 Replies View Related

How To Debug The Store Procedure In Sql Server

Jan 15, 2008

Hi every One,

I am new to Sql and I find it dificult to write complex Sp(Stored Procedures).Can you please help me in understanding the debugging of Sp.

Thanks In advance

sumit

View 5 Replies View Related

More Questions About The Workflow Persistence Store On Remote SQL Servers

Feb 14, 2008

I am pulling my hair out having built my first application using WWF, I have moved the application off the development machine to a machine ready for production for the persistence store will now be on a remote server.

I cannot create a connection string for the persistence store that will allow me to attach to the mdb on the remote server.

the original connection string in development was:

Data Source=.SQLEXPRESS;AttachDbFilename='C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDataWorkflowPersistenceStore.mdf';Integrated Security=True;Connect Timeout=30;User Instance=False


I cannot create an equivalent string that does not return a error like this one:


An attempt to attach an auto-named database for file 'C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDataWorkflowPersistenceStore.mdf failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share.


I did simply copy the WorkFlowPersistenceStore.mdf from the development machine to the server machine (completely independant environments) - It has worked in the past for other non wwf databases - Is there something particular about the wwf database that would have prevented this?

I have tried making the persistence store permanently attached (in the development environment) but this returns a different error that implies that a permanently attached database is not allowed - so I assume that the persistence service HAS to have a detached database - Am I correct?

I have tried a UNC naming that is the same on both the machine with the server and the client machine e.g. \MACHINEwwfWorkflowPersistenceStore.mdb - but that still returns an error - Is that not allowed? (or is that not allowed) - does the file path for an attached database have to be recognisable by the client machine, the server machine or both?

MUST the User Instance be True or False or does it not really matter

It is probably obvious that I am not confident in the whole area of connection strings and the effect of different parameters and the truth is I am not certain that I have not completely confused the SQL Server with the various attempts I have made.

Is there someone who could confirm that I am in the right area and could give me an example of what SHOULD work in a situation I have described so that I know that I am roughly right and to keep trying or I am barking up the wrong tree?

Thanks in anticipation!

View 3 Replies View Related

SQL Server 2012 :: Sending Email Through Store Procedure?

Jan 28, 2015

I have below code to send email in HTML table format with store procedure. from my

Database = "CreditControl"
Table = "Testing$"

and code as below

DECLARE @bodyMsg nvarchar(max)
DECLARE @subject nvarchar(max)
DECLARE @tableHTML nvarchar(max)
DECLARE @recipients nvarchar(max)
DECLARE @profile_name nvarchar(max)

[code]....

View 6 Replies View Related

Anyway To Force SQL Server To Save Store Procedure With Errors?

Feb 12, 2008

When I create/alter a store procedure in SQL Server 2005, SQL server always checks for syntax errors first and won't let me save the change if it detects any error. Is there a way we can force the SQL server to save the store procedure that fails the syntax check?



I know SQL server will allow such invalid store procedures if you detach & re-attach the entire database from one SQL server to another server. However, if I try to manually create the same store procedure from one server on a different server with a script, then it won€™t let you save the store procedure if the linked server (or the table) can€™t be accessed from the new sql server.



How do you get around this?





Thanks

View 13 Replies View Related

Call Store Procedure From Another Store Procedure

Nov 13, 2006

I know I can call store procedure from store procedure but i want to take the value that the store procedure returned and to use it:

I want to create a table and to insert the result of the store procedure to it.

This is the code: Pay attention to the underlined sentence!

ALTER PROCEDURE [dbo].[test]



AS

BEGIN

SET NOCOUNT ON;



DROP TABLE tbl1

CREATE TABLE tbl1 (first_name int ,last_name nvarchar(10) )

INSERT INTO tbl1 (first_name,last_name)

VALUES (exec total_cash '8/12/2006 12:00:00 AM' '8/12/2006 12:00:00 AM' 'gilad' ,'cohen')

END

PLEASE HELP!!!! and God will repay you in kind!

Thanks!

View 7 Replies View Related

How To Store Multiple Result In A Variable On Sql Server Stored Procedure

Feb 29, 2008

 name               age            weightaaa                    23                50bbb                    23                60ccc                     22               70ddd                    24                20  eee                     22               30i need the output that calculate the sum of weight group by name input : age limit ex: 22 - 23 output  : age           total weight  23               11022                100  this output must stored in a sql declared variable for some other further process . 

View 7 Replies View Related

How To Find Year From 1900 To 2000 Using Store Procedure In Sql Server

Nov 21, 2006

hi friends
i want to know how to calculate year ranging from 1900 to 2000 using store procedure in sql server

View 8 Replies View Related

SQL Server 2012 :: Input Parameter For Store Procedure Like In Statement Value 1 / Value 2

Jun 20, 2014

How can I input parameter for Store Procedure like In ('Value1','Value2')

Example :
Create procedure GetUsers
@Users nvarchar(200)
as
Select * from Users where UserID in (@Users)

View 6 Replies View Related

SQL Server 2012 :: DB2 Store Procedure Returning Two Data Sets

Oct 13, 2014

A DB2 store procedure returns two data sets, when executed from SSMS, using linked server. Do we have any simple way to save the two data sets in two different tables ?

View 1 Replies View Related

SQL Server 2008 :: Run Store Procedure When Table Is Updated With Record

Jul 15, 2015

I wanted to know if it is possible to run a stored proc (report) when a new record is inserted / updated into a certain table. Also if it could do so for records with a column that meet a certain criteria ie. sell description like '%test%'

View 2 Replies View Related

Running Store Procedure As Integration Service In SQl Server 2005

Aug 8, 2007

Hi
I am trying to run 4 - 5 store procedure in a strict order. I have created a SSIS package whereby I trying to execute store procedure in a orderly fashion. I have created constraints between them. Yet whenever I run the package - the store procedures run out of sequence.
Is there something I can do to force the store procedure in orderly manner?
Help will be much appreciated.

View 2 Replies View Related

Copy Store Procedure One Server To Another Server

Jan 30, 2008



Hi,

is it's possible by script or t-sql that i can use, to transfer/copy store procedure from one server database to another server database.


Thanks and looking forward.
-MALIK

View 16 Replies View Related

SQL Server 2014 :: Case Syntax In Store Procedure Evaluating 2 Query Options

May 4, 2015

What will be the best way to write this code? I have the following code:

BEGIN
declare
status_sent as nvarchar(30)
SET NOCOUNT ON;
case status_sent =( select * from TableSignal

[Code] ...

then if Signal Sent.... do the following query:

Select * from package.....

if signal not sent don't do anything

How to evaluate the first query with the 2 options and based on that options write the next query.

View 2 Replies View Related

Copying Table In Stored Procedure

Feb 16, 2001

How can I create a table identical to another one, in a stored procedure?
I need to copy the indexes and constraints too.
Example: I have a table "employee" and I want another table "employee2"
with the same indexes and primary key and references.

I need to do the work in a stored procedure because there are many, many tables, and this process belong to a convertion program.

I can't script the table because this process must be automatic no manual.

Thank you

View 1 Replies View Related

Copying From Stored Procedure To Temptable

Mar 9, 2001

Hi,

Obviously, in a select statement, I can select * into #temptable. But can I do the equivalent from a stored procedure? I do not want to edit the stored procedure, just take the records it returns and put them into the temporary table. I do not know what records are returned by the stroped procedure until it is executed. Is there a way I can do this?
Thanks
J

View 1 Replies View Related

Copying Table In Stored Procedure

Feb 16, 2001

How can I create a table identical to another one, in a stored procedure?
I need to copy the indexes and constraints too.
Example: I have a table "employee" and I want another table "employee2"
with the same indexes and primary key and references.

I need to do the work in a stored procedure because there are many, many tables, and this process belong to a convertion program.

Thank you

View 1 Replies View Related

Xcopy Not Copying All Files In Stored Procedure

Apr 8, 2008

I am executing xcopy with xp_cmdshell in a stored procedure and it is not copying all the files. There are about 600 files in the source directory and only around 190 are copied to the destination.

The command in the stored procedure is this N'xcopy c:source*.* c:dest'

Help please!!!!

Thanks,
Saied

View 14 Replies View Related

Store Procedure Not Store

Nov 20, 2013

My store Procedure is not save in Strore Procedure folder at the time of saving it give me option to save in Project folder and file name default is SQLQuery6.sql when i save it after saving when i run my store procedure

exec [dbo].[SP_GetOrdersForCustomer] 'ALFKI'

I am getting below error :

Msg 2812, Level 16, State 62, Line 1
Could not find stored procedure 'dbo.SP_GetOrdersForCustomer'.

View 13 Replies View Related

How To Access A .asp Page Or .aspx Page From A Store Procedure In Sql Server 2000

Sep 21, 2006

Hi, I wanted to know that can we access a webpagefrom a store procedure in sql server 2000 like we run a exe file from sql server. Bye.

View 1 Replies View Related

Store Procedure

Feb 17, 2008

Hi guysi would really appreciate your help here. what am i doing wrong with this stored procedure ALTER PROCEDURE usp_CreateNewCustomer( @LName as varchar(50), @FName as varchar(50), @Dept as varchar(50)=NULL, @PhoneType as varchar(50)=NULL, @Complete as Bit=NULL, @CustomerID as Int OUTPUT,  @FaxModel as varchar(50)=NULL, @FaxNumber as varchar(50)=NULL, )ASINSERT INTO [CustomerInfo] ([LName], [FName], [Dept], [PhoneType], [Complete]) VALUES (@LName, @FName, @Dept, @PhoneType, @Complete)SELECT SCOPE_IDENTITY()INSERT INTO Extras (CustomerID, FaxModel, FaxNumber) VALUES (@CustomerID, @FaxModel, @FaxNumber)RETURN  It keeps on complaning "'usp_CreateNewCustomer' expects parameter '@CustomerID', which was not supplied."thanks 

View 4 Replies View Related

Store Procedure?

Feb 28, 2008

Hi all,
I have a few question regarding SPROC. Firstly, I want to create a sp, that will return a multiple column of data, how do you achieve that. Secondly, I want to create a store procedure that will return a multiple columns in a cursor as an output variable. Any help will be much appreciated. Can you have more then 1 return parameters???
Thanks.
Kabir

View 2 Replies View Related

How To Run SQL Store Procedure Using Asp.net

Mar 5, 2008

I have asp.net web application which interface with SQL server,
I want to run store procedure query of SQL using my asp.net application.
How to declare connectons strings, dataset, adapter etc to run my store procedure resides in sql server.
for Instance Dim connections as string,
                 Dim da as dataset, Dim adpt as dataadapter. etc
if possible , then show me completely code so I can run store procedure using my button click event in my asp.net application
thank you
maxmax 

View 2 Replies View Related







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