A question from a newbie

Can somebody tell me the correct syntax if I want to grant SELECT privileges
on all tables in a db?
I tried:
GRANT SELECT TO public;
Thanks.
VenkatAdd the user to the db_datareader fixed database role.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"vvenk" <vvenk@.discussions.microsoft.com> wrote in message
news:A63A543C-1176-4AEF-BC2E-2FBA35D5DB63@.microsoft.com...
> Can somebody tell me the correct syntax if I want to grant SELECT
privileges
> on all tables in a db?
> I tried:
> GRANT SELECT TO public;
> Thanks.
> Venkat|||Geoff:
Thanks a lot. I was reading some of the postings on this forum that
suggested that I have to write a script to cycle through all the tables.
Your's is the most elegant solution.
Venkat
"Geoff N. Hiten" wrote:

> Add the user to the db_datareader fixed database role.
> --
> Geoff N. Hiten
> Microsoft SQL Server MVP
> Senior Database Administrator
> Careerbuilder.com
> I support the Professional Association for SQL Server
> www.sqlpass.org
> "vvenk" <vvenk@.discussions.microsoft.com> wrote in message
> news:A63A543C-1176-4AEF-BC2E-2FBA35D5DB63@.microsoft.com...
> privileges
>
>|||To add to Geoff's response, the built-in db_datareader and db_datawriter
roles are fine if these meet your security requirements. Granting
permissions to individual users or user-defined roles is appropriate when
you need more granular security and/or stored procedure execute permissions.
Utility scripts can be faster than granting permissions via Enterprise
Manager when you have a lot of objects.
Hope this helps.
Dan Guzman
SQL Server MVP
"vvenk" <vvenk@.discussions.microsoft.com> wrote in message
news:2FB6F0F8-D8B8-47B0-8743-DD51C39D4A5E@.microsoft.com...[vbcol=seagreen]
> Geoff:
> Thanks a lot. I was reading some of the postings on this forum that
> suggested that I have to write a script to cycle through all the tables.
> Your's is the most elegant solution.
> Venkat
> "Geoff N. Hiten" wrote:
>

A new member could not be added to a local group because the member has the wrong account type

Hi all,

I installed SQL 2005 SP2 + ReportServices Add-in for Sharepoint (WSS 3.0). All it's OK until I try to grant database access in the Sharepoint Central Admin site.

I setup the Reporting Services Integration (Manage integration settings). I use the default SQL instance, I put the USERNAME and the PASSWORD of my ADMIN account in the ENTER CREDENTIALS windows.. When I click the "OK" button, I receive always the error ...

"A new member could not be added to a local group because the member has the wrong account type"

I tried a lot of things... without success.

Is there someone who can help me....

PS: There is no error in the LOG

Thanks

I am also having this same issue. I cannot seem to find any help for this out there by Google search or otherwise. I've seen some blip about installing on a domain controller. Please help.|||I discovered a workaround. I used the IP address of the DBMS instead of the local computer name. Then I used the DOMAINNAME\SP_Setup_User (or admin) account and password. This worked. Hopefully no ill effects.|||

Unfortunately, this approach didn't work for me. If any other idea, please let me know.

Thanks

|||

Erik,

I came to the conclusion to use the IP address of the SQL Server instead of the name because, you know how you want to map a drive to the same server using two different user accounts? Well, you have to use the server name for one mapping and then something else for the second mapping, like the IP address. It probably has nothing to do with this problem, but it reminded me of it. So it did work, at least on my system. Again, I do not know if there are any obscure consequences of doing this. So far all is well. Maybe there is a Microsoft person listening in or someone who could lend some thoughts to the situation for you.

Mike

|||

Hi Guys, had the same problem. Tried with IP address and yes it worked. However, when I went into Visual Studio and created a report when I went to deploy it a username/password form pops up. No matter what username/password is inserted can't get it to deploy. Any help would be greatly appreciated.

Keith

|||

Try using Fully Qualified domain Name of the SQL Server..

and It Works!..

|||

do you mean servername\username & password because it did not work. I enterede servername/username in the reporting services UI and then went into visual studio to deploy (on same machine) but when I went to deploy you would enter servername/username and it would just keep poping up and never do anything.

