need to create a custom rendering format

Hi,
I am new to reporting service and I need to create a file of custom format except for thoese provided in RS,how to do it ? and I also want to put ths format in the dropdownlist in report manager , is there a way to do it?
Thanks a lot
--
Message posted via http://www.sqlmonster.comOn Sat, 22 Jan 2005 15:16:34 GMT, "angela chen via SQLMonster.com"
<forum@.SQLMonster.com> wrote:
>Hi,
>I am new to reporting service and I need to create a file of custom format except for thoese provided in RS,how to do it ? and I also want to put ths format in the dropdownlist in report manager , is there a way to do it?
>Thanks a lot
Yes, it is possible to create a custom rendering extension for RS.
Looks in the RS Books Online for more info. However, the task is not
trivial and, depending on your format, may require a major programming
effort.

Need to collect serverinfo

Is there a way that I can run some batch file or query to go out to all SQL
Servers and collect some ServerInfo such as Windows Version/Service Pack,
SQL Version/Service Pack,CPU ( Number and Type) ,Server Model(HP,Dell),
Memory , Disk Space, NIC settingg(IP address)etc and stick it into a table
for reporting. The more info the better..
Hello,
The below extended proc will give info regarding OS, SQL Server, Memory and
CPU.
Master..xp_msver
Thanks
Hari
"Hassan" <Hassan@.hotmail.com> wrote in message
news:%23cHiWIOQHHA.3872@.TK2MSFTNGP06.phx.gbl...
> Is there a way that I can run some batch file or query to go out to all
> SQL Servers and collect some ServerInfo such as Windows Version/Service
> Pack, SQL Version/Service Pack,CPU ( Number and Type) ,Server
> Model(HP,Dell), Memory , Disk Space, NIC settingg(IP address)etc and stick
> it into a table for reporting. The more info the better..
>
sql

Need to collect serverinfo

Is there a way that I can run some batch file or query to go out to all SQL
Servers and collect some ServerInfo such as Windows Version/Service Pack,
SQL Version/Service Pack,CPU ( Number and Type) ,Server Model(HP,Dell),
Memory , Disk Space, NIC settingg(IP address)etc and stick it into a table
for reporting. The more info the better..Hello,
The below extended proc will give info regarding OS, SQL Server, Memory and
CPU.
Master..xp_msver
Thanks
Hari
"Hassan" <Hassan@.hotmail.com> wrote in message
news:%23cHiWIOQHHA.3872@.TK2MSFTNGP06.phx.gbl...
> Is there a way that I can run some batch file or query to go out to all
> SQL Servers and collect some ServerInfo such as Windows Version/Service
> Pack, SQL Version/Service Pack,CPU ( Number and Type) ,Server
> Model(HP,Dell), Memory , Disk Space, NIC settingg(IP address)etc and stick
> it into a table for reporting. The more info the better..
>

Need to collect serverinfo

Is there a way that I can run some batch file or query to go out to all SQL
Servers and collect some ServerInfo such as Windows Version/Service Pack,
SQL Version/Service Pack,CPU ( Number and Type) ,Server Model(HP,Dell),
Memory , Disk Space, NIC settingg(IP address)etc and stick it into a table
for reporting. The more info the better..Hello,
The below extended proc will give info regarding OS, SQL Server, Memory and
CPU.
Master..xp_msver
Thanks
Hari
"Hassan" <Hassan@.hotmail.com> wrote in message
news:%23cHiWIOQHHA.3872@.TK2MSFTNGP06.phx.gbl...
> Is there a way that I can run some batch file or query to go out to all
> SQL Servers and collect some ServerInfo such as Windows Version/Service
> Pack, SQL Version/Service Pack,CPU ( Number and Type) ,Server
> Model(HP,Dell), Memory , Disk Space, NIC settingg(IP address)etc and stick
> it into a table for reporting. The more info the better..
>

Need to boot connections to my database

Hi,
We have a batch file that runs every night that drops a database, and restores
it to "baseline" version. But sometimes this fails because users have gone
home and left SQL Suery Analyser connected to the database, which makes the
drop fail.
I need to somehow "kill" all connections to the database - is there a way
to do this in a batch file? I tried stopping and restarting the sqlserver
service on the PC, but that doesn't seem to do it.
Thanks!
Matt
ALTER DATABASE dbname SET SINGLE_USER WITH ROLLBACK IMMEDIATE
Then Restore
THEN back to MULTI_USER
HAve a look at ALTER DATABASE syntax in SQL Books On Line
HTH. Ryan
"matt roberts" <mattwoberts@.gmail.com> wrote in message
news:6ae5d004cb228c82777fb4f8119@.news.microsoft.co m...
> Hi,
> We have a batch file that runs every night that drops a database, and
> restores it to "baseline" version. But sometimes this fails because users
> have gone home and left SQL Suery Analyser connected to the database,
> which makes the drop fail.
> I need to somehow "kill" all connections to the database - is there a way
> to do this in a batch file? I tried stopping and restarting the sqlserver
> service on the PC, but that doesn't seem to do it.
> Thanks!
> Matt
>
|||matt
ALTER DATABASE ... SET SINGLE_USER
go
ALTER DATABASE ... SET MULTI_USER
"matt roberts" <mattwoberts@.gmail.com> wrote in message
news:6ae5d004cb228c82777fb4f8119@.news.microsoft.co m...
> Hi,
> We have a batch file that runs every night that drops a database, and
> restores it to "baseline" version. But sometimes this fails because users
> have gone home and left SQL Suery Analyser connected to the database,
> which makes the drop fail.
> I need to somehow "kill" all connections to the database - is there a way
> to do this in a batch file? I tried stopping and restarting the sqlserver
> service on the PC, but that doesn't seem to do it.
> Thanks!
> Matt
>
|||Thanks to both of you - that works perfectly
Matt.
[vbcol=seagreen]
> matt
> ALTER DATABASE ... SET SINGLE_USER
> go
> ALTER DATABASE ... SET MULTI_USER
> "matt roberts" <mattwoberts@.gmail.com> wrote in message
> news:6ae5d004cb228c82777fb4f8119@.news.microsoft.co m...

