For a rigorous marker of the source database state, I'd like to capture the @@DBTS of an external database in a sproc. Yeah, I think I could issue USE ExternalDB GO SELECT @myVarbinary8 = @@DBTS GO USE OriginalDB GO but, even if I could, it seems ugly. For now, I've embedded a scalar-valued function in the source database to i...
I have a table "Blah" with a PK column BlahID and an XML column BlahItems in a database on SQL Server 2005. This table has records as follows... BlahID BlahItems ------ ------------------------------------------------------ 1 <root><item name="Fred" /><item name="Wilma" /></root> 2 <root><item name="Lisa" /><item name="Bart"...
I have a stored procedure that has a optional parameter, @UserID VARCHAR(50). The thing is, there are two ways to work with it: Give it a default value of NULL, the have an IF...ELSE clause, that performs two different SELECT queries, one with 'WHERE UserID = @UserID' and without the where. Give it a default value of '%' and then just ...
I am building a modular application. Through configuration you can turn these application modules on and off. I'm trying to determine what database structure (mssql2005) I should use for the tables that hold data for each of the modules. The two options that I've thought of are: Put all the tables into one big database and prefix th...
I am trying to convert a date with individual parts such as 12, 1, 2007 into a datetime in SQL Server 2005. I have tried the following: CAST(DATEPART(year, DATE)+'-'+ DATEPART(month, DATE) +'-'+ DATEPART(day, DATE) AS DATETIME) but this results in the wrong date. What is the correct way to turn the three date values into a proper date...
Hi , If I need to upgrade MSSQL 2000 TO MSSQL 2005 which is installed inside the citrix box. How can I check if it is compatible or not. Thanks ...
Is it possible to make efficient queries that use the complete regular expression feature set. If not Microsoft really should consider that feature. ...
How can you detect if your sql-server 2005 instance is a 32 or 64 bit instance from C# code? If you know some documentation on this issue, please let me know.. Thanks! ...
I have been reading about the differences between Table Variables and Temp Tables and stumbled upon the following issue with the Table Variable. I did not see this issue mentioned in the articles I pursued. I pass in a series of PKs via a XML data type and successfully create the records in both temp table structures. When I attempt ...
How can we find out that how many applications are there in a database? ...
I'd like to have a job that runs nightly, or even just once a week, that generates a script of our dev databases. They tend to be tinkered with, and developers have a habit of making changes without scripting them, or documenting them. I'd like to create a job that will essentially mimic what happens when I right-click and do Tasks > Ge...
Are there any tricks for preventing SQL Server from entitizing chars like &, <, and >? I'm trying to output a URL in my XML file but SQL wants to replace any '&' with '&' Take the following query: SELECT 'http://foosite.com/' + RTRIM(li.imageStore) + '/ImageStore.dll?id=' + RTRIM(li.imageID) + '&raw=1&rev=' + RTRIM...
I'm wondering if I'll have trouble installing SQL Server 2008 on my development machine whilst our production server is 2005? E.g. is something change so that my 2005 databases/sps/functions/views will not work correctly or other issues I should think about? Would like to do this just to test drive the new SQL Server but still be able t...
I got a row structure looks like ID bigint, ScanRept XML I want to wind up with a file named 4.xml containing just the xml from the ScanRept column where the ID number is 4. I don't want to do it interactively (by going into Manager Studio, finding the row, right-clicking the field and doing Save AS) - that's what I'll do if I can't fi...
This piece of T-SQL is deprecated in 2005: BACKUP LOG [DB_NAME] WITH TRUNCATE_ONLY I don't need to keep a backup the log for my db - but I do not want the piece of code to stop working if we port to SQL2008 or successive versions in future. Cheers ...
Hi, I have 4 different named instances of SQL Server 2005 on a single server (for testing purposes). There is no default instance on the server. Because I will eventually need to allow communication to these instances across the firewall, I have set the ports of each instance statically listening on all IPs for the server. Edit: TCP/I...
I've got a strange problem with indexing PDF files in SQL Server 2005, and hope someone can help. My database has a table called MediaFile with the following fields - MediaFileId int identity pk, FileContent image, and FileExtension varchar(5). I've got my web application storing file contents in this table with no problems, and am able...
We have an internal application that is using a service account and fine-grained table and field control is controlled by permissions tables in the application. If we wanted to use pass-through security with Active Directory, we have a problem in that a user with odbc access could modify tables outside of business rules that exist in t...
DECLARE @p_date DATETIME SET @p_date = CONVERT( DATETIME, '14 AUG 2008 10:45:30',?) SELECT * FROM table1 WHERE column_datetime = @p_date I need to compare date time like: @p_date=14 AUG 2008 10:45:30 column_datetime=14 AUG 2008 10:45:30 How can I do this? ...
Can I use SQL Authentication with User Instances? ...