creating xml file from sql server

i am very new at this xml thing, however, i know how to develop sql. i woul
d
like to write a simple sql statement (using one table) and generate/create a
n
XML FILE that i can save onto my desktop. does anyone have any advice'I have had limited success by doing...
osql -E -S(servername) -d(database name) -H-1 -Q "select * from tablename
for xml auto" -ooutput.xml
But the XML generated is rough. So therefore I posted a similiar question,
amazingly within minutes of yours...
"snickerskid" wrote:

> i am very new at this xml thing, however, i know how to develop sql. i wo
uld
> like to write a simple sql statement (using one table) and generate/create
an
> XML FILE that i can save onto my desktop. does anyone have any advice'|||Write an ADO or ADO.net based client program that opens a connection, sends
a FOR XML query over the command stream object, sets the root property on
the result stream and then pipes the result into a file.
HTH
Michael
"snickerskid" <snickerskid@.discussions.microsoft.com> wrote in message
news:10A92994-301F-4AF2-80DF-1F8E521F7758@.microsoft.com...
>i am very new at this xml thing, however, i know how to develop sql. i
>would
> like to write a simple sql statement (using one table) and generate/create
> an
> XML FILE that i can save onto my desktop. does anyone have any advice'|||See "Retrieving and Writing XML Data" in Sql Server books online.
Also, www.sqlxml.org has some good information and examples.
Andrew Conrad
Microsoft Corp
http://blogs.msdn.com/aconrad|||Try this link...
http://www.PerfectXML.com/Articles/XML/ExportSQLXML.asp
"snickerskid" wrote:

> i am very new at this xml thing, however, i know how to develop sql. i wo
uld
> like to write a simple sql statement (using one table) and generate/create
an
> XML FILE that i can save onto my desktop. does anyone have any advice'|||thanks to www.sqlxml.org this example works great:
<%
Response.ContentType = "text/xml"
Dim oCmd, sSQL
sSQL = "<root><sql:query xmlns:sql='urn:schemas-microsoft-com:xml-sql'>" & _
"select * from table for xml auto, elements</sql:query></root>"
Set oCmd = Server.CreateObject("ADODB.Command")
oCmd.ActiveConnection = "all my connection parameters"
oCmd.CommandText = sSQL
oCmd.Dialect = "{5D531CB2-E6Ed-11D2-B252-00C04F681B71}"
oCmd.Properties("Output Stream") = Response
oCmd.Execute , , 1024
Set oCmd = Nothing
%>
so once i have done this...how do you pipe the results into a file? thanks
for your patients and help. also, this takes forever to run because there
are about 50,000 records being returned...do you have any ideas for speedin
g
it up?
"Michael Rys [MSFT]" wrote:

> Write an ADO or ADO.net based client program that opens a connection, send
s
> a FOR XML query over the command stream object, sets the root property on
> the result stream and then pipes the result into a file.
> HTH
> Michael
> "snickerskid" <snickerskid@.discussions.microsoft.com> wrote in message
> news:10A92994-301F-4AF2-80DF-1F8E521F7758@.microsoft.com...
>
>|||thanks...that helped a lot...now, i just need to see some examples that pipe
the results to file. got anything up your sleeve? =)
"Chris" wrote:
> Try this link...
> http://www.PerfectXML.com/Articles/XML/ExportSQLXML.asp
> "snickerskid" wrote:
>|||Here are some examples:
http://www.sqlteam.com/Forums/topic...D=5&CAT_ID=3&To
pic_Title=SQL+Server+2000+XML&Forum_Title=Developer
http://www.sqlxml.org/faqs.aspx?faq=29
Andrew Conrad
Microsoft Corp

creating xml file from sql server

i am very new at this xml thing, however, i know how to develop sql. i would
like to write a simple sql statement (using one table) and generate/create an
XML FILE that i can save onto my desktop. does anyone have any advice?
I have had limited success by doing...
osql -E -S(servername) -d(database name) -H-1 -Q "select * from tablename
for xml auto" -ooutput.xml
But the XML generated is rough. So therefore I posted a similiar question,
amazingly within minutes of yours...
"snickerskid" wrote:

