Exporting SQL DB files: asp

Showing posts with label asp. Show all posts

Tuesday, March 27, 2012

Extra nextrecordset for sp_help in ASP

I'm running "sp_help mytable1" from ASP against SQL Server 2000 over ADODB. Other T-SQL commands work fine, including commands giving more than one result set. But, for this very command, the 6th and 7th record sets give:

ADODB.Recordset (0x800A0E78)
Operation is not allowed when the object is closed.

when I try to fetch the data.

I have a loop that, for any returned record sets, displays all data.
If I, instead of looping over all record sets, code one data-displaying loop per record set (i.e. a "manual" variant), the sp_help call works provided that I, before displaying the 6th and 7th record set, code an extra "set rs = rs.nextrecordset". I.e., for those record sets giving the above error, I have to do an extra nextrecordset.
So far, it's only with sp_help tablename this has occurred.

The general loop works fine in VB6 including the two problem recordsets. It's only in ASP that record set no. 6 and 7 are inaccessible without extra Next's.

Any hints?Can you post your asp code ?|||ASP code (the general one) follows below.
Enter sp_help mytable1 in a HTML form calling it.

<html>

<%

REM *************************
REM Subrutiner
REM *************************

REM Print a recordset's field details
sub printfields

FOR EACH FIELD IN rs.FIELDS
response.write("Field name: " & FIELD.NAME)
response.write(" Type: " & LTRIM(cSTR(FIELD.TYPE)))
response.write(" Defined size: " & LTRIM(cSTR(FIELD.DEFINEDSIZE)))
response.write(" Actual size: " & LTRIM(cSTR(FIELD.ACTUALSIZE)))
NEXT

end sub

REM *************************
REM Huvudrutinen
REM *************************

REM *** Create connection ***

dim conn

set conn = createobject("ADODB.Connection.2.6")
conn.provider = "SQLOLEDB"
conn.connectionstring = "data source=myserver1;
initial catalog=mydatabase1;Integrated Security=SSPI;Trusted_Connection=Yes;"

REM *** Open and run ***

conn.open
set rs = conn.execute( request.form("sqlcmd"))

REM *** Output to web client ***

response.write("" & request.form("sqlcmd") & " ")
REM printfields

do until rs is Nothing

do until rs.eof
for each field in rs.fields
response.write(" " & field.name & " = " & rs(field.name))
next
response.write(" ----------")
rs.movenext
loop

set rs = rs.nextrecordset

loop

REM *** Close connection ***

conn.close
set conn = nothing

%>

</html>|||I *think* I've solved it by checking if the next recordset is closed - if so, do an extra next recordset. Code included below. Still don't understand why the recordset get a State value of "closed".

(How do I write an "if object is NOT nothing then..." ?)

<html>
<%

REM *** Create connection ***

dim conn

set conn = createobject("ADODB.Connection.2.6")
conn.provider = "SQLOLEDB"
conn.connectionstring = "data source=myserver1;
initial catalog=mydatabase1;
Integrated Security=SSPI;Trusted_Connection=Yes;"

REM *** Open and run ***

conn.open
set rs = conn.execute( request.form("sqlcmd"))

REM *** Output to web client ***

response.write("" & request.form("sqlcmd") & " ")

REM do until rs.state = adStateClosed
do until rs is Nothing

do until rs.eof
for each field in rs.fields
response.write(" " & field.name & " = " & rs(field.name))
next
response.write(" ----------")
rs.movenext
loop

REM next recordset

set rs = rs.nextrecordset

REM if there is a next recordset, check if it's closed and if so, go to next after this
REM Don't know why it's needed.

if rs is nothing then
response.write("nop")
else

if rs.state = adStateClosed then

set rs = rs.nextrecordset

end if

end if

loop

REM *** Close connection ***

conn.close
set conn = nothing

%>

</html>sql

Externally triggered DTS to import excel data to SQL server

I need to extract data from an excel file to my SQL Server 2000 database. Users used to do this themselves through an ASP script I developed but some data in certain cells are invariably lost, NULL value instead is recorded (according to Microsoft this is the problem w/ using excel as a data source).