Any thing to look at for the account. I have tried a user account and on last atempt I used administrator, but no luck.

Thanks for any help!

|||

I have done some more work on it and in sharepoint central administration, reporting services, manage integration services, set to windows account. and it all saves with out complaing. Now when I gointo Visual studio on the same machine as reporting service and sharepoint I no longer get a log in box but I get "a connection could not be made to the report server... this operation is not supported on a report server that is configured to run in sharepoint integration...

anybody and thoughts?

Thanks

|||

did some more work/investigation and I stumbled into that if you go the sharepoint site, document library and try to add the new document type of reporting services, it does not show up in the list. So I think everything deployed okay but it appears there is no link between sharepoint and report services. Also incase this helps, sharepoint is not in the default website, but is it's own web site that instead of www we have companyweb. Other that reporting services sharpoint works fine.

Help!

|||

Keith,

Our report server web service is at http://{Report Server servername}:8080/ReportServer2K5, port 8080 being the new port of the re-enabled IIS default web site. However, the deployment properties for the VS 2005 report project actually had to be set like so: (1) TargetDataSourceFolder and TargetReportFolder = http://{SharePoint servername}:{Admin Site Port Number}/{Our Report Library Name}/, and (2) TargetServerURL = http://{SharePoint servername}:{Admin Site Port Number}/. I can deploy right from Visual Studio with this configuration. The account used for the Windows service identity is the same as that used for SharePoint administration in my case. There are a myriad of possible combinations for these accounts, though.

Mike

A new member could not be added to a local group because the member has the wrong account type

Hi all,

I installed SQL 2005 SP2 + ReportServices Add-in for Sharepoint (WSS 3.0). All it's OK until I try to grant database access in the Sharepoint Central Admin site.

I setup the Reporting Services Integration (Manage integration settings). I use the default SQL instance, I put the USERNAME and the PASSWORD of my ADMIN account in the ENTER CREDENTIALS windows.. When I click the "OK" button, I receive always the error ...

"A new member could not be added to a local group because the member has the wrong account type"

I tried a lot of things... without success.

Is there someone who can help me....

PS: There is no error in the LOG

Thanks

I am also having this same issue. I cannot seem to find any help for this out there by Google search or otherwise. I've seen some blip about installing on a domain controller. Please help.|||I discovered a workaround. I used the IP address of the DBMS instead of the local computer name. Then I used the DOMAINNAME\SP_Setup_User (or admin) account and password. This worked. Hopefully no ill effects.|||

Unfortunately, this approach didn't work for me. If any other idea, please let me know.

Thanks

|||

Erik,

I came to the conclusion to use the IP address of the SQL Server instead of the name because, you know how you want to map a drive to the same server using two different user accounts? Well, you have to use the server name for one mapping and then something else for the second mapping, like the IP address. It probably has nothing to do with this problem, but it reminded me of it. So it did work, at least on my system. Again, I do not know if there are any obscure consequences of doing this. So far all is well. Maybe there is a Microsoft person listening in or someone who could lend some thoughts to the situation for you.

Mike

|||

Hi Guys, had the same problem. Tried with IP address and yes it worked. However, when I went into Visual Studio and created a report when I went to deploy it a username/password form pops up. No matter what username/password is inserted can't get it to deploy. Any help would be greatly appreciated.

Keith

|||

Try using Fully Qualified domain Name of the SQL Server..

and It Works!..

|||

do you mean servername\username & password because it did not work. I enterede servername/username in the reporting services UI and then went into visual studio to deploy (on same machine) but when I went to deploy you would enter servername/username and it would just keep poping up and never do anything.

Any thing to look at for the account. I have tried a user account and on last atempt I used administrator, but no luck.

Thanks for any help!

|||

I have done some more work on it and in sharepoint central administration, reporting services, manage integration services, set to windows account. and it all saves with out complaing. Now when I gointo Visual studio on the same machine as reporting service and sharepoint I no longer get a log in box but I get "a connection could not be made to the report server... this operation is not supported on a report server that is configured to run in sharepoint integration...

anybody and thoughts?

Thanks

|||