> i am very new at this xml thing, however, i know how to develop sql. i would
> like to write a simple sql statement (using one table) and generate/create an
> XML FILE that i can save onto my desktop. does anyone have any advice?
|||Write an ADO or ADO.net based client program that opens a connection, sends
a FOR XML query over the command stream object, sets the root property on
the result stream and then pipes the result into a file.
HTH
Michael
"snickerskid" <snickerskid@.discussions.microsoft.com> wrote in message
news:10A92994-301F-4AF2-80DF-1F8E521F7758@.microsoft.com...
>i am very new at this xml thing, however, i know how to develop sql. i
>would
> like to write a simple sql statement (using one table) and generate/create
> an
> XML FILE that i can save onto my desktop. does anyone have any advice?
|||See "Retrieving and Writing XML Data" in Sql Server books online.
Also, www.sqlxml.org has some good information and examples.
Andrew Conrad
Microsoft Corp
http://blogs.msdn.com/aconrad
|||Try this link...
http://www.PerfectXML.com/Articles/XML/ExportSQLXML.asp
"snickerskid" wrote:

> i am very new at this xml thing, however, i know how to develop sql. i would
> like to write a simple sql statement (using one table) and generate/create an
> XML FILE that i can save onto my desktop. does anyone have any advice?
|||thanks to www.sqlxml.org this example works great:
<%
Response.ContentType = "text/xml"
Dim oCmd, sSQL
sSQL = "<root><sql:query xmlns:sql='urn:schemas-microsoft-com:xml-sql'>" & _
"select * from table for xml auto, elements</sql:query></root>"
Set oCmd = Server.CreateObject("ADODB.Command")
oCmd.ActiveConnection = "all my connection parameters"
oCmd.CommandText = sSQL
oCmd.Dialect = "{5D531CB2-E6Ed-11D2-B252-00C04F681B71}"
oCmd.Properties("Output Stream") = Response
oCmd.Execute , , 1024
Set oCmd = Nothing
%>
so once i have done this...how do you pipe the results into a file? thanks
for your patients and help. also, this takes forever to run because there
are about 50,000 records being returned...do you have any ideas for speeding
it up?
"Michael Rys [MSFT]" wrote:

> Write an ADO or ADO.net based client program that opens a connection, sends
> a FOR XML query over the command stream object, sets the root property on
> the result stream and then pipes the result into a file.
> HTH
> Michael
> "snickerskid" <snickerskid@.discussions.microsoft.com> wrote in message
> news:10A92994-301F-4AF2-80DF-1F8E521F7758@.microsoft.com...
>
>
|||thanks...that helped a lot...now, i just need to see some examples that pipe
the results to file. got anything up your sleeve? =)
"Chris" wrote:
[vbcol=seagreen]
> Try this link...
> http://www.PerfectXML.com/Articles/XML/ExportSQLXML.asp
> "snickerskid" wrote:
|||Here are some examples:
http://www.sqlteam.com/Forums/topic...=5&CAT_ID=3&To
pic_Title=SQL+Server+2000+XML&Forum_Title=Develope r
http://www.sqlxml.org/faqs.aspx?faq=29
Andrew Conrad
Microsoft Corp

Creating View

Dear Sender,
I know that this is b'coz of GO statement. but GO is not a
T-SQL command and it works only inside Query analyser. if
the same string i execute through VB code by using
connection.execute, it doesn't recognise GO so i removed
it. It works for creating tables if i remove GO and
execute through VB-code. now i have to run this
query "CREATE VIEW" from my vb code and at one go as i do
for tables, i want to create all the views. ur suggestions
are welcome.
Billi98
You cannot combine CREATE VIEW with any other command in the same batch ("execution"). So you need to loop the
input file, and each time you find a GO, execute what you have in the buffer.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"billi98" <anonymous@.discussions.microsoft.com> wrote in message
news:49f701c42c33$be5f7e20$a601280a@.phx.gbl...
> Dear Sender,
> I know that this is b'coz of GO statement. but GO is not a
> T-SQL command and it works only inside Query analyser. if
> the same string i execute through VB code by using
> connection.execute, it doesn't recognise GO so i removed
> it. It works for creating tables if i remove GO and
> execute through VB-code. now i have to run this
> query "CREATE VIEW" from my vb code and at one go as i do
> for tables, i want to create all the views. ur suggestions
> are welcome.
> Billi98
>