To get around this problem I asked my users to send me their excel files so I could import the data manualy using SQL Server's Import Data facility. But, this is not acceptable. They should be able to do this themselves w/o my intervention.

There is already an "upload file to server" facility that they can use. And after uploading I was thinking of using DTS to automatically import the data from excel. But the DTS package is normaly executed based on a set schedule. What I need is for users to upload the excel file to the server, then for them to trigger the DTS package w/o directly accessing the SQL server database.

Is this possible? Can I create a stored procedure that will execute the DTS package? I'm not quite familiar w/ stored procedures although I'm trying to learn more about it right now.

Here's a sample excel data source, info.xls:
Name Age State
John Smith 30 NY
Anne Collins 25 CA
Mike Peterson 22 TX

Destination db and table: dbUser, tblInfo
Fields: tName(nvarchar, 50), iAge(numeric, 3), tState(nvarchar, 2)

Any assistance on this will be highly appreciated. Thanks!You could get a stored procedure to start the scheduled job which is running the DTS package. A basic stored procedure to run the job would be:

CREATE PROCEDURE sp_StartDTS

AS

BEGIN

EXEC msdb..sp_start_job @.job_name = 'The DTS job name'

END

You can also use the job id etc... do a search in the Books Online for sp_start_job and you'll get the syntax. There is also a success/fail return code which you could use in the ASP page|||Originally posted by jasper627
I need to extract data from an excel file to my SQL Server 2000 database. Users used to do this themselves through an ASP script I developed but some data in certain cells are invariably lost, NULL value instead is recorded (according to Microsoft this is the problem w/ using excel as a data source).

To get around this problem I asked my users to send me their excel files so I could import the data manualy using SQL Server's Import Data facility. But, this is not acceptable. They should be able to do this themselves w/o my intervention.

There is already an "upload file to server" facility that they can use. And after uploading I was thinking of using DTS to automatically import the data from excel. But the DTS package is normaly executed based on a set schedule. What I need is for users to upload the excel file to the server, then for them to trigger the DTS package w/o directly accessing the SQL server database.

Is this possible? Can I create a stored procedure that will execute the DTS package? I'm not quite familiar w/ stored procedures although I'm trying to learn more about it right now.

Here's a sample excel data source, info.xls:
Name Age State
John Smith 30 NY
Anne Collins 25 CA
Mike Peterson 22 TX

Destination db and table: dbUser, tblInfo
Fields: tName(nvarchar, 50), iAge(numeric, 3), tState(nvarchar, 2)

Any assistance on this will be highly appreciated. Thanks!

to overcome bad data in the Excel spreadsheet you could import the data into a holding table that will allow nulls or other bad data, then run some SQL over the table identitfying good records by updating a bit field in the table. If the types of data errors are known and can be fixed automatically eg NULL should be 0 then you could fix that either in the DTS package with a VB script or later with SQL.

Monday, March 26, 2012

External DataSet on GoDaddy

Hello,
First, I tried to find the answet to this question, but no luck - so I decided to post it.

When I was creating my applications in asp.net - for the first time I decided to start using external DataSets. I think they are great and work very nice!

But once I pushed the application to GoDaddy, I got an error of this nature:
I was trying to access the exterbal dataSet like this:

Dim productsAdapter As NewNorthwindTableAdapters.ProductsTableAdapter()Dim products as Northwind.ProductsDataTable
...

And got an error saying that it couldn't find this type (but it worked fine on my local machine in visual studio).
So did I miss something that prevented this application working on GoDaddy? or there are some limitations on GoDaddy? ...or something else.

Thank you for looking into this for me.

Valera

I think Godaddy uses SQL 2000, so did you setup your connection strings in your web.config to connect to your Database on the GoDaddy server?

|||

I'm pretty sure it uses SQL Server 2005. And the connection string was setup correctly, becuase I can get access to the data without using external dataSet methods.

If anyone with the access to Godaddy could create a sample page that uses external dataset data access method and prove me worng or right - that would be helpful.

Thank you

|||

You are correct about 2005. When I first signed up in 2005, they were using SQL 2000.

Maybe I am a noob, but you say external dataset, how is that different from a regular dataset?

|||

