RE: (ot) SQL Server Full-Text Search CONTAINS()
Are there any results that actually have those 3 words near each other ? Russ -Original Message- From: Matt Quackenbush [mailto:quackfu...@gmail.com] Sent: 23 November 2010 18:01 To: cf-talk Subject: (ot) SQL Server Full-Text Search CONTAINS() SQL Server 2005 For the life of me, I cannot figure out why the hell this... WHERE CONTAINS (EventName,' my NEAR chemical NEAR romance ') ...returns 0 results, yet this... WHERE CONTAINS (EventName,' chemical NEAR romance ') ...returns the correct records. Why does my exclude any records? ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:339463 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: (ot) SQL Server Full-Text Search CONTAINS()
Ayep. The band name is My Chemical Romance. I'd say that definitely qualifies as NEAR. :-) ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:339467 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: (ot) SQL Server Full-Text Search CONTAINS()
I'm not overly familiar with NEAR but most of the examples I've seen where NEAR is strung together show it without the quotes. CONTAINS (EventName,'my NEAR chemical NEAR romance') That's probably no help, but I thought I'd share. On Tue, Nov 23, 2010 at 1:00 PM, Matt Quackenbush quackfu...@gmail.comwrote: SQL Server 2005 For the life of me, I cannot figure out why the hell this... WHERE CONTAINS (EventName,' my NEAR chemical NEAR romance ') ...returns 0 results, yet this... WHERE CONTAINS (EventName,' chemical NEAR romance ') ...returns the correct records. Why does my exclude any records? ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:339468 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: (ot) SQL Server Full-Text Search CONTAINS()
@ Michael- Unfortunately, you are correct: That's no help. Same result without the quotes. :-( I know that noise words are ignored, but what appears to be happening is that it is ignoring the entire thing, rather than just the (possible) noise word my. ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:339469 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
RE: (ot) SQL Server Full-Text Search CONTAINS()
Try this: CONTAINS (EventName,' my NEAR chemical AND romance ') I have found that since SQL2000 that the Near operator does weird things. In my search pages we parse the input, remove noise words and if there are more than 2 terms structure the query as term1 NEAR Term2 AND term3 AND term4. Essentially we only use NEAR one in a search and always as the first operator http://support.microsoft.com/kb/888008 http://support.microsoft.com/kb/970909 -Original Message- From: Michael Grant [mailto:mgr...@modus.bz] Sent: Tuesday, November 23, 2010 2:07 PM To: cf-talk Subject: Re: (ot) SQL Server Full-Text Search CONTAINS() I'm not overly familiar with NEAR but most of the examples I've seen where NEAR is strung together show it without the quotes. CONTAINS (EventName,'my NEAR chemical NEAR romance') That's probably no help, but I thought I'd share. On Tue, Nov 23, 2010 at 1:00 PM, Matt Quackenbush quackfu...@gmail.comwrote: SQL Server 2005 For the life of me, I cannot figure out why the hell this... WHERE CONTAINS (EventName,' my NEAR chemical NEAR romance ') ...returns 0 results, yet this... WHERE CONTAINS (EventName,' chemical NEAR romance ') ...returns the correct records. Why does my exclude any records? ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:339472 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: (ot) SQL Server Full-Text Search CONTAINS()
Thanks for the tip on using just a single NEAR keyword. In this particular example (my chemical romance), it still results in no records being returned. The only thing I can figure is that 'my' is a noise word, and all searching is ignored once a noise word is hit. For the time being, I've merely added 'my' to the list of words that are parsed out of the search string, and am getting the desired results. Maybe one day I'll actually have time to figure out what and why. :-) Thanks everyone for your help. ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:339475 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: (ot) SQL Server Full-Text Search CONTAINS()
Matt, I googled on sql server noise words and found this link: http://arcanecode.com/2008/05/29/creating-and-customizing-noise-words-in-sql-server-2005-full-text-search/ I followed the directions and found the noise lookup file being used by SQL Server 2005 on my computer, and confirmed that my is in the list. If you need my to be included in your searches, you could remove it from your list. However, if you ever migrate SQL to another server, you'll have to remember to update the noise list on that server. HTH, Carl Thanks for the tip on using just a single NEAR keyword. In this particular example (my chemical romance), it still results in no records being returned. The only thing I can figure is that 'my' is a noise word, and all searching is ignored once a noise word is hit. For the time being, I've merely added 'my' to the list of words that are parsed out of the search string, and am getting the desired results. Maybe one day I'll actually have time to figure out what and why. :-) Thanks everyone for your help. ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:339476 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: (ot) SQL Server Full-Text Search CONTAINS()
Carl, Thanks for that link. I appreciate it. :-) I guess I am simply totally misunderstanding noise/stop words in SQL Server. My understanding was that they were ignored in a query. But the behavior I am seeing indicates that the entire search string is ignored if such a word is encountered. shrug ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:339477 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
RE: (ot) SQL Server Full-Text Search CONTAINS()
I now recall having this issue myself many years ago, and the reason it doesn't work is because the noise words (now called stop words) are not actually indexed, rather than being stripped from the search which is what you assumed. On SQL 2000 you couldn't even use the stop words period or you get an error, so you had to parse the noise file and strip and noise words out of the CONTAINS clause so that they were not used. Obviously the error no longer occurs since SQL 2005, you just don;t get any search results. Here is a better explanation of full text and stop words for you. http://msdn.microsoft.com/en-us/library/ms142551.aspx -- Russ Michaels http://www.bluethunderinternet.com : B2B hosting, VPS's, Exchange, CF, Railo www.cfmldeveloper.com : CFML community, FREE ColdFusion/Railo hosting http://www.michaels.me.uk : My Blog skype me : russmichaels ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:339478 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: (ot) SQL Server Full-Text Search CONTAINS()
Just an off-the-wall idea. Did you try a different order to the words? I.E. chemical NEAR romance AND my I was speculating what might happen of the 'noise' word came later in the list. ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:339480 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: (ot) SQL Server Full-Text Search CONTAINS()
@ Russ- Thanks for the reply. I've read that page so many times that it's all just gibberish right now. My brain is mush. LOL Suffice it to say, as you stated, when encountered in a FTS search string, noise/stop words block any results from being delivered. @ Ian- I had not tried that prior to your asking, but I have now. No change in the results. @ Everyone- Thank you for your responses. I am putting this one to bed now. :-) ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:339481 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
RE: (ot) SQL Server Full-Text Search CONTAINS()
Matt, I did actually give an explanation of why it doesn't work, perhaps you skipped that. So moving the words around wouldn't work as you are still searching for 3 words near to each other which are not near each other as my is not in your collection index. As I said, you need to parse the noise word list to make sure you do not search on any of them, especially if you are dynamically search on values passed from a form. if you want to search on noise words you will need to remove them from the noise list and re-index your collection, then it should work. Russ -Original Message- From: Matt Quackenbush [mailto:quackfu...@gmail.com] Sent: 23 November 2010 22:07 To: cf-talk Subject: Re: (ot) SQL Server Full-Text Search CONTAINS() @ Russ- Thanks for the reply. I've read that page so many times that it's all just gibberish right now. My brain is mush. LOL Suffice it to say, as you stated, when encountered in a FTS search string, noise/stop words block any results from being delivered. @ Ian- I had not tried that prior to your asking, but I have now. No change in the results. @ Everyone- Thank you for your responses. I am putting this one to bed now. :-) ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:339482 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: (ot) SQL Server Full-Text Search CONTAINS()
Russ, You did, of course, state why, but it did not click until you just reworded it. Thank you! It makes perfect sense now. (And yes, I had already changed to removing noise words from the search string.) ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:339483 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: (ot) sql server 2005 express sp3 installation onto vista os
Actually, I run SQL 2005 Express on my Vista box at home for dev work, and it's actually been fine. SQL Express has its own problems (what do you mean I can't move data with it??), but that's not a Vista issue. ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:325375 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: (ot) sql server 2005 express sp3 installation onto vista os
On Wed, Aug 12, 2009 at 5:19 AM, Don L wrote: Mysql, sql lite, h2, derby = no install sql lite is eliminated for it's not a dbms but a c library for a dbm. of the remaining 3, which one is most compatiable with ms sql server 2005 syntax and feature-wise? PostgreSQL. Supports silent installation, PostgreSQL driver is included in Railo out of the box. And PostgreSQL is licensed under the BSD license so you can use it without fear of having to release your own stuff as open source. Jochem -- Jochem van Dieten http://jochem.vandieten.net/ ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:325383 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: (ot) sql server 2005 express sp3 installation onto vista os
Actually, I run SQL 2005 Express on my Vista box at home for dev work, and it's actually been fine. SQL Express has its own problems (what do you mean I can't move data with it??), but that's not a Vista issue. Could you look up Windows Installer version from registry on your Vista box for me? Probably here are places to start, HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion\Uninstall HKEY_LOCAL_MACHINE\Software\Microsoft\Active Setup\Installed Components Also, what edition of your Vista os? Thanks. ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:325398 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: (ot) sql server 2005 express sp3 installation onto vista os
Looks like it's still Vista SP1, Home Premium Edition. No clue what version of the Installer is in place. There are no entries in those keys that are specific to the installer itself, at least not on my OS. ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:325423 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: (ot) sql server 2005 express sp3 installation onto vista os
Looks like it's still Vista SP1, Home Premium Edition. No clue what version of the Installer is in place. There are no entries in those keys that are specific to the installer itself, at least not on my OS. Good to know, thanks. Those two registry branches are pretty standard for XP probably Vista is a quite different beast, could you run a key word search for Windows Installer at the computer level across all registries with regedit for me? Would greatly appreciate it. ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:325424 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: (ot) sql server 2005 express sp3 installation onto vista os
On Wed, Aug 12, 2009 at 5:19 AM, Don L wrote: Mysql, sql lite, h2, derby = no install sql lite is eliminated for it's not a dbms but a c library for a dbm. of the remaining 3, which one is most compatiable with ms sql server 2005 syntax and feature-wise? PostgreSQL. Supports silent installation, PostgreSQL driver is included in Railo out of the box. And PostgreSQL is licensed under the BSD license so you can use it without fear of having to release your own stuff as open source. Jochem thanks for the input. Jochem van Dieten http://jochem.vandieten. net/ ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:325425 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: (ot) sql server 2005 express sp3 installation onto vista os
Out of curiosity, after all the problems you have had with MSSQL Express and all the alternatives that have been offered. Are you going to continue to use MSSQL Express? Or will you try using one of the alternatives? Best Regards. Emmit On Wed, Aug 12, 2009 at 6:27 PM, Don L do...@yahoo.com wrote: On Wed, Aug 12, 2009 at 5:19 AM, Don L wrote: Mysql, sql lite, h2, derby = no install sql lite is eliminated for it's not a dbms but a c library for a dbm. of the remaining 3, which one is most compatiable with ms sql server 2005 syntax and feature-wise? PostgreSQL. Supports silent installation, PostgreSQL driver is included in Railo out of the box. And PostgreSQL is licensed under the BSD license so you can use it without fear of having to release your own stuff as open source. Jochem thanks for the input. Jochem van Dieten http://jochem.vandieten. net/ ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:325427 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: (ot) sql server 2005 express sp3 installation onto vista os
FYI, a) Project deadline is days away. One should probably not to change horse while crossing a river (of course another could argue should you really need to cross the river?) b) The randomly reported error may not accurately reflect sql server 2005 express sp3 installation success or failure with Vista. Too bad MS is not very accurate on this, only a small number of users has this (installation) problem, also, in rare situation it would crash a user's computer, shameful. c) I have another option. d) Converting the app from cf8 to railo has already caused me quite a bit of pain not because railo has some real issue but because of misunderstanding or difference in expectation. Would like to avoid this especially now. e) I leave the option open to move away from MS DBMS but right now timing is way too bad. But if MS is on my back then hell I would be forced to take action. Then who the heck am I? Kidding myself! f) Intend to publish the db schema (or most of them) in case other cfers would like to extend the app. But definitely not now. Out of curiosity, after all the problems you have had with MSSQL Express and all the alternatives that have been offered. Are you going to continue to use MSSQL Express? Or will you try using one of the alternatives? Best Regards. Emmit On Wed, Aug 12, 2009 at 6:27 PM, D ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:325428 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: (ot) sql server 2005 express sp3 installation onto vista os
Mysql, sql lite, h2, derby = no install All of which have rich SQL dialects and are very capable. Problem solved! G! On Tue, Aug 11, 2009 at 9:47 PM, Don L do...@yahoo.com wrote: I'd like to hear people's experience on this, my users/customers reported tons of problem with it while generally positive on XP. I don't have vista box myself, can't test it on my own. thks. ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:325363 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: (ot) sql server 2005 express sp3 installation onto vista os
sql lite is eliminated for it's not a dbms but a c library for a dbm. of the remaining 3, which one is most compatiable with ms sql server 2005 syntax and feature-wise? do they all support quiet installation? do h2 and derby support dll as well? how about creating datasource connection to railo cfml engine? fk ms! it made me so mad (their own os, their own dbms, all fucked up!) Mysql, sql lite, h2, derby = no install All of which have rich SQL dialects and are very capable. Problem solved! G! On Tue, Aug 11, 2009 at 9:47 PM, D I'd like to hear people's experience on this, my users/customers reported tons of problem with it while generally positive on XP. I don't have vista box myself, can't test it on my own. thks. ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:325364 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: (ot) SQL Server disk configuration
On Wed, Mar 11, 2009 at 3:01 AM, Rick Root wrote: The server has 4x146gb scsi drives set up in 2 RAID-1 mirrored sets. We ended up with a 20 gig C drive and a 120gb D: drive on one mirrored set, and 70 gig E: and F: drives on the other mirrored set. Currently we've got our SQL Server data files on the D: drive, the transaction logs on the E: drive, and the backups on the F: drive. The database is relatively small (7gb) but used pretty heavily.. Used how? Updates, inserts and deletes or just selects? How many of each per second? And which software exactly do you mean by SQL Server? I have the ability to add four more drives to the machine... Why would you? What is the current bottleneck and how would adding drives help? Jochem -- Jochem van Dieten http://jochem.vandieten.net/ ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:320346 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: OT: SQL Server Accessing Excel file.
Is there any way you can copy the file and use the copy? That was my first thought. On Mon, Mar 10, 2008 at 8:29 AM, DURETTE, STEVEN J (ATTASIAIT) [EMAIL PROTECTED] wrote: Hi all, Sorry for the off topic, but this has been a real pain in my behind. We have a process were our SQL server reads in data from an Excel file. From there the data is used in various CF pages. The problem is that the file that we import data from is opened by users to make changes before the next data load. A few of them, when it's their turn to edit the file, open the file and leave it open and leave for the day. They are are internal customer and threats of corporate policy violation, going to their bosses, etc have no effect. No matter if they have the file open or not, if the data doesn't load, it's considered our fault. Does anyone know of anyway to force SQL Server 2000 to open an excel file in read only mode? That way I can get the data and not have to worry anymore. I googled it, but I didn't find much, except how to track down who has the file open. (Which doesn't help me). Thanks, Steve Steve Durette SR IT Analyst 586-466-7654 [EMAIL PROTECTED] ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;160198600;22374440;w Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:300845 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
RE: OT: SQL Server Accessing Excel file.
I'll give that a try and see what happens. -Original Message- From: Gerald Guido [mailto:[EMAIL PROTECTED] Sent: Monday, March 10, 2008 9:09 AM To: CF-Talk Subject: Re: OT: SQL Server Accessing Excel file. Is there any way you can copy the file and use the copy? That was my first thought. On Mon, Mar 10, 2008 at 8:29 AM, DURETTE, STEVEN J (ATTASIAIT) [EMAIL PROTECTED] wrote: Hi all, Sorry for the off topic, but this has been a real pain in my behind. We have a process were our SQL server reads in data from an Excel file. From there the data is used in various CF pages. The problem is that the file that we import data from is opened by users to make changes before the next data load. A few of them, when it's their turn to edit the file, open the file and leave it open and leave for the day. They are are internal customer and threats of corporate policy violation, going to their bosses, etc have no effect. No matter if they have the file open or not, if the data doesn't load, it's considered our fault. Does anyone know of anyway to force SQL Server 2000 to open an excel file in read only mode? That way I can get the data and not have to worry anymore. I googled it, but I didn't find much, except how to track down who has the file open. (Which doesn't help me). Thanks, Steve Steve Durette SR IT Analyst 586-466-7654 [EMAIL PROTECTED] ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;160198600;22374440;w Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:300855 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: OT: SQL Server Problem
Never mind, I found my solution... take DB offline detach database rename transaction log file re-attach database WITHOUT transaction log SQL Server creates small transaction log file Problem solved! On 7/20/07, Rick Root [EMAIL PROTECTED] wrote: I have a problem with our database server. We have a database that we load from text files every day... the db itself is about 9 gb. The log settings is bulk logged and I don't perform backups. Unfortunately, it still logs SOME stuff... and the transaction log is now 55 gig Problem is... I have no room on the server now to perform a transaction log backup, and I can't shrink the file because it's pretty much all allocated. How do I get rid of the transaction log? Can I detach the database and then re-attach it without the log file? -- Rick Root Check out CFMBB, BlogCFM, ImageCFC, ImapCFC, CFFM, and more at www.opensourcecf.com -- Rick Root Check out CFMBB, BlogCFM, ImageCFC, ImapCFC, CFFM, and more at www.opensourcecf.com ~| CF 8 â Scorpio beta now available, easily build great internet experiences â Try it now on Labs http://www.adobe.com/cfusion/entitlement/index.cfm?e=labs_adobecf8_beta Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:284187 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: OT: SQL Server Import Help Needed
Les, Have you tried copying the cell and doing a Paste SpecialValues? Sometimes, with a cell that's been worked alot Excel stores all sorts of weird stuff in there. I've run into this before on files that were unusually large for what they contained. Doing a Select AllCopyPaste SpecialValues fixed it. That is if you're transferring from an .xls file and not a .csv. HTH, Jon On Jan 16, 2007, at 7:36 PM, Les Mizzell wrote: Trying to import an Excel Spreadsheet into an ntext(16) field using the Enterprise manager. Getting an error: Row Number 22 Data Source Column is too large for specified buffer size. Looking at the Excel Sheet - it's less than a paragraph of text. Going through my CF Admin form to add data to the same table - I can paste PAGES of stuff from Word into the same column with no problem. I'm sorta stumped. What could it be? ~| Create robust enterprise, web RIAs. Upgrade integrate Adobe Coldfusion MX7 with Flex 2 http://ad.doubleclick.net/clk;56760587;14748456;a?http://www.adobe.com/products/coldfusion/flex2/?sdid=LVNU Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:266725 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: OT: SQL Server CPU Utilization maxes at 25%
5GB Every morning?! All new data? This e-mail is from Reed Exhibitions (Oriel House, 26 The Quadrant, Richmond, Surrey, TW9 1DL, United Kingdom), a division of Reed Business, Registered in England, Number 678540. It contains information which is confidential and may also be privileged. It is for the exclusive use of the intended recipient(s). If you are not the intended recipient(s) please note that any form of distribution, copying or use of this communication or the information in it is strictly prohibited and may be unlawful. If you have received this communication in error please return it to the sender or call our switchboard on +44 (0) 20 89107910. The opinions expressed within this communication are not necessarily those expressed by Reed Exhibitions. Visit our website at http://www.reedexpo.com -Original Message- From: Rick Root To: CF-Talk Sent: Fri Sep 01 14:09:32 2006 Subject: OT: SQL Server CPU Utilization maxes at 25% I have a fairly large bulk insert DTS task in SQL Serve that loads 5 gig or so of data into our database every morning. It drops the original tables, bulk inserts the data, then recreates all the indexes. No logging, etc. I'm trying to figure out a way to improve the speed of this process - but the CPU utilization seems to have some kind of artificial limit of 25% See this graph: http://www.it.dev.duke.edu/temp/sqlcpu.gif It seems really odd to me that the CPU usage flatlines at 25%. What might be going on here? Rick ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:251743 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: OT: SQL Server CPU Utilization maxes at 25%
Rick Root wrote: I have a fairly large bulk insert DTS task in SQL Serve that loads 5 gig or so of data into our database every morning. It drops the original tables, bulk inserts the data, then recreates all the indexes. No logging, etc. I'm trying to figure out a way to improve the speed of this process - but the CPU utilization seems to have some kind of artificial limit of 25% See this graph: http://www.it.dev.duke.edu/temp/sqlcpu.gif It seems really odd to me that the CPU usage flatlines at 25%. What might be going on here? Is this perhaps a 4 CPU machine, with Task Manager set to show one graph for all, and with SQL server set to only use one CPU? ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:251745 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: OT: SQL Server CPU Utilization maxes at 25%
Robertson-Ravo, Neil (RX) wrote: 5GB Every morning?! All new data? We're dumping our entire mainframe db2 database every night and FTPing them to the SQL Server. We don't have the ability to do replication, and writing processes on the mainframe that would only dump the data that changed would be far more intensive than just unloading the complete tables. The SQL Server is essentially a data repository, and we don't have any replication tools nor the budget for any. Rick ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:251770 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: OT: SQL Server CPU Utilization maxes at 25%
Jim Wright wrote: Is this perhaps a 4 CPU machine, with Task Manager set to show one graph for all, and with SQL server set to only use one CPU? It's a dual CPU machine with hyperthreading. Yeah, I didn't consider that the single graph would really just show an average. When viewed as 4 CPU graphs, the CPU usage is much more erratic, spiking as high as 90%. I guess it's possible that I'm having I/O issues. I'll have to figure out how to do real performance monitoring to find out for sure =) My DTS package actually consists of about 50 completely independent steps, none of which rely on each other for completion - there's no workflow. Each step consists of a group of Transact-SQL statements - one to drop the existing table, one to recreate the table, one to do the bulk insert from a text file, and then one or more to create indexes. Usually there are 4 or 5 steps running simultaneously. rick ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:251772 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: OT: SQL Server Emergency
On Wednesday 19 July 2006 23:07, Jeff D. Chastain wrote: in the past that by removing the log file SQL server would rebuild a new one and you would only loose whatever transactions were in the log file. Well, unless it was half way through doing something, I suppose it might. Doesn't strike me as a very good plan except if-all-else-fails. I don't suppose undeleting the log works ? -- Tom Chiverton This email is sent for and on behalf of Halliwells LLP. Halliwells LLP is a limited liability partnership registered in England and Wales under registered number OC307980 whose registered office address is at St James's Court Brown Street Manchester M2 2JF. A list of members is available for inspection at the registered office. Any reference to a partner in relation to Halliwells LLP means a member of Halliwells LLP. Regulated by the Law Society. CONFIDENTIALITY This email is intended only for the use of the addressee named above and may be confidential or legally privileged. If you are not the addressee you must not read it and must not use any information contained in nor copy it nor inform any person other than Halliwells LLP or the addressee of its existence or contents. If you have received this email in error please delete it and notify Halliwells LLP IT Department on 0870 365 8008. For more information about Halliwells LLP visit www.halliwells.com. ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/cf_lists/message.cfm/forumid:4/messageid:247162 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
re: OT: SQL Server Emergency
Okay, I can't detach the database because it is being used in replication. So, I tried to use the stored procedure sp_removedbreplication to stop the replication and I get an error because the database is in recovery/emergency mode. Next Thanks. Sorry for the priority off-topic, but I have a SQL server down and I am running out of ideas. The log file for one of my databases was corrupted. I thought I rememberd in the past that by removing the log file SQL server would rebuild a new one and you would only loose whatever transactions were in the log file. Apparently not. I stopped SQL server, deleted the log file and restarted it. Now the database is marked as suspect. I have tried detaching the database, but the database is/was used in a replication and SQL server won't allow me to detach it. How can I disable the replication in order to detach the database and then reattach it so that it will rebuild a new log file? Thanks. ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/cf_lists/message.cfm/forumid:4/messageid:247130 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: OT: SQL Server- Postgres Distributed Transactions
Nick Han wrote: erver: Msg 7391, Level 16, State 1, Line 1 The operation could not be performed because the OLE DB provider 'MSDASQL' was unable to begin a distributed transaction. [OLE/DB provider returned message: Unsupported connect attribute (Set)] OLE DB error trace [OLE/DB Provider 'MSDASQL' ITransactionJoin::JoinTransaction returned 0x8004d00a]. Which PostgreSQL version? What is in the PostgreSQL log file? Jochem ~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:244523 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
Re: OT: SQL Server- Postgres Distributed Transactions
Nick Han wrote: Does anyone know if there is a solution to making transactions work, when using remote calls from SQL Server to Postgres? We have a link server setup and we are able to push data over to Postgres from SQL Server. What we are having a problem with is when trying to wrap transactions around the calls, the transaction fails. Are you using 2-phase commit? Which versions? What error? Jochem ~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:244493 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
RE: OT: SQL Server- Postgres Distributed Transactions
Jochem, This is the error msg I get, erver: Msg 7391, Level 16, State 1, Line 1 The operation could not be performed because the OLE DB provider 'MSDASQL' was unable to begin a distributed transaction. [OLE/DB provider returned message: Unsupported connect attribute (Set)] OLE DB error trace [OLE/DB Provider 'MSDASQL' ITransactionJoin::JoinTransaction returned 0x8004d00a]. -Original Message- From: Jochem van Dieten [mailto:[EMAIL PROTECTED] Sent: Wednesday, June 21, 2006 4:31 PM To: CF-Talk Subject: Re: OT: SQL Server- Postgres Distributed Transactions Nick Han wrote: Does anyone know if there is a solution to making transactions work, when using remote calls from SQL Server to Postgres? We have a link server setup and we are able to push data over to Postgres from SQL Server. What we are having a problem with is when trying to wrap transactions around the calls, the transaction fails. Are you using 2-phase commit? Which versions? What error? Jochem ~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:244502 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
Re: OT: SQL Server + internal/external IPs
On 10/4/05, Bob Haroche [EMAIL PROTECTED] wrote: Are you sure your router supports loopback? I had the same issue where I couldn't browse to my dev sites using their external IP's when I'm on my LAN, though I can when I'm outside my LAN. Nah, that's definitely not it - I can browse the dev sites, remote desktop, FTP using both internal and external - it was just SQL Server that wasn't playing along. We actually worked around the problem by adding an internal network adapter to the Virtual Server machine and doing the required DTS operations locally, then removing the extra adapter when we were finished. I think it must be something to do with how SQL Server was installed but as I fixed the problem I don't have time to investigate further. Big Meh! -- Kay Smoljak http://kay.zombiecoder.com/ ~| Discover CFTicket - The leading ColdFusion Help Desk and Trouble Ticket application http://www.houseoffusion.com/banners/view.cfm?bannerid=48 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:220289 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
Re: OT: SQL Server + internal/external IPs
On Oct 4, 2005, at 12:11 AM, Kay Smoljak wrote: The machine has two network interfaces - an internal 10.0.0.n IP and an external 203.n.n.n IP. I can connect to the local 10.0.0.n IP using Enterprise Manager no problems - but not using the external IP Are you sure your router supports loopback? I had the same issue where I couldn't browse to my dev sites using their external IP's when I'm on my LAN, though I can when I'm outside my LAN. I have a SOHO Netgear router and the manufacturer confirmed that routers of that grade do not support internal loopback like I was trying to do. My solution was to change by hosts file to point to the server like so: www.client.dev.mydomain.com 192.168.0.x.x So now I can browse to the server but I'm never leaving the LAN. -- Regards, Bob Haroche OnPoint Solutions www.OnPointSolutions.com ~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:220011 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
Re: OT : sql server roles help
actually, if in the query. I give the table owners name before the table. I can see it. So somehow the login doesnt know enough to look at the tables owner. Hmm. Anybody? On 5/25/05, DRE [EMAIL PROTECTED] wrote: Hi, I'm not that familiar with sql server but I've got a database that I can connect to. However, cf cant see a table. I know this has to do with roles and permissions and stuff like that but I just cant seem to get it right. Anybody got any tips? Thanks. -- DRE www.webmachineinc.com www.theanticool.com -- DRE www.webmachineinc.com www.theanticool.com ~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:207684 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
RE: OT : sql server roles help
actually, if in the query. I give the table owners name before the table. I can see it. So somehow the login doesnt know enough to look at the tables owner. Hmm. Anybody? If the login is different from the actual table owner, you will have to specify the table owner within the table name reference. Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ Fig Leaf Software provides the highest caliber vendor-authorized instruction at our training centers in Washington DC, Atlanta, Chicago, Baltimore, Northern Virginia, or on-site at your location. Visit http://training.figleaf.com/ for more information! ~| Find out how CFTicket can increase your company's customer support efficiency by 100% http://www.houseoffusion.com/banners/view.cfm?bannerid=49 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:207689 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
Re: OT : sql server roles help
Hmm, so how do I create a login thats the same as the table owner? I tried the login wizard and used the same name as the table owner and now have two users with the same name. Both seem to be the table owners but neither seems to be a login! I'm sure I'm close! Thanks for any help in advance. DRE On 5/25/05, Dave Watts [EMAIL PROTECTED] wrote: actually, if in the query. I give the table owners name before the table. I can see it. So somehow the login doesnt know enough to look at the tables owner. Hmm. Anybody? If the login is different from the actual table owner, you will have to specify the table owner within the table name reference. Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ Fig Leaf Software provides the highest caliber vendor-authorized instruction at our training centers in Washington DC, Atlanta, Chicago, Baltimore, Northern Virginia, or on-site at your location. Visit http://training.figleaf.com/ for more information! ~| Find out how CFTicket can increase your company's customer support efficiency by 100% http://www.houseoffusion.com/banners/view.cfm?bannerid=49 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:207694 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
RE: OT : sql server roles help
Hmm, so how do I create a login thats the same as the table owner? I tried the login wizard and used the same name as the table owner and now have two users with the same name. Both seem to be the table owners but neither seems to be a login! I'm sure I'm close! Thanks for any help in advance. Logins are server-wide. Once you have a login, you can assign it to a user or role within a specific database. I've never used the create login wizard, so I can't help you there, but once you have a login you can go to the specific database within Enterprise Manager, go to Users, and assign the user to the login. Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ Fig Leaf Software provides the highest caliber vendor-authorized instruction at our training centers in Washington DC, Atlanta, Chicago, Baltimore, Northern Virginia, or on-site at your location. Visit http://training.figleaf.com/ for more information! ~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:207698 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
Re: OT: SQL Server forum/list
Rick Root wrote: I'm looking for recommendations on a good SQL Server list or forum. http://vyaskn.tripod.com/lists.htm lots of smart folks on SSWUG.org (though i can't seem to get to it just now). however some of the MVP from that list are now more actve in the ms forums. ~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:194713 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
Re: OT: SQL Server forum/list
I enjoy SQLServerCentral.com -Mike Chabot Rick Root wrote: I'm looking for recommendations on a good SQL Server list or forum. ~| Discover CFTicket - The leading ColdFusion Help Desk and Trouble Ticket application http://www.houseoffusion.com/banners/view.cfm?bannerid=48 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:194735 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
Re: OT: SQL Server forum/list
There's also a SQL list on House of Fusion that has some very knowledgeable people, though it is low traffic. Okay... since my request for a good oracle list/forum was successful, here's another one. I'm looking for recommendations on a good SQL Server list or forum. We're going to be doing a lot of work with SQL Server coming up soon, and I'm working on importing large amounts of data (5 gigs or so of data to be imported every night). I'm gonna need some help making it occur as quickly as possible =) - Rick ~| Find out how CFTicket can increase your company's customer support efficiency by 100% http://www.houseoffusion.com/banners/view.cfm?bannerid=49 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:194807 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
Re: OT: SQL Server mirroring
Dustin Snell [Network Automation] wrote: I know there is clustercats for ColdFusion but we want to do the database server first. Everything I am reading says you need at least *three* machines to do a simple mirror with SQL Server, is this true?? Depends on which definition of mirroring / clustering / replication / failover you use and whether the purpose is reduncancy or performance. For resilient eager multimaster replication you would need at least 3 systems so that as long as 2 systems can agree with eachother they have a quorum and can proceed. If you have only 2 systems and they lose their connection they can each get conflicting updates and they have no way to determine which one gets precedent, effectively deadlocking both systems. We would like to have two machines, machine A and Machine B. Machine B stays in synch with A and only goes live if Machine A is out of service. Why would this need and more than two machines? This is typically called a hot standby configuration. If it is implemented with a shared disk array you need only 2 systems, if you can afford to potentially loose one or a few transactions you can implement it with 2 shared-nothing systems. Jochem ~| Discover CFTicket - The leading ColdFusion Help Desk and Trouble Ticket application http://www.houseoffusion.com/banners/view.cfm?bannerid=48 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:194579 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
Re: OT: SQL Server Licensing
no - it just went to the per CPU licensing model, john. We are considering migrating a number of web sites, RIAs, and a client server application from a variety of databases, including Oracle, MySQL, and Access, to SQL Server. Last time I used SQL Server (long time ago), there was an internet connector license required if you were going to have clients accessing database info via a web server (which bugged me because technically, the web server is the client). Does Microsoft still use this kind of licensing methodology? - RIck ~| Discover CFTicket - The leading ColdFusion Help Desk and Trouble Ticket application http://www.houseoffusion.com/banners/view.cfm?bannerid=48 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:192985 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
RE: OT - SQL Server Linked Server MS Access
I might be mistaken, but I think someone I work with did this a while back, it's the evening here so I'll have to ask tomorrow. What error are you getting? Ade -Original Message- From: S. Isaac Dealey [mailto:[EMAIL PROTECTED] Sent: 15 December 2004 20:31 To: CF-Talk Subject: OT - SQL Server Linked Server MS Access Does anyone on this list have experience connecting to an Access DB within SQL Server as a linked server? I can't seem to make sense of the errors being produced. s. isaac dealey -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.296 / Virus Database: 265.5.4 - Release Date: 15/12/2004 ~| Special thanks to the CF Community Suite Gold Sponsor - CFHosting.net http://www.cfhosting.net Message: http://www.houseoffusion.com/lists.cfm/link=i:4:187939 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
RE: OT - SQL Server Linked Server MS Access
Wow you're good -- either that or I was really distracted and tired yesterday... I just found the answer in the SQL Server BOL ... did a search for msg 7399 and found it... boy is that obscure tho... in order to connect to an insecure database, you have to create a linked server login with username Admin and no password -- and then to drop them, you specify sp_droplinkedserver 'linkedservername', 'droplogins' -- but the 2nd argument to sp_droplinkedserver is not a login string, a list of logins, etc -- it's literally, the hard-coded literal string value 'droplogins'... so the logic is 'droplogins' = true null = false I usually use 0/1 or true/false for that... :-/ Anyway, thanks ade I might be mistaken, but I think someone I work with did this a while back, it's the evening here so I'll have to ask tomorrow. What error are you getting? Ade -Original Message- From: S. Isaac Dealey [mailto:[EMAIL PROTECTED] Sent: 15 December 2004 20:31 To: CF-Talk Subject: OT - SQL Server Linked Server MS Access Does anyone on this list have experience connecting to an Access DB within SQL Server as a linked server? I can't seem to make sense of the errors being produced. s. isaac dealey -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.296 / Virus Database: 265.5.4 - Release Date: 15/12/2004 ~| Special thanks to the CF Community Suite Silver Sponsor - CFDynamics http://www.cfdynamics.com Message: http://www.houseoffusion.com/lists.cfm/link=i:4:187955 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
RE: OT - SQL Server Linked Server MS Access
Thanks Ade, I'm actually able to create the linked server okay, but I get this when I try to select anything from it. There's no password on the access db, so I'm not sure why it would fail authentication with nothing to authenticate. Server: Msg 7399, Level 16, State 1, Line 2 OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error. Authentication failed. [OLE/DB provider returned message: Cannot start your application. The workgroup information file is missing or opened exclusively by another user.] OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0' IDBInitialize::Initialize returned 0x80040e4d: Authentication failed.]. I might be mistaken, but I think someone I work with did this a while back, it's the evening here so I'll have to ask tomorrow. What error are you getting? Ade -Original Message- From: S. Isaac Dealey [mailto:[EMAIL PROTECTED] Sent: 15 December 2004 20:31 To: CF-Talk Subject: OT - SQL Server Linked Server MS Access Does anyone on this list have experience connecting to an Access DB within SQL Server as a linked server? I can't seem to make sense of the errors being produced. s. isaac dealey -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.296 / Virus Database: 265.5.4 - Release Date: 15/12/2004 ~| Special thanks to the CF Community Suite Silver Sponsor - RUWebby http://www.ruwebby.com Message: http://www.houseoffusion.com/lists.cfm/link=i:4:187948 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
Re: OT: sql server: SQLTransaction
Just for the record, things seemed to quiten down after i rebuilt all the statistics. AskJeeves spider was hitting the server in the particular way which was causing the problems: there's a big cfquery with loads of conditional stuff (cfif somethingand someCol=whatever/cfif), and using particular search criteria was causing the query to run 00's of times slower than normal. All the same SQL was run in query analyser it ran fast. Removing the cfqueryparam tags also made the query run fast, so i ran sp_updatestats, and everything ran fast again - hurrah! I suppose lessons would be to run sp_updatestats before anything else. And that the AskJeeves spider likes SES urls. Cheers Bert On Tue, 7 Dec 2004 13:13:48 +, Bert Dawson [EMAIL PROTECTED] wrote: Are you sure the username is the one that is used from CF then? Yes, it definately the CF user - when i started looking at this i created a new new SQL login, and the only place this is used is in the DSN set up in CFadmin. How would you suggest i use a manual checkpoint? Just open up query analyser and run CHECKPOINT? Yes. I'll give that a go... Also, have just profiler again, over a period of about 10 minutes when there appeared to be no abnormal activity (according to CPU usage), and there are still times where the number of SQLtransactions appear v high (up to 9,000 in a second), so i'm begining to wonder if this level of SQLTransactions/second is unusual, or just normal activity... I think it would be unusual Thats what i would have thought. They seem to some in pairs, with EventSubSlass: Begin and Commit, and ObjectName: sort_init Do the periods of high activity coincide with the transaction log backups? Nope, they seem to come and go, though sometimes they show up failry regularly, and other times the CPU is just up and down all day I'm begining to think i might need to rebuild some indexes, as there are a few tables which have fairly heavy levels of delete and insert. Do you think fragmente indexes could cause this sort of behaviour? Thanks Bert ~| Special thanks to the CF Community Suite Silver Sponsor - RUWebby http://www.ruwebby.com Message: http://www.houseoffusion.com/lists.cfm/link=i:4:187528 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
Re: OT: sql server: SQLTransaction
Are you sure the username is the one that is used from CF then? Yes, it definately the CF user - when i started looking at this i created a new new SQL login, and the only place this is used is in the DSN set up in CFadmin. How would you suggest i use a manual checkpoint? Just open up query analyser and run CHECKPOINT? Yes. I'll give that a go... Also, have just profiler again, over a period of about 10 minutes when there appeared to be no abnormal activity (according to CPU usage), and there are still times where the number of SQLtransactions appear v high (up to 9,000 in a second), so i'm begining to wonder if this level of SQLTransactions/second is unusual, or just normal activity... I think it would be unusual Thats what i would have thought. They seem to some in pairs, with EventSubSlass: Begin and Commit, and ObjectName: sort_init Do the periods of high activity coincide with the transaction log backups? Nope, they seem to come and go, though sometimes they show up failry regularly, and other times the CPU is just up and down all day I'm begining to think i might need to rebuild some indexes, as there are a few tables which have fairly heavy levels of delete and insert. Do you think fragmente indexes could cause this sort of behaviour? Thanks Bert ~| Special thanks to the CF Community Suite Silver Sponsor - RUWebby http://www.ruwebby.com Message: http://www.houseoffusion.com/lists.cfm/link=i:4:186433 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
Re: OT: sql server: SQLTransaction
We're not using client variables (default storage in cfadmin=none, clientManagement=No, no DSNs are configured to use client variables, or have the tables in them). How would you suggest i use a manual checkpoint? Just open up query analyser and run CHECKPOINT? Or place it in the code somewhere? Also, have just profiler again, over a period of about 10 minutes when there appeared to be no abnormal activity (according to CPU usage), and there are still times where the number of SQLtransactions appear v high (up to 9,000 in a second), so i'm begining to wonder if this level of SQLTransactions/second is unusual, or just normal activity... 0-99 SQLtransactions/second: 650 100-999 SQLTransactions/sec: 19 1000-1999: 6 2000-2999: 4 3000-3999: 2 4000-4999: 3 5000-5999: 1 6000-6999: 3 7000-7999: 0 8000-8999: 2 9000-: 1 AFAIK the only thing happening to the dBs apart from getting hit by CF is a backup of the transaction logs every 10 minutes, and a full back-up every hour. Any ideas of where to look next? (The underlying probem i'm trying to fix is jrpp.delayMs sometimes going through the roof for no apparent reason, and i suspect the dB, or connections to it, is in someway involved since the areas of the site which are more dB intensive are hit harder by timeouts when there is a problem) Thanks Bert On Fri, 03 Dec 2004 22:03:15 +0100, Jochem van Dieten [EMAIL PROTECTED] wrote: Bert Dawson wrote: I've got a SQL server box that ticks along quite happily at about 15% CPU, but occasionally goes up to around 40% and stays there for anything up to a minute. During these peaks the jrpp.delayMs can start to climb, up to from a few seconds up to hundreds of seconds. I also start getting a few timeouts in the application log. (timeout is se to 35 seconds) Do you get the same behaviour when using a manual checkpoint? I ran a SQL profile trace and spotted that during the peaks there were massive numbers of SQLTransaction entries: over a 2.5 minute period there are ussually about 12 SQLTransactions per second, but this hit a maximm of 16283, which sounds like a lot to me! Are you using client variables? Jochem ~| Special thanks to the CF Community Suite Gold Sponsor - CFHosting.net http://www.cfhosting.net Message: http://www.houseoffusion.com/lists.cfm/link=i:4:186283 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
Re: OT: sql server: SQLTransaction
Bert Dawson wrote: We're not using client variables (default storage in cfadmin=none, clientManagement=No, no DSNs are configured to use client variables, or have the tables in them). Are you sure the username is the one that is used from CF then? How would you suggest i use a manual checkpoint? Just open up query analyser and run CHECKPOINT? Yes. Also, have just profiler again, over a period of about 10 minutes when there appeared to be no abnormal activity (according to CPU usage), and there are still times where the number of SQLtransactions appear v high (up to 9,000 in a second), so i'm begining to wonder if this level of SQLTransactions/second is unusual, or just normal activity... I think it would be unusual 0-99 SQLtransactions/second: 650 100-999 SQLTransactions/sec: 19 1000-1999: 6 2000-2999: 4 3000-3999: 2 4000-4999: 3 5000-5999: 1 6000-6999: 3 7000-7999: 0 8000-8999: 2 9000-: 1 AFAIK the only thing happening to the dBs apart from getting hit by CF is a backup of the transaction logs every 10 minutes, and a full back-up every hour. Do the periods of high activity coincide with the transaction log backups? Jochem ~| Special thanks to the CF Community Suite Silver Sponsor - New Atlanta http://www.newatlanta.com Message: http://www.houseoffusion.com/lists.cfm/link=i:4:186336 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
Re: OT: sql server: SQLTransaction
Just as a guess, maybe a scheduled FTS catalog population? Jerry Jerry Johnson Web Developer Dolan Media Company [EMAIL PROTECTED] 12/03/04 12:01PM If anyone has any ideas what sort of thing could cause just a leap then i'd appreciate any input, or ideas of where to look next. ~| Special thanks to the CF Community Suite Silver Sponsor - New Atlanta http://www.newatlanta.com Message: http://www.houseoffusion.com/lists.cfm/link=i:4:186108 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
Re: OT: sql server: SQLTransaction
I do'n't know anything about the FTS service, and i don't knowingly use it, so i've stopped it. But I doubt that was the cause as there seemed to be no pattern to the 2 minutes @ 30% periods. Also, i suspect its something to do with CF since the loginname reported in profiler is only used in the CF dsn. (i created a new user today specially for CF so that i could eliminate the possibility that someone or something else was causing the trouble.) Any more ideas of things to look for? Cheers Bert On Fri, 03 Dec 2004 12:06:52 -0500, Jerry Johnson [EMAIL PROTECTED] wrote: Just as a guess, maybe a scheduled FTS catalog population? Jerry Jerry Johnson Web Developer Dolan Media Company [EMAIL PROTECTED] 12/03/04 12:01PM If anyone has any ideas what sort of thing could cause just a leap then i'd appreciate any input, or ideas of where to look next. ~| Special thanks to the CF Community Suite Silver Sponsor - New Atlanta http://www.newatlanta.com Message: http://www.houseoffusion.com/lists.cfm/link=i:4:186124 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
Re: OT: sql server: SQLTransaction
Bert Dawson wrote: I've got a SQL server box that ticks along quite happily at about 15% CPU, but occasionally goes up to around 40% and stays there for anything up to a minute. During these peaks the jrpp.delayMs can start to climb, up to from a few seconds up to hundreds of seconds. I also start getting a few timeouts in the application log. (timeout is se to 35 seconds) Do you get the same behaviour when using a manual checkpoint? I ran a SQL profile trace and spotted that during the peaks there were massive numbers of SQLTransaction entries: over a 2.5 minute period there are ussually about 12 SQLTransactions per second, but this hit a maximm of 16283, which sounds like a lot to me! Are you using client variables? Jochem ~| Special thanks to the CF Community Suite Silver Sponsor - RUWebby http://www.ruwebby.com Message: http://www.houseoffusion.com/lists.cfm/link=i:4:186167 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
Re: OT: Sql Server 2005 Express
Why? Because there's no gui yet? I'm setting up a new server this weekend and am planning on plugging in and playing with MSDE, which apparently has a GUI now and does not have the single-processor limit. I have MSDE running elsewhere but as something plugged in by a 3rd-party app (LiveStats). Never tried to use it in production. --Matt Robertson-- President, Janitor MSB Designs, Inc. mysecretbase.com ~| Purchase from House of Fusion, a Macromedia Authorized Affiliate and support the CF community. http://www.houseoffusion.com/banners/view.cfm?bannerid=35 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:183146 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
Re: OT: Sql Server 2005 Express
No GUI is part of it but I couldn't connect to it and I found a work around and didn't feel like messing with it. So I scrapped it :) On Tue, 2 Nov 2004 08:44:00 -0800, Matt Robertson [EMAIL PROTECTED] wrote: Why? Because there's no gui yet? I'm setting up a new server this weekend and am planning on plugging in and playing with MSDE, which apparently has a GUI now and does not have the single-processor limit. I have MSDE running elsewhere but as something plugged in by a 3rd-party app (LiveStats). Never tried to use it in production. --Matt Robertson-- President, Janitor MSB Designs, Inc. mysecretbase.com ~| Sams Teach Yourself Regular Expressions in 10 Minutes by Ben Forta http://www.houseoffusion.com/banners/view.cfm?bannerid=40 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:183152 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
Re: OT: Sql Server 2005 Express
I scrapped it after about 5 minutes of use On Sun, 31 Oct 2004 21:32:47 -0500, Michael T. Tangorre [EMAIL PROTECTED] wrote: Anyone download the SQL 2005 Express beta from MS yet? If so, where in the heck do you create a new DB? The new SQL Computer Manager is a bit confusing. Thanks. Mike ~| Purchase from House of Fusion, a Macromedia Authorized Affiliate and support the CF community. http://www.houseoffusion.com/banners/view.cfm?bannerid=38 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:183062 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
Re: OT: SQL Server Table Design Question
On Mar 18, 2004, at 5:04 AM, Tangorre, Michael wrote: What is the proper, or preferred way of doing the following: I have three tables: T1 - PK A T2 - PK B T3 - PK AB (compound) Is it acceptable to add column C to T3 and make that the PK, and then add a unique constraint to AB? This would ease writing in writing of the WHERE clauses when updating and deleting and such... Thoughts? So, C is an identity or autonumber field? I have seen/done this, but not for the reasons you state -- could you expand on how using C would ease writing of the where clause Dick [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
RE: OT: SQL Server Table Design Question
yeah, the question is a bit vague -Original Message- From: Dick Applebaum [mailto:[EMAIL PROTECTED] Sent: Thursday, March 18, 2004 8:44 AM To: CF-Talk Subject: Re: OT: SQL Server Table Design Question On Mar 18, 2004, at 5:04 AM, Tangorre, Michael wrote: What is the proper, or preferred way of doing the following: I have three tables: T1 - PK A T2 - PK B T3 - PK AB (compound) Is it acceptable to add column C to T3 and make that the PK, and then add aunique constraint to AB? This would ease writing in writing of the WHEREclauses when updating and deleting and such... Thoughts? So, C is an identity or autonumber field? I have seen/done this, but not for the reasons you state -- could you expand on how using C would ease writing of the where clause Dick [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
RE: OT: SQL Server Table Design Question
Basically, I only gave you a partial story.. The rest of it is that the join tables are used in other relationships and the where conditions and join conditions are getting sloppy. In addition, a lot of the front end deals with dynamic forms and passing around a handful of IDs is getting cumbersome. Does that tip the scale either way? Mike So, C is an identity or autonumber field? I have seen/done this, but not for the reasons you state -- could you expand on how using C would ease writing of the where clause [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
Re: OT: SQL Server Table Design Question
I tend to agree with Dave and opt for not adding Column C If I understand, you want to deal with C as an abstractionof the A-B relationship.I can see how passingC (instead of A B) would simplify form handling. But, you could pass AB just as easily as you can pass C. I think introducing C in your joins will just complicate them further as you will *still* need to specify T1.a = T3.a AND T3.b = T2.b relationship, somehow. Your code and SQL will be harder to read, explainand understand with this C thingie. So, it appears to be a tradeoff: denormalize added complexity vs convenience IMO, it isn't worth it HTH Dick On Mar 18, 2004, at 6:18 AM, Tangorre, Michael wrote: Basically, I only gave you a partial story.. The rest of it is that the join tables are used in other relationships and the where conditions and join conditions are getting sloppy. In addition, a lot of the front end deals with dynamic forms and passing around a handful of IDs is getting cumbersome. Does that tip the scale either way? Mike So, C is an identity or autonumber field? I have seen/done this, but not for the reasons you state -- could you expand on how using C would ease writing of the where clause [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
Re: OT: SQL Server Table Design Question
Just to play devil's advocate and also enjoy the unusual experience of disagreeing with Dave - your proposed approach of adding a surrogate key is our standard way of doing things. Even when the table is a simple intersection table consisting of nothing but 2 foreign keys, we always make the primary key an IDENTITY/autoincrement field. We never use a compound key as the primary key (though of course we use them as secondary keys). Why? Because it maximizes the amount of our code (including our automated ER-modelling-to-schema-generation app) that can work exactly the same way for different tables and even different applications. In conjunction with rigid standards for table column naming, it adds up to more productivity in the long run. Nick [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
RE: OT: SQL Server Table Design Question
I agree, we do the same thing here. TK -Original Message- From: Nick de Voil [mailto:[EMAIL PROTECTED] Sent: Thursday, March 18, 2004 10:26 AM To: CF-Talk Subject: Re: OT: SQL Server Table Design Question Just to play devil's advocate and also enjoy the unusual experience of disagreeing with Dave - your proposed approach of adding a surrogate key is our standard way of doing things. Even when the table is a simple intersection table consisting of nothing but 2 foreign keys, we always make the primary key an IDENTITY/autoincrement field. We never use a compound key as the primary key (though of course we use them as secondary keys). Why? Because it maximizes the amount of our code (including our automated ER-modelling-to-schema-generation app) that can work exactly the same way for different tables and even different applications. In conjunction with rigid standards for table column naming, it adds up to more productivity in the long run. Nick [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
Re: OT: SQL Server Table Design Question
On Mar 18, 2004, at 7:25 AM, Nick de Voil wrote: Just to play devil's advocate and also enjoy the unusual experience of disagreeing with Dave - your proposed approach of adding a surrogate key is our standard way of doing things. Even when the table is a simple intersection table consisting of nothing but 2 foreign keys, we always make the primary key an IDENTITY/autoincrement field. We never use a compound key as the primary key (though of course we use them as secondary keys). Why? Because it maximizes the amount of our code (including our automated ER-modelling-to-schema-generation app) that can work exactly the same way for different tables and even different applications. In conjunction with rigid standards for table column naming, it adds up to more productivity in the long run. Now, the above are valid reasons for including a separate key -- another might be that the T3 record contains intersection data, and/or it is sometimes meaningful to process this table by itself -- where the A B fields are reference data only; Dick [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
RE: OT: SQL Server Table Design Question
What are some of the downsides to this? Will I experience a performance hit should I use the additional key? Now, the above are valid reasons for including a separate key -- another might be that the T3 record contains intersection data, and/or it is sometimes meaningful to process this table by itself -- where the A B fields are reference data only; [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
Re: OT: SQL Server Table Design Question
You should not experience any performance hit. The only real downside is what Dave was talking about -- unless you have a good reason, why clutter up the database with an extra field and indexes -- the law of parsimony -- the simplest way is the best way. But, if you *do* have a good reason (as Nick did) then I am all for breaking the rules, when justified. Say, you have 3 dbs Customer Product ProductAndCustomer ProductAndCustomer is an intersection of the other 2 ProductAndCustomerIDPK Identity (autonumber) CustomerID FK ProductIDFK DateandTime OtherIntersectionData In our app, a customer can reorder a product many times so there can be many intersections for a given customer and product The combo of CustomerID and ProductID can't be used for a PK as it is not unique. We could use the timestamp as an uniquifer, but that's not a good choice. Setting up a separate ID field makes good sense. Further, when doing drill-downs, we can process the intersection records alone, or with one (or both) sides of the intersection. Dick On Mar 18, 2004, at 9:23 AM, Tangorre, Michael wrote: What are some of the downsides to this? Will I experience a performance hit should I use the additional key? Now, the above are valid reasons for including a separate key -- another might be that the T3 record contains intersection data, and/or it is sometimes meaningful to process this table by itself -- where the A B fields are reference data only; [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
RE: OT - SQL server 2000 - total number of objects in a DB
You're supposed to use system functions or stored procedures to access this, but check out the sysobjects table. You could write a count() query to get what you need out of that. Otherwise look in the books-online under system functions to do it the right way, I don't know if there's already a function that will return the number of objects in a given DB. Adam. -Original Message- From: Andres [mailto:[EMAIL PROTECTED]] Sent: Tuesday, January 28, 2003 8:28 AM To: CF-Talk Subject: OT - SQL server 2000 - total number of objects in a DB Hello Everyone, How can i get a count of all indexes a database has? I have a SQL 2000 server and need to get a count of various objects (tables, indexes, triggers, view, SPs) Does anyone know of a way to get these counts WITHOUT having to do it manually? Thank you! Andres ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribeforumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Get the mailserver that powers this list at http://www.coolfusion.com Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: OT - SQL server 2000 - total number of objects in a DB
-Original Message- From: Andres [mailto:[EMAIL PROTECTED]] Sent: Tuesday, January 28, 2003 9:28 AM To: CF-Talk Subject: OT - SQL server 2000 - total number of objects in a DB Hello Everyone, How can i get a count of all indexes a database has? I have a SQL 2000 server and need to get a count of various objects (tables, indexes, triggers, view, SPs) Does anyone know of a way to get these counts WITHOUT having to do it manually? You can use SP_HELP DatabaseName to get almost all of this. There are other system procedures for everything else... But I don't know them off the top of my head. Look in the books online and they'll all be there. Jim Davis ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribeforumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Get the mailserver that powers this list at http://www.coolfusion.com Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: OT: SQL Server database problem
Quoting Adams, Stephen [EMAIL PROTECTED]: The problem I am having is a ODBC Error Code = 22001 (String data right truncation) error. My table design is like this. Column name DatatypeLength MessageID int 4 MessageType char20 Department char20 CreationDatechar15 PublicationDate char15 MessageTitletext16 MessageTeaser text16 MessageBody text16 I think that the problem is when the text is being added to the fields, MessageTitle, MessageTeaser and MessageBody. 'Some text to throw error' is indeed longer as 16 characters. Not entirely unexpected behaviour, isn't it? Jochem ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/index.cfm?sidebar=listsbody=lists/cf_talk FAQ: http://www.thenetprofits.co.uk/coldfusion/faq This list and all House of Fusion resources hosted by CFHosting.com. The place for dependable ColdFusion Hosting.
Re: OT: SQL Server database problem
You should either trim the text being inserted using the left function ... EXAMPLE: Left(form.MessageTeaser, 16 ) or you should expand the size of the field in the table. If this is data input via a form you could use the size option in the input tag to enforce the size restriction but I would still trim the data with the left function for safety sake. EXAMPLE: input type=Text name=MessageTeaser size=16 -John- At 02:58 PM 10/28/2002 +, you wrote: ** WESTMINSTER CITY COUNCIL Please refer to the disclaimer beneath this message ** Hi, I have a SQL Server 7 database and I am trying to insert a large amount of text, passed by a ColdFusion form, into a table within this database. The problem I am having is a ODBC Error Code = 22001 (String data right truncation) error. My table design is like this. Column name DatatypeLength MessageID int 4 MessageType char20 Department char20 CreationDatechar15 PublicationDate char15 MessageTitletext16 MessageTeaser text16 MessageBody text16 I think that the problem is when the text is being added to the fields, MessageTitle, MessageTeaser and MessageBody. This is the complete error message: ODBC Error Code = 22001 (String data right truncation) [Microsoft][ODBC SQL Server Driver][SQL Server]String or binary data would be truncated. SQL = INSERT INTO Messages ( MessageType, Department, CreationDate, PublicationDate, MessageTitle, MessageTeaser, MessageBody, Status ) VALUES ( 'None', 'Housing', '28/10/2002', 'Awaiting', 'Some text to throw error', 'Some text to throw error, Some text to throw error', 'Some text to throw error,Some text to throw error and Some text to throw error', 'Awating Approval' ) Data Source = WIRE_USERS The error occurred while processing an element with a general identifier of (CFQUERY), occupying document position (24:1) to (24:57) in the template file E:\INETPUB\WWWROOT\WIRE\MESSAGES\SENDFORAPPROVAL.CFM. Anyone know how I can fix this error? Thanks Stephen ** Westminster City Council switchboard: +44 20 7641 6000 ** This E-Mail may contain information which is privileged, confidential and protected from disclosure. If you are not the intended recipient of this E-mail or any part of it, please telephone Westminster City Council immediately on receipt. You should not disclose the contents to any other person or take copies. ** ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/index.cfm?sidebar=listsbody=lists/cf_talk FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm
RE: OT: SQL Server database problem
** WESTMINSTER CITY COUNCIL Please refer to the disclaimer beneath this message ** O.K I have increased the length of these three fields to 100, 150 and 500 respectively, but still I get the 22001 error. -Original Message- From: Jochem van Dieten [mailto:jochemd;oli.tudelft.nl] Sent: 28 October 2002 15:05 To: CF-Talk Subject: Re: OT: SQL Server database problem Quoting Adams, Stephen [EMAIL PROTECTED]: The problem I am having is a ODBC Error Code = 22001 (String data right truncation) error. My table design is like this. Column name DatatypeLength MessageID int 4 MessageType char20 Department char20 CreationDatechar15 PublicationDate char15 MessageTitletext16 MessageTeaser text16 MessageBody text16 I think that the problem is when the text is being added to the fields, MessageTitle, MessageTeaser and MessageBody. 'Some text to throw error' is indeed longer as 16 characters. Not entirely unexpected behaviour, isn't it? Jochem ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/index.cfm?sidebar=listsbody=lists/cf_talk FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Get the mailserver that powers this list at http://www.coolfusion.com
Re: OT: SQL Server database problem
I think the error message is straight forward, that is you are trying to insert string that's longer than your datatype can handle. For example: Field: MessageTitle (16): You are trying to insert: Some text to throw error which has 24 characters. Try to increase the length of the field in your DB. HTH, Rizal At 09:58 PM 10/28/2002, you wrote: ** WESTMINSTER CITY COUNCIL Please refer to the disclaimer beneath this message ** Hi, I have a SQL Server 7 database and I am trying to insert a large amount of text, passed by a ColdFusion form, into a table within this database. The problem I am having is a ODBC Error Code = 22001 (String data right truncation) error. My table design is like this. Column name DatatypeLength MessageID int 4 MessageType char20 Department char20 CreationDatechar15 PublicationDate char15 MessageTitletext16 MessageTeaser text16 MessageBody text16 I think that the problem is when the text is being added to the fields, MessageTitle, MessageTeaser and MessageBody. This is the complete error message: ODBC Error Code = 22001 (String data right truncation) [Microsoft][ODBC SQL Server Driver][SQL Server]String or binary data would be truncated. SQL = INSERT INTO Messages ( MessageType, Department, CreationDate, PublicationDate, MessageTitle, MessageTeaser, MessageBody, Status ) VALUES ( 'None', 'Housing', '28/10/2002', 'Awaiting', 'Some text to throw error', 'Some text to throw error, Some text to throw error', 'Some text to throw error,Some text to throw error and Some text to throw error', 'Awating Approval' ) Data Source = WIRE_USERS The error occurred while processing an element with a general identifier of (CFQUERY), occupying document position (24:1) to (24:57) in the template file E:\INETPUB\WWWROOT\WIRE\MESSAGES\SENDFORAPPROVAL.CFM. Anyone know how I can fix this error? Thanks Stephen ** Westminster City Council switchboard: +44 20 7641 6000 ** This E-Mail may contain information which is privileged, confidential and protected from disclosure. If you are not the intended recipient of this E-mail or any part of it, please telephone Westminster City Council immediately on receipt. You should not disclose the contents to any other person or take copies. ** ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/index.cfm?sidebar=listsbody=lists/cf_talk FAQ: http://www.thenetprofits.co.uk/coldfusion/faq This list and all House of Fusion resources hosted by CFHosting.com. The place for dependable ColdFusion Hosting.
RE: OT: SQL Server database problem
You don;t need to trim these down to 16 chars as that isn't the max length you can store in a SQL 'text' field - it's something like up to 2Gb of data (on SQL2K) - even though the length says 16 on the table design (I think this was discussed in a thread here 2 or 3 weeks ago) and changing this length will make no difference. The SQL text field is like a memo field in Access. So with the data you are inputting to MessageTitle, MessageTeaser and MessageBody you should have no problem. You've not given the field length for the 'Status' column into which you;re trying to insert 16 chars - could this be the problem ? Alex -Original Message- From: John Morgan [mailto:gameczar;zbzoom.net] Sent: 28 October 2002 15:14 To: CF-Talk Subject: Re: OT: SQL Server database problem You should either trim the text being inserted using the left function ... EXAMPLE: Left(form.MessageTeaser, 16 ) or you should expand the size of the field in the table. If this is data input via a form you could use the size option in the input tag to enforce the size restriction but I would still trim the data with the left function for safety sake. EXAMPLE: input type=Text name=MessageTeaser size=16 -John- At 02:58 PM 10/28/2002 +, you wrote: * * WESTMINSTER CITY COUNCIL Please refer to the disclaimer beneath this message * * Hi, I have a SQL Server 7 database and I am trying to insert a large amount of text, passed by a ColdFusion form, into a table within this database. The problem I am having is a ODBC Error Code = 22001 (String data right truncation) error. My table design is like this. Column name DatatypeLength MessageID int 4 MessageType char20 Department char20 CreationDatechar15 PublicationDate char15 MessageTitletext16 MessageTeaser text16 MessageBody text16 I think that the problem is when the text is being added to the fields, MessageTitle, MessageTeaser and MessageBody. This is the complete error message: ODBC Error Code = 22001 (String data right truncation) [Microsoft][ODBC SQL Server Driver][SQL Server]String or binary data would be truncated. SQL = INSERT INTO Messages ( MessageType, Department, CreationDate, PublicationDate, MessageTitle, MessageTeaser, MessageBody, Status ) VALUES ( 'None', 'Housing', '28/10/2002', 'Awaiting', 'Some text to throw error', 'Some text to throw error, Some text to throw error', 'Some text to throw error,Some text to throw error and Some text to throw error', 'Awating Approval' ) Data Source = WIRE_USERS The error occurred while processing an element with a general identifier of (CFQUERY), occupying document position (24:1) to (24:57) in the template file E:\INETPUB\WWWROOT\WIRE\MESSAGES\SENDFORAPPROVAL.CFM. Anyone know how I can fix this error? Thanks Stephen * * Westminster City Council switchboard: +44 20 7641 6000 * * This E-Mail may contain information which is privileged, confidential and protected from disclosure. If you are not the intended recipient of this E-mail or any part of it, please telephone Westminster City Council immediately on receipt. You should not disclose the contents to any other person or take copies. * * ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/index.cfm?sidebar=listsbody=lists/cf_talk FAQ: http://www.thenetprofits.co.uk/coldfusion/faq This list and all House of Fusion resources hosted by CFHosting.com. The place for dependable ColdFusion Hosting.
RE: OT: SQL Server database problem
Are you sure that the fields you think are the problem really are. Try doing the insert with short strings for the fields in question and see if you still get the error. -John- At 03:16 PM 10/28/2002 +, you wrote: ** WESTMINSTER CITY COUNCIL Please refer to the disclaimer beneath this message ** O.K I have increased the length of these three fields to 100, 150 and 500 respectively, but still I get the 22001 error. -Original Message- From: Jochem van Dieten [mailto:jochemd;oli.tudelft.nl] Sent: 28 October 2002 15:05 To: CF-Talk Subject: Re: OT: SQL Server database problem Quoting Adams, Stephen [EMAIL PROTECTED]: The problem I am having is a ODBC Error Code = 22001 (String data right truncation) error. My table design is like this. Column name DatatypeLength MessageID int 4 MessageType char20 Department char20 CreationDatechar15 PublicationDate char15 MessageTitletext16 MessageTeaser text16 MessageBody text16 I think that the problem is when the text is being added to the fields, MessageTitle, MessageTeaser and MessageBody. 'Some text to throw error' is indeed longer as 16 characters. Not entirely unexpected behaviour, isn't it? Jochem ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/index.cfm?sidebar=listsbody=lists/cf_talk FAQ: http://www.thenetprofits.co.uk/coldfusion/faq This list and all House of Fusion resources hosted by CFHosting.com. The place for dependable ColdFusion Hosting.
RE: OT: SQL Server database problem
** WESTMINSTER CITY COUNCIL Please refer to the disclaimer beneath this message ** Alex thank you that was the cause of the problem. Stephen -Original Message- From: A.Little [mailto:A.Little;open.ac.uk] Sent: 28 October 2002 15:23 To: CF-Talk Subject: RE: OT: SQL Server database problem You don;t need to trim these down to 16 chars as that isn't the max length you can store in a SQL 'text' field - it's something like up to 2Gb of data (on SQL2K) - even though the length says 16 on the table design (I think this was discussed in a thread here 2 or 3 weeks ago) and changing this length will make no difference. The SQL text field is like a memo field in Access. So with the data you are inputting to MessageTitle, MessageTeaser and MessageBody you should have no problem. You've not given the field length for the 'Status' column into which you;re trying to insert 16 chars - could this be the problem ? Alex -Original Message- From: John Morgan [mailto:gameczar;zbzoom.net] Sent: 28 October 2002 15:14 To: CF-Talk Subject: Re: OT: SQL Server database problem You should either trim the text being inserted using the left function ... EXAMPLE: Left(form.MessageTeaser, 16 ) or you should expand the size of the field in the table. If this is data input via a form you could use the size option in the input tag to enforce the size restriction but I would still trim the data with the left function for safety sake. EXAMPLE: input type=Text name=MessageTeaser size=16 -John- At 02:58 PM 10/28/2002 +, you wrote: * * WESTMINSTER CITY COUNCIL Please refer to the disclaimer beneath this message * * Hi, I have a SQL Server 7 database and I am trying to insert a large amount of text, passed by a ColdFusion form, into a table within this database. The problem I am having is a ODBC Error Code = 22001 (String data right truncation) error. My table design is like this. Column name DatatypeLength MessageID int 4 MessageType char20 Department char20 CreationDatechar15 PublicationDate char15 MessageTitletext16 MessageTeaser text16 MessageBody text16 I think that the problem is when the text is being added to the fields, MessageTitle, MessageTeaser and MessageBody. This is the complete error message: ODBC Error Code = 22001 (String data right truncation) [Microsoft][ODBC SQL Server Driver][SQL Server]String or binary data would be truncated. SQL = INSERT INTO Messages ( MessageType, Department, CreationDate, PublicationDate, MessageTitle, MessageTeaser, MessageBody, Status ) VALUES ( 'None', 'Housing', '28/10/2002', 'Awaiting', 'Some text to throw error', 'Some text to throw error, Some text to throw error', 'Some text to throw error,Some text to throw error and Some text to throw error', 'Awating Approval' ) Data Source = WIRE_USERS The error occurred while processing an element with a general identifier of (CFQUERY), occupying document position (24:1) to (24:57) in the template file E:\INETPUB\WWWROOT\WIRE\MESSAGES\SENDFORAPPROVAL.CFM. Anyone know how I can fix this error? Thanks Stephen * * Westminster City Council switchboard: +44 20 7641 6000 * * This E-Mail may contain information which is privileged, confidential and protected from disclosure. If you are not the intended recipient of this E-mail or any part of it, please telephone Westminster City Council immediately on receipt. You should not disclose the contents to any other person or take copies. * * ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/index.cfm?sidebar=listsbody=lists/cf_talk FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm
Re: OT: SQL Server database problem
Just another taught... Using varchar instead of text as a datatype for this kind of data maybe helpful... Rizal At 10:21 PM 10/28/2002, you wrote: I think the error message is straight forward, that is you are trying to insert string that's longer than your datatype can handle. For example: Field: MessageTitle (16): You are trying to insert: Some text to throw error which has 24 characters. Try to increase the length of the field in your DB. HTH, Rizal ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/index.cfm?sidebar=listsbody=lists/cf_talk FAQ: http://www.thenetprofits.co.uk/coldfusion/faq This list and all House of Fusion resources hosted by CFHosting.com. The place for dependable ColdFusion Hosting.
RE: OT: SQL Server database problem
** WESTMINSTER CITY COUNCIL Please refer to the disclaimer beneath this message ** Thanks Rizal, but the problem was caused by another field being to small, not the three I originally thought. Stephen -Original Message- From: Rizal Firmansyah [mailto:rizal.firmansyah;sentracommerce.com] Sent: 28 October 2002 15:42 To: CF-Talk Subject: Re: OT: SQL Server database problem Just another taught... Using varchar instead of text as a datatype for this kind of data maybe helpful... Rizal At 10:21 PM 10/28/2002, you wrote: I think the error message is straight forward, that is you are trying to insert string that's longer than your datatype can handle. For example: Field: MessageTitle (16): You are trying to insert: Some text to throw error which has 24 characters. Try to increase the length of the field in your DB. HTH, Rizal ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/index.cfm?sidebar=listsbody=lists/cf_talk FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Signup for the Fusion Authority news alert and keep up with the latest news in ColdFusion and related topics. http://www.fusionauthority.com/signup.cfm
Re: OT: SQL Server text vs. ntext
S. Isaac Dealey wrote: I have a db where I had been using a text field and I'm converting it to an ntext field so that the db can potentially support unicode at a much later date. The problem I'm having is this: I'm not just opening the db and changing the field to ntext, I created a new db and created the new table with ntext and am importing the data from the original db using a query. On one record in particular, I get a right-data truncation error. Do text and ntext fields not have the same maximum length? Is the size of the data more limited in an ntext field as a result of using a larger number to represent each character? Max lengths of fields is implementation defined, but usually the length in characters is for ntext is half that of text (wil result in the same length in bytes is a 2-byte form-of-use). To figure out what is exactly going one, try to compare the results of the character_length() function (number of characters) with the results of the octet_length() function (number of bytes). (Those are the official names, your database might have named them differently.) Jochem ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/index.cfm?sidebar=listsbody=lists/cf_talk FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm
Re: OT: SQL Server text vs. ntext
Thanks Jochem, I took a closer look at the documentation for SQL Server 2000 and it gives 2^30 -1 (1,xxx,xxx,xxx) as the maximum length for an ntext field... So ... considering that the entire file performing the insertion of data into this table is no more than 5k I'm suspect that there's something else going on here... I can't for the life of me figure out what tho... All of the column lengths defined in the original db are the same as defined in the new db ( maybe I should double them since I'm using nvarchar instead of varchar now ) ... but the only other nvarchar fields in the table have character lengths of 255 and 7 and are receiving data that is 20-25 and 0 characters respectively .. Isaac Certified Advanced ColdFusion 5 Developer www.turnkey.to 954-776-0046 S. Isaac Dealey wrote: I have a db where I had been using a text field and I'm converting it to an ntext field so that the db can potentially support unicode at a much later date. The problem I'm having is this: I'm not just opening the db and changing the field to ntext, I created a new db and created the new table with ntext and am importing the data from the original db using a query. On one record in particular, I get a right-data truncation error. Do text and ntext fields not have the same maximum length? Is the size of the data more limited in an ntext field as a result of using a larger number to represent each character? Max lengths of fields is implementation defined, but usually the length in characters is for ntext is half that of text (wil result in the same length in bytes is a 2-byte form-of-use). To figure out what is exactly going one, try to compare the results of the character_length() function (number of characters) with the results of the octet_length() function (number of bytes). (Those are the official names, your database might have named them differently.) Jochem ~~ ~~~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/index.cfm?sideb ar=listsbody=lists/cf_talk FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/index.cfm?sidebar=listsbody=lists/cf_talk FAQ: http://www.thenetprofits.co.uk/coldfusion/faq This list and all House of Fusion resources hosted by CFHosting.com. The place for dependable ColdFusion Hosting.
Re: OT : SQL Server Bulk Copy
You can use SQL Server DTS to write the file and you can also execute the DTS using xp_cmdshell extended stored procedure. Joe From: S. Isaac Dealey [EMAIL PROTECTED] Reply-To: [EMAIL PROTECTED] To: CF-Talk [EMAIL PROTECTED] Subject: OT : SQL Server Bulk Copy Date: Thu, 19 Sep 2002 14:59:58 -0400 Can anyone on the list who's familiar with BCP for SQL Server 2000 point me in the direction of some documentation or an article which explains how to write data to a file from within query analyzer? We have a project which will require writing data to a file from a scheduled DTS task on the sql server machine, which is separate from the cf server, so I can't use cfexecute to access BCP.exe via a command line. S. Isaac Dealey Certified Advanced ColdFusion 5 Developer New Epoch www.turnkey.to 954-776-0046 __ Your ad could be here. Monies from ads go to support these lists and provide more resources for the community. http://www.fusionauthority.com/ads.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: OT SQL SERVER Backup
Hi Jim, Yes.Go to Enterprise Manager Select the database--Right Click --All Tasks --Backup Select Database - Complete. Add Destination ( where the back up file to be taken) Click Ok. The Backup is created on file selected for backup In Options Tab See that the Following are checked. Verify Backup Upon Completion.(It will ensure after backup is completed successfully). Regards Nagaraj. - Original Message - From: Jim Curran [EMAIL PROTECTED] To: CF-Talk [EMAIL PROTECTED] Sent: Saturday, June 15, 2002 6:24 AM Subject: OT SQL SERVER Backup Is there an easy way to back up all the SQL server DB's and restore them on a separate Machine? - j __ This list and all House of Fusion resources hosted by CFHosting.com. The place for dependable ColdFusion Hosting. 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: OT SQL SERVER Backup
I have SQL server 2K and thiry + databases ... I know how to backup each one, but was wondering if there is a bulk backup of the entire server. - j -Original Message- From: Marlon Moyer [mailto:[EMAIL PROTECTED]] Sent: Saturday, June 15, 2002 12:24 AM To: CF-Talk Subject: Re: OT SQL SERVER Backup Which version of SQL Server...SQL2000 has a copy database wizard that can create a DTS package that you can save and run later. At 08:54 PM 6/14/2002 -0400, you wrote: Is there an easy way to back up all the SQL server DB's and restore them on a separate Machine? - j __ This list and all House of Fusion resources hosted by CFHosting.com. The place for dependable ColdFusion Hosting. 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: OT SQL SERVER Backup
Is there an easy way to back up all the SQL server DB's and restore them on a separate Machine? ... I have SQL server 2K and thiry + databases ... I know how to backup each one, but was wondering if there is a bulk backup of the entire server. There are a couple of options that might work for you. If this is a one-time thing, and you can stop the source database server, you can simply copy the database and log files to the new server, and use sp_attach_db to attach each to the new server. Alternatively, you can use T-SQL to automate the backup and restore process; on the source database server, run the BACKUP DATABASE command through any database. OSQL is a handy command-line client for doing this sort of stuff. Since you want to backup the entire server, you'll probably want to write a T-SQL batch to query the master database to find out about each database, then loop over that information. I'll bet you can find a useful script for that here: http://www.swynk.com/sqlscripts/backuprest7.asp Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ voice: (202) 797-5496 fax: (202) 797-5444 __ 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: OT SQL SERVER Backup
To backup all databases use a database maintenance plan to backup them up to the local server then have it run a script on completion to copy the files to the other server then when that job is finished have it run the restore of the databases. Joshua Tipton -Original Message- From: Dave Watts [mailto:[EMAIL PROTECTED]] Sent: Saturday, June 15, 2002 7:01 PM To: CF-Talk Subject: RE: OT SQL SERVER Backup Is there an easy way to back up all the SQL server DB's and restore them on a separate Machine? ... I have SQL server 2K and thiry + databases ... I know how to backup each one, but was wondering if there is a bulk backup of the entire server. There are a couple of options that might work for you. If this is a one-time thing, and you can stop the source database server, you can simply copy the database and log files to the new server, and use sp_attach_db to attach each to the new server. Alternatively, you can use T-SQL to automate the backup and restore process; on the source database server, run the BACKUP DATABASE command through any database. OSQL is a handy command-line client for doing this sort of stuff. Since you want to backup the entire server, you'll probably want to write a T-SQL batch to query the master database to find out about each database, then loop over that information. I'll bet you can find a useful script for that here: http://www.swynk.com/sqlscripts/backuprest7.asp Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ voice: (202) 797-5496 fax: (202) 797-5444 __ Get the mailserver that powers this list at http://www.coolfusion.com 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: OT SQL SERVER Backup
Dave Watts wrote: There are a couple of options that might work for you. If this is a one-time thing, and you can stop the source database server, you can simply copy the database and log files to the new server, and use sp_attach_db to attach each to the new server. snip Another alternative if you don't need to move the log files is to look into the command (see Books Online for syntax): sp_attach_single_file_db This allows you to attach just using the .MDF file. Stephen __ This list and all House of Fusion resources hosted by CFHosting.com. The place for dependable ColdFusion Hosting. 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: OT SQL SERVER Backup
Which version of SQL Server...SQL2000 has a copy database wizard that can create a DTS package that you can save and run later. At 08:54 PM 6/14/2002 -0400, you wrote: Is there an easy way to back up all the SQL server DB's and restore them on a separate Machine? - j __ 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: OT: SQL Server Connection
does the database server have a publicly acessible IP address? if so, just crate the ODBC source on the ISP for the site, and point it to that IP. christopher olive cto, vp of web development, vp it security atnet solutions, inc. 410.931.4092 http://www.atnetsolutions.com -Original Message- From: Thanh Nguyen [mailto:[EMAIL PROTECTED]] Sent: Wednesday, April 03, 2002 3:11 PM To: CF-Talk Subject: FW: OT: SQL Server Connection Hi all, I have a website that's accessing a sql database on the same network. Starting next week, I'd have to move my website to an ISP. My Question is how should I set up the connection to my SQL server database which will stay in-house? I can't'move the database because we also have other in-house applications connecting to the same database. What's the best way to do this? Should I create a duplicate database on the ISP server just for the website. and sync it with our main database here? any potential problems with doing this? Any advice would be appreciated. Thanks. __ Get the mailserver that powers this list at http://www.coolfusion.com 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: OT: SQL Server Connection
Nope, don't have public IP. -Original Message- From: Christopher Olive [mailto:[EMAIL PROTECTED]] Sent: Wednesday, April 03, 2002 12:15 PM To: CF-Talk Subject: RE: OT: SQL Server Connection does the database server have a publicly acessible IP address? if so, just crate the ODBC source on the ISP for the site, and point it to that IP. christopher olive cto, vp of web development, vp it security atnet solutions, inc. 410.931.4092 http://www.atnetsolutions.com -Original Message- From: Thanh Nguyen [mailto:[EMAIL PROTECTED]] Sent: Wednesday, April 03, 2002 3:11 PM To: CF-Talk Subject: FW: OT: SQL Server Connection Hi all, I have a website that's accessing a sql database on the same network. Starting next week, I'd have to move my website to an ISP. My Question is how should I set up the connection to my SQL server database which will stay in-house? I can't'move the database because we also have other in-house applications connecting to the same database. What's the best way to do this? Should I create a duplicate database on the ISP server just for the website. and sync it with our main database here? any potential problems with doing this? Any advice would be appreciated. Thanks. __ Signup for the Fusion Authority news alert and keep up with the latest news in ColdFusion and related topics. http://www.fusionauthority.com/signup.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: (OT) SQL Server Schema
Is there a way I can get a text file or print out of all of the tables, along with column names, datatypes and sizes, in MSSQL Server 2000? H. Howard Owens Internet Operations Coordinator www.insidevc.com [EMAIL PROTECTED] AIM: GoCatGo1956 __ 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: (OT) SQL Server Schema
www.swynk.com -Original Message- From: Owens, Howard [mailto:[EMAIL PROTECTED]] Sent: Wednesday, January 30, 2002 7:07 PM To: CF-Talk Subject: RE: (OT) SQL Server Schema Is there a way I can get a text file or print out of all of the tables, along with column names, datatypes and sizes, in MSSQL Server 2000? H. Howard Owens Internet Operations Coordinator www.insidevc.com [EMAIL PROTECTED] AIM: GoCatGo1956 __ 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: (OT) SQL Server Schema
Cool. I just discovered the answer to my own question. And in case you haven't figured this out ... I'll tell you want I did. I opened the database design view, click in one of the field names, then did a select all (ctl-a), then copied, then pasted into an Excel spreadsheet. Worked perfect. H. Howard Owens Internet Operations Coordinator www.insidevc.com [EMAIL PROTECTED] AIM: GoCatGo1956 -Original Message- From: Owens, Howard [SMTP:[EMAIL PROTECTED]] Sent: Wednesday, January 30, 2002 4:07 PM To: CF-Talk Subject: RE: (OT) SQL Server Schema Is there a way I can get a text file or print out of all of the tables, along with column names, datatypes and sizes, in MSSQL Server 2000? H. Howard Owens Internet Operations Coordinator www.insidevc.com [EMAIL PROTECTED] AIM: GoCatGo1956 __ 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: (OT) SQL Server Schema
You can do a join on sysobjects and syscolumns tables in your DB and get the data that way also. andy __ 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: (OT) SQL Server Schema
SP_HELPDB EXTENDED STORED PROCEDURE. -Original Message- From: Owens, Howard [mailto:[EMAIL PROTECTED]] Sent: Wednesday, January 30, 2002 7:54 PM To: CF-Talk Subject: RE: (OT) SQL Server Schema Cool. I just discovered the answer to my own question. And in case you haven't figured this out ... I'll tell you want I did. I opened the database design view, click in one of the field names, then did a select all (ctl-a), then copied, then pasted into an Excel spreadsheet. Worked perfect. H. Howard Owens Internet Operations Coordinator www.insidevc.com [EMAIL PROTECTED] AIM: GoCatGo1956 -Original Message- From: Owens, Howard [SMTP:[EMAIL PROTECTED]] Sent: Wednesday, January 30, 2002 4:07 PM To: CF-Talk Subject: RE: (OT) SQL Server Schema Is there a way I can get a text file or print out of all of the tables, along with column names, datatypes and sizes, in MSSQL Server 2000? H. Howard Owens Internet Operations Coordinator www.insidevc.com [EMAIL PROTECTED] AIM: GoCatGo1956 __ 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: (OT) SQL Server Schema
Using Enterprise manager you can right click on the DB and choose genera te SQL scripts - it will automatically generate a create script for all or part of the DB, including views and indexes. Or.. you can choose the diagram icon and build a nice visual view. You can do everything in design view including managing constraints and changing data fields. I h ave used Design view to diagram MySQL databases in the past (by running a DTS import, then arranging the tables) - just so I can have a visual print ou t to work with. One more way is using the object viewer in the query anaylyzer (F8). Right click on a table and choose - generate script as create. Good luck Mark -Original Message- From: Andy Parry [mailto:[EMAIL PROTECTED]] Sent: Wednesday, January 30, 2002 5:13 PM To: CF-Talk Subject: RE: (OT) SQL Server Schema You can do a join on sysobjects and syscolumns tables in your DB and get the data that way also. andy __ 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: OT: SQL Server 7, Windows 95 and Service Manager
On Win98, I have a shortcut in the startup folder: drive:\MSSQL7\Binn\scm.exe -Action 1 -Silent 1 Stephen In Windows 95, how do you change the account the Service Manager uses to try and start SQL Server on boot up, bearing in mind there is no Services option (as is the case with NT)? Thanks -- Aidan Whitehall [EMAIL PROTECTED] Macromedia ColdFusion Developer Fairbanks Environmental +44 (0)1695 51775 ~~ 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: OT SQL Server
INT/NOT NULL/IDENTITY ~Simon Simon Horwith Macromedia Certified Instructor Certified ColdFusion Developer Fig Leaf Software 1400 16th St NW, # 500 Washington DC 20036 202.797.6570 (direct line) www.figleaf.com -Original Message- From: ibtoad [mailto:[EMAIL PROTECTED]] Sent: Wednesday, June 13, 2001 10:37 AM To: CF-Talk Subject: OT SQL Server I have a simple question. How do you set the primary key field to autonumber in SQL server? I have always used Access where this is a choice but don't see how to do it with SQL Server 2000. Thanks, Rich ~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
