Parameters in an OLE DB Command

SQL Server Integration Services

Index ‹ SQL Server

- Previous
  - 1
    - How do can I do this? Maybe something like this: declare @mock table (colA int, colB int) insert into @mock values (1, 2) insert into @mock values (2, 1) insert into @mock values (2, 6) insert into @mock values (3, 6) insert into @Mock values (6, 3) insert into @mock values (6, 7) insert into @mock values (7, 6) select * from @mock x where not exists ( select 0 from @mock y where x.cola = y.colb and x.colb = y.cola ) -- ------- Output: ------- -- colA colB -- ----------- ----------- -- 2 6 Tag: Parameters in an OLE DB Command
  - 2
    - OLE DB Source to Flat File Destintation using Fixed Width Columns - determining source column width Thanks for the info, from that I found another way to do it with the help of a co-worker. select c . id , t . name , t . xtype , c . name , c . length , c . xprec , c . prec from syscolumns c , sysobjects t where t . id = c . id and t . xtype = 'U' Is what we used, and the c.prec value seems to be the value I want, so with this query, I can get the prec value for any column by doing another select statement where I match up the column names. Thanks! Tag: Parameters in an OLE DB Command
  - 3
    - date picker format thanks for your reply adolf... my pc regional setting: Regional Options tab : Standard and formats: English (United Kingdom) Short date: 14/06/2007 Long date: 14 June 2007 Location : United Kingdom Advanced tab: Language for non-Unicode programs: English (United Kingdom) anywhere else to check thanks! Tag: Parameters in an OLE DB Command
  - 4
    - Data disappearing during Run time This is what Mike is refering to: http://blogs.msdn.com/sqlexpress/archive/2006/07/17/668971.aspx HTH, Jens K. Suessmeyer. --- http://www.sqlserver2005.de --- Tag: Parameters in an OLE DB Command
  - 5
    - Old problem, but... Cannot open user default database. Login failed. Login failed for user 'NT AUTHORITY\NETWORK SERVICE'. I didn't use SQL Express before, now I am trying to use it. I thinkk it is convinient if you just want to copy db files to user's machine. like this <add name="schsecurity" connectionString="Data Source=.\SQLExpress;Integrated Security=true;User Instance=True;AttachDBFilename=|DataDirectory|aspnetdb.mdf" /> as long as user has sql express installed, he should be able to use the db once he copy the file aspnetdb.mdf over. but when I tried it, I got Cannot open user default database. Login failed. Login failed for user 'NT AUTHORITY\NETWORK SERVICE'. it looks simple, SQL express can not verify user 'NT AUTHORITY\NETWORK SERVICE' so I connect SQL express with SQL server management studio, and add the user into login of the SQL express and set the user as sysadmin. But I still get the error. Any idea thanks! Tag: Parameters in an OLE DB Command
  - 6
    - Data Conversion Hi, Lots of different data types the destination table fields are mailny int, bigint varchar, date and decimal Thanks, Dave. Tag: Parameters in an OLE DB Command
  - 7
    - Results from Aggregate() are wrong As explained in Mosha's blog , Aggregate() used as a slicer in a WHERE clause will effectively be replaced by a set of the same members (in AS2005). So the classic multi-select problem that could arise is when HIERARCHIZE ( AddCalculatedMembers ({ DrillDownLevel ({[Product].[Product Indexing].[All]})})) includes a calculated member which uses (in your case) [Product].[Product Species].CurrentMember directly. Tag: Parameters in an OLE DB Command
  - 8
    - Inconsistent Linked Server Query Results Hello, I have a linked server named 'Charlie_File' to an Excel Workbook that I set up in SQLServer 2005 Management Studio. The workbook is on my local C drive. Sometimes, I get the results back that I expect when I run the following query; SELECT * FROM OPENQUERY ( Charlie_file , 'SELECT * FROM [Feb$]' ) Sometimes, on subsequent runs of the above query, I get the following message; Msg 7399, Level 16, State 1, Line 1 The OLE DB provider "MSDASQL" for linked server "Charlie_file" reported an error. The provider did not give any information about the error. Msg 7303, Level 16, State 1, Line 1 Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "Charlie_file". There seems to be about a minute or so of a delay before the query will run correctly on subsequent attempts. Is there a connection issue here where a connection blocks subsequent attempts to select the data within a specific time span Thank you for your help! cdun2 Tag: Parameters in an OLE DB Command
  - 9
    - Can't get Dynamic Security working ... The expression that you provided above should only be set on DDO attribute. Please remove it from all other attributes. Tag: Parameters in an OLE DB Command
  - 10
    - Sql Query to create nested XML Sitemap OK. Could you look at this previous post and comment on whether this is the sort of thing you are trying to do; I am not completely sure what I am aiming at. http://forums.microsoft.com/MSDN/ShowPost.aspx PostID=1703535&SiteID=1 Tag: Parameters in an OLE DB Command
  - 11
    - How Extract the data of TFINF when doing text mining? Thanks How Extract the data of TFINF when doing text mining Thanks Tag: Parameters in an OLE DB Command
  - 12
    - Passing SELECT ALL from URL Oops sorry, the example where I craft the select all was for a single select or a pre-SP2 install. For a multi-select with built-in Select all you would actually just use the standard list of values. here the trick to avoid a huge Where Item is IN ('a', 'b', 'c',......) would be to do it like this Procedure parameter , @Item varchar (Max ) = '##' Procedure handling of ALL option. declare @NKeyCount as int declare @NKeyMax as int set @NKeyCount = onglobals. dbo .fn_CountChar( @Item , ',' ) set @NKeyMax = ( Select Count ( Distinct b.Item_Cd) as Value from meta_NewProductBaseline b ) if @NKeyCount + 1 = @NKeyMax begin set @Item = '##' end If @Item is null begin set @Item = '##' end Procedure limit And ( @Item = '##' or b.Item_Cd in ( select ltrim (SQLstr) from ONGlobals. dbo .clrfn_SplitCommaDelimitedString( @Item ) ) ) Unfortunately, you need a bunch of functions to get it to work. I hope you already have those functions. If not, you can try to find it from this forum or I can give you the code. Philippe Tag: Parameters in an OLE DB Command
  - 13
    - configure sql xml support IIS Hope someone can help me...anything would be appreciated. I am using sql server 2000...When I open the Configure sql xml support IIS tool to create a Virtual Directory the only thing that shows up is a computer icon and a IIS Virtual Directory Management for SQL Server. I can't expand ... there isn't a default web site. The other tool called Enterprise Manager works fine with XP. Matter of fact all of the other tools work except for the Virtual Directory Manager. Tag: Parameters in an OLE DB Command
  - 14
    - Clone new database from existing Do you get an error message or what are you experiencing If you can’t get attach / detach to run for you, you can also use the backup / restore functionality for this. HTH, jens K. Suessmeyer. --- http://www.sqlserver2005.de --- Tag: Parameters in an OLE DB Command
  - 15
    - How to restrict a custom component to be used only in the event handler tab? Thanks Darren, Is there any help available for the creating UITypeEditor controls Regards, $wapnil Tag: Parameters in an OLE DB Command
  - 16
    - catch me if you can / bug in the "bug"? That is not a bug, that is how it works. There are "catchable" errors and non-catchable errors. The "table does not exist" is a non-catchable error. Non-catchable errors simply terminate the batch, or stored proc, and you can do NOTHING about it. This is a HUGE failing of the error processing in MS SQL. There are 10,000 possible errors in TSQL code and you can actually only "catch" 500 of them. Tag: Parameters in an OLE DB Command
