sql-server-2005

with 2 field that allow null how to make a constrain that at only one must be filled?

to simplify this let take that table: table1 ------------- id unique primary int myVal1 int null (fk) myVal2 int null (fk) myData int not null what would be the best way to create a constrain on this table so only one value can be filled? these would work: insert into table1 (myval1,myData) values (1,234) insert into table1 (m...

How is fill factor physically allocated?

I've been trawling books online and google incantations trying to find out what fill factor physically is in a leaf-page (SQL Server 2000 and 2005). I understand that its the amount of room left free on a page when an index is created, but what I've not found is how that space is actually left: i.e., is it one big chunk towards the end ...

sql server helper stored procedure or utility for alter table alter column IDENTITY(1,1)

I wanted to modify a column in a sql server 2005 table to IDENTITY(1,1) Incidentally this table is empty and the column to be changed is a primary key. This column is also a foreign key for two other tables. After googling I found that you cannot use Alter table syntax to modify a column and make it an indentity column. Link #1 : How...

How to return a failure if a table is empty

Using SQL Server 2005, but still using DTS. I need to add a step to check if a table is empty, and somehow fail the step if it is. It's easy checking if the table is empty: Select count(*) from source_table But returning 0 is a success, too. If it is 0, I want a failure (so that I can fork to a different option, email us, skip some...

Selecting Time ranges from Date Time fields in Access

I have a table containing reports and the date/time they were created. I'd like to create a graph to tally the number of reports created during intervals of ten minutes between two time periods: 8:00AM-9:00AM and 1:00PM-2:00PM. Here's an example of a query I'd like to run: SELECT s.StudyStartDateTime AS "8:00 - 8:10", s.StudyStartDate...

Adding a logical key to a View in SQL Server Manager

The .NET Entity framework is giving me the following error: "The table/view 'Foo.dbo.vwFoo' does not have a primary key defined and no valid primary key could be inferred. This table/view has been excluded. To use the entity you will need to review your schema, add the correct keys and uncomment it." The view is a collection of a varie...

Can an ActiveX Script (DTS) return complete instead of success or failure?

An ActiveX Script in a DTS package can return DTSTaskExecResult_Success or DTSTaskExecResult_Failure. Is there a way to indicate that it is not a success without failing the entire package? We'd like it to keep going, but in a different direction. The options for a path are Success, Failure, and Completion, but it appears the only ret...

is it possible to remove the union between these 2 query and how to create this constrain?

you have this 2 tables: table1 ------------- id fromId fromOtherTableId person and Table2 ------------- OtherTableId Person first question this query work fine select t.id from table1 as t inner join Table2 as a on t.fromOtherTableId=a.OtherTableId where a.Person = 54 union select t1.id from table1 as t1 inner join Tabl...

What's your preferred method for generating datasets in SSRS?

I am trying to figure out what is the 'best' (read: "your preferred method") way to generate datasets for SQL Server Reporting Services Reports (either 2005/2008): In-report queries Stored procedures Views But more than just choosing one of the above, why would you use that particular method? Also, please include your perspective (De...

For Nvarchar(Max) I am only getting 4000 characters in TSQL?

Hi, This is for SS 2005. Why I am i only getting 4000 characters and not 8000? It truncates the string @SQL1 at 4000. ALTER PROCEDURE sp_AlloctionReport( @where NVARCHAR(1000), @alldate NVARCHAR(200), @alldateprevweek NVARCHAR(200)) AS DECLARE @SQL1 NVARCHAR(Max) SET @SQL1 = 'SELECT DISTINCT VenueInfo.VenueID, Ve...

VBScript how to set the connection string

I am not sure whether it has been disscussed before or not but I have the following code (I changed from example for an Access database.) I don't know what to put inside the 'Provider' and the 'Data Source'. I'm using MS SQL Server 2005 and how to find this information in my machine? Please, even I try to follow from here http://www.c...

Run application in response to database event

Hello, I have a an application that needs to run at the end of a series of database jobs in SQL Server 2005. The application will do processing on the data that was created by these jobs. What would be the best way to trigger the execution of this application? ...

What are some common SQL injection checks I can use?

I'm running through my web app and I'm trying to test various parts of the system to make sure they aren't succeptible to SQL injection. What are some common sql injection checks I can perform on textboxes/textareas, etc that would be good checks for vulnerability? I'm not worried about damaging my data as I'm running this on a test be...

How do I 'subtract' sql tables?

Its not really a subtraction I'm looking for. And I know its not a union or intersection... I have been given a long and complex stored procedure that returns a table of active and inactive documents. I have also been given a similar stored procedure that returns another table that contains only the active documents. How could I ge...

How do I specify SSIS Package Database Connection Through Package Configurations?

I have an SQL 2005 SSIS package that takes data from an Oracle DB Table, and transfers it to a SQL Server Table. I have set up an "Oracle Provider for OLE DB" for the Oracle connection and a "SQL Native Client" for SQL Server Connection. The Oracle and SQL connections will depend on the development and shipping stage, which are: Loca...

Internal SQL Server Error when referencing a named instance of SQL

I am trying to script some inserts from a source database to a target database, using linked servers. For the first time we are using a second named instance of SQL, and it is giving me an "Internal SQL Server Error" when I run the following code. If I remove the brackets around the named instance, I no longer get the Internal SQL Serv...

Does SQL Server 2005 use short-circuit evaluation in constraints?

If I have that constraint on a table, (field1 is null and field2 is not null and dbo.expensivefunction(field2) = field3) If my field2 is null, is the function will be called anyway? Is "and" equal the meaning of (andalso, &&) in .Net in a constraint on sql server 2005? ...

For Each equivalent in SQL

I have a table, tblDocs that has a few columns: DocName varchar(50) DocLocation int Active int DocID int All entries in the table have a DocName and DocLocation. Active and DocID are blank. What I need to do is for each row in tblDocs I need to check the value of DocLocation and based on that value I update tblDocs, setting the Acti...

SQL While making Stored Procedure so slow

Hello, I've develop a Stored Procedure that gets data from the table VisitorsInfluences and builds five galaxies with the "most influenced visitors" at the middle of each one, and the visitors that are most influenced by them around them on each galaxy. Is this clear so far? I don't know why, the Stored Procedure is taking around 6 or ...

SQL Server, where field is int?

how can I accomplish: select * from table where column_value is int I know I can probably inner join to the system tables and type tables but I'm wondering if there's a more elegant way. Note that column_value is a varchar that "could" have an int, but not necessarily. Maybe I can just cast it and trap the error? But again, that se...