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
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
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
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
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
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
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
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
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="<b>Invoice Number</b>"></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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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