RE: SQL Server and Nulls

[DURETTE, STEVEN J]

Dbfield=

Steve

-Original Message-
From: Robert Harrison [mailto:rharri...@aimg.com] 
Sent: Friday, April 17, 2015 3:58 PM
To: cf-talk
Subject: SQL Server and Nulls


In an update query, to a tinyint field which allows nulls, I have the update

dbfield=#mydatefield#

If mydatefield has no value, sql is throwing an error. The field allows
nulls. I've never had to say if "" then NULL before.  What the heck?


Robert Harrison
Full Stack Developer
AIMG
rharri...@aimg.com
Main Office: 704-321-1234  ext.118
Direct Line: 516-302-4345
www.aimg.com





~|
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:360470
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm

Re: SQL Server and Nulls

[John M Bliss]

dbfield = 

On Fri, Apr 17, 2015 at 3:58 PM, Robert Harrison  wrote:

>
> In an update query, to a tinyint field which allows nulls, I have the
> update
>
> dbfield=#mydatefield#
>
> If mydatefield has no value, sql is throwing an error. The field allows
> nulls. I've never had to say if "" then NULL before.  What the heck?
>
>
> Robert Harrison
> Full Stack Developer
> AIMG
> rharri...@aimg.com
> Main Office: 704-321-1234  ext.118
> Direct Line: 516-302-4345
> www.aimg.com
>
>
>
> 

~|
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:360471
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm

Re: SQL Server Data Archival - my solution

[Dan O'Keefe]

I find this intriguing as well. Almost like a poor mans historical
archive system.

A generator for the triggers would be cool also based on Illidium PU-36
--
Dan O'Keefe



On Fri, Jul 30, 2010 at 9:08 AM, Pete Ruckelshaus
 wrote:
>
> Feel free to pass on any enhancements or improvements!
>

~|
Order the Adobe Coldfusion Anthology now!
http://www.amazon.com/Adobe-Coldfusion-Anthology-Michael-Dinowitz/dp/1430272155/?tag=houseoffusion
Archive: 
http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:335921
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm

Re: SQL Server Data Archival - my solution

[Pete Ruckelshaus]

Feel free to pass on any enhancements or improvements!

On Fri, Jul 30, 2010 at 11:02 AM, Robert Harrison <
rob...@austin-williams.com> wrote:

>
> Very nice! Thanks for sharing that. Think I'll play with it a bit as well.
>
>
> Robert B. Harrison
> Director of Interactive Services
> Austin & Williams
> 125 Kennedy Drive, Suite 100
> Hauppauge NY 11788
> P : 631.231.6600 Ext. 119
> F : 631.434.7022
> http://www.austin-williams.com
>
> Great advertising can't be either/or.  It must be &.
>
> Plug in to our blog: A&W Unplugged
> http://www.austin-williams.com/unplugged
>
>
>
>
> __ Information from ESET Smart Security, version of virus signature
> database 5326 (20100730) __
>
> The message was checked by ESET Smart Security.
>
> http://www.eset.com
>
>
> 

~|
Order the Adobe Coldfusion Anthology now!
http://www.amazon.com/Adobe-Coldfusion-Anthology-Michael-Dinowitz/dp/1430272155/?tag=houseoffusion
Archive: 
http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:335894
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm

RE: SQL Server Data Archival - my solution

[Robert Harrison]

Very nice! Thanks for sharing that. Think I'll play with it a bit as well.


Robert B. Harrison
Director of Interactive Services
Austin & Williams
125 Kennedy Drive, Suite 100 
Hauppauge NY 11788
P : 631.231.6600 Ext. 119 
F : 631.434.7022
http://www.austin-williams.com 

Great advertising can't be either/or.  It must be &.

Plug in to our blog: A&W Unplugged
http://www.austin-williams.com/unplugged


 

__ Information from ESET Smart Security, version of virus signature
database 5326 (20100730) __

The message was checked by ESET Smart Security.

http://www.eset.com
 

~|
Order the Adobe Coldfusion Anthology now!
http://www.amazon.com/Adobe-Coldfusion-Anthology-Michael-Dinowitz/dp/1430272155/?tag=houseoffusion
Archive: 
http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:335887
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm

Re: SQL Server Data Archival - my solution

[Pete Ruckelshaus]

Steve, to keep things simple(r), I would store that info at the source table
level.  Since it's a web site, there's a single generic user account at the
database level, but I do track user and IP at the CF application level in
the table that specific data is stored...it's just not shown in the tblUsers
example table.

Pete

On Fri, Jul 30, 2010 at 10:26 AM, DURETTE, STEVEN J (ATTASIAIT) <
sd1...@att.com> wrote:

>
> Pete,
>
> This is awesome! I'm going to consider using it for certain tables that
> we have problems with. I might suggest that you add in the user name
> and/or ip that made the change.  That can help if you need to find out
> who made specific changes!
>
> Thanks,
> Steve
>
>
>


~|
Order the Adobe Coldfusion Anthology now!
http://www.amazon.com/Adobe-Coldfusion-Anthology-Michael-Dinowitz/dp/1430272155/?tag=houseoffusion
Archive: 
http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:335886
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm

RE: SQL Server Data Archival - my solution

[DURETTE, STEVEN J (ATTASIAIT)]

Pete,

This is awesome! I'm going to consider using it for certain tables that
we have problems with. I might suggest that you add in the user name
and/or ip that made the change.  That can help if you need to find out
who made specific changes!

Thanks,
Steve


-Original Message-
From: Pete Ruckelshaus [mailto:pruckelsh...@gmail.com] 
Sent: Friday, July 30, 2010 10:20 AM
To: cf-talk
Subject: SOT: SQL Server Data Archival - my solution


I know a lot of time people (including myself) come here with questions,
and
don't have many opportunities to offer solutions to share.  I had a need
to
store a record of changes to records in database tables for a content
management system.  I wanted to keep things simple, without requiring a
bunch of additional code to my CF codebase, and I didn't want to make
SQL
Server management more complicated than it needed to be by adding a ton
of
tables.

Anyway, in a nutshell, I've got an archive table that stores changed
records
as an XML object in a field.  An insert, update, delete trigger grabs
values
from the inserted or deleted tables, packs that info up, and saves it to
the
archive table.  The beauty of this is its relative simplicity -- the
archive
table can store data from any table in the database.

Here's the write-up.  I'm sure it's not perfect -- I'm far from an SQL
Server expert - but it's going to work very well for my needs.
Process - single archive table storing XML

The archive table would look something like this:

*tblArchive*

uid uniqueidentifier DEFAULT NEWID(),

timestamp datetime DEFAULT GETDATE(),

sourceTable varchar(100),

sourceID int,

action varchar(10),

xData xml



* UID would be a unique identifier for this archive table.

* Timestamp would set the time of archive and would allow to
sort
revisions based upon when they happened.

* sourceTable would be the name of the table that the data is
coming
from.

* sourceID would be the record identifier (presumably the
primary
key) of that record from the original table.

* Action would be whatever SQL action was performed on that
record,
i.e. insert, update, delete

* xData would be the XML object that stores the record that is
being
changed.



Assuming a fictitious (and rather simple) table named "tblUsers" with
the
following structure:



*tblUsers*

id (int, PK, ident, autoincrement),

fname (varchar(20)),

lname (varchar(30)),

employeeid (int),

extension (int)


Archive Trigger for Inserts, Updates and Deletes

For each table that is to have archived data, run the following trigger,
modifying table names where necessary:


CREATE TRIGGER[tgrArchiveRecord]

ON[tblUsers]

FOR   INSERT, UPDATE, DELETE

AS

  IF@@rowcount = 0

RETURN

  DECLARE @table varchar(100);

  DECLARE @sourceid int;

  DECLARE @action varchar(10);

  SET @table = 'tblUsers';

  IF EXISTS (SELECT * FROM DELETED)

  BEGIN

IFEXISTS (SELECT * FROM DELETED) AND

  EXISTS (SELECT * FROM INSERTED)

BEGIN

  SET   @sourceid = (SELECT id FROM inserted);

  SET @action = 'update';

  INSERT INTO tblArchive(sourceTable, sourceid,

action, xData)

  SELECT @table, id, @action, (SELECT *

  FROM deleted AS record

  WHERE deleted.id = record.id FOR XML AUTO)

FROM deleted;

  RETURN

END



SET   @sourceid = (SELECT id FROM deleted);

SET @action = 'delete';

INSERT INTO tblArchive (sourceTable, sourceid, action,
xData)

SELECT @table, id, @action, (SELECT *

  FROM deleted AS record

  WHERE deleted.id = record.id FOR XML AUTO)

FROM deleted;

  END

  ELSE

  BEGIN

SET   @sourceid = (SELECT id FROM inserted);

SET @action = 'insert';

INSERT INTO tblArchive (sourceTable, sourceid, action,
xData)

SELECT @table, id, @action, (SELECT *

  FROM inserted AS record

  WHERE inserted.id = record.id FOR XML
AUTO)

  FROM inserted;

  END

GO
Retrieving Data From Archive

The likely scenario for retrieving data from the archive table is to
either
display a history of a record, or to present previous changes so that a
user
can roll back changes to a previous version.  Retrieving data from the
Archive table's XML column is fairly straightforward:


SELECT  NULL as uid, u.id AS id, u.fname, u.lname, u.employeeid,

u.extension, getdate() AS [timestamp]

FROMtblUsers u

   UNION

SELECT  A.uid,

A.sourceID AS id,

A.xData.value('(/*/@fname)[1]', 'varchar(20)') AS fname,

A.xData.value('(/*/@lname)[1]', 'varchar(30)') AS lname

RE: SQL Server Performance

[UXB Internet]

>> hardware improvement options and need to quantify
>>  (as much as possible) performance increases

In my limited experience with MSSQL the best investment is improved Hard
drive speed and/or throughput and increased memory. They are usually the
bottlenecks.


Dennis Powers
UXB Internet - A Website Design & Hosting Company
P.O. Box 6028
Wolcott, CT 06716
203-879-2844
http://www.uxbinternet.com


 



~|
Order the Adobe Coldfusion Anthology now!
http://www.amazon.com/Adobe-Coldfusion-Anthology-Michael-Dinowitz/dp/1430272155/?tag=houseoffusion
Archive: 
http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:335872
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm

Re: SQL Server question re table names - still stuck

[Ricardo Russon]

>
> 3) When I do a CFQuery, it won't work unless I write the table name as
> "canadianofficespacecom.city", instead of just "city".
>


Is the database specified in the DSN?

Sounds as if one isn't specified.


Ricardo.


~|
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:329122
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4

Re: SQL Server question re table names - still stuck

[Ezra Parker]

On Fri, Dec 11, 2009 at 2:53 PM, Larry Soo  wrote:
> So would I be correct in assuming that
> the problem I'm having is that the default schema for "dbo" is "master"
> instead of "canadianofficespacecom"?

I believe so, yes. In order to resolve this from the user side of
things you're going to need to connect as a different user, as you
will not be able to change the default schema of the "sa" user, as
noted here:

http://msdn.microsoft.com/en-us/library/ms176060.aspx

"The value of DEFAULT_SCHEMA is ignored if the user is a member of the
sysadmin fixed server role. All members of the sysadmin fixed server
role have a default schema of dbo."

So I would suggest creating a "canadianofficespacecom" login to
correspond to the "canadianofficespacecom" user in your DB, and then
executing the following T-SQL script:

USE canadianofficespace;
ALTER USER canadianofficespacecom WITH LOGIN = canadianofficespacecom,
DEFAULT_SCHEMA = canadianofficespacecom;
GO

(As a side note, I had thought you could change the default schema in
the GUI interface, but after trying it out it appears I was mistaken
about this -- sorry for the misinformation there.)

If this runs successfully, then change the DSN to connect with the new
user name and password and you should be good to go.

The alternative option would be to change the schema for the tables to
"dbo", but if it were me I would prefer changing users anyway in order
to avoid connecting as "sa".

Please post back if you run into any problems.

--
Ezra Parker

~|
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:329121
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4

re: SQL Server question re table names - still stuck

[Larry Soo]

Recap of problem: I imported an old, backed up SQL Server file.  Now, when 
I view the tables or try to access them in CF, it requires me to prefix all 
table names with "canadianofficespacecom.".

Ezra Parker wrote:
> I didn't see a mention of which version of SQL Server you're running,
> but it sounds to me like canadianofficespacecom is either an object
> owner or a schema:

I'm using SQL Server 2008...and the database is a few years old...possibly 
pre-dating 2005 or even older.

> I'm assuming that it's most likely SQL Server 2005 or 2008, in which
> case the simplest fix would probably be to change the default schema
> for the user that you specified in your CF datasource. To do this,
> access the properties for the user in question and you will see a
> default schema field -- change the value to canadianofficespacecom and
> you should then be able to query the tables without the prefix.

I tried that.  The username I was using is SA.  The default schema was 
"master" so I changed it to "canadianofficespacecom".  The weird thing is 
that when I click on the OK button and the dialog closes, when I re-open 
it, it still shows the default schema as being "master".  It doesn't report 
an error.

brad wrote:
> What version of SQL server are you on again? I'm not sure if I've ever
> had one table name show up, but when I go to rename it, another one
> appears. 
> 
> Also, what happens when you try and script a rename?

I haven't tried to script a rename because the manual renaming didn't work. 
 FYI I'm on SQL Server 2008.

Here's a screen shot of the users and schemas: 
http://www.bc4x4.com/temp/cos-screenshot.jpg

