Drill down slow: retrieve

Showing posts with label retrieve. Show all posts

Tuesday, March 27, 2012

Downloading File located in SQL Server with ASP

I have a SQL 2000 Standard server setup and I have a table in one of my
databases to store files in. I am using asp code to retrieve the files from
the database. I can download txt, bmp files just fine but when it comes to
pdf documents it just hangs at the downloading the file screen and then gives
me a timeout error after awhile. It is not a problem with the file sizes. I
am testing it with a 20 and 60k pdf files and one of the txt files I used to
test with is a few mb. I am thinking it's either an IIS server config issue
or SQL Server config problem. The IIS server is for our intranet and is not
locked down. Both IIS and SQL are running on the same box which is a Windows
2003 Enterprise server. Any help would be great.
Thanks,
Brett Bishop
MCSE, MCP+I
US Bankruptcy Court"fibreiv" <fibreiv@.discussions.microsoft.com> wrote in message
news:0880684A-A141-4D78-842A-041C501DBCF5@.microsoft.com...
> I have a SQL 2000 Standard server setup and I have a table in one of my
> databases to store files in. I am using asp code to retrieve the files
from
> the database. I can download txt, bmp files just fine but when it comes
to
> pdf documents it just hangs at the downloading the file screen and then
gives
> me a timeout error after awhile. It is not a problem with the file sizes.
I
> am testing it with a 20 and 60k pdf files and one of the txt files I used
to
> test with is a few mb. I am thinking it's either an IIS server config
issue
> or SQL Server config problem. The IIS server is for our intranet and is
not
> locked down. Both IIS and SQL are running on the same box which is a
Windows
> 2003 Enterprise server. Any help would be great.
Can you access PDF files via URL from the file system?|||fibreiv wrote:
> I have a SQL 2000 Standard server setup and I have a table in one of my
> databases to store files in. I am using asp code to retrieve the files from
> the database. I can download txt, bmp files just fine but when it comes to
> pdf documents it just hangs at the downloading the file screen and then gives
> me a timeout error after awhile. It is not a problem with the file sizes. I
> am testing it with a 20 and 60k pdf files and one of the txt files I used to
> test with is a few mb. I am thinking it's either an IIS server config issue
> or SQL Server config problem. The IIS server is for our intranet and is not
> locked down. Both IIS and SQL are running on the same box which is a Windows
> 2003 Enterprise server. Any help would be great.
Perhaps it has to do with the content type when the file is being sent
down. You may also want to set the content-disposition to indicate to
download the file, "Content-Disposition: Attachment" in the response header.
You aren't saying if you are trying to open up the file directly or save
it to disk first.
--
Aaron Weiker
http://aaronweiker.com/
http://www.sqlprogrammer.org/|||Hi,
This is what i would do.
Try to download a PDF file from the file system (this rules out a problem
with IIS serving PDF-files).
Use WFetch[1] to request the PDF page (from the db), and see if IIS gives
a complete reply back (this will rule out a client-side problem).
Usually these kind of problems are caused by the programmers code. SQL
Server only stores the data, and IIS only "gives" the client the data.
Your code however do everything in between, and that is where it usually
goes wrong.
--
Regards,
Kristofer Gafvert
www.gafvert.info - My Articles and help
www.ilopia.com
fibreiv wrote:
> I have a SQL 2000 Standard server setup and I have a table in one of my
> databases to store files in. I am using asp code to retrieve the files
from
> the database. I can download txt, bmp files just fine but when it comes
to
> pdf documents it just hangs at the downloading the file screen and then
gives
> me a timeout error after awhile. It is not a problem with the file
sizes. I
> am testing it with a 20 and 60k pdf files and one of the txt files I
used to
> test with is a few mb. I am thinking it's either an IIS server config
issue
> or SQL Server config problem. The IIS server is for our intranet and is
not
> locked down. Both IIS and SQL are running on the same box which is a
Windows
> 2003 Enterprise server. Any help would be great.
> Thanks,
> Brett Bishop
> MCSE, MCP+I
> US Bankruptcy Court|||I have the Content type set to application/pdf and I can't open it or save
it. Just to clarify the data is stored in SQL Image field.
"Aaron Weiker" wrote:
> fibreiv wrote:
> > I have a SQL 2000 Standard server setup and I have a table in one of my
> > databases to store files in. I am using asp code to retrieve the files from
> > the database. I can download txt, bmp files just fine but when it comes to
> > pdf documents it just hangs at the downloading the file screen and then gives
> > me a timeout error after awhile. It is not a problem with the file sizes. I
> > am testing it with a 20 and 60k pdf files and one of the txt files I used to
> > test with is a few mb. I am thinking it's either an IIS server config issue
> > or SQL Server config problem. The IIS server is for our intranet and is not
> > locked down. Both IIS and SQL are running on the same box which is a Windows
> > 2003 Enterprise server. Any help would be great.
> Perhaps it has to do with the content type when the file is being sent
> down. You may also want to set the content-disposition to indicate to
> download the file, "Content-Disposition: Attachment" in the response header.
> You aren't saying if you are trying to open up the file directly or save
> it to disk first.
> --
> Aaron Weiker
> http://aaronweiker.com/
> http://www.sqlprogrammer.org/
>|||The file is stored in the Database in an Image field so there is no file
system access to the file.
"Tom Kaminski [MVP]" wrote:
> "fibreiv" <fibreiv@.discussions.microsoft.com> wrote in message
> news:0880684A-A141-4D78-842A-041C501DBCF5@.microsoft.com...
> > I have a SQL 2000 Standard server setup and I have a table in one of my
> > databases to store files in. I am using asp code to retrieve the files
> from
> > the database. I can download txt, bmp files just fine but when it comes
> to
> > pdf documents it just hangs at the downloading the file screen and then
> gives
> > me a timeout error after awhile. It is not a problem with the file sizes.
> I
> > am testing it with a 20 and 60k pdf files and one of the txt files I used
> to
> > test with is a few mb. I am thinking it's either an IIS server config
> issue
> > or SQL Server config problem. The IIS server is for our intranet and is
> not
> > locked down. Both IIS and SQL are running on the same box which is a
> Windows
> > 2003 Enterprise server. Any help would be great.
> Can you access PDF files via URL from the file system?
>
>|||"fibreiv" <fibreiv@.discussions.microsoft.com> wrote in message
news:9BEC1745-770E-47BF-8749-06E8EBB48601@.microsoft.com...
> The file is stored in the Database in an Image field so there is no file
> system access to the file.
Understood.
As part of the troubleshooting routine, I'm asking if you can access one
from the file system.|||I copied the same file thats in the database to the folder I have all the asp
files in on my server and put the url right to the pdf document and it opened
up acrobat and opened the file.
"Tom Kaminski [MVP]" wrote:
> "fibreiv" <fibreiv@.discussions.microsoft.com> wrote in message
> news:9BEC1745-770E-47BF-8749-06E8EBB48601@.microsoft.com...
> > The file is stored in the Database in an Image field so there is no file
> > system access to the file.
> Understood.
> As part of the troubleshooting routine, I'm asking if you can access one
> from the file system.
>
>|||fibreiv wrote:
> I have the Content type set to application/pdf and I can't open it or save
> it. Just to clarify the data is stored in SQL Image field.
> "Aaron Weiker" wrote:
>>Perhaps it has to do with the content type when the file is being sent
>>down. You may also want to set the content-disposition to indicate to
>>download the file, "Content-Disposition: Attachment" in the response header.
Are you setting "Content-Disposition" at all in the header?
--
Aaron Weiker
http://aaronweiker.com/
http://www.sqlprogrammer.org/|||"fibreiv" <fibreiv@.discussions.microsoft.com> wrote in message
news:AED1E8B2-C2EE-4E02-9A38-BA527F0C6B2B@.microsoft.com...
> I copied the same file thats in the database to the folder I have all the
asp
> files in on my server and put the url right to the pdf document and it
opened
> up acrobat and opened the file.
OK - at this point, we'll need to see code to get an idea of how you're
doing it. In that case, I would suggest adding the ASP newsgroup as well
...|||Here is what I am doing to test it. I have hardcoded the file I want to get
out of the database.
Connect = "Provider=SQLOLEDB.1;Persist Security Info=False;User
ID=sa;Password=;Initial Catalog=INNB;Data Source=pompom"
Set db = Server.CreateObject("ADODB.Connection")
db.Open Connect
SQL = "SELECT filesize, contenttype, filename, filedata FROM thefiles where
id = 2"
Set rs = db.Execute( SQL )
If rs.EOF Then Response.End
Response.ContentType = "application/pdf"
Response.AddHeader "Content-Disposition", "attachment;filename=agencies.pdf"
Response.AddHeader "Content-Length", "16863"
Response.BinaryWrite rs("filedata")
"Aaron Weiker" wrote:
> fibreiv wrote:
> > I have the Content type set to application/pdf and I can't open it or save
> > it. Just to clarify the data is stored in SQL Image field.
> >
> > "Aaron Weiker" wrote:
> >>Perhaps it has to do with the content type when the file is being sent
> >>down. You may also want to set the content-disposition to indicate to
> >>download the file, "Content-Disposition: Attachment" in the response header.
> Are you setting "Content-Disposition" at all in the header?
> --
> Aaron Weiker
> http://aaronweiker.com/
> http://www.sqlprogrammer.org/
>|||fibreiv wrote:
> Connect = "Provider=SQLOLEDB.1;Persist Security Info=False;User
> ID=sa;Password=;Initial Catalog=INNB;Data Source=pompom"
> Set db = Server.CreateObject("ADODB.Connection")
> db.Open Connect
> SQL = "SELECT filesize, contenttype, filename, filedata FROM thefiles where
> id = 2"
> Set rs = db.Execute( SQL )
> If rs.EOF Then Response.End
> Response.ContentType = "application/pdf"
> Response.AddHeader "Content-Disposition", "attachment;filename=agencies.pdf"
> Response.AddHeader "Content-Length", "16863"
> Response.BinaryWrite rs("filedata")
Have you tried putting Response.End at the end? Someone else may be able
to help identify a missing piece as it's been a long time since I did this.
--
Aaron Weiker
http://aaronweiker.com/
http://www.sqlprogrammer.org/

