sql-server

SQL Server - Noob Relationships Question

I have two tables: CREATE TABLE InmarsatZenith.dbo.ClientJob (JobRef int PRIMARY KEY NOT NULL, IntRef uniqueidentifier, CopyDeadline datetime, PublicationDate datetime, Repeat bit, BusinessType nvarchar(25), Sector nvarchar(30), Lang nvarchar(15), Format nvarchar(25), CreativeRotation nvarchar(50), TipinType nvarchar(25)) and CREATE ...

SQL Query to return an item within range or nearest range

I have a table of ranges that looks like CREATE TABLE [dbo].[WeightRange]( [ID] [int] IDENTITY(1,1) NOT NULL, [Description] [nvarchar](50) NULL, [LowerBound] [decimal](18, 2) NULL, [UpperBound] [decimal](18, 2) NULL, [GroupID] [int] NULL ) Given a weight and group id I need to find the matching (or nearest) range i...

Select returns 0 rows in a cursor and right number of rows in manual running

Hello: I've this cursor declaration: DECLARE CursorArticulo CURSOR FOR SELECT HstAt.IdArticulo, SUM(HstAt.Cantidad) As SumaCantidad, HstAt.Precio FROM HstArticulosTickets hstAT INNER JOIN HstTickets HstT ON hstAT.IdTicket=hstT.IdTicket WHERE hstT.NumUsuarioEmisor=@UsuarioAct ...

Auto-restore query windows

Using SSMS under sql08Express. In past installations of SSMS, if windows fired a restart and forced open applications to close, the editor would automatically re-open the active query windows when the app restarted. Now I can't find that property setting. IOW, when Windows installs patches overnight and auto-restarts, all my adhoc quer...

Tracking 'Useful Links'

Hi there, I'm building a Useful Links database for a Classic ASP site. The table has the usual ID, Title, URL, Description, DateAdded and DateModified. I also want to record in the table each time a link has been clicked or viewed. I wondered if anyone had built such a system or knows of a way that I could accomplish this? Thank you. ...

Bulk Insert with optional text qualifier

I am importing csv to db using bulk insert. It is the comma delimited csv file. No text qualifiers for all fields. But some fields may have comma as part of the data. for eg, ADDRESS field value. Those values are surronded with double quotes. Those double quotes appear only if the field value has comma in it otherwise values are not su...

Get a list of dates between two dates using a function

FYI: My question is duplicate of this MySQL question, but intended for SQL Server Is there a function to write a query that will return a list of days between two dates? For example, lets say there is a function called ExplodeDates: SELECT ExplodeDates('2010-01-01', '2010-01-13'); This would return a single column table with the val...

SQL Server as Prerequisite in VS Setup Project error in Windows Vista

Hello. I'm having problems installing SQL Server as prerequisite on my Visual Studio Setup application on Windows Vista. I' able to install it if I install Microsoft Visual C++ 2008 SP1 Redistributable Package (x86) and then run the setup, but that's not what I need because I need that the Microsoft Visual C++ 2008 SP1 Redistributable P...

Sql server: update new record on insert

Using the "Northwind" sample: I would like to create an insert trigger on the OrderDetails table so that every time an OrderDetail is inserted, it gets the current value of the UnitCost as defined in the Products table. It should be something like this, but I have trouble to get it right. Can you help ? CREATE TRIGGER Trigger1 ON Ord...

SQL Server spams in event log on every call of extended proc

I've wrote an extended stored procedure in dll. Every time I call it, SQL Server wrote useless "Information" in Windows Event Log (Windows Logs\Application): Event Type: Information Event Source: MSSQLSERVER Event Category: (2) Event ID: 17055 Date: 04.09.2009 Time: 14:54:57 User: N/A Computer: 4STORYTESTDB Description: 8...

How do I Query an Adjacency relation (in SQL) to get a Leaf by providing a Path?

Greetings, fellow coders! I have this table that contains categories and subcategories (actually I don't, but let's stick to a classic example): Id ParentId Name 1 NULL A 2 1 B 3 2 C 4 3 D 5 NULL B 6 5 D Is there a way for me to get cat...

SQL Server Convert DD/MM/YY to YYYY-DD-MM

I'd like to do this in the INSERT SQL. Can anyone show me. Thanks --Mark ...

How to cascade delete over many to many table

I have a 3 tables that look like this: On the foreign keys I have set cascade deletes. Right now, when I delete a record in the Folder table, only the related record in the FolderItem is deleted. This is expected and correct. What I would to accomplish is when I delete a record in the Folder table, the corresponding records in the Fo...

My slow T-SQL query needs a complete rethink.

Good afternoon all. I'm going to post the stored procedure in it's entire glory. Feel free to rip it to shreds. The author won't mind. DECLARE @itemTypeID INT SELECT @itemTypeID=ItemTypeID FROM dbo.ItemTypes WHERE ItemTypeName = 'Advert' BEGIN SELECT a.Active, a.ParentClass, a.Classification, a.Variant, FV."Full Views", ...

Checking if a SQL Server user already exists

I need to check if a specific user already exists on the SQL Server, and if it doesn't, then I need to add it. I have found the following code to actually add the user to the database, but I want to wrap this in an IF statement (somehow) to check if the user exists first. CREATE LOGIN [myUsername] WITH PASSWORD=N'myPassword', DEFAULT_...

SQL Server update primary key that's also a foreign key in two tables

I need to update the primary key for a record but it's also the foreign key in two other tables. And I need the updated primary key to be reflected in the child tables as well. Here is my query and the error: begin tran update question set questionparent = 10000, questionid= 10005 where questionid = 11000; Error 9/4/2009 10:04:49 AM ...

Where can I find resources for setting up a Silverlight app that accesses sql server 2008 Express DB?

I am trying to set up a basic Silverlight application to run behind the firewall on my Windows XP machine with IIS 5.1. We have a MySQL server for production purposes, but I would like to set up SQL Server 2008 Express Edition on my Windows XP machine to host the database part of the application. I'm completely new to a lot of .NET d...

Linq2Sql: re-Attaching entity to datacontext but not original datacontext problem

Hi there, can anyone help? I have an issue with linq2sql and trying to Attach (update) an entity class through a datacontext, it complains its not the original context, this is true (see code below) ... I thought it was best practices to open the datacontext and close it when not needed? I basically have my app calling a service layer...

Database name convention: DATETIME column

What is your naming convention for DATETIME columns (in my case, using MS SQL Server) For a column that stores when the row was created CreatedDatetime makes sense, or LastModifiedDatetime. But for a simple table, let's say one called Event, would you create columns called: EventID, // Primary key EventDatetime, ...

How to Alter Column Data Type Without Affecting Existing Column Width

Hi Folks, I have an existing column of data type varchar that I need to change to nvarchar, however I do not want to alter the existing column width of (5). If I use the following statement ALTER TABLE MYTABLE ALTER COLUMN MYCOLUMN NVARCHAR (5) NOT NULL I end up with a column of nvarchar data type, but with a column width of (10)!...