export from mssql database

Hello,

I try to export a mssql database into a csv-file.

I tried this with asp, but it doesn't work.

<%
Dim objConn
Dim csv_path

Set objConn = Server.CreateObject("ADODB.Connection")
objConn.open "database","username","passwd"

csv_path =Server.MapPath("./") & "\csv_test.csv"
SQL ="SELECT * INTO OUTFILE 'C:\inetpub\wwwroot\csv_test.csv' FIELDS
TERMINATED BY ';' OPTIONALLY ENCLOSED BY '"' FROM GROUPS"
objConn.Execute SQL
objConn.Close
Set objConn = Nothing
%
There is a problem near 'C:\inetpub\wwwroot\csv_test.csv'
Can anyone help? Thank you.

Manuel"Manuel" <manuelitoh@.web.de> wrote in message
news:4a77b755.0410130720.9128112@.posting.google.co m...
> Hello,
> I try to export a mssql database into a csv-file.
> I tried this with asp, but it doesn't work.
> <%
> Dim objConn
> Dim csv_path
> Set objConn = Server.CreateObject("ADODB.Connection")
> objConn.open "database","username","passwd"
>
> csv_path =Server.MapPath("./") & "\csv_test.csv"
> SQL ="SELECT * INTO OUTFILE 'C:\inetpub\wwwroot\csv_test.csv' FIELDS
> TERMINATED BY ';' OPTIONALLY ENCLOSED BY '"' FROM GROUPS"
> objConn.Execute SQL
> objConn.Close
> Set objConn = Nothing
> %>
> There is a problem near 'C:\inetpub\wwwroot\csv_test.csv'
> Can anyone help? Thank you.
> Manuel

You seem to be using MySQL syntax, not MSSQL (Microsoft SQL Server), so I
guess you'll get a better answer in a MySQL forum. If you are using MSSQL,
then I suggest you check out osql.exe, bcp.exe, or DTS.

Simon

Export fails under Forms Authentication