Need to boot connections to my database

Hi,
We have a batch file that runs every night that drops a database, and restor
es
it to "baseline" version. But sometimes this fails because users have gone
home and left SQL Suery Analyser connected to the database, which makes the
drop fail.
I need to somehow "kill" all connections to the database - is there a way
to do this in a batch file? I tried stopping and restarting the sqlserver
service on the PC, but that doesn't seem to do it.
Thanks!
MattALTER DATABASE dbname SET SINGLE_USER WITH ROLLBACK IMMEDIATE
Then Restore
THEN back to MULTI_USER
HAve a look at ALTER DATABASE syntax in SQL Books On Line
HTH. Ryan
"matt roberts" <mattwoberts@.gmail.com> wrote in message
news:6ae5d004cb228c82777fb4f8119@.news.microsoft.com...
> Hi,
> We have a batch file that runs every night that drops a database, and
> restores it to "baseline" version. But sometimes this fails because users
> have gone home and left SQL Suery Analyser connected to the database,
> which makes the drop fail.
> I need to somehow "kill" all connections to the database - is there a way
> to do this in a batch file? I tried stopping and restarting the sqlserver
> service on the PC, but that doesn't seem to do it.
> Thanks!
> Matt
>|||matt
ALTER DATABASE ... SET SINGLE_USER
go
ALTER DATABASE ... SET MULTI_USER
"matt roberts" <mattwoberts@.gmail.com> wrote in message
news:6ae5d004cb228c82777fb4f8119@.news.microsoft.com...
> Hi,
> We have a batch file that runs every night that drops a database, and
> restores it to "baseline" version. But sometimes this fails because users
> have gone home and left SQL Suery Analyser connected to the database,
> which makes the drop fail.
> I need to somehow "kill" all connections to the database - is there a way
> to do this in a batch file? I tried stopping and restarting the sqlserver
> service on the PC, but that doesn't seem to do it.
> Thanks!
> Matt
>|||Thanks to both of you - that works perfectly
Matt.
[vbcol=seagreen]
> matt
> ALTER DATABASE ... SET SINGLE_USER
> go
> ALTER DATABASE ... SET MULTI_USER
> "matt roberts" <mattwoberts@.gmail.com> wrote in message
> news:6ae5d004cb228c82777fb4f8119@.news.microsoft.com...
>

Need to boot connections to my database

Hi,
We have a batch file that runs every night that drops a database, and restores
it to "baseline" version. But sometimes this fails because users have gone
home and left SQL Suery Analyser connected to the database, which makes the
drop fail.
I need to somehow "kill" all connections to the database - is there a way
to do this in a batch file? I tried stopping and restarting the sqlserver
service on the PC, but that doesn't seem to do it.
Thanks!
MattALTER DATABASE dbname SET SINGLE_USER WITH ROLLBACK IMMEDIATE
Then Restore
THEN back to MULTI_USER
HAve a look at ALTER DATABASE syntax in SQL Books On Line
--
HTH. Ryan
"matt roberts" <mattwoberts@.gmail.com> wrote in message
news:6ae5d004cb228c82777fb4f8119@.news.microsoft.com...
> Hi,
> We have a batch file that runs every night that drops a database, and
> restores it to "baseline" version. But sometimes this fails because users
> have gone home and left SQL Suery Analyser connected to the database,
> which makes the drop fail.
> I need to somehow "kill" all connections to the database - is there a way
> to do this in a batch file? I tried stopping and restarting the sqlserver
> service on the PC, but that doesn't seem to do it.
> Thanks!
> Matt
>|||matt
ALTER DATABASE ... SET SINGLE_USER
go
ALTER DATABASE ... SET MULTI_USER
"matt roberts" <mattwoberts@.gmail.com> wrote in message
news:6ae5d004cb228c82777fb4f8119@.news.microsoft.com...
> Hi,
> We have a batch file that runs every night that drops a database, and
> restores it to "baseline" version. But sometimes this fails because users
> have gone home and left SQL Suery Analyser connected to the database,
> which makes the drop fail.
> I need to somehow "kill" all connections to the database - is there a way
> to do this in a batch file? I tried stopping and restarting the sqlserver
> service on the PC, but that doesn't seem to do it.
> Thanks!
> Matt
>

Need to alter a DML Trigger

Hi

I'm trying to write a trigger to insert data into an archive file. I added a new trigger using database explorer, wrote the trigger and then saved it. The trigger has an error in it and I need to alter it. Can you tell me how to access the trigger ?

Many thanks

Chris

Hi,

Have u tried looking under the DataBase\Programmability\DataBaseTriggers .. your trigger will show under this node

Also see this video from Microsoft

SQL Server 2005 Video Presentation: Enterprise Data Management, DDL Triggers

Hope this will help

Satya

|||

Satya