BTW, when I look at user "dbo", I see that its Database Role Membership 
lists it as having db_owner status.  So would I be correct in assuming that 
the problem I'm having is that the default schema for "dbo" is "master" 
instead of "canadianofficespacecom"?

(Thank you to everyone for your help so far.)


...lars



~|
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:329113
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4

RE: SQL Server question re table names - still stuck

[brad]

Hmm, what's the chance the extra part isn't really the database name,
but it is the owner name. Kind of like the default owner is dbo and your
full path to a table is database.dbo.tablename.  Perhaps it is
databasename.somethingyoudontpect.tablename.  Perhaps you need to look
at altering the owner or schema of the table instead of the actual name.

What version of SQL server are you on again?  I'm not sure if I've ever
had one table name show up, but when I go to rename it, another one
appears.  

Also, what happens when you try and script a rename?

http://technet.microsoft.com/en-us/library/aa275462%28SQL.80%29.aspx

~Brad

 Original Message --------
Subject: re: SQL Server question re table names - still stuck
From: "Larry Soo" 
Date: Fri, December 11, 2009 3:07 am
To: cf-talk 


> How many tables are there? If there are under 75, you might be just as
> fast renaming them them by hand.

Well, I tried renaming them by hand but it won't work. Using the SQL 
Server Manger, I right-clicked on the table and then clicked on RENAME. 
The table name, when I right-clicked on it, said something like:
abc.city. 
But when the RENAME edit box came up, it contained only "city". So the 
table isn't actually named abc.city. It is only presenting the table
that 
way, and when I do a query in CF, it expects me to also refer to the
table 
with that database name prefix.

Hmm...ok, here's the exact scenario:
1) I'm imported the data from a backup file and named the database 
"canadianofficespace".
2) When I look at the tables in SQL Server Manager, it prefixes every
table 
name with "canadianofficespacecom", which is not the name of the
database. 
I presume it's the name of the database that it originally came from.
3) When I do a CFQuery, it won't work unless I write the table name as 
"canadianofficespacecom.city", instead of just "city".

Can anyone help me out here? This is well beyond my SQL Server
knowledge.

...lars




~|
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:329093
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4

Re: SQL Server question re table names - still stuck

[Leigh]

> but it sounds to me like canadianofficespacecom is either
> an object
> owner or a schema:

+1

That would make more sense than being part of the actual table name.


  

~|
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:329081
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4

Re: SQL Server question re table names - still stuck

[Ezra Parker]

I didn't see a mention of which version of SQL Server you're running,
but it sounds to me like canadianofficespacecom is either an object
owner or a schema:

http://www.sqlteam.com/article/understanding-the-difference-between-owners-and-schemas-in-sql-server

I'm assuming that it's most likely SQL Server 2005 or 2008, in which
case the simplest fix would probably be to change the default schema
for the user that you specified in your CF datasource. To do this,
access the properties for the user in question and you will see a
default schema field -- change the value to canadianofficespacecom and
you should then be able to query the tables without the prefix.

HTH,

--
Ezra Parker


On Fri, Dec 11, 2009 at 1:07 AM, Larry Soo  wrote:
>
> I wrote:
>> > A client asked me to see if I could get a copy of his old web site up
> and
>> > running. I did a restore of the backed up database onto my PC. The
>> > problem is that all the table names have the name of the database
> prepended
>> > to them.
>> >
>> > When I view the tables in SQL Server Manager, the table, "city", is
> named
>> > "abc.city", where oldabc is the name of the database from which the
> data
>> > was backed up from. For the sake of this example, "newabc" is the
> database
>> > name I used on my own machine, to which the backup data was restored
> to.
>
>
> brad wrote:
>> I can't say I've ever seen that one before, but you could just do a loop
>> over the contents of INFORMATION_SCHEMA.TABLES and build/exec an ALTER
>> statement for each one.
>>
>> http://technet.microsoft.com/en-us/library/ms190273.aspx
>>
>> How many tables are there? If there are under 75, you might be just as
>> fast renaming them them by hand.
>
> Well, I tried renaming them by hand but it won't work.  Using the SQL
> Server Manger, I right-clicked on the table and then clicked on RENAME.
> The table name, when I right-clicked on it, said something like: abc.city.
> But when the RENAME edit box came up, it contained only "city".  So the
> table isn't actually named abc.city.  It is only presenting the table that
> way, and when I do a query in CF, it expects me to also refer to the table
> with that database name prefix.
>
> Hmm...ok, here's the exact scenario:
> 1) I'm imported the data from a backup file and named the database
> "canadianofficespace".
> 2) When I look at the tables in SQL Server Manager, it prefixes every table
> name with "canadianofficespacecom", which is not the name of the database.
> I presume it's the name of the database that it originally came from.
> 3) When I do a CFQuery, it won't work unless I write the table name as
> "canadianofficespacecom.city", instead of just "city".
>
> Can anyone help me out here?  This is well beyond my SQL Server knowledge.
>
> ...lars
>
>
> 

~|
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:329080
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4

re: SQL Server question re table names - still stuck

[Larry Soo]

I wrote:
> > A client asked me to see if I could get a copy of his old web site up 
and 
> > running. I did a restore of the backed up database onto my PC. The 
> > problem is that all the table names have the name of the database 
prepended 
> > to them.
> > 
> > When I view the tables in SQL Server Manager, the table, "city", is 
named 
> > "abc.city", where oldabc is the name of the database from which the 
data 
> > was backed up from. For the sake of this example, "newabc" is the 
database 
> > name I used on my own machine, to which the backup data was restored 
to.


brad wrote:
> I can't say I've ever seen that one before, but you could just do a loop
> over the contents of INFORMATION_SCHEMA.TABLES and build/exec an ALTER
> statement for each one.
> 
> http://technet.microsoft.com/en-us/library/ms190273.aspx
> 
> How many tables are there? If there are under 75, you might be just as
> fast renaming them them by hand.

Well, I tried renaming them by hand but it won't work.  Using the SQL 
Server Manger, I right-clicked on the table and then clicked on RENAME.  
The table name, when I right-clicked on it, said something like: abc.city.  
But when the RENAME edit box came up, it contained only "city".  So the 
table isn't actually named abc.city.  It is only presenting the table that 
way, and when I do a query in CF, it expects me to also refer to the table 
with that database name prefix.

Hmm...ok, here's the exact scenario:
1) I'm imported the data from a backup file and named the database 
"canadianofficespace".
2) When I look at the tables in SQL Server Manager, it prefixes every table 
name with "canadianofficespacecom", which is not the name of the database.  
I presume it's the name of the database that it originally came from.
3) When I do a CFQuery, it won't work unless I write the table name as 
"canadianofficespacecom.city", instead of just "city".

Can anyone help me out here?  This is well beyond my SQL Server knowledge.

...lars


~|
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:329078
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: 
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4

RE: SQL Server question re table names

[Leigh]

I have only seen that with exports from MS Access. 

Yes, +1 about using the information INFORMATION_SCHEMA views. Tapping into the 
system views is my favorite way to generate mass statements on-the-fly ;-)

DECLARE @DBPrefix VARCHAR(50)
SET @DBPrefix = 'oldabc.'

SELECT  'exec sp_rename @objname=''['+ TABLE_NAME +']'', @newname = '''+ 
SUBSTRING(TABLE_NAME , LEN(@DBPrefix)+1, LEN(TABLE_NAME)) +
FROMINFORMATION_SCHEMA.TABLES
WHERE   TABLE_NAME LIKE @DBPrefix +'_%'

-Leigh


  

~|
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:328978
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4

RE: SQL Server question re table names

[brad]

I can't say I've ever seen that one before, but you could just do a loop
over the contents of INFORMATION_SCHEMA.TABLES and build/exec an ALTER
statement for each one.

http://technet.microsoft.com/en-us/library/ms190273.aspx

How many tables are there?  If there are under 75, you might be just as
fast renaming them them by hand.

~Brad


 Original Message 
Subject: SQL Server question re table names
From: "Larry Soo" 
Date: Tue, December 08, 2009 1:45 pm
To: cf-talk 


A client asked me to see if I could get a copy of his old web site up
and 
running. I did a restore of the backed up database onto my PC. The 
problem is that all the table names have the name of the database
prepended 
to them.



~|
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:328974
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: 
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4

RE: SQL Server Logins (should I be seeing so many in the log?)

[brad]

"I have maintain connections selected."


Did you check that setting on all data sources for both CF Servers.  Are
you storing client vars in via a datasource?

Run a trace on the SQL server to see what sort of operations are being
performed-- especially by new connections.

~Brad


~|
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:325445
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4

Re: SQL server - Order totals by month, even without month data

[Will Tomlinson]

Thanks to everyone for their help with this!

Will 

~|
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:320991
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4

RE: SQL server - Order totals by month, even without month data

[brad]

I too would recommend creating a temp table with a record for every date
you want represented in your graph.  Instead of unioning it though, I
usually left outer join from it to my data with an isnull on any number
columns so they show up 0 for months where there was no data.

~Brad

 Original Message 
Subject: Re: SQL server - Order totals by month, even without month
data
From: Greg Morphis 
Date: Wed, March 25, 2009 7:26 am
To: cf-talk 


Create a dummy table with the information for the current year and
preform a UNION to your existing data

On Wed, Mar 25, 2009 at 6:26 AM, Will Tomlinson 
wrote:
>
> I have your typical tblorders. I'm trying to query it to get order totals by 
> month, and I'm outputting them in 
>
> All goes fine, except in 2009. We have no order data for April-December of 
> course, so the query returns just three rows (Jan, Feb, March).
>


~|
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:320945
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: 
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4

Re: SQL server - Order totals by month, even without month data

[Greg Morphis]

Create a dummy table with the information for the current year and
preform a UNION to your existing data

On Wed, Mar 25, 2009 at 6:26 AM, Will Tomlinson  wrote:
>
> I have your typical tblorders. I'm trying to query it to get order totals by 
> month, and I'm outputting them in 
>
> All goes fine, except in 2009. We have no order data for April-December of 
> course, so the query returns just three rows (Jan, Feb, March).
>
> I need it to return all 12 months, with 0 for the months with no orders 
> (April-Dec)
>
> I've played with this query and can't get anything to work for me. Any ideas?
>
> Thanks!
>
> Will
>
> select year(orderdate) as orderYear,
> month(orderdate) as monthOrdered,
> sum(ordertotal) as totalOrders,
> dateName(month, orderdate) as monthName
> from tblorders
> where orderpaid = 1
> and year(orderdate) = 2009
> group by year(orderdate),
> month(orderdate),
> dateName(month, orderdate)
> order by  year(orderdate) asc,
> month(orderdate) asc
>
> 

~|
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:320941
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: 
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4

Re: SQL server - Order totals by month, even without month data

[Azadi Saryev]

when i had to do a similar thing, this is how i did it:

your query returns monthName and totalOrders columns.

create a structure from your query with Month as key and Total as value:





create a list of month names in the format returned by your query:


create a new query you will use for your chart:


loop over the mthslist list and populate the new query with month name
and either 0 or your query's totalOrders value from the structure you
created before:





  

  


now you have a 'chartqry' query with all 12 months you can use for your
chart.

Azadi Saryev
Sabai-dee.com
http://www.sabai-dee.com/



Will Tomlinson wrote:
> I have your typical tblorders. I'm trying to query it to get order totals by 
> month, and I'm outputting them in  
>
> All goes fine, except in 2009. We have no order data for April-December of 
> course, so the query returns just three rows (Jan, Feb, March).
>
> I need it to return all 12 months, with 0 for the months with no orders 
> (April-Dec)
>
> I've played with this query and can't get anything to work for me. Any ideas? 
>
> Thanks!
>
> Will
>
> select year(orderdate) as orderYear,
> month(orderdate) as monthOrdered,
> sum(ordertotal) as totalOrders,
> dateName(month, orderdate) as monthName
> from tblorders
> where orderpaid = 1
> and year(orderdate) = 2009
> group by year(orderdate),
> month(orderdate),
> dateName(month, orderdate)
> order by  year(orderdate) asc,
> month(orderdate) asc 
>   

~|
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:320927
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: 
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4

Re: SQL server - Order totals by month, even without month data

[Azadi Saryev]

posted a reply hours ago, but it still hasn't showed up... reposting...

when i had to do a similar thing, this is how i did it:

your query returns monthName and totalOrders columns.

create a structure from your query with Month as key and Total as value:





create a list of month names in the format returned by your query:


create a new query you will use for your chart:


loop over the mthslist list and populate the new query with month name
and either 0 or your query's totalOrders value from the structure you
created before:





  

  


now you have a 'chartqry' query with all 12 months you can use for your
chart.



Azadi Saryev
Sabai-dee.com
http://www.sabai-dee.com/



~|
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:320924
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4

RE: SQL server - Order totals by month, even without month data

[William Seiter]

There are 2 ways that come to mind right off the bat.
1.  Create another table that has each month in it.  Do an outer join that
allows the other table to return 0 for the months where you have no records
in your original table
2.  Inspect the query in CF for missing months.  If there are any missing
months, then create a new query using QueryNew to hold the old query, plus
the new rows of months that have a 0 in them.

William

-Original Message-
From: Will Tomlinson [mailto:w...@wtomlinson.com] 
Sent: Wednesday, March 25, 2009 4:26 AM
To: cf-talk
Subject: SQL server - Order totals by month, even without month data


I have your typical tblorders. I'm trying to query it to get order totals by
month, and I'm outputting them in  

