Showing posts with label app. Show all posts
Showing posts with label app. Show all posts

Thursday, March 29, 2012

filter data from multiple tables crashes app!

i didnt think my sql qeury was that complicated that it would crash my web
app. all im trying to do is filter data between two tables. heres my query

<cfquery name="GetResults" datasource="#datasource#">
SELECT *
FROM Content, Content_Sites
WHERE Content.ContentID <> Content_Sites.ContentID
ORDER BY Content.ContentID DESC
</cfquery
equals works, but when i try not equals, it all goes haywire. any ideas?

TIAjonezy (jonezy@.donotmailmejonezy.com) writes:
> i didnt think my sql qeury was that complicated that it would crash my
> web app. all im trying to do is filter data between two tables. heres
> my query
><cfquery name="GetResults" datasource="#datasource#">
> SELECT *
> FROM Content, Content_Sites
> WHERE Content.ContentID <> Content_Sites.ContentID
> ORDER BY Content.ContentID DESC
></cfquery>
> equals works, but when i try not equals, it all goes haywire. any ideas?

Yes and no. Since I don't know your tables, and neither know what you
are trying to achieve, how could I really have any ideas?

But, OK, having seen people using <> in the wrong place before, I can
make a guess. Say that both table have a thousand rows. You are now
asking for all million combinations of these two thousand rows - save
those that have the same ID.

I guess what you are looking for is really something like:

SELECT *
FROM Content c ,
WHERE NOT EXISTS (SELECT *
FROM Content_Sites cs
WHERE c.ContentID = cs.ContentID)
ORDER BY c.ContentID DESC

That is, list all Content that does not have any content site.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||In article <vl786sjaijmr91@.corp.supernews.com>,
jonezy@.donotmailmejonezy.com says...
> i didnt think my sql qeury was that complicated that it would crash my web
> app. all im trying to do is filter data between two tables. heres my query
> <cfquery name="GetResults" datasource="#datasource#">
> SELECT *
> FROM Content, Content_Sites
> WHERE Content.ContentID <> Content_Sites.ContentID
> ORDER BY Content.ContentID DESC
> </cfquery>
> equals works, but when i try not equals, it all goes haywire. any ideas?

How big are the tables? You DO realize you've asked for a
cross product? That means the database is returning the
ENTIRE contents (minus one row) of the Content_Sites table
for each ROW of the Content table.

Assuming 500 rows in Content_Sites and 1000 rows in
Content, you are getting back 500,000 rows in your query.

--
Cam|||thanks.., dunno how i overlookd NOT EXISTS.

i also realized i forgot to include the table connection between content and
content_sites. guess i was in a hurry.

thanks again!

"Erland Sommarskog" <sommar@.algonet.se> wrote in message
news:Xns93E9E8B075BFAYazorman@.127.0.0.1...
> jonezy (jonezy@.donotmailmejonezy.com) writes:
> > i didnt think my sql qeury was that complicated that it would crash my
> > web app. all im trying to do is filter data between two tables. heres
> > my query
> ><cfquery name="GetResults" datasource="#datasource#">
> > SELECT *
> > FROM Content, Content_Sites
> > WHERE Content.ContentID <> Content_Sites.ContentID
> > ORDER BY Content.ContentID DESC
> ></cfquery>
> > equals works, but when i try not equals, it all goes haywire. any
ideas?
> Yes and no. Since I don't know your tables, and neither know what you
> are trying to achieve, how could I really have any ideas?
> But, OK, having seen people using <> in the wrong place before, I can
> make a guess. Say that both table have a thousand rows. You are now
> asking for all million combinations of these two thousand rows - save
> those that have the same ID.
> I guess what you are looking for is really something like:
> SELECT *
> FROM Content c ,
> WHERE NOT EXISTS (SELECT *
> FROM Content_Sites cs
> WHERE c.ContentID = cs.ContentID)
> ORDER BY c.ContentID DESC
> That is, list all Content that does not have any content site.
>
> --
> Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.aspsql

Wednesday, March 7, 2012

File storage with SQL server

Hello,

i'm thinkink about design of a new WinApp.

In this app, i need to store files (xls, doc and pdf essentialy), i need to be able to open and modify it.

I want to use SQL Server 2005 to store those file to be able to use replication process.

Is it possible, is it a good idea ?

Thanks a lot

yes its possible and in most cases prefered. you would store them in an image field.

programmatically, your app would open the file via a stream and read the file to a byte array. You would then use a parameterized a parameterized query to write the bytes to the array.

http://support.microsoft.com/default.aspx/kb/317016/EN-US/

|||Thank you very much for this answer !|||I wont slag you for your inglish if you dont slag me for my typink

Sunday, February 26, 2012

File Protection