By external DataSet I mean that in your project you can add a new item - DataSet (just like a web form) that will be stored in App_Code folder. It is very well described here:http://www.asp.net/learn/dataaccess/tutorial01vb.aspx?tabid=63

|||

Oh, I am very fimilar with Scott's tutorials, they are excellent. I would not suggest using 'Strongly typed Dataset'. I didn't realize that is what you meant. Custom entities (A true database class) are far superior for scalability.

In Godaddy, is your ASP.NET runtime set to 2.0?

|||

Yes ser - 2.0 it is :]

|||

Could you take a screenshot of the error and and upload it to imageshack or some site and post it here. (block out sensitive data)

I am assuming you are trying to rebuild Scott's tutorial, correct?

Sunday, February 26, 2012

Express cannot convert different data types automaticly?

I have a SQL Server 2000 database which works like a charm in my asp app. Joining tables by id columns which are of different data types (eg. int in one table and varchar in the other) is no problem with the SQL Server engine, but Native Client refuses to do so and hands me back an error. Even if columns have a slightly different collation (like Slovenian_CI_AS and SQL_Server_1250_CI_AS) SQL Express native client returns an ERROR?!!! Is it possible that Native client cannot resolve this on its own? Must I change the colattion and datatype on all my tables? Please help!!!!

Peca, developer from Serbia

If you have two columns in two tables, and one is, say PK in one table and FK on the other, then they really should be of the same datatype.

If possible, you should make changes where necessary.

I'm not familiar with Slovenian collations, but different collations may me incompatible between eachother, so there's a chance there could be errors when different collations are used at the same time.

/Kenneth

|||Yes Kenneth, you are right, but the same query works with the SQL SERVER 2000 engine and does not work with EXPRESS database engine. And the problem is that PK's cannot be of the same type in this case. "OLD" sql 2000 engine does not complain but the new one does. I just want to know if this is "solid rock rule" or I can change this behaviour and make him accept these querys?|||

Ah, I see... I haven't tried out those circumstances, so I'm sorry that I don't have a definite answer. Perhaps someone else will chime in with some insights.

/Kenneth

|||

All what you need is to specify collation explicitly in your queries while comparing charater data which collattion differ.

See ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/76763ac8-3e0d-4bbb-aa53-f5e7da021daa.htm

|||

Yes, I know that, but thank you anyway... With the collation I can live, but joining tables with different data types as pk's is a different story. If I say - join the table A with pk datatype int and table B with datatype varchar engine should do the following - join the fields that match ( values 1 -int and "1" varchar) and discard the rest (varchar like "aaa").This really works in SQL Server 2000 but not in Express (returns an error). I am developing databases for years now and I've been working on databases with millions of records and hundreds of tables. This database cannot be normalised more than it is...I'll just stick to 2000 and forget about Express...A BIG thanks to all that replied to y question!!!

|||

>>join the table A with pk datatype int and table B with datatype varchar engine should do the following - join the fields that match ( values 1 -int and "1" varchar) and discard the rest (varchar like "aaa").

Joining two tables throug int field and varchar field, MSSQL would try to convert varchar values into integer value and would generate an error as soon as 'aaa' value reached, unless you specify otherwise in your query.

|||

Agreed. That example has never worked in SQL 2000 either.
the conversion will follow the rules for datatype precedence, and the varchar will be converted to int when implicit conversion comes into play.

So, this is not some new behaviour by Express.

Small demo below from SQL Server 2000: (this is the expected behaviour)

create table #a (pk int not null, a varchar(10) not null)
create table #b (pk varchar(10) not null, b varchar(10) not null)
go
insert #a select 1, 'a'
insert #b select 1, 'b'
insert #b select 'aaa', 'b'
go
select *
from #a join #b
on #a.pk = #b.pk
go

pk a pk b
-- - - -
1 a 1 b

Server: Msg 245, Level 16, State 1, Line 1
Syntax error converting the varchar value 'aaa' to a column of data type int.

/Kenneth

|||

Kenneth, did I mention that I have the WHERE clausule? Ok, let's end this now and for all ! These are table definitions and bellow is the query that works in SQL 2000 (I can send screenshots to all that do not believe me) and DOES NOT work with EXPRESS (both your and my example).