Sunday, March 11, 2012

Double Inner Join

I have a table where 2 field in a table which are linked to 2 other different tables.

I need to retrieve values from the 3 tables.
Can I use twice the inner join ? like :

select f_tb2,f_tb3,* from tb1
inner join tb2 on tb2.f_id=tb1.an_id and
inner join tb3 on tb3.f_id=tb1.another_id
etc...

Or something like that ?

Or any other method ?

Thanks for help.Originally posted by Plarde
I have a table where 2 field in a table which are linked to 2 other different tables.

I need to retrieve values from the 3 tables.
Can I use twice the inner join ? like :

select f_tb2,f_tb3,* from tb1
inner join tb2 on tb2.f_id=tb1.an_id and
inner join tb3 on tb3.f_id=tb1.another_id
etc...

Or something like that ?

Or any other method ?

Thanks for help.

Yes you can join many columns in a column to any number of tables. Frequently there will be more than one column in a table which needs to correspond to other tables.

When you start talking about outer joining a table to more than one table is when you may run into limitations, but in this case you will definitely be able to do that :)|||yes you can do it, with one correction and one suggestion:

select f_tb2, f_tb3, tb1.*
from tb1
inner join tb2 on tb2.f_id=tb1.an_id and
inner join tb3 on tb3.f_id=tb1.another_id