I am developing a Web site using VS.Net 2005 using Forms authentication under
ASP.Net 2.0. The user enters a SQL logon and password, we authenticate that,
then use an embedded logon and password for all SQL calls. It works fine,
except when I try to export a report.
I have ReportViewer embedded on an aspx form, using local processing. The
report opens just fine, accepts parameter changes and renders correctly.
However, when I select an Export format and click Export, I get an "ASP.NET
session has expired" message with the following stack trace:
[AspNetSessionExpiredException: ASP.NET session has expired]
Microsoft.Reporting.WebForms.ReportDataOperation..ctor() +335
Microsoft.Reporting.WebForms.ExportOperation..ctor() +24
Microsoft.Reporting.WebForms.HttpHandler.GetHandler() +281
Microsoft.Reporting.WebForms.HttpHandler.ProcessRequest(HttpContext
context) +34
System.Web.CallHandlerExecutionStep.System.Web.HttpApplication.IExecutionStep.Execute() +154
System.Web.HttpApplication.ExecuteStep(IExecutionStep step, Boolean&
completedSynchronously) +64
I've been developing in VB and SQL for over 10 years, but I'm new to .Net
and Report Service. I've searched books and Web sites, and nothing I've
found mentions this problem or anything like it (which I'm sure means I'm
looking in the wrong place!).
Thanks.When I click on Export, I get a new IE instance, and the ASP session
identifier in the URL is different. If I replace it with the same string in
the IE instance that displays the reportViewer, the Export works just fine.
Now I need to figure out how to force the Export to open under the same ASP
session.
"Paul Gettys" wrote:
> I am developing a Web site using VS.Net 2005 using Forms authentication under
> ASP.Net 2.0. The user enters a SQL logon and password, we authenticate that,
> then use an embedded logon and password for all SQL calls. It works fine,
> except when I try to export a report.
> I have ReportViewer embedded on an aspx form, using local processing. The
> report opens just fine, accepts parameter changes and renders correctly.
> However, when I select an Export format and click Export, I get an "ASP.NET
> session has expired" message with the following stack trace:
>
> [AspNetSessionExpiredException: ASP.NET session has expired]
> Microsoft.Reporting.WebForms.ReportDataOperation..ctor() +335
> Microsoft.Reporting.WebForms.ExportOperation..ctor() +24
> Microsoft.Reporting.WebForms.HttpHandler.GetHandler() +281
> Microsoft.Reporting.WebForms.HttpHandler.ProcessRequest(HttpContext
> context) +34
> System.Web.CallHandlerExecutionStep.System.Web.HttpApplication.IExecutionStep.Execute() +154
> System.Web.HttpApplication.ExecuteStep(IExecutionStep step, Boolean&
> completedSynchronously) +64
> I've been developing in VB and SQL for over 10 years, but I'm new to .Net
> and Report Service. I've searched books and Web sites, and nothing I've
> found mentions this problem or anything like it (which I'm sure means I'm
> looking in the wrong place!).
> Thanks.
>|||I found an answer to my own problem, but I'm not sure it is the best
practice. I have to edit the ASP.NET configuration for the site to use
Cookies.
Under State Management, change the Cookieless mode selection to UseCookies.
This (as the documentation explains) causes the site to use Session cookies
to retain the security information, which successfully passes to the browser
window opened by the Export command on the ReportViewer control.
This appears to pass 508 Compliance muster for cookies, but Iâ'm still
checking on that.
"Paul Gettys" wrote:
> When I click on Export, I get a new IE instance, and the ASP session
> identifier in the URL is different. If I replace it with the same string in
> the IE instance that displays the reportViewer, the Export works just fine.
> Now I need to figure out how to force the Export to open under the same ASP
> session.
> "Paul Gettys" wrote:
> > I am developing a Web site using VS.Net 2005 using Forms authentication under
> > ASP.Net 2.0. The user enters a SQL logon and password, we authenticate that,
> > then use an embedded logon and password for all SQL calls. It works fine,
> > except when I try to export a report.
> >
> > I have ReportViewer embedded on an aspx form, using local processing. The
> > report opens just fine, accepts parameter changes and renders correctly.
> > However, when I select an Export format and click Export, I get an "ASP.NET
> > session has expired" message with the following stack trace:
> >
> >
> > [AspNetSessionExpiredException: ASP.NET session has expired]
> > Microsoft.Reporting.WebForms.ReportDataOperation..ctor() +335
> > Microsoft.Reporting.WebForms.ExportOperation..ctor() +24
> > Microsoft.Reporting.WebForms.HttpHandler.GetHandler() +281
> > Microsoft.Reporting.WebForms.HttpHandler.ProcessRequest(HttpContext
> > context) +34
> >
> > System.Web.CallHandlerExecutionStep.System.Web.HttpApplication.IExecutionStep.Execute() +154
> > System.Web.HttpApplication.ExecuteStep(IExecutionStep step, Boolean&
> > completedSynchronously) +64
> >
> > I've been developing in VB and SQL for over 10 years, but I'm new to .Net
> > and Report Service. I've searched books and Web sites, and nothing I've
> > found mentions this problem or anything like it (which I'm sure means I'm
> > looking in the wrong place!).
> >
> > Thanks.
> >

Export Access DB to MSSQL Server 2000

Hello,

I am working on a couple fo the ASP.NET walkthoughs and I would like to practice with a Database that I created in Access. I realize that I could use the Access DB, but I would like to learn to work with a more industrial strength DB. Is there a method to export to MSSQL server 2000 from Access ?

Regards,

JamesIf I remember correctly, use below steps to do that!

Open Enterprisemanager of SQLserver 2000, Open any database where you want to import access db table, then keep your cursor on Tables --> Right Click --> Select All Tasks --> ImportData --> Click Next --> Choose Microsoft Access from theData Source list box, and from there follow the steps in wizard, it will walk you through the steps to import AccessDB to that DB.

Hope it helps! [Note: You have to have permission to do this]|||I am actually using MSDE 2000. Is the process the same ?