Creating View

Dear Sender,
I know that this is b'coz of GO statement. but GO is not a
T-SQL command and it works only inside Query analyser. if
the same string i execute through VB code by using
connection.execute, it doesn't recognise GO so i removed
it. It works for creating tables if i remove GO and
execute through VB-code. now i have to run this
query "CREATE VIEW" from my vb code and at one go as i do
for tables, i want to create all the views. ur suggestions
are welcome.
Billi98Hello,
The GO statement only works through osql and isql (or in
this case Query Analyser).
All it does is signal the end of Transact-SQL statements,
including creating views SP or tables.
To be honest I am not too sure what your asking, you do
not actually need it when you are creating a view, but its
a 'nice to have' is you have a script you need running
though QA.
J
>--Original Message--
>Dear Sender,
>I know that this is b'coz of GO statement. but GO is not
a
>T-SQL command and it works only inside Query analyser. if
>the same string i execute through VB code by using
>connection.execute, it doesn't recognise GO so i removed
>it. It works for creating tables if i remove GO and
>execute through VB-code. now i have to run this
>query "CREATE VIEW" from my vb code and at one go as i do
>for tables, i want to create all the views. ur
suggestions
>are welcome.
>Billi98
>
>.
>|||You cannot combine CREATE VIEW with any other command in the same batch ("execution"). So you need to loop the
input file, and each time you find a GO, execute what you have in the buffer.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"billi98" <anonymous@.discussions.microsoft.com> wrote in message
news:49f701c42c33$be5f7e20$a601280a@.phx.gbl...
> Dear Sender,
> I know that this is b'coz of GO statement. but GO is not a
> T-SQL command and it works only inside Query analyser. if
> the same string i execute through VB code by using
> connection.execute, it doesn't recognise GO so i removed
> it. It works for creating tables if i remove GO and
> execute through VB-code. now i have to run this
> query "CREATE VIEW" from my vb code and at one go as i do
> for tables, i want to create all the views. ur suggestions
> are welcome.
> Billi98
>

Creating View