Your example a bit rewriten:
create table #a (pk int not null, a varchar(10) not null)
create table #b (pk varchar(10) not null, b varchar(10) not null)
go

insert #a select 1, 'a'
insert #a select 2, 'b'
insert #b select 'aaa', 'be'
insert #b select '1', 'c'
insert #b select '2', 'd'

select * from #a ,#b
where #a.pk = #b.pk and (#b.b='c' or #b.b='g') and #a.pk in (1)
go

My example that does the same

table :KOMP_REF pk:SIFRA varchar(50)
table :VESTI pk:ID int

they are not referenced in any way (there is no explicit reference between them e.g. constraints, referencial integrity etc.)

query VESTI:
select id,headline,text,date from vesti where id=11
result:
11 heder <FONT face="Courier New".. 2005-12-23 11:51:13.107

query KOMP_REF:
select * from komp_ref
result:
...
lentro 7 4 43 11 81 1 1
11 7 4 127 -1 22 1 1
...

values 81 and 22 are of column SIFRA_KOMP so I believe that row with the value 81 should be excluded from the join by the where clausule in the query bellow

query:
select vesti.id from vesti,komp_ref
where vesti.id=komp_ref.sifra and
(komp_ref.sifra_komp=22 or komp_ref.sifra_komp=64 or komp_ref.sifra_komp=65 or
komp_ref.sifra_komp=91 or komp_ref.sifra_komp=92 or komp_ref.sifra_komp=93)
and vesti.id IN (11)

Express error msg:
Conversion failed when converting the varchar value 'lentro ' to data type int.

Database is copied from sql2000 into express (just copied files without any import procedure etc.)without any changes (I just changed the conn string in my ASP app). I understand all you said but WHY does 2000 do not report an error? Maybe that is the right question? Why is OR operator so confusing for Express? Without the or #b.b='g' even your example works in express but when I add it only SQL2000 returns a recordset? Any comments?!?

|||Sorry KeWin, I wrote Kenneth by mistake :)|||

:) No worries, Kenneth is what my mother calls me also.
(sry for the late reply)

Unfortunately I don't have the ability to try this on Express at the moment, but...
What if you tried rewriting the join ANSI style instead of the old legacy syntax.

select vesti.id
from vesti
join komp_ref
on vesti.id = komp_ref.sifra
and komp_ref.sifra_komp in ('22', '64', '65', '91', '92', '93')
and vesti.id = 11

..see if anything changes..?

/Kenneth

|||unfortunately...no... still reporting the same error... But that is to be expected since the exapmle I gave above has a 100% valid SQL syntax and there are no reasons why it would not work... And did I mention that it works with 2000? ;) (less than 2000 times daaaaa) ... If I accidentaly found a bug (hope not because it would be a huuuuuuuuge one) the boys and girls from MS owe me a beer :) thanx 4 all Kewin!

Express cannot convert different data types automaticly?

Peca, developer from Serbia

If you have two columns in two tables, and one is, say PK in one table and FK on the other, then they really should be of the same datatype.

If possible, you should make changes where necessary.

/Kenneth

Ah, I see... I haven't tried out those circumstances, so I'm sorry that I don't have a definite answer. Perhaps someone else will chime in with some insights.

/Kenneth

|||

All what you need is to specify collation explicitly in your queries while comparing charater data which collattion differ.

See ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/76763ac8-3e0d-4bbb-aa53-f5e7da021daa.htm

|||

So, this is not some new behaviour by Express.

Small demo below from SQL Server 2000: (this is the expected behaviour)

pk a pk b
-- - - -
1 a 1 b

Server: Msg 245, Level 16, State 1, Line 1
Syntax error converting the varchar value 'aaa' to a column of data type int.

/Kenneth

|||

Your example a bit rewriten:
create table #a (pk int not null, a varchar(10) not null)
create table #b (pk varchar(10) not null, b varchar(10) not null)
go

insert #a select 1, 'a'
insert #a select 2, 'b'
insert #b select 'aaa', 'be'
insert #b select '1', 'c'
insert #b select '2', 'd'

select * from #a ,#b
where #a.pk = #b.pk and (#b.b='c' or #b.b='g') and #a.pk in (1)
go

My example that does the same

