I have a datareader and whenever the value that it is suppose to display in the Label is empty because there is no Data Entered Yet I gives me and error and I have the event set ONLOAD so the page with no data to disply does not load.
So the Ranking.text = a select statement i have, but when I dont have any data into the SQL database there is nothing to SELECT so the program gives of an error.
Hi I have written a piece of code for Login form which reads the user id and password from db. It works fine with the Sql server 2000 but I get a error with Sql server 2005. SqlConnection conn = new SqlConnection("Data Source=D\SQLEXPRESS;Initial Catalog=model;Integrated Security=True"); SqlCommand cmd = new SqlCommand("Select * from JsLoginDetails", conn); conn.Open(); SqlDataReader dr = cmd.ExecuteReader(); while (dr.Read()) { if ((Login1.UserName == dr.GetValue(0).ToString()) && Login1.Password == dr.GetValue(1).ToString()) { Response.Redirect("MainJs.aspx"); } else { Login1.FailureText = "Invalid Userid Or Password"; } } dr.Dispose(); conn.Close(); } I get and error Invalid object name 'JsLoginDetails'. pls help thnksdiv
I have a DTSX package which reads values from a fixed-length text file using a data reader and writes some of the column values from the file to an Oracle table. We have used this DTSX several times without incident but recently the process started inserting NULL values for some of the columns when there was a valid value in the source file. If we extract some of the rows from the source file into a smaller file (i.e 10 rows which incorrectly returned NULLs) and run them through the same package they write the correct values to the table, but running the complete file again results in the NULL values error. As well, if we rerun the same file multiple times the incidence of NULL values varies slightly and does not always seem to impact the same rows. I tried outputting data to a log file to see if I can determine what happens and no error messages are returned but it seems to be the case that the NULL values occur after pulling in the data via a Data Reader. Has anyone seen anything like this before or does anyone have a suggestion on how to try and get some additional debugging information around this error?
As you see in the images the connection is closing. During the read it counts 5 columns which is correct. When I step through the code it closes the connection when it hits dt.Load(reader) and nothing is loaded into the datatable.
------------------------------------------------------------AS I STEP THROUGH -----------------------------------------------------------------------------------------------------------------------
[DTS.Pipeline] Error: "component "Excel Source" (1)" failed validation and returned validation status "VS_NEEDSNEWMETADATA".
and also this:
[Excel Source [1]] Warning: The external metadata column collection is out of synchronization with the data source columns. The column "Fiscal Week" needs to be updated in the external metadata column collection. The column "Fiscal Year" needs to be updated in the external metadata column collection. The column "1st level" needs to be added to the external metadata column collection. The column "2nd level" needs to be added to the external metadata column collection. The column "3rd level" needs to be added to the external metadata column collection. The "external metadata column "1st Level" (16745)" needs to be removed from the external metadata column collection. The "external metadata column "3rd Level" (16609)" needs to be removed from the external metadata column collection. The "external metadata column "2nd Level" (16272)" needs to be removed from the external metadata column collection.
I tried going data flow->excel connection->advanced editor for excel source-> input and output properties and tried to refresh the columns affected. It seems that somehow the 3 columns are not read in from the source file? ans alslo fiscal year, fiscal week is not set up up properly in my data destination? anyone faced such errors before?
A data reader is using a connection manager to connect to an ODBC System DSN . A query in the SqlCommand property is provided. Data is being truncated in the only string column . The data type in data reader output-->external columns shows as Unicode string [DT_WSTR] Length 7.
The truncated output in a text file is the first 3 characters from left to right . Changing the column order has no effect.
A linked server was created in SQL Server Management Studio to test the ODBC System DSN using the following:
Data returned using "OPENQUERY" does not truncate the string column indicating that the ODBC Driver returns data as expected with sql 2005, but not with the Data Reader.
I am having a very wierd issue regarding a DB2 sql query. I need to get data from Db2 and insert into our sql server database. Using data flow task, to get data I am using the data reader source. COnnection is ado.netodbc connection. THis sql query also has some comments in it.
The first wierd thing is...
1. On Development server, when I run this query manually, meaning using toad, winsql (connection to the db2 database), the query runs fine. Brings back approx 667 rows which is correct. ON the same server when I try to run this query, via a SSIS pkg, data flow task, using data reader source, gives me error on those comments that exist in that query. But if I run the same SSIS pkg on another server (Integration server). It runs fine. The same pkg also runs fine if I run it from my machine. SO What is different on my Dev server compared to the Integration server.
2. Say if I take those comments out from the sql query, then try to run the ssis pkg. The query is stuck at the first record and goes in an infinite loop mode. though my query is not a procedure, it is just a sql statement. But this ssis pkg with the query runs absolutly fine on the other server. I aslo tried using the other types of connection and ole db source but still the same problem on the Dev server.
What do I need to look for that is so different on the dev server compare to the INT server. I also checked the version on both these server for Visual Studio 2005(by going to About Microsoft Visual Studio), it is the same.
This is what I have on both the servers.... Microsoft SQL Server Integration Services Designer Version 9.00.3042.00
i have a package in ssis that needs to deliver data from outside servers with odbc connection. i have desined the package with dataflow object that includes inside a datareader source. the data reader source connect via ado.net odbc connection to the ouside servers and makes a query like: select * from x where y=? and then i pass the data to my sql server. my question is like the following:
how do i config the datasource reader or the dataflow so it will recognize an input value to my above query? i.e for example:
select * from x where y=5 (5 is a global variable that i have inside the package). i did not see anywhere where can i do it.
is it possible to use a data reader to read from 2 tables with 1 store procedure(sp)? ------------------------------------------------- ex: create sp1 as
select * from tbl1 select * from tbl2 ------------------------------------------------- how can i use a data reader to read the items from tbl2?
Hi Guys,I have a quick question about DataReader, I have a function called "ExportTotal" i am calling this function from another function. what it does is it will put the Total for each Network in the Line 34. Right now what it is doing is It is putting the First Networktotal,2ndNetwork total,3rdNetwork total....... in the line 34. what i want is if the control comes to first network then it has to put only 1st network total and for the 2nd network only 2nd network total and so on. Please see my function below. Can you guys tell me what i am doing wrong?ThxPrivate Function exporttotal() As String Dim sql As String Dim dbFunctions As New DatabaseUtilities Dim tempinvoicetotal As String 'Dim rateactuals As String Dim filetext As String Dim tempclientname As String Dim strconn As String Dim prev_network As String = "" Dim current_network As String = "" strconn = CONNECTIONSTRING sql = "SELECT CAST(SUM(tblSpot.rateActual) AS int(4)) AS Rateactuals, SUM(tblSpot.rateActual * 0.85) AS netrate, SUM(tblSpot.rateActual * 0.15) AS commrate,TBLCLIENT.CLIENTNAME " & _"FROM tblSpot INNER JOIN tblContract ON tblSpot.fkContract = tblContract.pkid INNER JOIN " & _ " tblClient ON tblContract.fkClient = tblClient.pkid WHERE tblSpot.fkContractType = 'UNWIRED' AND " & _ "fkInvoiceNumber = '" & Me.txtinvoicenumber.Text & "' GROUP BY TBLCLIENT.CLIENTNAME" Dim myConn As New SqlConnection(CONNECTIONSTRING) Dim myCommand As New SqlCommand(sql, myConn) myConn.Open() Dim dbreader As SqlDataReader = myCommand.ExecuteReader() While dbreader.Read() Try Dim Rateactuals As String If dbreader("Rateactuals") Is DBNull.Value Then Rateactuals = "" Else Rateactuals = dbreader("Rateactuals") tempinvoicetotal = Rateactuals End If Dim clientname As String If dbreader("clientname") Is DBNull.Value Then clientname = "" Else clientname = dbreader("clientname") tempclientname = clientname End If If prev_network = "" Then filetext = filetext & vbCr & "34;;" & tempinvoicetotal & "00" & ";"Session("EDIExport4") = filetext prev_network = tempclientname Else current_network = tempclientname If prev_network <> current_network Then filetext = filetext & vbCr & "34;;" & tempinvoicetotal & "00" & ";"Session("EDIExport4") = filetext prev_network = tempclientname Else End If End If Catch SqlEx As SqlClient.SqlException Session("Error") = SqlEx.Message.ToString Response.Redirect("Error.aspx?Form=" & Request.Path) Catch Ex As System.Exception Session("Error") = Ex.Message.ToString Response.Redirect("Error.aspx?Form=" & Request.Path) End Try dbFunctions = Nothing End While myConn.Close() End Function
Hi All, I got an error while running this code.There is already an open DataReader associated with this Command which must be closed first How can I resolve the error?
protected void Page_Load(object sender, EventArgs e) { string sql; SqlConnection Connection = new SqlConnection("ConnectionString"); sql = "SELECT PO_SE_Line_ID FROM PO_STOCK_QUERY_LINE_DETAILS WHERE TRANS_NUM ='TR-A-00-01-93'"; SqlCommand command = new SqlCommand(sql, Connection); SqlDataReader Dr; Connection.Open(); Dr = command.ExecuteReader(); while (Dr.Read()) { sql = "SELECT SUPPLIER_ITEM_CODE,SUPPLIER_MAN_DESC,SUPPLIER_PAT_DESC,SUPPLIER_ITEM_DESC,SUPPLIER_ADDIT_DESC,SUPPLIER_SUGG_RETAIL FROM PO_STOCK_QUERY_LINE_DETAILS where TRANS_NUM ='TR-A-00-01-93' and PO_SE_Line_ID=" + Dr["PO_SE_Line_ID"].ToString(); SqlCommand command1 = new SqlCommand(sql, Connection); SqlDataReader Dr1;
Hi, i dont know whats gone wrong! but all of a sudden it seems to be throwing an error, i have looked at my previous code and it matches exactly when it was working, here is the code below int i = 0;for (i = 1; i <= 3; i++) {
//This gets the stock ID from the textbox.string stock_ID = ((TextBox)Panel1.FindControl("txtID" + i.ToString())).Text;
//This is the sql statement.string sql = "SELECT [n_or_sh], [title], [cost_price], [selling_price] FROM tbl_stock WHERE stock_ID = " + stock_ID;
//This creates a sql command which executes the sql statement.SqlCommand sqlCmd = new SqlCommand(sql, myConn); myConn.Open(); //This is a reader for the results to go in.SqlDataReader dr = sqlCmd.ExecuteReader(); //This reads the first result from the sqlReader dr.Read(); //This sets the title label text to the value of the description column.TextBox currentBox1 = (TextBox)Panel1.FindControl("txtDesc" + i); string strtxtDesc = currentBox1.Text;strtxtDesc = dr["title"].ToString();
}; myConn.Close(); i = 0;
the error its throwing is this CS1519: Invalid token '(' in class, struct, or interface member declaration for the line myConn.Open() does anybody have any idea how to solve this? Jez
Hi Guys,I have a quick question about DataReader, I have a function called "ExportTotal" i am calling this function from another function. what it does is it will put the Total for each Network in the Line 34. Right now what it is doing is It is putting the First Networktotal,2ndNetwork total,3rdNetwork total....... in the line 34. what i want is if the control comes to first network then it has to put only 1st network total and for the 2nd network only 2nd network total and so on. Please see my function below. Can you guys tell me what i am doing wrong?ThxPrivate Function exporttotal() As String Dim sql As String Dim dbFunctions As New DatabaseUtilities Dim tempinvoicetotal As String 'Dim rateactuals As String Dim filetext As String Dim tempclientname As String Dim strconn As String Dim prev_network As String = "" Dim current_network As String = "" strconn = CONNECTIONSTRING sql = "SELECT CAST(SUM(tblSpot.rateActual) AS int(4)) AS Rateactuals, SUM(tblSpot.rateActual * 0.85) AS netrate, SUM(tblSpot.rateActual * 0.15) AS commrate,TBLCLIENT.CLIENTNAME " & _"FROM tblSpot INNER JOIN tblContract ON tblSpot.fkContract = tblContract.pkid INNER JOIN " & _ " tblClient ON tblContract.fkClient = tblClient.pkid WHERE tblSpot.fkContractType = 'UNWIRED' AND " & _ "fkInvoiceNumber = '" & Me.txtinvoicenumber.Text & "' GROUP BY TBLCLIENT.CLIENTNAME" Dim myConn As New SqlConnection(CONNECTIONSTRING) Dim myCommand As New SqlCommand(sql, myConn) myConn.Open() Dim dbreader As SqlDataReader = myCommand.ExecuteReader() While dbreader.Read() Try Dim Rateactuals As String If dbreader("Rateactuals") Is DBNull.Value Then Rateactuals = "" Else Rateactuals = dbreader("Rateactuals") tempinvoicetotal = Rateactuals End If Dim clientname As String If dbreader("clientname") Is DBNull.Value Then clientname = "" Else clientname = dbreader("clientname") tempclientname = clientname End If If prev_network = "" Then filetext = filetext & vbCr & "34;;" & tempinvoicetotal & "00" & ";"Session("EDIExport4") = filetext prev_network = tempclientname Else current_network = tempclientname If prev_network <> current_network Then filetext = filetext & vbCr & "34;;" & tempinvoicetotal & "00" & ";"Session("EDIExport4") = filetext prev_network = tempclientname Else End If End If Catch SqlEx As SqlClient.SqlException Session("Error") = SqlEx.Message.ToString Response.Redirect("Error.aspx?Form=" & Request.Path) Catch Ex As System.Exception Session("Error") = Ex.Message.ToString Response.Redirect("Error.aspx?Form=" & Request.Path) End Try dbFunctions = Nothing End While myConn.Close() End Function
WMI Data Reader Task: WmiConnection = WMI Connection Manager WqlQuerySourceType = Direct input WqlQuerySource = "SELECT Name FROM CIM_Datafile WHERE Name = 'C: est.txt'" OutputType = Property value OverwriteDestination = Overwrite destination DestinationType = Variable Destination = User::WmiVariable
I've tried setting the WmiVariable to both String and Object data types. I've tested the WMI connection (both within SSIS and through a sample VBS script), and that works just fine.
Hi , I have a situation where i need to read data from one table and and the result that i get i pass it thru a where clause in another query for which i need to read the database again. Problem iam phasing is with the Sqldatareader in a while loop . When iam trying to open a SqlDataReader with in a SqlDataReader iam getting an error that i need to close the data reader that i have already opened. Is there a way to solve this problem Code: public void fnIduction() { //Calculating the Deduction of Individual Employees //Select from the EmployeeMast tableSqlCommand cmdEmp2 = new SqlCommand("select EmpCode,EmpName,DesigCode,DeptCode,BankCode,PFNo,PanNo,BankAcNo,GS,ESINo,PayrollGroup,LICNo,CategCode from EmployeeMast", cn); cn.Open();SqlDataReader dr02; dr02 = cmdEmp2.ExecuteReader();while (dr02.Read()) {strEmpCode = dr02["EmpCode"].ToString(); //Select from MondedPay,Deduction tables (Variable Deductions or Monthly Deductions)SqlCommand cmdDed02 = new SqlCommand("Select MondedPay.EmpCode,MondedPay.DedPayCode,Deduction.Name,MondedPay.DedPayMonth,MondedPay.DedPayYear,MondedPay.DedPayAmount from MondedPay,Deduction where Deduction.Code=MondedPay.DedPayCode and EmpCode='" + strEmpCode + "' and MondedPay.mType =1 ", cn);SqlDataReader drDed02; drDed02 = cmdDed02.ExecuteReader();while (drDed02.Read()) { strMonDedNam = drDed02["Name"].ToString();dobMonDedAmt = Convert.ToDouble(drDed02["DedPayAmount"].ToString());if (intDedPer == 1) { dobMonDedAmt = (dobDedAmt * dobBasic) / 100; dobMonDedTot = dobMonDedTot + dobMonDedAmt; } else { dobMonDedTot = dobMonDedTot + dobMonDedAmt; } } drDed02.Close();
Can anyone provide an example or point me to an article that explains how to programmatically set the SQL command for a data reader. I am using a data flow task in a For Loop Container and want to change the SQL command for each loop based on the data from the previous.
maybe you can help me with a little SSIS task. I want to fetch some wmi infos from serveral servers with the wmi reader. to read something is no problem, but whats the best way to bring them to a table or the control flow? Read from the generated file cant be a way. I also tried the variable. How do I get them to data flow. I tried a script task but i cant read the resultset. What format has the variable? ado.net, ado? Whats the best way to get the results into a table?
I am using a Ado Connection Manager to connect to a M S Access source.
But when I use this connection Manager in Data Reader Source, I am Not able to Configure Data reader Source. It gives exception "Cannot Acquire Managed Connection From Run Time Connection Manager".
I recently started a short topic about staging an incremental load, and the answer was to use a variable. I've been fiddling around with it have run into a question about calling the variables.
I've gotten my "Execute SQL Task" to generate a single row result set and store it as a global / package Variable. Now I'm not sure how to call / use this variable when I'm loading data from an ADO.NET Data Reader Source.
Do I need to map the variable to a parameter first? Can I call the variable in the SELECT statement like this?
Code Snippet SELECT * FROM source WHERE date > @variable
I was reading an MSDN page on Using Variables in Packages where they recommended the following syntax:
Code SnippetSELECT * from Production.Product WHERE ProductID = ?
But I'm not sure how to get the variable or parameter mapped to my Data Reader Source.
Any help would be greatly appreciated. Thanks in advance,
I am query event log information for a single day using WMI Data reader task and it is taking for ever. I am querying event log for remote servers but the server exectuing the process and queried servers both are in the same domain.
I've created some store procedure for use with Insert. This works great. Now I'd like to be able to use Select statement so I can get the data in some textboxes.
Right now I use Select with SqlCommand and DataReader. This works fine, but I'd like to learn with Stored Procedure with this.
What do I put in the stored procedure to return found records and how what code should I use after executenonquery?
How do I get columns to output when I have a data reader source? My connection is an ODBC and does complex sql. I am connection to a Netezza database and I would like to execute a very complex query, but in essence does
Create newtable as
(select day, sessionId)
from source
// lots of other joins and unions
select day, sessionId from newtable
drop newtable
I have an ODBC connection and I have a Datareader source, I cannot connect this source to my SQL Server destination because no output columns are available. What am I missing here?
Are there any good examples of this, taking data from a ODBC source into SQL server?
Hi I'm currently trying to create a few DTS packages that Import some very wide tables, Im using an ODBC data source into a Data Reader. I want to re-direct any errors into an error destination. The problem I've got is that I can go in and set each column to redirect but was hoping I could select or specify that this needs to happen for all the rows? Does anyone know if this is possible and if so how I go about it?
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)
Hi guys n gals ! I am having a few problems manipulating the results of my data reader,To gather the data I need my code is: // database connection SqlConnection dbcon = new SqlConnection(ConfigurationManager.AppSettings["dbcon"]); // sql statement to select latest news item and get the posters name SqlCommand rs = new SqlCommand("select * from tblnews as news left join tblmembers as members ON news.news_posted_by = members.member_idno order by news.news_idno desc", dbcon); // open connection dbcon.Open(); // execute SqlDataReader dr = rs.ExecuteReader(); // send the data to the repeater repeater_LatestNews.DataSource = dr; repeater_LatestNews.DataBind(); Then I am using: <%#DataBinder.Eval(Container.DataItem, "news_comments")%> in my repeater.What I need to do is pass the "news_comments" item to a function I created which will then write the result. The code for my function is: // prevent html public string StripHtml(string data) { // grab the data string theData = data; // replace < with &alt; theData = Regex.Replace(theData, "<", "<"); // return result return theData; } But I am having problms in doing this,Can anyone point me in the right direction on what I should be doing ???
In several weeks of effort and research I cannot locate any method to get this to work and need some help. We've loaded the most recent version of the Microsoft OLE DB (Feb 2007) and have located several discussion threads on these and other forums that all point to the same conclusion... you cannot use this to talk to a UDB/DB2 instance on Sun Solaris unless you are pulling a full table. Any custom SQL will not work. So, I'm trying to setup a custom query which in most cases is to determine a date to use in the filter (where clause) and the rest of the query is static. The DataReader source does not support parameters and also appears to not support SQL command from variable. Any thoughts on how to get this to work without having to read the full table and pass the data to subsequent filters within the package???
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?
We are writing SSIS packages to pull data from MSO€™s through a stored procedure and drop the output into the pipeline to store it on our local database.
Here is an example of a very simple data flow task that executes the following query in step 1
This command is stored in a variable called SQLOrgCommand. When I open the task and click on Columns I do not see any columns returned This allows me to conclude that the metadata needed from the GetCustomerData is not understood by SSIS because the SP uses dynamic sql.
While profiling we realized that it executed the following SQL:
SET FMTONLY ON
to get the metadata. However when we ran the sp with FMTONLY ON we recieved the following errors
Msg 536, Level 16, State 5, Procedure sp_GetData, Line 100 Invalid length parameter passed to the SUBSTRING function. Msg 536, Level 16, State 2, Procedure sp_GetData, Line 101 Invalid length parameter passed to the RIGHT function. Msg 536, Level 16, State 5, Procedure sp_GetData, Line 116 Invalid length parameter passed to the SUBSTRING function. Msg 536, Level 16, State 2, Procedure sp_GetData, Line 117 Invalid length parameter passed to the RIGHT function. Msg 536, Level 16, State 5, Procedure sp_GetData, Line 187 Invalid length parameter passed to the SUBSTRING function.
The same stored procedure executed without FMTONLY OFF generates the proper output.
Is this a known bug in SQL Server? If so is there a patch that we can install.
I'm using a Hemisphere West MSR-152 card reader. I need to take data from the reader and store it into SQL Server 2005 Express. Can anyone give ideas on how to do this (sample code recommendations). I'm using visual studio 2005 express and I code with VB.NET.
When i do a select on my emplee table for rows with null idCompany i dont get any records
I then try to modify the table to not allow a null idCompany and i get this error message:
'Employee (aMgmt)' table - Unable to modify table. Cannot insert the value NULL into column 'idCompany', table 'D2.aMgmt.Tmp_Employee'; column does not allow nulls. INSERT fails. The statement has been terminated.
Hi, in SSIS I read data from a DB2 database on AS400 using the Client Access ODBC Driver for DB2 from IBM and write it to a SQL Server database. Since it does not work using the odbc driver as data source directly, I use a data reader component with .net providersodbc. Some special characters were not translated correctly when read from DB2. They show up as ? in the SQL Server target table.
I tried to change the client locale in the ODBC connection properties but it did not help me. I tried changing all other settings in odbc but it still does not work.
In dts I could source all the data without this problems and good speed using the same nodbc driver.
The OLEDB Providers delivered with SSIS do not work in SSIS or I am too stupid to configure them correctly. They are even too slow as I explained above.
I cannot use the MS OLEDB Provider for DB2, because it works only in Enterprize Edition and we only have the Standard Edition.
Thus, only using Client Access ODBC Driver for DB2 with net providersodbc (as bridge) is performant enough and works on Itanium. But how to work around the problem with the special characters?