Regards
James|||From access process should be same

I found a link at support on steps for it, please see and use that for exporting from access

Convert an Access Database to SQL Server

Hope it helps!|||Thanks

It Works !!

Experts only: SQL Server does not exist or access denied.

Hi,
I'm accessing MS SQL server standard edition using ASP.net 1.1 web applications
my site has traffic ranking 84,000 on alexa.com. all web applicationsare mounted on One sigle Application pool. I have developed
monitoring webservice for monitoring the runtime errors occured duringtransactions, where I came across below errors all the time. duringheavy traffic.
1. SQL Server does not exist or access denied.
2. General network error. Check your network documentation.
3. Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
First one is very important as Microsoft it self agree that it was bug in their products. How can I fix it.

http://support.microsoft.com/kb/328306

Client uses TCP/IP and Named Pipes.

I'm hoping to get it fixed using this thread.

Thanks,
Satalaj.

Have you checked the server loads on the machine that is running SQL during peak times?

I'm far from an expert on this subject, but it seems the server can't respond due to server load. How many concurrent users do you have during peaks?

Cheers!

/Eskil

|||

Yes, It is happening during peak load. How can I fix it.

Server DB size is 3GB and RAM 1GB. server uses 60% to 99% of RAM during peak load.

|||

Did you check the Eventviewer to see if your SQL Server Service is getting restarted/recycled during this peak loads that makes it unavailable for a few seconds/minutes.

|||

Hi urstop

If this is the case then all my webapplications should produce same erro, It is only happening for someweb applications mounted on one application domain.

|||

But are your other web applications also have lot of traffic. Because if the traffic is less for other applications then there are chances that during this error period we do not have any traffic coming to your other web applications. Just a thought.

|||I have written a webservice whichkeeps log of all error it gets called from webapplication if any erroroccured in web applications. Do you know how can I stop recycling ofthe SQL server. or Do I need to update the RAM on SERVER. if so howmuch RAM will be required for DB of size 3GB|||

You have mentioned that you have 1GB of RAM. I think that should be good enough for your DB size.

Also, does your eventviewer has events which indicate that SQL Service is getting recycled? Becuase it should not be a case where we are looking in a wrong area

|||

RAM/CPU limitations on your SQL server will usually result in slow queries during peak times and not error messages. Take a look at your page file usage and memory available to see if this might be the case. I would definately suggest adding a gb, 2gb should be plenty but extra never hurts.

Is the web server separate from the SQL server or are they on the same machine? If they are on the same separating the two might help as well.

|||

Hi Dustin, web and SQl server are separated. I'm going to see the log as per urstop suggession

Expected end of statement

i got this kind of error type:

Error Type:
Microsoft VBScript compilation (0x800A0401)
Expected end of statement
/wanhe/services/update_succeed.asp, line 80, column 129
sqlStrDB = "update applicant set Name='"&username&"', Gender= '"&gender&"', Age='"&age&"',Marriage='"&maritalstatus&"',HighEdu='"&hEducation&"',EngScore='"&engScore&"',ToeflScore='"&toeflScore&"',IeltsScore='"&ieltsScore&"',FysmScore='"&fysmScore&"',NcetScore='"&ncetScore&"',Tel='"&coTel&"',Mobile='"&coMobile&"',Email='"&coEmail&"',AppSchool='"&appSchool&"',AppMajor='"&appMajor&"',Comment='"&coComment&"',companyNameCN='"&coNameCN&"',companyNameEN='"&coNameEN&"',CoAddress='"&coAddress&"',CoPostal='"&coPostal&"' where ID = "&userID&""

this is my coding:
<%
dim cnStrDB
dim rcSetDB
dim sqlStrDB

'create connection and recordset objects
Set cnStrDB = Server.CreateObject("ADODB.Connection")
Set rcSetDB = Server.CreateObject("ADODB.Recordset")

' connection string
dim pathDB
dim providerDB

