RE: SQL query help
Darren, since you are using an Access db go into your Access program. Start to create a query to return the value you want. Then look at the SQL of this query you created in Access. Access doesn't use standard SQL and has a lot of different keywords. So this will get you on the right track of what your SQL statement should look like. -Original Message- From: Darren Adams [mailto:[EMAIL PROTECTED]] Sent: Monday, January 28, 2002 9:56 AM To: CF-Talk Subject: RE: SQL query help I think it was just that id didnt like the value in quotes !! -Original Message- From: Michael Blair [mailto:[EMAIL PROTECTED]] Sent: 28 January 2002 16:54 To: CF-Talk Subject: RE: SQL query help Try reversing the mm/dd/yy into the db and see. -Original Message- From: Darren Adams [mailto:[EMAIL PROTECTED]] Sent: Monday, January 28, 2002 10:44 AM To: CF-Talk Subject: RE: SQL query help Even though the datestamp field is a date/time field in access I still get this error message. ODBC Error Code = 22005 (Error in assignment) [Microsoft][ODBC Microsoft Access Driver] Data type mismatch in criteria expression. Data Source = "marketing" SQL = "select * from main_table where datestamp > '28/01/02'" The error occurred while processing an element with a general identifier of (CFQUERY), occupying document position (1:1) to (1:60) in the template file E:\Inetpub\inetsu\menutest\marketing\browse.cfm -Original Message- From: Douglas Brown [mailto:[EMAIL PROTECTED]] Sent: 28 January 2002 16:44 To: CF-Talk Subject: Re: SQL query help What error do you get? There are two major products that come out of Berkeley: LSD and [Unix] BSD. We don't believe this to be a coincidence. Doug Brown - Original Message - From: "Darren Adams" <[EMAIL PROTECTED]> To: "CF-Talk" <[EMAIL PROTECTED]> Sent: Monday, January 28, 2002 7:40 AM Subject: SQL query help > Hi guys > > Can anyone help, I just need to check that the date of a record in a > database is within a specific range. > > > #today# > > > select * from main_table > where 1=1 > and datestamp BETWEEN '#date#' AND #today# > > and region = '#region#'> "--- Select ---"> and materi= '#material#'> and vertic= '#vertical#' > and target = '#target#'> "--- Select ---"> and product_servi= '#product#' > > > > This query give me an error in assignment. > > I know that this should be easy but I cant think of it!!! > > Cheers, > Darren Adams > Web Developer > Marketing Department > Systems Union > > mail: [EMAIL PROTECTED] > > __ Dedicated Windows 2000 Server PIII 800 / 256 MB RAM / 40 GB HD / 20 GB MO/XFER Instant Activation · $99/Month · Free Setup http://www.pennyhost.com/redirect.cfm?adcode=coldfusiona FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: SQL query help
What are you trying to do with your where clause? -Original Message- From: Darren Adams [mailto:[EMAIL PROTECTED]] Sent: Monday, January 28, 2002 8:41 AM To: CF-Talk Subject: SQL query help Hi guys Can anyone help, I just need to check that the date of a record in a database is within a specific range. #today# select * from main_table where 1=1 and datestamp BETWEEN '#date#' AND #today# and region = '#region#' and material = '#material#' and vertical = '#vertical#' and target = '#target#' and product_service = '#product#' This query give me an error in assignment. I know that this should be easy but I cant think of it!!! Cheers, Darren Adams Web Developer Marketing Department Systems Union mail: [EMAIL PROTECTED] __ Get Your Own Dedicated Windows 2000 Server PIII 800 / 256 MB RAM / 40 GB HD / 20 GB MO/XFER Instant Activation · $99/Month · Free Setup http://www.pennyhost.com/redirect.cfm?adcode=coldfusionb FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: weird CFQuery problem
You don't need those double parens around your where clause. Try this. SELECT qryShipSearch.ActShipDate, qryShipSearch.RPO_Num, qryShipSearch.CustPartNum, qryShipSearch.TRW_PartNum, qryShipSearch.SerialNum, qryShipSearch.SalesNumber, qryShipSearch.ShippingRequestNum FROM qryShipSearch WHERE qryShipSearch.SalesNumber LIKE '%01A778%' -Original Message- From: Snyder, Jason [mailto:[EMAIL PROTECTED]] Sent: Tuesday, January 15, 2002 10:57 AM To: CF-Talk Subject: weird CFQuery problem I am generating an SQL statement partially inside of a CFQuery tag and partially in CFScript. When I try to execute the query w/ a WHERE clause, it bombs out. If I try w/o a WHERE clause it is happy. If I try w/o a WHERE clause and w/ an ORDER BY clause it is happy. The error message I get when it bombs is: - Error Diagnostic Information ODBC Error Code = 37000 (Syntax error or access violation) [Microsoft][ODBC Microsoft Access Driver] Syntax error in query expression '((qryShipSearch.SalesNumber LIKE ''%01A778%''))'. The error occurred while processing an element with a general identifier of (CFQUERY), occupying document position (101:1) to (101:73). - If I use CFOutput and copy/paste to print the whole SQL statement to the screen, go into source view of the output, copy that SQL statement into another query and then run it, it works. Here is the query that bombs: - SELECT #scrTable#.ActShipDate, #scrTable#.RPO_Num, #scrTable#.CustPartNum, #scrTable#.TRW_PartNum, #scrTable#.SerialNum, #scrTable#.SalesNumber, #scrTable#.ShippingRequestNum FROM #scrTable# #strSQLWhere# #strSQLOrder#; - Here is the one that works: - SELECT qryShipSearch.ActShipDate, qryShipSearch.RPO_Num, qryShipSearch.CustPartNum, qryShipSearch.TRW_PartNum, qryShipSearch.SerialNum, qryShipSearch.SalesNumber, qryShipSearch.ShippingRequestNum FROM qryShipSearch WHERE ((qryShipSearch.SalesNumber LIKE '%01A778%')) ; - __ Why Share? Dedicated Win 2000 Server · PIII 800 / 256 MB RAM / 40 GB HD / 20 GB MO/XFER Instant Activation · $99/Month · Free Setup http://www.pennyhost.com/redirect.cfm?adcode=coldfusionc FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: Querying FoxPro Date
No no FoxPro is all foxed up this is how you would have to query it.
select ponumber, supplier,odr_date
from purchase
where odr_date > ctod('12/10/01')
-Original Message-
From: Don Vawter [mailto:[EMAIL PROTECTED]]
Sent: Friday, December 14, 2001 9:13 AM
To: CF-Talk
Subject: Re: Querying FoxPro Date
It's been a few years but I think this might work
select ponumber, supplier,odr_date
from purchase
where odr_date > {12/10/2001}
- Original Message -
From: "Michael Haggerty" <[EMAIL PROTECTED]>
To: "CF-Talk" <[EMAIL PROTECTED]>
Sent: Friday, December 14, 2001 8:34 AM
Subject: RE: Querying FoxPro Date
> Just a stab in the dark, is that the query you are actually running? The
> double quotes before the date are actually a pair of single quotes.
>
> I have no experience with FoxPro, but the query you posted will not run
> under SQL Server. Change the single quotes to read like this and you
should
> be alright:
>
> WHERE odr_date > '12/10/01'
>
> Mike
>
>
> -Original Message-
> From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]
> Sent: Thursday, December 13, 2001 4:35 PM
> To: CF-Talk
> Subject: Querying FoxPro Date
>
>
> How can you select records for a FoxPro date by using date?
>
> The query:
>
>
> select ponumber, supplier,odr_date
> from purchase
> where odr_date > ''12/10/01"
>
>
>
> It returns with the error:
>
> ODBC Error Code = 22005 (Error in assignment)
> [Microsoft][ODBC Visual FoxPro Driver]Operator/operand type mismatch.
>
> In SQL, it works fine. But the DB will be in FoxPro
>
> When I select all and out the date, the date format is in -mm-dd. I
> tried using that format, but no luch either.
>
> Any clues?
>
> Thanks
> Jacob
>
>
~~
Structure your ColdFusion code with Fusebox. Get the official book at
http://www.fusionauthority.com/bkinfo.cfm
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: MS SQL 7/2k File Size
There will be a file most likely in your D:\MSSQL7\DATA folder that will be a .mdf file. That is your database, look at the properties of that. If you don't know what the file would be named or can't figure it out you can look in Enterprise Manager right click on your database go to properties and look at the location of your database and it will tell you the name and the place it is stored. Hope this helps. Chris Sinkwitz -Original Message- From: BEN MORRIS [mailto:[EMAIL PROTECTED]] Sent: Wednesday, August 29, 2001 7:35 AM To: CF-Talk Subject: MS SQL 7/2k File Size List, Does anyone know of a way in SQL (as opposed to enterprise manager) to get the file size of a MS SQL database? Thanks in advance, Ben Morris ~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: Way Slow web pages
It's in your Client Network Utility for your local machine and Server Network Utility for the Server. Are you using MDAC 2.5? If so there is a bug that might affect you that will make the server app really slow and sometimes even timeout. You might want to check Microsoft for a hotfix on your MDAC if you are using that version. -Original Message- From: Bruce, Rodney [mailto:[EMAIL PROTECTED]] Sent: Monday, August 27, 2001 10:34 AM To: CF-Talk Subject: RE: Way Slow web pages Justin Were is the option to choose between pipes and TCP/IP? I'm looking in the SQL Manger properties but don't see it? both are slow -Original Message- From: Justin Greene [mailto:[EMAIL PROTECTED]] Sent: Friday, August 24, 2001 5:56 PM To: CF-Talk Subject: RE: Way Slow web pages Make sure to config the SQL client to use TCP/IP not named pipes (especially if querying from a different machine), and try using SQL security rather than NT security. Also what happens when you try to login using query manager (using the same login the webserver uses) and run the query manually. Login slow, query slow, both, neither? Justin -Original Message- From: Bruce, Rodney [mailto:[EMAIL PROTECTED]] Sent: Thursday, August 23, 2001 3:05 PM To: CF-Talk Subject: OT: Way Slow web pages -- Spamex - Forums Now Available http://forums.spamex.com/forums/ -- Replies will be sent to [EMAIL PROTECTED] -- Additional Info: http://www.spamex.com/i/?v=52 sorry about the very broad question. but what are some things that could cause a web server to almost be 10times slower. web pages that were taking ~3,000ms are up to ~30,000. same with others that were at ~200ms are at ~2,000. is seems to be the query/db access time. it's affecting both Access and SQL backed web pages. Access itself seems to be running fine. but SQL Manager seems to be running real slow. takes over 2 mins to just display a list of the tables when a db is chosen. and then almost 4 mins to display the design table. I have reviewed the logs, the only listing, but is not consistent is that the administrator account fails on accessing \winlogon Object type: desktop. EventID: 560 looks to be trying the access \winlogon once a sec. but this is not going on all the time. This will happen for about 3 mins then stop. the changes to the server I can think of, MS office and SQL 7 have both been reloaded. This is not an active server, once this problem gets fixed, it will be, so work load is not a factor. It is a WINNT 4.0, 1 GB RAM, dual 800mhz CPUs. using CF 5. I have tried to turn the different services off,(CF, WEB, SQL), but this does not affect the problem. I am at a loss. any suggestions will be appreciated. ~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: Importing saved DTS in SQL 7
You could have DTS packages on one server that are not on your dev server and by replacing the .mdf and .ldf it would erase any hooks to that package that it didn't know about. What you are doing is basically taking and entire SQL repository from your dev machine and placing it on your prod machine. In theory they should be exactly the same, but there is always the chance that someone made a change in production and didn't update the dev. Also if you use security on your DTS packages it will most likely fail because the ids in SQL are system generated SIDs which would be different on another machine. -Original Message- From: Billy Cravens [mailto:[EMAIL PROTECTED]] Sent: Monday, August 27, 2001 9:11 AM To: CF-Talk Subject: RE: Importing saved DTS in SQL 7 I'd agree, but like I said, I've had some success with it, so file it away as a "last resort". Just for my own curiosity, what would cause corruption? --- Billy Cravens Web Development, EDS [EMAIL PROTECTED] -----Original Message- From: Chris Sinkwitz [mailto:[EMAIL PROTECTED]] Sent: Monday, August 27, 2001 10:47 AM To: CF-Talk Subject: RE: Importing saved DTS in SQL 7 I would not recommend this way at all. This gives you a good change of corrupting your database. -Original Message- From: Billy Cravens [mailto:[EMAIL PROTECTED]] Sent: Monday, August 27, 2001 8:38 AM To: CF-Talk Subject: RE: Importing saved DTS in SQL 7 In situations like that, I usually burn SQL Server's data files (.mdf and .ldf) to a cd. Copy the files into SQL Server's data directory. This *should* work. Not the cleanest method, but I've had good experience with it. --- Billy Cravens Web Development, EDS [EMAIL PROTECTED] -Original Message- From: Timothy Heald [mailto:[EMAIL PROTECTED]] Sent: Monday, August 27, 2001 9:27 AM To: CF-Talk Subject: RE: Importing saved DTS in SQL 7 Its a very unique situation. We have a training lab here that is completely cut off from the outside world. One of our clients is coming in to train on our applications. Al of their data is on our live DB, but I need to be able to save something to disk to carry over to the training server and import it. I have already saved a .dts file of the db and now I can't figure out how to import it. Tim Heald ACP/CCFD :) Application Development http://www.schoollink.net/ -Original Message- From: Mark Stewart [mailto:[EMAIL PROTECTED]] Sent: Monday, August 27, 2001 10:08 AM To: CF-Talk Subject: RE: Importing saved DTS in SQL 7 Tim, Need a little more detail here. Where do you want to import from/to? Is it safe to say that you've created a DTS package on, let's say, a dev db server and you now want to export it to your production db server? Mark -Original Message- From: Timothy Heald [mailto:[EMAIL PROTECTED]] Sent: Monday, August 27, 2001 10:03 AM To: CF-Talk Subject: OT: Importing saved DTS in SQL 7 Hey, How do I go about importing a saved DTS once I have it? Tim Heald ACP/CCFD :) Application Development http://www.schoollink.net/ ~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: Importing saved DTS in SQL 7
I would not recommend this way at all. This gives you a good change of corrupting your database. -Original Message- From: Billy Cravens [mailto:[EMAIL PROTECTED]] Sent: Monday, August 27, 2001 8:38 AM To: CF-Talk Subject: RE: Importing saved DTS in SQL 7 In situations like that, I usually burn SQL Server's data files (.mdf and .ldf) to a cd. Copy the files into SQL Server's data directory. This *should* work. Not the cleanest method, but I've had good experience with it. --- Billy Cravens Web Development, EDS [EMAIL PROTECTED] -Original Message- From: Timothy Heald [mailto:[EMAIL PROTECTED]] Sent: Monday, August 27, 2001 9:27 AM To: CF-Talk Subject: RE: Importing saved DTS in SQL 7 Its a very unique situation. We have a training lab here that is completely cut off from the outside world. One of our clients is coming in to train on our applications. Al of their data is on our live DB, but I need to be able to save something to disk to carry over to the training server and import it. I have already saved a .dts file of the db and now I can't figure out how to import it. Tim Heald ACP/CCFD :) Application Development http://www.schoollink.net/ -Original Message- From: Mark Stewart [mailto:[EMAIL PROTECTED]] Sent: Monday, August 27, 2001 10:08 AM To: CF-Talk Subject: RE: Importing saved DTS in SQL 7 Tim, Need a little more detail here. Where do you want to import from/to? Is it safe to say that you've created a DTS package on, let's say, a dev db server and you now want to export it to your production db server? Mark -Original Message- From: Timothy Heald [mailto:[EMAIL PROTECTED]] Sent: Monday, August 27, 2001 10:03 AM To: CF-Talk Subject: OT: Importing saved DTS in SQL 7 Hey, How do I go about importing a saved DTS once I have it? Tim Heald ACP/CCFD :) Application Development http://www.schoollink.net/ ~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: Importing saved DTS in SQL 7
Step 1. Go into Enterprise Manager Step 2. Connect to your database server Step 3. Right click on the Data Transformation Services folder Step 4. Select All Tasks then select Open Package Step 5. Browse to your floppy or where you put the .dts package and open it. Step 6. If you want to reuse it just save again once it is open to the SQL Server Repository. If you have anymore questions let me know. Chris Sinkwitz -Original Message- From: Mark Stewart [mailto:[EMAIL PROTECTED]] Sent: Monday, August 27, 2001 7:41 AM To: CF-Talk Subject: RE: Importing saved DTS in SQL 7 Ah! I see. Just to make sure I was going to give you good info, I went into enterprise manager and viola, there is no import, file - open, or anything... Good thing I checked. The key issue for you is that your lab, and rightfully so, is cut off from everything else. I've always used the Save As... and just saved it to another server. However, this won't work for you. I've just looked around and I don't see where you can import or even generate sql scripts for your package. I'm no guru, but if you can do this, you would think it would be somewhat intuitive. I'll keep looking and let you know if I find anything. Good Luck. Mark -Original Message- From: Timothy Heald [mailto:[EMAIL PROTECTED]] Sent: Monday, August 27, 2001 10:27 AM To: CF-Talk Subject: RE: Importing saved DTS in SQL 7 Its a very unique situation. We have a training lab here that is completely cut off from the outside world. One of our clients is coming in to train on our applications. Al of their data is on our live DB, but I need to be able to save something to disk to carry over to the training server and import it. I have already saved a .dts file of the db and now I can't figure out how to import it. Tim Heald ACP/CCFD :) Application Development http://www.schoollink.net/ -Original Message- From: Mark Stewart [mailto:[EMAIL PROTECTED]] Sent: Monday, August 27, 2001 10:08 AM To: CF-Talk Subject: RE: Importing saved DTS in SQL 7 Tim, Need a little more detail here. Where do you want to import from/to? Is it safe to say that you've created a DTS package on, let's say, a dev db server and you now want to export it to your production db server? Mark -Original Message- From: Timothy Heald [mailto:[EMAIL PROTECTED]] Sent: Monday, August 27, 2001 10:03 AM To: CF-Talk Subject: OT: Importing saved DTS in SQL 7 Hey, How do I go about importing a saved DTS once I have it? Tim Heald ACP/CCFD :) Application Development http://www.schoollink.net/ ~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
Server Hangs on NT Shutdown
Installed CF 5.0 on an NT4.0 Server w/ sp6a and running IIS 4.0 whenever I try to shutdown the SiteMinder Authentication Service and the SiteMinder Authorization Service both hang and I have to end task to get the machine to reboot. Anyone have this issue? If so do you know how to resolve it?? Thanks! ~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