Thanks for that. I don't usually use Management Studio Express, I just use VWD express. However, I looked under DataBase\Programmability\DatabaseTriggers and the trigger is not there. I've run the INSERT routine in my application and it is still failing because of the trigger I wrote. So I still have the problem of finding the triggert to correct. (I'm a bit puzzled by this, if you write a stored procedure using VWD express you can subsequently view it and alter it in the VWD express environment).

I would be grateful for any further suggestions.

Chris

|||

Well I've found the trigger under DataBase \ Tables \ 'tableName' \ Triggers.

Without any answers to my previous post, I guess I just have to assume that DML Triggers can be created using VWDexpress but not altered.

Anyway, Management Studio Express is quite cool.

Chris

|||

Hello Chris

If you need more info about trigger then you may read this article

http://www.sqlteam.com/item.asp?ItemID=3850

|||

Mehedi

Thanks for that.

Chris

Need suggestions on text file parsing into database

I have a website, where people upload tab delimited text files of their product inventories, which the site parses and inserts into a database table. Here's the catch: Instead of insisting that each user use a standardized format, each user can upload the file in whatever column order they want, they just have to let the site know through a GUI which column is in which order. And, they may upload columns that if not mapped, will be ignored. Right now, I am doing all of this in code and it runs slow, I was thinking of offloading this to either a stored procedure, ssis, or bulk upload. But, with the varying format of the uploaded text file, I am not sure how I could do that. Any suggestions?

Thanks!

Hi keggdirdle,

As far as I can see, we cannot make the database recognize tab delimited text files automatically.

Whatever technology you use (stored procedure, ssis, or bulk upload), they all depends on fixed format for data.

In this case, I don't think it is possible without your own code.

need some help, making many rows out of one, millions of times

so here's the deal:

i'm getting data in the form of an access db, which may be changed to a txt file due to size. each record has 2 columns at the end, the fields are EffFrom and EffTo, which are of type date and specify the date range for which the rest of the data in the record is valid. Here's the problem, i need to take those ranges and create a row for each day. i.e. if the range is 9/1/2003 to 9/15/2003 i would need 15 rows all with the same data except for a new date field which will replace efffrom and effto. seems like a cursor/loop issue to me, BUT there will eventually be millions of rows that need to be manipulated in this fashion. i started writing a stored procedure that will convert the data, do the necessary lookups [a few of the fields need to be resolved into numerical values before inserting them into the main table], but when i get to the point where i'm pulling the temp table into a cursor then going through row by row and making anywhere from 1 to 365 rows out of each row in the cursor, i'm shaking my head and feeling like there has to be a better way.

Ultimately, i'd like to do it through DTS, but i'm not very crafty with VBScript and opted to go the stored procedure/temp table route.

Here's what the data looks like

location_code1 varchar (will become int through lookup)
location_code2 varchar (will become int through lookup)
deptime varchar (string manipulation being done to add ':')
arrtime varchar (string manipulation being done to add ':')
carriercode varchar (will become int through lookup)
efffrom date
effto date -- described above

does anybody have some quick/dirty code or methods of creating multiple rows from one based on a date range [i know this goes against normalization, but the application requires the data to be this way and it cannot be rewritten]..or some DTS advice?

i'm stumped and in dire need of some inspiration. thanks in advance.See this thread for help on using a table of sequential values to "fill in" dates in a date range:

http://dbforums.com/showthread.php?threadid=914261

Once you create your sequential value table, use it in a query like this:

Select YourFields,
dateadd(dd, SequentialValue, EffFrom) as OnDate
from YourTable, SequentialValues
where SequentialValues.SequentialValue < DateDiff(dd, EffFrom, EffTo)

I didn't check this code for one-off errors or parameter order, but you should be able to get an idea of what you need to do.

Note: this method works well, but if you are going to use it against a table with millions of rows, don't include sequential values in your table greater than the largest datespan you expect, in order to keep the runtime down.

blindman|||awesome...the data is definitely lookin good as far as generating the multiple rows...now to incorporate this into a huge data load.

would you suggest a stored procedure with a variable of type table then a mass insert? or something through DTS? i'll prob try a few different methods and evaluate the speed, but any advice would be appreciated.

thanks!!!!|||I've never liked DTS, and use it mostly for simple data transfers.

A temporary table would probably process fastest, but a permanent table would only need to be created once. Either way probably won't make a large difference.

blindman

need solution for change file path

greeting,
i would like to ask some question about MS SQL 2000 Server.
recall: during the installation, for the setup type, there's one setting for
the destination folder for (1) program files and (2)data files
i need a solution for:
during the installation, if the user pin point the path to default
<<DRIVE>>:\program files\mssql...
now i am having a new partition for the HDD, how can i switch MSSQL ordinary
data files path directory pin point to another directory?
example : from default data files path directory c:\mssql to e:\mssql?
thank youyk.ngYou can set a new database default location by selecting the server
properties, database settings tab and update the data and log directories on
the new database default location section.
Ben Nevarez, MCDBA, OCP
Database Administrator
"zouky" wrote:
> greeting,
> i would like to ask some question about MS SQL 2000 Server.
> recall: during the installation, for the setup type, there's one setting for
> the destination folder for (1) program files and (2)data files
> i need a solution for:
> during the installation, if the user pin point the path to default
> <<DRIVE>>:\program files\mssql...
> now i am having a new partition for the HDD, how can i switch MSSQL ordinary
> data files path directory pin point to another directory?
> example : from default data files path directory c:\mssql to e:\mssql?
> thank youyk.ng
>
>

need solution for change file path

greeting,
i would like to ask some question about MS SQL 2000 Server.
recall: during the installation, for the setup type, there's one setting for
the destination folder for (1) program files and (2)data files
i need a solution for:
during the installation, if the user pin point the path to default
<<DRIVE>>:\program files\mssql...
now i am having a new partition for the HDD, how can i switch MSSQL ordinary
data files path directory pin point to another directory?
example : from default data files path directory c:\mssql to e:\mssql?
thank youyk.ngYou can set a new database default location by selecting the server
properties, database settings tab and update the data and log directories on
the new database default location section.
Ben Nevarez, MCDBA, OCP
Database Administrator
"zouky" wrote:

> greeting,
> i would like to ask some question about MS SQL 2000 Server.
> recall: during the installation, for the setup type, there's one setting f
or
> the destination folder for (1) program files and (2)data files
> i need a solution for:
> during the installation, if the user pin point the path to default
> <<DRIVE>>:\program files\mssql...
> now i am having a new partition for the HDD, how can i switch MSSQL ordina
ry
> data files path directory pin point to another directory?
> example : from default data files path directory c:\mssql to e:\mssql?
> thank youyk.ng
>
>

Need Select Query

Hi to all
I Need a sql Qery for Text files.
Fist I want to know will it be possible to select the All Filelds fromText file...?
I just thoght of like
Select * from MyTextFile.Txt
Please Help me.to my knowledge there isn't a way to do this via Transact SQL. If you have access to the xp_cmdshell stored procedure yo umight try something like:

Code:
------------------------------
master.dbo.xp_cmdshell 'type MyTextFile.Txt'
------------------------------

Of course you will need to fully qualify the path to the text file as SQL server would assume (based on the above) that MyTextFile.Txt is on drive C: of your SQL server.|||I just tried like this
master.dbo.xp_cmdshell 'type C:\Sample.Txt'

I got the message as follows

outpur
The system cannot find the file specified.
NULL

(2 row(s) affected)|||again, xp_cmdshell will look on your sql server for the file NOT your local PC. Try this and see what you get:

code:
------------------------------
master.dbo.xp_cmdshell 'dir'
master.dbo.xp_cmdshell 'type c:\boot.ini'
------------------------------|||Thanks a lot
It is workig fine..

Need security advice on xp_cmdshell, bcp, xml procedure

I have a stored procedure that creates an xml file. It executes a SELECT
statement with the FOR XML clause and then writes the xml file using bcp and
xp_cmdshell. I am calling this procedure by passing it a parameter via ADO.
I have configured the SQL Server Agent with a proxy account so non-SysAdmin
can execute xp_cmdshell.

I'm concerned about giving non-SysAdmins execute on xp_cmdshell. I'm also
concerned about having to maintain the password on my proxy account when
that users' password changes.

Is there a better, more secure way to generate this xml file.

ThanksTerri (terri@.cybernets.com) writes:
> I have a stored procedure that creates an xml file. It executes a SELECT
> statement with the FOR XML clause and then writes the xml file using bcp
> and xp_cmdshell. I am calling this procedure by passing it a parameter
> via ADO. I have configured the SQL Server Agent with a proxy account so
> non-SysAdmin can execute xp_cmdshell.
> I'm concerned about giving non-SysAdmins execute on xp_cmdshell. I'm also
> concerned about having to maintain the password on my proxy account when
> that users' password changes.
> Is there a better, more secure way to generate this xml file.

I'm not really sure what you but it sounds like you do something like:

bcp "SELECT ... FOR XML" queryout outfile.bcp

This is not likely to work very well. ODBC will chop the XML document
after each 2033 character. See KB 275583.

So you would need to get the XML document to the client, and have the
client to create the file and put it where it belongs. Which probably
is better from a security perspective as well.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||> This is not likely to work very well. ODBC will chop the XML document
> after each 2033 character. See KB 275583.
> So you would need to get the XML document to the client, and have the
> client to create the file and put it where it belongs. Which probably
> is better from a security perspective as well.

Thanks Erland,

I'm not using Query Analyzer so I don't think KB 275583 applies to me.

I'm calling the following procedure via ADO

CREATE PROCEDURE procGenerateXML
@.CheckRequestID int
AS
declare @.sql nvarchar(4000)
set @.sql= 'bcp "EXEC TestDB..proctest ' + CONVERT(varchar(8),@.ID) + '"' + '
queryout test.xml -SServer1 -T -c -r -t'
exec master..xp_cmdshell @.sql
GO

The procedure proctest looks like:

CREATE PROCEDURE proctest
@.ID int
AS
SELECT...
FROM...
WHERE...
FOR XML AUTO, ELEMENTS
GO

I then call the procedure like this
Dim cn As New ADODB.Connection
Dim cmd As New ADODB.Command
Dim Param1
Dim ID As Integer
Dim provstr As String
Dim myfrm As Form
Dim dbs As Database
Set dbs = CurrentDb()
ID = Me.ID

cn.Provider = "sqloledb"
provstr = "Server=Server1;Database=TestDB;Trusted_Connection= Yes"
cn.Open provstr

Set cmd.ActiveConnection = cn
cmd.CommandText = "dbo.procGenerateXML"
cmd.CommandType = adCmdStoredProc
Set Param1 = cmd.CreateParameter("Input", adInteger, adParamInput)
cmd.Parameters.Append Param1
Param1.Value = ID
Set rs = cmd.Execute

I'm looking for guidance on the following:

-Can I use this xp_cmdshell method without giving my end users execute
permissions on xp_cmdshell and if not;
-Are there alternatives that don't use xp_cmdshell

Thanks|||Terri (terri@.cybernets.com) writes:
>> This is not likely to work very well. ODBC will chop the XML document
>> after each 2033 character. See KB 275583.
>>
>> So you would need to get the XML document to the client, and have the
>> client to create the file and put it where it belongs. Which probably
>> is better from a security perspective as well.
> Thanks Erland,
> I'm not using Query Analyzer so I don't think KB 275583 applies to me.

But you are using BCP which is implemented in ODBC. So I would definitely
encourage you to test to generate a large XML document, before you
ponder the issues with access to xp_cmdshell.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||If you are already calling this code from ADO, then instead of BCP and
all that you can just directly execute the FOR XML statement, loop
through each 2033 char return results and create the XML file from the
web server.

Erland Sommarskog wrote:
> Terri (terri@.cybernets.com) writes:
> >> This is not likely to work very well. ODBC will chop the XML document
> >> after each 2033 character. See KB 275583.
> >>
> >> So you would need to get the XML document to the client, and have the
> >> client to create the file and put it where it belongs. Which probably
> >> is better from a security perspective as well.
> > Thanks Erland,
> > I'm not using Query Analyzer so I don't think KB 275583 applies to me.
> But you are using BCP which is implemented in ODBC. So I would definitely
> encourage you to test to generate a large XML document, before you
> ponder the issues with access to xp_cmdshell.
>
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx|||pb648174 (google@.webpaul.net) writes:
> If you are already calling this code from ADO, then instead of BCP and
> all that you can just directly execute the FOR XML statement, loop
> through each 2033 char return results and create the XML file from the
> web server.

Actually, if he would do it the simple-minded way, he would not get
2033-characters slices, as he is using SQLOLEDB(*) - he would get a binary
thingie instead.

I have not investigated it, but I believe the proper way to receive FOR
XML in ADO with SQLOLEDB is to use the Stream object.

But apart from that fine detail, I agree with you. Doing this from SQL
Server will be diffictul.

(*) If you use the MSDASQL provider, that is ODBC, then you would have
to as you say. But I would not recommend that.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Thanks to those who replied, I'm going to investigate the ADO stream object.

"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns970CB19EE5537Yazorman@.127.0.0.1...
> pb648174 (google@.webpaul.net) writes:
> > If you are already calling this code from ADO, then instead of BCP and
> > all that you can just directly execute the FOR XML statement, loop
> > through each 2033 char return results and create the XML file from the
> > web server.
> Actually, if he would do it the simple-minded way, he would not get
> 2033-characters slices, as he is using SQLOLEDB(*) - he would get a binary
> thingie instead.
> I have not investigated it, but I believe the proper way to receive FOR
> XML in ADO with SQLOLEDB is to use the Stream object.
> But apart from that fine detail, I agree with you. Doing this from SQL
> Server will be diffictul.
> (*) If you use the MSDASQL provider, that is ODBC, then you would have
> to as you say. But I would not recommend that.|||I did something like this in C# .NET recently via the native SQL OleDB
provider and had to do the looping action. I think that even when I
submitted the Query using Query Analyzer, I could see it coming back as
multiple rows... I could be wrong though.

need rldblib.lib, llibca.lib,dbdecdec

any body have three files below:
rldblib.lib, llibca.lib,dbdecdec.exe
that file use library clipsql,
using to access sql server under clipper aplication.
please help me...
thank before
Supratman - BogorI think these files last shipped on the SQLSVR 7.0 CD. If you can find an old copy of one you should be able to get the files.|||No, there are files with those names, but they won't help for a Clipper application. Clipper applications are 16 bit MS-DOS applications, they will require the 16 bit versions of those files that shpped with SQL 4.21a.

The quickest way to get those files is probably to open a support incident with Microsoft. If you run into any trouble getting the Microsoft support folks to understand what you need, let me know the SR number and I'll call in to explain it for you.

-PatP|||the last file is dbdecdec.exe|||the last file is dbdecdec.exeTrue, but that is for a network topology that has been dead for more than a decade... The company that created/used it has been bought up and dismantled.

If you truly need support for Pathworks, you have real problems because SQL Server hasn't supported that since at least version 6.5, and probably earlier than that. I know that Microsoft doesn't support it, and I seriously doubt that anyone else does.

-PatP

Need Line Breaks in Fixed Width File

Hello,

I have a series of fixed width files, all with the same schema. I need to import the data into a SQL Server table. Each record in the flat file begins with 'D1'. The length of each record (string) is 380. There are cases where the record ends after position 193, and a new record appears in the current string beginning at position 194. So at position 194 'D' appears, and '1' appears at position 195.

In the flat file, I need to insert a line break after position 193 if position 194 = 'D' and if position 195 = '1'. I'm guessing I would do this with a Script Component Transformation. Once the file is edited, then I can bring the data into the table.

What might the script look like? If you have any suggestions, samples, or know of examples on the web you can point me to, please share.

Thank you for your help!

cdun2

Do you really need to insert CR/LF? For what reason?

If all you want is to read this file, use a "Data Task" and use the Flat File connection with a format type of "Fixed Width". This will allow you to define a file import for the file without cr/lf.|||

Thanks for your response. There are cases in the record strings where a new record begins after character 193 instead of at character 1. I need to insert the line break after character 193 so that the records that start at character 194 will correctly start at character 1. Does that make sense?

I suppose I could do a conditional split of some kind, or find some way to separate out the records that start after 193.

I hope that clarifies things.

cdun2

Need Line Breaks in Fixed Width File

Hello,

I have a series of fixed width files, all with the same schema. I need to import the data into a SQL Server table. Each record in the flat file begins with 'D1'. The length of each record (string) is 380. There are cases where the record ends after position 193, and a new record appears in the current string beginning at position 194. So at position 194 'D' appears, and '1' appears at position 195.

In the flat file, I need to insert a line break after position 193 if position 194 = 'D' and if position 195 = '1'. I'm guessing I would do this with a Script Component Transformation. Once the file is edited, then I can bring the data into the table.

What might the script look like? If you have any suggestions, samples, or know of examples on the web you can point me to, please share.

Thank you for your help!

cdun2

Do you really need to insert CR/LF? For what reason?

If all you want is to read this file, use a "Data Task" and use the Flat File connection with a format type of "Fixed Width". This will allow you to define a file import for the file without cr/lf.|||

Thanks for your response. There are cases in the record strings where a new record begins after character 193 instead of at character 1. I need to insert the line break after character 193 so that the records that start at character 194 will correctly start at character 1. Does that make sense?

I suppose I could do a conditional split of some kind, or find some way to separate out the records that start after 193.

I hope that clarifies things.

cdun2

Need help with XML output to file

I'm using SQL Server 2005 / 9.0.3042

I'm not new to sql server, but making my first experience with xml in sql server 2005.

I have a query like this (based on <Table> with neccessary data):

SELECT TAG, PARENT, <columns...>

FROM <Table>

FOR XML EXPLICIT

This query creates a xml file exactly as i need it when i execute it in Management Studio. Well, with one exception. It does not write the <xml...> tag at the beginning of the xml file. But i'm sure i get that in there somewho else. What i need to do now is get that output to a file on disk. And that's where my problem starts.

I tried SQLCMD within Management Studio, but that doesn't accept the ':XML ON' tag and ignores it. the resulting file written is not usable, as it also contains query summary information.

Any direction would be greatly appreciated!

Have you given DTS/SSIS a try. If you are having to do this procedure often, I would go with one of those.

|||

I could not find any way to choose xml as the destination for ssis. Could you give me a start on how to go on?

|||Hi Danny,
As you said that you are facing this problem in sql2005, so can u please tell me the querry by which i can generate a xml file through table in sql2000,
My basic question to you is,
HOW TO GENERATE AN XML FILE USING A SQL QUERY IN QUERY ANALYZER.?
is it possible.|||

Hi Prashant,

There are various ways to create a xml file. In general, u create a select statement and use "FOR XML xxxxx" at the end. Please have a look in Books Online for the possible <xxxxxx>. I would say it depends on purpose u want to achieve, you would choose the appropriate <xxxxxx> method. For each method u need to have its own data base.

I for my case needed to choose the FOR XML EXPLICIT method, as i need to reproduce a specific xml file dynamically, based on certain data. Running the query will create a xml file and give it as the result, so i can open it, and if i need copy the content. If "FOR XML EXPLICIT" is your choice, here is a simple example. I haven't done much on the other <xxxxxx> methods, so i'm sorry i won't be much of help. Method FOR XML EXPLICIT is the most time consuming way, but it gives almost every control to produce exactly the xml file needed.

OK: Here the sample for FOR XML EXPLICIT:

Let's suppose we need a xml file like this:

<Order>

<OrderItem Title="book1" Price="250.00"/>

<OrderItem Title="book2" Price="15.75" Discount=5.00/>

</Order>

For this we need to create a table holding the data to create the xml file using FOR XML EXPLICIT. This table must look like this: In the vertical it will have 1 row for ea line in the xml file. in the horizontal it needs the sum of all possible attributes. Enter a value will print the value in the xml file, enter empty string will print empty string in xml file, enter NULL as value will remove the attribute in the xml file. The table also needs the informatione to tell FOR XML EXPLICIT how the hierachy of the xml file must be. That is done using the TAG and PARENT attribut. The columns in the table must exactly match the names of the elements and attributes in the xml file, plus the level (number between - see blow).

Ok, here is the table:

TAG PARENT [Order!1] [OrderItem!2!Title] [OrderItem!2!Prive] [OrderItem!2!Discount]

-

1 NULL '' NULL NULL NULL

2 1 NULL book1 250.00 NULL

3 1 NULL book2 15.75 5.00

-

This is only a very simple example of FOR XML EXPLICIT, but i hope it makes clear on how it works. I used this way to generate our xml files dynamically. It was much work to build the system, but gives me much flexibility to construct all the various different xml files. Last but not least, it's only useful if the structure of the xml file don't change so often.

Need help with XML output to file

I'm using SQL Server 2005 / 9.0.3042

I'm not new to sql server, but making my first experience with xml in sql server 2005.

I have a query like this (based on <Table> with neccessary data):

SELECT TAG, PARENT, <columns...>

FROM <Table>

FOR XML EXPLICIT

This query creates a xml file exactly as i need it when i execute it in Management Studio. Well, with one exception. It does not write the <xml...> tag at the beginning of the xml file. But i'm sure i get that in there somewho else. What i need to do now is get that output to a file on disk. And that's where my problem starts.

I tried SQLCMD within Management Studio, but that doesn't accept the ':XML ON' tag and ignores it. the resulting file written is not usable, as it also contains query summary information.

Any direction would be greatly appreciated!

Have you given DTS/SSIS a try. If you are having to do this procedure often, I would go with one of those.

|||

I could not find any way to choose xml as the destination for ssis. Could you give me a start on how to go on?

|||Hi Danny,
As you said that you are facing this problem in sql2005, so can u please tell me the querry by which i can generate a xml file through table in sql2000,
My basic question to you is,
HOW TO GENERATE AN XML FILE USING A SQL QUERY IN QUERY ANALYZER.?
is it possible.|||

Hi Prashant,

There are various ways to create a xml file. In general, u create a select statement and use "FOR XML xxxxx" at the end. Please have a look in Books Online for the possible <xxxxxx>. I would say it depends on purpose u want to achieve, you would choose the appropriate <xxxxxx> method. For each method u need to have its own data base.

I for my case needed to choose the FOR XML EXPLICIT method, as i need to reproduce a specific xml file dynamically, based on certain data. Running the query will create a xml file and give it as the result, so i can open it, and if i need copy the content. If "FOR XML EXPLICIT" is your choice, here is a simple example. I haven't done much on the other <xxxxxx> methods, so i'm sorry i won't be much of help. Method FOR XML EXPLICIT is the most time consuming way, but it gives almost every control to produce exactly the xml file needed.

OK: Here the sample for FOR XML EXPLICIT:

Let's suppose we need a xml file like this:

<Order>

<OrderItem Title="book1" Price="250.00"/>

<OrderItem Title="book2" Price="15.75" Discount=5.00/>

</Order>

For this we need to create a table holding the data to create the xml file using FOR XML EXPLICIT. This table must look like this: In the vertical it will have 1 row for ea line in the xml file. in the horizontal it needs the sum of all possible attributes. Enter a value will print the value in the xml file, enter empty string will print empty string in xml file, enter NULL as value will remove the attribute in the xml file. The table also needs the informatione to tell FOR XML EXPLICIT how the hierachy of the xml file must be. That is done using the TAG and PARENT attribut. The columns in the table must exactly match the names of the elements and attributes in the xml file, plus the level (number between - see blow).

Ok, here is the table:

TAG PARENT [Order!1] [OrderItem!2!Title] [OrderItem!2!Prive] [OrderItem!2!Discount]

-

1 NULL '' NULL NULL NULL

2 1 NULL book1 250.00 NULL

3 1 NULL book2 15.75 5.00

-

This is only a very simple example of FOR XML EXPLICIT, but i hope it makes clear on how it works. I used this way to generate our xml files dynamically. It was much work to build the system, but gives me much flexibility to construct all the various different xml files. Last but not least, it's only useful if the structure of the xml file don't change so often.

Need help with XML Bulk Load

I have a source xml file which I think is fairly complex. It s an EDIFACT
D96A INVRPT file.
I just need to bring the data within into a single table
CREATE TABLE InventoryOnHand (
InventoryDate nvarchar(10) DEFAULT (getdate()),
Barcode nvarchar(50),
Quantity float,
Warehouse nvarchar(50)
)
I've been fiddling with creating a schema to read the data, but I keep
getting various errors.
If anyone wants to help, let me know & I'll email you a sample source file
and my attempts at a schema. I'm getting desperate - I've spent so many
hours mucking around I'm at the end of my tether, so any assistance is
greatly appreciated!
cheers
DannyPlease send me your files and I'll take a look.
Regards,
--
Monica Frintu
"dc" wrote:

> I have a source xml file which I think is fairly complex. It s an EDIFACT
> D96A INVRPT file.
> I just need to bring the data within into a single table
> CREATE TABLE InventoryOnHand (
> InventoryDate nvarchar(10) DEFAULT (getdate()),
> Barcode nvarchar(50),
> Quantity float,
> Warehouse nvarchar(50)
> )
> I've been fiddling with creating a schema to read the data, but I keep
> getting various errors.
> If anyone wants to help, let me know & I'll email you a sample source file
> and my attempts at a schema. I'm getting desperate - I've spent so many
> hours mucking around I'm at the end of my tether, so any assistance is
> greatly appreciated!
> cheers
> Danny
>
>|||Monica,
Thanks for your offer. You can you ensure your email address is correct -
I'm getting NDRs.
cheers
Danny
dannyc@.accolade.com.au
"Monica Frintu [MSFT]" <MonicaFrintuMSFT@.discussions.microsoft.com> wrote in
message news:66FD8513-C76B-48B1-80AC-7BD50ED994F1@.microsoft.com...
> Please send me your files and I'll take a look.
> Regards,
> --
> Monica Frintu
>
> "dc" wrote:
>|||Monica,
This might help instead
My table is thus:
CREATE TABLE AA_InventoryOnHand (
InventoryDate nvarchar(10),
Barcode nvarchar(50) ,
Quantity float
)
My sample XML file is long, but copying to notepad and saving as
V3_INVRPT.XML should help someone to diagnose. Go down to the
============== line
<?xml version="1.0" encoding="UTF-8"?>
<EDIFACT_D96A_INVRPT xmlns="http://holoncorp.com/xml/EDIFACT/D96A/INVRPT"
xmlns:v3="http://holoncorp.com/xml/EDIFACT/D96A/INVRPT">
<UNB UNB010_0001_syntaxIndentifier="UNOA" UNB010_0002_syntaxVersion="3"
UNB020_0004_senderIdentification="VISA Sydney"
UNB020_0007_partnerIdentificationCodeQua
lifier="ZZ"
UNB030_0007_partnerIdentificationCodeQua
lifier="ZZ"
UNB030_0010_recipientIdentification="Barilla"
UNB040_0017_dateOfPreparation="070510" UNB040_0019_timeOfPreparation="1017"
UNB050_0020_interchangeControlReference=
"1020"/>
<INVRPT>
<UNH UNH010_0062_referenceNumber="0001" UNH020_0051_controllingAgency="UN"
UNH020_0052_versionNumber="D" UNH020_0054_releaseNumber="96A"
UNH020_0057_associationAssignedCode="EAN005"
UNH020_0065_typeIdentifier="INVRPT"/>
<BGM BGM010_1001_messageName="35" BGM020_1004_messageNumber="1020"
BGM030_1225_messageFunction="9"/>
<DTM DTM010_2005_dateTimePeriodQualifier="366"
DTM010_2379_dateTimePeriodFormatQualifie
r="102"
DTM010_2380_dateTimePeriod="20070510"/>
<GRP2>
<NAD NAD010_3035_partyQualifier="GY" NAD020_3039_partyIdIdentification="VISA
Sydney" NAD020_3055_codeListResponsibleAgency="86"/>
<GRP4>
<CTA CTA010_3139_contactFunctionCode="WH"
CTA020_3412_departmentOrEmployeeName="Jim Vikas"/>
<COM COM010_3148_communicationAddressIdentifi
er=""
COM020_3155_communicationAddressQualifie
r="TE"/>
<COM COM010_3148_communicationAddressIdentifi
er=""
COM020_3155_communicationAddressQualifie
r="EM"/>
</GRP4>
</GRP2>
<GRP2>
<NAD NAD010_3035_partyQualifier="GM"
NAD020_3039_partyIdIdentification="Cantarella"
NAD020_3055_codeListResponsibleAgency="86"/>
</GRP2>
<GRP9>
<LIN LIN030_7140_itemNumber="841158000029"
LIN030_7143_itemNumberTypeCode="EN"/>
<GRP12>
<INV INV040_4503_inventoryBalanceMethodCode="1"/>
<QTY QTY010_6060_quantity="144" QTY010_6063_quantityQualifier="17"
QTY010_6411_measurementUnitCode="EA"/>
<QTY QTY010_6060_quantity="0" QTY010_6063_quantityQualifier="170"
QTY010_6411_measurementUnitCode="EA"/>
<QTY QTY010_6060_quantity="0" QTY010_6063_quantityQualifier="253"
QTY010_6411_measurementUnitCode="EA"/>
</GRP12>
</GRP9>
<GRP9>
<LIN LIN030_7140_itemNumber="841158000043"
LIN030_7143_itemNumberTypeCode="EN"/>
<GRP12>
<INV INV040_4503_inventoryBalanceMethodCode="1"/>
<QTY QTY010_6060_quantity="166" QTY010_6063_quantityQualifier="17"
QTY010_6411_measurementUnitCode="EA"/>
<QTY QTY010_6060_quantity="2" QTY010_6063_quantityQualifier="170"
QTY010_6411_measurementUnitCode="EA"/>
<QTY QTY010_6060_quantity="0" QTY010_6063_quantityQualifier="253"
QTY010_6411_measurementUnitCode="EA"/>
</GRP12>
</GRP9>
<GRP9>
<LIN LIN030_7140_itemNumber="AZZURRA" LIN030_7143_itemNumberTypeCode="EN"/>
<GRP12>
<INV INV040_4503_inventoryBalanceMethodCode="1"/>
<QTY QTY010_6060_quantity="1000" QTY010_6063_quantityQualifier="17"
QTY010_6411_measurementUnitCode="EA"/>
<QTY QTY010_6060_quantity="30" QTY010_6063_quantityQualifier="170"
QTY010_6411_measurementUnitCode="EA"/>
<QTY QTY010_6060_quantity="0" QTY010_6063_quantityQualifier="253"
QTY010_6411_measurementUnitCode="EA"/>
</GRP12>
</GRP9>
<GRP9>
<LIN LIN030_7140_itemNumber="fab" LIN030_7143_itemNumberTypeCode="EN"/>
<GRP12>
<INV INV040_4503_inventoryBalanceMethodCode="1"/>
<QTY QTY010_6060_quantity="10" QTY010_6063_quantityQualifier="17"
QTY010_6411_measurementUnitCode="EA"/>
<QTY QTY010_6060_quantity="1" QTY010_6063_quantityQualifier="170"
QTY010_6411_measurementUnitCode="EA"/>
<QTY QTY010_6060_quantity="0" QTY010_6063_quantityQualifier="253"
QTY010_6411_measurementUnitCode="EA"/>
<GIN GIN010_7405_identityNumberQualifier="BX"
GIN020_7402_identityNumberRange="13"/>
</GRP12>
</GRP9>
<GRP9>
<LIN LIN030_7140_itemNumber="fab" LIN030_7143_itemNumberTypeCode="EN"/>
<GRP12>
<INV INV040_4503_inventoryBalanceMethodCode="1"/>
<QTY QTY010_6060_quantity="100" QTY010_6063_quantityQualifier="17"
QTY010_6411_measurementUnitCode="EA"/>
<QTY QTY010_6060_quantity="20" QTY010_6063_quantityQualifier="170"
QTY010_6411_measurementUnitCode="EA"/>
<QTY QTY010_6060_quantity="0" QTY010_6063_quantityQualifier="253"
QTY010_6411_measurementUnitCode="EA"/>
<GIN GIN010_7405_identityNumberQualifier="BX"
GIN020_7402_identityNumberRange="12"/>
</GRP12>
</GRP9>
<GRP9>
<LIN LIN030_7140_itemNumber="INSTANT" LIN030_7143_itemNumberTypeCode="EN"/>
<GRP12>
<INV INV040_4503_inventoryBalanceMethodCode="1"/>
<QTY QTY010_6060_quantity="1000" QTY010_6063_quantityQualifier="17"
QTY010_6411_measurementUnitCode="EA"/>
<QTY QTY010_6060_quantity="30" QTY010_6063_quantityQualifier="170"
QTY010_6411_measurementUnitCode="EA"/>
<QTY QTY010_6060_quantity="0" QTY010_6063_quantityQualifier="253"
QTY010_6411_measurementUnitCode="EA"/>
</GRP12>
</GRP9>
<GRP9>
<LIN LIN030_7140_itemNumber="KING OSCAR"
LIN030_7143_itemNumberTypeCode="EN"/>
<GRP12>
<INV INV040_4503_inventoryBalanceMethodCode="1"/>
<QTY QTY010_6060_quantity="1000" QTY010_6063_quantityQualifier="17"
QTY010_6411_measurementUnitCode="EA"/>
<QTY QTY010_6060_quantity="40" QTY010_6063_quantityQualifier="170"
QTY010_6411_measurementUnitCode="EA"/>
<QTY QTY010_6060_quantity="0" QTY010_6063_quantityQualifier="253"
QTY010_6411_measurementUnitCode="EA"/>
</GRP12>
</GRP9>
<GRP9>
<LIN LIN030_7140_itemNumber="sard brisling"
LIN030_7143_itemNumberTypeCode="EN"/>
<GRP12>
<INV INV040_4503_inventoryBalanceMethodCode="1"/>
<QTY QTY010_6060_quantity="1000" QTY010_6063_quantityQualifier="17"
QTY010_6411_measurementUnitCode="EA"/>
<QTY QTY010_6060_quantity="30" QTY010_6063_quantityQualifier="170"
QTY010_6411_measurementUnitCode="EA"/>
<QTY QTY010_6060_quantity="0" QTY010_6063_quantityQualifier="253"
QTY010_6411_measurementUnitCode="EA"/>
</GRP12>
</GRP9>
<UNT UNT010_0074_numberOfSegments="54" UNT020_0062_referenceNumber="0001"/>
</INVRPT>
<UNZ UNZ010_0020_interchangeControlReference=
"1020"
UNZ010_0036_interchangeControlCount="1"/>
</EDIFACT_D96A_INVRPT>
=======================
And here is the schema file I've written.
<?xml version="1.0" ?>
<Schema xmlns="urn:schemas-microsoft-com:xml-data"
xmlns:dt="urn:schemas-microsoft-com:xml:datatypes"
xmlns:sql="urn:schemas-microsoft-com:xml-sql" >
<ElementType name="EDIFACT_D96A_INVRPT" sql:is-constant="1">
<element type="UNB" />
<element type="LIN" />
<element type="QTY" />
</ElementType>
<ElementType name="UNB" sql:relation="AA_InventoryOnHand" >
<AttributeType name="UNB040_0017_dateOfPreparation" dt:type="string"
/>
<attribute type="UNB040_0017_dateOfPreparation"
sql:field="InventoryDate" />
</ElementType>
<ElementType name="LIN" sql:relation="AA_InventoryOnHand" >
<AttributeType name="LIN030_7140_itemNumber" dt:type="string" />
<attribute type="LIN030_7140_itemNumber" sql:field="Barcode" />
</ElementType>
<ElementType name="QTY" sql:relation="AA_InventoryOnHand" >
<AttributeType name="QTY010_6060_quantity" dt:type="float" />
<attribute type="QTY010_6060_quantity" sql:field="Quantity" />
</ElementType>
</Schema>
==============================
And here's a vbs file I'm running
Set objBL = CreateObject("SQLXMLBulkLoad.SQLXMLBulkLoad")
objBL.ConnectionString = "provider=SQLOLEDB.1;data
source=DANNYCVM;database=MyDemoDB;uid=sa
;pwd=MysaPassword"
objBL.ErrorLogFile = "c:\error.log"
objBL.Execute "c:\Invmapping.xml", "c:\V3_INVRPT.xml"
Set objBL = Nothing
=============================
The table should be populated thus:
070510 841158000029 144
070510 841158000043 166
070510 841158000043 2
etc
"dc" <dannyc@.accoalde.com.au> wrote in message
news:uns4XWxkHHA.1624@.TK2MSFTNGP06.phx.gbl...
>I have a source xml file which I think is fairly complex. It s an EDIFACT
>D96A INVRPT file.
> I just need to bring the data within into a single table
> CREATE TABLE InventoryOnHand (
> InventoryDate nvarchar(10) DEFAULT (getdate()),
> Barcode nvarchar(50),
> Quantity float,
> Warehouse nvarchar(50)
> )
> I've been fiddling with creating a schema to read the data, but I keep
> getting various errors.
> If anyone wants to help, let me know & I'll email you a sample source file
> and my attempts at a schema. I'm getting desperate - I've spent so many
> hours mucking around I'm at the end of my tether, so any assistance is
> greatly appreciated!
> cheers
> Danny
>