table :KOMP_REF pk:SIFRA varchar(50)
table :VESTI pk:ID int

they are not referenced in any way (there is no explicit reference between them e.g. constraints, referencial integrity etc.)

query VESTI:
select id,headline,text,date from vesti where id=11
result:
11 heder <FONT face="Courier New".. 2005-12-23 11:51:13.107

query KOMP_REF:
select * from komp_ref
result:
...
lentro 7 4 43 11 81 1 1
11 7 4 127 -1 22 1 1
...

values 81 and 22 are of column SIFRA_KOMP so I believe that row with the value 81 should be excluded from the join by the where clausule in the query bellow

Express error msg:
Conversion failed when converting the varchar value 'lentro ' to data type int.

|||Sorry KeWin, I wrote Kenneth by mistake :)|||

:) No worries, Kenneth is what my mother calls me also.
(sry for the late reply)

Unfortunately I don't have the ability to try this on Express at the moment, but...
What if you tried rewriting the join ANSI style instead of the old legacy syntax.

select vesti.id
from vesti
join komp_ref
on vesti.id = komp_ref.sifra
and komp_ref.sifra_komp in ('22', '64', '65', '91', '92', '93')
and vesti.id = 11

..see if anything changes..?

/Kenneth

Friday, February 24, 2012

Express and AspNet_RegSql.exe

Can the Asp Net exe sql script be used to add the aspnetdb database to an Sq
l
Express?
I am doing this for running the App Blocks.
The error that comes back is "can not connect".
Thaks,
MarcCheck to see if you need to specify the instance name for the connection. I
know that when I installed a CTP version of 2005, the "standard" installatio
n
created a non-default instance.
"Marc" wrote:

> Can the Asp Net exe sql script be used to add the aspnetdb database to an
Sql
> Express?
> I am doing this for running the App Blocks.
> The error that comes back is "can not connect".
> Thaks,
> Marc
>

Exposing data

Hi,
I'm about to enter some new ground. I have a background of web development (
ASP) and SQL Server (network environment only). We have a new ASP.NET applic
ation running a SQL DB. This application is to be put out on the net.
Our network admin wants to buy another SQL licence and have the SQL server s
it outside of our network firewall. I'm more inclined to have the SQL server
inside some sort of DMZ (a topic new to me) and open a port for data access
.
Before I take this argument to the network admin I want to arm myself with t
he necessary knowledge. I guess I'm looking for articles/URLs/tutorials whic
h will teach me how to expose a SQL DB on our network to the internet withou
t risking the secuirty of o
ur network or the SQL box itself.
many thanks
JayGenerally for web apps, the exposure works like this:
One port (or maybe two) on the firewall is open for HTTP (80, and perhaps
443 for SSL).
The web server, BEHIND THE FIREWALL, can talk to the database server. Some
admins even go so far as to put a second firewall between the web and
database servers (which I think is generally overkill). No SQL Server ports
should ever be open on the firewall. And no SQL Server should ever be in a
DMZ! That's just asking for trouble...
Anyway, here's a link with some good information about how to secure a web
app using SQL Server:
http://msdn.microsoft.com/library/d...-us/dnnetsec/ht
ml/openhack.asp
"Jay" <Jay@.wdc.com> wrote in message
news:5D4E06B3-1BC0-4A9B-9215-2C20BC72379D@.microsoft.com...
> Hi,
> I'm about to enter some new ground. I have a background of web development
(ASP) and SQL Server (network environment only). We have a new ASP.NET
application running a SQL DB. This application is to be put out on the net.
> Our network admin wants to buy another SQL licence and have the SQL server
sit outside of our network firewall. I'm more inclined to have the SQL
server inside some sort of DMZ (a topic new to me) and open a port for data
access.
> Before I take this argument to the network admin I want to arm myself with
the necessary knowledge. I guess I'm looking for articles/URLs/tutorials
which will teach me how to expose a SQL DB on our network to the internet
without risking the secuirty of our network or the SQL box itself.
> many thanks
> Jay|||This would not be recommended. SQL should be protected by a firewall.
Consider using ISA server 2000 and publishing SQL to the internet.
1433 is one of the highest scanned ports by hackers.
http://www.microsoft.com/technet/pr...n/sp3sec02.mspx
#XSLTsection126121120120
287932 INF: TCP Ports Needed for Communication to SQL Server Through a
Firewall
http://support.microsoft.com/?id=287932
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.|||Thanks Adam,
Priniting it now... will digest it after my first coffee of the morning.
Jay

