Hi All,
Using a data access mode of SQL Command, I have a stmt that returns one date ( e.g select max(LastChangedDate) from TblA ).
I want to store the result in a variable. How do I do this?
Cheers,
Tamim.
I am currently writing a custom source adapter that extracts data from a JD Edwards OneWorld system. In the custom user interface of the source component I need to allow the user to set a query (a custom property), and then refresh a list of output columns that will be extracted based upon that query (similar to the list shown in the advanced editor).
My question is, can I apply the custom property change to the component and build my output column list without closing and restarting my custom UI form? I understand that the IDTSComponentUI interface being implemented allows for transactional editing of the component, in that the changes to the component are not applied until I have returned a result in the implemented Edit() method. However is there a way to apply changes without returning this result (and closing my UI)?
Essentially I am looking to have similar behaviour to that of the 'Refresh' button in the advanced component editor form.
Thanks
I am using an "XML Source Adapter" in an SSIS Package to try and load an XML file into a database table. But having trouble with a date field.
The XSD defines the field as:-
<xs:element minOccurs="1" name="DateTime" type="xs: string" />
The XML looks like this:-
<DateTime>12/31/2007 10:12:14.123</DateTime>
And the database table column is defined:-
[DateTime] [datetime] NOT NULL,
So in the Data Flow I have a "Data Conversion Transformation" converting [DT_WSTR] to [DT_DBTIMESTAMP].
However, when I run the package I get:-
Error: 0xC02020C5 at Load XXX File, Data Conversion 1 [13550]: Data conversion failed while converting column "DateTime" (15100) to column "DateTimeDT" (15422). The conversion returned status value 2 and status text "The value could not be converted because of a potential loss of data.".
So what how do I fix it?
I don't mind losing some miliseconds of precision, so I have tried configuring the error output of the Data Conversion for this field to "ignore failure" for "Truncation". But that seems to have no effect.
I'd be happy to change the XSD to:
<xs:element minOccurs="1" name="DateTime" type="xs:dateTime" />
But the "XML Data Flow Source" doesn't seem to support the dateTime data type.
Any suggestions appreciated. Thanks.
I'm having alot of trouble figuring out the proper way to log from inside my custom source adapter.
I couldn't find my useful information in MSDN, it mostly applies to logging from inside a script task.
I'd like to log my messages along with all the other SSIS package log entries, which I have going to the dts log table.
I'm assuming I should be using some functionality from Microsoft.SqlServer.Dts.RunTime, probably the LogProvider.
Can anyone advise?
I'm working on a new project using SSIS in SQL Server 2005 and have an issue that I need resolved.
I'm loading an XML file into SSIS using the XML Source Adapter. This maps to an OLE DB destination which reads the data into a SQL table (please see below for the table structure). In between this stage is a data conversion to convert the Unicode characters from XML into non-Unicode characters.
CREATE TABLE [dbo].[XMLTest](
[Forename] [varchar](50) COLLATE Latin1_General_CI_AS NULL,
[Surname] [varchar](50) COLLATE Latin1_General_CI_AS NULL,
[PostCode] [varchar](50) COLLATE Latin1_General_CI_AS NOT NULL
)
The problem I have is with blank strings in xml are being inserted into the SQL table as NULL values and not blanks (see the xml below).
<Root>
<People>
<Forename>Duncan3</Forename>
<Surname></Surname>
<PostCode>ME16 0WH</PostCode>
</People>
</Root>
Correct me if I'm wrong but a null value in xml means the node does not exist (See the xml below)?
<Root>
<People>
<Forename>Duncan3</Forename>
<PostCode>ME16 0WH</PostCode>
</People>
</Root>
Please also find the schema attached for reference.
<?xml version="1.0"?>
<xschema attributeFormDefault="unqualified" elementFormDefault="qualified" xmlns:xs="http://www.w3.org/2001/XMLSchema">
<xs:element name="Root">
<xs:complexType>
<xsequence>
<xs:element minOccurs="0" name="People">
<xs:complexType>
<xsequence>
<xs:element minOccurs="1" name="Forename" type="xstring" />
<xs:element minOccurs="0" name="Surname" type="xstring" />
<xs:element minOccurs="1" name="PostCode" type="xstring" />
</xsequence>
</xs:complexType>
</xs:element>
</xsequence>
</xs:complexType>
</xs:element>
</xschema>
I am using the "XML Source Adapter" in an SSIS package.
I notice that although you can specify the XML filename as an expression, the XSD appears to have to be a fixed file path.
This is a problem for me since the path for the XSD is different in my development than it will be in production (in production it's on drive E:, which I don't have).
I'd like to have the file location specified in the config file, but since I can't make it an expression how can I do that?
Also, since they don't have Connection Managers I can't switch DelayValidation on.
Thanks.
I am a business user trying to build an incremental ETL package with SSIS. I have a working prototype on SQL Server 2005 where I select the max(ID) from the last successful run and pass that value into a variable. Then, in my Data Flow step, I select an OLE DB source adapter and use this variable in a custom select statement.
Here's my challenge....the live data is actually in a Postgres DB so I have to use a Data Reader Source adapter. When I try to pass my variable to this adapter the job bombs out. Does anyone know how to dynamically update the query text inside a Data Reader source adapter using variables or otherwise?
Hi,
Quick question on how SSIS handles queries from Data Source in a Data Flow. I noticed that when I run a particular query from Query Analyzer it takes forever. But, when I run the same query in SSIS data source in a data flow. The query results are immediate.
The query plan is already cached in SQL.
Is this just something which I am seeing incorrect or is there some bit of optimization in there in SSIS. As per my understanding SSIS does not optimize the source query.
Thanks,
Gaurav
Hi
I am using Oledb Provider for Db2 from IBM. (There is another one from microsoft)
Through this provider I am constructing a oledb connection manager.
This connection manager I am Using in Oledb Source adapter.
Now when I set Data Access mode as Table or view I Am able to preview the data.
But when I use Data Access Mode as SQL Command try to preview I get this Error
TITLE: Microsoft Visual Studio
------------------------------
Error at s_NEWMAPPING10 [EMP [1]]: An OLE DB error has occurred. Error code: 0x80040E21.
------------------------------
ADDITIONAL INFORMATION:
Exception from HRESULT: 0xC0202009 (Microsoft.SqlServer.DTSPipelineWrap)
------------------------------
BUTTONS:
OK
------------------------------
I am not able to figure out how to resolve this.
I want to use the excel sheets located on a share point site as source, for this i downloaded the adapter from the following link URL....I got the connection manager to work, but when coming to load the excel files to the destination location its not doing what is intended. For example i want the destination location my local drive for the excel files, how can i do that which destination should i use?
View 2 Replies View RelatedI created a OLE DB Source, and created a sql command text.
The issue is with parameters, I have to use the '?' to identify.
What if I have multiple variables of the same type scatter around, how can I define that?
Somthing like this would be good...
Declare @month
set @month = ?
select blah,@month where month=@month
or
How could I use the ADO Syntax and just put @month?
Thanks,
Mardo
Hi,
I'm having an issue where it does not look like my variable is getting passed in with my source query. I have a date in a variable (which I have checked with a breakpoint to determine that it is getting set) and I am attempting to limit my query based on this date.
The query in the OLE DB Source is as follows:
SELECT
a.TransactionNumber,
d.StoreCode,
cast((convert(varchar, a.[time], 102) + ' ' + substring(convert(VARCHAR, a.[time],8), 1, charindex(':', convert(VARCHAR, a.[time],8))-1) + ':00:00') as datetime) as Time,
a.CashierID,
c.ItemLookupCode AS PLU,
b.FullPrice,
b.SalesTax,
b.Quantity,
b.Price
FROM
[Transaction] a
JOIN
TransactionEntry b
ON
a.TransactionNumber = b.TransactionNumber
JOIN
Item c
ON
b.ItemID = c.ID
JOIN
Store d
ON
a.StoreID = d.ID
WHERE
a.Time >= ?
In the 'parameters' window I have set up a mapping where Parameter0 maps to the User:LastTransactionDate parameter.
However, this does not seem to be applied and I always get ALL records back from the source table as if there were no WHERE statement at all.
Anyone have any input on this? Am I missing something obvious?
Thanks
Greetings SSIS friends,
I have tried using a sql command for my data reader source. I added the following expression to my datareader source :
"select * from result where result_id > " + @[max_result_id]
but I get the following error message :
The SQL command has not been set correctly. Check SQLCommand property.
I then got rid of the variable (defined at package level) and replaced it with a string like so :
"select * from result where result_id > " + "123456"
but I still get the same error message!
What am I doing wrong?!
Please advise.
Hi,
I have created lastUpdatedDate variable on package level. I have run a sql task and store a date in that variable.
now i am trying to pass that variable as parameter to oledb source connection (using command). it seems that we cant pass parameter in any sub query or derived table in query. its only working in outer query as soon as we place ? in WHERE clause of inner query it start throwing an 'Syntax Error' error saying that connection provider might not support that.
any idea ?????
I dont want to use command variables as my query is going to be quite big.
Note : I have tried Sql Server Native and OLEDB provider for sql server and this behaviour is seems to be constant in both.
Thanks,
Furrukh baig
[Microsoft follow-up]
In the Raw File Source component when I set AccessMode='File name from variable' and select the drop down for 'FileNameVariable' property the first thing I see is all the system variables. What's the point in displaying those? They are completely useless in this context. Would it really have been that hard to remove them?
Can this be changed please?
-Jamie
I am running a sql task which will pass table as object variable to the result set
I have a for each loop container which is used to loop for all the servers. I use two of the parameters to establish connection string in the for each loop task from the reasult set variables.
Now my next step is a data flow task (Data Reader Source) where i have to run a query but the table name and column names are dynamic and i dont see an option to call variables.
can someone tell me if this is possible (var1,var2,... are variables in the package scope)
select var1, var2 from var3.var4
where var5 = 'y'
Hi Guys,
(I have searched this forum extensively, but still can't find the solution to this problem)
Here it is:
I have step in my ETL process that gets facts from another database. Here is how I set it up:
1) I have to package variables called User::startDate and User::endDate of data type datetime
2) Two separate Execute SQL Tasks populate those variables with appropriate dates (this works fine)
3) Then I have a Data Flow Task with OLE DB source that uses a call to a sproc of the form "exec ETL_GetMyData @startDate = ?, @endDate = ?" with parameters mapped accordingly (0 -> User::startDate, 1 -> User::endDate)
When I run this I get an error 0xC0207014: "The SQL command requires a parameter named "@startDate", which is not found in the parameter mapping."
It is true that the sproc in fact requires @startDate and @endDate parameters, so next thing I tried to do is call the sproc the following way: "exec ETL_GetMyData @startDate = ?, @endDate = ?"
To no avail. It gives me the same error. Incidentally, when I hard code both dates like "exec ETL_GetMyData '2006-04-01', '2006-04-02'" everything works well.
Also, I want to mention that in the first two cases, I get an error right in the editor. When I try to parse the statement it gives me "Invalid parameter number" message.
This has been such a pain in my neck. I've waisted the whole day trying to monkey with the various parts of package/statements to get this to work and it still doesn't. I dont' want to say anything about Integration Services design right now, but you probably know what I'm thinking...
Hi
I am after some help with the following issue
I have a package that reads a table for a file path of a excel file. This gets passed to a variable and then this file is imported into a staging table for further transformation work. The issue i have is that file 1 may contain data in Column A which is 50 characters long in which case i have to import the excel as a DT_WSTR, do a data conversion to a DT_STR and load to the staging table. However file 2 may contain data in Column which is over 255 characters in which case it would import as a DT_NTEXT which i then transform to a DT_TEXT and then to a DT_STR. I used a fixed file path in the Excel Connection to start with which was for File 1 so the datatype for column 1 is a DT_WSTR. I then changed the excel connection to a filepath variable, put the path of file 2 in my table and called it from my package. It failed as the data exceeding 255 characters in column 1 needed to be a DT_NTEXT. I can change it and it works but if i then run the package using file 1 (less than 255 characters) it fails again as it wants it to be a DT_WSTR.
Is there anyway around this? Am i missing something as i would have thought that by setting it to DT_NTEXT this would cover data under 255 characters as well.
Any help is gratefully aprreciated.
Learning how to use SSIS...
I have a data flow that uses an OLEDB Source Component to read data from a table. The data access mode is SQL Command. The SQL Command is:
select lpartid, iCallNum, sql_uid_stamp
from call where sql_uid_stamp not in (select sql_uid_stamp from import_callcompare)
I wanted to add additional clauses to the where clause.
The problem is that I want to add to this SQL Command the ability to have it use a package variable that at the time of the package execution uses the variable value.
The package variable is called [User::Date_BeginningYesterday]
select lpartid, iCallNum, sql_uid_stamp
from call where sql_uid_stamp not in (select sql_uid_stamp from import_callcompare) and record_modified < [User::Date_BeginningYesterday]
I have looked at various forum message and been through the BOL but seem to missing something to make this work properly.
http://msdn2.microsoft.com/en-us/library/ms139904.aspx
The article, is the closest I have (what I belive) come to finding a solution. I am sure the solution is so easy that it is staring me in the face and I just don't see it. Thank you for your assistance.
...cordell...
Hello, I am trying to configure a Script Component as a data source. Although this should be a simple exercise, I am running into a problem.
My control flow contains a Foreach Loop with a file iterator. The Directory Expression of the Foreach Loop Editor is supplied by an expression mapped to a package level variable called inputdirectory. The FileNameRetrieval Expression is mapped to a package scoped variable called filename.
My data flow is encapsulated by the Foreach Loop, and contains a Script Component as Source for the Data Flow Source, and a Flat File for the Data Flow Destination. The contents of the Script Designer are as follows:
Code Block
Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper
Imports System.IO
Public Class ScriptMain
Inherits UserComponent
Dim thisFileDate As Date
Dim thisFileName As String
Dim thisFilePath As String
Public Overrides Sub CreateNewOutputRows()
thisFileName = ReadOnlyVariables("filename").Value.ToString()
thisFilePath = ReadOnlyVariables("inputdirectory").Value.ToString()
thisFileDate = File.GetCreationTime(thisFilePath & "" & thisFileName)
FileSpecBuffer.AddRow()
FileSpecBuffer.FileName = thisFileName
FileSpecBuffer.FullPath = thisFilePath & "" & thisFileName
FileSpecBuffer.CreateDate = thisFileDate
End Sub
End Class
When I debug the package, I get the following error:
The collection of variables locked for read access is not available at this point.
at Microsoft.SqlServer.Dts.Pipeline.ScriptComponent.get_ReadOnlyVariables()
at ScriptComponent_67311120e6eb4162a3ea1f70847f04de.ScriptMain.CreateNewOutputRows()
at ScriptComponent_67311120e6eb4162a3ea1f70847f04de.UserComponent.PrimeOutput(Int32 Outputs, Int32[] OutputIDs, PipelineBuffer[] Buffers)
at Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost.PrimeOutput(Int32 outputs, Int32[] outputIDs, PipelineBuffer[] buffers)
My googlefu fails me at reconciling this. I have read various posts about changing the line ReadOnlyVariables("filename").Value to ReadWriteVariables("filename").Value, and handling the buffer assignment in PostExecute. The problem is that all examples shown are either for Script Component as Destination or Script Component as Transformation. I have tried playing with the Custom Properties in the Script Commponent set the ReadOnlyVariables and ReadWriteVariables, using a PreExecute method, a PostExecute method, all with different errors returning. I'm at a loss here. Could anybody provide me with a simple working example so that I can correctly populate my output buffer in the context of Script Component as Source?
I fully understand that I could just run a Script Task Using System.IO.Directory, and System.IO.File, but I really want to make this package work in the manner I've described. Any help would be appreciated.
Thanks,
John T
I am trying to get the contents of the Excel Files dynamically and dumping into the SQL Database using SSIS. Through WMI Event Watcher, I could find when one or more Excel files dumped in a particular folder and using ForEach Loop Container I was able to take all the filenames and pass it through Variables. But at the same time in the Data Flow, I have to pass each Sheet of an Excel File to the Excel Source control and export the data to my SQL Database using OLEDB Destination.
For that I need to get the names of each sheets in an Excel File and pass it to the Excel Source Control through variables. But when I give Data Access Mode as "Table name or view name variable" and provide the variable name in that, then it is giving an error message as "A destination table name has not been provided".
And at the same time, Since I was not able to provide an static Filename (as I am passing through Variables), when I tried to map the columns in the OleDB Destination, it is not allowing me to map the columns.
So all these things I should do at Run-time using Variables in SSIS. I don't want to hard-code any filenames or Sheet names. If any one of you have a solution, please share with me.
Thanks & Regards,
Prakash Srinivasan
hi I have this asp sql statement which works fine but I want to use an spl adapter instead as I am enabling paging in details view. I am stuck in one place. How do I declare @listing as I have done in the select parameter in the asp sql statement? Where in ths sql adapter do i declare it? listing value is derived from a dropdown box when the user makes a selection.
This is the sql statement i want to declare in the adapter.<asp:SqlDataSource ID="SqlDataSource1" runat="server"
ConnectionString="<%$ ConnectionStrings:ASPNETDBConnectionString1 %>" SelectCommand="SELECT UserDetails.UserId, UserDetails.FirstName, UserDetails.LastName, UserDetails.City, aspnet_Membership.Email FROM UserDetails INNER JOIN
aspnet_Membership ON UserDetails.UserId = aspnet_Membership.UserId WHERE Category = @Listing">
<selectparameters>
<asp:controlparameter name="Listing" controlid="SearchListingDropDownList" propertyname="SelectedValue"/></selectparameters>
This is the adapter so far which gives me an error to declare scalable value @listing. How do i incoporate the select parameters below?
Function getTheData() As DataTable Dim DS As New DataSet()
Dim strConnect As New SqlConnection(ConfigurationManager.ConnectionStrings("ASPNETDBConnectionString1").ConnectionString)Dim objSQLAdapter As New SqlDataAdapter("SELECT aspnet_Membership.UserId,UserDetails.FirstName + ' ' + UserDetails.LastName AS 'Customer',UserDetails.City, aspnet_Membership.Email FROM aspnet_Membership INNER JOIN UserDetails ON aspnet_Membership.UserId = UserDetails.UserId WHERE Category = @Listing", strConnect)objSQLAdapter.Fill(DS, "aspnet_Membership,UserDetails")
Return DS.Tables("aspnet_Membership,UserDetails").Copy
End Function
www.mylookmeup.co.uk
I am running Windows XP and have SQL Server 2005 Enterprise Edition that I use for testing on my notebook. I want to install Oralce 11g on it as well and use VS 2005 as a front end programming. For me to install Oracle I need to install a loopback adapter. If I do this will it mess up SQL Server 2005 or will it not affect SQL Server 2005? Please advise
View 1 Replies View RelatedCan someone tell me why I an getting the following error message for my table adapter: "incorrect syntax near '?'"
The selecte statement is below.
SELECT Questions.QuestionID, Questions.QuestionNumber, Questions.QuestionText, Questions.SampleAnswer, SAFSection.SectionNumber + ' - ' + SAFSection.SectionName as [Section], SAFSubSection.SubSectionNumber + ' - ' + SAFSubSection.SubSectionName as [SubSection], SAFSubSection.SubSectionDefinition, SAFSubSection.SubSectionQuestion, SAFSubSection.SubSectionInstruction, Answers.AnswerFROM (SAFSubSection INNER JOIN (SAFSection INNER JOIN Questions ON SAFSection.[SectionID] = Questions.[QuestionSectionID]) ON SAFSubSection.[SubSectionID] = Questions.[QuestionSubSectionID]) LEFT JOIN Answers ON Questions.QuestionID = Answers.QuestionIDWHERE Answers.SystemID = ?Order BY SAFSection.SectionID, SAFSubSection.SubSectionID,Questions.QuestionID
Hi,
I am not comfortable with DTS 2000 but I need to execute a encapsulated DTS 2000 package from a SSIS package. The real problem is when I need to pass SSIS variables to DTS 2000 package. The DTS 2000 package have 3 global variables that I can identify on " Execute DTS 2000 Package Task Editor - Inner Variables ". I believe the SSIS variables must be mapped on " Execute DTS 2000 Package Task Editor - OuterVariables ". How can I associate the SSIS variables(OuterVariables ) to "Inner Variables"? How can I do it? Much Thanks.
João
Hi,
I would like to design a SSIS package, which have couple of variables. It loads a xls file specified in a variable [varExcelFileFullPath] .
I will run it by commands: exec xp_cmdshell 'dtexec /SQL ....' (pls see an example below).
It seems it does not get the values passed in for those variables. I deployed the package to a sql server.
are there any grammar errors here? I copied it from dtexecui. It worked inside Dtexecui not in dos command.
exec xp_cmdshell 'dtexec /SQL "LoadExcelDB" /SERVER test /USER *** /PASSWORD ****
/MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING EW
/LOGGER "{6AA833A1-E4B2-4431-831B-DE695049DC61}";"Test.SuperBowl"
/Set Package.Variables[User::varExcelFileName].Properties[Value];"TestAdHocLayer"
/Set Package.Variables[User::varExcelWorkbookName].Value;"Sheet1$"
/Set Package.Variables[User::varExcelFileFullPath].Value;"D: estshareTestAdHocLayer.xls"
/Set Package.Variables[User::varDestinationTableName].Value;"FeaturesTmp"
/Set Package.Variables[User::varPreSQLAction].Value;"delete from FeaturesTmp"
'
thanks,
Guangming
In VS2003 I have a user defaults table that I would call in the OnLoad - No Postback section to get the user defaults and then used some of the fields from that recordset as Parameters for other recordsets that would also load in that same section and might even issue a different Sql statement based on the field. In VS2005 (VB.NET), I can no longer figure out how to accomplish this. I can use the SQLDatasource Control which seems much improved but I cannot even find the generated code which I would use as a quick way to create my own datasources in the OnLoad event. Where is this code now stored and how can I accomplish my objectives in VB.NET 2005 with the current framework or programatically create data sources as necessary as I did before?
View 3 Replies View RelatedHello,
I have a table adapter which uses a Query string to get the select view and a stored procedure for its delete portion. Not all of the columns in the select statement are required for the stored procedure. When I try to do the delete, it attempts at passing in more variables than it needs and ends up resulting in an error saying it can't find a procedure that is valid. Is there any way to restrict which columns are being passed?
Thanks,
Chris
Hi All,
I have built a custom flat file destination adapter but it appears that the code is not working. When I debug the process I notice that the ProcessInput section is called multiple times. The first time it looks like everything is working, then it call it again and there is no inpout from the DTSInput90.
Why would it do this?
Thanks
Mike
I have been struggling with the Adapter Fill issue for days.
I got an eeror "Invalid object name 'Weather'." Why? How to modify it?
Thanks
Code Snippet
using System;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using My.Name.Space;
public partial class GetStrings : System.Web.UI.Page
{
getStrings g = new getStrings();
protected void Page_Load(object sender, EventArgs e)
{
string strConnectionString = ConfigurationManager.ConnectionStrings["SqlConnectionString"].ConnectionString;
SqlConnection myConnection = new SqlConnection(strConnectionString);
DataSet myDataSet = new DataSet();
try
{
myConnection.Open();
// create a new DataSet
// create the data
GenerateDataSet(myDataSet, myConnection);
}
finally
{
myConnection.Close();
// bind each to table to a grid
GridView1.DataSource = myDataSet.Tables["Weather"];
}
// databind the page
GridView1.DataBind();}
void GenerateDataSet(DataSet dset, SqlConnection conn)
{
AddTable(dset);
FillTable(dset, conn);
}
void AddTable(DataSet dset)
{
// create the table
DataTable WeatherTable = new DataTable("Weather");
DataColumn WeatherID = WeatherTable.Columns.Add("State", typeof(string));
WeatherTable.Columns.Add("Division", typeof(String));
WeatherTable.Columns.Add("TEMP", typeof(string));
WeatherTable.Columns.Add("PCPN", typeof(string));
WeatherTable.Columns.Add("UL", typeof(string));
WeatherTable.Columns.Add("LL", typeof(string));
WeatherTable.Columns.Add("PCT", typeof(string));
WeatherTable.Columns.Add("POT", typeof(string));
WeatherTable.Columns.Add("RO", typeof(string));
WeatherTable.Columns.Add("MOIST_INDEX", typeof(string));
WeatherTable.Columns.Add("CF", typeof(string));
WeatherTable.Columns.Add("MNA", typeof(string));
WeatherTable.Columns.Add("DROUGHT_INDEX", typeof(string));
WeatherTable.Columns.Add("DROUGHT", typeof(string));
// set the column properties
WeatherTable.Columns["State"].MaxLength = 50;
WeatherTable.Columns["Division"].MaxLength = 50;
WeatherTable.Columns["TEMP"].MaxLength = 50;
WeatherTable.Columns["PCPN"].MaxLength = 50;
WeatherTable.Columns["UL"].MaxLength = 50;
WeatherTable.Columns["LL"].MaxLength = 50;
WeatherTable.Columns["PCT"].MaxLength = 50;
WeatherTable.Columns["POT"].MaxLength = 50;
WeatherTable.Columns["RO"].MaxLength = 50;
WeatherTable.Columns["MOIST_INDEX"].MaxLength = 50;
WeatherTable.Columns["CF"].MaxLength = 50;
WeatherTable.Columns["MNA"].MaxLength = 50;
WeatherTable.Columns["DROUGHT_INDEX"].MaxLength = 50;
WeatherTable.Columns["DROUGHT"].MaxLength = 50;
GetDataRow(WeatherTable, g.gets());
// add the table
dset.Tables.Add(WeatherTable);
}
void FillTable(DataSet dset, SqlConnection conn)
{
// create the Command and DataAdapter
SqlDataAdapter WeatherAdapter = new SqlDataAdapter();
SqlCommand WeatherCommand = new SqlCommand("SELECT State, Division,MOIST_INDEX,DROUGHT_INDEX FROM Weather", conn);
WeatherAdapter.SelectCommand = WeatherCommand;
// fill the DataTable
WeatherAdapter.Fill(dset,2,0,"Weather");//wrong here,
//WeatherAdapter.Fill(dset.Tables["Weather"]);
}
//assuming the first dimention is collumn and the second is row
public void GetDataRow(DataTable datatable, string[][] source)
{
DataRow dr;
int rows = source.GetLength(0);
int collumns = source[0].Length;
for (int i = 0; i < rows; i++)
{
dr = datatable.NewRow();
if (source[i] != null)
{
dr.ItemArray = (object[])source[i];
datatable.Rows.Add(dr);
}
}
datatable.AcceptChanges();
}
}
I downloaded and installed the Informix Client SDK 3.0 and was able to extract data from an Informix 10 database. All of a sudden, it stopped working. The error message I received is "... error in initializing provider. (-25580) System error occurred in network function.". Has anyone encountered this error before and know of a solution?
Thanks.
Now, I don't know if what I want to do is possible, but here goes. In the table I want to query, there is an "approval status" column, of type Int32. There are four approval levels, 1, 2, 3 and 4. What I want to set up is a query in the table adapter that can return all entries of one or more approval levels. In "raw" sql, I would do something like: SELECT * FROM facility_table WHERE (approved IN (1,2,3));What I want to do though, is to have the list of approval codes to be a parameter that I can pass to the table adapter query, so the where clause becomes "WHERE (approved IN (@approval))", and I pass a string with the list of approval codes. But the query designer doesn't want to cooperate with me, as it insists that "@approval" should be an int32.Any suggestions?
View 1 Replies View RelatedI have an application (ASP.NET 2.0/SQL Server 2005) which makes heavy use of table adapters for pulling records from SQL. Under heavy load, we get a lot of SQL Server Timeout errors. We have run a trace on SQL Server and it shows that several of the SQL statements being passed into SQL Server, from the Table Adapters, have bad SQL.
For example, here is the SQL in one of the table adapters
SELECT HomeMsgID, messageName, messageHTML, messageText, populationID
FROM MyUCR_HomeMessages
WHERE (populationID IN
(SELECT populationID
FROM MyUCR_Population_CPID AS
MyUCR_Population_CPID_1
WHERE (CPID = @CPID))) AND (isVisible = 1)
AND (showDate <= @showDate) AND (removeDate >= @removeDate)
I call it with the following:
DateTime showDate = DateTime.Today;
DateTime removeDate = DateTime.Today;
myUCR_HomePageMsgsTableAdapters.MyUCR_HomeMessagesTableAdapter ta = new
myUCR_HomePageMsgsTableAdapters.MyUCR_HomeMessagesTableAdapter();
myUCR_HomePageMsgs.MyUCR_HomeMessagesDataTable dt = new
myUCR_HomePageMsgs.MyUCR_HomeMessagesDataTable();
ta.FillByCPID(dt, showDate, removeDate, CPID);
What the SQL trace shows, when it fails, is this (notice the extra single
quotes around the showDate, removeDate parameters):
E000
exec sp_executesql N'SELECT HomeMsgID, messageName, messageHTML,
messageText, populationID
FROM MyUCR_HomeMessages
WHERE (populationID IN
(SELECT populationID
FROM MyUCR_Population_CPID AS
MyUCR_Population_CPID_1
WHERE (CPID = @CPID))) AND (isVisible = 1)
AND (showDate <= @showDate) AND (removeDate >= @removeDate)',N'@showDate
datetime,@removeDate datetime,@CPID int',@showDate=''2007-02-05
00:00:00:000'',@removeDate=''2007-02-05 00:00:00:000'',@CPID=3071225
1[Microsoft][SQL Native Client][SQL Server]Incorrect syntax near '2007'.
I recreated the SQL to use a stored procedure, and got a similar error:
E000exec dbo.spFillHomeMsgByCPID @showDate=''2007-02-05
00:00:00:000'',@removeDate=''2007-02-05 00:00:00:000'',@CPID=3008195
5[Microsoft][SQL Native Client][SQL Server]Incorrect syntax near '2007'.
However, if I create dynamic SQL and use the following, there are no errors.
string mySql = string.Empty;
mySql = "SELECT HomeMsgID, messageName, messageHTML, messageText,
populationID FROM MyUCR_HomeMessages WHERE (populationID IN (SELECT
populationID FROM MyUCR_Population_CPID AS MyUCR_Population_CPID_1 WHERE
(CPID = " + CPID + "))) AND (isVisible = 1) AND (showDate <= '" + showDate +
"') AND (removeDate >= '" + removeDate + "')"; SqlDataAdapter adapter = new
SqlDataAdapter(mySql, ConfigurationManager.ConnectionStrings["MyUCR2007ConnectionString"].ToString());
DataSet RecordCount = new DataSet();
adapter.Fill(RecordCount);
DataTable testDT = RecordCount.Tables[0];
I am using VSTS with the Service Pack installed. SQL 2005 is running on W2K3 Enterprise, fully patchedThanks,James