' change this path to your database path
pathDB = "C:\Inetpub\wwwroot\wanhe\services\oApplication.mdb "
providerDB = "Microsoft.Jet.OLEDB.4.0"

' defining database connection (connectionstring.asp)
cnStrDB.ConnectionString = pathDB
cnStrDB.Provider = providerDB
cnStrDB.Mode = 3

cnStrDB.open
dim username
Dim coNameCN
Dim coNameEN
Dim coAddress
Dim coPostal
dim gender
dim age
dim maritalstatus
dim hEducation
dim engScore
dim toeflScore
dim ieltsScore
dim fysmScore
dim ncetScore
dim appSchool
dim appMajor
Dim coTel
Dim userID
Dim coMobile
dim coEmail
dim coComment

username = request.form("userName")
coNameCN = request.form("txtCoNameCN")
coNameEN= request.form("txtCoNameEN")
coAddress= request.form("txtCoAddress")
coPostal= request.form("txtPostal")
coTel= request.form("Tel")
coMobile= request.form("Mobile")
coEmail= request.form("Email")
coComment= request.form("Comment")
gender= request.form("Gender")
age= request.form("Age")
maritalstatus= request.form("Age")
hEducation= request.form("highEdu")
'response.write hEducation
'response.end
engScore= request.form("EngScore")
toeflScore= request.form("ToeflScore")
ieltsScore= request.form("IeltsScore")
fysmScore= request.form("FysmScore")
ncetScore= request.form("NcetScore")
appSchool= request.form("AppSchool")
appMajor= request.form("AppMajor")

userID=Request.cookies("ID")

sqlStrDB = "update applicant set Name= '"&username&"', Gender= '"&gender&"', Age='"&age&"',Marriage='"&maritalstatus&"', HighEdu='"&hEducation&"', EngScore='"&engScore&"',ToeflScore='"&toeflScore&"',IeltsScore='"&ieltsScore&"',FysmScore='"&fysmScore&"',NcetScore='"&ncetScore&"',Tel='"&coTel&"',Mobile='"&coMobile&"',Email='"&coEmail&"',AppSchool='"&appSchool&"',AppMajor='"&appMajor&"',Comment='"&coComment&"',companyNameCN='"&coNameCN&"',companyNameEN='"&coNameEN&"',CoAddress='"&coAddress&"',CoPostal='"&coPostal&"' where ID = "&userID&""

rcSetDB.ActiveConnection = cnStrDB
rcSetDB.cursorType = 3 'for add record into database
rcSetDB.cursorLocation = 3
rcSetDB.open(sqlStrDB)

%>

i know it's somehting wrong with
HighEdu='"&hEducation&"
but i cant find where is wrong

anyone can helps me??
thanks a lot
:()Maybe that "" at the end should have been ";"?

You should really use prepared statements with bind variables. Not only do they improve performance and remove a security loophole, they also make it easier to write your SQL:

sqlStrDB = "update applicant set Name=?, Gender=?, Age=?,Marriage=?, HighEdu=?, ... , CoPostal=? where ID = ?;"|||same error message|||your first line:
sqlStrDB = "Your statement" _ (add the "_" at the end)

your second line:
& "Your statement" (add "&" at the first)

try to manipulate it if error happens again. I think it should work because you have to separate your string to another lines by these characters.

regards.

chris|||the problem is the variable name
i change to another name can liao

by the way thanks for ur help

Execution TIme Problem

I am trying to run a SQL Server procedure from a program in ASP.Net 2005. This procedure is to insert around 500 records(can exceed every month) in a table with 4 columns and is also containing another small procedure also. When this procedure is executed from online server, it shows timeout message as:

Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.

But when the same procedure is run from SQL Query Anayser it excute within seconds. How can i solve this problem , i need this solution urgently too.

Hope to get ur response soon.

Hello romita

Do you mind sharing some code. It would be helpful to try and figure out the problem

Also are you able to run SQL Profiler to check that statments that are hitting the databse from the aps.net application.

regards

G

|||

Hi Gonzo11,

