Add account to SQL Server in .net
Hi, Is there any API SQL Server 2008 offers for .net application to create and grant access authorities? Thanks! ...
sql-server-2008
Old Subsonic SQL Server 2008
I have an old implementation of the Commerce Starter Kit using Subsonic 1.xxx. A while ago my client upgraded to sql server 2008 and BOOM!, subsonic can't access it. Question(s): Does the old subsonic source code exist somewhere? Is there anyone who had the same problem and started using Subsonic 2 instead? What's the easiest way to m...
Compound primary key in Table type variable
SQL Server 2008: DECLARE @MyTable TABLE( PersonID INT NOT NULL, Person2ID INT NOT NULL, Description NVARCHAR(100), CONSTRAINT PK PRIMARY KEY CLUSTERED (PersonID, Person2ID) ); Gives: Msg 156, Level 15, State 1, Line 5 Incorrect syntax near the keyword 'CONSTRAINT'. Is there any way to have compound Primary key in Table ...
How to match/compare values in two resultsets in SQL Server 2008?
Hi, I'm working on a employee booking application. I've got two different entities Projects and Users that are both assigned a variable number of Skills. I've got a Skills table with the various skills (columns: id, name) I register the user skills in a table called UserSkills (with two foreign key columns: fk_user and fk_skill) I reg...
sql trigger disappears
I've got a trigger in a sql server table. This trigger has disappeared. Is there something systematic that might be causing it to be deleted? There is no replication on this db. ...
Large scale ETL string lookups performance issues
I have an ETL process performance problem. I have a table with 4+ billion rows in it. Structure is: id bigint identity(1,1) raw_url varchar(2000) not null md5hash char(32) not null job_control_number int not null Clustered unique index on the id and non clustered unique index on md5hash SQL Server 2008 Enterprise Page level compr...
Help troubleshooting SqlException: Timeout expired on connection, in a non-load situation
I have a server hosting a website of mine that has almost zero-traffic. A few people (< 20) enter the site every day, and a few RSS readers are subscribed to some feeds we put out. Almost every night, an RSS reader will hit us in the middle of the night and get an exception that the website can't connect to the SQL Server because of a T...
Office SQL Query Problem
I've got a database View in SQL Server 2008 and when I run it from Query Analyser, it runs within a couple of seconds. When I run it in Excel 2007, it loads the data just fine. But when I run it in Word 2007 on a mail merge, it just seems to take a very long time to run (literally minutes to run). If anyone has an idea how to solve this...
Are SQL Server 2008 Table Valued Parameters vulnerable to SQL injection attacks?
I have been investigating Table-Valued Parameters in SQL Server 2008, and I've discovered that when passing such a parameter to a stored procedure, a query such as the following is sent to the database server: declare @p1 dbo.MyTypeName insert into @p1 values(N'row1col1',N'row1col2') insert into @p1 values(N'row2col1',N'row2col2') inser...
SQL: How to keep track rows already matched in a correlated subquery?
Hi, Here's another challenge for you SQL-gurus out there... I wrote another question here on matching two result sets(http://stackoverflow.com/questions/1421842/how-to-match-compare-values-in-two-resultsets-in-sql-server-2008). This question is a sequel to that question and answer, but since its a different topic I'm creating it as a n...
SQL Server 2008: Unexpected results using SELECT TOP
Can anyone explain the SQL Server 2008 behaviour I'm seeing? Given a simple table definition: Column Type Nullability id (PK) int not null author_id int null title varchar(255) not null body varchar(MAX) not null type varchar(255) null comments_count...
Optimize for speed a simple stored procedure
Hello: In SQL 2008 I've this easy-but-bad-write sp that works: ALTER PROCEDURE [dbo].[paActualizaCapacidadesDeZonas] AS BEGIN SET NOCOUNT ON; DECLARE @IdArticulo AS INT DECLARE @ZonaAct AS INT DECLARE @Suma AS INT UPDATE CapacidadesZonas SET Ocupado=0 DECLARE csrSumas CURSOR FOR SELECT AT.IdArticulo,...
Taking incremental backups of SQL Server 2008 Database
We are building an ASP.NET application (with C#.net as language) and will be hosting on Windows Server 2003 Operating System with SQL Server 2008 as database. We are planning to take the incremental database backups to a disk and use them to restore whenever there is a necessity. So would you please guide me on this, if you have an ide...
Parent-Child Relationship: E-R Diagram
Would you please let me know a reference document or web references for a good E-R diagram displaying Parent Child relationships between the tables (Min. of 8 tables) with MS SQL Server 2008 database? I am planning to design a big database, so looking forward for your help. ...
Problem with SQL Merge Statement
Source Table Id, Name, Address 1 A #202 1 A #203 1 A #204 2 A #202 Target Table Id, Name, Address 1 A NULL After Merge Id, Name, Address 1 A #202 2 A #202 I am using this SQL create table #S (ID int, Name varchar(25) NULL, Address varchar(25) NULL) create table #T (ID int, Name varc...
When does the DataContext will open a connection to the DB?
I am using L2S to access my MSSQL 2008 Express server. I would like to know when the DataContext will open a connection to the DB? and will it close the connection right after it opened it? For example: var dc = new TestDB(); // connection opened and closed? dc.SomeTable.InsertOnSubmit(obj); // connection opened and closed? foreach...
SQL Server equivalent to MySQL enum data type?
I have been trying to find out if the data-type enum exists in SQL Server 2008 like the one you have in MySQL. ...
How can I round a column in a single SQL request without changing the overall sum ?
I've got a table defined like this : create table #tbFoo (bar float) And I'm looking for a way to round every value contained in column bar without changing the total sum (which is known to be an integer, or very close to an integer because of float number precision). Rounding every value to the nearest integer won't work (ex : 1,5;...
SQL Server 2008 data protection
I have a client-server application where a .NET client accesses all the data and stored procedures in a SQL Server 2008 database. Is there any way to protect all this data so that only the users I create and authorize can access this specific database? Especially the user 'sa' comes to mind. I don't like him to access all my data. ...
How to create SQL Server Express DB from SQL Server DB
I have a SQL Server 2008 DB. I want to extract SOME tables (and associated schema, constraints, indexes, etc) and create a SQL Server Express DB. It isn't a sync of the target, we stomp on it. We ONLY need to do this in the file system (not across the wire). We are not fond of the synchronization stuff and at this point don't know how t...