Expose SQL Meta Data via ASP

Hi,

Apologies if this is better posted in an ASP group, but here goes
anyway ...

Is it possible to work out what parameters a stored procedure expects,
using ASP?

I would like to take the name of a stored procedure, work out what
input parameters it has and build a form based on them in ASP.

Thanks,
MB.Tools (mbrocklehurst@.hotmail.com) writes:
> Apologies if this is better posted in an ASP group, but here goes
> anyway ...
> Is it possible to work out what parameters a stored procedure expects,
> using ASP?
> I would like to take the name of a stored procedure, work out what
> input parameters it has and build a form based on them in ASP.

You can use the .Refresh method for a command object. That will populate
a parameter collection for you.

Now, how you actually access that from ASP, you will have to ask someone
else. I don't know ASP.

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||or you can run a query against INFORMATION_SCHEMA.PARAMETERS

"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns959DF15265D4AYazorman@.127.0.0.1...
> Tools (mbrocklehurst@.hotmail.com) writes:
> > Apologies if this is better posted in an ASP group, but here goes
> > anyway ...
> > Is it possible to work out what parameters a stored procedure expects,
> > using ASP?
> > I would like to take the name of a stored procedure, work out what
> > input parameters it has and build a form based on them in ASP.
> You can use the .Refresh method for a command object. That will populate
> a parameter collection for you.
> Now, how you actually access that from ASP, you will have to ask someone
> else. I don't know ASP.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp

exporting xml

Hello
I'm using asp to perform a query against an MSSQL database
This query gets data dinamically and should write an xml file with the
resulting recordset
XML should be formatted as follow
I've just tryed FOR XML but I'm not able to get the well formatted xml
So:
1. I need help to get well formatted xml like the following
2. I'd like to know the best way to save the resulting xml to a file (in
asp)
<?xml version = '1.0' encoding='iso-8859-1'?>
<ROWSET>
<ROW num="1">
<FIELD1>value..</FIELD1>
<FIELD2>value...</FIELD2>
...
<FIELDN>value...</FIELDN>
</ROW>
<ROW num="2">
<FIELD1>value..</FIELD1>
<FIELD2>value...</FIELD2>
...
<FIELDN>value...</FIELDN>
</ROW>
</ROWSET>
Thanks
Here's one way:
In your ASP code, use the SQLXMLOLEDB provider to return the XML to a
DOMDocument - specifying an appropriate xml root property to make the XML
well-formed. Then use the Save method of the DOCDocument object to save the
file.
Here's an example (it assumes SQLXML 3.0 is installed):
Const DBGUID_SQL = "{C8B522D7-5CF3-11CE-ADE5-00AA0044773D}"
Const adExecuteStream = 1024
Dim conn
Set conn = CreateObject("ADODB.Connection")
conn.Provider = "SQLXMLOLEDB.3.0"
conn.ConnectionString = "DATA PROVIDER=SQLOLEDB;" & _
"SERVER=(local);DATABASE=northwind;INTEGRATED SECURITY=sspi;"
conn.Open
Dim cmd
Set cmd = CreateObject("ADODB.Command")
Set cmd.ActiveConnection = conn
'Set the dialect
cmd.Dialect = DBGUID_SQL
'Create DOMDocument object for results.
Dim xmlDoc
Set xmlDoc= CreateObject("MSXML2.DOMDocument")
'Assign the output stream.
cmd.Properties("Output Stream") = xmlDoc
'Specify the command (you'd need to add code to generate this dynamically -
this is just an example based on your desired output)
cmd.CommandText = "SELECT ProductID FIELD1, ProductName FIELD2 FROM Products
ROW FOR XML AUTO"
'Specify the root tag
cmd.Properties("xml root") = "ROWSET"
'Execute the command returning a stream
cmd.Execute, , adExecuteStream
'Save the XML
xmlDoc.Save "C:\Results.xml"
The only major issue you'll have is getting your "num" attribute. If the
number relates to a field in the data (e.g. a ProductNo column or similar)
then you'll need to use an EXPLICIT mode query to retrieve it as an
attribute when everything else is an element. If it's not a data column, and
just the number of the row in the result set you'll need to either write a
stored procedure to generate the right values for each row (off the top of
my head, you could retrieve the data into a temp table with an IDENTITY
column and then return the data from that) or you could just retrieve the
data and then add the num attribute to each ROW element in the DOMDocument
before saving.
--
Graeme Malcolm
Principal Technologist
Content Master Ltd.
www.contentmaster.com
"Denis" <dzoddi@.mvmnet.com> wrote in message
news:ODlXKMIKFHA.580@.TK2MSFTNGP15.phx.gbl...
Hello
I'm using asp to perform a query against an MSSQL database
This query gets data dinamically and should write an xml file with the
resulting recordset
XML should be formatted as follow
I've just tryed FOR XML but I'm not able to get the well formatted xml
So:
1. I need help to get well formatted xml like the following
2. I'd like to know the best way to save the resulting xml to a file (in
asp)
<?xml version = '1.0' encoding='iso-8859-1'?>
<ROWSET>
<ROW num="1">
<FIELD1>value..</FIELD1>
<FIELD2>value...</FIELD2>
...
<FIELDN>value...</FIELDN>
</ROW>
<ROW num="2">
<FIELD1>value..</FIELD1>
<FIELD2>value...</FIELD2>
...
<FIELDN>value...</FIELDN>
</ROW>
</ROWSET>
Thanks