Regarding the same problem posted yesterday, am enclosing the code of procedure below for ur reference. Please check n let me know if theres any solution.

CREATE PROCEDURE proc_userroyalty
@.month int, @.year int, @.joins int output, @.royalties int output, @.royaltyamt decimal output, @.netamt decimal output

as

declare @.joinamt bigint, @.user varchar(50), @.left int, @.middle int, @.right int, @.royalty decimal, @.regdate datetime , @.totalpairs int, @.lsdate varchar(50), @.bdate datetime, @.maxamt decimal, @.nxtmon int, @.totalroyal decimal, @.nxtdt datetime, @.nxtyr int

if exists(select * from db_bonusdetails where bd_purpose='R' and month(bd_date)=@.month and year(bd_date)=@.year and @.month<month(getdate()) and @.year<=year(getdate()) )
begin
select @.joins = count(*) from db_users where month(us_regdate)=@.month and yeaR(us_regdate)=@.year
select @.royalties=count(*) from db_tempbonus where temp_month<=@.month andtemp_year=@.year
if @.month<8 and @.year <=2007
begin
select @.joinamt=@.joins * 100
end
else
begin
select @.joinamt=@.joins * 120
end

select @.royaltyamt = floor(@.joinamt / @.royalties)
select @.netamt=sum(bd_amount) from db_bonusdetails where bd_purpose='R' and month(bd_date)=@.month and year(bd_date)=@.year group by bd_amount

end
else
begin
delete from db_bonusdetails where bd_purpose='R' and datepart(mm,bd_date)=@.month and datepart(yyyy,bd_date)=@.year

select @.joins=count(*) from db_users where datepart(month,us_regdate) = @.month and datepart(year,us_regdate) = @.year and us_status='Y'

exec proc_royal @.month, @.year
select @.royalties=count(*) from db_tempbonus where temp_month<=@.month andtemp_year=@.year

select @.joinamt=@.joins * 120

if @.royalties > 0
begin

select @.royaltyamt = floor(@.joinamt / @.royalties)
select @.lsdate=str(@.month) + '-' + '28' + '-' + str(@.year)
select @.bdate=convert(datetime,@.lsdate)


declare cur_bonus cursor
for select temp_user,temp_regdt from db_tempbonus where temp_month<=@.month andtemp_year=@.year

open cur_bonus
fetch next from cur_bonus into @.user,@.regdate

while @.@.fetch_status = 0
begin
select @.nxtdt=dateadd(mm,3,@.regdate)

if datediff(dd,@.nxtdt,getdate()) > 0
begin
select @.nxtmon=month(@.nxtdt)
select @.nxtyr=year(@.nxtdt)

SELECT @.totalpairs= SUM(mp_lcount) + SUM(mp_mcount) + SUM(mp_rcount)
FROM db_monthlypairs
WHERE (mp_month<=@.nxtmon ) and (mp_year <=@.nxtyr) AND (mp_user = @.user)

if @.totalpairs< 30
begin
select @.maxamt=25000
end
else
begin
select @.maxamt=50000
end
end
else
begin
select @.maxamt=50000
end

select @.totalroyal=sum(bd_amount) from db_bonusdetails wherebd_user=@.user and bd_purpose='R'

if @.totalroyal <@.maxamt or @.totalroyal is null
begin
insert into db_bonusdetails values(@.bdate,@.user,'R',@.royaltyamt)
end

fetch next from cur_bonus into @.user,@.regdate
end

close cur_bonus
deallocate cur_bonus

select @.netamt=sum(bd_amount) from db_bonusdetails where bd_purpose='R' and datepart(mm,bd_date)=@.month and datepart(yyyy,bd_date)=@.year

end
else
begin
select @.royalties=0
select @.royaltyamt=0
select @.netamt=0
end
end
GO

PROCEUDRE 2 - proc_royal to be executed within above one.

CREATE PROCEDURE proc_royal
@.month int , @.year int
AS

