Your question, your audience. Choose who sees your identity—and your question—with question security.
Connect to SQL Server Export Table to CSV
Your help would be appreciated with this.
I need to create a VB Script that connects to an sql server and exports a table to csv. Examples or a basic script i can modify would be good.
Not sure where to start.
I need to create a VB Script that connects to an sql server and exports a table to csv. Examples or a basic script i can modify would be good.
Not sure where to start.
Asked:
6-
4
2- +2
1 Solution
here's the full code
just change your sql and connectionstring to the database
on error resume next
dim db, rs
set db= createobject("adodb.connec
db.open "your connection string here" '<<<change connection string here
if err.number<> 0 then
msgbox "Cannot connect to db"
else
set rs=createobject("adodb.rec
set rs=db.execute ("select prodnum,descript,price from products where isactive=1") ''<<<change SQL here
if err.number<>0 then msgbox "Error"
rs.movefirst
Set fs= CreateObject("Scripting.fi
Set file = fs.CreateTextFile("c:\expo
while not rs.eof
'create csv line here
file.writeline(rs.GetStrin
rs.movenext
wend
file.close
set fs=nothing
end if
rs.close
set rs=nothing
db.close
set db=nothing
hope this helps...
Cheers,
Dan
just change your sql and connectionstring to the database
on error resume next
dim db, rs
set db= createobject("adodb.connec
db.open "your connection string here" '<<<change connection string here
if err.number<> 0 then
msgbox "Cannot connect to db"
else
set rs=createobject("adodb.rec
set rs=db.execute ("select prodnum,descript,price from products where isactive=1") ''<<<change SQL here
if err.number<>0 then msgbox "Error"
rs.movefirst
Set fs= CreateObject("Scripting.fi
Set file = fs.CreateTextFile("c:\expo
while not rs.eof
'create csv line here
file.writeline(rs.GetStrin
rs.movenext
wend
file.close
set fs=nothing
end if
rs.close
set rs=nothing
db.close
set db=nothing
hope this helps...
Cheers,
Dan
method 1: DSN-less connection
---------------
db.open "Driver={Microsoft Access Driver (*.mdb)};Dbq=C:\mydatabase
'if your access database doesnt have a pwd then
db.open "Driver={Microsoft Access Driver (*.mdb)};Dbq=C:\mydatabase
Method2: DSN connection
------------
You can also... create a system dsn which is what i recommend...
here's how to:
1. go to control panel > administrative tools > Data sources (ODBC)
2. then click on the System DSN TAB
3. click on ADD
4. Select Microsoft Access Driver (*.MDB)
5. click Finish
6. enter a name for your dsn ex: myodbcname (this will be a sort of shortcut to ur db)
7. enter a description for this dsn (this step is optional)
8. click on the button "SELECT" under database
9. select your database file (it is preferred that the db is on a short path)
10. click on OK
now your connection string would be: "DSN=myodbcname;uid=admin;
now your connection string would be: "DSN=myodbcname;uid=admin;
for more information on connection strings goto: www.connectionstrings.com
hope i was helpful..
cheers,
dan
http://www.connectionstrings.com/
sorry i missunderstood ur question... i thought u wanted ms access connection string
here's the connection string in case you have an SQL SERVER:
Method 1: DSN-Less connection
--------------
db.open "Driver={SQL Server};Server=MyserverNam
Method 2: DSN Connection
-----------------
create the dsn like above... this time select sql server as driver.
follow the wizard till the end...
u will have to know if the authentication method for sql server is windows authentication or sql server authentication.
connection string is the same: "DSN=myodbcname;uid=admin;
cheers,
dan
here's the connection string in case you have an SQL SERVER:
Method 1: DSN-Less connection
--------------
db.open "Driver={SQL Server};Server=MyserverNam
Method 2: DSN Connection
-----------------
create the dsn like above... this time select sql server as driver.
follow the wizard till the end...
u will have to know if the authentication method for sql server is windows authentication or sql server authentication.
connection string is the same: "DSN=myodbcname;uid=admin;
cheers,
dan
Thanks...
I tried the above and found that when i try to export table it Give a MSG Box saying "Error"
I have done some investigating and found that i actually want to export a view thats inside the database called V_ST.
I went to design view of the database and have copied the select script from it.....
Hopefully this helps as to what i need to do...
SELECT ST.STKEY, ST.SURNAME + ' ' + ST.PREF_NAME + ' ' + ST.SCHOOL_YEAR + ' ' + ST.HOME_GROUP AS STUDENT,
CASE WHEN ST.CONTACT_A = 'A' THEN DF.TITLE_A + ' ' + (SUBSTRING(DF.NAME_A, 1, 1))
+ ' ' + DF.SURNAME_A WHEN ST.CONTACT_A = 'B' THEN DF.TITLE_B + ' ' + (SUBSTRING(DF.NAME_B, 1, 1))
+ ' ' + DF.SURNAME_B WHEN ST.CONTACT_A = 'N' THEN NULL
ELSE CASE WHEN DF.SURNAME_A = DF.SURNAME_B THEN DF.TITLE_A + ' ' + (SUBSTRING(DF.NAME_A, 1, 1))
+ ' & ' + DF.TITLE_B + ' ' + (SUBSTRING(DF.NAME_B, 1, 1))
+ ' ' + DF.SURNAME_A ELSE CASE WHEN DF.SURNAME_A <> DF.SURNAME_B THEN DF.TITLE_A + ' ' + (SUBSTRING(DF.NAME_A, 1, 1))
+ ' ' + DF.SURNAME_A + ' & ' + DF.TITLE_B + ' ' + (SUBSTRING(DF.NAME_B, 1, 1)) + ' ' + DF.SURNAME_B WHEN DF.SURNAME_A IS NULL AND
DF.SURNAME_B IS NOT NULL THEN DF.TITLE_B + ' ' + (SUBSTRING(DF.NAME_B, 1, 1)) + ' ' + DF.SURNAME_B WHEN DF.SURNAME_B IS NULL AND
DF.SURNAME_A IS NOT NULL THEN DF.TITLE_A + ' ' + (SUBSTRING(DF.NAME_A, 1, 1)) + ' ' + DF.SURNAME_A WHEN DF.SURNAME_A IS NULL AND
DF.SURNAME_B IS NULL THEN 'STUDENT HAS NO LISTED PARENTS' END END END AS PARENT1,
CASE WHEN ST.CONTACT_A = 'A' THEN DF.TITLE_A + ' ' + DF.SURNAME_A WHEN ST.CONTACT_A = 'B' THEN DF.TITLE_B + ' ' + DF.SURNAME_B WHEN
ST.CONTACT_A = 'N' THEN NULL
ELSE CASE WHEN DF.SURNAME_A = DF.SURNAME_B THEN DF.TITLE_A + ' & ' + DF.TITLE_B + ' ' + DF.SURNAME_A ELSE CASE WHEN DF.SURNAME_A
<> DF.SURNAME_B THEN DF.TITLE_A + ' ' + DF.SURNAME_A + ' & ' + DF.TITLE_B + ' ' + DF.SURNAME_B WHEN DF.SURNAME_A IS NULL AND
DF.SURNAME_B IS NOT NULL THEN DF.TITLE_B + ' ' + DF.SURNAME_B WHEN DF.SURNAME_B IS NULL AND DF.SURNAME_A IS NOT NULL
THEN DF.TITLE_A + ' ' + DF.SURNAME_A WHEN DF.SURNAME_A IS NULL AND DF.SURNAME_B IS NULL
THEN 'STUDENT HAS NO LISTED PARENTS' END END END AS PARENT2, ST.SURNAME, ST.FIRST_NAME, ST.SECOND_NAME, ST.PREF_NAME,
ST.TITLE, ST.AB_STUDY, ST.ED_ALLOW AS EMA, ST.BIRTHDATE, ST.CAMPUS, ST.ENTRY, ST.GENDER, ST.HOME_GROUP, ST.HOME_LANG,
ST.HOUSE, ST.KOORIE, ST.NOTES, ST.RELATION_A01, ST.SCHOOL_YEAR, ST.FAM_ORDER, KCY.DESCRIPTION, ST.STATUS, ST.STUDENT_PIC,
ST.TAG, ST.YOUTH_ALLOW, ST.FAMILY, DF.DFKEY, DF.SURNAME_A, DF.NAME_A, DF.TITLE_A, DF.E_MAIL_A, DF.SURNAME_B, DF.NAME_B,
DF.E_MAIL_B, DF.TITLE_B, DF.MAILKEY, DF.NO_STUDENTS, UM.UMKEY, UM.ADDRESS01, UM.ADDRESS02, UM.ADDRESS03, UM.POSTCODE,
UM.STATE, UM.TELEPHONE, UM.FAX
FROM (C21015374..ST AS ST INNER JOIN
C21015374..KCY AS KCY ON ST.SCHOOL_YEAR = KCY.KCYKEY) INNER JOIN
C21015374..DF AS DF ON DF.DFKEY = ST.FAMILY INNER JOIN
C21015374..UM AS UM ON DF.MAILKEY = UM.UMKEY
WHERE ST.STATUS <> 'LEFT'
I tried the above and found that when i try to export table it Give a MSG Box saying "Error"
I have done some investigating and found that i actually want to export a view thats inside the database called V_ST.
I went to design view of the database and have copied the select script from it.....
Hopefully this helps as to what i need to do...
SELECT ST.STKEY, ST.SURNAME + ' ' + ST.PREF_NAME + ' ' + ST.SCHOOL_YEAR + ' ' + ST.HOME_GROUP AS STUDENT,
CASE WHEN ST.CONTACT_A = 'A' THEN DF.TITLE_A + ' ' + (SUBSTRING(DF.NAME_A, 1, 1))
+ ' ' + DF.SURNAME_A WHEN ST.CONTACT_A = 'B' THEN DF.TITLE_B + ' ' + (SUBSTRING(DF.NAME_B, 1, 1))
+ ' ' + DF.SURNAME_B WHEN ST.CONTACT_A = 'N' THEN NULL
ELSE CASE WHEN DF.SURNAME_A = DF.SURNAME_B THEN DF.TITLE_A + ' ' + (SUBSTRING(DF.NAME_A, 1, 1))
+ ' & ' + DF.TITLE_B + ' ' + (SUBSTRING(DF.NAME_B, 1, 1))
+ ' ' + DF.SURNAME_A ELSE CASE WHEN DF.SURNAME_A <> DF.SURNAME_B THEN DF.TITLE_A + ' ' + (SUBSTRING(DF.NAME_A, 1, 1))
+ ' ' + DF.SURNAME_A + ' & ' + DF.TITLE_B + ' ' + (SUBSTRING(DF.NAME_B, 1, 1)) + ' ' + DF.SURNAME_B WHEN DF.SURNAME_A IS NULL AND
DF.SURNAME_B IS NOT NULL THEN DF.TITLE_B + ' ' + (SUBSTRING(DF.NAME_B, 1, 1)) + ' ' + DF.SURNAME_B WHEN DF.SURNAME_B IS NULL AND
DF.SURNAME_A IS NOT NULL THEN DF.TITLE_A + ' ' + (SUBSTRING(DF.NAME_A, 1, 1)) + ' ' + DF.SURNAME_A WHEN DF.SURNAME_A IS NULL AND
DF.SURNAME_B IS NULL THEN 'STUDENT HAS NO LISTED PARENTS' END END END AS PARENT1,
CASE WHEN ST.CONTACT_A = 'A' THEN DF.TITLE_A + ' ' + DF.SURNAME_A WHEN ST.CONTACT_A = 'B' THEN DF.TITLE_B + ' ' + DF.SURNAME_B WHEN
ST.CONTACT_A = 'N' THEN NULL
ELSE CASE WHEN DF.SURNAME_A = DF.SURNAME_B THEN DF.TITLE_A + ' & ' + DF.TITLE_B + ' ' + DF.SURNAME_A ELSE CASE WHEN DF.SURNAME_A
<> DF.SURNAME_B THEN DF.TITLE_A + ' ' + DF.SURNAME_A + ' & ' + DF.TITLE_B + ' ' + DF.SURNAME_B WHEN DF.SURNAME_A IS NULL AND
DF.SURNAME_B IS NOT NULL THEN DF.TITLE_B + ' ' + DF.SURNAME_B WHEN DF.SURNAME_B IS NULL AND DF.SURNAME_A IS NOT NULL
THEN DF.TITLE_A + ' ' + DF.SURNAME_A WHEN DF.SURNAME_A IS NULL AND DF.SURNAME_B IS NULL
THEN 'STUDENT HAS NO LISTED PARENTS' END END END AS PARENT2, ST.SURNAME, ST.FIRST_NAME, ST.SECOND_NAME, ST.PREF_NAME,
ST.TITLE, ST.AB_STUDY, ST.ED_ALLOW AS EMA, ST.BIRTHDATE, ST.CAMPUS, ST.ENTRY, ST.GENDER, ST.HOME_GROUP, ST.HOME_LANG,
ST.HOUSE, ST.KOORIE, ST.NOTES, ST.RELATION_A01, ST.SCHOOL_YEAR, ST.FAM_ORDER, KCY.DESCRIPTION, ST.STATUS, ST.STUDENT_PIC,
ST.TAG, ST.YOUTH_ALLOW, ST.FAMILY, DF.DFKEY, DF.SURNAME_A, DF.NAME_A, DF.TITLE_A, DF.E_MAIL_A, DF.SURNAME_B, DF.NAME_B,
DF.E_MAIL_B, DF.TITLE_B, DF.MAILKEY, DF.NO_STUDENTS, UM.UMKEY, UM.ADDRESS01, UM.ADDRESS02, UM.ADDRESS03, UM.POSTCODE,
UM.STATE, UM.TELEPHONE, UM.FAX
FROM (C21015374..ST AS ST INNER JOIN
C21015374..KCY AS KCY ON ST.SCHOOL_YEAR = KCY.KCYKEY) INNER JOIN
C21015374..DF AS DF ON DF.DFKEY = ST.FAMILY INNER JOIN
C21015374..UM AS UM ON DF.MAILKEY = UM.UMKEY
WHERE ST.STATUS <> 'LEFT'
what's the error code u're getting? is it a runtime error? or a msgbox that u set!!
please give us some code...
the sql of the view is not needed for now...
cheers,
dan
please give us some code...
the sql of the view is not needed for now...
cheers,
dan
can you try:
if you'r catching the error using if err.number <>0 then please use instead:
msgbox "Error: " & err.number & vbcrlf & err.description
tell me what u get!
and try this sql:
Dim sql As String
sql = "SELECT ST.STKEY, ST.SURNAME + ' ' + ST.PREF_NAME + ' ' + ST.SCHOOL_YEAR + ' ' + ST.HOME_GROUP AS STUDENT,"
sql = sql & " CASE WHEN ST.CONTACT_A = 'A' THEN DF.TITLE_A + ' ' + (SUBSTRING(DF.NAME_A, 1, 1))"
sql = sql & " + ' ' + DF.SURNAME_A WHEN ST.CONTACT_A = 'B' THEN DF.TITLE_B + ' ' + (SUBSTRING(DF.NAME_B, 1, 1))"
sql = sql & " + ' ' + DF.SURNAME_B WHEN ST.CONTACT_A = 'N' THEN NULL"
sql = sql & " ELSE CASE WHEN DF.SURNAME_A = DF.SURNAME_B THEN DF.TITLE_A + ' ' + (SUBSTRING(DF.NAME_A, 1, 1))"
sql = sql & " + ' & ' + DF.TITLE_B + ' ' + (SUBSTRING(DF.NAME_B, 1, 1))"
sql = sql & " + ' ' + DF.SURNAME_A ELSE CASE WHEN DF.SURNAME_A <> DF.SURNAME_B THEN DF.TITLE_A + ' ' + (SUBSTRING(DF.NAME_A, 1, 1))"
sql = sql & " + ' ' + DF.SURNAME_A + ' & ' + DF.TITLE_B + ' ' + (SUBSTRING(DF.NAME_B, 1, 1)) + ' ' + DF.SURNAME_B WHEN DF.SURNAME_A IS NULL AND"
sql = sql & " DF.SURNAME_B IS NOT NULL THEN DF.TITLE_B + ' ' + (SUBSTRING(DF.NAME_B, 1, 1)) + ' ' + DF.SURNAME_B WHEN DF.SURNAME_B IS NULL AND"
sql = sql & " DF.SURNAME_A IS NOT NULL THEN DF.TITLE_A + ' ' + (SUBSTRING(DF.NAME_A, 1, 1)) + ' ' + DF.SURNAME_A WHEN DF.SURNAME_A IS NULL AND"
sql = sql & " DF.SURNAME_B IS NULL THEN 'STUDENT HAS NO LISTED PARENTS' END END END AS PARENT1,"
sql = sql & " CASE WHEN ST.CONTACT_A = 'A' THEN DF.TITLE_A + ' ' + DF.SURNAME_A WHEN ST.CONTACT_A = 'B' THEN DF.TITLE_B + ' ' + DF.SURNAME_B WHEN"
sql = sql & " ST.CONTACT_A = 'N' THEN NULL"
sql = sql & " ELSE CASE WHEN DF.SURNAME_A = DF.SURNAME_B THEN DF.TITLE_A + ' & ' + DF.TITLE_B + ' ' + DF.SURNAME_A ELSE CASE WHEN DF.SURNAME_A"
sql = sql & " <> DF.SURNAME_B THEN DF.TITLE_A + ' ' + DF.SURNAME_A + ' & ' + DF.TITLE_B + ' ' + DF.SURNAME_B WHEN DF.SURNAME_A IS NULL AND"
sql = sql & " DF.SURNAME_B IS NOT NULL THEN DF.TITLE_B + ' ' + DF.SURNAME_B WHEN DF.SURNAME_B IS NULL AND DF.SURNAME_A IS NOT NULL"
sql = sql & " THEN DF.TITLE_A + ' ' + DF.SURNAME_A WHEN DF.SURNAME_A IS NULL AND DF.SURNAME_B IS NULL"
sql = sql & " THEN 'STUDENT HAS NO LISTED PARENTS' END END END AS PARENT2, ST.SURNAME, ST.FIRST_NAME, ST.SECOND_NAME, ST.PREF_NAME,"
sql = sql & " ST.TITLE, ST.AB_STUDY, ST.ED_ALLOW AS EMA, ST.BIRTHDATE, ST.CAMPUS, ST.ENTRY, ST.GENDER, ST.HOME_GROUP, ST.HOME_LANG,"
sql = sql & " ST.HOUSE, ST.KOORIE, ST.NOTES, ST.RELATION_A01, ST.SCHOOL_YEAR, ST.FAM_ORDER, KCY.DESCRIPTION, ST.STATUS, ST.STUDENT_PIC,"
sql = sql & " ST.TAG, ST.YOUTH_ALLOW, ST.FAMILY, DF.DFKEY, DF.SURNAME_A, DF.NAME_A, DF.TITLE_A, DF.E_MAIL_A, DF.SURNAME_B, DF.NAME_B,"
sql = sql & " DF.E_MAIL_B, DF.TITLE_B, DF.MAILKEY, DF.NO_STUDENTS, UM.UMKEY, UM.ADDRESS01, UM.ADDRESS02, UM.ADDRESS03, UM.POSTCODE,"
sql = sql & " UM.STATE , UM.TELEPHONE, UM.FAX"
sql = sql & " FROM (C21015374..ST AS ST INNER JOIN"
sql = sql & " C21015374..KCY AS KCY ON ST.SCHOOL_YEAR = KCY.KCYKEY) INNER JOIN"
sql = sql & " C21015374..DF AS DF ON DF.DFKEY = ST.FAMILY INNER JOIN"
sql = sql & " C21015374..UM AS UM ON DF.MAILKEY = UM.UMKEY"
sql = sql & " WHERE ST.STATUS <> 'LEFT'"
cheers,
dan
if you'r catching the error using if err.number <>0 then please use instead:
msgbox "Error: " & err.number & vbcrlf & err.description
tell me what u get!
and try this sql:
Dim sql As String
sql = "SELECT ST.STKEY, ST.SURNAME + ' ' + ST.PREF_NAME + ' ' + ST.SCHOOL_YEAR + ' ' + ST.HOME_GROUP AS STUDENT,"
sql = sql & " CASE WHEN ST.CONTACT_A = 'A' THEN DF.TITLE_A + ' ' + (SUBSTRING(DF.NAME_A, 1, 1))"
sql = sql & " + ' ' + DF.SURNAME_A WHEN ST.CONTACT_A = 'B' THEN DF.TITLE_B + ' ' + (SUBSTRING(DF.NAME_B, 1, 1))"
sql = sql & " + ' ' + DF.SURNAME_B WHEN ST.CONTACT_A = 'N' THEN NULL"
sql = sql & " ELSE CASE WHEN DF.SURNAME_A = DF.SURNAME_B THEN DF.TITLE_A + ' ' + (SUBSTRING(DF.NAME_A, 1, 1))"
sql = sql & " + ' & ' + DF.TITLE_B + ' ' + (SUBSTRING(DF.NAME_B, 1, 1))"
sql = sql & " + ' ' + DF.SURNAME_A ELSE CASE WHEN DF.SURNAME_A <> DF.SURNAME_B THEN DF.TITLE_A + ' ' + (SUBSTRING(DF.NAME_A, 1, 1))"
sql = sql & " + ' ' + DF.SURNAME_A + ' & ' + DF.TITLE_B + ' ' + (SUBSTRING(DF.NAME_B, 1, 1)) + ' ' + DF.SURNAME_B WHEN DF.SURNAME_A IS NULL AND"
sql = sql & " DF.SURNAME_B IS NOT NULL THEN DF.TITLE_B + ' ' + (SUBSTRING(DF.NAME_B, 1, 1)) + ' ' + DF.SURNAME_B WHEN DF.SURNAME_B IS NULL AND"
sql = sql & " DF.SURNAME_A IS NOT NULL THEN DF.TITLE_A + ' ' + (SUBSTRING(DF.NAME_A, 1, 1)) + ' ' + DF.SURNAME_A WHEN DF.SURNAME_A IS NULL AND"
sql = sql & " DF.SURNAME_B IS NULL THEN 'STUDENT HAS NO LISTED PARENTS' END END END AS PARENT1,"
sql = sql & " CASE WHEN ST.CONTACT_A = 'A' THEN DF.TITLE_A + ' ' + DF.SURNAME_A WHEN ST.CONTACT_A = 'B' THEN DF.TITLE_B + ' ' + DF.SURNAME_B WHEN"
sql = sql & " ST.CONTACT_A = 'N' THEN NULL"
sql = sql & " ELSE CASE WHEN DF.SURNAME_A = DF.SURNAME_B THEN DF.TITLE_A + ' & ' + DF.TITLE_B + ' ' + DF.SURNAME_A ELSE CASE WHEN DF.SURNAME_A"
sql = sql & " <> DF.SURNAME_B THEN DF.TITLE_A + ' ' + DF.SURNAME_A + ' & ' + DF.TITLE_B + ' ' + DF.SURNAME_B WHEN DF.SURNAME_A IS NULL AND"
sql = sql & " DF.SURNAME_B IS NOT NULL THEN DF.TITLE_B + ' ' + DF.SURNAME_B WHEN DF.SURNAME_B IS NULL AND DF.SURNAME_A IS NOT NULL"
sql = sql & " THEN DF.TITLE_A + ' ' + DF.SURNAME_A WHEN DF.SURNAME_A IS NULL AND DF.SURNAME_B IS NULL"
sql = sql & " THEN 'STUDENT HAS NO LISTED PARENTS' END END END AS PARENT2, ST.SURNAME, ST.FIRST_NAME, ST.SECOND_NAME, ST.PREF_NAME,"
sql = sql & " ST.TITLE, ST.AB_STUDY, ST.ED_ALLOW AS EMA, ST.BIRTHDATE, ST.CAMPUS, ST.ENTRY, ST.GENDER, ST.HOME_GROUP, ST.HOME_LANG,"
sql = sql & " ST.HOUSE, ST.KOORIE, ST.NOTES, ST.RELATION_A01, ST.SCHOOL_YEAR, ST.FAM_ORDER, KCY.DESCRIPTION, ST.STATUS, ST.STUDENT_PIC,"
sql = sql & " ST.TAG, ST.YOUTH_ALLOW, ST.FAMILY, DF.DFKEY, DF.SURNAME_A, DF.NAME_A, DF.TITLE_A, DF.E_MAIL_A, DF.SURNAME_B, DF.NAME_B,"
sql = sql & " DF.E_MAIL_B, DF.TITLE_B, DF.MAILKEY, DF.NO_STUDENTS, UM.UMKEY, UM.ADDRESS01, UM.ADDRESS02, UM.ADDRESS03, UM.POSTCODE,"
sql = sql & " UM.STATE , UM.TELEPHONE, UM.FAX"
sql = sql & " FROM (C21015374..ST AS ST INNER JOIN"
sql = sql & " C21015374..KCY AS KCY ON ST.SCHOOL_YEAR = KCY.KCYKEY) INNER JOIN"
sql = sql & " C21015374..DF AS DF ON DF.DFKEY = ST.FAMILY INNER JOIN"
sql = sql & " C21015374..UM AS UM ON DF.MAILKEY = UM.UMKEY"
sql = sql & " WHERE ST.STATUS <> 'LEFT'"
cheers,
dan
ok i have returned back to trying the original v_st exporting the view as though it were a table.
the error i get is saying that the user i am using is not a database owner.
I checked the user and can see it's got public access
How can i export as the public user without actually changing any settings in my SQL Server.
I checked this by briefly adding the db_owner to the username properties and it worked.
the error i get is saying that the user i am using is not a database owner.
I checked the user and can see it's got public access
How can i export as the public user without actually changing any settings in my SQL Server.
I checked this by briefly adding the db_owner to the username properties and it worked.
The public role is a special database role to which every database user belongs. The public role contains default access permissions for any user who can access the database. This database role cannot be dropped.
you dont need to export as public, u just need to give access to the user to all tables in ur db
the easiest solution is to put the user in the db_datareader group
and if u want him to also write to the db then add him to the db_datawriter group
tell me if this helps,
cheers,
dan
you dont need to export as public, u just need to give access to the user to all tables in ur db
the easiest solution is to put the user in the db_datareader group
and if u want him to also write to the db then add him to the db_datawriter group
tell me if this helps,
cheers,
dan
Question has a verified solution.
Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.
Have a better answer? Share it in a comment.
Join & Write a Comment Already a member? Login.
Featured Post
Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.
One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.
Tackle projects and never again get stuck behind a technical roadblock.
Join Now
I don't really have time this minute to write a script but why don't you grab the recordset as you would normally and insert it into an Excel file and tell Excel to save it as a csv file. This assumes that you have Office/Excel on the system.
Regards,
Lee