tsql

Set Users to not be Challenged for Credentials?

How can I restrict my users to stop getting Log in / accessed into my Database by Enterprise Manager or by any other way in SQL Server 2000? ...

Generate all the continous intervals from a series

I have this problem I can't quite solve. I can get the missing numbers interval, but I can't piece them back together to from my continous series. So if I have a series defined as [1000,1001,1002,1003,1005,1006,1008] I want to extract the three continuous series [1000,1001,1002,1003] and [1005,1006] and [1008]. Using a simple CTE I got ...

SQL insert error

Below code saying error incorreect syntax near "Main" INSERT INTO tbl ( 'Week', Main, a, b, c, d, e ) Select 'Week', Main, a, b, c, d, e FROM tbl_link ...

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

Why do system stored procs fail with the owner included?

In SQL Server, the following works: sp_help MyProc sp_depends MyTable But if you include the owner, it fails with a syntax error: sp_help dbo.MyProc sp_depends dbo.MyTable It makes you put the param in quotes for it to work. Yet, I could do the following no problem: exec dbo.MyProc Why the inconsistency? Is there a reason? ...

Should I use nvarchar(max) in place of a nvarchar(64) column or as an additional column?

I am building a table for tracking the history of particular objects in my database. Currently I have following columns: HistoryId int IDENTITY(1,1) NOT NULL HistoryDate datetimeoffset(7) NOT NULL HistoryTypeId int NOT NULL HistoryDetails nvarchar(max) NULL For the most part each history item will be self-explanatory through the His...

Convert from SQL Server to Oracle SQL

I have 3 very large stored procedure I need convert from SQL Server to Oracle, is that a converter out there that anyone has tried that would work for this? I really don't want to have to do this manually if there is another option. ...

create pdf files in sql server

All of our correspondence is done via database mail in sql server. The data for document generation and the rules to trigger the generation are all on sql server. We now have to create a pdf file. I was planning on using pdfsharp/migradoc to do it, but then we'd have to create document and time its readiness with sql server data state...

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: inline conditional convert with XML?

I need to convert data in plain text columns into xml columns. However, not all of the content is valid XML, so I need to ignore any rows with invalid XML. Is there a way I can filter out the invalid data either using some sort of inline conditional convert or something in the WHERE clause? ...

foreign keys on table from different database

I have two databases in SQL Server and i have a common table for both the databases an important big table which holds the foreign keys to other tables. The problem is the Table is in DatabaseA, and I need to refer foreign keys to this table from DatabaseB. I know SQL doesn't support cross database referential integrity so what's the be...

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

Is SQL Server Timestamp deprecated and what to use in replacement

The T-SQL MSDN page states The timestamp syntax is deprecated. This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature. Does this mean to suggest that the SQL Server Timestamp data type is ...

Get the records of last month in SQL server

I want to get the records of last month based on my db table [member] field "date_created". What's the sql to do this? For clarification, last month - 1/8/2009 to 31/8/2009 If today is 3/1/2010, I'll need to get the records of 1/12/2009 to 31/12/2009. ...

unique count of the columns ?

i want to get a unique count of the of multiple columns containing the similar or different data...i am using sql server 2005...for one column i am able to take the unique count... but to take a count of multiple columns at a time, what's the query ? ...

FOR XML AUTO in stored procedure

will FOR XML AUTO queries in stored procedures and using ExecuteXmlReader to retreive data to set business objects cause any performance hit? ...

Date Column was created

Hi, Do you know if there is a way to get the creation date for a SQL Server 2005 table column? I tried the sp_columns [tablename] to get some table information but no luck. Thank you. ...

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

Can you tell SQL Server not to return specific rows?

We have a database where contacts are only soft-deleted, i.e. we set a bit named deleted to true. The API handles this, but sometimes you need to run queries directly against the database and it's easy to forget that bit, and export contacts that are actually deleted. It's not a big problem, because I'm used to the system, but if I'm on ...