sql-server

Introducing FOREIGN KEY constraint 'c_name' on table 't_name' may cause cycles or multiple cascade paths.

I have a database table called Lesson: columns: [LessonID, LessonNumber, Description] ...plus some other columns I have another table called Lesson_ScoreBasedSelection: columns: [LessonID,NextLessonID_1,NextLessonID_2,NextLessonID_3] When a lesson is completed, its LessonID is looked up in the Lesson_ScoreBasedSelection table to get th...

Delete from a table based on date

Hi, Can anyone help me with the script which will delete the data older than the particular date. Thanks ...

Best practices and/or advice for diamond relationing tables (Linq to SQL)

I have to import the content of 4 tables from an old database into SQL 2005 for easier reporting. Products contains the id and product name, ProductProperties contains a variable number of properties for each product, Ingredients contains a variable number of ingredients for each product, and IngredientProperties contains the same prope...

SQL Server 2005 - Is there a way to lock a SELECT in a Transaction?

Hi! Is there a way to lock a SELECT in a Transaction? If a SELECT occurs, no more SELECTs are executed while the first one is not finished. Thanks! ...

Using Sqlite InMemory DB for unittesting MSSQL-DB

Hallo, i am trying to implent this solution NHibernate-20-SQLite-and-In-Memory-Databases the only problem is that we have hbm's like this <class name="aTable" table="[dbo].[aTable]" mutable="true" lazy="false"> with [dbo] in the tablename because we are working with mssql and this does not work with Sqlite. i found also this postin...

Random Row from SQL Server DB using DLINQ

I need to retrieve random rows from SQL Server database. I am looking for a way to achieve this using LINQ query. Is it possible? SQL Query: SELECT [Id] FROM [MyTable] ORDER BY NEWID() What is the equivalent LINQ query for the above SQL? Thanks in advance. ...

Storing UTF-16/Unicode data in SQL Server

According to this, SQL Server 2K5 uses UCS-2 internally. It can store UTF-16 data in UCS-2 (with appropriate data types, nchar etc), however if there is a supplementary character this is stored as 2 UCS-2 characters. This brings the obvious issues with the string functions, namely that what is one character is treated as 2 by SQL Serve...

How to make a column case sensitive in sql 2005 or 2008

Is it possible to change the default collation based on a column? i want to make 1 column case sensitive but all the others not ...

SqlDateTime.MinValue != C# DateTime.MinValue, why?

I wonder, why SqlDateTime.MinValue is not the same as C# DateTime.MinValue? ...

Pass a variable to fully qualified name as database in a SQL query

Hi I want to query across multiple databases in SQL Server, so I thought I could create a variable called @DBNAME and pass it to a query as follows (but it doesn't work). This will be inside a loop where DBNAME is stepped through as a count on dbid.. my count works and it prints out @DBNAME as all of the production databases.. but I ca...

Choosing the appropriate precision for decimal(x,y)

I am currently redesigning a legacy database to run on SQL Server 2005, and I want to replace most of the old float-columns with decimals. Decimal(15,4) would be sufficent for my needs but the SQL Server doc states that this would use the same storage space (9 bytes) as a Decimal(19,4). Having a larger number in the same storage space s...

Dimension Security in SSAS 2005 Cubes

Hi, I am trying to implement security in SSAS 2005 cube. I have had my share of success so far, am able to create a role and apply the ALLOWED and DENIED sets to members I wanted to apply security on. Now starts the problem - If I browse within the Analysis Services and select the role the security works as intended. But when I login a...

LinQ To SQL with UPDLOCK

Is there a way to use UPDLOCK with LinQ To SQL? I have a scenario that needs it. Thanks! ...

Select distinct row with max date from SQL Server table?

I need to get a set of distinct records for a table along with the max date across all the duplciates. ex: Select distinct a,b,c, Max(OrderDate) as maxDate From ABC Group By a,b,c The issue is I get a record back for each different date. Ex: aaa, bbb, ccc, Jan 1 2009 aaa, bbb, ccc, Jan 28 2009 How can I limit this so I end up w...

Under what circumstances would SQL replication partially (and silently) fail from a MSDTC Transaction from a c# application

We have a particularly bizarre problem; let me set the scene. Solution found see below We have three SQL Server 2005 databases, for the sake of argument called: Alpha, Beta and Gamma. There is a replication relationship defined between these databases as follows: All three databases have a table named "AnExample" with the same schema....

How to reduce size of ms sql table that grew from a datatype change.

I have a table on an MS SQL 2005 server that was about 4gb in size. (about 17 million records) I changed one of the fields from datatype char(30) to char(60). (there are in total 25 fields most of which are char(10) so the amount of char space adds up to about 300) This caused the table to double in size (over 9gb) I then changed t...

How to Suppress SQL Authentication

Hello, I am really stumped on this, I am not sure if someone can at least point me in the right direction? I have an MS Access Reporting Application. It has a few local tables in it but predominantly relies on tables from a SQL Server 2005 database that it links to. It links to the SQL DB via a System DSN. I have built a function th...

How can I compare time in SQL Server?

Hi! I'm trying to compare time in a datetime field in a SQL query, but I don't know it it's right. I don't want to compare the date part, just the time part. I'm doing this: SELECT timeEvent FROM tbEvents WHERE convert(datetime, startHour, 8) >= convert(datetime, @startHour, 8) Is it correct? I'm asking this because I need to know...

How to determine if a field is set to not null?

I have an existing program deployed where some customer databases have a field set to not null, while others are nullable. I need to run a patch to correct the database so that the column is nullable but do not need to run it against all databases, just ones where it is incorrect. Is there a simple method that can be used in SQL Server...

How to Determine Values for Missing Months based on Data of Previous Months in T-SQL

I have a set of transactions occurring at specific points in time: CREATE TABLE Transactions ( TransactionDate Date NOT NULL, TransactionValue Integer NOT NULL ) The data might be: INSERT INTO Transactions (TransactionDate, TransactionValue) VALUES ('1/1/2009', 1) INSERT INTO Transactions (TransactionDate, TransactionValue) V...