Dear Sender,
I know that this is b'coz of GO statement. but GO is not a
T-SQL command and it works only inside Query analyser. if
the same string i execute through VB code by using
connection.execute, it doesn't recognise GO so i removed
it. It works for creating tables if i remove GO and
execute through VB-code. now i have to run this
query "CREATE VIEW" from my vb code and at one go as i do
for tables, i want to create all the views. ur suggestions
are welcome.
Billi98You cannot combine CREATE VIEW with any other command in the same batch ("ex
ecution"). So you need to loop the
input file, and each time you find a GO, execute what you have in the buffer
.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"billi98" <anonymous@.discussions.microsoft.com> wrote in message
news:49f701c42c33$be5f7e20$a601280a@.phx.gbl...
> Dear Sender,
> I know that this is b'coz of GO statement. but GO is not a
> T-SQL command and it works only inside Query analyser. if
> the same string i execute through VB code by using
> connection.execute, it doesn't recognise GO so i removed
> it. It works for creating tables if i remove GO and
> execute through VB-code. now i have to run this
> query "CREATE VIEW" from my vb code and at one go as i do
> for tables, i want to create all the views. ur suggestions
> are welcome.
> Billi98
>

Creating Temporary within Stored Procedure

I'm creating a temporary table within my stored procedure should I add a
drop temporary table statement at the end of my stored procedure so that if
stored procedure fails the temporary table will be dropped?
Thanks
No need to; SQL Server will automatically drop the temporary table when the
procedure returns, regardless of whether it was successful or not.
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:AB48F052-B137-4F70-A124-2AFF800FB984@.microsoft.com...
> I'm creating a temporary table within my stored procedure should I add a
> drop temporary table statement at the end of my stored procedure so that
if
> stored procedure fails the temporary table will be dropped?
> Thanks

Creating Temporary within Stored Procedure

I'm creating a temporary table within my stored procedure should I add a
drop temporary table statement at the end of my stored procedure so that if
stored procedure fails the temporary table will be dropped?
ThanksNo need to; SQL Server will automatically drop the temporary table when the
procedure returns, regardless of whether it was successful or not.
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:AB48F052-B137-4F70-A124-2AFF800FB984@.microsoft.com...
> I'm creating a temporary table within my stored procedure should I add a
> drop temporary table statement at the end of my stored procedure so that
if
> stored procedure fails the temporary table will be dropped?
> Thanks

Creating Temporary within Stored Procedure

I'm creating a temporary table within my stored procedure should I add a
drop temporary table statement at the end of my stored procedure so that if
stored procedure fails the temporary table will be dropped?
ThanksNo need to; SQL Server will automatically drop the temporary table when the
procedure returns, regardless of whether it was successful or not.
--
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:AB48F052-B137-4F70-A124-2AFF800FB984@.microsoft.com...
> I'm creating a temporary table within my stored procedure should I add a
> drop temporary table statement at the end of my stored procedure so that
if
> stored procedure fails the temporary table will be dropped?
> Thankssql

Creating SQL statements programmatically from listbox (ADVANCED)

I am creating a page that creates a report based on a dynamically created SQL statement which is created by user input.

Everything is good except for the WHERE section, which is created from values in a list box.

For Example:
lstCriteria.items(1).value = "COMPANY = 'foo'"
lstCriteria.items(2).value = "DAY= 2"

I build my SQL statement with these values like so:
SELECT * FROM POO WHERE COMPANY = 'foo' AND DAY = 2

The problem I am having is when there are multiple values of the same type in the list box. Say:
lstCriteria.items(1).value = "COMPANY = 'foo'"
lstCriteria.items(2).value = "DAY= 2"
lstCriteria.items(1).value = "COMPANY = 'moo'"

My employer wants this to be valid, but I am having a tough time coming up with a solution.

I know that my SQL statement needs to now read:
SELECT * FROM POO WHERE COMPANY = 'foo' AND DAY = 2 OR COMPANY = 'poo' AND DAY = 2

I have code set up to read the values of each list box item up to the "=". And I know that I need to compair this value with the others in the list box...but I am not running into any good solutions.

Any HELP?How about OR like values together?

SELECT * FROM POO WHERE (COMPANY = 'foo' OR COMPANY = 'poo') AND DAY = 2

|||Yes, that would work. But I am looking more at how to extract this data from the listbox to a usable format. I am working on setting the first value (the left(N) characters of the listbox item, which is also a column name) in an array, then inserting the secondvalue, checking if it is in the array, if it is, adding it to the array. If it is not, then adding it to a new array. This way I could loop thought the arrays and create my where statement...but It's just a thought on a whiteboard now.

Any Other suggestions?

creating sql statement "where" at the report server

bs"d
While creating the report I looked for a way of filtering the data by
"WHERE" to compare two attributes such as ( customers.name = friends.name )
Is there a way to do it by the gui setup?
--
daiOn Jun 28, 10:00 am, dai <daikad...@.gmail.commm> wrote:
> bs"d
> While creating the report I looked for a way of filtering the data by
> "WHERE" to compare two attributes such as ( customers.name = friends.name )
> Is there a way to do it by the gui setup?
> --
> dai
If you have at least one group and either a table or matrix control,
you can try modifying the filter properties via: right-click the table/
matrix control -> select Properties -> Groups tab -> Edit... button ->
Filters tab. Then add an expression similar to: Fields!
CustomersName.Value = Fields!FriendsName.Value -or- possibly have the
Friends.Name as part of a hidden parameter set to a default and use:
Fields!CustomersName.Value = Parameters!FriendsName.Value
Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant

creating SQL statement

Alright, so let me explain the details first.

I have two tables. One is the default aspnet_users table that themembership class builds. that has GUID, username, lowereduser, and such.

then I have another table called "UserSkills". That stores the GUID of the member, then the skills they have. so in that table i have. userID as GUID, then about 12 languages in 'bit' format.. (thats becuase in the webpage when they fill out there profile, all these are checkboxes. Basically all of the info is here http://www.listofcoders.com/profile.aspx?name=fenixsn. so there are a couple of bit fields, 1 text, and couple of varchars.

anways, so i wanna build a powerful search thingy. where the users have the option to search a user that only does for ex say php, asp, asp.net. and is from location "Canada". ok so when they fill out the info, I want my SQL statement to do the following

search the userskills table for the required fields. there might be more then 1 person that has the same profile, but different GUID. and then maybe using "Join" or another sql statement, grab there username, and last activity date from the users table that memberhship createes.

so in short, how do i make a dynamic sql statement.

anyone can help me out here?|||anyone help me pleas|||

Hi masfenix,

If you're trying to search according to the user input, you will need to use a WHERE clause after SELECT statement.

SELECT * FROM Table1 WHERECountry=@.Country ANDSkill=@.Skill

If the skill is not in the same table, you can use a JOIN

SELECT *,Skill.SkillName FROM Table1 LEFT OUTER JOIN Skill ON Table1.SkillID = Skill.SkillID WHERE .....

In your page code, pass the criterias through parameters and the expected result will be returned.

|||

Hi, I wanna make a SPROC out of this.

how do I write the select statement then RETURN THE DATA (there could be more then 1 row returend).

and how do i read that data and put it in a gridview?

Creating select statement with seperate columns for different valu

I've been trying to figure out a select statement that would list a seperate
column with the count of a particular value for each value. Basically if I
have a table like this:
number value
-- --
10 good
10 bad
10 bad
12 good
14 bad
16 better
and I want to return all numbers with good or bad values and the totals for
those values, like this:
number good bad
-- -- --
10 1 2
12 1 0
14 0 1
How would I create the query?The best way to query this would be to use:
select num, count(value), value from checkcount
group by num, value
your result would be:
10 2 bad
14 1 bad
16 1 better
10 1 good
12 1 good
then you would want to create a user interface to format your result shown
in your example.
However, if you wanted SQL to bring your result back formatted as your
example, then you would want to inner join your table. hopefully, it is not
a large table. The following query would bring back your desired formatting:
select checkcount.num, isnull( thegood.good ,0) good, isnull( thebad.bad ,0)
bad from checkcount
left join (select num, count(value)as good from checkcount
where value = 'good'
group by num) theGood
on checkcount.num = thegood.num
left join (select num, count(value)as bad from checkcount
where value = 'bad'
group by num) thebad
on checkcount.num = thebad.num
where value in ('good','bad')
group by checkcount.num,thegood.good,thebad.bad
Thanks Kllyj64
"David Tilman" wrote:

> I've been trying to figure out a select statement that would list a sepera
te
> column with the count of a particular value for each value. Basically if I
> have a table like this:
> number value
> -- --
> 10 good
> 10 bad
> 10 bad
> 12 good
> 14 bad
> 16 better
> and I want to return all numbers with good or bad values and the totals fo
r
> those values, like this:
> number good bad
> -- -- --
> 10 1 2
> 12 1 0
> 14 0 1
> How would I create the query?|||If you're lucky enough to have SQL Server 2005, try the new PIVOT operator:
-- DROP TABLE #tmp
CREATE TABLE #tmp ( number INT, xvalue VARCHAR(10) )
SET NOCOUNT ON
INSERT INTO #tmp VALUES ( 10, 'good' )
INSERT INTO #tmp VALUES ( 10, 'bad' )
INSERT INTO #tmp VALUES ( 10, 'bad' )
INSERT INTO #tmp VALUES ( 12, 'good' )
INSERT INTO #tmp VALUES ( 14, 'bad' )
INSERT INTO #tmp VALUES ( 16, 'better' )
SET NOCOUNT OFF
SELECT *
FROM #tmp AS t
PIVOT
(
COUNT(xvalue) FOR xvalue In ( [good], [bad], [better] )
) AS x
That is my first PIVOT query! That's going to be useful!
Let me know how you get on.
Damien
"David Tilman" wrote:

> I've been trying to figure out a select statement that would list a sepera
te
> column with the count of a particular value for each value. Basically if I
> have a table like this:
> number value
> -- --
> 10 good
> 10 bad
> 10 bad
> 12 good
> 14 bad
> 16 better
> and I want to return all numbers with good or bad values and the totals fo
r
> those values, like this:
> number good bad
> -- -- --
> 10 1 2
> 12 1 0
> 14 0 1
> How would I create the query?|||Try,
select
number,
sum(case when value = 'good' then 1 else 0 end) as good,
sum(case when value = 'bad' then 1 else 0 end) as bad
from
t1
group by
number
go
How to rotate a table in SQL Server
http://support.microsoft.com/defaul...574&Product=sql
For SQL Server 2005, see PIVOT operator.
AMB
"David Tilman" wrote:

> I've been trying to figure out a select statement that would list a sepera
te
> column with the count of a particular value for each value. Basically if I
> have a table like this:
> number value
> -- --
> 10 good
> 10 bad
> 10 bad
> 12 good
> 14 bad
> 16 better
> and I want to return all numbers with good or bad values and the totals fo
r
> those values, like this:
> number good bad
> -- -- --
> 10 1 2
> 12 1 0
> 14 0 1
> How would I create the query?

Creating ODBC link within SELECT statement

Is this possible. To explain myself, here is my situation. I created an
Excel Spreadsheet that uses an ODBC link to a SQL database. In order for
anyone to use this file on their PC, that same ODBC link must be setup. Is
their some code I can use within a Select Statement that will allow me to
bypass having to create an ODBC on everyone's PC?
Thanks.
Hi Preacher Man,
You've cross-posted this to a bunch of newsgroups, some of which, for
example microsoft.public.fox.vfp.queries-sql, don't even apply to your
question. What you haven't done is post to a relevant Excel newsgroup. Try
posting this question (and your other one) there.
Someone will probably tell you that you can use a connection string or a
DSN-less connection as described here:
http://support.microsoft.com/kb/q165866/ . (GoogleGroups helped me find
that.)
Cindy Winegarden MCSD, Microsoft Visual FoxPro MVP
cindy_winegarden@.msn.com www.cindywinegarden.com
"Preacher Man" <nospam> wrote in message
news:uFEmV7eJGHA.3936@.TK2MSFTNGP10.phx.gbl...
> Is this possible. To explain myself, here is my situation. I created an
> Excel Spreadsheet that uses an ODBC link to a SQL database. In order for
> anyone to use this file on their PC, that same ODBC link must be setup.
> Is their some code I can use within a Select Statement that will allow me
> to bypass having to create an ODBC on everyone's PC?
> Thanks.
>

Creating ODBC link within SELECT statement

Is this possible. To explain myself, here is my situation. I created an
Excel Spreadsheet that uses an ODBC link to a SQL database. In order for
anyone to use this file on their PC, that same ODBC link must be setup. Is
their some code I can use within a Select Statement that will allow me to
bypass having to create an ODBC on everyone's PC?
Thanks.
Hi Preacher Man,
You've cross-posted this to a bunch of newsgroups, some of which, for
example microsoft.public.fox.vfp.queries-sql, don't even apply to your
question. What you haven't done is post to a relevant Excel newsgroup. Try
posting this question (and your other one) there.
Someone will probably tell you that you can use a connection string or a
DSN-less connection as described here:
http://support.microsoft.com/kb/q165866/ . (GoogleGroups helped me find
that.)
Cindy Winegarden MCSD, Microsoft Visual FoxPro MVP
cindy_winegarden@.msn.com www.cindywinegarden.com
"Preacher Man" <nospam> wrote in message
news:uFEmV7eJGHA.3936@.TK2MSFTNGP10.phx.gbl...
> Is this possible. To explain myself, here is my situation. I created an
> Excel Spreadsheet that uses an ODBC link to a SQL database. In order for
> anyone to use this file on their PC, that same ODBC link must be setup.
> Is their some code I can use within a Select Statement that will allow me
> to bypass having to create an ODBC on everyone's PC?
> Thanks.
>