exporting xml

Hello
I'm using asp to perform a query against an MSSQL database
This query gets data dinamically and should write an xml file with the
resulting recordset
XML should be formatted as follow
I've just tryed FOR XML but I'm not able to get the well formatted xml
So:
1. I need help to get well formatted xml like the following
2. I'd like to know the best way to save the resulting xml to a file (in
asp)
<?xml version = '1.0' encoding='iso-8859-1'?>
<ROWSET>
<ROW num="1">
<FIELD1>value..</FIELD1>
<FIELD2>value...</FIELD2>
..
<FIELDN>value...</FIELDN>
</ROW>
<ROW num="2">
<FIELD1>value..</FIELD1>
<FIELD2>value...</FIELD2>
..
<FIELDN>value...</FIELDN>
</ROW>
</ROWSET>
ThanksHere's one way:
In your ASP code, use the SQLXMLOLEDB provider to return the XML to a
DOMDocument - specifying an appropriate xml root property to make the XML
well-formed. Then use the Save method of the DOCDocument object to save the
file.
Here's an example (it assumes SQLXML 3.0 is installed):
Const DBGUID_SQL = "{C8B522D7-5CF3-11CE-ADE5-00AA0044773D}"
Const adExecuteStream = 1024
Dim conn
Set conn = CreateObject("ADODB.Connection")
conn.Provider = "SQLXMLOLEDB.3.0"
conn.ConnectionString = "DATA PROVIDER=SQLOLEDB;" & _
" SERVER=(local);DATABASE=northwind;INTEGR
ATED SECURITY=sspi;"
conn.Open
Dim cmd
Set cmd = CreateObject("ADODB.Command")
Set cmd.ActiveConnection = conn
'Set the dialect
cmd.Dialect = DBGUID_SQL
'Create DOMDocument object for results.
Dim xmlDoc
Set xmlDoc= CreateObject("MSXML2.DOMDocument")
'Assign the output stream.
cmd.Properties("Output Stream") = xmlDoc
'Specify the command (you'd need to add code to generate this dynamically -
this is just an example based on your desired output)
cmd.CommandText = "SELECT ProductID FIELD1, ProductName FIELD2 FROM Products
ROW FOR XML AUTO"
'Specify the root tag
cmd.Properties("xml root") = "ROWSET"
'Execute the command returning a stream
cmd.Execute, , adExecuteStream
'Save the XML
xmlDoc.Save "C:\Results.xml"
The only major issue you'll have is getting your "num" attribute. If the
number relates to a field in the data (e.g. a ProductNo column or similar)
then you'll need to use an EXPLICIT mode query to retrieve it as an
attribute when everything else is an element. If it's not a data column, and
just the number of the row in the result set you'll need to either write a
stored procedure to generate the right values for each row (off the top of
my head, you could retrieve the data into a temp table with an IDENTITY
column and then return the data from that) or you could just retrieve the
data and then add the num attribute to each ROW element in the DOMDocument
before saving.
--
Graeme Malcolm
Principal Technologist
Content Master Ltd.
www.contentmaster.com
"Denis" <dzoddi@.mvmnet.com> wrote in message
news:ODlXKMIKFHA.580@.TK2MSFTNGP15.phx.gbl...
Hello
I'm using asp to perform a query against an MSSQL database
This query gets data dinamically and should write an xml file with the
resulting recordset
XML should be formatted as follow
I've just tryed FOR XML but I'm not able to get the well formatted xml
So:
1. I need help to get well formatted xml like the following
2. I'd like to know the best way to save the resulting xml to a file (in
asp)
<?xml version = '1.0' encoding='iso-8859-1'?>
<ROWSET>
<ROW num="1">
<FIELD1>value..</FIELD1>
<FIELD2>value...</FIELD2>
..
<FIELDN>value...</FIELDN>
</ROW>
<ROW num="2">
<FIELD1>value..</FIELD1>
<FIELD2>value...</FIELD2>
..
<FIELDN>value...</FIELDN>
</ROW>
</ROWSET>
Thanks

