SQL Server: Why xp_cmdshell is disabled by default?
What are the security reasons that extended stored procedure xp_cmdshell is disabled by default? ...
sql-server-2008
How to use transactions (begin transaction, commit transaction)?
I have seen transaction usage in some cases but never really understood in which situations they should be used. How and when transactions should be used (begin transaction statement)? I have read that Microsoft do not suggest to use transaction statements (commit, rollback) inside a trigger and stored procedure. ...
Collation Conflict
i always got the message "Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AI" in the equal to operation." when i run the script that came from MSSQL 2005 server. btw i used MSSQL 2008. here is the script USE [database1] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO --exec rsp_S...
partial lookup performance
Hi All, I have implemented partial lookup in fact table due to selection lookup having the validation in between the values of the lookup table. query as: SELECT X1, X2, X3 FROM TABLE1 WHERE X1 = ? AND ? BETWEEN X2 AND X3 --- WHERE ? ARE VARIABLES IN THE modify the SQL statement in the advanced tab of lookup. I have calculated the...
How to create conditional destinations in SSIS Data Flow?
Goal: I have a Db source. Depending on a variable, i need to store it into a fixed width file OR a delimited file. How do I do this in a data flow? I tried creating a conditional split, with two conditions. One condition going to a fixed width destination, and one to a delimited condition. Problem is that conditional split executed BOTH...
Executing a Table-Valued Function from a Stored Procedure with multiple Table-Valued Parameters being passed through?
I've got a stored procedure that executes some repetitive code, so I decided to make the redundant code into a table-valued function. The problem that I'm encountering is: Msg 137, Level 16, State 1, Procedure Search, Line 98 Must declare the scalar variable "@myTVP". A simple example of the SQL code that I'm using is: CREATE TYPE [d...
Retrieve List of all SPs with some condition
Hi, I want to have list of all SPs from my database that fulfill some conditions, say all those SPs that have StudentId in them as i want to update those SPs where StudentId would be a key column. How can i get the list of all such SPs? Thanks for your inputs. ...
SQL Server 2008: Check constraints that guarantees that only one value in all rows is set to 1 and others are 0
There is a need to build constraint on the column that guarantees that only one value in all rows is 1 and all the others are 0. Solution with triggers exists but I would like to have something built in. Is such thing possible at all? ...
SQL: Find rows where Column contains all of the given words
Hi, I have some column EntityName, and I want to have users to be able to search names by entering words separated by space. The space is implicitly considered as an 'AND' operator, meaning that the returned rows must have all of the words specified, and not necessarily in the given order. For example, if we have rows like these: abb...
SQL Server database full: Could not allocate space for object
My C# .NET 3.5 application uses MS SQL Server 2008 Express. I am writing huge amount of data to the database. At some point I get an exception: {"Could not allocate space for object 'dbo.Attachment'.'PK_Attachme_3214EC0707020F21' in database 'Cases' because the 'PRIMARY' filegroup is full. Create disk space by deleting unneeded files, d...
How to force sql server to execute one command at a time without using GO?
I would like to make sure that a series of commands are executed in a serial way, like in update TABLE1... update TABLE2... update TABLE3... I would like that update TABLE2 starts only when update TABLE1 has completed. Of course I can do this with GO: update TABLE1... GO update TABLE2... GO update TABLE3... GO But in this case i w...
MS SQL Server 2008 Check Constraint, and LINQ-To-SQL
This is a tough question to word...so bear with me. I have two tables in my database, [Item] and [AssignedProperty]. [AssignedProperty] is the child in a parent-child relationship with [Item]. So it basically just has an ItemID field and a foreign key relationship utilizing it. [AssignedProperty] also has an identical relationship with ...
In an UPDATE trigger - get the Primary Key without the column's name
I need to update the DateModified Column without knowing the name of the Primary Key Column. Basically, I've got a plain-jane UPDATE trigger like this: CREATE TRIGGER updated_SCHEMA_TABLE ON [SCHEMA].[TABLE] AFTER UPDATE AS BEGIN SET NOCOUNT ON; UPDATE [SCHEMA].[TABLE] SET DateModified = getdate() ...
How to specify a SQL table type input parameter in Crystal Reports?
I need to pass a list of key values to a stored procedure in SQL-Server, and that stored procedure then returns back the values needed for a Crystal Report. Rather than sending a comma separated list and then parsing that, it was suggested that I use a Table Type. So, I've defined that table type as simply made of integers: CREATE TYP...
SSAS 2008 date/time measure partition or dimension last processed?
Is there a way to tell the last date / time a partition was processed? I've lots of SSAS databases, with lots of partitions and the automation is not, but will be independently recording when a partition was processed. Is there an independent way to query and confirm? I've looked @ system.discover_partition_stat and system.discover_...
Create a UDT that has SQL @tables as its members
I wonder whether it is possible to create a CLR user-defined type in SQL Server 2008 members of which would be table variables. You know, you can declare a table variable: declare @foo table (row_id int not null ... ); So I fancy a UDT with several members, each of which is a table defined that way (a different table, of course), so ...
Error restoring backup from SQL2K to SQL2008R2
I'm restoring a SQL 2000 user database on a sql 2008 r2 box, but sometimes I receive the following error. This is also at the SQL Log. Error: 928, Severity: 20, State: 1 During upgrade, database raised exception 156, severity 25, state 1, address 0000000001C05A9F. Use the exception number to determine the cause It has ...
How do I trim out middle initial from a person's full name in SQL?
I'm look for the simplest way to split up first and last name wile trimming out the middle initial. The current layout of the field is [Last Name], [First Name] [MI]. Also, middle initial is not always there. My current code is below, I'm just not sure how to trim out the middle initial from first name without writing a case statement...
Foreign key relationship within tables in a distributed configuration
The schemas are same on all servers. The data of respective server should not be on another but there are some relations having foreign keys to data which is not on current server currently its not possible to implement foreign key constrains across servers "then how is it handled in a distributed database architecture!? (there are f...
SQL Server FILESTREAM performance
SQL Server FILESTREAM has some known limitations. 1) Database mirroring does not support FILESTREAM. 2) For clustering, FILESTREAM filegroups must be put on a shared disk which defeats the purpose of creating the clusters. Given these limitations in FILESTREAM is it advisable to build a FILESTREAM solution. Im looking to save and retri...