Creating ODBC link within SELECT statement

Is this possible. To explain myself, here is my situation. I created an
Excel Spreadsheet that uses an ODBC link to a SQL database. In order for
anyone to use this file on their PC, that same ODBC link must be setup. Is
their some code I can use within a Select Statement that will allow me to
bypass having to create an ODBC on everyone's PC?
Thanks.
Hi Preacher Man,
You've cross-posted this to a bunch of newsgroups, some of which, for
example microsoft.public.fox.vfp.queries-sql, don't even apply to your
question. What you haven't done is post to a relevant Excel newsgroup. Try
posting this question (and your other one) there.
Someone will probably tell you that you can use a connection string or a
DSN-less connection as described here:
http://support.microsoft.com/kb/q165866/ . (GoogleGroups helped me find
that.)
Cindy Winegarden MCSD, Microsoft Visual FoxPro MVP
cindy_winegarden@.msn.com www.cindywinegarden.com
"Preacher Man" <nospam> wrote in message
news:uFEmV7eJGHA.3936@.TK2MSFTNGP10.phx.gbl...
> Is this possible. To explain myself, here is my situation. I created an
> Excel Spreadsheet that uses an ODBC link to a SQL database. In order for
> anyone to use this file on their PC, that same ODBC link must be setup.
> Is their some code I can use within a Select Statement that will allow me
> to bypass having to create an ODBC on everyone's PC?
> Thanks.
>

