Returning SQL and other query metadata

I wanted to return the sql from a query that I was running to find out why no records were returned. Found information here about query metadata that not only returns the sql run at the server, but tells you about returning the parameters too.

qryTest.getMetaData().getExtendedMetaData().sql

qryTest.getMetaData().getExtendedMetaData().sqlparameters

SQL Server Date Formats

As part of my last post I needed to spit out the date in the right format because I was using a javascript output that I couldn't use coldfusion to format. So I needed to use coldfusion and found a great reference for formatting dates natively in SqlServer:

http://www.sql-server-helper.com/tips/date-formats.aspx

SQL query join that returns only a max value in the joined table.

I have a somewhat complicated query. This query includes 4 or 5 tables. In my app, changes to the company table are logged to a company history table. I simply record the time of the update and that it was an update. When the record is created an entry is made recording the date and the type of "create.

Anyway, some records have been updated several times and I am returning search results. The user asked to have those results returned with the last updated date, which means I needed to join to that history table and return only the max(date) from records linked to the corresponding company.

The answer was to use a derived query and I found a great example here: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=102255

ColdFusion 8 And SQL Server 2005

I had a problem setting up a datasource for coldfusion using cf8 and sql server 2005. I read several articles:

ColdFusion And SQL Server 2005
ColdFusion + SQL Server Express 2005

It wasn't working until I went to services and started the "SQL Server Browser" service which had been disabled. Don't know why. But once I started that it worked.

Not sure which of the fixes from the article helped also, but there you go.

DTSWizard.exe and Business Intelligence Development Studio

I have always hated the fact that it was so difficult to do a simple import into sql server with the new version 2005 stuff, like the management express studio. A year or so ago I spent time looking all over for dtswizard.exe and since I just got a new computer from dell I was doing the same thing. It started because I simply wanted to move some data over from excell to a 2005 sql server database.

This use to be a piece of cake in SQL 2000. You just right click on the database and choose import. No idea why microsoft would turn this into such a nightmare.

After spending the last 2 1/2 hours on this I finally find out, I think, that Business Intelligence Development Studio is where this functionality lies. Couldn't figure out how to find that. Normal install doesn't always do it. Found this forum here: http://social.msdn.microsoft.com/Forums/en-US/tfsreporting/thread/eac00857-a7af-4075-aa31-e52335c59304/

The post by a.netAvoider is what worked for me:

Thanks guys,I got away with only uninstalling Workstation components. This way you won't lose your database settings and save yourself a whole lot of installation time.To do this I opened Add/Remove programs from Control Panel, selected SQL Server 2005 Express, clicked on uninstall, and in the uninstall dialogue box which appears next unticked SQL Server 2005 instance and selected Work Station components in the list below.The I installed BIZ DEV using SQL Express Toolkit installation file. I just downloaded it now ( 17/12/2007 ). You'd hope microsoft would have fixed this issue in 5 months.

"Error 15023: User or role '%s' already exists in the current database."

Got this error recently when I downloaded a clients database from a shared host to my local machine. Tried to add the same user.

This article resolved it for me: http://www.codeproject.com/database/OrphanedSQLUsers.asp.

Here is the SQl that I ran "sp_change_users_login 'auto_fix', 'user'"

Next N records, paginated sql server results

I have several places on my web site where I let the user page through search results and recordsets. Usually this is inneficient because your return all resulsts and essentially hide everything but the rows they want to see. This is very inneficient and the only examples of doing this correctly were difficult at best.

Well, with SQL Server 2005 there is a new feature - ROW_NUMBER() that makes this simpoler to implement. Not 100% intuitive but I was able to figure it out. The following link is a good tutorial that helped me:

http://www.davidhayden.com/blog/dave/archive/2005/12/30/2652.aspx

Dynamic SQL Where statements

I was trying to figure out how to use an if statement in the where clause of a sql statement. I came across this article that explains the use of coalesce. This is a great way to handle this.

http://www.sqlteam.com/item.asp?ItemID=2077

SELECT Cus_Name,
Cus_City,
Cus_Country
FROM Customers
WHERE Cus_Name = COALESCE(@Cus_Name,Cus_Name) AND
Cus_City = COALESCE(@Cus_City,Cus_City) AND
Cus_Country = COALESCE(@Cus_Country,Cus_Country)

Read the article to understand it. Basically if there is a value provided to the stored procedure then the comparison is done. If there is no value it essentially includes all records or (matches itself). I know, that doesn't make sense.

How do I use a variable in a TOP clause in SQL Server?

http://sqlserver2000.databases.aspfaq.com/how-do-i-use-a-variable-in-a-top-clause-in-sql-server.html

I have a web page that runs a script which is so slow that it will time out if the number of records is too great. The temporary solution is to split the records into two subsets and then process those sets seperately. Doing that manually was not too big of a deal, but I wanted a better way.

I calculate the number of rows / 2.
Then I needed to use that value to automatically split the set. The only thing I needed to change was the brackets around the variable. So it turned out to be this:

Select top (@myvar) from mytable.

The article goes way more into depth