Database Discussion Boards

Brian Kavanagh3-Mar-09 5:57

3-Mar-09 5:57

I am trying to bulk insert a text file. The file is delimited by a pipe '|'. Everything works except that it does not recognize when there is a new line (record) and attempts to insert only 1 row.

I am using the default value for ROWTERMINATOR of '\r\n', but it does not work. Are there any other values that I can use to make this work?

Thansk...Brian

modified on Tuesday, March 3, 2009 2:29 PM

Re: ROWDELIMITER for bulk insert

Ashfield4-Mar-09 1:11

Ashfield

4-Mar-09 1:11

Brian Kavanagh wrote:
Are there any other values that I can use to make this work?

You use whatever the records are seperated by - usually\r\n, but not always, particularly if coming from a unix box. Have a look at your file in a hex editor (plenty of free ones on google) to see what values you really need.

Bob
Ashfield Consultants Ltd
Proud to be a 2009 Code Project MVP

Re: ROWDELIMITER for bulk insert

Brian Kavanagh4-Mar-09 6:39

Brian Kavanagh

4-Mar-09 6:39

Bob:

Thanks for the response. Unfortunately my thread is a little confusing since I indicate both ROWDELIMITER and ROWTERMINATOR.

I am talking about the row terminator. I have tried \r\n and other combinations to no avail.

The files that will be imported are simply ANSII file types created in a MS Windows environment.

Whenever it gets the the end of the line it continues to insert data into the same row and does not create a new record like it should.

Any guidance will be appreciated!

Thanks...Brian

Re: ROWDELIMITER for bulk insert

Ashfield4-Mar-09 8:42

Ashfield

4-Mar-09 8:42

My answer still stands, the chances are the lines are not terminated by crlf as expected, and although they are created in a windows environment there is no guarantee they are terminated by crlf (\r\n). My first check would be to see the real values in hex.

Give it a try and let us know.

Bob
Ashfield Consultants Ltd
Proud to be a 2009 Code Project MVP

Re: ROWDELIMITER for bulk insert

Brian Kavanagh4-Mar-09 10:09

Brian Kavanagh

4-Mar-09 10:09

Bob:

I looked at the file using a hex editor (as suggested) and at the end of each line there are 2 hex values "0d" and "0a". When I look up the values there are a carriage return and a line feed, repectively.

There are no other values except for the actual data before or after these values.

So...what next?

Thanks...Brian

Re: ROWDELIMITER for bulk insert

Ashfield4-Mar-09 21:00

Ashfield

4-Mar-09 21:00

Well, at least it has confirmed the line terminator. Are you using a format file? Do the number of fields match the expected number?

Bob
Ashfield Consultants Ltd
Proud to be a 2009 Code Project MVP

Re: ROWDELIMITER for bulk insert

Brian Kavanagh6-Mar-09 2:57

Brian Kavanagh

6-Mar-09 2:57

Eureka!

I took a step back and asked what was missing? I updated to SQL Server 2005 with Service Pack 2 and that solved the issue.

Once installed, I removed the line that set the ROWTERMINATOR and allowed it to use the default value it worked.

Thanks for all your help!

Brian

Re: ROWDELIMITER for bulk insert

Ashfield6-Mar-09 4:53

Ashfield

6-Mar-09 4:53

Brian Kavanagh wrote:
Thanks for all your help!

No problem, pleased you found the solution.

Bob
Ashfield Consultants Ltd
Proud to be a 2009 Code Project MVP

what are the correct Microsoft SQL server versions for Windows server 2003 x64 edition?

pubududilena3-Mar-09 3:32

pubududilena

3-Mar-09 3:32

HI,

what are the correct SQL SERVER versions for Windows server 2003 x64 edition operating system?
becasue we have tried to install SQL SERVER 2000 enterprise edition on there,but it was failed.

Thanks in advance.

Re: what are the correct Microsoft SQL server versions for Windows server 2003 x64 edition?

Vimalsoft(Pty) Ltd3-Mar-09 20:49

Vimalsoft(Pty) Ltd

3-Mar-09 20:49

Why dont you install SQL 2005 or 2008 they will work

Vuyiswa Maseko,

Few companies that installed computers to reduce the employment of clerks have realized their expectations.... They now need more and more expensive clerks even though they call them "Developers" or "Programmers."

C#/VB.NET/ASP.NET/SQL7/2000/2005/2008
http://www.vuyiswamaseko.tiyaneProperties.co.za
vuyiswa@its.co.za
www.ITS.co.za

Revoking Oracle Roles and Privileges

Muammar©2-Mar-09 23:21

Muammar©

2-Mar-09 23:21

Good morning professional DBAs,

I have a problem revoking privileges from an oracle user and I dont understand where is he getting his privileges from.. Here's what I'm doing:

SQL> SELECT PRIVILEGE FROM DBA_SYS_PRIVS WHERE GRANTEE = 'MyUser';

PRIVILEGE
----------------------------------------
CREATE SESSION

SQL> SELECT GRANTED_ROLE FROM DBA_ROLE_PRIVS WHERE GRANTEE = 'MyUser';

no rows selected

SQL> SELECT PRIVILEGE FROM DBA_TAB_PRIVS WHERE GRANTEE = 'MyUser';

no rows selected

Yet, when connecting to MyUser/MyPassword, I still can insert, update and delete any table without granting those privileges first!!

Please help guys.

All generalizations are wrong, including this one!
(\ /)
(O.o)
(><)

Re: Revoking Oracle Roles and Privileges

scottgp3-Mar-09 0:27

scottgp

3-Mar-09 0:27

Shouldn't you be selecting from DBA_TAB_PRIVS WHERE GRANTEE = 'MYUSER'?
Also, shouldn't you use uppercase 'MYUSER' in the other two select statements as well?
Scott

Connecting to a SQL database programmatically

Richard Blythe2-Mar-09 17:25

Richard Blythe

2-Mar-09 17:25

I've programmed against a Access database for awhile and I think it's time I start using SQL server. My initial problem is a bit embarrasing but I can't seem to figure out how to connect to a SQL database programatically. I've googled the issue and couldn't find the answer I needed.
I also would like the answer to include how to connect to a password protected db.

Thanks,
Richard

There cannot be a crisis today; my schedule is already full.

Re: Connecting to a SQL database programmatically

Rupesh Kumar Swami2-Mar-09 19:10

Rupesh Kumar Swami

2-Mar-09 19:10

try Connection String for differetnt type of databases[^]

Rupesh Kumar Swami
Software Developer,
Integrated Solution,
Bikaner (India)

My Company
Award: Best VB.NET article of June 2008: Create Column Charts Using OWC11

Re: Connecting to a SQL database programmatically

Eddy Vluggen3-Mar-09 4:38

Eddy Vluggen

3-Mar-09 4:38

You forgot to mention the programming language that you want to use, and the query language of your preference Smile | :)

There's a nice example in C# here[^], but you could also scout the MSDN video's about VB.NET[^]. There are more video's on the MSDN-site, also about the combination of SQL Server and C#.

Happy learning Smile | :)

I are troll Smile | :)

Logging both text and numbers

User 127822-Mar-09 17:01

User 12782

2-Mar-09 17:01

I have a sensor network where the various sensors can either return a string or numeric measurement, I want to store these values along with a timestamp. I see two possible ways to handle this situation:

1. I have a general sensor data table with value column being an nvarchar of reasonable length. I can convert the numerics to strings and store them in the table as text. Pros: simplier design, Cons: wasted memory, loss of mathematical operations (min, max, etc) on the dataset.

2. Have two tables: one for text measurements, one for numeric measurements. Pros: retain ability for math on records, no wasted memory, Cons: extra step to determine table to log/retrieve from, more difficult to do aggregate queries.

Is there a third option? Any opinions on how to design this?

Thanks

modified 12-Jul-20 21:01pm.

Re: Logging both text and numbers

Eddy Vluggen3-Mar-09 4:41

Eddy Vluggen

3-Mar-09 4:41

How many measurements do you expect per minute?

Harddisks and memory are cheap enough to stop worrying about wasting a byte or two. The second option sounds right to me, perhaps from it's own thread.

I are troll Smile | :)

VC++ 6 and excel cant read anything other than text formatted cell

RDEPTYLTD2-Mar-09 12:03

RDEPTYLTD

2-Mar-09 12:03

Visual C++ and Microsoft Excel 97 ~ 2003

I am working on a project at the moment and have an issue with reading MS Excel 97 ~ 2003 spreadsheets.

My problem relates to the Excel field format of any given field in the spreadsheet, if a field is formatted in Excel to anything other than Text, then I cannot read the data from that field. for example if column A1 is formatted in excel to Text (good data returned) if A2 is formated to a number (a null is returned) if A3 is text (good data is returned), and so on.

The problem seems to be getting my code to understand the format of any particular field, Here is a snipped of my code;

1) I am using the CRecordset class to open and read the Excel spreadsheet. The spreadsheet has
defined name ranges and generally all works just fine (Provided the fields are text formatted).

2)If i use this line of code to first attempt to read what type of data is in a NUMBER field
"recset.GetODBCFieldInfo( m_csaColumnNames.GetAt(i), fieldinfo );"
I get a positive response when i try the different data types
"if(fieldinfo.m_nSQLType == SQL_VARCHAR){AfxMessageBox("SQL_VARCHAR");}"
suggesting its a data type SQL_VARCHAR data type