did some more work/investigation and I stumbled into that if you go the sharepoint site, document library and try to add the new document type of reporting services, it does not show up in the list. So I think everything deployed okay but it appears there is no link between sharepoint and report services. Also incase this helps, sharepoint is not in the default website, but is it's own web site that instead of www we have companyweb. Other that reporting services sharpoint works fine.

Help!

|||

Keith,

Our report server web service is at http://{Report Server servername}:8080/ReportServer2K5, port 8080 being the new port of the re-enabled IIS default web site. However, the deployment properties for the VS 2005 report project actually had to be set like so: (1) TargetDataSourceFolder and TargetReportFolder = http://{SharePoint servername}:{Admin Site Port Number}/{Our Report Library Name}/, and (2) TargetServerURL = http://{SharePoint servername}:{Admin Site Port Number}/. I can deploy right from Visual Studio with this configuration. The account used for the Windows service identity is the same as that used for SharePoint administration in my case. There are a myriad of possible combinations for these accounts, though.

Mike

A new member could not be added to a local group because the member has the wrong account type

Hi all,

I installed SQL 2005 SP2 + ReportServices Add-in for Sharepoint (WSS 3.0). All it's OK until I try to grant database access in the Sharepoint Central Admin site.

I setup the Reporting Services Integration (Manage integration settings). I use the default SQL instance, I put the USERNAME and the PASSWORD of my ADMIN account in the ENTER CREDENTIALS windows.. When I click the "OK" button, I receive always the error ...

"A new member could not be added to a local group because the member has the wrong account type"

I tried a lot of things... without success.

Is there someone who can help me....

PS: There is no error in the LOG

Thanks

Hi Did anyone have a resolution to this situation. We're getting the same error message following the same sequence of events. Any assistance is appreciated.

|||

No... nothing... niet...

If you find any info, let me know please.

|||

Anybody find anything on this? I'm getting same error|||

I'm getting the same frustrating error. Any news on a solution?

Thanks!

A new member could not be added to a local group because the member has the wrong account type

Hi all,

I installed SQL 2005 SP2 + ReportServices Add-in for Sharepoint (WSS 3.0). All it's OK until I try to grant database access in the Sharepoint Central Admin site.

I setup the Reporting Services Integration (Manage integration settings). I use the default SQL instance, I put the USERNAME and the PASSWORD of my ADMIN account in the ENTER CREDENTIALS windows.. When I click the "OK" button, I receive always the error ...

"A new member could not be added to a local group because the member has the wrong account type"

I tried a lot of things... without success.

Is there someone who can help me....

PS: There is no error in the LOG

Thanks

Hi Did anyone have a resolution to this situation. We're getting the same error message following the same sequence of events. Any assistance is appreciated.

|||

No... nothing... niet...

If you find any info, let me know please.

|||

Anybody find anything on this? I'm getting same error|||

I'm getting the same frustrating error. Any news on a solution?

Thanks!

A new member could not be added to a local group because the member has the wrong account type

Hi all,

I installed SQL 2005 SP2 + ReportServices Add-in for Sharepoint (WSS 3.0). All it's OK until I try to grant database access in the Sharepoint Central Admin site.

I setup the Reporting Services Integration (Manage integration settings). I use the default SQL instance, I put the USERNAME and the PASSWORD of my ADMIN account in the ENTER CREDENTIALS windows.. When I click the "OK" button, I receive always the error ...

"A new member could not be added to a local group because the member has the wrong account type"

I tried a lot of things... without success.

Is there someone who can help me....

PS: There is no error in the LOG

Thanks

Hi Did anyone have a resolution to this situation. We're getting the same error message following the same sequence of events. Any assistance is appreciated.|||

No... nothing... niet...

If you find any info, let me know please.

|||Anybody find anything on this? I'm getting same error|||

I'm getting the same frustrating error. Any news on a solution?

Thanks!

A new member could not be added to a local group because the member has the wrong account type

Hi all,

I installed SQL 2005 SP2 + ReportServices Add-in for Sharepoint (WSS 3.0). All it's OK until I try to grant database access in the Sharepoint Central Admin site.

I setup the Reporting Services Integration (Manage integration settings). I use the default SQL instance, I put the USERNAME and the PASSWORD of my ADMIN account in the ENTER CREDENTIALS windows.. When I click the "OK" button, I receive always the error ...

