sql-server

Problem reading out parameter from stored procedure using c#

Hi, I just come across a strange problem where i cannot retrieve the sql stored procedure out parameter value. I struck with this problem for nearly 2 hours. Code is very simple using (var con = new SqlConnection(connectionString)) { con.Open(); SqlCommand cmd = new SqlCommand("sp_mgsearach", con); ...

SQL - Distinct on two columns and summarized values on third

Hi! I have a table with 3 different IDs. I want to distinct on column 1 and 2 and summarize the values of column 3 (into one field - maybe comma separated string). The summarized field doesn't have to "look nice" (no problems with: '4,3,' (comma at the end)). I'm using MS SQL Server 2008. E.g: ID1 ID2 ID3 1 1 5 1 1 8 ...

Setting PathAnnotation on SSIS path from code

I have been trying lately to create SSIS packages from .NET code rather than using drag and drop in Visual Studio. As part of the package documentation, I would like to be able to annotate data flow paths. The code below - copied from MSDN - establishes a path between two SSIS data flow components. By adding path.Name = "My name"; ...

Sproc performance degrades over time

Hi, this is my first post so if you need clarificatrion on anything then just let me know. My server details are as follows: - Windows 2008 Datacentre edition SQL 2008 standard edition (10.0.1600) 12GB Ram Quad core single processor machine The problem I have a stored procedure that runs and when I have just started SQL up, it takes ...

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...

Which table stores the information about constraints in SQL Server 2008 ?

Hi, I am new to database. I want to know, in which table all constraints info. is stored in SQl Server like in Oracle it is stored in USER_CONSTRAINTS & USER_CONS_COLUMNS View. What is the sql server way to know it ? ...

SQL Server Retrieving Recurring Appointments By Date

I'm working on a system to store appointments and recurring appointments. My schema looks like this Appointment ----------- ID Start End Title RecurringType RecurringEnd RecurringTypes --------------- Id Name I've keeped the Recurring Types simple and only support Week Days, Weekly, 4 Weekly, 52 Weekly If RecurringType is null the...

Regex for RM-EC001 (in sql-server)

Hello, i have a text and want to find all occurences of: prefix RM-EC + 3 digit number. For example RM-EC001, RM-EC099 or RM-EC100. But RM-EC99 should not be found. Thank you. ...

'device not ready' while using File.Copy() in C#

I am trying to move databases from one SQL server to another.I am doing this through a Console written in C#. The methodology is a follows. I first detach the database, move the data and log files to the new location and then attach the files from there. However after I detach the file, I am not able to copy the data and log files. The e...

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. ...

MSSQL: Only last entry in GROUP BY (with id)

Hi everyone. Following / copying computhomas's question, but adding some twists... I have the following table in MSSQL2008 id | business_key | result | date 1 | 1 | 0 | 9 2 | 1 | 1 | 8 3 | 2 | 1 | 7 4 | 3 | n | 6 5 | 4 | 1 | 5 6 | 4 | 0 | 4 And now i want to group based on the business_key returning the complete entry with the newest...

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 Server numeric format

When I do the following: DECLARE @x float SET @x = 1234.5 SELECT CONVERT(varbinary, @x) What is the returned format? I need to get the original number back from .net somehow, and I only have the binary value available. Clarification: The statement above returns (no column name) 0x40934A0000000000 Somehow, I must get the value 123...

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...

Retrieve & update 20000 data records stops working

Hi everyone, I am using the following code do get all data records from a MS SQL database and I try to update every single record. The code is used in a WebService. The issue is, that the code runs fine if I have 1000 data records but now I have 20000 data records an the code first returned with an timeout. Then I set the cmd.CommandTim...

Table foreign key / association to itself?

Hi, I've got a table that stores inventory for a store that looks like this InventoryId int ParentId int ShortDesc int ... [other product data] ... A TShirt will exist in the table with a ParentId of -1. Any variations of size and colour will exist in the same table with the original parent InventoryID in the ParentID f...

SQL Server: How to execute UPDATE from within recursive function?

I have a recursive scalar function that needs to update a record in another table based on the value it is returning, however UPDATE statements are not allowed in the function. How can I update the table from within the function? ...

How can I make a foreign key only able to reference a subset of rows in the target table

I have a table with a primary key (lets call it "person"), and another table that references it (lets call it "grade", as in student grades). Table "grade" has field "grade.personid", which is a foreign key to "person.personid". Lets say "person" has field "person.type" as well (varchar with possible values of "student" or "teacher" for...

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...

How to use a recursive function to update a table?

This is a follow-up from this question. Functions are not allowed to write to the database, but what if I wanted to update a record every time a function was called, specifically a recursive function? Currently, I have a function that takes an ID and returns a float. I'd like to update a table using the given ID and the returned float....