I have a job setup in SQL Server 2005 which has an Operating System (CmdExec) step. The step calls a program which can take a long time to run. I see that if the program takes longer than 1 minute 40 seconds to respond the step fails with an error message "The operation has timed out". The program actually continues to run and generate...
When I try to import Text File into SQL Server 2000 using DTS, it gives me the following error Not enough storage available to complete this operation Any suggestions? ...
I'm trying to figure out how I can check if a database role exists in SQL Server. I want to do something like this: if not exists (select 1 from sometable where rolename='role') begin CREATE ROLE role AUTHORIZATION MyUser; end What table/proc should I use here? ...
Dear Boffins I got a large conversion job- 299Gb of JPEG images, already in the database, into thumbnail equivalents for reporting and bandwidth purposes. I've written a thread safe SQLCLR function to do the business of re-sampling the images, lovely job. Problem is, when I execute it in an UPDATE statement (from the PhotoData field ...
I'm looking to filter the resultset of a stored procedure. What I'd like is something like the following (non-working) syntax: IF EXISTS ( SELECT 1 FROM (EXEC sp_linkedservers) WHERE srv_name = 'myServer' ) PRINT N'dropping linked servers' GO edit - this is just one example, I'd like a general solution if possible ...
For INSERT, UPDATE and DELETE SQL statements executed directly against the database, most database providers return the count of rows affected. For stored procedures, the number of records affected is always -1. How do we get the number of records affected by a stored procedure? ...
Can an auto-incrementing primary key be constrained by artificial limits? For example if I only want integer primary keys to be from a specific range of integers, say between 100 and 999 inclusive, and auto-increment, is that possible? And if so, on which database server software. I'm mainly interested in MS SQL Server 2000 or greater bu...
Whenever the value is null for this query SELECT ISNULL(someDateTime,'') FROM someTable the result is someDateTime ------------ 1900-01-01 00:00:00.000 I want it to be "No", so if I run this: SELECT ISNULL(someDateTime,'No') FROM someTable then there's this error: Conversion failed when converting datetime from characte...
I am creating a calendar table for my warehouse. I will use this as a foreign key for all the date fields. The code shown below creates the table and populates it. I was able to figure out how to find Memorial Day (last Monday of May) and Labor Day (first Monday of September). SET NOCOUNT ON DROP Table dbo.Calendar GO Create Table d...
For my data warehouse, I am creating a calendar table as follows: SET NOCOUNT ON DROP Table dbo.Calendar GO Create Table dbo.Calendar ( CalendarId Integer NOT NULL, DateValue Date NOT NULL, DayNumberOfWeek Integer NOT NULL, NameOfDay VarChar (10) NOT NULL, NameOfMonth VarChar (10) NOT NULL, WeekOfY...
I'm using a single textbox to search a report and filter out records. One of my fields is an int32 and the rest are varchar's. So when the filter tries to compare the string from the textbox with the int32 field, I get an error. Here's the SQLDataSouce and the search box: <asp:TextBox ID="SearchBox" AutoPostBack="true" OnTextChanged="Se...
Is there any tool that will inspect either asp.net or sql server and report all the queries that are run against the database? The reason I ask is I am using Linq for a project and want to double check what its actually doing for each page. Ideally I'd like to view a page in a browser and have a report of all the queries that were ru...
I had a working FREETEXTTABLE query that searched for a @searchString. I now need to UNION that with another simple query that tries to parse the @searchString into an INT, and if it succeeds, filtering the table by looking for the row with PK equal to the parse @searchString. Previously, I could easily JOIN the FREETEXTTABLE result to ...
I am creating a table that has a column called MonthName. When I defined this column the word "MonthName" showed up in blue like it was a reserved word. So I tried to look it up. I cannot find it in Books Online or on Microsoft's site or even on our own SO. I tried sp_help "monthname" and the reply was "The Object 'monthname' does not ...
In general, I believe "FULL OUTER JOIN Considered Harmful", to turn the phrase. Background: http://weblogs.sqlteam.com/jeffs/archive/2007/04/19/Full-Outer-Joins.aspx But I do have a specific situation where it would be really handy: Given: CREATE VIEW Calcs(c1, c2, c3, fordate, ...other columns) AS /* Complicated set of equations...
Is there a way to query SQL Server XML type so that for an element with xsi:nil="true", return null instead of default datetime value, which is 1900-01-01 00:00:00.000? here is a code snippet declare @data xml set @data = '<DOD xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:nil="true" />' select Value1 = @data.value('/...
I need to find a DTS package that contains a specific table name. There are 200+ DTS packages each with multiple objects in them. My_Prod is the table name. Is there a T-SQL command to find all the DTS packages that reference this table name? ...
I have a stored procedure for SQL Server 2000 that can only have a single instance being executed at any given moment. Is there any way to check and ensure that the procedure is not currently in execution? Ideally, I'd like the code to be self contained and efficient (fast). I also don't want to do something like creating a global temp...
Precision loss is one thing, but precision gain??? I have a text file w/ the following coordinates: 41.88694340165634 -87.60841369628906 When I paste this into SQL Server Mgmt Studio table view, it results in this: 41.886943401656339 -87.608413696289062 Am I dreaming? How is this possible? I'm pasting from notepad, and it's r...
How do you get the identity column value after an insert in SQL Server Compact 3.5? ...