correction: remove the word "and" from between the joins

suggestion: qualify the asterisk, otherwise it returns all columns from all tables|||here is a rather large query we use regularly with multiple joins:

select item.item,
Item.description,
LocInv.Item_Desc as LIDesc,
WOH.Item_Desc as WOHDesc,
WOD.Item_desc as WODDesc,
WI.Item_desc as WIDesc,
BOMD.Item_desc as BOMDDesc,
BOMH.Item_desc as BOMHDesc
from Item with (nolock)
inner join Location_inventory LocInv with (nolock)
on Item.Item = LocInv.Item
inner join Work_Order_Header WOH with (nolock)
on Item.Item = WOH.Item
inner join Work_Order_Detail WOD with (nolock)
on Item.Item = WOD.Item
inner join Work_Instruction WI with (nolock)
on Item.Item = WI.Item
inner join Bill_Of_Materials_Detail BOMD with (nolock)
on Item.Item = BOMD.Item
inner join Bill_Of_Materials_Header BOMH with (nolock)
on Item.Item = BOMH.Item
where (Item.Description <> LocInv.Item_Desc and Item.Item = LocInv.Item)
or (Item.Description <> WOH.Item_Desc and Item.Item = WOH.Item)
or (Item.Description <> WOD.Item_Desc and Item.Item = WOD.Item)
or (Item.Description <> WI.Item_Desc and Item.Item = WI.Item)
or (Item.Description <> BOMD.Item_Desc and Item.Item = BOMD.Item)
or (Item.Description <> BOMH.Item_Desc and Item.Item = BOMH.Item)

Friday, March 9, 2012

Don't want escape characters in results