- Next
  - 1
    - How to set the current connectionString user as a defualt value? I think the resone is the user I which I used in myConnectionString, is Admin user, I tried with "dataread, datawrite" user and its worked.. Thank you myself Tag: Parameters in an OLE DB Command
  - 2
    - Generating letters in SSRS Hi. I'm looking for tutorials on how to use SSRS 2005 to generate letters in Word or PDF format from a database. We need to send letters with our company logo and some boilerplate text to a set of companies based on a SQL Server query. So the letter will look something like this, with the red areas repeating from the dataset, [logo goes here] 123 Any St. AnyCity, CA 90210 [today's date] Dear [name from database] , Some boilerplate text goes here. Your Account [account number from database] is past due. Sincerely, [scanned signature goes here] Somebody Tag: Parameters in an OLE DB Command
  - 3
    - Can I retrieve a result set from a sp into a variable within a Execute SQL Task? I have tried this with just about every combination available with no luck. Do you need to add anything to the exec command I am passing it one variable and it now looks like this "exec sp_name '20071201'" Do I need to change it to look like this "exec sp_name '20071201', " or this "exec sp_name '20071201', OUTPUT". I also have a variable set up under result set and included it in executevariablevalue. Just to be clear the SP is not putting anything in a variable but builds a report in a fiels and at the end does a select @report to display what was built in @report. I hope this is clear. Thanks. Tag: Parameters in an OLE DB Command
  - 4
    - Creating Stored Procedure in SQLServer2005 Can you provide me with more details As how to reach the Stored Procedure and how to Execute it Thank you Tag: Parameters in an OLE DB Command
  - 5
    - Help with modifying a data source's query at runtime Please help figure out what is wrong with my code. The script is supposed to load a package (from file). The loaded package already has everything set up to run a query against a local server and output the results to an Excel file. The reason for the outer script is because I need to change the query based on a global variable. When the query changes, though, I think the existing dataflow Path is no longer valid, so I should remove it and re-create another one with the new input mappings. Here is my code, which runs and throws an exception at the AcquireConnections call. The error is Error: 0x2 at Script Task: The script threw an exception: Exception from HRESULT: 0xC020801B I pieced together this code from the examples in the online books, but I am not sure what to do. ' Microsoft SQL Server Integration Services Script Task ' Write scripts using Microsoft Visual Basic ' The ScriptMain class is the entry point of the Script Task. Imports System Imports System.Data Imports System.Math Imports Microsoft.SqlServer.Dts.Runtime Imports Microsoft.SqlServer.Dts.Pipeline Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper Public Class ScriptMain Public Sub Main() ' Dim app As Microsoft.SqlServer.Dts.Runtime.Application = New Application() Dim package As Microsoft.SqlServer.Dts.Runtime.Package = _ app.LoadPackage( "c:\systime\ExcelOut\ExcelOut\ExcelOutDo.dtsx" , Nothing ) Dim pkgVars As Variables = package.Variables Dim gsVar As Variable = pkgVars( "User::gsExcelFile" ) Dim currVars As Variables = Dts.Variables Console.WriteLine(Dts.Variables( "User::gsExcelFile" ).Value) gsVar.Value = Dts.Variables( "User::gsExcelFile" ).Value pkgVars( "User::gsQuery" ).Value = Dts.Variables( "User::gsQuery" ).Value pkgVars( "User::gsCreateTable" ).Value = Dts.Variables( "User::gsCreateTable" ).Value Dim e As Executable = package.Executables( "ExcelOutTask" ) Dim thMainPipe As Microsoft.SqlServer.Dts.Runtime.TaskHost = _ CType (e, Microsoft.SqlServer.Dts.Runtime.TaskHost) Dim dataFlowTask As MainPipe = CType (thMainPipe.InnerObject, MainPipe) ' Get the source component. Dim SourceComponent As IDTSComponentMetaData90 = _ dataFlowTask.ComponentMetaDataCollection( "Local Source" ) Dim srcDesignTime As CManagedComponentWrapper = SourceComponent.Instantiate() srcDesignTime.ProvideComponentProperties() ' Reinitialize the metadata. srcDesignTime.AcquireConnections(vbNull) srcDesignTime.ReinitializeMetaData() srcDesignTime.ReleaseConnections() ' Get the destination component. Dim destination As IDTSComponentMetaData90 = _ dataFlowTask.ComponentMetaDataCollection( "Excel Destination" ) Dim destDesignTime As CManagedComponentWrapper = destination.Instantiate() destDesignTime.ProvideComponentProperties() ' Create the path. dataFlowTask.PathCollection.RemoveAll() Dim path As IDTSPath90 = dataFlowTask.PathCollection.New() path.AttachPathAndPropagateNotifications(SourceComponent.OutputCollection(0), _ destination.InputCollection(0)) 'Console.WriteLine(dataFlowTask.PathCollection.Count) Dim ret As DTSExecResult ret = package.Execute() Console.WriteLine(ret.ToString) Dts.TaskResult = Dts.Results.Success End Sub End Class Tag: Parameters in an OLE DB Command
  - 6
    - Two Dimensions Sharing same Parent Table Richard, Thanks a lot for your answer. The Company Dimension is like the "Master Dimension". Dimensions: Company(***) Brand(***, CompanyFK) - Company attribute on Top Level and Brand attribute on Bottom Level Department(***, CompanyFK) - Company attribute on Top Level and Department attribute on Bottom Level Your example is great so.. :) I drag Brand Group attribute to the rows and some measure to the data area. Now I drag Company Dimension into filter. What happens I get the list of all Brand Group companies, not only the ones related to Company that I filtered. An example: Company Dimension values : Coca-Cola, Pepsi Brand Dimension values (Company, Brand Group, Brand) : Coca-Cola , Chocolates, Cocoa ; Coca-Cola , Chocolates, Mix Cocoa Pepsi, Drinks, Coke Drink ; Pepsi, Drinks, Water Drink Filter by Company Dimension : Coca-Cola Drag Brand Group to the rows. Result : Chocolates ( from Coca-Cola) and Drinks ( from Pepsi). Chocolates with values and Drinks not of course. Result: expected : Chocolates ( from Coca-Cola) with values The result that I expect appears when I drag the Company attribute from Brand Dimension into filter otherwise .... If the Company attribute is the same on all Dimensions why doesn’t work like a common attribute For excel users is confusing work like this... Thanks a lot.. Joao and when you click down the hierarchy Company (Top Level) -> Brand Group -> Brand (Brand Dimension) you get a list of all companies, not only the ones related to Brand Tag: Parameters in an OLE DB Command
  - 7
    - Can't shrink DB BTW...... what the command u give to shrinkfil and what the error u gets ..... if any Madhu Tag: Parameters in an OLE DB Command
  - 8
    - Reporting services in SharePoint integration mode problems Hi, I'm having problems setting up Reporting Services in SharePoint Integration mode. The development environment works fine, but I can't get it to work in production. Problem seems to be in the authentication between SharePoint and Reporting Services. When I go to "Set Server Defaults" in the SharePoint central administration I get the following message: Code Snippet An unexpected error occurred while connecting to the report server. Verify that the report server is available and configured for SharePoint integrated mode. --> System.Web.Services.Protocols.SoapException: Server was unable to process request. ---> System.Net.WebException: The request failed with HTTP status 401: Unauthorized. at System.Web.Services.Protocols.SoapHttpClientProtocol.ReadResponse(SoapClientMessage message, WebResponse response, Stream responseStream, Boolean asyncCall) at System.Web.Services.Protocols.SoapHttpClientProtocol.Invoke(String methodName, Object[] parameters) at Microsoft.SqlServer.ReportingServices2006.ReportingService2006.ListSecureMethods() at Microsoft.ReportingServices.SharePoint.Soap.ReportService2006.ListSecureMethods() --- End of inner exception stack trace --- I get the same message when trying to deploy a report from Visual Studio to a SharePoint lib. Some info: - SP apppool is running under domain account a - SSRS apppool is running under domain account b - SP uses NTLM authentication - SP and SSRS are running on different servers - SSRS Configuration shows all green checkmarks Things I tried: - Basic authentication instead of Windows Integrated on SSRS - NTLM only instead of Negotiate on SSRS - Settings SPN's for SSRS, didn't help - Checked NTFS rights on ReportingServer dir, were fine (Domain users have read/list) Does anyone have an idea about how to solve this Any help is appreciated. Tag: Parameters in an OLE DB Command
  - 9
    - Recursive Query Without Self-Referencing Structure I need a statement that can return a hierarchy of folders. There are a number of good articles out there describing how to do this in SQL 2005 with a Common Table Expression (CTE) that references itself under a UNION ALL. Yet every example I come across involves a self-referencing table. Unfortunately the architect of my project did not use that simple structure. Instead, we have a Folder table and Folder Properties table. Creating a child Folder involves adding a row in the Properties table that links to a parent. Here's the statement I came up with: WITH Folders (ID, ParentID, FolderName, FolderDepth) AS ( SELECT FOLDER_ID, NULL, FOLDER_NAME, 0 FROM ZZ_FOLDER WHERE FOLDER_ID = 1 UNION ALL SELECT F.FOLDER_ID, Folders.ID, F.FOLDER_NAME, Folders.FolderDepth + 1 FROM ZZ_FOLDER F INNER JOIN ZZ_FOLDER_PROP FP ON FP.FPROP_FOLDER_ID = F.FOLDER_ID WHERE FP.FPROP_LINK_ID = Folders.ID ) SELECT * FROM Folders The initial statement brings back the top of the folder structure. The recursive statement brings back Folder rows that have an entry in their Property rows for the parent. Yet when I try to execute this, I get: Msg 4104, Level 16, State 1, Line 1 The multi-part identifier "Folders.ID" could not be bound. Msg 4104, Level 16, State 1, Line 1 The multi-part identifier "Folders.ID" could not be bound. Msg 4104, Level 16, State 1, Line 1 The multi-part identifier "Folders.FolderDepth" could not be bound. It sounds like all the recursive references to the CTE are unavailable. What am I doing wrong Would welcome any suggestions, too. I realize this isn't a T-SQL specific question, more SQL in general, but I didn't see a more relevant forum. What follows are some tables/data to run the above statement against. -- Create Folder Tables CREATE TABLE ZZ_FOLDER ( FOLDER_ID INTEGER NOT NULL PRIMARY KEY, FOLDER_NAME VARCHAR(50)) CREATE TABLE ZZ_FOLDER_PROP ( FPROP_ID INTEGER NOT NULL PRIMARY KEY, FPROP_FOLDER_ID INTEGER NOT NULL, FPROP_LINK_TYPE VARCHAR(50), FPROP_LINK_ID INTEGER NOT NULL) GO -- Populate Folder Tables INSERT INTO ZZ_FOLDER VALUES (1, 'Top Level') INSERT INTO ZZ_FOLDER VALUES (2, 'Second Level A') INSERT INTO ZZ_FOLDER VALUES (3, 'Second Level B') INSERT INTO ZZ_FOLDER VALUES (4, 'Second Level C') INSERT INTO ZZ_FOLDER VALUES (5, 'Third Level A1') INSERT INTO ZZ_FOLDER VALUES (6, 'Third Level A2') INSERT INTO ZZ_FOLDER VALUES (7, 'Third Level A3') INSERT INTO ZZ_FOLDER VALUES (8, 'Third Level B1') INSERT INTO ZZ_FOLDER VALUES (9, 'Third Level C1') INSERT INTO ZZ_FOLDER VALUES (10, 'Third Level C2') INSERT INTO ZZ_FOLDER VALUES (11, 'Fourth Level A3A') INSERT INTO ZZ_FOLDER_PROP VALUES (1, 2, 'Folder', 1) INSERT INTO ZZ_FOLDER_PROP VALUES (2, 3, 'Folder', 1) INSERT INTO ZZ_FOLDER_PROP VALUES (3, 4, 'Folder', 1) INSERT INTO ZZ_FOLDER_PROP VALUES (4, 5, 'Folder', 2) INSERT INTO ZZ_FOLDER_PROP VALUES (5, 6, 'Folder', 2) INSERT INTO ZZ_FOLDER_PROP VALUES (6, 7, 'Folder', 2) INSERT INTO ZZ_FOLDER_PROP VALUES (7, 8, 'Folder', 3) INSERT INTO ZZ_FOLDER_PROP VALUES (8, 9, 'Folder', 4) INSERT INTO ZZ_FOLDER_PROP VALUES (9, 10, 'Folder', 4) INSERT INTO ZZ_FOLDER_PROP VALUES (10, 11, 'Folder', 7) GO /* Top Level Second Level A Third Level A1 Third Level A2 Third Level A3 Fourth Level A3A Second Level B Third Level B1 Second Level C Third Level C1 Third Level C2 */ Tag: Parameters in an OLE DB Command
  - 10
    - c# reusing parameters I have a select statement which requires numerous parameters. here is a snippet. SqlCommand cmd = new SqlCommand("SELECT this from MyTable WHERE Answer1 = @Att 1AND Answer2 = @Att2 AND Answer3 = @Att3", connection) my parameters are added as follows. SqlParameter Att1 = new SqlParameter("@Att1", SqlDbType.VarChar, 50); Att1.Value = Attributes1; cmd.Parameters.Add(Att1); and so on... What I would like to do is be able to remove a parameter and re-run the SELECT statement if the number of entries retrieved is less than 5 (or any number) I tried just having a new Sql command like this. SqlCommand cmd2 = new SqlCommand("SELECT this from MyTable WHERE Answer1 = @Att 1AND Answer2 = @Att2, connection) and then did this.. cmd2.Parameters.Add(Att1); but it didn't work. Is there a way to do this so I don't have to keep copying the whole parameter command Thank you in advance for your help. And please be gentle, I'm very new to this. Tag: Parameters in an OLE DB Command
  - 11
    - allocation problem/disk space disappears when processing cube ok, but why does the disk space disappears it goes away in such way that free space + used space does not add up. until ssas server is restarted. Tag: Parameters in an OLE DB Command
  - 12
    - searching a Unicode data within a Image data type Hi RML_NLD, Thing is like I have an application which has an editor just like this editor. so whatever contents we have written on editor, it saved in Image DataType. I may have some hindi text as well. suppose i want to find some hindi text within our column, i need to have full-text catalog. so here my Question is: i have an Image Data Type with Column name 'ResourceText' which is contains also some unicode data as well. now i want to find some unicode data from that particular Column, how do i that If i have only English character on editor & stored in Image data type column, i am able to find it with the help of Full-Text Catalog, but now suppose i have some characters rather than English one, suppose consider an "Hindi Character" , & storing a all contents in that particular column, i m unable to find a that character using Full-Text Catalog. so i need a solution that how do i find some unicode data from Image Data Type using Full-Text Catalog. e.g. select * from tblResources where FreeText (ResourceText,' ') I am unable to find this '' " text from ResourceText which is Image Data Type in SQL Server 2000 , but if i try to search something like: select * from tblResources where FreeText (ResourceText,'English word'), then it shows me desired result. so my Full Text catalog is not supported for that as i guess to find some some text rather than English characters. i don't want to take ntext because Editor may contains so many things like Images and Everything like that. So how to do with Image Data type to find some characters rather then English one. I hope you understand my problem & will help for the same. waiting for your response soon. Dear, Thanks in Advance for helping me in this manner I am using SQL Server 2000. Thanking You, Harshal Choksi Tag: Parameters in an OLE DB Command
  - 13
    - SQL 2005 32-bit vs 64-bit guidance What some good reasons to select 64-bit rather than 32-bit for SQL Server 2005 We are preparing to install a very large SQL Server 2005 cluster. Initially, the primary database is estimated at 1 Terabyte, with 5 more databases that will add up to another Terabyte. After a few years, the estimate jumps up to 20 Terabytes for the largest DB and another 20 for the other five, combined. We've made the assumption that a database this large, with up to 1000 concurrent users, that this is a good candidate for a 64-bit installation. And that we're better off starting out with 64-bit, rather than migrating later. Can someone provide some guidance Tag: Parameters in an OLE DB Command
  - 14
    - Setting a filter in the SELECT-statement Would adding [Sales Code] to the subquery group solve your problem Like this: SELECT [DatabaseName$Item].No_, [DatabaseName$Item].Description, [DatabaseName$Sales Price HAG].[Unit Price], [DatabaseName$Sales Price HAG].[Starting Date], [DatabaseName$Sales Price HAG].[Starting Time], [DatabaseName$Sales Price HAG].[Sales Code] (<== here on this item I want to set a filter) FROM [DatabaseName$Item] INNER JOIN [DatabaseName$Sales Price HAG] ON [DatabaseName$Item].No_ = [DatabaseName$Sales Price HAG].[Item No_] INNER JOIN ( SELECT [Item No_] , [Sales Code] , MAX([Starting Date]) AS MaxStartingDate, MAX([Starting Time]) AS MaxStartingTime FROM [DatabaseName$Sales Price HAG] AS [DatabaseName$Sales Price HAG_1] GROUP BY [Item No_] , [Sales Code] ) AS SubQuery1 ON [DatabaseName$Sales Price HAG].[Item No_] = SubQuery1.[Item No_] AND [DatabaseName$Sales Price HAG].[Starting Date] = SubQuery1.MaxStartingDate AND [DatabaseName$Sales Price HAG].[Starting Time] = SubQuery1.MaxStartingTime AND [DatabaseName$Sales Price HAG].[Sales Code] = SubQuery1.[Sales Code] This will give you the MAX startingdate & time for each [Item No_] + [Sales Code] combination. So, if you have 3 sales codes for an item, you will get 3 records back from the subquery, each with their own MAX startingdate & time. Hope this helps. Jarret Tag: Parameters in an OLE DB Command
  - 15
    - ADO Error: an insufficient number of arguments.... Well, actually the answer is in your question. It did not need a parameter in the past while it was a view so the other views were using it. After a while (according to your text) you turned it in to a function and add a parameter but you did not change your old views which uses this ex-view to use a parameter. You still call this function from your views without a parameter and that's why SQL Server yells for a parameter. Tag: Parameters in an OLE DB Command
  - 16
    - Installing SQL Server 2000 SP3a Thank you Vidhya. I apply Sp4 regards Theo Tag: Parameters in an OLE DB Command

