sql-server

How do I output a table to a txt file using tsql?

How do I output a table to a txt file using tsql? I don't want to use DTS or SSIS in this instance. ...

how to send email notificaiton to subscribers (asp.net C#2008 & SQL Server2005)

Hi, I'm new with asp.net and trying to do some code. I have a SQL server 2005 database which contains the members' details and other tables. The members' membership is valid for one year. If they didn't renew their membership the code should send them an Email (which is given in the members' table) before 1 month of the expiry date not...

SQL Server table to xml

Hello, this time i have question how to convert MSSQL table to XML My source SQL table: +-----------+-----------------+ |atributname|atributvalue | +-----------+-----------------+ |phone |222 | |param4 |bbbbcdsfceecc | |param3 |bbbbcdsfceecc | |param2 |bbbbcdsfccc | +-----------+-----------...

Sorting Specific Column in a GridView after a DataBound

Hi, I have given an access to stored procedure, which i'm not able to edit. This Stored Procedure returns a Table with 2 Column, what I did is set a GridView's DataSource using SQLDataSource in this stored procedure. but I want this GridView to Sort an specific column to descending whenever this GridView Loads. <asp:GridView ID="GridVie...

computed columns, how to increment a subset

What I want to do is have a table structure like this: TypeID int not null (foreign key) ItemID int (computed value +1 for each value with same TypeId) Data string e.g. TypeId ItemId Data 1 1 "some data" 1 2 "some data" 2 1 "some data" 3 1 "some data" 3 2 ...

SQL FOR XML Help

Prob an easy question, but I am new to forming XML in SQL 2005, but What would be the best FOR XML SQL statement to use to form the XML seen below from a table that looks like this? Column1 Column2 ------------------------ Baseball Football Cricket Polo Swim Beach Desired XML output: <Category Nam...

Whats the best free ER-modelling tool?

Hi, whats the best free entity relationship modelling tool? I have an sql server 2008 database that I want to be modelled out with fields and foreign key contraints and I dont particulary like the one which is built into SQL Management studio. ...

Using case statement in update query

Is it possible to use case statement within an update query? I need to do something like this: If person name starts with 'S' then append '1', else append '2'. I tried this in sql server and it didn't work UPDATE PERSON CASE WHEN NAME LIKE 'S%' THEN SET NAME = NAME + '1' ELSE SET NAME = NAME + '2' END ...

SQl Server How to maintain the data.

Hi all, I am working on a product (ASP.NET Web site) developed for educational institutions. There are around 20 educational inst. in my site. For each of them academic year start and end date varies. There are huge number of records in the database for attendance and results. Now I need to show all previous years data (like attendance...

Advantages of Userdefined functions over Stored Procedures

I have some doubt regarding user defined functions. I would like to know why / when to use functions. What are the advantages of functions over stored procedure? Researching via google I have seen articles suggesting: stored procedure are more advantageous than functions. function have limited error handling functions cannot use tem...

Indexed Views And Scalar UDFs

Can UDFs be used inside index views or does this break the SCHEMABINDING logic? For example if in the where clause of the view I had something like this WHERE UserCanSeePost(UserId,PostId) This UDF would then do a lookup on another table to check some restrictions. I'm guessing this cant be done in indexed views? ...

Will SQL Server use a compound index when only a single column is in the WHERE clause?

Say I've got a table: CREATE TABLE Users ( Id INT IDENTITY (1, 1), FirstName VARCHAR(40), LastName VARCHAR(40) ) Queries are usually on FirstName or LastName, but also on FirstName and LastName. If I create a non-clustered index on FirstName and another on LastName, then my first two queries are catered for. Apparently, S...

Please explain the SET Option Requirements for computed columns

Creating Indexes on Computed Columns contains a list of six SET options that must be ON and one that must be OFF when using computed columns in an index. Why must they be set this way? ...

ADO.NET: What API I can use to get message when SET NOCOUNT OFF

Hi guys: Many articles recommend adopting "SET NOCOUNT ON" to avoid receiving message like "1 Row(s) affected". I'm curious what ADO.NET API I can use to get message when SET NOCOUNT OFF. Thanks. ...

When should I use the SQL Server Unicode 'N' Constant?

I've been looking into the use of the Unicode 'N' constant within my code, for example: select object_id(N'VW_TABLE_UPDATE_DATA', N'V'); insert into SOME_TABLE (Field1, Field2) values (N'A', N'B'); After doing some reading around when to use it, and I'm still not entirely clear as to the circumstances under which it should and should...

SQL Server Indexed View Question

I have a requirement to create a report that is killing the processor and taking a long time to run. I think I could speed this up significantly by creating an index view that keeps all this data in one place making it a lot easy to query/report on. This view would not just be used for the report as I think it would benefit quite a few ...

Formatting Dates In SqlServer To Use As Comparison

This should be easy I have a date column on table A SELECT * FROM TABLEA WHERE DTE = '01/02/2010' The problem is that this is deployed onto US servers and I have no control over the date as its an arg. How can I tell SqlServer to treat this date as being in that format?? I gave tried this: SELECT * FROM TABLEA WHERE DTE = CONV...

Cannot create a stop list in SQl 2008

I'm new to the stop list functionality in SQL 2008 and seem to be missing something obvious. I'm trying to exectue the following statement: CREATE FULLTEXT STOPLIST myStoplist3 FROM SYSTEM STOPLIST; GO and I'm getting the following error: Msg 102, Level 15, State 1, Line 1 Incorrect syntax near 'STOPLIST'. Any ideas? ...

What's the equivalent of Postgres' RAISE NOTICE in SQLServer 2005?

I am looking to output a message from a function in MS SQL 2005 when executed from the sql server management studio, similar to Postgres' RAISE NOTICE. ...

RDMBS: Approach for efficiently accessing tree structure data held in a database

Some details about the scenario: 1. We have a MS SQL Server 2008 database which stores 'Resource' records in a table. 2. Resources can have multiple child resources. 3. A child resource can belong to multiple parent resources. 4. Resources are linked to each other via an intermediary table, i.e. Resource -> Link -> Resource. 5. Th...