This is an adapted version of a question from someone in my office. She's trying to determine how to tell what ports MSDE is running on for an application we have in the field. Answers to that narrower question would be greatly appreciated. I'm also interested in a broader answer that could be applied to any networked applications. ...
I'm looking to use SQL to format a number with commas in the thousands, but no decimal (so can't use Money) - any suggestions? I'm using SQL Server 2005, but feel free to answer for others as well (like MySQL) ...
I've seen a few attempted SQL injection attacks on one of my web sites. It comes in the form of a query string that includes the "cast" keyword and a bunch of hex characters which when "decoded" are an injection of banner adverts into the DB. My solution is to scan the full URL (and params) and search for the presence of "cast(0x" and i...
I have some code that uses SMO to populate a list of available SQL Servers and databases. While we no longer support SQL Server 2000, it's possible that the code could get run on a machine that SQL Server 2000 and not have the SMO library installed. I would perfer to check for SMO first and degrade the functionality gracefully instead ...
What is the best way to get identity of inserted row? I know about @@IDENTITY and IDENT_CURRENT and SCOPE_IDENTITY but don't understand the pros and cons attached to each. Can someone please explain the differences and when I should be using each? ...
In one of my applications I have a 1gb database table that is used for reference data. It has a huge amounts of reads coming off that table but there are no writes ever. I was wondering if there's any way that data could be loaded into RAM so that it doesn't have to be accessed from disk? I'm using SQL Server 2005 ...
Does anyone know of any free tools that can assist in converting an SQL2005 database back to SQL200 format? I know that you can script all the objects and then do a dump of the data, but this is a lot of work to do manually. ...
Is there a graceful way to handle passing a list of ids as a parameter to a stored procedure. For instance, I want departments 1, 2, 5, 7, 20 returned by my stored procedure. In the past, I have passed in a comma delimited list of ids, like the below code, but feel really dirty doing it. SQL Server 2005 is my only applicable limitatio...
Can we have the database and transaction logs on the same drive? What will be its consequences if it is not recommended? ...
In the business I work for we are discussion methods to reduce the read load on our primary database. One option that has been suggested is to have live one-way replication from our primary database to a slave database. Applications would then read from the slave database and write directly to the primary database. So... Application R...
I want to use SQL Profiler to trace the queries executed agains my database, track performance, etc. However it seems that the SQL Profiler is only available in the Enterprise edition of SQL Server 2005. Is this the case indeed, and can I do something about it? ...
We are now using NHibernate to connect to different database base on where our software is installed. So I am porting many SQL Procedures to Oracle. SQL Server has a nice function called DateDiff which takes a date part, startdate and enddate. Date parts examples are day, week, month, year, etc. . . What is the Oracle equivalent? I...
In a stored procedure, when is #Temptable created in SQL Server 2005? When creating the query execution plan or when executing the stored procedure? if (@x = 1) begin select 1 as Text into #Temptable end else begin select 2 as Text into #Temptable end ...
How can I create a custom aggregate function in MS SQL Server? An example would help a lot. ...
Unable to find a SQL diff tool that meets my needs, I am writing my own. Between the INFORMATION_SCHEMA and sys tables, I have a mostly-complete working version. But one thing I can't find in the metadata is the definition of a trigger, you know, the actual SQL code. Am I overlooking something? Thanks. ...
I'm trying to determine the best way to truncate or drop extra decimal places in SQL without rounding. For example: declare @value decimal(18,2) set @value = 123.456 This will auto round @Value to be 123.46....which in most cases is good. However, for this project I don't need that. Is there a simple way to truncate the decimals I...
Does anyone know of a way to alter a computed column without dropping the column in SQL Server. I want to stop using the column as a computed column and start storing data directly in the column, but would like to retain the current values. Is this even possible? ...
How do you shade alternating rows in a SQL Server Reporting Services report? Edit: There are a bunch of good answers listed below--from quick and simple to complex and comprehensive. Alas, I can choose only one... ...
Ideally, this script could be run multiple times, as new tables were added to the database. SQL Server Management Studio generates scripts for individual database objects, but I'm looking for more of a "fire-and-forget" script. ...
This seems like odd behaviour from SQL's full-text-index. FTI stores number in its index with an "NN" prefix, so "123" is saved as "NN123". Now when a user searches for words beginning with N (i.e. contains "n*" ) they also get all numbers. So: select [TextField] from [MyTable] where contains([TextField], '"n*"') Returns: MyTabl...