declare @.user varchar(50), @.regdt datetime, @.lcount int , @.mcount int, @.rcount int, @.uspos char(1), @.rows int, @.mainuser varchar(50), @.regdate datetime

declare cur1 cursor
for select mp_user, us_regdate from db_users,db_monthlypairs where mp_user=us_login and mp_month<=@.month and mp_year<=@.year and mp_user not in(select temp_user from db_tempbonus where temp_month<=@.month and temp_year<=@.year)
group by mp_user, us_regdate having sum(mp_lcount)>=1 and sum(mp_mcount) >=1 and sum(mp_rcount)>=1
open cur1
fetch cur1 into @.mainuser, @.regdate
while @.@.fetch_status=0
begin
declare cur2 cursor

for select us_login, us_regdate, us_position from db_users whereus_reference=@.mainuser and month(us_regdate) <= @.month and year(us_regdate)<=@.year
open cur2
fetch cur2 into @.user, @.regdt, @.uspos
while @.@.fetch_status=0
begin
if @.uspos ='L'
begin
select @.lcount=count(*) from db_users whereus_reference=@.user and month(us_regdate) <= @.month and year(us_regdate)<=@.year
select @.lcount = @.lcount + 1
end
else
begin
if @.uspos ='M'
begin
select @.mcount=count(*) from db_users whereus_reference=@.user and month(us_regdate) <= @.month and year(us_regdate)<=@.year
select @.mcount = @.mcount + 1
end
else
begin
select @.rcount=count(*) from db_users whereus_reference=@.user and month(us_regdate) <= @.month and year(us_regdate)<=@.year
select @.rcount = @.rcount + 1
end

end
fetch cur2 into @.user, @.regdt, @.uspos
end

close cur2
deallocate cur2

if @.lcount>=4 and @.mcount>=4 and @.rcount >=4
begin
insert into db_tempbonus values(@.mainuser, @.regdate, @.month, @.year)
end
fetch cur1 into @.mainuser, @.regdate
end

close cur1
deallocate cur1
GO

proc_royal is to add new members satisfying the condtitions to a table eah month. In the main procedure based on all records in this table that many records will be inserted in another one. This part came as updation in the project, so its very risky to change other codes as it might affect existing program.

Hope u got the idea of the procedure.

If theres any simple way with less execution time please let me know. By each month records can increase too, so solution has to be one for furture user too.

Awaiting ur response,

Regards,

Romita

|||

Basedon your post, the error occurs when the execution time beyond TimeoutProperty. When you are trying to connect or access to a Database tablewhich is having large volume of data, query execution time will bemore. There are two main Timeout property in ADO.NET.

1.Connection Timeout for Connection. It could be solved by settingConnectionTimeout property of Connection object in Connection String.
2. Timeout for Data access ( Command Object ). You can setCommandTimeoutproperty to Command object.I recommend you set CommandTimeOut propertyto bigger one value. Try this.

Please let me know whether this canhandle this problem or not.If you have any further questions, please feel free to let me know.

|||

Hello Romita,

Thank you for you reply. I will need some time to go over you SP.

In the meantime are you able to run the SQL Profiler and check what is actually hitting the DB and what is taking the longest time.

regards,

G

|||

Hi!

I have tried setting command timeout and connection timeout properties, but still this Time Out Expired exists. Just setting the Timeout property of each object is enough or some other changes are also reqd.

Actually i had tried executing the procedure without calling another procedure within, it didn;t solve my problem. Time is taking to insert 400-500 records at a time, but it cannot be programmed in other way too.

Pls let me know, if theres more to do in Timeout settings or if theres another solution.

Regards,

Romita

|||

Hello Romita,

I have looked at the code that you have sent but i fyou say that it is inserting 400-500 rows that generaly should not take a very long time.

You have to see which parts of the SP are taking up most of the time and address them first.

So, you would have to run SQL Server Profiler, that will help you identify what is hitting the database and what is taking the most time.
Once you identify that you can try to address that issue. For example, very often creating an index for a table ususaly helps.

Hope this helps

regards,
G