Query XML datasource

I have an XML file that I receive from a vendor and would like to report
from this data every morning. Can anyone send me a link where I can do
this? I do not have a schema at this time but I think I can create one if
needed.
Thanks
Scottsee the thread
http://groups.google.com/group/microsoft.public.sqlserver.reportingsvcs/browse_thread/thread/d8d798424508e0e8/c4ceb1e126528133?lnk=raot#c4ceb1e126528133
I think your solution will also depend on
[1] how do you receive the XML file from the vendor?
[2] how do you like the report every morning? (schedule,
auto-delivery?)
Scott M wrote:
> I have an XML file that I receive from a vendor and would like to report
> from this data every morning. Can anyone send me a link where I can do
> this? I do not have a schema at this time but I think I can create one if
> needed.
> Thanks
> Scott|||Will this also work in RS 2000?
<siva.jasthi@.gmail.com> wrote in message
news:1134501012.316584.125040@.g44g2000cwa.googlegroups.com...
> see the thread
> http://groups.google.com/group/microsoft.public.sqlserver.reportingsvcs/browse_thread/thread/d8d798424508e0e8/c4ceb1e126528133?lnk=raot#c4ceb1e126528133
>
> I think your solution will also depend on
> [1] how do you receive the XML file from the vendor?
> [2] how do you like the report every morning? (schedule,
> auto-delivery?)
>
>
> Scott M wrote:
>> I have an XML file that I receive from a vendor and would like to report
>> from this data every morning. Can anyone send me a link where I can do
>> this? I do not have a schema at this time but I think I can create one
>> if
>> needed.
>> Thanks
>> Scott
>|||Hi Scott,
Unfortunately, it will not work in SQL Server 2000 Reporting Services.
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Is there an online tutorial for how to create a data extension for that?
"Michael Cheng [MSFT]" <v-mingqc@.online.microsoft.com> wrote in message
news:YUstdbIAGHA.832@.TK2MSFTNGXA02.phx.gbl...
> Hi Scott,
> Unfortunately, it will not work in SQL Server 2000 Reporting Services.
>
> Sincerely yours,
> Michael Cheng
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> =====================================================> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>|||Hi Scott,
Thanks for the respond.
I believe the following articles might help
Build an XML Data Extension for SQL Server Reporting Services
http://www.devx.com/dbzone/Article/21214
Tutorial: Using XML Data in a Report
http://msdn2.microsoft.com/en-us/library/ms345334(en-US,SQL.90).aspx
Hope this helps.
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||I am not understanding from the MSDN article in this post where do you put
the XML file to connect to for your database.
"Michael Cheng [MSFT]" wrote:
> Hi Scott,
> Thanks for the respond.
> I believe the following articles might help
> Build an XML Data Extension for SQL Server Reporting Services
> http://www.devx.com/dbzone/Article/21214
> Tutorial: Using XML Data in a Report
> http://msdn2.microsoft.com/en-us/library/ms345334(en-US,SQL.90).aspx
> Hope this helps.
>
> Sincerely yours,
> Michael Cheng
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> =====================================================> This posting is provided "AS IS" with no warranties, and confers no rights.
>|||That first article looks like it might be what I need. I'll let you guys
know.
Thanks VERY much!!
Scott
"Michael Cheng [MSFT]" <v-mingqc@.online.microsoft.com> wrote in message
news:ktNF%23pVAGHA.1236@.TK2MSFTNGXA02.phx.gbl...
> Hi Scott,
> Thanks for the respond.
> I believe the following articles might help
> Build an XML Data Extension for SQL Server Reporting Services
> http://www.devx.com/dbzone/Article/21214
> Tutorial: Using XML Data in a Report
> http://msdn2.microsoft.com/en-us/library/ms345334(en-US,SQL.90).aspx
> Hope this helps.
>
> Sincerely yours,
> Michael Cheng
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> =====================================================> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>|||Hi Scott,
You are welcome :) If you have any questions or concerns next time, don't
hesitate to let me know. We are always here to be of assistance!
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================This posting is provided "AS IS" with no warranties, and confers no rights.

query to see unused space in any data file

