Creating a .CSV from a SQL Server Table

Forums

My Profile

Inbox

Address Book

My Subscription

My Forums

Creating a .CSV from a SQL Server Table

Logged in as: Guest

arrSession:exec spGetSession 2,2,51982

	Active Users: There are 0 members and 0 guests.
	Users viewing this topic: none

Printable Version

All Forums >> [Scripting] >> WSH & Client Side VBScript >> Creating a .CSV from a SQL Server Table
Do you like VisualBasicScript.com? Link to us and help spread the word about our forum. Thanks!

Page: [1]

Message

<< Older Topic Newer Topic >>

Creating a .CSV from a SQL Server Table - 9/19/2007 1:57:04 AM

kronik

Posts: 5
Score: 0
Joined: 9/19/2007
Status: offline

Hi guys.. I'm new here and.. struggling. ;)

Why doesn't this work? :P

I'm receiving a "ActiveX can't create object: 'ADODB.Field'" error.

I assume that the ADO library is operational as it managed to utilize the class for connection/rs? What am I missing?

Post #: 1

RE: Creating a .CSV from a SQL Server Table - 9/19/2007 4:44:17 AM

ehvbs

Posts: 2223
Score: 50
Joined: 6/22/2005
From: Germany
Status: offline

Hi kronik,

as the error message says, ADODB.Fields can't be created directly. You have to
ask an ADODB.Recordset object for its fields - as in

For Each oField In oRS.Fields

Recordsets can be created (you may want to get an Recordset not connected
to a connection, a.k.a. 'disconnected recordset'). But most of the time you can
ask the connection to give you a recordset - as in

Set oRS = oCN.Execute( sSQL )

So delete the redundant/redundant + wrong lines

Set rs = CreateObject("ADODB.Recordset")
Set fld = CreateObject("ADODB.Field")

from your script and see what happens (I didn't test the rest of your
script).

Good luck!

ehvbs

(in reply to kronik)

Post #: 2

RE: Creating a .CSV from a SQL Server Table - 9/19/2007 5:38:52 AM

kronik

Posts: 5
Score: 0
Joined: 9/19/2007
Status: offline

Now receiving : Item cannot be found in the collection corresponding to the requested name or ordinal,
offending line : s=s & "'" & rs.fields(i).value

(in reply to ehvbs)

Post #: 3

RE: Creating a .CSV from a SQL Server Table - 9/19/2007 5:49:30 AM

ebgreen

Posts: 5250
Score: 31
Joined: 7/12/2005
Status: offline

Pretty sure that the fields are in a 0 based array, so try replacing this:

for i=1 to rs.fields.count

With this:

for i=0 to rs.fields.count - 1

_____________________________

"... when you are good and crazy, oooh, oooh, oooh, the sky is the limit!" - The Tick
Goog places to start:http://www.visualbasicscript.com/m_24727/tm.htm
http://www.visualbasicscript.com/m_47117/tm.htm

(in reply to kronik)

Post #: 4

RE: Creating a .CSV from a SQL Server Table - 9/19/2007 5:59:54 AM

kronik

Posts: 5
Score: 0
Joined: 9/19/2007
Status: offline

Thanks ebgreen.

Now it runs, but it doesn't actually get the records, just the field names and it delimits those.. what'd I do wrong?

I also need to put a break between each, so would that be a Chr(13) & Chr(10)?

(in reply to ebgreen)

Post #: 5

RE: Creating a .CSV from a SQL Server Table - 9/19/2007 6:03:09 AM

ebgreen

Posts: 5250
Score: 31
Joined: 7/12/2005
Status: offline

You can use vbCrLf for the page break. Are you sure that your query returns records?

_____________________________

"... when you are good and crazy, oooh, oooh, oooh, the sky is the limit!" - The Tick
Goog places to start:http://www.visualbasicscript.com/m_24727/tm.htm
http://www.visualbasicscript.com/m_47117/tm.htm

(in reply to kronik)

Post #: 6

RE: Creating a .CSV from a SQL Server Table - 2/26/2008 12:44:55 AM

cfrangus

Posts: 1
Score: 0
Joined: 2/25/2008
Status: offline

Don't know if you figured this out by now but you're missing the write to the file for the records. That's why you don't see your data.

Insert this after 'move to next record

s=s & vbCrLf
objFile.Write s

you should see your data then.

(in reply to ebgreen)

Post #: 7

If you found our site useful please link to us <a href="http://www.visualbasicscript.com">VisualBasicScript.com</a>.

All Forums >> [Scripting] >> WSH & Client Side VBScript >> Creating a .CSV from a SQL Server Table

Page: [1]

Jump to:

New Messages	No New Messages
Hot Topic w/ New Messages	Hot Topic w/o New Messages
Locked w/ New Messages	Locked w/o New Messages

Post New Thread

Reply to Message

Post New Poll

Submit Vote

Delete My Own Post

Delete My Own Thread

Rate Posts