Hi,
I want to pass data to the stored procedure by vb code.One of the input parameters of the SP has Text Data Type.whats the equivalent of this datatype in vb?
Thanks in Advance.
You can use byte array (byte[]) for BLOB data type (text/ntext/image) in SQL Server:)
I am exporting my sql table to the access database. My text fileds in SQL are stored as varchar(50) in SQL server 2005. but when I export the table to Access, These fileds get converted to 'memo' fields. I tried converting these memo fields in access to text field but Access does do that, throws an error message saying not enough disk space.
When I try to convert these 'varchar' fields in sql to text fileds, sql throws a 'time out ' message nad does not convert it to text.
What do I do?
Thanks
Text data type in Sql Server is used to store large texts that go beyond 8KB in size. Usually, sql server keeps a 16 byte pointer to the text data in the page. It can contain upto 2 GB size data. In Sql Server 2005, usage of Text data type is not recommended and varchar(max) is recommended.
http://articles.techrepublic.com.com/5100-6345-5033381.html
https://www.microsoft.com/technet/prodtechnol/sql/2000/reskit/part2/c0561.mspx?mfr=true
helloo
Can I use "like" in datatable.select method?
meaning:
Dim exp As String = "c.cDesc like " & txtSearch.Text & " + N'%' "
Dim rows() As DataRow = dtCenters.Select(exp)
knowing that txtSearch.text has unicode characters
Yes. However, your expression does not include quotes so you are probably not sending what you think you are sending.
Dim exp As String = "c.cDesc like '" & txtSearch.Text & "' + N'%' "
The better option is to use a parameter and set the value of the parameter to your textbox.
sql
Even thought I'm writing numerics, every cell gets this error with a green tag:
Convert numbers stored as text to numbers
Excel Cells were all pre-formated to accounting 2 decimal, and if i manually type the exact data Im sending it formats just fine.
I'm hearing this a common problem -
On another project I was able to find a workaround for the web based version of excel, by writing this to the top of the file:
<style>.text { mso-number-format:\@.; } </style>
is there anything I can pre-set in excel (cells are already formated) or write to my file so that numerics are seen as numerics and not text.
Maybe some setting in my write drivers - using sql servers excel destination.
So close.. Thanks for any help or information.
You received some sugestion on your other thread: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1392693&SiteID=1
Opening new threads for same problem makes more dificult to help you.
Is it possible to have it in a way so that if a certain record (line in the text file) fails to load due to watever reason, it gets written to another table, but the rest of the records still get loaded?
I try to do so and end up with the whole data flow task failing and it stalls at the record that had the error and doesn't seem to continue forward.
I just used the red arrow (on failure) and put that to another SQL destination object. But yeah that didnt work.
If someone has a better way of doing so, would be awesome if you can share that.
Cheers
You have to set the 'Access mode' property of 'OleDB DEstination' OpenRowset.It should not be fastload.Then set ErrorOuput as Redirect Row. Now you can get your erroneous rows redirected.
This should do.
|||yup...i didnt know about the accessmode property. that did it. thanks I face the same problem but I didn't find the 'Access mode' property of 'OleDB DEstination' that contain OpenRowset its only contain Tabel and View with Fastload. Where I can find that property ?
And what if the error occured in Flat File Source ? can we still using the same solution ? Thanks in advance.
Best Regards,
Hery|||Dear all,
Please help me on this.
Thanks in advance.
Is it possible to have it in a way so that if a certain record (line in the text file) fails to load due to watever reason, it gets written to another table, but the rest of the records still get loaded?
I try to do so and end up with the whole data flow task failing and it stalls at the record that had the error and doesn't seem to continue forward.
I just used the red arrow (on failure) and put that to another SQL destination object. But yeah that didnt work.
If someone has a better way of doing so, would be awesome if you can share that.
Cheers
You have to set the 'Access mode' property of 'OleDB DEstination' OpenRowset.It should not be fastload.Then set ErrorOuput as Redirect Row. Now you can get your erroneous rows redirected.
This should do.
|||yup...i didnt know about the accessmode property. that did it. thanks I face the same problem but I didn't find the 'Access mode' property of 'OleDB DEstination' that contain OpenRowset its only contain Tabel and View with Fastload. Where I can find that property ?
And what if the error occured in Flat File Source ? can we still using the same solution ? Thanks in advance.
Best Regards,
Hery|||Dear all,
Please help me on this.
Thanks in advance.
Is it possible to have it in a way so that if a certain record (line in the text file) fails to load due to watever reason, it gets written to another table, but the rest of the records still get loaded?
I try to do so and end up with the whole data flow task failing and it stalls at the record that had the error and doesn't seem to continue forward.
I just used the red arrow (on failure) and put that to another SQL destination object. But yeah that didnt work.
If someone has a better way of doing so, would be awesome if you can share that.
Cheers
You have to set the 'Access mode' property of 'OleDB DEstination' OpenRowset.It should not be fastload.Then set ErrorOuput as Redirect Row. Now you can get your erroneous rows redirected.
This should do.
|||yup...i didnt know about the accessmode property. that did it. thanks I face the same problem but I didn't find the 'Access mode' property of 'OleDB DEstination' that contain OpenRowset its only contain Tabel and View with Fastload. Where I can find that property ?
And what if the error occured in Flat File Source ? can we still using the same solution ? Thanks in advance.
Best Regards,
Hery|||Dear all,
Please help me on this.
Thanks in advance.
I am transfering data from a textfile to sql server.I use a data flow task for trasfering my text files.
Here is what i do.
1.Add text file source
What i want to achieve here is if the text file countains the column name in the first row i should delete them and if it does not contain column name in the first row just transfer it.
how can this be achieved?
2.add one more column to my text file which should contain the status(insert or update).
how can this be done?
3.before transfering data ot destination i want to know if the record exists if exists i just want to update it instead of insert.and if new record i want to insert it .and the status in the above new column need to change.
please help...
Not sure without looking at your data, but look into using the conditional split transformation to perform your logic checks. Use a derived column transformation to add a column.|||sureshv wrote:
1.Add text file source
What i want to achieve here is if the text file countains the column name in the first row i should delete them and if it does not contain column name in the first row just transfer it.
how can this be achieved?
There is a property on the flat file connection manager that you can configure for that (ColumnsNamesInFirstDatarow).
2.add one more column to my text file which should contain the status(insert or update).
how can this be done?
Use a derived column to add extra columns to data flow pipeline
3.before transfering data ot destination i want to know if the record exists if exists i just want to update it instead of insert.and if new record i want to insert it .and the status in the above new column need to change.
There are tons of threads discussing that issue; the most popular is to use Lookup transform against the destination table; if a match occurs means the row already exists; otherwise it does not.
|||In the first case.
Data is just a text file with column names in the first row.
eg:
name address telno
xxx yyy zzz
xxx sdfdsf sdfsf
But i dont want the column names coming from text file. instead i want to have my own column names(which i can and know to create)
If i transter data as in the text file the column names would appear in the first row.just to get rid of it i want to remove column name from text file.
In second case:
I use derived column to add a column,and conditional split for bad data.
My point is in the new column how should i insert the status(insert) or )update.
what should i use.
|||There is a property on the flat file connection manager that you can configure for that (ColumnsNamesInFirstDatarow).
I dont want to use this i can have and want to have my own column names.WHich can be done and i know about it.
please look at my reply earlier
|||
My point is in the new column how should i insert the status(insert) or )update.
see if this helps:
http://blogs.conchango.com/jamiethomson/archive/2006/09/12/SSIS_3A00_-Checking-if-a-row-exists-and-if-it-does_2C00_-has-it-changed.aspx
you could add a derived column transform at after each lookup transform and add there a column with the expected value(insert/update)...
"Malarb" <Malarb@.discussions.microsoft.com> wrote in message|||Hi,
Hi all,
I've developed a data driven subscription report. I have a paramete (named "Data") that is a result of my query to the current date. It is working fine.
Now I would like to make one change: In the step4 of the creation of the data-driven report we have the option to give a name to the filename.The name that I gave was TestFile. In this option i'm also able to select the parameter instead of giving the name to the filename. Can I make something like TestFile_ & @.Data? Wich would result in TestFile_01-02-2007? Or the only way is to make, in the query of the paramenter another field with this result?
Thanks in advance.
Marco.
I tried the other option, making it by a query and this is what I want.
Anyway, instead of making this in my query, I couldn't have done this in the text? Like I said TestFile_ & @.Data? This would be interpreted as a string am I right?
I have a windows form containing some text boxes and radio buttons bount to an SQL Server database, with code like the following:
this.tbBorrowerLastName.DataBindings.Add (new Binding("Text", dsData.Tables["Results"],"BorrowerLastName"();
this.rbMale.DataBindings.Add(new Binding("Checked", dsData.Tables["Results"],"Male"))
If I add a new row and try to move there using the code below, nothing happens (neither movement to a new position nor error message), unless I remove the radio button bindings. (The same thing occurs if I attempt to move to any record that has a null value in the database field for a radio button).
myLastRow = dsData.Tables["Results"].NewRow();
dsData.Tables["Results"].Rows.Add(myLastRow);
I attempt to move by changing the value of the position property of the BindingContext object.
How can I retain the radio button bindings and still be able to add and move to a new row?
Any help would be appreciated.
I was able to figure my own solution.
The problem was a null value for the checked property of the radio buttons. I tried adding a default value to Visual Studio and to the database, but neither of those solved the problem.
Inbetween the two lines of code in my earlier post (beginning myLastRow and dsData.Tables respectively), I added:
myLastRow["Male"]=true;
myLastRow["Female"]=false;
Problem solved.