OLE DB DataSource W Stored Procedure Not Populating Column Metadata
Mar 28, 2006
I'm having some issues getting OLE DB Data Sources to work w stored procs in SSIS. Here's the situation.
I have an OLE DB Data Source set up to call a stored proc w no parameters. The stored procedure loops through a set of databases and inserts data from each database into a results table. I'm attempting to return the results table to SSIS, but the Available External Columns are not populating. However, previewing the query in SSIS does show results. The insert in to the results table is done by a call to sp_executesql.
I've tried setting the results table up as a temp table, table variable, and static table. I have NOCOUNT set ON and am only returning one recordset. I've seen the other threads in here about similar problems, but none of their solutions seem to work for me.
Any help would be much appreciated....
View 2 Replies
ADVERTISEMENT
May 25, 2007
Hi all,
Im still relatively new to SQL Server & ASP.NET and I was wondering if anyone could be of any assistance. I have been googling for hours and getting nowhere.
Basically I need to access the query results from the execution of a stored procedure. I am trying to populate a DataSet with the data but I am unsure of how to go about this.
This is what I have so far:-
1 SqlDataSource dataSrc2 = new SqlDataSource();2 dataSrc2.ConnectionString = ConfigurationManager.ConnectionStrings[DatabaseConnectionString1].ConnectionString;3 dataSrc2.InsertCommandType = SqlDataSourceCommandType.StoredProcedure;4 dataSrc2.InsertCommand = "reportData";5 6 dataSrc2.InsertParameters.Add("ID", list_IDs.SelectedValue.ToString());7 8 int rowsAffected;9 10 11 try12 {13 rowsAffected = dataSrc2.Insert();14 }
As you know this way of executing the query only returns the number of rows affected. I was wondering if there is a way of executing the procedure in a way that returns the data, so I can populate a DataSet with that data.
Any help is greatly appreciated.
Slainte,
Sean
View 2 Replies
View Related
Sep 14, 2007
Hello. I hope I am posting this in the right place. I am a beginning coder and could really use some help.
I have a site which is using the club starter kit as a foundation. I now want to be able to create a new page which looks just like the event_list.aspx page but includes a drop down box. The drop down box will be populated with all of the locations. When the drop down item is selected, the event list will be filtered by location. I have been able to do this writing new code but I want to be able to use a stored procedure using the PagedUpcomingEventList procedure as a model. This is what I have accomplished so far:
I have the drop down box which is populated properly from a new Datasource.
View 1 Replies
View Related
Feb 22, 2006
I tried to write a CLR stored procedure using C# in SQL 2005 to access an Access
database.
When I use the OleDbConnection class in System.Data, the procedure throws SecurityException at runtime.
Output as following:
System.Security.SecurityException: Request for the permission of type 'System.Data.OleDb.OleDbPermission, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' failed.
at System.Security.CodeAccessSecurityEngine.Check(Object demand, StackCrawlMark& stackMark, Boolean isPermSet)
at System.Security.PermissionSet.Demand()
at System.Data.Common.DbConnectionOptions.DemandPermission()
at System.Data.OleDb.OleDbConnection.PermissionDemand()
at System.Data.OleDb.OleDbConnectionFactory.PermissionDemand(DbConnection outerConnection)
at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
at System.Data.OleDb.OleDbConnection.Open()
at Dbbest.Data.BulkStuff.bulkcopy(String source_oledb_connection_string, String source_table, String destination_table, Int32 batchSize, Int32 notifyAfter)
The action that failed was:
Demand
The type of the first permission that failed was:
System.Data.OleDb.OleDbPermission
The Zone of the assembly that failed was:
MyComputer
View 1 Replies
View Related
Apr 9, 2015
I am having one store procedure which use to load data from flat file to staging table dynamically.
Everything is working fine.staging_temp table have single column. All the data stored in that single column. below is the sample row.
AB¯ALBERTA ¯93¯AI
AI¯ALBERTA INDIRECT ¯94¯AI
AL¯ALABAMA ¯30¯
After the staging_temp data gets inserted into main table.my probelm is to handle such a file where number of columns are more than the actual table.
If you see the sample rows there are 4 column separated by "¯".but actual I am having only 3 columns in my main table.so how can I get only first 3 column from the satging_temp table.
Output should be like below.
AB¯ALBERTA ¯93
AI¯ALBERTA INDIRECT ¯94
AL¯ALABAMA ¯30
How to achieve above scenario...
View 1 Replies
View Related
Mar 12, 2007
I have a stored procedure that takes a date range and returns all the sales in that date range. I'm trying to create the report model for ad-hoc reporting. When I go to create the dataset view, it only lets me select tables or views.... how do I get around this?
View 2 Replies
View Related
Jul 2, 2014
Have two tables: table1 and table2
Working with two main columns in both tables: D_ID and C_ID.
Table1 has D_ID populated and C_ID is not.
Table2 is a cross reference table with both D_ID and C_ID values within.
Looking for best way to populate C_ID in table1 from C_ID values in table2 where table1.D_ID = table2.D_ID.
There are too many values to do a case in stored procedure which has been my best practice. Trying to up my game with using SSIS 2012 as well.
View 1 Replies
View Related
Aug 23, 2006
I have a table with two columns:
OwnerName Owner
John;Smith
Mary;Smith
OwnerName is populated. Owner is not.
I want to populate the Owner column with the OwnerName in alphabetical order. I have already created a function to do this.
Select fnGetOwner(OwnerName) from OwnerTable. This returns:
Smith, John
Smith, Mary
How do I populate the blank Owner field beside the OwnerName in the OwnerTable?
View 3 Replies
View Related
Apr 25, 2008
Hello, all.
I am new to SQL querying and I came across an issue while experimenting.
Say, I have two tables like the following, a Users table at the top, and a Roles table at the bottom (drawing the issue as I'm not fluent in English to explain):
(Users table)
UserId | RoleId | RoleOldId
-------+--------+----------
1......|x.......|11........
2......|y.......|22........
3......|z.......|22........
(Roles table)
RoleId | RoleOldId | RoleName
-------+-----------+-------------
10.....|11.........|Anonymous....
20.....|22.........|Superuser....
30.....|33.........|Administrator
The Roles table was changed to include a new column Roles.RoleId and the old column is named Roles.RoleOldId. Same is done for Users table.
Assume these are not foreign and/or primary keys. How can I fill Users.RoleId with new Roles.RoleIds, matching RoleOldIds at each tables? The resulting (x, y, z) set of Users table will be (10, 20, 20).
Thank you for your time.
View 3 Replies
View Related
Apr 29, 2015
I want to populate a datetime column on the fly within a stored procedure. Below is the query that I currently have that does same but slows down query performance.
CREATE TABLE #TaxVal
(
ID INT
, PaidDate DATETIME
, CustID INT
, CompID INT
[code]...
Which is the best way to write this query for better performance?
View 2 Replies
View Related
Nov 5, 2015
Can I invoke stored procedure stored inside from a user defined table column?
View 5 Replies
View Related
Dec 5, 2005
Sorry I'm pretty new to SQL so I don't know if this is a simple question. I have a table, and I am trying to add a column to the table and populate this column using what would be called an 'IF' function in Excel.
Basically 'column A' has numbers in it. I want SQL to look at 'column A' and if the first 5 digits of the number in 'column A' are 00001, then put 'description A' into new column 'column B'. If the first 5 digits of the number in 'column A' are 00002, then put 'description B' into 'column A' etc.
Any ideas?
View 2 Replies
View Related
Aug 29, 2013
I have a table of id numbers that I wish to mask. My thought was to create a new column for this new id number and populate it with a unique sequential value - start at 1 and go as high as needed. My problem is that I cannot recall how to populate that column with a number...
View 4 Replies
View Related
Mar 25, 2008
I've tried looking in sys.syscolums and sys.syscomments, but I can't seem to find where the Description information is retain for a Field in the system tables -- any hints?
Thanks, Rob.
View 2 Replies
View Related
Jun 21, 2006
SQL table and column metadata
Is there a way to add or update the column or table (using the extended properties) description metadata via T-SQL (from within a stored procedure) or via a program (such as VB.NET using ADO)?
These metadata properties are available via the SSMS interface:
Columns via the Column Properties/Table Designer/Description
Tables via the Table Properties/Extended Properties/[Extended Property Name]
Thanks in advance,
Mark
View 6 Replies
View Related
Oct 25, 2007
I keep getting the following error in SSIS. Also, I don't get the error on every server the package is run on, but less than 5 (the package is run on over 100).
"The external metadata column collection is out of synchronization with the data source columns. The column "Timestamp" needs to be added to the external metadata column collection"
Please tell me where I need to remove Timestamp from. Thanks
-Kyle
View 1 Replies
View Related
Oct 24, 2007
I keep getting the following error in SSIS. Also, I don't get the error on every server the package is run on, but less than 5 (the package is run on over 100).
"The external metadata column collection is out of synchronization with the data source columns. The column "Timestamp" needs to be added to the external metadata column collection"
Please tell me where I need to remove Timestamp from. Thanks
-Kyle
View 1 Replies
View Related
May 9, 2006
Hi all!
My problem I've been struggling with is the following. I have a set of text files (around 70), each with different column numbers and types. I define Flat File Connection Managers for each of them where I can nicely rename, set data types and omit certain columns. I do this once and this will be the basis for the rest of the data process (would be nice programmatically too actually).
I would like to pump each of these text files into SQL Server tables using CREATE TABLE and BULK INSERT (because do it one-by-one is really a pain). The question is:
is there a way to obtain column information (Script Task) from a Connection Manager so I can run CREATE TABLE-s? I just need the names, data type for each nothing fancy...
(I bumped into interfaces like IDTSConnectionManagerFlatFileColumns90, which I cannot handle from the Script Task.)
Any help appreciated!
View 6 Replies
View Related
Apr 10, 2015
I am having one store procedure which use to load data from flat file to staging table dynamically.everything is working fine. staging_temp table have single column.all the data stored in that single column below is the sample row.
AB¯ALBERTA ¯93¯AI
AI¯ALBERTA INDIRECT ¯94¯AI
AL¯ALABAMA ¯30¯
after the staging_temp data gets inserted into main table.my probelm is to handle such a file where number of columns are more than the actual table.if you see the sample rows there are 4 column separated by "¯".but actual I am having only 3 columns in my main table.so how can I get only first 3 column from the satging_temp table.output should be like below.
AB¯ALBERTA ¯93
AI¯ALBERTA INDIRECT ¯94
AL¯ALABAMA ¯30
View 45 Replies
View Related
Mar 12, 2008
Hi,
I have an Excel file source. I keep getting this error when running the package:
"The external metadata column collection is out of synchronization with the data source columns. The column "x" needs to be updated in the external metadata column collection."
When I get this error with regular flat files, it's because I've changed the data type of a given column in the flat file connection manager. And I resolve it simply by double-clicking on the flat file source task, and viola - it corrects it for me.
How do I correct this with an Excel file? Help.
Thanks
View 10 Replies
View Related
Aug 2, 2007
Hi!
I have created a view and one of the columns in the view has an alias assigned to it.
I'm able to read the metadata from INFORMATION_SCHEMA.VIEW_COLUMN_USAGE and also lookup
from which table each column in the view orginated from except for the column that has an alias assigned to it.
Is there any other way to lookup a column that has an alias assigned to it?
Thanks alot!
Adam
View 2 Replies
View Related
Mar 18, 2008
Have many views based on legacy tables that have different table and column names. Want to create a table that shows view table / column and underlying table column, e.g.
CREATE VIEW [dbo].[Branch]
AS
SELECT
divbra_id BranchID,
cmpny_id CompanyID,
divbra_cde BranchCode,
divbra_nme BranchName
FROM MyDatabase.dbo.divbranc
GO
is an existing view. I want to pull out the following metadata:
divbranc divbra_id Branch BranchID
divbranc cmpny_id Branch CompanyID
divbranc divbra_cde Branch BranchCode
divbranc divbra_nme Branch BranchName
Is there anyway to get this from SQL metadata without actually parsing the view SELECT statement in code?
View 7 Replies
View Related
Dec 1, 2006
Hello,
I have an input into a raw file destination that has IsSorted=TRUE.
Any output from a raw file source that uses that raw file will have IsSorted=FALSE.
So, a question for Microsoft. Is it a legitimate request to ask that the IsSorted and SortKey metadata be stored in a raw file? If so I shall raise it at Connect.
-Jamie
View 7 Replies
View Related
Jan 25, 2008
HII m trying to use the following Store Procedure to search Suppliers... user provides a Column Name (Search By) and an Expression... but i m not getting it work properly... plz review it and tell me wots wrong with this stored procedure is... CREATE PROCEDURE dbo.SearchSupplier ( @Column nvarchar(50), @Expression nvarchar(50) ) AS SELECT SupplierID,Name,Address,Country,City,Phone,Fax FROM Supplier WHERE @Column LIKE '%'+@Expression +'%' OR @Column = @Expression RETURN Thanks
View 2 Replies
View Related
Jul 20, 2005
I am trying to determine the number of columns that are returned froma stored procedure using TSQL. I have a situation where users will becreating their own procedures of which I need to call and place thoseresults in a temp table. I will not be able to modify those usersprocedures. I figure if I have the number of columns I can dynamicallycreate a temp table with the same number of columns, at which point Ican then perform an INSERT INTO #TempTableCreatedDynamically EXEC@UserProcCalled. With that said, does anyone have any idea how todetermine the number of rows that an SP will return in TSQL?Thanks!
View 2 Replies
View Related
Apr 17, 2007
Hello,
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 help me to resolve this issue.
Thanks.
View 3 Replies
View Related
Apr 19, 2008
I was trying to get my data from olb table to excel. but i got this error
my dataflow is as follows:
oledb source-> excel destination
i have got a excel file template at the destination all ready.
with the column names+ the sheet all ready.
but somehow it got stuck...
anybody can help?
thanks!
View 8 Replies
View Related
Mar 25, 2008
Hi guys,
I have a table with following columns and records.
Empid Empname Phone Flag
14 Rajan 2143 116 Balan 4321 122 Nalini 3456 023 Ganesh 9543 0
Now i need to create a stored procedure which will convert the flag values to vice versa since it is a bit datatype. That is if execute the stored procedure it should convert all the flag values to 1 if it zero and zero's to 1. How? Pls provide me the full coding for the stored procedure.
Thanx.
View 2 Replies
View Related
Feb 17, 2006
I have a simple Gridview control that has a delete command link on it.If I use the delete SQL code in line it works fine. If I use a stored procedure to perform the SQL work, I can't determine how to pass the identity value to the SP. Snippets are below...The grid<asp:GridView ID="GridView2" runat="server" AllowPaging="True" AllowSorting="True" AutoGenerateColumns="False" DataSourceID="SqlDataSource2"> <Columns> <asp:BoundField DataField="member_id" HeaderText="member_id" InsertVisible="False" ReadOnly="True" SortExpression="member_id" /> <asp:BoundField DataField="member_username" HeaderText="member_username" SortExpression="member_username" /> <asp:BoundField DataField="member_firstname" HeaderText="member_firstname" SortExpression="member_firstname" /> <asp:BoundField DataField="member_lastname" HeaderText="member_lastname" SortExpression="member_lastname" /> <asp:BoundField DataField="member_state" HeaderText="State" SortExpression="member_state" /> <asp:CommandField ShowEditButton="True" /> <asp:CommandField ShowDeleteButton="True" /> </Columns> </asp:GridView> <asp:SqlDataSource ID="SqlDataSource2" runat="server" ConnectionString="<%$ ConnectionStrings:rentalConnectionString1 %>" SelectCommand="renMemberSelect" SelectCommandType="StoredProcedure" DeleteCommand="renMemberDelete" DeleteCommandType="StoredProcedure" OldValuesParameterFormatString="original_{0}" > <DeleteParameters> <asp:Parameter Name="member_id" Type="Int32" /> </DeleteParameters> </asp:SqlDataSource>the SPCREATE PROCEDURE renMemberDelete@member_id as intAs UPDATE [renMembers] SET member_status=1 WHERE [member_id] = @member_idGO
View 1 Replies
View Related
Feb 10, 2005
Hey Guys,
Here is the issue I'm having. I am writing a stored procedure that takes a couple of parameters. Each one is the value within a specific column in one table (i.e., @part = 'o-ring' or @sub_assembly = 'hydraulic ram'). Needless to say, the columns form a hierarchy. What I am trying to achieve is to allow the user to specify one of the parameters and get a count for all records where the specified value is in the corresponding column. So, if the user puts in the parameter @part = 'o-ring', I want it to know that the where clause for the select statement should look for o-ring in the part column and not the sub_assembly column. Here is what I am trying to do, which isn't working.
DECLARE @querycolumn varchar(20),
@queryvalue varchar(35)
SET @querycolumn = ''
SET @queryvalue = ''
IF(@sub_assembly = NULL)
BEGIN
IF(@part = NULL)
BEGIN
PRINT 'This is an error. You must have at least a part'
END
ELSE
BEGIN
SET @querycolumn = 'Part'
SET @queryvalue = @part
END
END
ELSE
BEGIN
SET @querycolumn = 'SubAssembly'
SET @queryvalue = @sub_assembly
END
SELECT SubAssembly, Part, COUNT(RecordID)
FROM Table
WHERE @querycolumn = @queryvalue
GROUP BY SubAssembly, Part
ORDER BY SubAssembly, Part
The problem is that I'm getting an error when I try to use @querycolumn to supply the column name to the WHERE clause. Any ideas or suggestions?
View 14 Replies
View Related
Sep 19, 2012
I have a stored procedure that I am using to convert tables to a new format for a project. The project requires new tables, new fields in existing tables, dropping fields in existing tables and dropping an existing table. The SP takes care of doing all this and copying the data from the tables that are going to be dropped to the correct places. Everything is working fine except for one table and I can't figure out why.
For this particular table, it already exists in the database and has new fields added to it. Then I try and update those fields with values from another table. This is where I am getting the Invalid column name error (line is highlighted in red). If I comment out the code where the error is occurring and run the update alone everything works fine so I know the Update statement works.
Here is the specific error message I am getting in SQL Server 2005:
Msg 207, Level 16, State 1, Line 85
Invalid column name 'AssignedAgent'.
Msg 207, Level 16, State 1, Line 85
Invalid column name 'DateTimeAssigned'.
Here is the SP: -
IF OBJECT_ID('ConvertProofTables','P') IS NOT NULL
DROP PROCEDURE ConvertProofTables;
GO
CREATE PROCEDURE ConvertProofTables
AS
SET ANSI_NULLS ON
[Code] ....
View 7 Replies
View Related
Mar 27, 2004
I trying to create a general stored procedure which updates 1 out of 140 columns depending on the column name provided as a parameter.
I'm not having much luck, just wondering if anyone else had tried to do this and whether it is actually possible?
Any help would be much appreciated
Chris
View 4 Replies
View Related
Feb 2, 2015
I have a table, which is being replicated with the identity column set "not for replication" and a stored procedure, which is also replicated (execution) which inserts into this table. When the execution of the stored procedure happens, the replication monitor complains about identity value not being provided.other than removing the stored procedure from replication?
View 0 Replies
View Related
