Hello,
When I transfer/copy tables from one database to another (on the same SQL Server), everything is fine. When I try to tranfer/copy Stored Procedures, I get the error message:
"Failed to copy objects from Microsoft SQL Server to Microsoft SQL Server."
I am using the DTS import/export wizard to do this.
Thanks,
Bruce
Hi all, is there a way I can simply copy the stored procedures in one DB, and "paste" them over into another DB that is identical? Thanks!
View 2 Replies View RelatedI am trying to make a copy of my live MSSQL 2005 Database to my local Developer Edition install.
To copy the whole database, I am using MS Database Publishing Wizard. I am able to copy the tables and data without problem, but after generating the sql file and running the query against the local db to copy everything, I get errors for the stored procedures.
The stored procedures name is preceded by a login, for instance:
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [matthews].[proc_allitemswithtaxologykeylist]
( @list varchar(7777) )
AS (sql code below)
I have created the user "matthews" on the local instance and given the account DBO role membership in the database. What else do I need to do?
Is there a better way to synch live databases with a local install, perhaps from within management studio itself? I am more experienced with enterprise manager, but 2005 won't let you connect with it.
Thank you for any help.
Hi, Could someone please advise me of how to copy stored procedures from one database to another?
Many thanks,
James
I have several stored procedures, created in a development environment,that I need to move to a 'QA' environment, and then in turn, to variousproduction environments.When I move these stored procedures, I would like to encrypt them,using the 'WITH ENCRYPTION' clause.My question is, how do I copy these stored procedures from developmentto their target SQL server environment in an encrypted state?Up until now, we have been moving them by generating an SQL script andthen executing that script on the target server. I have tried thisusing a script with 'WITH ENCRYPTION' specified within it, but itdoesn't appear to work when I try and execute that script on the targetserver.Any advice would be greatly appreciated.Nick.
View 1 Replies View RelatedHow do i copy Stored Procedures from one SQL Express database to another?
View 8 Replies View RelatedHi,In SQL 2000 if I wanted to take a complete copy of another running sqldatabase all did was create a new database locally and right-click itand select import and point to another database and click copyeverything (stored procedures as well) and it did it for. I can't seemto find the same functionality in SQL 2005. You can copy tables andviews but not the whole database. Is there another way of doing this?Our SQL database is hosted externaly and they recommend using theimport/export feature to do it. Does anyone know I can copy everything(such stored procedures, data table relations...etc)TanksMA.
View 1 Replies View RelatedUsing the MSSQL JDBC 1.2 driver (Oct 2007 release), we don't seem to be able to call stored procedures which internally uses app_name() function to fill into database tables .
This driver fails to access such stored procedures in both SQL Server 2000 and SQL Server 2005 databases.
The previous 1.1 driver (2006), suceeds in both cases.
Here is a test case which demonstrates this problem.
create table ICS_ConsraintTest (PrimaryIdentifier varchar(64),
ServiceProvider varchar(64),
SecondaryID varchar(64),
SecondaryServiceProvider varchar(64),
Description varchar(64),
PanoramaObject varchar(64),
Operation int,
ScalingFactor int,
DisplayAs float,
FeedType int,
InputValue int,
OutputValue float,
salary float,
birthdate datetime,
CONSTRAINT SimpleTestConstraint CHECK((len(ltrim([PrimaryIdentifier])) > 0
and len(ltrim([ServiceProvider])) > 0
and len(ltrim([SecondaryID])) > 0
and len(ltrim([PanoramaObject])) > 0
and [salary] <> 0.0)))
Stored procedure id defined as follows:
CREATE PROCEDURE SP_ICS_TestWithConstraints1(@PrimaryIdentifier varchar(64),
@PrimaryServiceProvider varchar(64),
@ServiceProvider varchar(64),
@SecondaryID varchar(64),
@SecondaryServiceProvider varchar(64),
@Description varchar(64),
@PanoramaObject varchar(64),
@Operation int,
@ScalingFactor int,
@DisplayAs float,
@FeedType int,
@InputValue int,
@OutputValue float,
@salary float,
@birthdate datetime) AS
BEGIN
BEGIN TRANSACTION
BEGIN
/* Insert */
INSERT INTO ICS_ConsraintTest ( PrimaryIdentifier,
ServiceProvider,
SecondaryID,
SecondaryServiceProvider,
Description,
PanoramaObject,
Operation,
ScalingFactor,
DisplayAs,
FeedType,
InputValue,
OutputValue,
salary,
birthdate)
VALUES ( @PrimaryIdentifier,
@ServiceProvider,
app_name(),
@SecondaryServiceProvider,
@Description,
@PanoramaObject,
@Operation,
@ScalingFactor,
@DisplayAs,
@FeedType,
@InputValue,
@OutputValue,
@salary,
@birthdate)
END
COMMIT TRANSACTION
END
Check out the app_name() is passed as the SecondaryID which causes the failure.
This question is about SQL Server 2005:
I have been trying to figure out how to copy tables and stored procedures between 2 databases (on the same server) using SQL Server Management Studio. I have tried right clicking on the table name, "script table as", "drop to", "clipboard", then I click on the 2nd database, and then click on the "tables" . I change the name of the database and click "execute". This creates the table but does not copy the data. I have also tried "create to" "clipboard" and "insert to" "clipboard" and cannot seem to be able to figure out how to get the results that I want. I am new at this but need to get the tables with the data copied along with the stored procedures, even if I have to do them one at a time. When I was using SQL Server 2000, I was able to use DTS to copy objects to other databases easily. Can someone please tell me a way to accomplish what I need to do? I have gotten information here before that was very useful and was hoping that someone can help me again.Thank you so much. Carol Quinn
Is there any sample code or help on trying to copy/replace views or prodecure in SQL or Oracle? Im using VBA in Excel...
View 4 Replies View Related
I am copying data ( ~600000 rows) using SqlBulkCopy. The operation fails after copying 390000 rows with the follwoing exception ( this happens every time when i run and after the same number of rows copied). Is anything else i need to do differently. The server has 35GB of free space & 1 GB of RAM.
System.Data.SqlClient.SqlException: Timeout expired. The timeout period elapse
d prior to completion of the operation or the server is not responding.
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConn
ection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParserStateObject.ReadSniError(TdsParserStateObject stateObj, UInt32
error)
at System.Data.SqlClient.TdsParserStateObject.ReadSni(DbAsyncResult asyncResult, TdsParserStateO
bject stateObj)
at System.Data.SqlClient.TdsParserStateObject.ReadPacket(Int32 bytesExpected)
at System.Data.SqlClient.TdsParserStateObject.ReadBuffer()
at System.Data.SqlClient.TdsParserStateObject.ReadByte()
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataRe
ader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlBulkCopy.WriteToServerInternal()
at System.Data.SqlClient.SqlBulkCopy.WriteRowSourceToServer(Int32 columnCount)
at System.Data.SqlClient.SqlBulkCopy.WriteToServer(IDataReader reader)
at PSMigrate.Program.MigrateWorkItesLatestData() in E:dd_tfs_beta3vsetSCMworkitemtrackingTo
olsPSMigrateProgram.cs:line 522
at PSMigrate.Program.MigrateData() in E:dd_tfs_beta3vsetSCMworkitemtrackingToolsPSMigrate
Program.cs:line 106
at PSMigrate.Program.Main(String[] args) in E:dd_tfs_beta3vsetSCMworkitemtrackingToolsPSMi
grateProgram.cs:line 86
Here is the part of the code
using (SqlCommand cmd = new SqlCommand())
{
SqlDataReader dataReader;
cmd.CommandText = sql;
cmd.CommandType = CommandType.Text;
cmd.Connection = conn;
conn.Open();
dataReader = cmd.ExecuteReader();
// write the data to the server
using (SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(m_destConnString))
{
// column mappings
// event settings
sqlBulkCopy.NotifyAfter = 5000;
sqlBulkCopy.SqlRowsCopied += new SqlRowsCopiedEventHandler(RowsCopiedEventHandler);
// batch settings
sqlBulkCopy.BatchSize = 10000;
sqlBulkCopy.DestinationTableName = "<tablename>";
sqlBulkCopy.WriteToServer(dataReader);
}
I want to know the differences between SQL Server 2000 storedprocedures and oracle stored procedures? Do they have differentsyntax? The concept should be the same that the stored proceduresexecute in the database server with better performance?Please advise good references for Oracle stored procedures also.thanks!!
View 11 Replies View RelatedHi,
This Might be a really simple thing, however we have just installed SQL server 2005 on a new server, and are having difficulties with the set up of the Store Procedures. Every time we try to modify an existing stored procedure it attempts to save it as an SQL file, unlike in 2000 where it saved it as part of the database itself.
Thank you in advance for any help on this matter
Using SQL 2005, SP2. All of a sudden, whenever I create any stored procedures in the master database, they get created as system stored procedures. Doesn't matter what I name them, and what they do.
For example, even this simple little guy:
CREATE PROCEDURE BOB
AS
PRINT 'BOB'
GO
Gets created as a system stored procedure.
Any ideas what would cause that and/or how to fix it?
Thanks,
Jason
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
Hi All,
I have been abloe to copy tables from one db to another, but am getting a "Failed to copy objects" error when trying to import stored procs. can anyone please tell me what I'm doing wrong?
TIA,
Bruce
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
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
Can i do something like this
INSERT INTO CITIBANK.dbo.Contract (*)
FROM exec sp..
where sp is the stored procedure ?
Or do i need to specify all the colums where the * is ?
and if so is it the columns from the SP or the colums
from the destination table ?
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
How do I search for and print all stored procedure names in a particular database? I can use the following query to search and print out all table names in a database. I just need to figure out how to modify the code below to search for stored procedure names. Can anyone help me out?
SELECT TABLE_SCHEMA + '.' + TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
Seems like I'm stealing all the threads here, : But I need to learn :) I have a StoredProcedure that needs to return values that other StoredProcedures return.Rather than have my DataAccess layer access the DB multiple times, I would like to call One stored Procedure, and have that stored procedure call the others to get the information I need. I think this way would be more efficient than accessing the DB multiple times. One of my SP is:SELECT I.ItemDetailID, I.ItemDetailStatusID, I.ItemDetailTypeID, I.Archived, I.Expired, I.ExpireDate, I.Deleted, S.Name AS 'StatusName', S.ItemDetailStatusID, S.InProgress as 'StatusInProgress', S.Color AS 'StatusColor',T.[Name] AS 'TypeName', T.Prefix, T.Name AS 'ItemDetailTypeName', T.ItemDetailTypeID FROM [Item].ItemDetails I INNER JOIN Item.ItemDetailStatus S ON I.ItemDetailStatusID = S.ItemDetailStatusID INNER JOIN [Item].ItemDetailTypes T ON I.ItemDetailTypeID = T.ItemDetailTypeID However, I already have StoredProcedures that return the exact same data from the ItemDetailStatus table and ItemDetailTypes table.Would it be better to do it above, and have more code to change when a new column/field is added, or more checks, or do something like:(This is not propper SQL) SELECT I.ItemDetailID, I.ItemDetailStatusID, I.ItemDetailTypeID, I.Archived, I.Expired, I.ExpireDate, I.Deleted, EXEC [Item].ItemDetailStatusInfo I.ItemDetailStatusID, EXEC [Item].ItemDetailTypeInfo I.ItemDetailTypeID FROM [Item].ItemDetails IOr something like that... Any thoughts?
View 3 Replies View RelatedGreetings:
I have MSSQL 2005. On earlier versions of MSSQL saving a stored procedure wasn't a confusing action. However, every time I try to save my completed stored procedure (parsed successfully ) I'm prompted to save it as a query on the hard drive.
How do I cause the 'Save' action to add the new stored procedure to my database's list of stored procedures?
Thanks!
We recently upgraded to SQL Server 2005. We had several stored procedures in the master database and, rather than completely rewriting a lot of code, we just recreated these stored procedures in the new master database.
For some reason, some of these stored procedures are getting stored as "System Stored Procedures" rather than just as "Stored Procedures". Queries to sys.Objects and sys.Procedures shows that these procs are being saved with the is_ms_shipped field set to 1, even though they obviously were not shipped with the product.
I can't update the sys.Objects or sys.Procedures views in 2005.
What effect will this flag (is_ms_shipped = 1) have on my stored procedures?
Can I move these out of "System Stored Procedures" and into "Stored Procedures"?
Thanks!
Hello friends......How are you ? I want to ask you all that how can I do the following ?
I want to now that how many ways are there to do this ?
How can I call one or more stored procedures into perticular one Stored Proc ? in MS SQL Server 2000/05.
Hello
I'm start to work with SSIS.
We have a lot (many hundreds) of old (SQL Server2000) procedures on SQL 2005.
Most of the Stored Procedures ends with the following commands:
SET @SQLSTRING = 'SELECT * INTO ' + @OutputTableName + ' FROM #RESULTTABLE'
EXEC @RETVAL = sp_executeSQL @SQLSTRING
How can I use SSIS to move the complete #RESULTTABLE to Excel or to a Flat File? (e.g. as a *.csv -File)
I found a way but I think i'ts only a workaround:
1. Write the #Resulttable to DB (changed Prozedure)
2. create data flow task (ole DB Source - Data Conversion - Excel Destination)
Does anyone know a better way to transfer the #RESULTTABLE to Excel or Flat file?
Thanks for an early Answer
Chaepp
Hi,
Do you know how to write stored procedures inside another stored procedure in MS SQL.
Create procedure spMyProc inputData varchar(50)
AS
----- some logical
procedure spMyProc inputInsideData varchar(10)
AS
--- some logical
--- go
-------
I am writing a set of store procedures (around 30), most of them require the same basic logic to get an ID, I was thinking to add this logic into an stored procedure.
The question is: Would calling an stored procedure from within an stored procedure affect performance? I mean, would it need to create a separate db connection? am I better off copying and pasting the logic into all the store procedures (in terms of performance)?
Thanks in advance
John
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?
i have created the folowing function but keep geting an error.
Only functions and extended stored procedures can be executed from within a function.
Why am i getting this error!
Create Function myDateAdd
(@buildd nvarchar(4), @avdate as nvarchar(25))
Returns nvarchar(25)
as
Begin
declare @ret nvarchar(25)
declare @sqlval as nvarchar(3000)
set @sqlval = 'select ''@ret'' = max(realday) from (
select top '+ @buildd +' realday from v_caltable where realday >= '''+ @avdate +''' and prod = 1 )a'
execute sp_executesql @sqlval
return @ret
end
Hi everyone,
I don't specify the following statements in SP.
begin transaction
commit transaction
rollback
In sp, there are a lot of insert and update. If i execute SP and stop it in half way. will it rollback all update and insert statement? also, will it rollback if it fails?
cheers
Hi,There is a stored procedure which runs through job.It is calling to other stored procedure and other stored procedures arecalling to another .. so on (approx 12-15 sp in batch)Problem:Sometime it does not execute properly. (approx very rare... once in 500execution or sometime on new site/database)I want to know the reason for it.If anybody have faced the similar problem.Please tell the possible causes and possible solutions.Thanks in Adv.T.S.Negi(MIND)
View 1 Replies View RelatedI am having a problem trying to run a stored procedure
from a MS SQL Server version 6.5 server. The stored
procedure calls another stored procedure on a MS SQL
Server version 7.0 server.
The job task always fails with the error message...
"Login failed for user 'sa'. (Message 18456)".
I can run the stored procedure from the T-Query window
on the 6.5 server successfully but it will not run
as a job task.
Does anyone have any suggestions?