"A new member could not be added to a local group because the member has the wrong account type"

I tried a lot of things... without success.

Is there someone who can help me....

PS: There is no error in the LOG

Thanks

Hi Did anyone have a resolution to this situation. We're getting the same error message following the same sequence of events. Any assistance is appreciated.|||

No... nothing... niet...

If you find any info, let me know please.

|||Anybody find anything on this? I'm getting same error|||

I'm getting the same frustrating error. Any news on a solution?

Thanks!

A new member could not be added to a local group because the member has the wrong account type

Hi all,

I installed SQL 2005 SP2 + ReportServices Add-in for Sharepoint (WSS 3.0). All it's OK until I try to grant database access in the Sharepoint Central Admin site.

I setup the Reporting Services Integration (Manage integration settings). I use the default SQL instance, I put the USERNAME and the PASSWORD of my ADMIN account in the ENTER CREDENTIALS windows.. When I click the "OK" button, I receive always the error ...

"A new member could not be added to a local group because the member has the wrong account type"

I tried a lot of things... without success.

Is there someone who can help me....

PS: There is no error in the LOG

Thanks

I am also having this same issue. I cannot seem to find any help for this out there by Google search or otherwise. I've seen some blip about installing on a domain controller. Please help.|||I discovered a workaround. I used the IP address of the DBMS instead of the local computer name. Then I used the DOMAINNAME\SP_Setup_User (or admin) account and password. This worked. Hopefully no ill effects.|||

Unfortunately, this approach didn't work for me. If any other idea, please let me know.

Thanks

|||

Erik,

I came to the conclusion to use the IP address of the SQL Server instead of the name because, you know how you want to map a drive to the same server using two different user accounts? Well, you have to use the server name for one mapping and then something else for the second mapping, like the IP address. It probably has nothing to do with this problem, but it reminded me of it. So it did work, at least on my system. Again, I do not know if there are any obscure consequences of doing this. So far all is well. Maybe there is a Microsoft person listening in or someone who could lend some thoughts to the situation for you.

Mike

|||

Hi Guys, had the same problem. Tried with IP address and yes it worked. However, when I went into Visual Studio and created a report when I went to deploy it a username/password form pops up. No matter what username/password is inserted can't get it to deploy. Any help would be greatly appreciated.

Keith

|||

Try using Fully Qualified domain Name of the SQL Server..

and It Works!..

|||

do you mean servername\username & password because it did not work. I enterede servername/username in the reporting services UI and then went into visual studio to deploy (on same machine) but when I went to deploy you would enter servername/username and it would just keep poping up and never do anything.

Any thing to look at for the account. I have tried a user account and on last atempt I used administrator, but no luck.

Thanks for any help!

|||

I have done some more work on it and in sharepoint central administration, reporting services, manage integration services, set to windows account. and it all saves with out complaing. Now when I gointo Visual studio on the same machine as reporting service and sharepoint I no longer get a log in box but I get "a connection could not be made to the report server... this operation is not supported on a report server that is configured to run in sharepoint integration...

anybody and thoughts?

Thanks

|||

did some more work/investigation and I stumbled into that if you go the sharepoint site, document library and try to add the new document type of reporting services, it does not show up in the list. So I think everything deployed okay but it appears there is no link between sharepoint and report services. Also incase this helps, sharepoint is not in the default website, but is it's own web site that instead of www we have companyweb. Other that reporting services sharpoint works fine.

Help!

|||

Keith,

Our report server web service is at http://{Report Server servername}:8080/ReportServer2K5, port 8080 being the new port of the re-enabled IIS default web site. However, the deployment properties for the VS 2005 report project actually had to be set like so: (1) TargetDataSourceFolder and TargetReportFolder = http://{SharePoint servername}:{Admin Site Port Number}/{Our Report Library Name}/, and (2) TargetServerURL = http://{SharePoint servername}:{Admin Site Port Number}/. I can deploy right from Visual Studio with this configuration. The account used for the Windows service identity is the same as that used for SharePoint administration in my case. There are a myriad of possible combinations for these accounts, though.

Mike

A mindboggingly basic question

