Why does the PRINT statement in T-SQL seem to only sometimes work? What are the constraints on using it? It seems sometimes if a result set is generated, it becomes a null function, I assumed to prevent corrupting the resultset, but could it's output not go out in another result set, such as the row count? ...
It appear that SQL Server like most other products Random Function really is not that random. So we have this nice little function to generate a 10 char value. Is there a better way to accomplish what the following does. I am betting there is. DECLARE @SaltCount INT; SELECT @SaltCount = COUNT(*) FROM tmp_NewLogin; PRINT 'Set Salt val...
I've recently seen occasional problems with stored procedures on a legacy system which displays error messages like this: Server Message: Number 10901, Severity 17: This query requires X auxiliary scan descriptors but currently there are only Y auxiliary scan descriptors available. Either raise the value of the 'number o...
Hi All, I need to get all the dates present in the date range using SQL Server 2005 ...
I need to pull some BLOB data from a SQL Server 2005 database and generate a SQL script to insert this same data in another database, in another server. I am only allowed to do this using SQL scripts, I can't use any other utility or write a program in Java or .NET to do it. The other big restriction I have is that I don't have access ...
I'm writing a simple time tracking program to manage my own projects. I'm a big fan of keeping reporting code in the database, so I've been attempting to create a few sprocs that generate the invoices and timesheets etc. I have a table that contains Clock Actions, IE "Punch In", and "Punch Out". It also contains the user that did this a...
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? ...
I have a table: MyTable config as XML title as varchar(255) In MyTable.config I have XML in the following structure: <configuration pagetitle="myConfig"> <column> <row> <component id="1" type="MyPiece" title="My Title" text="junk" /> </row> </column> </configuration> I need a script to inject the value of MyTa...
I want to do something like: exec sproc1 and sproc2 at the same time when they are both finished exec sproc3 I can do this in dts. Is there a way to do it in transact sql? Or is there a way to do it with a batch script (eg vbs or powershell)? ...
I was reviewing some SQL queries and I saw a select statement that looked like this SELECT * FROM dbo.mytable WHERE (dbo.mytable.[Date] < { fn NOW() }) What is the purpose of using a WHERE statement like this? Wouldn't be easier to use a simple GETDATE()? ...
How would I return multiple values (say, a number and a string) from a user-defined function in SQL Server? ...
If I have data in the following format id subid text 1 1 Hello 1 2 World 1 3 ! 2 1 B 2 2 B 2 3 Q And would like it in this format: id fold 1 HelloWorld! 2 BBQ How could I accomplish it in T-SQL? ...
I'm pulling email address records from a table in SQL Server 2005, and want to build a single string to use as the @recipients list with sp_send_dbmail. The table has a field called EmailAddress and there are 10 records in the table. I'm doing this: DECLARE @email VARCHAR(MAX) SELECT @email = ISNULL(@email + '; ', '') + EmailAddres...
I'm in the process of converting a varchar(8000) field to an xml field in MSSQL 2005. Many of the XML docs have small issues (example: degree symbol not encoded). Is there a better way to validate the XML than a Try/Catch process? Maybe I could write a .NET Stored Procedure. What is the proper method to validate (or invalidate) an XML d...
I am attempting to insert a copy of a row from one table into another table with the same schema, with the addition of one column (a timestamp) to provide a "history" of the first table in MS Sql Server 2005. So, my query, without the additional column would be: "SELECT INTO [WebsiteHistory] FROM [Website]" I want to populate the tim...
Is there a tsql command on sqlserver 2008 which can be run in order to enable Database Diagramming instead of this dialog appearing: This database does not have one or more of the support objects required to use database diagramming. Do you wish to create them? ...
Hi, I want to assign default values to a column in my select sql query so that if the value of that column is null I get that default value in my recordset. Is there anyway to do this? Example: select col1 (some default value) from tblname; ...
I am struggling with a creating a query. It is related to a large and complicated database but for the sake of this post I have boiled the problem down to something simpler. I have three tables X, Y, Z defined as CREATE TABLE [dbo].[X]( [ID] [bigint] NOT NULL ) CREATE TABLE [dbo].[Y]( [ID] [nchar](10) NOT NULL ) CREATE TABLE [dbo...
In a SQL Server database, I record people's date of birth. Is there an straight-forward method of working out the person's age on a given date using SQL only? Using DATEDIFF(YEAR, DateOfBirth, GETDATE()) does not work as this only looks at the year part of the date. For example DATEDIFF(YEAR, '31 December 2007', '01 January 2008') retu...
You get a compilation error if you define the string parameter to have a size greater than 8000 e.g. The size (9000) given to the type 'varchar' exceeds the maximum allowed for any data type (8000). Any ideas? ...