I need to execute several stored procedures on a Sybase server and copy the results to SQL Server 2005 tables. While using an ad-hoc sql statement the "Available External Columns" list is correct, however when using a stored procedure the list is empty. I've tried to work around this a couple of ways without success.
1) DelayedValidation.
I ran the sql from the stored procedure body in the OLE DB Source to set the column list, then turned on DelayValidation for the Data Flow component. When I switch to using a stored procedure it still connects to the Sybase database and removes the column list. Still does this even after turning on DelayedValidation for the sequence container and entire package (the OLE DB source itself does not have the option).
2) Using a variable that changes during runtime.
I copied the sql from the Sybase procedure into the default value of a variable. A script changes it to a procedure at runtime. This provides a column list in design mode but throws the error "The external metadata column collection is out of synchronization with the data source columns. The external metadata column xxxx needs to be removed from the external metadata column collection" and repeats for every column in the list. I know that the column names and data types are identical.
3) Manually updated the external/output columns list.
Was very painful and gave me the same errors.
It seems that DelayedValidation is the route I'm supposed to take, but I don't see how it would be any different during runtime.
Hi,I would like to create stored procedure that can call, for example dlland pass String, and dll returns changed string back. How is thispossible? Can someone write me an example.Thanks,Alan.
I want to use parameters within a stored procedure to generate dynamic columns using SUSER_SNAME as the name for the column that I want to dynaically select (e.g. Select @SUSER_SNAME, First, Last, City FROM MyTable). I have been able to successfully use parameters in the WHERE clause within a stored procedure but haven't been able to find a way to use parameters for column names let alone to tie the parameter value back to SUSER_SNAME.
Can someone please explain the difference between Output and External columns? I can't fathom why "Output" columns aren't good enough. In other words, what is there a need or value in having two types of "output" columns?
I have a OLE DB Data Source Component that uses an SQL command as the data access mode. On the Connection Manager "tab" of the OLE DB Source Editor I can sucessfully parse my query and produce a preview of the data.
However, when I go to the Columns tab I see no available external columns? Apart from the fact I'm using a union I can't think of any reason why I can't see any columns, it doesn't make sense.
Hopefully someone can help me with what I'm sure is a very simple question (new to the XML thing). I receive an XML file from "someplace" that I need to parse out using the XML Source in SSIS. I have SSIS generate me an XSD document, as one isn't provided for me. However, after I do this, SSIS does not show any available external columns to pull data from- the "Columns" section of the source is just blank. I'm pretty sure this has to do with a syntax error in either the XML file that is being provided to me, or the XSD doc that SSIS is generating. Below are both (obviously with data dummied up). Can someone take a look and let me what needs to be changed in either file to get this up and running? I'm looking to grab the AccountNumber, RecordNumber, ProcessedDate, Status, and StatusMessage elements.
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!!
I have a SSIS package with a Data Flow task. This task transfers the data from SQL Server 2000 to a table in SQL Server 2005.
I deployed and tested this package on the Test Server. Then put this package in a job and executed it - Works fine.
On the production server- If I execute the package through DTEXECUI, it works fine. But when I try executing it through a job- the job fails and it gives me following error:
Description: The external metadata column collection is out of synchronization with the data source columns. The "external metadata column "T_FieldName" (82)" needs to be removed from the external metadata column collection....
What I don't understand is, why are there no errors displayed when I execute the package through DTEXECUI.
Can anyone tell me if it is possible to use a Sybase Stored Proc as a dataset in RS? There is only the text option in the Command Type drop down list for the dataset.
I am using an ODBC driver to connect to the Sybase db, there is no OLE DB provider installed.
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.
Hi, I have an XP that accepts one parameter, does a calculation and returns a value This value is not an output parameter but is displayed in Query Analyzer
i do: exec xp_add10 '25' and it returns: 35
I want to call this XP inside a regular SP. In this case, how can i access the value 35.
I am trying to reference the following stored procedure. I was just wondering if it can be done. I get the following error when i try to save it. Error: The Column prefix 'CRMBackOffice.dbo' does not match with the table name or alias name used in the query
SELECT BRN_CustCode, BRN_Name1, BRN_Name2, BRN_Status FROM [VENUS_II].[dbo].[Branches] WHERE BRN_CustCode NOT IN ([CRMBackOffice].[dbo].[csp_GetCRMIIICustomerList])
I'm having problems installing a external stored procedure that I created in Visual C++. I've created the .ddl and put it in the correct directory.
I've run the following commands to install it sp_addextendedproc xp_regex, "D:garthExternalStoredProceduresTecasxp_regex.dll"
I then exec the procedure and get the following:
execute master..xp_regex
ODBC: Msg 0, Level 16, State 1 Cannot load the DLL \tedevsql02garthExternalStoredProceduresTecas, or one of the DLLs it references. Reason: 126(The specified module could not be found.).
The .dll is definately in that directory. I'm not sure what I'm doing wrong. Any tips?
I have generated a DLL file in VC++ 2005 by a 'C' file. It works fine when I put in a 32bits machine(32bits Windows Server 2003 + 32 bits SQL Server 2005).
However, when I build it into 64 bits, it doesn't work in a 64 bits machine. I have checked by Dependenct Walker, the DLL generated is linked with KERNEL32.DLL / OPENDS60.DLL / MSVCR80D.DLL, all of these DLL files are on the 64 bits machines and linked correctly.
I have created a .net application using visual studio .net and sql server destop edition on my pc. I have exported the database tables and stored procedures and imported them into a Sql Server database on a web hosting service. The web host does not allow me to access this database directly through visual studio .net.
My connection string to the external database works ok and I can access my stored procedures through my web pages. I know they accept parameters and that I can receive Return Values from them. However, whenever I try to access any of the tables on the external database through a stored procedure, I get a sqlException saying that the table cannot be found (Invalid object name 'UserList').
I have created a text type command which selects data from one of the tables and this runs through without any errors. I have also managed to Insert a row onto one of the external tables also by using a text type command. My only problem seems to be with commands using stored procedures.
Just in case this is the problem - the owner of the table/procedures on my desktop is shown as dbo but on the external database the owner of the tables is shown as [domainname].co.uk_dbuser while the stored procedures owner is still dbo.
Example of stored procedure on external database:
/****** Object: Stored Procedure dbo.AddUser Script Date: 01/03/2005 21:10:06 ******/ CREATE PROCEDURE dbo.AddUser ( @Username Varchar(20), @Password Varchar(20) ) AS ( Select User_ID From UserList Where User_Username = @Username ) GO
Have tried changing dbo.AddUser to [domain].co.uk_dbuser.Adduser but this would not save because there were too many full-stops!
Any help would be greatly appreciated as I am completely stuck.
I am trying to create a CLR based stored procedure in C#. When i tried printing simple "Hello" from it, it works fine. Now requirement is to run an exe file from it. For that i use process.start. But when i try to execute the procedure i get all the security execptions. Can someone please help. Following is the code snippet. -------------------------------------------------------------------------------------------------------------------------------------------------------------
public partial class StoredProcedures
{
[Microsoft.SqlServer.Server.SqlProcedure]
public static void RunProc(string arg)
{
SqlPipe pipe = SqlContext.Pipe;
pipe.Send("Hello");
Process.Start("E: est.exe");
}
}
CREATE ASSEMBLY [RunProcess]
FROM 'RunProcess.dll'
CREATE PROCEDURE dbo.sqlclr_RunProc
(
@arg nvarchar(1024)
)
AS EXTERNAL NAME [RunProcess].[StoredProcedures].[RunProc]
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?
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?
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?
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"?
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)?
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'
i need to use only one stored procedure and access many tablesso how write a stored procedure for that dohelp me looking forward for a reply to the earliest i am developing web page using asp.net using c# and sqlserver as backend
