Data export to individual files

Hello,
I am looking to T-SQL extract records from a table and have each record
stored in individual .eml files. A query based DTS doesn't let me
specify what extension my files will have AND I'm unsure how to have
each record written to a separate file? Any ideas on how I can pull
this off?
Much appreciated,
Prpryan75 wrote:
> Hello,
> I am looking to T-SQL extract records from a table and have each record
> stored in individual .eml files. A query based DTS doesn't let me
> specify what extension my files will have AND I'm unsure how to have
> each record written to a separate file? Any ideas on how I can pull
> this off?
> Much appreciated,
> Pr
>
A few possibilities:
1. Use OSQL to run the query and pipe the output to the desired file
2. Use xp_cmdshell to issue DOS "ECHO" commands to write the desired file
3. Use a combination of xp_cmdshell and OPENROWSET to create and then
write the desired file
Tracy McKibben
MCDBA
http://www.realsqlguy.com

Data export to individual files

Hello,
I am looking to T-SQL extract records from a table and have each record
stored in individual .eml files. A query based DTS doesn't let me
specify what extension my files will have AND I'm unsure how to have
each record written to a separate file? Any ideas on how I can pull
this off?
Much appreciated,
Prpryan75 wrote:
> Hello,
> I am looking to T-SQL extract records from a table and have each record
> stored in individual .eml files. A query based DTS doesn't let me
> specify what extension my files will have AND I'm unsure how to have
> each record written to a separate file? Any ideas on how I can pull
> this off?
> Much appreciated,
> Pr
>
A few possibilities:
1. Use OSQL to run the query and pipe the output to the desired file
2. Use xp_cmdshell to issue DOS "ECHO" commands to write the desired file
3. Use a combination of xp_cmdshell and OPENROWSET to create and then
write the desired file
Tracy McKibben
MCDBA
http://www.realsqlguy.com

Data entry in a datetime field

Hi,

I am using SQL Server Management Studio Express for creating my database. I also use the GUI tools for data entry instead of using T-SQL. Whenever I try to enter a value in a field that has a smalldatetime data type, it gives me an error message -

"Invalid Value for cell (row 1, column 2).

The changed value in this cell was not recognised as valid.

.Net Framework Datatype: Datetime

Error Message: Index was outside the bounds of the array.

Type a value appropriate for this data type or press ESC to cancel the change."

Now I have tried entering all different combinations in which one can enter a date or a time or both, including in the format I have specified in the windows regional settings, but I always get the same error message.

How do I input data into the field?

Hi,

you are using a format SQL Serve ri snot expecting. Which User language did you configured for the accessing user and which date are you trying to insert ?
HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

Hi,

I use only English as the default language for all programs, OS included. The date in question is not just one particular date, it is any date or time value, and I've been facing this issue ever since I started using SQL Server Express a year ago. Only I haven't used it much since, and need to seriously develop something now, that I bothered to ask the question here.

Some examples of the values I tried entering yesterday, and none of them worked. (I got the same error message for each value) -

1-1-2007

01-01-2007

01/01/2007

01,01,2007

01:01:2007

Jan 01, 2007 (this is the format I have specified for short date in the Win regional settings - MMM dd, yyyy)

I even tried entering time alongside with all those above figures in the format -

11:35 PM

11:35 AM

23:35

The only time value(s) I didn't try was/were - hh:mm:ss tt - which is the time format specified in my Win regional settings, neither standalone, nor with the date values.

I also tried entering a time value standalone, without the date. Still no go.

(Also, as I have gleaned from previous posts, if all goes well and SQL Server accepts your data, then if you enter just a date value in a datetime field, then the field automatically gets populated with the value of the system time at that moment, and if you enter just a standalone time value, then the date part will be filled by the system date value on the date of the entry. Am I correct so far?)

Now I'm in a real fix because of the inability to enter date/time values.

Could you please help me out?

|||Guys, I need your help. Please give me a solution to my problem. (This post has already been relegated to the second page without any replies).|||People, I need some help here. Any replies addressing my issue will be greatly appreciated. Thank You in advance.|||