Hi -
I'm building a VB.NET application using an MSDE database. I'm working on an
install program for the app, and I'm running into an error with the MSDE
install.
I'm installing MSDE by executing the MSDE setup:
setup.exe INSTANCENAME="test601" BLANKSAPWD=1
DATADIR="d:\data\test601\Data\" TARGETDIR="d:\data\test601\"
DISABLENETWORKPROTOCOLS=0 SECURITYMODE=SQL /L*v
"d:\data\test601\MSDE601.log"
The install works without error on my WinXP Pro development machine. But I
get a Windows File Protection error on a 'fresh' WinXP Home computer.
I create the WinXP Home environment by installing WinXP Home from the MSDN
DVDs and then applying all of the Windows Updates from the MS site (except
for SP2; I'll test that one separately). I then install MSDE by running the
above setup, and I get the following Windows File Protection error:
"Files that are required for Windows to run properly have been replaced by
unrecognized versions. To maintain system stability, Windows must restore
the original versions of these files. Insert your Windows XP Home Edition
CD-ROM now."
How can I prevent this error message from appearing? (MSDE appears to have
installed fine, other than displaying this error.) Or, perhaps more
importantly, how can I install MSDE in a way that does not threaten "system
stability?"
Thanks for your help.
- Jeff
A have the some problem.
Your sistem is hacked by a virus.
"Jeff" <jeff_nospam@.eNetPortals.com> wrote in message news:uNrgd.13498$ta5.4810@.newsread3.news.atl.earth link.net...
Hi -
I'm building a VB.NET application using an MSDE database. I'm working on an
install program for the app, and I'm running into an error with the MSDE
install.
I'm installing MSDE by executing the MSDE setup:
setup.exe INSTANCENAME="test601" BLANKSAPWD=1
DATADIR="d:\data\test601\Data\" TARGETDIR="d:\data\test601\"
DISABLENETWORKPROTOCOLS=0 SECURITYMODE=SQL /L*v
"d:\data\test601\MSDE601.log"
The install works without error on my WinXP Pro development machine. But I
get a Windows File Protection error on a 'fresh' WinXP Home computer.
I create the WinXP Home environment by installing WinXP Home from the MSDN
DVDs and then applying all of the Windows Updates from the MS site (except
for SP2; I'll test that one separately). I then install MSDE by running the
above setup, and I get the following Windows File Protection error:
"Files that are required for Windows to run properly have been replaced by
unrecognized versions. To maintain system stability, Windows must restore
the original versions of these files. Insert your Windows XP Home Edition
CD-ROM now."
How can I prevent this error message from appearing? (MSDE appears to have
installed fine, other than displaying this error.) Or, perhaps more
importantly, how can I install MSDE in a way that does not threaten "system
stability?"
Thanks for your help.
- Jeff
|||Thanks for your response -
While it may be true that a virus was at work here, I was able to get a
different cause ("known issue") from MS (see below). Just curious: what
virus did your computer have?
- Jeff
Response from MS:
I found that it turned out to be a known issue in MSDE setup or SP3a Setup
that File Protection will pop-up . The root cause is the protected system
file sqlunirl.dll was not restored to its original, valid version because
the Windows File Protection restoration process was cancelled by user
interaction. However on computers with Windows XP SP1 if the dllcache
folder is missing or the contents of the dllcache are empty, then you might
still get the pop-ups even with sp3a.
First of all, upgrade your Windows XP to the latest MDAC 2.8
Microsoft Data Access Components (MDAC) 2.8
http://www.microsoft.com/downloads/d...fe3-c795-4b7d-
b037-185d0506396c&DisplayLang=en
Secondly, if above doesn't work, the workaround for this issue is
1. First manually run the dahotfix.exe, which will install/copy the correct
copy of SQLUNIRL.DLL (2000.80.728.0) into the dllcache. (I got the
dahotfix.exe from the sqlredis.exe that ships with SQL2KSP3 in the
x86\other folder. Just right click on this sqlredis.exe and extract the exe
to a local folder. This will give you mdac_qfe.exe. Then again, right click
on mdac_qfe.exe and extract the contents of this exe into another local sub
folder. This should give you a dahotfix.exe)
2. Then run the setup, which should complete successfully with no errors.
"news.microsoft.com" <x> wrote in message
news:uqpxmFdzEHA.2716@.TK2MSFTNGP14.phx.gbl...
A have the some problem.
Your sistem is hacked by a virus.
"Jeff" <jeff_nospam@.eNetPortals.com> wrote in message
news:uNrgd.13498$ta5.4810@.newsread3.news.atl.earth link.net...
Hi -
I'm building a VB.NET application using an MSDE database. I'm working on an
install program for the app, and I'm running into an error with the MSDE
install.
I'm installing MSDE by executing the MSDE setup:
setup.exe INSTANCENAME="test601" BLANKSAPWD=1
DATADIR="d:\data\test601\Data\" TARGETDIR="d:\data\test601\"
DISABLENETWORKPROTOCOLS=0 SECURITYMODE=SQL /L*v
"d:\data\test601\MSDE601.log"
The install works without error on my WinXP Pro development machine. But I
get a Windows File Protection error on a 'fresh' WinXP Home computer.
I create the WinXP Home environment by installing WinXP Home from the MSDN
DVDs and then applying all of the Windows Updates from the MS site (except
for SP2; I'll test that one separately). I then install MSDE by running the
above setup, and I get the following Windows File Protection error:
"Files that are required for Windows to run properly have been replaced by
unrecognized versions. To maintain system stability, Windows must restore
the original versions of these files. Insert your Windows XP Home Edition
CD-ROM now."
How can I prevent this error message from appearing? (MSDE appears to have
installed fine, other than displaying this error.) Or, perhaps more
importantly, how can I install MSDE in a way that does not threaten "system
stability?"
Thanks for your help.
- Jeff