3) If i then call GetFieldValue to get the info from a field with a switch command

CDBVariant variant;

recset.GetFieldValue( m_csaColumnNames.GetAt(i), variant);

switch(variant.m_dwType)
{
case DBVT_SHORT:{ szValue.Format("%d", variant.m_iVal);
break;
}
case DBVT_LONG:{ szValue.Format("%d", variant.m_lVal);
break;
}
case DBVT_SINGLE:{ if ( variant.m_fltVal == 0.0 )
szValue = "Verify";
else
szValue.Format("%.1f", variant.m_fltVal);
break;
}

case DBVT_DOUBLE:{ if ( variant.m_dblVal == 0.0 )
szValue = "Verify";
else
szValue.Format("%.1f", variant.m_dblVal);
break;
}

case DBVT_DATE:{
szValue.Format("%B %d, %Y",variant.m_pdate );
break;
}

case DBVT_STRING:{ szValue = *variant.m_pstring; //szValue = V_BSTRT( &varValue );//convert BSTR to CString
break;
}

case DBVT_BOOL:{ if(variant.m_boolVal)
szValue = "TRUE";
else
szValue = "FALSE";
break;
}

case DBVT_UCHAR:{ szValue = (char*)variant.m_chVal;
break;
}

case DBVT_NULL:{
szValue = "Error Null"; AfxMessageBox(szValue);
break;
}
default:{
szValue = "\0";
break;
}
}//switch

RESULTS
All fields which are text, return a valid value which is exactly as per the Excel Spreadsheet, all fields which contains numbers or I even tried date formats in excel return DBVT_NULL which is totally useless because the cell has a number in it or a date.

SUMMARY
I have tried to read the data using GetODBCFieldInfo only and then calling the m_ member depending on the type returned which i know to be in the field, but that does not give me anything other than an unsigned short with random values that bear no resemblance to what is in the actual field.

Any help that anyone can offer in regards to solving this problem would be much appreciated and I I can get the entire class working properly I have happy to post it here for all to use.

Thanks in advance for any assistance.

Getting TableName in DataSet/DataTable from SP

Maxim Langman2-Mar-09 11:21

Maxim Langman

2-Mar-09 11:21

Hello,
I have a little Stored Procedure (SQL Server 2005), which returns some tables. Now, I'm getting those tables in the DataSet in my code. The problem is that I'm not getting TableName of none of the tables. All I get is a default naming convention "Table", Table1", Table2" ....
What is the way to get the TableName from SP? How can I basically know which table represents which select in the SP?
Thanks,
Maxim

Re: Getting TableName in DataSet/DataTable from SP

ya3mro10-Nov-09 20:26

ya3mro

10-Nov-09 20:26

i think you can view code of the dataset and search for tag name <tables> and you find inside it all tables data from database

Re: Getting TableName in DataSet/DataTable from SP

sharad487-Dec-09 20:46

sharad48

7-Dec-09 20:46

CREATE PROCEDURE MyProc AS
BEGIN

Select 'FirstTableName' AS TableName, Column1, Column2
FROM FirstTable

Select 'SecondTableName' AS TableName, Column1, Column2
FROM SecondTable

Select 'ThirdTableName' AS TableName, *
FROM ThirdTable

END

In the returning DataSet first column for all DataTables will have the TableName you have specified in the SP. You can identify the specific table using these names and assign an appropriate name to your DataTables.

Re: Getting TableName in DataSet/DataTable from SP

Maxim Langman9-Dec-09 19:34

Maxim Langman

9-Dec-09 19:34

Good idea, but unfortunatelly doesn't work. It seems like the name of the table (select) never comes from Stored Procedure to the DataSet.Table. This is very sad, because any time the stored procedure is changed, the code has to be recompiled. So what I did, is an additional SELECT with the names of the tables in the order it goes in the SP. This way I know how many tables I get and the names and the order Smile | :)

A Query

mehrdadc481-Mar-09 23:44

mehrdadc48

1-Mar-09 23:44

Hi,
I have two tables, I need records of first table that is not in the result of innerJion of these two tables.
How could I obtain that?

Best wishes

Re: A Query

Rupesh Kumar Swami1-Mar-09 23:56

Rupesh Kumar Swami

1-Mar-09 23:56

are you try left join?
or something like that

select * from table1 where id not in (select table1.id from table1 inner join table2 on table1.id=table2.id)

Rupesh Kumar Swami
Software Developer,
Integrated Solution,
Bikaner (India)

My Company
Award: Best VB.NET article of June 2008: Create Column Charts Using OWC11

Re: A Query

mehrdadc482-Mar-09 0:03

mehrdadc48

2-Mar-09 0:03

Unfortunately I dont have a unique field in table1 niether table2.(as id)
I know it's terrible, but it's not my fault.

Best wishes

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.