Hi, I have a varchar column containing text which is actually fragments of
HTML.
eg:
sometext
When I retrieve the data using FOR XML I get results with escaped
characters:
sometext
Is there any way to turn off the escaping and just return the actual data?
Thanks!Can you give an example of the query you are using?
You may simply be able to explicitly cast your varchar
column to xml.|||Nice Sunday.
First of all, your HTML string should be well-formed in XML perspective. If
not, resultant XML will be broken. If you have no problem for your string,
just try this script, and see if this makes any sense for you. You may make
some UDF to make the job more convenient.
drop table t1
go
create table t1 (t varchar(10))
insert into t1 values (N'<a/>')
declare @.x xml
set @.x=(select top 1 t from t1)
select @.x
Pohwan Han. Seoul. Have a nice day.
"Paul Robinson" <robinsonpr@.aol.com> wrote in message
news:uCpDPbddGHA.4128@.TK2MSFTNGP05.phx.gbl...
> Hi, I have a varchar column containing text which is actually fragments of
> HTML.
> eg:
> sometext
> When I retrieve the data using FOR XML I get results with escaped
> characters:
> sometext
>
> Is there any way to turn off the escaping and just return the actual data?
> Thanks!
>|||Or convert(),
select convert(xml, t) from t1
Optionally, if your HTML is not well-formed and just want to show the HTML
in browser as HTML, you can use some mid-tier like XSLT with
disable-output-escaping attribute.
Pohwan Han. Seoul. Have a nice day.
"Han" <hp4444@.kornet.net.korea> wrote in message
news:%23hNpSPydGHA.2416@.TK2MSFTNGP03.phx.gbl...
> Nice Sunday.
> First of all, your HTML string should be well-formed in XML perspective.
> If not, resultant XML will be broken. If you have no problem for your
> string, just try this script, and see if this makes any sense for you. You
> may make some UDF to make the job more convenient.
> drop table t1
> go
> create table t1 (t varchar(10))
> insert into t1 values (N'<a/>')
> declare @.x xml
> set @.x=(select top 1 t from t1)
> select @.x
> --
> Pohwan Han. Seoul. Have a nice day.
> "Paul Robinson" <robinsonpr@.aol.com> wrote in message
> news:uCpDPbddGHA.4128@.TK2MSFTNGP05.phx.gbl...
>|||I'm using SQLServer2000.
An example query is below (there are more columns but I've just cut it down
to one to illustrate the problem:
select 1 AS [Tag], 0 AS [Parent],
varMemo AS [memo!1!!element],
from memo
FOR XML EXPLICIT
Result:
<memo>my test data.</memo>
The data column which is a varchar contains this:
my test data.
What I want out is:
<memo>my test data.</memo>
<markc600@.hotmail.com> wrote in message
news:1147450841.895258.275160@.i39g2000cwa.googlegroups.com...
> Can you give an example of the query you are using?
> You may simply be able to explicitly cast your varchar
> column to xml.
>|||Ah slight problem with the xml data type - we're using SQLServer2000. I
just posted the example query in another part of the thread!
Thanks...
"Han" <hp4444@.kornet.net.korea> wrote in message
news:%23hNpSPydGHA.2416@.TK2MSFTNGP03.phx.gbl...
> Nice Sunday.
> First of all, your HTML string should be well-formed in XML perspective.
If
> not, resultant XML will be broken. If you have no problem for your string,
> just try this script, and see if this makes any sense for you. You may
make
> some UDF to make the job more convenient.
> drop table t1
> go
> create table t1 (t varchar(10))
> insert into t1 values (N'<a/>')
> declare @.x xml
> set @.x=(select top 1 t from t1)
> select @.x
> --
> Pohwan Han. Seoul. Have a nice day.
> "Paul Robinson" <robinsonpr@.aol.com> wrote in message
> news:uCpDPbddGHA.4128@.TK2MSFTNGP05.phx.gbl...
of
data?
>|||Try changing
varMemo AS [memo!1!!element],
to
varMemo AS [memo!1!!xml],|||That's got it! Many thanks, I appreciate the help!!
<markc600@.hotmail.com> wrote in message
news:1147684296.310383.20930@.u72g2000cwu.googlegroups.com...
> Try changing
> varMemo AS [memo!1!!element],
> to
> varMemo AS [memo!1!!xml],
>

Friday, February 17, 2012

Doing a SELECT x FROM y WHERE z IN (@param) in stored procedure?

Hello,

I have a table with a foreign key field. I need to retrieve all the records where the foreign key matches any of a set. In plain ol' SQL this is accomplished with the IN(a,b,c) statement but I can't get that to work in a stored procedure.

How would I do this? I can imagine that I could parse the input string and create a temporary table and use that to do a join but that seems rather convoluted.

Any tips highly appreciated! If I'm not being clear I'll gladly post more details.

Thanks,

Noc

PS SQL 2000, ASP.NET 1.1, VS 2003.Have a look at the following article:

http://www.sqlteam.com/item.asp?ItemID=11499|||Many thanks! Looks like I was on the righ track with the temporary table idea but it sure is a lot easier to implement with that sample code.

Tuesday, March 27, 2012

Downloading File located in SQL Server with ASP

Downloading File located in SQL Server with ASP

Downloading File located in SQL Server with ASP

Downloading File located in SQL Server with ASP

Sunday, March 11, 2012

Double Inner Join

Friday, March 9, 2012

Don't want escape characters in results

Friday, February 17, 2012

Doing a SELECT x FROM y WHERE z IN (@param) in stored procedure?

Drill down slow

Blog Archive

About Me