Id like to see how much of unused space is there for any database data file
as in EM ? How can i do so using TSQL ?1. Use the taskpad view for the database
2. DBCC SHOWFILESTATS
--
Linchi Shea
linchi_shea@.NOSPAMml.com
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:%23j61al7gDHA.3144@.tk2msftngp13.phx.gbl...
> Id like to see how much of unused space is there for any database data
file
> as in EM ? How can i do so using TSQL ?
>|||Create following stored procedure to get the free space
left in the database.
Usage of the foll. procedure would be as
exec proc_free_space 'northwind' --pass db name as
parameter
--script of the procedure.
create proc proc_free_space @.db sysname
as
create table #temp2
(fileid int,filegroup int,totalextents int,usedextents
int,name sysname,filename sysname)
insert into #temp2 exec ('use ' + @.db + ';dbcc
showfilestats')
select 'Data Files :' [SPACE ALLOCATED] , '' [TOTAL
(M)], '' [USED (M)], '' [FREE M)]
union all
select reverse(substring(reverse(filename),1,
charindex('\', reverse(filename)) - 1)) [FILENAME],
str(cast([used (m)]as decimal(10,2)) + cast([free (m)] as
decimal(10,2)),10,2) 'Total (M)', [Used (M)] , [Free (M)]
from (select filename, str(usedextents * cast(64 as decimal
(8,2))/1024, 10,2) 'Used (M)',str((totalextents * cast(64
as decimal(8,2))/1024) - (usedextents * cast(64 as decimal
(8,2))/1024) , 10,2) 'Free (M)'
from
#temp2)a
go
--procedure ends here
To get the free space for each database use system stored
procedure sp_msforeachdb and pass the name of above stored
procedure.
Ex:
EXEC sp_Msforeachdb "proc_free_space '?'"
- Vishal|||Set up a profiler trace to capture what EM is doing and you will see the
TSQL. You might even see some undocumented commands.
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:#j61al7gDHA.3144@.tk2msftngp13.phx.gbl...
> Id like to see how much of unused space is there for any database data
file
> as in EM ? How can i do so using TSQL ?
>

Query to return database file names and locations

Does anyone know a query that will return all the databases (SQL 2005) on a server with their file names and locations?

Thanks

You could always just use the standard sp_helpdb.

EXEC sp_helpdb <dbname>

|||

In SQL SERVER 2005, catalog view sys.master_files exposes this information.

So,

select db_name(database_id) database_name, * from sys.master_files

will return all database file names and locations (except resource database)

query to as400 with join

I have 2 querys which i have to run against as400 one is with just one lib/file name and the other is with two lib/files joined.

the one with only one file/lib is working fine but the the one using a join statement is not working

do i have to change anything in the syntax when iam using join.

select x from lib/file

select y from lib1.file1 inner join lib2.file2

Do you have an "ON" statement in the join? Right now you're returning a cartesian product and you aren't joining on any keys.|||

yes there is an 'ON' statement, group by and order by

select y_id, count(X) from lib1.file1 inner join lib2.file2 on

FILE1.COL1=FILE2.COL1

GROUP BY Y_ID

ORDER BY Y_ID

WHEN I USE iSeries (IBM application) TO RUN THIS QUERY I JUST CHANGE "." TO "/" AND IT WORKS FINE THERE

query timeout expired

Using VB, I am running a bulk insert query from csv file into a newly created table. It works fine on small test files; but when I try it on the production data, I get a "query timeout expired" message and processing ends. The text files contain several hundred thousand lines.

How can I resolve this problem. I have several hundred of these csv files and more coming.

Here's the code:

Dim sSQL As String
sSQL = "BULK INSERT " & TableName & " "
sSQL = sSQL & "FROM '" & DataPath & "' WITH "
sSQL = sSQL & "(FIELDTERMINATOR = ',', ROWTERMINATOR = '\n', FIRSTROW = 2)"

DbConn.Execute sSQL

I commented out the DbConn.Execute sSQL and discovered a different error which is probably causing the timeout. Prior to the bulk insert, I check if the table exists, drop it if it does and then do the bulk insert. So now I'm getting the message:

Invalid object name 'XLDS_ds1'

The XLDS_ds1 is the table name. I was able to create the table originally so how can it be invalid? Here's the SQL code assigned to a string

sSQLExists = "IF OBJECT_ID(N'ImEx.dbo." & TableName & "', N'U') IS NOT NULL"
sSQLExists = sSQLExists & " DROP TABLE ImEx.dbo." & TableName & ";"

|||

Still can't get past the invalid object name. Since the table does not currently exist (apparently it was dropped at some point), I commented out the sSQLExists code and tried executing just the sSQL code in the original post. Still getting query timeout expired.

BTW, I ran the following query in MSSMS and got a success message but the table was NOT dropped. Anybody have a clue as to what is happenning?

IF OBJECT_ID (N'ImEx.dbo.XLDS_ds1', N'U') IS NOT NULL DROP TABLE ImEx.dbo.XLDS_ds1;

Command(s) completed successfully.

query text file as linked server in v. 7.0?

Hi,
Can one query a text file as a linked server in sql 7.0? If so, anyone know which driver/provider string I would use?
thxOriginally posted by manster
Hi,

Can one query a text file as a linked server in sql 7.0? If so, anyone know which driver/provider string I would use?

thx

Did you check out sp_addlimked server? There's a section on text files...

BOL

H. Use the Microsoft OLE DB Provider for Jet to access a text file
This example creates a linked server for directly accessing text files, without linking the files as tables in an Access .mdb file. The provider is Microsoft.Jet.OLEDB.4.0 and the provider string is 'Text'.

The data source is the full pathname of the directory that contains the text files. A schema.ini file, which describes the structure of the text files, must exist in the same directory as the text files. For more information about creating a schema.ini file, refer to Jet Database Engine documentation.

--Create a linked server
EXEC sp_addlinkedserver txtsrv, 'Jet 4.0',
'Microsoft.Jet.OLEDB.4.0',
'c:\data\distqry',
NULL,
'Text'
GO