ChrisHelt

Hello!

Is there a way to use an OLE DB Command transform to execute the following SQL query

"Select sum(length(" + @[User::varDBName] + ":informix.cyh_t.u_obj)) from " + @[User::varDBName] + ":informix.cyh_t, " + @[User::varDBName] + ":informix.cch_t
where " +
@[User::varDBName] + ":informix.cch_t.d_obj = {D '" + @[User::strQueryDate] + "'} and " +
@[User::varDBName] + ":informix.cch_t.n_objid = " + @[User::varDBName] + ":informix.cyh_t.n_objid"

I get a DB number from an OLE DB Source, do a lookup to get the DB name and then I want to use the OLE DB command to get a result from the query but I can't seem to get this to work. Am I using the wrong transform

Thanks.

Re: Parameters in an OLE DB Command

Jamie Thomson

Not quite sure what you want to do here but issuing a SELECT statement from an OLE DB Command won't do anything. It won't insert records into the pipeline for you. If that is what you want to do then LOOKUP is more suitable.

-Jamie

Re: Parameters in an OLE DB Command

Phil Brammer

Are you querying the same database for all of your information Or are you going at multiple databases You might be better off doing some of this work in the control flow if you have different databases. If they are the same databases, then just issue a compound query.

Re: Parameters in an OLE DB Command

ChrisHelt

Thank you both for your replies. I think I have a flaw with my flow. I am querying multiple databaes so I'll try reworking the flow.

Thanks again!