Creating ODBC link within SELECT statement

Is this possible. To explain myself, here is my situation. I created an
Excel Spreadsheet that uses an ODBC link to a SQL database. In order for
anyone to use this file on their PC, that same ODBC link must be setup. Is
their some code I can use within a Select Statement that will allow me to
bypass having to create an ODBC on everyone's PC?
Thanks.Hi Preacher Man,
You've cross-posted this to a bunch of newsgroups, some of which, for
example microsoft.public.fox.vfp.queries-sql, don't even apply to your
question. What you haven't done is post to a relevant Excel newsgroup. Try
posting this question (and your other one) there.
Someone will probably tell you that you can use a connection string or a
DSN-less connection as described here:
http://support.microsoft.com/kb/q165866/ . (GoogleGroups helped me find
that.)
Cindy Winegarden MCSD, Microsoft Visual FoxPro MVP
cindy_winegarden@.msn.com www.cindywinegarden.com
"Preacher Man" <nospam> wrote in message
news:uFEmV7eJGHA.3936@.TK2MSFTNGP10.phx.gbl...
> Is this possible. To explain myself, here is my situation. I created an
> Excel Spreadsheet that uses an ODBC link to a SQL database. In order for
> anyone to use this file on their PC, that same ODBC link must be setup.
> Is their some code I can use within a Select Statement that will allow me
> to bypass having to create an ODBC on everyone's PC?
> Thanks.
>