I don't understand. I can enter the datetime values in all the above mentioned combinations if I use an Insert or Update statement using T-SQL, but I can't seem to enter data in the datetime/smalldatetime fields at all if I try to use the GUI of Management Studio Express to enter data. (right click the table, select show table, and the grid view pops up). Ditto for using GUI tools of Visual Studio Express. Is this a bug in the Visual Studio (Express) software?

Also, my other concerns are, once I develop a front-end for data entry using VB, will I face the same issues with data entry in the datetime fields using the GUI of the front end? I can't test that right now, because I'm still learning VB, and cannot create the front end just yet.

|||

Alright, I have done a complete reinstall of Win XP, and have also reinstalled SQL Server Express. Prior to this, I had SQLExpress SP1, but now I have directly installed SP2.

Now I continue to face the same issue. SQL Server will not accept any value for any datetime field, unless it is entered as an SQL insert statement. It won't accept the value entered in the exact same format as the insert statement from the GUI mode of Management Studio Express, nor will it take any values from any GUI developed using VBExpress and the fill dataset method. It throws an error everytime. I don't know any other way of databinding and updating/inserting using VB at this time.

I am at my wits' end because of this. I ask again, is this a bug in SQL Server Express? If so how do I report it to MS and get my issue resolved? If not, even then how can I solve this problem?

sql

Data entry in a datetime field

Hi,

I am using SQL Server Management Studio Express for creating my database. I also use the GUI tools for data entry instead of using T-SQL. Whenever I try to enter a value in a field that has a smalldatetime data type, it gives me an error message -

"Invalid Value for cell (row 1, column 2). The changed value in this cell was not recognised as valid. .Net Framework Datatype: Datetime Error Message: Index was outside the bounds of the array. Type a value appropriate for this data type or press ESC to cancel the change."

Now I have tried entering all different combinations in which one can enter a date or a time or both, including in the format I have specified in the windows regional settings, but I always get the same error message.

How do I input data into the field?

Hi,

you are using a format SQL Serve ri snot expecting. Which User language did you configured for the accessing user and which date are you trying to insert ?
HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

Hi,

I use only English as the default language for all programs, OS included. The date in question is not just one particular date, it is any date or time value, and I've been facing this issue ever since I started using SQL Server Express a year ago. Only I haven't used it much since, and need to seriously develop something now, that I bothered to ask the question here.

Some examples of the values I tried entering yesterday, and none of them worked. (I got the same error message for each value) -

1-1-2007

01-01-2007

01/01/2007

01,01,2007

01:01:2007

Jan 01, 2007 (this is the format I have specified for short date in the Win regional settings - MMM dd, yyyy)

I even tried entering time alongside with all those above figures in the format -

11:35 PM

11:35 AM

23:35

The only time value(s) I didn't try was/were - hh:mm:ss tt - which is the time format specified in my Win regional settings, neither standalone, nor with the date values.

I also tried entering a time value standalone, without the date. Still no go.

(Also, as I have gleaned from previous posts, if all goes well and SQL Server accepts your data, then if you enter just a date value in a datetime field, then the field automatically gets populated with the value of the system time at that moment, and if you enter just a standalone time value, then the date part will be filled by the system date value on the date of the entry. Am I correct so far?)

Now I'm in a real fix because of the inability to enter date/time values.

Could you please help me out?

|||Guys, I need your help. Please give me a solution to my problem. (This post has already been relegated to the second page without any replies).|||People, I need some help here. Any replies addressing my issue will be greatly appreciated. Thank You in advance.|||

I don't understand. I can enter the datetime values in all the above mentioned combinations if I use an Insert or Update statement using T-SQL, but I can't seem to enter data in the datetime/smalldatetime fields at all if I try to use the GUI of Management Studio Express to enter data. (right click the table, select show table, and the grid view pops up). Ditto for using GUI tools of Visual Studio Express. Is this a bug in the Visual Studio (Express) software?