All goes fine, except in 2009. We have no order data for April-December of
course, so the query returns just three rows (Jan, Feb, March).

I need it to return all 12 months, with 0 for the months with no orders
(April-Dec)

I've played with this query and can't get anything to work for me. Any
ideas? 

Thanks!

Will

select year(orderdate) as orderYear,
month(orderdate) as monthOrdered,
sum(ordertotal) as totalOrders,
dateName(month, orderdate) as monthName
from tblorders
where orderpaid = 1
and year(orderdate) = 2009
group by year(orderdate),
month(orderdate),
dateName(month, orderdate)
order by  year(orderdate) asc,
month(orderdate) asc 



~|
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:320921
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4

Re: SQL server - Order totals by month, even without month data

[Donnie Carvajal]

The only way to get data into a query is to have data in a table.  So there are 
a couple of options.

1.  Create a months table that has 12 records
2.  Create a temp table with 12 records

You may also need to create a years table as well.

Then your query would be something like

SELECT year, month, SUM(total)
FROM (SELECT * FROM Years CROSS JOIN Months) AS Months
LEFT OUTER JOIN
tblOrders
ON Month(orderDate) = month
GROUP BY year, month
ORDER BY year, month

The cross join will give you a record in your query for every year that you 
would like to account for and every month of that year.

You can of course add any other criteria you would like to the WHERE clause.


> I have your typical tblorders. I'm trying to query it to get order 
> totals by month, and I'm outputting them in  
> 
> All goes fine, except in 2009. We have no order data for 
> April-December of course, so the query returns just three rows (Jan, 
> Feb, March).
> 
> I need it to return all 12 months, with 0 for the months with no 
> orders (April-Dec)
> 
> I've played with this query and can't get anything to work for me. Any 
> ideas? 
> 
> Thanks!
> 
> Will
> 
> select year(orderdate) as orderYear,
> month(orderdate) as monthOrdered,
> sum(ordertotal) as totalOrders,
> dateName(month, orderdate) as monthName
> from tblorders
> where orderpaid = 1
> and year(orderdate) = 2009
> group by year(orderdate),
> month(orderdate),
> dateName(month, orderdate)
> order by  year(orderdate) asc,
> month(orderdate) asc 


~|
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:320916
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4

RE: SQL server - Order totals by month, even without month data

[Robert Rawlins]

Hello Will,

I'd advise building yourself a calendar table, these are always a useful
facility in any application where you're going to be doing report
generation, I've attached the SQL script which creates and populates your
calendar table, this basically creates a record for each date in a range and
breaks into different date parts, then for your query below we select the
dates from the calendar table, ensuring we get a record for each group, be
it day, month, year etc and then we'll LEFT OUTER JOIN the order table on
that date, this ensures we get a row for every date in the range and it'll
just return NULL for the months where orders don't exist.

SQL CREATE Script: http://pastebin.com/m57ba4578
SQL INERT Script: http://pastebin.com/m65d6ee0f

That script will create a table and insert records for the next 10 years,
change the dates on the insert script to suit your own requirements.

The script for your main query would then look something like this:
http://pastebin.com/m1e234813

I have no way of testing that script, its working off the info you gave, if
it doesn't work then let me know your table structure and some example data
and we'll bash something together.

Hope that helps you on your way.

Rob

-Original Message-
From: Will Tomlinson [mailto:w...@wtomlinson.com] 
Sent: 25 March 2009 11:26
To: cf-talk
Subject: SQL server - Order totals by month, even without month data


I have your typical tblorders. I'm trying to query it to get order totals by
month, and I'm outputting them in  

All goes fine, except in 2009. We have no order data for April-December of
course, so the query returns just three rows (Jan, Feb, March).

I need it to return all 12 months, with 0 for the months with no orders
(April-Dec)

I've played with this query and can't get anything to work for me. Any
ideas? 

Thanks!

Will

select year(orderdate) as orderYear,
month(orderdate) as monthOrdered,
sum(ordertotal) as totalOrders,
dateName(month, orderdate) as monthName
from tblorders
where orderpaid = 1
and year(orderdate) = 2009
group by year(orderdate),
month(orderdate),
dateName(month, orderdate)
order by  year(orderdate) asc,
month(orderdate) asc 



~|
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:320912
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4

RE: SQL server - Order totals by month, even without month data

[Dawson, Michael]

Create a calendar table.  Load it with dates from 1900-2100.  Each
record relates to a single day.  You can join to this table in many
ways.  Here is an example of one of my records...

date
1900-01-01 00:00:00.000

dayOfWeek
2

dayOfWeekName
Monday

day
1

dayLeadingZero
01

month
1

monthLeadingZero
01

year
1900

yearMonthDay
19000101

yearMonth
190001

dayOfYear
1

dayOfYearLeadingZero
001

fiscalYear
1900

isWeekday
1

quarter
1

fiscalQuarter
3

Thanks,
Mike

-Original Message-
From: Will Tomlinson [mailto:w...@wtomlinson.com] 
Sent: Wednesday, March 25, 2009 6:26 AM
To: cf-talk
Subject: SQL server - Order totals by month, even without month data


I have your typical tblorders. I'm trying to query it to get order
totals by month, and I'm outputting them in  

All goes fine, except in 2009. We have no order data for April-December
of course, so the query returns just three rows (Jan, Feb, March).

I need it to return all 12 months, with 0 for the months with no orders
(April-Dec)

I've played with this query and can't get anything to work for me. Any
ideas? 

Thanks!

Will

select year(orderdate) as orderYear,
month(orderdate) as monthOrdered,
sum(ordertotal) as totalOrders,
dateName(month, orderdate) as monthName
from tblorders
where orderpaid = 1
and year(orderdate) = 2009
group by year(orderdate),
month(orderdate),
dateName(month, orderdate)
order by  year(orderdate) asc,
month(orderdate) asc 

~|
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:320909
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4

RE: SQL server - Order totals by month, even without month data

[Dawson, Michael]

Also, since you are using SQL, you can use a CTE to dynamically generate
a date range as a temp table, so to speak.  I prefer a regular table as
I mentioned in my last reply, however.

Thanks,
Mike

-Original Message-
From: Will Tomlinson [mailto:w...@wtomlinson.com] 
Sent: Wednesday, March 25, 2009 6:26 AM
To: cf-talk
Subject: SQL server - Order totals by month, even without month data


I have your typical tblorders. I'm trying to query it to get order
totals by month, and I'm outputting them in  

All goes fine, except in 2009. We have no order data for April-December
of course, so the query returns just three rows (Jan, Feb, March).

I need it to return all 12 months, with 0 for the months with no orders
(April-Dec)

I've played with this query and can't get anything to work for me. Any
ideas? 

Thanks!

Will

select year(orderdate) as orderYear,
month(orderdate) as monthOrdered,
sum(ordertotal) as totalOrders,
dateName(month, orderdate) as monthName
from tblorders
where orderpaid = 1
and year(orderdate) = 2009
group by year(orderdate),
month(orderdate),
dateName(month, orderdate)
order by  year(orderdate) asc,
month(orderdate) asc 



~|
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:320908
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4

Re: SQL Server 2005 Datasource failing

[Stan Winchester]

For those who are interested this was solved at: 
http://forums.newatlanta.com/messages.cfm?threadid=D0F5E9DE-C84B-43EC-8C34667F8FB9077B

Thanks to those who helped!
Stan 

~|
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:319509
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4

Re: SQL Server 2005 Datasource failing

[Stan Winchester]

I checked that TCP/IP is enabled and it was. I found an article that talked 
about making sure SQL Server 2000 was at least sp3a, so I updated to sp4. 

Now CF8 on a remote box verifies the connection, but BD7 does not (BD7 is on 
the same box as CF8). I get the error:

"Login failed for user '[user name]'. (Msg 18456, Level 14, State 1, Line 0)"

I have checked and rechecked the user name / password in BD7 and they are 
correct. I am getting closer, but still need BD7 to verify.

Also, BD7 seems to require the server name to be with a forward slash as 
follows:
[server name]/[instance name] whereas CF8 uses a back-slash. If I used a 
back-slash BD7 returns "Verifying [data source]... [server name]\[instance 
name]"

> Is TCP/IP enabled on your SQL 2005 instance?  By default, TCP/IP 
> connections are disabled in SQL 2005.
> 


~|
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:319493
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: 
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4

RE: SQL Server 2005 Datasource failing

[Pat Wenke]

Is TCP/IP enabled on your SQL 2005 instance?  By default, TCP/IP connections 
are disabled in SQL 2005.

- Pat

-Original Message-
From: Brad Wood [mailto:b...@bradwood.com] 
Sent: Tuesday, February 17, 2009 10:32 PM
To: cf-talk
Subject: Re: SQL Server 2005 Datasource failing


What port is SQL Server 2005 listening on?

Also, can you connect using the same password in Enterprise Manager?

Run a trace on the 2005 database and include failed login events from the 
audit group.  Can you see the login failures coming through. If not, CF 
isn't hitting the correct SQL instance.

~Brad


- Original Message - 
From: "Stan Winchester" 
To: "cf-talk" 
Sent: Tuesday, February 17, 2009 9:38 PM
Subject: SQL Server 2005 Datasource failing


>
> On my local network I've been using SQL Server 2000 and connecting to it 
> with BD7 & CF8 with no problems. I also have SQL Server 2005 on it and am 
> having trouble creating a datasource that logins. It seems to see SQL 
> Server 2005, but I am getting the following error:
>
> Connection verification failed for data source: [my datasource]
> java.sql.SQLException: [Macromedia][SQLServer JDBC 
> Driver][SQLServer]Cannot open database "[my datasource]" requested by the 
> login. The login failed.
> The root cause was that: java.sql.SQLException: [Macromedia][SQLServer 
> JDBC Driver][SQLServer]Cannot open database "[my datasource]" requested by 
> the login. The login failed.
>
> With SQL Server 2000 I just connected using the [server name] over port 
> 1433 with no problems. SQL Server 2005 is on the box so the server is 
> called "[server name]\sqlserver2005" as shown in the connections 
> properties in MS SQL Server Management Studio, with the instance name 
> "sqlserver2005".
>
> I have checked and re-checked the user name / password and they are the 
> same in CF8 & the SQL Server 2005 database.
>
> Any ideas what I am missing?
>
> Thanks, Stan




~|
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:319459
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4

Re: SQL Server 2005 Datasource failing

[Brad Wood]

What port is SQL Server 2005 listening on?

Also, can you connect using the same password in Enterprise Manager?

Run a trace on the 2005 database and include failed login events from the 
audit group.  Can you see the login failures coming through. If not, CF 
isn't hitting the correct SQL instance.

~Brad


- Original Message - 
From: "Stan Winchester" 
To: "cf-talk" 
Sent: Tuesday, February 17, 2009 9:38 PM
Subject: SQL Server 2005 Datasource failing


>
> On my local network I've been using SQL Server 2000 and connecting to it 
> with BD7 & CF8 with no problems. I also have SQL Server 2005 on it and am 
> having trouble creating a datasource that logins. It seems to see SQL 
> Server 2005, but I am getting the following error:
>
> Connection verification failed for data source: [my datasource]
> java.sql.SQLException: [Macromedia][SQLServer JDBC 
> Driver][SQLServer]Cannot open database "[my datasource]" requested by the 
> login. The login failed.
> The root cause was that: java.sql.SQLException: [Macromedia][SQLServer 
> JDBC Driver][SQLServer]Cannot open database "[my datasource]" requested by 
> the login. The login failed.
>
> With SQL Server 2000 I just connected using the [server name] over port 
> 1433 with no problems. SQL Server 2005 is on the box so the server is 
> called "[server name]\sqlserver2005" as shown in the connections 
> properties in MS SQL Server Management Studio, with the instance name 
> "sqlserver2005".
>
> I have checked and re-checked the user name / password and they are the 
> same in CF8 & the SQL Server 2005 database.
>
> Any ideas what I am missing?
>
> Thanks, Stan


~|
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:319457
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4

Re: SQL Server on development box?

[Gerald Guido]

Thanx Jim. Awesome resource!!

~G~

On Wed, Oct 8, 2008 at 7:19 PM, Jim Davis <[EMAIL PROTECTED]>wrote:

> > -Original Message-
> > From: Gerald Guido [mailto:[EMAIL PROTECTED]
> > Sent: Wednesday, October 08, 2008 6:19 PM
> > To: cf-talk
> > Subject: Re: SQL Server on development box?
> >
> > >> absolute best-of-breed management tools
> >
> > The only issue I really had with the express MSSQL Management Studio is
> > the
> > lack of import/export functionality. If you have a license for MSSQL
> > 2005
> > you should be able to install the Management Studio that came with the
> > server.
>
> Actually this is a just a bone-head move by MS.  The DTS tools
> ("import/export") are available and work with SQL Server Express - they're
> just not installed by default and little hard to find.
>
> Here's a blog entry that links to it and describes how to install:
>
>
> http://mobiledeveloper.wordpress.com/2007/01/31/data-import-export-with-sql-
> server-express-using-dts-wizard/<http://mobiledeveloper.wordpress.com/2007/01/31/data-import-export-with-sql-server-express-using-dts-wizard/>
>
> The DTS wizard is (my opinion) very good (I've been forced to use a lot of
> DB2 and Oracle lately so I'm absolutely sold on MS's infinitely better
> interfaces) - but it's always been a separate tool.
>
> I'm not sure what you mean by the last bit... as long as you have a license
> for the full SQL Server you can definitely (legally) install the client
> tools to as many clients as you like... that's been the case since at least
> SQL Server 6.5.
>
> Jim Davis
>
>
>
> 

~|
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:313650
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: 
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4

RE: SQL Server on development box?

[Jim Davis]

> -Original Message-
> From: Gerald Guido [mailto:[EMAIL PROTECTED]
> Sent: Wednesday, October 08, 2008 6:19 PM
> To: cf-talk
> Subject: Re: SQL Server on development box?
> 
> >> absolute best-of-breed management tools
> 
> The only issue I really had with the express MSSQL Management Studio is
> the
> lack of import/export functionality. If you have a license for MSSQL
> 2005
> you should be able to install the Management Studio that came with the
> server.

Actually this is a just a bone-head move by MS.  The DTS tools
("import/export") are available and work with SQL Server Express - they're
just not installed by default and little hard to find.

Here's a blog entry that links to it and describes how to install:

http://mobiledeveloper.wordpress.com/2007/01/31/data-import-export-with-sql-
server-express-using-dts-wizard/

The DTS wizard is (my opinion) very good (I've been forced to use a lot of
DB2 and Oracle lately so I'm absolutely sold on MS's infinitely better
interfaces) - but it's always been a separate tool.

I'm not sure what you mean by the last bit... as long as you have a license
for the full SQL Server you can definitely (legally) install the client
tools to as many clients as you like... that's been the case since at least
SQL Server 6.5.

Jim Davis



~|
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:313639
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: 
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4

Re: SQL Server on development box?

[Gerald Guido]

>> absolute best-of-breed management tools

The only issue I really had with the express MSSQL Management Studio is the
lack of import/export functionality. If you have a license for MSSQL 2005
you should be able to install the Management Studio that came with the
server.


On Wed, Oct 8, 2008 at 4:55 PM, Jim Davis <[EMAIL PROTECTED]>wrote:

>
>
>
> I second this - the Express version is limited, but not stripped down.  If
> you can live within those limits (and nearly everybody can) then you've got
> a great DBMS with (IMHO) the absolute best-of-breed management tools
> around.
>
> Jim Davis
>
>
> 

~|
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:313636
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: 
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4

RE: SQL Server on development box?

[Jim Davis]

> -Original Message-
> From: RobG [mailto:[EMAIL PROTECTED]
> Sent: Wednesday, October 08, 2008 12:15 PM
> To: cf-talk
> Subject: Re: SQL Server on development box?
> 
> I thought MSSQL Express (developer) edition was free?
> 
> Rob

I second this - the Express version is limited, but not stripped down.  If
you can live within those limits (and nearly everybody can) then you've got
a great DBMS with (IMHO) the absolute best-of-breed management tools around.

Jim Davis


~|
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:313630
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: 
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4

RE: SQL Server on development box?

[brad]

There is an express version of Management Studio you can use for free. 
The biggest limitation to me was that you can't have a database larger
than 4 Gigs.  If you have a larger database, fork over the 50 bucks.

~Brad

 Original Message 
Subject: Re: SQL Server on development box?
From: Wes Middendorff <[EMAIL PROTECTED]>
Date: Wed, October 08, 2008 11:15 am
To: cf-talk 

If you want to use their tools, yes. You also use squirrel as a query
editor. It is a free java based editor that works with any db platform
that has a jdbc driver. 




~|
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:313618
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: 
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4

Re: SQL Server on development box?

[Gerald Guido]

They have an Express version. I just set it up.
http://www.microsoft.com/sql/editions/express/default.mspx

If you need Full text search get the Express Edition with Advanced Services.

Here is the run down on features
http://www.microsoft.com/sql/editions/express/comparison.mspx


G

On Wed, Oct 8, 2008 at 12:05 PM, Billy Cox <[EMAIL PROTECTED]> wrote:

> I recently moved our sites to a new server running CF8 and SQL 2005
> Standard. Do I have to buy the 'Developer Edition' of SQL 2005 for my
> development box or is there another way?
>
> It's only $50, but being a corporate type, I like free better.
>
>
> Billy Cox
> Old World Spices
>
>
>
>
>
>
> 

~|
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:313617
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: 
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4

Re: SQL Server on development box?

[Wes Middendorff]

If you want to use their tools, yes. You also use squirrel as a query editor. 
It is a free java based editor that works with any db platform that has a jdbc 
driver. 

~|
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:313616
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4

Re: SQL Server on development box?

[RobG]

I thought MSSQL Express (developer) edition was free?

Rob


On Wed, Oct 8, 2008 at 9:05 AM, Billy Cox <[EMAIL PROTECTED]> wrote:
> I recently moved our sites to a new server running CF8 and SQL 2005
> Standard. Do I have to buy the 'Developer Edition' of SQL 2005 for my
> development box or is there another way?
>
> It's only $50, but being a corporate type, I like free better.

~|
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:313615
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4

Re: SQL Server 2005 permission problems

[James Buckingham]

Well I got this up and running through ODBC but unforunately when I did 
Transfer ORM, which I'm using to manage my DAO, throws an error message.

How do permissions work in SQL Server in terms of CF. From what I can all I 
need is the datawriter and datareader roles applied to that user. This is what 
I've got on my working DBs and it's fine!

Any help would be appreciated.

Cheers,
James 

~|
Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to 
date
Get the Free Trial
http://ad.doubleclick.net/clk;203748912;27390454;j

Archive: 
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:312578
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4

RE: SQL Server connection best practice?

[Mark Kruger]

Ben,

Search House of fusion for a recent thread on query optimization as well...
There was an interesting issue with a stored proc taking too long and it
turned out to be permissions on the database (made me go hm.)

-mark
 

-Original Message-
From: Ben Mueller [mailto:[EMAIL PROTECTED] 
Sent: Wednesday, January 02, 2008 2:17 PM
To: CF-Talk
Subject: SQL Server connection best practice?

Hi all,

I'm not even sure how to pose this question, so I did a poor job of
searching the archives, if this question has already been answered.

We're running CFMX7 and SQL Server 2000 in Windows2003 Server.  I know there
are a variety of ways that one can get CF to talk to a SQL Server box: ODBC,
JDBC, Named Pipes, and so on.  I don't even pretend to know what all of that
really means.  The question is what's the best practice these days?  I think
at one point, ODBC was faster, but then JDBC was recommended, and now I'm no
longer sure.

This came up because I ran a query in SQL Server query analyzer that
returned the result set in 0 milliseconds.  however, running it using
CFQUERY (debugging enabled) showed that the query took about 800ms to
execute.  The recordset returned is pretty big, so I'm assuming that the
bulk of the 800ms is the transfer time, and that got me to thinking about
the above question.

Any guidance is appreciated.

Thanks,
Ben 



~|
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:295727
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: 
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4

Re: SQL Server connection best practice?

[Ben Mueller]

Okay, that's kind of what I thought.  I think the SequeLink thing is what I was 
thinking of wrt to the ODBC driver.  Thanks, Dave.

>ColdFusion only supports JDBC, so you'll be using JDBC. You can either use a
>"pure Java" JDBC driver, or you can connect to ODBC datasources using
>SequeLink as a JDBC proxy of sorts. I'd strongly recommend using just JDBC.
>
>JDBC requires that your SQL Server be configured to listen on TCP/IP. It
>doesn't support Named Pipes.
>
>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! 

~|
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:295720
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4

RE: SQL Server connection best practice?

[Dave Watts]

> We're running CFMX7 and SQL Server 2000 in Windows2003 
> Server.  I know there are a variety of ways that one can get 
> CF to talk to a SQL Server box: ODBC, JDBC, Named Pipes, and 
> so on.  I don't even pretend to know what all of that really 
> means.  The question is what's the best practice these days?  
> I think at one point, ODBC was faster, but then JDBC was 
> recommended, and now I'm no longer sure.

ColdFusion only supports JDBC, so you'll be using JDBC. You can either use a
"pure Java" JDBC driver, or you can connect to ODBC datasources using
SequeLink as a JDBC proxy of sorts. I'd strongly recommend using just JDBC.

JDBC requires that your SQL Server be configured to listen on TCP/IP. It
doesn't support Named Pipes.

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!

~|
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:295719
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: 
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4

RE: SQL Server 2005 Workgroup vs Standard License?

[Dave Watts]

> Hopefully this is not off topic.  We have a lot of CF sites 
> running our software that use SQL 2000.  We are going to be 
> moving to SQL 2005 and are doing research on the licensing 
> options.  It seems that there is a "Standard" license and a 
> "Workgroup" license of SQL 2005.  For the purposes of hosting 
> dynamic websites, we're not sure what the key differences or 
> trade-offs are for our purposes (other than price).  The only 
> one that seems like it might be relevant is a max limit of 2 
> GB of RAM for the Workgroup license.  Other than that, we're 
> not sure what the key factors are.
> 
> Any advice on this choice (other than price)?  For example, 
> are there any reasons to definitely get or not get one or the 
> other of these licenses for the purpose of hosting a bunch of 
> dynamic CF-based sites on a shared server?
> (note, the web server running CF is separate from the db server).

The Workgroup edition doesn't support any of the native SQL Server 2005
redundancy functionality (mirroring, etc). Other than that, and the RAM
limitation, you should be fine with Workgroup. The feature comparison matrix
pretty much lays it all out:

http://www.microsoft.com/sql/prodinfo/features/compare-features.mspx

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!


~|
ColdFusion is delivering applications solutions at at top companies 
around the world in government.  Find out how and where now
http://www.adobe.com/cfusion/showcase/index.cfm?event=finder&productID=1522&loc=en_us

Archive: 
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:293660
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4

Re: SQL Server Issue

[Cutter (CF Related)]

Dave,

Thanks for responding on this. After almost six hours on the phone with 
MS, it looks like the best guess option is to restart services. Since 
the release of MS SQL 2000 their support has received one call for this 
issue, so they aren't really sure. We'll find out in another twenty min 
when we finally kick the services ...

Steve 'Cutter' Blades
Adobe Certified Expert
Advanced Macromedia ColdFusion MX 7 Developer

http://blog.cutterscrossing.com

"The best way to predict the future is to help create it"



Dave Watts wrote:
> Someone else here on the list today had a similar problem, and I found this:
> http://support.microsoft.com/kb/811886
>
> Dave Watts, CTO, Fig Leaf Software
> http://www.figleaf.com/
>   

~|
ColdFusion is delivering applications solutions at at top companies 
around the world in government.  Find out how and where now
http://www.adobe.com/cfusion/showcase/index.cfm?event=finder&productID=1522&loc=en_us

Archive: 
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:292182
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4

RE: SQL Server Issue

[Dave Watts]

> Currently we utilize MS SQL 2000 with multiple databases. One 
> of our CF servers (one of the 6.1 boxes) recycled it's cached 
> ds connections and was no longer able to authenticate to the 
> db's. We were also no longer able to connect via Enterprise 
> Manager, receiving the (essentially) same
> error:
> 
> detail = [Macromedia][SQLServer JDBC 
> Driver][SQLServer]CryptoAPI function 'CryptAcquireContext' 
> failed. Error 0x57: The parameter is incorrect.
> 
> My fear is that our other CF servers will cycle their cached 
> db connections soon, and begin receiving the same type of 
> errors. We would restart the SQL server, but then there's the 
> fear that it won't come back at all, and none of our servers 
> will reconnect. Has anyone come across this before? Any 
> suggestions? Google isn't helping much, and MS has had us on 
> hold for a while now, so any help would be appreciated.

Someone else here on the list today had a similar problem, and I found this:
http://support.microsoft.com/kb/811886

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!


~|
Get involved in the latest ColdFusion discussions, product
development sharing, and articles on the Adobe Labs wiki.
http://labs/adobe.com/wiki/index.php/ColdFusion_8

Archive: 
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:292180
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: 
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4

Re: SQL Server problem getting records within date range

[Janet MacKay]

> (CONVERT(varchar, dbo.tblUserTransactionLog.Processed, 101) >= 
> \'08/01/2007\')  AND 

Yes, that works but could hinder indexes. Josh is correct about date 
comparisons.  If you want all records with a _date_ of 08/01/2007 a better way 
to write that query is:

--- Notice the less than < 08/02/2007
AND  dbo.tblUserTransactionLog.Processed >= convert(datetime, '08/01/2007', 101)
AND  dbo.tblUserTransactionLog.Processed < convert(datetime, '08/02/2007', 101)



~|
Get involved in the latest ColdFusion discussions, product
development sharing, and articles on the Adobe Labs wiki.
http://labs/adobe.com/wiki/index.php/ColdFusion_8

Archive: 
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:285815
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: 
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4

Re: SQL Server problem getting records within date range

[C. W. B.]

Ah Ha!  I figured out the problem with the query.  This following seems to work 
as expected:

SELECT 
  dbo.tblSubscribers.SID, 
  dbo.tblSubscribers.Username, 
  max(dbo.tblUserTransactionLog.Processed) AS LastTransaction
FROM 
  dbo.tblSubscribers,
  dbo.tblUserTransactionLog
WHERE
  dbo.tblSubscribers.SID = dbo.tblUserTransactionLog.SID AND
  (CONVERT(varchar, dbo.tblUserTransactionLog.Processed, 101) >= 
\'08/01/2007\')  AND 
  (CONVERT(varchar, dbo.tblUserTransactionLog.Processed, 101) <= \'08/01/2007\')
GROUP BY 
  dbo.tblSubscribers.SID, 
  dbo.tblSubscribers.Username

Thanks to Janet and Josh for their help!

Thanks, - cwb


~|
Create robust enterprise, web RIAs.
Upgrade to ColdFusion 8 and integrate with Adobe Flex
http://www.adobe.com/products/coldfusion/flex2/?sdid=RVJP

Archive: 
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:285787
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4

Re: SQL Server problem getting records within date range

[Josh Nathanson]

C. W.,

I don't know much about SQL server in particular, but try using 'date' 
instead of 'datetime' in your CONVERT function, if that's possible.  I've 
run across this before in MySQL.

This is because a value like '2007-08-02 14:00:00' is greater than 
'2007-08-02', so that won't be picked up by less than or equal to.

-- Josh


- Original Message - 
From: "C. W. B." <[EMAIL PROTECTED]>
To: "CF-Talk" 
Sent: Wednesday, August 08, 2007 5:37 PM
Subject: Re: SQL Server problem getting records within date range


> Hello again Janet...
>
> I am so sorry to be bugging you again on this, but I am having trouble 
> with this new query - not doubt it's something I just don't understand.
>
> If I set the dates in this query to 08/01/2007 and 08/02/2007 I only get 
> results from 08/01/2007.  And, if I have them both set to 08/02/2007, I 
> don't get no results at all - but I would expect to set the results from 
> 8/2, which do indeed exist.
>
> Note that I am using >= and <= in the query.
>
> What am I missing here?
>
> Feeling kinda dumb today :(
>
>
>>Yes, that looks correct.
>
> 

~|
ColdFusion 8 - Build next generation apps
today, with easy PDF and Ajax features - download now
http://download.macromedia.com/pub/labs/coldfusion/cf8_beta_whatsnew_052907.pdf

Archive: 
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:285782
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: 
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4

Re: SQL Server problem getting records within date range

[C. W. B.]

Hello again Janet...

I am so sorry to be bugging you again on this, but I am having trouble with 
this new query - not doubt it's something I just don't understand.

If I set the dates in this query to 08/01/2007 and 08/02/2007 I only get 
results from 08/01/2007.  And, if I have them both set to 08/02/2007, I don't 
get no results at all - but I would expect to set the results from 8/2, which 
do indeed exist.  

Note that I am using >= and <= in the query.

What am I missing here?

Feeling kinda dumb today :(


>Yes, that looks correct. 

~|
ColdFusion is delivering applications solutions at at top companies 
around the world in government.  Find out how and where now
http://www.adobe.com/cfusion/showcase/index.cfm?event=finder&productID=1522&loc=en_us

Archive: 
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:285779
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: 
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4

Re: SQL Server problem getting records within date range

[C. W. B.]

Hello again Janet, thank you for your help on this.  It suare has had me 
stumped.

So, like this?

SELECT 
  dbo.tblSubscribers.SID, 
  dbo.tblSubscribers.Username, 
  max(dbo.tblUserTransactionLog.Processed) AS LastTransaction
FROM 
  dbo.tblSubscribers,
  dbo.tblUserTransactionLog
WHERE
  dbo.tblSubscribers.SID = dbo.tblUserTransactionLog.SID AND
  (dbo.tblUserTransactionLog.Processed >= CONVERT(datetime, '08/01/2007', 101)) 
 AND 
  (dbo.tblUserTransactionLog.Processed <= CONVERT(datetime, '08/07/2007', 101))
GROUP BY 
  dbo.tblSubscribers.SID, 
  dbo.tblSubscribers.Username

Thanks, CWB

> > I should have mentioned that I need to get only the most recent 
> > transaction for each user within the given time-frame.  That's why I 
> 
> > am using max().  Using this aggregate function, I think, requires I 
> 
> > use "having," no?.
> 
> No, if I'm understanding correctly its not required here.  The WHERE 
> clause restricts the results to the given time frame and the aggregate 
> + the GROUP BY gives you the max (per user)
> 
> Janet 


~|
Download the latest ColdFusion 8 utilities including Report Builder,
plug-ins for Eclipse and Dreamweaver updates.
http;//www.adobe.com/cfusion/entitlement/index.cfm?e=labs%5adobecf8%5Fbeta

Archive: 
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:285754
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4

Re: SQL Server problem getting records within date range

[Janet MacKay]

Yes, that looks correct. 

~|
Create robust enterprise, web RIAs.
Upgrade to ColdFusion 8 and integrate with Adobe Flex
http://www.adobe.com/products/coldfusion/flex2/?sdid=RVJP

Archive: 
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:285756
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4

Re: SQL Server problem getting records within date range

[Janet MacKay]

> I should have mentioned that I need to get only the most recent 
> transaction for each user within the given time-frame.  That's why I 
> am using max().  Using this aggregate function, I think, requires I 
> use "having," no?.

No, if I'm understanding correctly its not required here.  The WHERE clause 
restricts the results to the given time frame and the aggregate + the GROUP BY 
gives you the max (per user)

Janet 

~|
Check out the new features and enhancements in the
latest product release - download the "What's New PDF" now
http://download.macromedia.com/pub/labs/coldfusion/cf8_beta_whatsnew_052907.pdf

Archive: 
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:285741
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4

Re: SQL Server problem getting records within date range

[C. W. B.]

Hello Janet - thanks for the reply.

I should have mentioned that I need to get only the most recent transaction for 
each user within the given time-frame.  That's why I am using max().  Using 
this aggregate function, I think, requires I use "having," no?.

Thanks, CWB


>Use the WHERE not HAVING clause. Try something like
>
>SELECT ...
>FROM   ... 
>WHERE  dbo.tblSubscribers.SID = dbo.tblUserTransactionLog.SID
>ANDdbo.tblUserTransactionLog.Processed >= convert(datetime, '08/01/2007', 
>101)
>AND   dbo.tblUserTransactionLog.Processed <= convert(datetime, '08/07/2007', 
>101)
>GROUP BY
>  dbo.tblSubscribers.SID,
>  dbo.tblSubscribers.Username, 

~|
Create robust enterprise, web RIAs.
Upgrade to ColdFusion 8 and integrate with Adobe Flex
http://www.adobe.com/products/coldfusion/flex2/?sdid=RVJP

Archive: 
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:285733
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4

Re: SQL Server problem getting records within date range

[Janet MacKay]

Use the WHERE not HAVING clause. Try something like

SELECT ...
FROM   ... 
WHERE  dbo.tblSubscribers.SID = dbo.tblUserTransactionLog.SID
ANDdbo.tblUserTransactionLog.Processed >= convert(datetime, '08/01/2007', 
101)
AND   dbo.tblUserTransactionLog.Processed <= convert(datetime, '08/07/2007', 
101)
GROUP BY
  dbo.tblSubscribers.SID,
  dbo.tblSubscribers.Username,


~|
Check out the new features and enhancements in the
latest product release - download the "What's New PDF" now
http://download.macromedia.com/pub/labs/coldfusion/cf8_beta_whatsnew_052907.pdf

Archive: 
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:285727
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: 
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4

RE: SQL Server 2005 datasource - permissions

[Dave Watts]

> You are correct sir! Thank you. I do have a follow up 
> question, however. I'm curious - the password I was trying to 
> use that was being rejected was a GUID created using 
> CreateUUID(). Should/can such identifiers not be used as 
> passwords in the CF Administrator? My thought was that you 
> really can not get more secure than that. Thanks.

The CF Administrator will not accept passwords longer than sixteen
characters. If you can't log in at all, of course, that is quite secure.

GUID/UUIDs are no more secure, by themselves, than randomly-generated
character strings. They simply won't be generated with the same value twice.

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!

This email has been processed by SmoothZap - www.smoothwall.net


~|
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:283696
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4

Re: SQL Server 2005 datasource - permissions

[Andrew Peterson]

>> I have a SQL Server database and I want to make it accessible 
>
>I'll bet you actually have the incorrect password. There's nothing special
>you have to to create logins that you can use from CF. Try changing the
>password.
>
>Dave 

You are correct sir! Thank you. I do have a follow up question, however. I'm 
curious - the password I was trying to use that was being rejected was a GUID 
created using CreateUUID(). Should/can such identifiers not be used as 
passwords in the CF Administrator? My thought was that you really can not get 
more secure than that. Thanks.

Andy

~|
Create robust enterprise, web RIAs.
Upgrade & integrate Adobe Coldfusion MX7 with Flex 2
http://www.adobe.com/products/coldfusion/flex2/?sdid=RVJP

Archive: 
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:283690
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: 
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4

RE: SQL Server 2005 datasource - permissions

[Dave Watts]

> I have a SQL Server database and I want to make it accessible 
> to CF. I can connect successfully to the datasource in CFIDE 
> using the sa account, but I cannot do the same using a New 
> Login with SQL Server Authentication. The error I receive is 
> "Login failed for user 'theuser'" which is incidentally the 
> same error I receive when using an incorrect password. 
> 
> What permissions does a SQL Server 2005 Login need in order 
> for CF to use it to access a SQL Server database? Thanks in 
> advance. I'm using CF 7.0.2

I'll bet you actually have the incorrect password. There's nothing special
you have to to create logins that you can use from CF. Try changing the
password.

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!

This email has been processed by SmoothZap - www.smoothwall.net


~|
Upgrade to Adobe ColdFusion MX7
Experience Flex 2 & MX7 integration & create powerful cross-platform RIAs
http://www.adobe.com/products/coldfusion/flex2/?sdid=RVJQ 

Archive: 
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:283670
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4

RE: SQL Server 2005 Express on dual CPU machine?

[Damien McKenna]

To answer my own question:

http://support.microsoft.com/default.aspx/kb/914278
"Microsoft SQL Server 2005 Express Edition can only use one CPU, even if
the computer has more than one CPU."
So it can be installed on a multi-CPU machine, it'll just limit how it
works.  Perfect.

Damien McKenna
Web Developer
The LIMU Company

~|
Create Web Applications With ColdFusion MX7 & Flex 2. 
Build powerful, scalable RIAs. Free Trial
http://www.adobe.com/products/coldfusion/flex2/?sdid=RVJS 

Archive: 
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:282472
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: 
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4

Re: SQL Server Syntax - Converting this CF Code

[Andrew Peterson]

Thanks - let me try that - a lot cleaner than I thought it would be. 

~|
Upgrade to Adobe ColdFusion MX7
The most significant release in over 10 years. Upgrade & see new features.
http://www.adobe.com/products/coldfusion?sdid=RVJR

Archive: 
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:272691
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4

Re: SQL Server Syntax - Converting this CF Code

[Jim Wright]

Andrew Peterson wrote:
> Hi,
> 
> I'd like to see how this is done, even though it is not necessary and I like 
> my ColdFusion thank you very much. Can someone give me a general idea of how 
> this code would look in SQL Server? It takes literally 5 minutes to write in 
> CF; but for me to put it into a SQL Server stored procedure I'm lost. Here's 
> the code.
> 
> 
> SELECT RecordType FROM LHF_Line 
> where quarter = '#lastQuarter#'
> 
>   
> 
>
>   
>   INSERT INTO LHF_Line...abc
> 
>
>   
>   INSERT INTO LHF_Line...def
>   
>
> 
> 

I'm not quite sure where the ...abc and ...def data is going to come 
from, but possibly something like this would work...

CREATE PROC spThisProc @qrtr varchar(50) AS

INSERT INTO LHF_Line(...abc)
SELECT ...abc FROM LHF_Line WHERE quarter = @quarter AND recordtype IN 
('MC','NA')

INSERT INTO LHF_Line(...def)
SELECT ...def FROM LHF_Line WHERE quarter = @quarter AND recordtype NOT 
IN ('MC','NA')

~|
Deploy Web Applications Quickly across the enterprise with ColdFusion MX7 & 
Flex 2
Free Trial 
http://www.adobe.com/products/coldfusion/flex2/?sdid=RVJU

Archive: 
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:272688
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: 
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4

Re: SQL Server Database connection

[Andrew Scott]

Is it MS SQL 200 or MS SQL 2005?

If 2005, the tcp/ip port 1433 is not switched on by default.


On 3/9/07, Paul Dormody <[EMAIL PROTECTED]> wrote:
>
> I have looked at the Adobe help files and Forta's blog about setting up
> a MS SQL datasource in CF MX 7. I have followed all of the suggestions
> and still cannot get a connection.
>
> I need help.
>
> Thank you,
>
> Paul Dormody
> [EMAIL PROTECTED]
> 651-208-9600
>
>
> 

~|
Deploy Web Applications Quickly across the enterprise with ColdFusion MX7 & 
Flex 2. 
Free Trial 
http://www.adobe.com/products/coldfusion/flex2/

Archive: 
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:272097
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4

Re: SQL Server DSN, text data type, CLOB is required?

[Jacob Munson]

> Long Text Buffer - The default buffer size, used if Enable Long Text
> Retrieval(CLOB) is not selected. The default value is 64000 bytes.

Oh, 64,000 chars should be plenty.  Thanks guys.

-- 
My Sites:
http://www.techfeed.net/blog/
http://www.cfquickdocs.com/
http://cfformprotect.riaforge.org/

~|
Create robust enterprise, web RIAs.
Upgrade & integrate Adobe Coldfusion MX7 with Flex 2
http://www.adobe.com/products/coldfusion/flex2/

Archive: 
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:271948
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4

RE: SQL Server DSN, text data type, CLOB is required?

[Dave Watts]

> A co-worker of mine just told me that in order to use the 
> text data type in SQL Server, you have to enable CLOB and 
> BLOB in the data source in CFAdmin.  Is this true?  I need to 
> use the text data type because my users potentially could 
> enter a large amount of text (> 8000 chars).

Straight from the documentation:

CLOB - Select to return the entire contents of any CLOB/Text columns in the
database for this data source. If not selected, ColdFusion MX retrieves the
number of characters specified in the Long Text Buffer setting.

Long Text Buffer - The default buffer size, used if Enable Long Text
Retrieval(CLOB) is not selected. The default value is 64000 bytes.

So, if your CLOBs will exceed the buffer size, you will need to enable CLOB.
If not, you shouldn't have to. BLOBs aren't an issue here, because you're
using CLOBs.

http://livedocs.adobe.com/coldfusion/7/htmldocs/1743.htm#1278307

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!


~|
ColdFusion MX7 by Adobe®
Dyncamically transform webcontent into Adobe PDF with new ColdFusion MX7. 
Free Trial. http://www.adobe.com/products/coldfusion

Archive: 
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:271947
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: 
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4

Re: SQL Server DSN, text data type, CLOB is required?

[Gareth Hughes]

You don't have to enable clob to use text or ntext data types but if you 
don't then CF will crop any input to those fields that exceeds a certain 
length. I can't remember what the length is that CF crops at but I have 
experienced this. Not sure about blob.


- Original Message - 
From: "Jacob Munson" <[EMAIL PROTECTED]>
To: "CF-Talk" 
Sent: Wednesday, March 07, 2007 10:57 PM
Subject: SQL Server DSN, text data type, CLOB is required?


A co-worker of mine just told me that in order to use the text data
type in SQL Server, you have to enable CLOB and BLOB in the data
source in CFAdmin.  Is this true?  I need to use the text data type
because my users potentially could enter a large amount of text (>
8000 chars).

-- 
My Sites:
http://www.techfeed.net/blog/
http://www.cfquickdocs.com/
http://cfformprotect.riaforge.org/



~|
Upgrade to Adobe ColdFusion MX7
Experience Flex 2 & MX7 integration & create powerful cross-platform RIAs
http://www.adobe.com/products/coldfusion/flex2/

Archive: 
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:271946
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4

Re: SQL Server DSN, text data type, CLOB is required?

[Matt Quackenbush]

I'm not an expert, but I've been using 'text' and/or 'ntext' in SQL Server
for years and have never done that, so I'm going to say no, that's not true.


Matt


A co-worker of mine just told me that in order to use the text data
> type in SQL Server, you have to enable CLOB and BLOB in the data
> source in CFAdmin.  Is this true?  I need to use the text data type
> because my users potentially could enter a large amount of text (>
> 8000 chars).
>


~|
Create robust enterprise, web RIAs.
Upgrade & integrate Adobe Coldfusion MX7 with Flex 2
http://www.adobe.com/products/coldfusion/flex2/

Archive: 
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:271941
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: 
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4

RE: SQL Server, querying between two DBs

[Andy Matthews]

I'm sure our dba has this done already. Thanks for pointing it out though/ 

-Original Message-
From: James Smith [mailto:[EMAIL PROTECTED] 
Sent: Wednesday, January 24, 2007 5:08 AM
To: CF-Talk
Subject: RE: SQL Server, querying between two DBs

> Our dba finally got out of his meeting. Turns out that not only are 
> they separate databases, but they're also on a different server.
> 
> So it's [server_name].databasename.dbo.tablename.

This will work fine but remember to set up "linked servers" in Enterprise
Manager or the cross-server join will fail.  It is also worth noting that
this can be rather unpredictable in speed sometimes returning results
lightning fast and sometimes crawling so it may be worth setting up
replication to copy one of the databases across to the other server and
perform your queries against that.

--
James Smith - IT Director
uWish Ltd - http://www.uWish.co.uk 




~|
Upgrade to Adobe ColdFusion MX7 
Experience Flex 2 & MX7 integration & create powerful cross-platform RIAs 
http: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:267460
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: 
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4

RE: SQL Server, querying between two DBs

[James Smith]

> Our dba finally got out of his meeting. Turns out that not 
> only are they separate databases, but they're also on a 
> different server.
> 
> So it's [server_name].databasename.dbo.tablename.

This will work fine but remember to set up "linked servers" in Enterprise
Manager or the cross-server join will fail.  It is also worth noting that
this can be rather unpredictable in speed sometimes returning results
lightning fast and sometimes crawling so it may be worth setting up
replication to copy one of the databases across to the other server and
perform your queries against that.

--
James Smith - IT Director
uWish Ltd - http://www.uWish.co.uk 


~|
Upgrade to Adobe ColdFusion MX7 
Experience Flex 2 & MX7 integration & create powerful cross-platform RIAs 
http: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:267446
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4

RE: SQL Server, querying between two DBs

[Andy Matthews]

Perfect...

Our dba finally got out of his meeting. Turns out that not only are they
separate databases, but they're also on a different server.

So it's [server_name].databasename.dbo.tablename.

Thanks for getting back to me guys. 

-Original Message-
From: Jim Wright [mailto:[EMAIL PROTECTED] 
Sent: Tuesday, January 23, 2007 3:35 PM
To: CF-Talk
Subject: Re: SQL Server, querying between two DBs

Andy Matthews wrote:
> I've got two DBs showing up in Enterprise Manager. I need to query 
> against both of them. In MySQL you can do this by simply prepending 
> the db name to the tablename like so:
>  
> SELECT t1.id
> FROM db1.t1 t1
> INNER JOIN db2.t1 t2
> ON t1.id = t2.fkID
>  

Assuming that the databases are on the same SQL instance, then you just need
to prepend the database and schema name...and you can use a shortcut for the
schema...

say all objects are owned by dbo

you can do..
SELECT t1.id
FROM db1.dbo.t1 t1
INNER JOIN db2.dbo.t1 t2
ON t1.id = t2.fkID

or

SELECT t1.id
FROM db1..t1 t1
INNER JOIN db2..t1 t2
ON t1.id = t2.fkID

and, of course this assumes that the user you are connecting with has the
correct rights to both dbs



~|
Upgrade to Adobe ColdFusion MX7 
Experience Flex 2 & MX7 integration & create powerful cross-platform RIAs 
http: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:267380
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: 
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4

Re: SQL Server, querying between two DBs

[Jim Wright]

Andy Matthews wrote:
> I've got two DBs showing up in Enterprise Manager. I need to query against
> both of them. In MySQL you can do this by simply prepending the db name to
> the tablename like so:
>  
> SELECT t1.id
> FROM db1.t1 t1
> INNER JOIN db2.t1 t2
> ON t1.id = t2.fkID
>  

Assuming that the databases are on the same SQL instance, then you just 
need to prepend the database and schema name...and you can use a 
shortcut for the schema...

say all objects are owned by dbo

you can do..
SELECT t1.id
FROM db1.dbo.t1 t1
INNER JOIN db2.dbo.t1 t2
ON t1.id = t2.fkID

or

SELECT t1.id
FROM db1..t1 t1
INNER JOIN db2..t1 t2
ON t1.id = t2.fkID

and, of course this assumes that the user you are connecting with has 
the correct rights to both dbs

~|
Upgrade to Adobe ColdFusion MX7 
Experience Flex 2 & MX7 integration & create powerful cross-platform RIAs 
http: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:267379
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: 
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4

RE: SQL Server, querying between two DBs

[Dave Watts]

> I've got two DBs showing up in Enterprise Manager. I need to 
> query against both of them. In MySQL you can do this by 
> simply prepending the db name to the tablename like so:
>  
> SELECT t1.id
> FROM db1.t1 t1
> INNER JOIN db2.t1 t2
> ON t1.id = t2.fkID
>  
> How would you do this in Enterprise Manager?

I'm not sure what you mean by doing this "in Enterprise Manager". You can
query across databases within the same SQL Server instance using the same
syntax you use in MySQL, as long as you're using a login with rights to both
databases. If the databases are on separate instances or servers, you will
have to set the second server up as a linked server within the first one,
and map your local login to the appropriate login on the second server.

If you're actually in Enterprise Manager, and you want to run a query,
select Query Analyzer from the Tools menu, or just open it directly from the
Start menu.

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!


~|
Upgrade to Adobe ColdFusion MX7 
Experience Flex 2 & MX7 integration & create powerful cross-platform RIAs 
http: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:267377
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: 
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4

Re: SQL Server, querying between two DBs

[Robertson-Ravo, Neil (RX)]

You use Query Analyser, Tools > Query Analyser (in SQL 2000).  New Query in
2005.


..



"This e-mail is from Reed Exhibitions (Gateway House, 28 The Quadrant,
Richmond, Surrey, TW9 1DN, 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: Andy Matthews
To: CF-Talk
Sent: Tue Jan 23 21:15:38 2007
Subject: SQL Server, querying between two DBs

I've got two DBs showing up in Enterprise Manager. I need to query against
both of them. In MySQL you can do this by simply prepending the db name to
the tablename like so:
 
SELECT t1.id
FROM db1.t1 t1
INNER JOIN db2.t1 t2
ON t1.id = t2.fkID
 
How would you do this in Enterprise Manager?
 

 
Andy Matthews
Senior Coldfusion Developer

Office:  877.707.5467 x747
Direct:  615.627.9747
Fax:  615.467.6249
[EMAIL PROTECTED]
www.dealerskins.com  
 




~|
Upgrade to Adobe ColdFusion MX7 
Experience Flex 2 & MX7 integration & create powerful cross-platform RIAs 
http: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:267376
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: 
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4

Re: SQL Server Import Help Needed

[Richard Cooper]

Is this a one off import? If yes, I've never had a problem pasting excel into 
access. From there it should be easier to import into sql server. 

~|
Upgrade to Adobe ColdFusion MX7 
Experience Flex 2 & MX7 integration & create powerful cross-platform RIAs 
http: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:266728
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: 
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4

RE: SQL Server Import Help Needed

[Dawson, Michael]

I ran into this last year, but I forgot the exact cure.  Try changing
your ntext field to a varchar large enough to hold your text.  Then,
once imported, change the varchar back to ntext.

If that doesn't work, save the spreadsheet as a delimited text file and
then import that.

I believe the problem, that I had, was that there were too many "long"
text fields in my table and SQL would throw the error about the record
length.

M!ke 

-Original Message-
From: Les Mizzell [mailto:[EMAIL PROTECTED] 
Sent: Tuesday, January 16, 2007 6:36 PM
To: CF-Talk
Subject: OT: SQL Server Import Help Needed

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:266721
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4

Re: SQL Server Error 8179: Could not find prepared statement with handle x

[George Abraham]

Thanks,
George

On 1/9/07, Richard Kroll <[EMAIL PROTECTED]> wrote:
>
> The drivers we used are located at http://jtds.sourceforge.net/
>
> Rich Kroll
>
>


~|
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:266062
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: 
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4

RE: SQL Server Error 8179: Could not find prepared statement with handle x

[Richard Kroll]

The drivers we used are located at http://jtds.sourceforge.net/

Rich Kroll


> -Original Message-
> From: George Abraham [mailto:[EMAIL PROTECTED]
> Sent: Tuesday, January 09, 2007 10:02 AM
> To: CF-Talk
> Subject: Re: SQL Server Error 8179: Could not find prepared statement
with
> handle x
> 
> Hmm, we did have other problems with the current drivers we are using
that
> was shipped with the product by (then) Macromedia. Mayve we can look
at
> this
> driver you mention.
> 
> Thanks,
> George
> 


~|
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:266046
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: 
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4

Re: SQL Server Error 8179: Could not find prepared statement with handle x

[George Abraham]

Hmm, we did have other problems with the current drivers we are using that
was shipped with the product by (then) Macromedia. Mayve we can look at this
driver you mention.

Thanks,
George

On 1/9/07, Richard Kroll <[EMAIL PROTECTED]> wrote:
>
> George,
> We ran into the same problem a few months ago.  What we found was the
> error was thrown on random queries that had CFQUERYPARAMs in them (which
> was almost every query in our application).  Somewhere on the net I
> found an article explaining that it had something to do with the
> drivers.  We switched our drivers to the jTDS JTDS drivers for MS SQL 2k
> and the problem went away.
>
> HTH
>
> Rich Kroll
>
> > -Original Message-
> > From: George Abraham [mailto:[EMAIL PROTECTED]
> > Sent: Tuesday, January 09, 2007 8:38 AM
> > To: CF-Talk
> > Subject: SQL Server Error 8179: Could not find prepared statement with
> > handle x
> >
> > Hi all,
> > One of our CFMX 7 applications (which was working fine before)
> suddenly
> > started throwing this error - SQL Server Error 8179: Could not find
> > prepared
> > statement with handle x. The funny part is, it throws the error at
> pretty
> > much any point in the application without any apparent reason in the
> > sequence of code. If I comment out that code, then the some other
> query
> > code
> > will malfunction the same way with a different value for x. I looked
> > around
> > for solutions, but could not get any usable ones. One thing that
> worked
> > for
> > us is to increase the maximum number of pooled statements by 1 in the
> > datasource and then the problem went away. However it would be nice to
> > know
> > why the heck it happened in the first place.
> >
> > Thanks,
> > George
> >
> >
> >
>
> 

~|
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:266044
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: 
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4

RE: SQL Server Error 8179: Could not find prepared statement with handle x

[Richard Kroll]

George, 
We ran into the same problem a few months ago.  What we found was the
error was thrown on random queries that had CFQUERYPARAMs in them (which
was almost every query in our application).  Somewhere on the net I
found an article explaining that it had something to do with the
drivers.  We switched our drivers to the jTDS JTDS drivers for MS SQL 2k
and the problem went away.

HTH

Rich Kroll

> -Original Message-
> From: George Abraham [mailto:[EMAIL PROTECTED]
> Sent: Tuesday, January 09, 2007 8:38 AM
> To: CF-Talk
> Subject: SQL Server Error 8179: Could not find prepared statement with
> handle x
> 
> Hi all,
> One of our CFMX 7 applications (which was working fine before)
suddenly
> started throwing this error - SQL Server Error 8179: Could not find
> prepared
> statement with handle x. The funny part is, it throws the error at
pretty
> much any point in the application without any apparent reason in the
> sequence of code. If I comment out that code, then the some other
query
> code
> will malfunction the same way with a different value for x. I looked
> around
> for solutions, but could not get any usable ones. One thing that
worked
> for
> us is to increase the maximum number of pooled statements by 1 in the
> datasource and then the problem went away. However it would be nice to
> know
> why the heck it happened in the first place.
> 
> Thanks,
> George
> 
> 
> 

~|
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:266039
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: 
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4

Re: SQL Server - starting integer at 1000

[Will Tomlinson]

thanks guys!!

Will

~|
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:265529
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: 
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4

Re: SQL Server - starting integer at 1000

[Pete Freitag]

Hi Will,

Set the identity seed to 1000 in enterprise manager.

-- 
Pete Freitag
http://foundeo.com/ - ColdFusion Consulting & Products
http://petefreitag.com/ - My Blog
http://spendfish.com/ - Fish for Deals

On 1/3/07, Will Tomlinson <[EMAIL PROTECTED]> wrote:
> I've decided to change an orderID PK from varchar to int data type. I'll set 
> it to have a yes identity so it'll increment, but how how do you set it to 
> start at say, 1000. I don't want people's orders starting at 1. lol!
>
> Thanks,
> Will

~|
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:265528
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4

RE: SQL Server - starting integer at 1000

[Adkins, Randy]

In SQL Server, open the table in Design mode, select the field and set
it to 
Identity and set the SEED to 1000.

 

-Original Message-
From: Will Tomlinson [mailto:[EMAIL PROTECTED] 
Sent: Wednesday, January 03, 2007 7:58 AM
To: CF-Talk
Subject: SQL Server - starting integer at 1000

I've decided to change an orderID PK from varchar to int data type. I'll
set it to have a yes identity so it'll increment, but how how do you set
it to start at say, 1000. I don't want people's orders starting at 1.
lol!

Thanks,
Will



~|
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:265527
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4

Re: SQL Server 2005 JDBC Driver

[Jochem van Dieten]

Tim Claremont wrote:
> I am running CFMX 7.1 on Win2k3 Server with SQL Server 2005

CF MX 7.0 or CF MX 6.1?


> I just installed SQL Server 2005, and I am unable to connect to it 
> with the JDBC Driver. I have downloaded the driver from MS, and 
> followed the instructions in the CF TechNote on the Adobe site. I then 
> restarted the CFMX Server service. No luck.
> 
> The error complains that it cannot log into the database. Naturally I 
> have checked and rechecked usernames and passwords, and the same 
> username and password works fine with the ODBC connection.

Do you use Windows authentication or MS SQL Server authentication?

Jochem

~|
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:263407
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4

RE: SQL Server 2005 vs MySQL 5?

[Munson, Jacob]

> > Some of my frustrations with MySQL are:
> > - Only 1 query per 
> 
> I happen to think that is a good thing :)

Sorry, I have to jump in here.  I agree with this, it is a good thing.
They don't allow multiple queries because to do so is a security risk.
But I always laugh because whenever I bring this up, SQL Server guys
invariably get on my case stating that there's no security problem, or
that MySQL DOES allow this.  A guy commenting on this blog post even
tried to convince me the problem was in ColdFusion instead of PHP:
http://techfeed.net/blog/index.cfm/2006/11/1/Joel-Spolsky-errors-about-S
QL-injection



--
This transmission may contain information that is privileged, confidential 
and/or exempt from disclosure under applicable law. If you are not the intended 
recipient, you are hereby notified that any disclosure, copying, distribution, 
or use of the information contained herein (including any reliance thereon) is 
STRICTLY PROHIBITED. If you received this transmission in error, please 
immediately contact the sender and destroy the material in its entirety, 
whether in electronic or hard copy format. Thank you. 

==
"EMF " made the previous annotations.

~|
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:260361
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: 
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4

Re: SQL Server 2005 vs MySQL 5?

[Adrian Moreno]

> Some of my frustrations with MySQL are:
> - Only 1 query per 

MySQL can handle multiple queries, it's just disabled by default. Cameron 
Childress has a post about this.

MySQL: How to Combine Multiple SQL Statements in One CFQUERY
http://www.sumoc.com/blog/index.cfm?mode=entry&entry=7F6F7314-5004-2066-B7A8BDFB26843712

> - Unable to handle complex join queries e.g. I ran a complex query in 
> a trail version of SQL Server 2000 and it returned in 3 seconds, it 
> took 33 mins in MySQL (this may be becuase we are using a non 
> enterprise version - are there different versions???)

I've written extremly complex joins in MySQL that run very fast. Have you 
checked the indexes? Are the tables using MyISAM or InnoDB format? 

> - The server grinds to a holt with my complex queries which in turn 
> kills the website. The same thing happens when I try to export a 
> backup of the entire DB.

Are you using the command-line backup or the MySQL Admin UI? 

-- Adrian

~|
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:260204
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4

Re: SQL Server 2005 vs MySQL 5?

[Jochem van Dieten]

Matthew Chambers wrote:
> 
> Some of my frustrations with MySQL are:
> - Only 1 query per 

I happen to think that is a good thing :)


> - Unable to handle complex join queries e.g. I ran a complex query in 
> a trail version of SQL Server 2000 and it returned in 3 seconds, it 
> took 33 mins in MySQL (this may be becuase we are using a non 
> enterprise version - are there different versions???)

MySQL is obviously taking the wrong execution plan. Have you looked at the 
explain output to see if all the necessary indexes are there? MySQL 5 has a 
better optimizer so upgrading might help. I don't think that there is a 
difference between community and commercial versions.


> - The server grinds to a holt with my complex queries which in turn 
> kills the website.

Fix the query plans.


> The same thing happens when I try to export a 
> backup of the entire DB.

If you want a hot backup of MySQL, either pay for the hot backup tool or run a 
slave.


> I obviously have to justify the cost of SQL Server however would 
> anyone argue the MySQL 5 enterprise would be better?

Unless you want to run it on a non-Windows platform, I don't think MySQL is 
better.

But why limit your choices to MySQL and MS SQL Server? There are so many more 
good databases available.

Jochem

~|
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:260160
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4

RE: SQL Server 2005 vs MySQL 5?

[Snake]

To cut your cost for SQL Server find an SPLA partner and pay $10 month
instead.

Russ 

-Original Message-
From: Matthew Chambers [mailto:[EMAIL PROTECTED] 
Sent: 12 November 2006 23:57
To: CF-Talk
Subject: SQL Server 2005 vs MySQL 5?

Hi,

I've been working on a website built using MySQL and have been frustraited
by it on numerious occasions over the past 8 months. Admittedly we are
running v4.1 however because I've got much more experience with SQL Server
(and from what I've read it is the better choice) I'm looking for some
feedback on MySQL 5 vs SQL Server 2005?

Some of my frustrations with MySQL are:
- Only 1 query per 
- Unable to handle complex join queries e.g. I ran a complex query in a
trail version of SQL Server 2000 and it returned in 3 seconds, it took 33
mins in MySQL (this may be becuase we are using a non enterprise version -
are there different versions???)
- The server grinds to a holt with my complex queries which in turn kills
the website. The same thing happens when I try to export a backup of the
entire DB.

I obviously have to justify the cost of SQL Server however would anyone
argue the MySQL 5 enterprise would be better?

By the way, I can't really understand the pricing structures of both
products. We are only using the DB for the website, so does that mean we
only need 1 liecence? If so does that mean I'm correct in saying that MySQL
basic would be $595/year, where as SQL Server 2005 (5-Client) would be $1550
outright? Why does MySQL having an ongoing cost?

Also, can anyone recommend MySQL Silver? Is the enterprise dashboard really
worth it?

Cheers
Matthew



~|
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:260138
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4

Re: SQl Server 2005 w/ Mirroring. How does CF know when to 'failo ver' to the mirror?

[Dan Plesse]

It mainly shows a way to make your code more compact and reading stuff from
a properties file and using that infomation in your JDBC connection. Does
SQL server send out information when it restarts?
If isClosed() is YES sever is down and another check or ping isClosed() No
means it's back.


~|
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:255929
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: 
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4

RE: SQl Server 2005 w/ Mirroring. How does CF know when to 'failo ver' to the mirror?

[blists]

Hi Dan,

I'm confused, how is this code intended to work? Does it keep the connection
with the new principle? What is the server was restarted. 

blists

-Original Message-
From: Dan Plesse [mailto:[EMAIL PROTECTED] 
Sent: October 7, 2006 11:36 PM
To: CF-Talk
Subject: Re: SQl Server 2005 w/ Mirroring. How does CF know when to 'failo
ver' to the mirror?

You can also use java util props and keep shrinking the code until you get
one line.

Like the CF classLoader, one line is all you need!



It would interesting if you load these lines into CF from a props file.
Call it sql_server.properties

 
 
 
 










~|
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:255928
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: 
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4

Re: SQl Server 2005 w/ Mirroring. How does CF know when to 'failo ver' to the mirror?

[Dan Plesse]

And finally if isClosed() equals YES then its closed and a NO means its
open.















~|
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:255916
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4

Re: SQl Server 2005 w/ Mirroring. How does CF know when to 'failo ver' to the mirror?

[Dan Plesse]

I think this is it.










~|
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:255915
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: 
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4

Re: SQl Server 2005 w/ Mirroring. How does CF know when to 'failo ver' to the mirror?

[Dan Plesse]

You can also use java util props and keep shrinking the code until you get
one line.

Like the CF classLoader, one line is all you need!



It would interesting if you load these lines into CF from a props file.
Call it sql_server.properties

 
 
 
 








~|
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:255914
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4

RE: SQl Server 2005 w/ Mirroring. How does CF know when to 'failo ver' to the mirror?

[blists]

Following up on this, I was able to set up the mirroring on SQL 2005 between
2 servers using he High Availability mode with a 3rd witness server to
provide the automatic failover. The SQL side of things works great. If I
take the Primary DB server "DBServer1", offline (by stopping the SQL
Server), the "DBServer2" which was the Mirror becomes the primary. The code
below also works by automatically contacting the DBServer2 when the
DBServer1 can not be reached. 

However it is a bit slow, because it tries to contact DBServer1 first and
then uses DBServer2 - but I think this is because the connection info is not
being cached because the connection is being opened and closed and that this
may not happen when it is set up as a regular datasource.

The only real problem is this; When the DB the was originally the Primary
(the one that went down) comes back up, it assumes the role of mirror, and
the failover server REMAINS the primary. This is apparently how the
mirroring works in SQL Server 2005. The problem with this is two fold. For
starters, it means you would need to have a fairly robust mirror, since it
may take over as the primary some day. And the bigger problem is that once
the first server server comes back on-line and starts actings as the Mirror
the code below returns an error because it can contact the first server, but
fails when opening the DB because the Mirror DB is inaccessible as it is in
RESTORE mode and can not be accessed. 

It would be nice if once the Original Primary DB Server came back online
that it would resume Primary Duties and switch with the Mirror currently
acting as the Primary.  










#con.getWarnings()#
Driver Name: #DatabaseMetaData.getDriverName()#
Driver Version: #DatabaseMetaData.getDriverVersion()#
Database Name:#DatabaseMetaData.getDatabaseProductName()#
Database Version: #DatabaseMetaData.getDatabaseProductVersion()#



-Original Message-
From: blists [mailto:[EMAIL PROTECTED] 
Sent: October 5, 2006 12:05 PM
To: CF-Talk
Subject: RE: SQl Server 2005 w/ Mirroring. How does CF know when to 'failo
ver' to the mirror?

Hi Dave,

Thanks for the feedback, after some more searching last night, I found a
thread that showed how to add an alternateServers=(server2:1433) to the
connection string which would configure the datasource to automatically
contact the second / alternate server is the first was down.

http://forums.datadirect.com/ddforums/message.jspa?messageID=3685


Adobe also has an article about this and about how those settings when
inputed directly in the datasource config in cfadmin would be lost and need
to be manually added to the neo-query.xml file
http://www.macromedia.com/go/25b0de5e

I was able to add this parameter and have been testing it but am not quite
there yet. I don't actually even have two database servers set up yet..so..
I did manage to hack together this code to test the datasource configuration
and see if the failover works. This is basically the same code from the
article above combined with some insights gained from an adobe article:

ColdFusion MX: Creating a pure Java connection to a data source
http://www.adobe.com/cfusion/knowledgebase/index.cfm?id=tn_19607















#con.getWarnings()#
Driver Name: #DatabaseMetaData.getDriverName()#
Driver Version: #DatabaseMetaData.getDriverVersion()#
Database Name:#DatabaseMetaData.getDatabaseProductName()#
Database Version: #DatabaseMetaData.getDatabaseProductVersion()#



  Error - info below
  






  Close Error - info below
  



I'm gonna keep trying..

Blists

-Original Message-
From: Dave Watts [mailto:[EMAIL PROTECTED] 
Sent: October 4, 2006 8:37 PM
To: CF-Talk
Subject: RE: SQl Server 2005 w/ Mirroring. How does CF know when to 'failo
ver' to the mirror?

> I am just doing some research into this. With SQL Server 2005 
> and mirroring, the mirror will automatically take over if the 
> primary/principle server fails. But if you the datasource 
> references the principle SQL Servers name, and that server 
> was down, how would it failover?
> 
> The only thing I can think of is that you may need to setup 
> two datasources for the principle and mirror and set up a 
> connection check within the CF application code that checks 
> the server and changes the #datasource# var used in the app 
> to the mirror if the principle is down.
> 
> But this seems incorrect, because in which state would we 
> consider the princible down? And would those states match the 
> mirrored states? And what about client variable storage, 
> would that need the same type of thing? Any have a mirrored 
> SQL Server and coldfusion running together? How do you d

RE: SQl Server 2005 w/ Mirroring. How does CF know when to 'failo ver' to the mirror?

[blists]

Hi Dave,

Thanks for the feedback, after some more searching last night, I found a
thread that showed how to add an alternateServers=(server2:1433) to the
connection string which would configure the datasource to automatically
contact the second / alternate server is the first was down.

http://forums.datadirect.com/ddforums/message.jspa?messageID=3685


Adobe also has an article about this and about how those settings when
inputed directly in the datasource config in cfadmin would be lost and need
to be manually added to the neo-query.xml file
http://www.macromedia.com/go/25b0de5e

I was able to add this parameter and have been testing it but am not quite
there yet. I don't actually even have two database servers set up yet..so..
I did manage to hack together this code to test the datasource configuration
and see if the failover works. This is basically the same code from the
article above combined with some insights gained from an adobe article:

ColdFusion MX: Creating a pure Java connection to a data source
http://www.adobe.com/cfusion/knowledgebase/index.cfm?id=tn_19607















#con.getWarnings()#
Driver Name: #DatabaseMetaData.getDriverName()#
Driver Version: #DatabaseMetaData.getDriverVersion()#
Database Name:#DatabaseMetaData.getDatabaseProductName()#
Database Version: #DatabaseMetaData.getDatabaseProductVersion()#



  Error - info below
  






  Close Error - info below
  



I'm gonna keep trying..

Blists

-Original Message-
From: Dave Watts [mailto:[EMAIL PROTECTED] 
Sent: October 4, 2006 8:37 PM
To: CF-Talk
Subject: RE: SQl Server 2005 w/ Mirroring. How does CF know when to 'failo
ver' to the mirror?

> I am just doing some research into this. With SQL Server 2005 
> and mirroring, the mirror will automatically take over if the 
> primary/principle server fails. But if you the datasource 
> references the principle SQL Servers name, and that server 
> was down, how would it failover?
> 
> The only thing I can think of is that you may need to setup 
> two datasources for the principle and mirror and set up a 
> connection check within the CF application code that checks 
> the server and changes the #datasource# var used in the app 
> to the mirror if the principle is down.
> 
> But this seems incorrect, because in which state would we 
> consider the princible down? And would those states match the 
> mirrored states? And what about client variable storage, 
> would that need the same type of thing? Any have a mirrored 
> SQL Server and coldfusion running together? How do you do it?

SQL Server 2005's mirroring is somewhat like log shipping in SQL Server
2000. With that, you'd have a primary and a standby server, and transaction
logs would be shipped periodically from the primary to the standby. If the
primary failed, you'd have to manually reconfigure everything to point to
the standby - you'd typically do this by changing the IP address of the
standby to that of the primary, and restart things as necessary. You would
lose whatever data had been added to the primary since the last log shipping
operation. SQL Server 2005's mirroring includes replication functionality as
well - in SQL Server 2000, replication and log shipping are completely
separate, although many DBAs would use them together. Mirroring is also
easier to implement than replication or log shipping in 2000, although I
don't know (yet) whether it's more or less reliable and/or fragile.

SQL Server 2005 has an additional feature which I've only read a bit about -
the ability to have a monitor server that's used to automatically flip
connections to the standby when the primary fails. I have no idea how that
works exactly, because I haven't used it.

Finally, SQL Server 2000 and 2005 both support clustering, which presents a
single virtual IP to your database clients. This requires some pretty
specific hardware support, and if that hardware fails, you get a big-time
failure - cluster members essentially lose all network connectivity. Ouch!

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!



~|
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.houseoffusio

RE: SQl Server 2005 w/ Mirroring. How does CF know when to 'failo ver' to the mirror?

[Dave Watts]

> I am just doing some research into this. With SQL Server 2005 
> and mirroring, the mirror will automatically take over if the 
> primary/principle server fails. But if you the datasource 
> references the principle SQL Servers name, and that server 
> was down, how would it failover?
> 
> The only thing I can think of is that you may need to setup 
> two datasources for the principle and mirror and set up a 
> connection check within the CF application code that checks 
> the server and changes the #datasource# var used in the app 
> to the mirror if the principle is down.
> 
> But this seems incorrect, because in which state would we 
> consider the princible down? And would those states match the 
> mirrored states? And what about client variable storage, 
> would that need the same type of thing? Any have a mirrored 
> SQL Server and coldfusion running together? How do you do it?

SQL Server 2005's mirroring is somewhat like log shipping in SQL Server
2000. With that, you'd have a primary and a standby server, and transaction
logs would be shipped periodically from the primary to the standby. If the
primary failed, you'd have to manually reconfigure everything to point to
the standby - you'd typically do this by changing the IP address of the
standby to that of the primary, and restart things as necessary. You would
lose whatever data had been added to the primary since the last log shipping
operation. SQL Server 2005's mirroring includes replication functionality as
well - in SQL Server 2000, replication and log shipping are completely
separate, although many DBAs would use them together. Mirroring is also
easier to implement than replication or log shipping in 2000, although I
don't know (yet) whether it's more or less reliable and/or fragile.

SQL Server 2005 has an additional feature which I've only read a bit about -
the ability to have a monitor server that's used to automatically flip
connections to the standby when the primary fails. I have no idea how that
works exactly, because I haven't used it.

Finally, SQL Server 2000 and 2005 both support clustering, which presents a
single virtual IP to your database clients. This requires some pretty
specific hardware support, and if that hardware fails, you get a big-time
failure - cluster members essentially lose all network connectivity. Ouch!

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!

~|
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:255605
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: 
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4

Re: SQL Server Upgrade and Coldfusion

[Rick Root]

JediHomer wrote:
> As far as can remember, if you use the short hand for outer joins... e.g.
> 
> select *
> from member, payment
> where member.id *= payment.member_id
> 
> Aren't supported by default, you have to change something on the server...
> 
> I believe this is blogged on Ben Forta's Blog
> (http://www.forta.com/blog/index.cfm/2006/1/15/SQL-Server-2005-Outer-Join-Gotcha)

Interesting.  thankfully, I have been using ANSI join syntax for a long 
long time, and stopped doing where clause joins years ago =)

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:253760
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4

RE: SQL Server 2005

[Steve LaBadie]

Thanks very much for the insight Jim.

Steve LaBadie, Web Manager
East Stroudsburg University
200 Prospect St.
East Stroudsburg, Pa 18301
570-422-3999
[EMAIL PROTECTED]
http://www.esu.edu

-Original Message-
From: Jim Wright [mailto:[EMAIL PROTECTED] 
Sent: Thursday, September 21, 2006 11:54 AM
To: CF-Talk
Subject: Re: SQL Server 2005

Steve LaBadie wrote:
> I have upgraded my web server from Access to SQL Server.  Is there a
GUI
> interface for creating tables and database similar to Access?
> 

In addition to Management Studio, you can use Access to manage a SQL 
database fairly effectively...create a new Access Project and point it 
at your SQL server.  Depending on the permissions of the user you 
connect with, you will be able to do many of the management functions 
that you would find in Management Studio.  While you definitely need to 
familiarize yourself with Management Studio, access projects can be 
useful to build quick interfaces to a database to manage things that 
might not warrant a web interface or standalone desktop app.



~|
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:253750
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: 
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4

RE: SQL Server 2005

[Robertson-Ravo, Neil (RX)]

Express Edition is a different tool. You don't need it if you have the full
standard version


-Original Message-
From: Steve LaBadie [mailto:[EMAIL PROTECTED] 
Sent: 21 September 2006 16:54
To: CF-Talk
Subject: RE: SQL Server 2005

I did install Management Studio, so I assume I do not need the Express
Edition?

Can you recommend any particular books on how to use the Management
Studio?

Steve LaBadie, Web Manager
East Stroudsburg University
200 Prospect St.
East Stroudsburg, Pa 18301
570-422-3999
[EMAIL PROTECTED]
http://www.esu.edu

-Original Message-
From: Robertson-Ravo, Neil (RX)
[mailto:[EMAIL PROTECTED] 
Sent: Thursday, September 21, 2006 11:46 AM
To: CF-Talk
Subject: RE: SQL Server 2005

If you have standard you will have SQL Server Management Studio if you
chose
to install it!



-Original Message-
From: Steve LaBadie [mailto:[EMAIL PROTECTED] 
Sent: 21 September 2006 16:39
To: CF-Talk
Subject: RE: SQL Server 2005

Installed standard edition 32-bit

What is SQL Server 2005 Express Edition compared to the Management
Studio?

Steve LaBadie, Web Manager
East Stroudsburg University
200 Prospect St.
East Stroudsburg, Pa 18301
570-422-3999
[EMAIL PROTECTED]
http://www.esu.edu
-Original Message-
From: Robertson-Ravo, Neil (RX)
[mailto:[EMAIL PROTECTED] 
Sent: Thursday, September 21, 2006 11:32 AM
To: CF-Talk
Subject: RE: SQL Server 2005

SQL Server Management Studio (used to be Enterprise Manager in 2000).

Which version of SQL 2005 did you install? I seem to remember that MSDE
does
not come with an IDE.






-Original Message-
From: Steve LaBadie [mailto:[EMAIL PROTECTED] 
Sent: 21 September 2006 16:27
To: CF-Talk
Subject: SQL Server 2005

I have upgraded my web server from Access to SQL Server.  Is there a GUI
interface for creating tables and database similar to Access?

 

Steve LaBadie, Web Manager
East Stroudsburg University
200 Prospect St.
East Stroudsburg, Pa 18301
570-422-3999
[EMAIL PROTECTED] <mailto:[EMAIL PROTECTED]> 
http://www.esu.edu <http://www3.esu.edu> 

 













~|
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:253743
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: 
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4

RE: SQL Server 2005

[Steve LaBadie]

I did install Management Studio, so I assume I do not need the Express
Edition?

Can you recommend any particular books on how to use the Management
Studio?

Steve LaBadie, Web Manager
East Stroudsburg University
200 Prospect St.
East Stroudsburg, Pa 18301
570-422-3999
[EMAIL PROTECTED]
http://www.esu.edu

-Original Message-
From: Robertson-Ravo, Neil (RX)
[mailto:[EMAIL PROTECTED] 
Sent: Thursday, September 21, 2006 11:46 AM
To: CF-Talk
Subject: RE: SQL Server 2005

If you have standard you will have SQL Server Management Studio if you
chose
to install it!



-Original Message-
From: Steve LaBadie [mailto:[EMAIL PROTECTED] 
Sent: 21 September 2006 16:39
To: CF-Talk
Subject: RE: SQL Server 2005

Installed standard edition 32-bit

What is SQL Server 2005 Express Edition compared to the Management
Studio?

Steve LaBadie, Web Manager
East Stroudsburg University
200 Prospect St.
East Stroudsburg, Pa 18301
570-422-3999
[EMAIL PROTECTED]
http://www.esu.edu
-Original Message-
From: Robertson-Ravo, Neil (RX)
[mailto:[EMAIL PROTECTED] 
Sent: Thursday, September 21, 2006 11:32 AM
To: CF-Talk
Subject: RE: SQL Server 2005

SQL Server Management Studio (used to be Enterprise Manager in 2000).

Which version of SQL 2005 did you install? I seem to remember that MSDE
does
not come with an IDE.






-Original Message-
From: Steve LaBadie [mailto:[EMAIL PROTECTED] 
Sent: 21 September 2006 16:27
To: CF-Talk
Subject: SQL Server 2005

I have upgraded my web server from Access to SQL Server.  Is there a GUI
interface for creating tables and database similar to Access?

 

Steve LaBadie, Web Manager
East Stroudsburg University
200 Prospect St.
East Stroudsburg, Pa 18301
570-422-3999
[EMAIL PROTECTED] <mailto:[EMAIL PROTECTED]> 
http://www.esu.edu <http://www3.esu.edu> 

 











~|
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:253742
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4

Re: SQL Server 2005

[Jim Wright]

Steve LaBadie wrote:
> I have upgraded my web server from Access to SQL Server.  Is there a GUI
> interface for creating tables and database similar to Access?
> 

In addition to Management Studio, you can use Access to manage a SQL 
database fairly effectively...create a new Access Project and point it 
at your SQL server.  Depending on the permissions of the user you 
connect with, you will be able to do many of the management functions 
that you would find in Management Studio.  While you definitely need to 
familiarize yourself with Management Studio, access projects can be 
useful to build quick interfaces to a database to manage things that 
might not warrant a web interface or standalone desktop app.

~|
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:253741
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4