Sunday, February 19, 2012

exporting to pdf without generating report

hi
I am calling a report from asp and if i click the button the report has to
be be stored in a specified location given by the user and report window
should not be generated.
It is very urgent please help me to solve the problem
thanks
terranceHi,
If you are using URL access then try this.
e.g.
http://servername/reportserver?/Sales/YearlySalesSummary&rs:Format=PDF&rs:Command=Render
If you are in the program, you need to change the response.contenttype as
well to "Application/PDF"
Amarnath
"ter" wrote:
> hi
> I am calling a report from asp and if i click the button the report has to
> be be stored in a specified location given by the user and report window
> should not be generated.
> It is very urgent please help me to solve the problem
> thanks
> terrance
>

Exporting to Excel; timeout

I have 8000 rows in the report and trying to export excel in asp.net code, it does not export in the Report manager and it give exception saying “The underlying connection was closed: An unexpected error occurred on a receive.”

Small number of rows are exported correctly. Is there any setting I can change sin RS2005 web service

Follow this KB http://support.microsoft.com/default.aspx/kb/909678

Friday, February 17, 2012

Exporting to Excel

Hi,
I'm facing problems when tried exporting data from SQL Database to Excel Spread sheet using ASP.

The data stored in the database is retrieved and exported to excel format and while exporting, i'm specifying the file name along with the password to the excel file and also the location to be stored. But when i try to save more than one excel file at a time, popup window will appear asking for the confirmation of the save option.

Can any one tell me the cause for the above problem along with the solution if any.

Regards,
SheshAre you running Excel on the server? If that's so, you might run into other problems as well:
http://support.microsoft.com:80/support/kb/articles/Q257/7/57.ASP&NoWebContent=1&NoWebContent=1

I would recommend saving yourself some headaches and use something designed for an ASP/ASP.NET environment. SoftArtisan's "ExcelWriter" is what I use for both streaming spreadsheets to the browser or to the file system. It's no problem populating the spreadsheet from a SQL server datasource. They have evaluation versions online that come with sample code if you're interested in giving it a whirl.http://officewriter.softartisans.com/default.aspx?PageID=37

Good luck!

Tuesday, March 27, 2012

Extra nextrecordset for sp_help in ASP

Externally triggered DTS to import excel data to SQL server

Monday, March 26, 2012

External DataSet on GoDaddy

Sunday, February 26, 2012

Express cannot convert different data types automaticly?

Express cannot convert different data types automaticly?

Friday, February 24, 2012

Express and AspNet_RegSql.exe

Exposing data

Expose SQL Meta Data via ASP

exporting xml

exporting xml

Sunday, February 19, 2012

exporting to pdf without generating report

Exporting to Excel; timeout

Friday, February 17, 2012

Exporting to Excel

Exporting SQL DB files

Blog Archive

About Me