Also, my other concerns are, once I develop a front-end for data entry using VB, will I face the same issues with data entry in the datetime fields using the GUI of the front end? I can't test that right now, because I'm still learning VB, and cannot create the front end just yet.

|||

Alright, I have done a complete reinstall of Win XP, and have also reinstalled SQL Server Express. Prior to this, I had SQLExpress SP1, but now I have directly installed SP2.

Now I continue to face the same issue. SQL Server will not accept any value for any datetime field, unless it is entered as an SQL insert statement. It won't accept the value entered in the exact same format as the insert statement from the GUI mode of Management Studio Express, nor will it take any values from any GUI developed using VBExpress and the fill dataset method. It throws an error everytime. I don't know any other way of databinding and updating/inserting using VB at this time.

I am at my wits' end because of this. I ask again, is this a bug in SQL Server Express? If so how do I report it to MS and get my issue resolved? If not, even then how can I solve this problem?

cycling through results of a select statement

I am new to stored procedures and T-SQL so please stick with me. I have a table that holds information about companies. I am trying to write a stored procedure that when run will query that table and find out if there are more than one entry of that company. All company names in that table must be unique (they can only occur once), if they occur more than once I need to flag it for reporting.

So what is the best way to go about this? Essentially what i was thinking was doing a select * on the table and then going from the first entry to the last and at each entry running a select * from table where companyname = @.nameofcompany. @.nameofcompany would be the name for that entry. If the select statement revealed more than one entry then i would know there was a problem.

Like I said I am new and this is probably very simple but i need a little help getting started

thanksSELECT *
FROM myTable99 o
WHERE EXISTS ( SELECT Company_Name
FROM myTable99 i
WHERE o.Company_Name = i.Company_Name
GROUP BY Company_Name
HAVING COUNT(*) > 1)

But you wouldn't have to do that if you defined the table like

CREATE TABLE myTable99(Company_Name varchar(50) UNIQUE)

EDIT: Where in Jersey? And what school?|||I am originally from Vernon (Mountain Creek). Went to school at Stevens Institute of Technology in Hoboken, NJ.

I didn't create the dll for this database so i just loaded the schema by the .sql file. Right now I am handed an excel template and i wrote somce vb code to go through that excel file pull out the information i want and then write it to a text file delimited with "#" and then i load it into SQL server with a bulk load command. If the user sends me a template that already has duplicate entries in it and i try to load the data into SQL column that has a unique indentifier what will happen? Will it throw an error? If this is the case then it would probably be better to get the data in the database and then decided whether or not it is a duplicate.
Redefining the table seems like the simplest way to go but i don't want to break functionality in the process

thanks|||I just tried to add this code and it is complaining on the second line in reference to the o. Here is the error: Error 170: Line 2: incorrect syntax near 'o'. As I said i am new to stored procedures and T-SQL do i need to declare the o and i as variables somewhere?|||I didn't test the code, so you gave me a start...but the code does work...

Where are you running this from? Do you have query analyzer and the other sql server client toools?

USE Northwind
GO

SET NOCOUNT ON
CREATE TABLE myTable99 (Company_Name varchar(50))
GO

INSERT INTO myTable99(Company_Name)
SELECT 'Vernon Valley' UNION ALL
SELECT 'Mountain Creek' UNION ALL
SELECT 'Hidden Valley' UNION ALL
SELECT 'Campgaw' UNION ALL
SELECT 'Break Neck Road' UNION ALL
SELECT 'High Point' UNION ALL
SELECT 'Octogon Lounge' UNION ALL
SELECT 'Great Gorge' UNION ALL
SELECT 'Mountain Creek'
GO

SELECT *
FROM myTable99 o
WHERE EXISTS ( SELECT Company_Name
FROM myTable99 i
WHERE o.Company_Name = i.Company_Name
GROUP BY Company_Name
HAVING COUNT(*) > 1)
GO

SET NOCOUNT OFF
DROP TABLE myTable99
GO|||sorry i am an a** i have an extra space floating in there. Man i am an idiot|||Hey...you're from Jersey...never apologize