Creating ODBC link within SELECT statement

Is this possible. To explain myself, here is my situation. I created an
Excel Spreadsheet that uses an ODBC link to a SQL database. In order for
anyone to use this file on their PC, that same ODBC link must be setup. Is
their some code I can use within a Select Statement that will allow me to
bypass having to create an ODBC on everyone's PC?
Thanks.Hi Preacher Man,
You've cross-posted this to a bunch of newsgroups, some of which, for
example microsoft.public.fox.vfp.queries-sql, don't even apply to your
question. What you haven't done is post to a relevant Excel newsgroup. Try
posting this question (and your other one) there.
Someone will probably tell you that you can use a connection string or a
DSN-less connection as described here:
http://support.microsoft.com/kb/q165866/ . (GoogleGroups helped me find
that.)
--
Cindy Winegarden MCSD, Microsoft Visual FoxPro MVP
cindy_winegarden@.msn.com www.cindywinegarden.com
"Preacher Man" <nospam> wrote in message
news:uFEmV7eJGHA.3936@.TK2MSFTNGP10.phx.gbl...
> Is this possible. To explain myself, here is my situation. I created an
> Excel Spreadsheet that uses an ODBC link to a SQL database. In order for
> anyone to use this file on their PC, that same ODBC link must be setup.
> Is their some code I can use within a Select Statement that will allow me
> to bypass having to create an ODBC on everyone's PC?
> Thanks.
>