I'm very new to being an SQL DBA (I'm an MS Access and Cold Fusion
developer), and I've been tossed in the deep with a grant project
where we're going to be pushing out data in XML format. So I'm trying
to bring myself up to speed on, well, how to do this. I've been going
through an online XML tutorial (http://www.w3schools.com/xml/) and I
think I get the basic idea of XML and schemas.
I've lately been trying to apply what I learned there to get XML
output from SQL. I started with the code snippet in the SQL Books
Online for the Northwind Db. I created a stored procedure with this in
it:
SELECT Customers.CustomerID, ContactName, CompanyName,
Orders.CustomerID, OrderDate
FROM Customers, Orders
WHERE Customers.CustomerID = Orders.CustomerID
AND (Customers.CustomerID = N'ALFKI'
OR Customers.CustomerID = N'XYZAA')
ORDER BY Customers.CustomerID
FOR XML AUTO
GO
This is what it outputs in the query analyzer:
<Customers CustomerID="ALFKI" ContactName="Maria Anders"
CompanyName="Alfreds Futterkiste"><Orders CustomerID="ALFKI"
OrderDate="1997-08-25T00:00:00"/><Orders CustomerID="ALFKI"
OrderDate="1997-10-03T00:00:00"/><Orders CustomerID="ALFKI"
OrderDate="1997-10
I'm assuming that it's being truncated because of something to do with
the query analyzer.
So I tried linking to the SP via an Access DB, and outputting it into
a text format (well, I've tried a couple of different things). When I
try the text format, this is what I get:
| XML_F52E2B61-18A1- |
| <Customers |
I just must be missing something about how I can get the information
the SP retrieves into a plain file that can be used as XML. I know
this is really basic programming stuff, but I seem to be hitting a
wall in my head somewhere in how I'm thinking about this.
So what I'm looking for is an idea of what to use on the front end to
get the SP I have into a file that can be pushed around places. Access
doesn't seem to have what I'm looking for (or maybe I'm just not
looking at the right commands). This is the Access code I'm using to
create the files in question:
DoCmd.OutputTo acOutputQuery, "qryXMLOrdersAndCustomers", txt,
"C:\CFusionMX\wwwroot\TestingXMLStuff\xmlfromnorth wind.xml"
I know this is a longwinded post, and I know that this is a really
basic question, but if someone could help point me in the right
direction, I'd really appreciate it.
Siobhan Perricone
Systems Developer
Vermont Agency of Natural Resources
(my comments are my own, not my employer's)
You are correct, there are limitations with Query Analyzer regarding column
size which causes the result set to be truncated. Go to
TOOLS/OPTIONS/RESULTS and set the "Maximum characters per column:" to 8192.
That is the max allowed in QA.
To get the results into a file go to QUERY and select "Results to File".
If you get results greater than 8192 chars, you might need to edit the file
to get rid of unneccesary line breaks that occur at 8192 intervals. However,
this size should allow you to run most test queries.
"Siobhan Perricone" <siobhan.perricone@.nostatespam.vt.us> wrote in message
news:j552g0pefgfmvtchbb6tes9ah0vp61cgbo@.4ax.com...
> I'm very new to being an SQL DBA (I'm an MS Access and Cold Fusion
> developer), and I've been tossed in the deep with a grant project
> where we're going to be pushing out data in XML format. So I'm trying
> to bring myself up to speed on, well, how to do this. I've been going
> through an online XML tutorial (http://www.w3schools.com/xml/) and I
> think I get the basic idea of XML and schemas.
> I've lately been trying to apply what I learned there to get XML
> output from SQL. I started with the code snippet in the SQL Books
> Online for the Northwind Db. I created a stored procedure with this in
> it:
> SELECT Customers.CustomerID, ContactName, CompanyName,
> Orders.CustomerID, OrderDate
> FROM Customers, Orders
> WHERE Customers.CustomerID = Orders.CustomerID
> AND (Customers.CustomerID = N'ALFKI'
> OR Customers.CustomerID = N'XYZAA')
> ORDER BY Customers.CustomerID
> FOR XML AUTO
> GO
> This is what it outputs in the query analyzer:
> <Customers CustomerID="ALFKI" ContactName="Maria Anders"
> CompanyName="Alfreds Futterkiste"><Orders CustomerID="ALFKI"
> OrderDate="1997-08-25T00:00:00"/><Orders CustomerID="ALFKI"
> OrderDate="1997-10-03T00:00:00"/><Orders CustomerID="ALFKI"
> OrderDate="1997-10
> I'm assuming that it's being truncated because of something to do with
> the query analyzer.
> So I tried linking to the SP via an Access DB, and outputting it into
> a text format (well, I've tried a couple of different things). When I
> try the text format, this is what I get:
> --
> | XML_F52E2B61-18A1- |
> --
> | <Customers |
> --
> I just must be missing something about how I can get the information
> the SP retrieves into a plain file that can be used as XML. I know
> this is really basic programming stuff, but I seem to be hitting a
> wall in my head somewhere in how I'm thinking about this.
> So what I'm looking for is an idea of what to use on the front end to
> get the SP I have into a file that can be pushed around places. Access
> doesn't seem to have what I'm looking for (or maybe I'm just not
> looking at the right commands). This is the Access code I'm using to
> create the files in question:
> DoCmd.OutputTo acOutputQuery, "qryXMLOrdersAndCustomers", txt,
> "C:\CFusionMX\wwwroot\TestingXMLStuff\xmlfromnorth wind.xml"
> I know this is a longwinded post, and I know that this is a really
> basic question, but if someone could help point me in the right
> direction, I'd really appreciate it.
> --
> Siobhan Perricone
> Systems Developer
> Vermont Agency of Natural Resources
> (my comments are my own, not my employer's)
|||On Fri, 23 Jul 2004 11:26:08 -0400, "John Kotuby" <jkotuby@.snet.net>
wrote:

>You are correct, there are limitations with Query Analyzer regarding column
>size which causes the result set to be truncated. Go to
>TOOLS/OPTIONS/RESULTS and set the "Maximum characters per column:" to 8192.
>That is the max allowed in QA.
Ok, I've done that. Thanks!

>To get the results into a file go to QUERY and select "Results to File".
>If you get results greater than 8192 chars, you might need to edit the file
>to get rid of unneccesary line breaks that occur at 8192 intervals. However,
>this size should allow you to run most test queries.
What I'm really asking is what do I use to be able to make a file on a
regular basis. I mean, I'm not going to go into query analyzer and
pull this data every day. I was afraid that my question was so low
level that people would think I couldn't possibly be really asking
what I am.
Say you have a pile of data in SQL that you want to shove out into an
XML file that you can then ship off to someone. How do you do that? (I
guess that's how basic a question I'm asking here) I just seem to be
missing something in the documents and books I'm reading. They are all
assuming a level of expertise or experience I just don't have. Maybe
I'm skimming too much.

>"Siobhan Perricone" <siobhan.perricone@.nostatespam.vt.us> wrote in message
>news:j552g0pefgfmvtchbb6tes9ah0vp61cgbo@.4ax.com.. .
>
Siobhan Perricone
Systems Developer
Vermont Agency of Natural Resources
(my comments are my own, not my employer's)
|||Siobhan,
Not sure what you're VB is like but assuming you can read it then the
following would save the results of a stored procedure 'SQL_First' to a
result file
Sub SaveXml()
Dim oCmd As Command
Dim oPrm As Parameter
Dim oDom As IXMLDOMDocument2
Set oDom = New DOMDocument40
Set oCmd = New Command
oCmd.ActiveConnection = "Provider=SQLOLEDB.1;Integrated
Security=SSPI;Persist Security Info=False;Initial Catalog=Northwind;Data
Source=."
oCmd.CommandText = "SQL_First"
oCmd.CommandType = adCmdStoredProc
oCmd.Properties("Output Stream") = oDom
oCmd.Execute , , 1024
oDom.Save "c:\temp\results.xml"
End Sub
{The project would need to reference ADO and msxml4.dll )
Hope that helps some
Graham
"Siobhan Perricone" <siobhan.perricone@.nostatespam.vt.us> wrote in message
news:41m2g0djdtibvtqmpoave9oa7il74vgdae@.4ax.com... [vbcol=seagreen]
> On Fri, 23 Jul 2004 11:26:08 -0400, "John Kotuby" <jkotuby@.snet.net>
> wrote:
column[vbcol=seagreen]
8192.[vbcol=seagreen]
> Ok, I've done that. Thanks!
file[vbcol=seagreen]
However,[vbcol=seagreen]
> What I'm really asking is what do I use to be able to make a file on a
> regular basis. I mean, I'm not going to go into query analyzer and
> pull this data every day. I was afraid that my question was so low
> level that people would think I couldn't possibly be really asking
> what I am.
> Say you have a pile of data in SQL that you want to shove out into an
> XML file that you can then ship off to someone. How do you do that? (I
> guess that's how basic a question I'm asking here) I just seem to be
> missing something in the documents and books I'm reading. They are all
> assuming a level of expertise or experience I just don't have. Maybe
> I'm skimming too much.
>
message
>
> --
> Siobhan Perricone
> Systems Developer
> Vermont Agency of Natural Resources
> (my comments are my own, not my employer's)
|||On Sun, 25 Jul 2004 19:26:39 GMT, "Graham Shaw" <Graham@.somewhere.com>
wrote:
Hi Graham,
Thanks so much for responding.

>Not sure what you're VB is like but assuming you can read it then the
>following would save the results of a stored procedure 'SQL_First' to a
>result file
I'm not great. I came into Db programming from being a super user,
rather than coming at it from actual training in languages. So I'm
trying to work my way through what you've so kindly provided. I'm not
sure I understand everything you've put in here, but I'm able to grok
a fair amount from context.

>Sub SaveXml()
>Dim oCmd As Command
>Dim oPrm As Parameter
>Dim oDom As IXMLDOMDocument2
When I compile this, it pops up an "User-defined type not defined"
error at the last line of this block. I've never done a "user-defined
type" before. Is this something I should set up somewhere?
Or would this part of your post:

>{The project would need to reference ADO and msxml4.dll )
Be the explanation for that? If so, I'm not sure what to do with that
information.
One thing I'm wondering now, is there a command I can put at the end
of my stored procedure in SQL that'll just run it and pop it out to a
file? I have been trying to find such, but I suspect I'm not looking
for the right syntax in books online.
I don't think I really have to have this in an MS Access Db...
*ponder*
Siobhan Perricone
Systems Developer
Vermont Agency of Natural Resources
(my comments are my own, not my employer's)
|||Hi Siobhan
Yes the unknown type is the bit about referencing in VB goto
Project/References on the menu and scroll down to Microsoft XML, v4.0 (if
you only have v3.0 then select that but change the Set oDom = New
DOMDocument40 to Set oDom = New DOMDocument30. For the ADO follow the same
procedure and select "Microsoft ActiveX Data Objects 2.X Library" where X is
prefereably 6 or above although 5 will work with what I gave you.
Another alternative would be to look in BOL for the command line version of
the query analyser iql that can output its results to a file.
G
"Siobhan Perricone" <siobhan.perricone@.nostatespam.vt.us> wrote in message
news:f5cag05sstqbuam2cggtbermgu0urqq8gs@.4ax.com...
> On Sun, 25 Jul 2004 19:26:39 GMT, "Graham Shaw" <Graham@.somewhere.com>
> wrote:
> Hi Graham,
> Thanks so much for responding.
>
> I'm not great. I came into Db programming from being a super user,
> rather than coming at it from actual training in languages. So I'm
> trying to work my way through what you've so kindly provided. I'm not
> sure I understand everything you've put in here, but I'm able to grok
> a fair amount from context.
>
> When I compile this, it pops up an "User-defined type not defined"
> error at the last line of this block. I've never done a "user-defined
> type" before. Is this something I should set up somewhere?
> Or would this part of your post:
>
> Be the explanation for that? If so, I'm not sure what to do with that
> information.
> One thing I'm wondering now, is there a command I can put at the end
> of my stored procedure in SQL that'll just run it and pop it out to a
> file? I have been trying to find such, but I suspect I'm not looking
> for the right syntax in books online.
> I don't think I really have to have this in an MS Access Db...
> *ponder*
> --
> Siobhan Perricone
> Systems Developer
> Vermont Agency of Natural Resources
> (my comments are my own, not my employer's)
|||Sorry iql should be isql
"Graham Shaw" <Graham@.somewhere.com> wrote in message
news:3qbNc.663$C85.648@.newsfe1-gui.ntli.net...
> Hi Siobhan
> Yes the unknown type is the bit about referencing in VB goto
> Project/References on the menu and scroll down to Microsoft XML, v4.0 (if
> you only have v3.0 then select that but change the Set oDom = New
> DOMDocument40 to Set oDom = New DOMDocument30. For the ADO follow the same
> procedure and select "Microsoft ActiveX Data Objects 2.X Library" where X
is
> prefereably 6 or above although 5 will work with what I gave you.
> Another alternative would be to look in BOL for the command line version
of
> the query analyser iql that can output its results to a file.
> G
> "Siobhan Perricone" <siobhan.perricone@.nostatespam.vt.us> wrote in message
> news:f5cag05sstqbuam2cggtbermgu0urqq8gs@.4ax.com...
>
|||On Mon, 26 Jul 2004 17:44:31 GMT, "Graham Shaw" <Graham@.somewhere.com>
wrote:

>Yes the unknown type is the bit about referencing in VB goto
>Project/References on the menu and scroll down to Microsoft XML, v4.0 (if
>you only have v3.0 then select that but change the Set oDom = New
>DOMDocument40 to Set oDom = New DOMDocument30. For the ADO follow the same
>procedure and select "Microsoft ActiveX Data Objects 2.X Library" where X is
>prefereably 6 or above although 5 will work with what I gave you.
Ok, I have those things set, and it's giving me an error I wouldn't
have expected. It says:
Runtime error '-2147467259 (80004005)':
[DBNETLIB] [ConnectionOpen(Connect()).] SQL Server does not exist or
access denied.
I would have thought that since I'm running this from a Db that has a
connection to the server set up it'd run.
I haven't looked for an explantion of the error yet (clicking help
basically says "automation error" but nothing else). But I'm starting
with looking at the ActiveConnection line. Just posting here in case
you know off the top of your head what might cause this.

>Another alternative would be to look in BOL for the command line version of
>the query analyser iql that can output its results to a file.
I've tried looking for this, but I'm embarassed to say that I'm not
certain how to use it. BOL is great about giving details on command
syntax, but not great on methods of applying the commands. *sigh* I
know, I need a class...

>"Siobhan Perricone" <siobhan.perricone@.nostatespam.vt.us> wrote in message
>news:f5cag05sstqbuam2cggtbermgu0urqq8gs@.4ax.com.. .
>
Siobhan Perricone
Systems Developer
Vermont Agency of Natural Resources
(my comments are my own, not my employer's)
|||Hi Siobhan
If you already have a connection object open then just do
set oCmd.ActiveConnection=YourConnectionObjectName
instead of the connection string
as for isql just go into BOL click on the search tab and type 'isql utility'
in the text box and hit "list topics", it should come up ranked about number
9 ;o)
Graham
"Siobhan Perricone" <siobhan.perricone@.nostatespam.vt.us> wrote in message
news:asfcg0hebo7pjs29sdnd3n8u19bv541qde@.4ax.com... [vbcol=seagreen]
> On Mon, 26 Jul 2004 17:44:31 GMT, "Graham Shaw" <Graham@.somewhere.com>
> wrote:
same[vbcol=seagreen]
is[vbcol=seagreen]
> Ok, I have those things set, and it's giving me an error I wouldn't
> have expected. It says:
> Runtime error '-2147467259 (80004005)':
> [DBNETLIB] [ConnectionOpen(Connect()).] SQL Server does not exist or
> access denied.
> I would have thought that since I'm running this from a Db that has a
> connection to the server set up it'd run.
> I haven't looked for an explantion of the error yet (clicking help
> basically says "automation error" but nothing else). But I'm starting
> with looking at the ActiveConnection line. Just posting here in case
> you know off the top of your head what might cause this.
of[vbcol=seagreen]
> I've tried looking for this, but I'm embarassed to say that I'm not
> certain how to use it. BOL is great about giving details on command
> syntax, but not great on methods of applying the commands. *sigh* I
> know, I need a class...
message[vbcol=seagreen]
a
>
> --
> Siobhan Perricone
> Systems Developer
> Vermont Agency of Natural Resources
> (my comments are my own, not my employer's)