Creating ODBC link within SELECT statement

Is this possible. To explain myself, here is my situation. I created an
Excel Spreadsheet that uses an ODBC link to a SQL database. In order for
anyone to use this file on their PC, that same ODBC link must be setup. Is
their some code I can use within a Select Statement that will allow me to
bypass having to create an ODBC on everyone's PC?
Thanks.Hi Preacher Man,
You've cross-posted this to a bunch of newsgroups, some of which, for
example microsoft.public.fox.vfp.queries-sql, don't even apply to your
question. What you haven't done is post to a relevant Excel newsgroup. Try
posting this question (and your other one) there.
Someone will probably tell you that you can use a connection string or a
DSN-less connection as described here:
http://support.microsoft.com/kb/q165866/ . (GoogleGroups helped me find
that.)
Cindy Winegarden MCSD, Microsoft Visual FoxPro MVP
cindy_winegarden@.msn.com www.cindywinegarden.com
"Preacher Man" <nospam> wrote in message
news:uFEmV7eJGHA.3936@.TK2MSFTNGP10.phx.gbl...
> Is this possible. To explain myself, here is my situation. I created an
> Excel Spreadsheet that uses an ODBC link to a SQL database. In order for
> anyone to use this file on their PC, that same ODBC link must be setup.
> Is their some code I can use within a Select Statement that will allow me
> to bypass having to create an ODBC on everyone's PC?
> Thanks.
>

Creating multiple Tab Deliminted Exports

Is there a way to read from a table to get values that will be contained within a "where" clause of another SQL statement that can be ready one by one(meaning the same sql statement will be executed mutliple times) that will export a tab delimted file?

Hi Cheston,

In my case, I was able to use Execute SQL Task to output the final result set using the code something like this

SELEC * FROM (SELECT * FROM Table1) Table2 WHERE (Tablestatus <> 'No Change'), but then the question is how I can export the Result Set in a tab delimitted format.

|||

cheston wrote:

Is there a way to read from a table to get values that will be contained within a "where" clause of another SQL statement that can be ready one by one(meaning the same sql statement will be executed mutliple times) that will export a tab delimted file?

You can select your values using an execute SQL task on the control flow. Then using a foreach loop, you would "shred" the variable populated from the execute SQL task. Inside the foreach loop, you would run your data flow. Build a package level scoped variable, set EvaluateAsExpression = True and build an expression that contains your base SQL statement, and then concatenates to it the value of the foreach loop's variable (which would contain just one iteration's WHERE clause as selected from the table). Then, inside the data flow, use an OLE DB Source which uses a variable as the source for the SQL. Pick the variable you just built with the expression.

That's pretty much it in a nutshell.|||

See is that example helps:

http://rafael-salas.blogspot.com/2006/12/import-header-line-tables-into-dynamic_22.html

Creating Mathematical Formulas and Calculations

I've created a sql statement that retrieves number data from various table joins. The number data is then grouped according to various categories. What I need to do is to calculate the average of all the number data in a particular group. How do i go about this? Once calculated, the average needs to be displayed under the data.

For example, the report will list say five numbers (some sets may have more, it depends on how much data is returned based on the query), then under the five numbers, the average is given

2

4

3

6

0

Average: 5

What technique is best? Do I have to calculate the numbers in sql or do I need to configure the report to calculate the average? If so, how? Can someone show me step by step how to do averages for a set of data in the reporting services?

I am completely new to doing reports, I'm just a hobbyist, and I've only used databases to retrieve basic data, but not make manipulations for reports.

Any help will be appreciated.

You can use the "Avg" function in reporting services to do this calculation. Average returns the average of the numbers.