sql-server

T-SQL: Using OVER and PARTITION BY

Hello, I have the following data | Item | Value | Date | ------------------------------ | 1 | 10 | 01.01.2010 | 1 | 20 | 02.01.2010 | 1 | 30 | 03.01.2010 | 1 | 40 | 04.01.2010 | 1 | 50 | 05.01.2010 | 1 | 80 | 10.01.2010 | 2 | 30 | 04.01.2010 | 2 | 60 | 06...

Autoincrementing hierarchical IDs on SQL Server

Consider this table on SQL Server wordID aliasID value =========================== 0 0 'cat' 1 0 'dog' 2 0 'argh' 2 1 'ugh' WordID is a id of a word which possibly has aliases. AliasID determines a specific alias to a word. So above 'argh' and 'ugh' are aliases...

Diaplaying SSMS standard report in your web application

SSMS 2005 & 2008 comes with inbuilt standard reports. How can we display these standard reports inside a asp.net web site? ...

Sync database with filter using SyncOrchestrator with Sync Framework 2.0

Hi, I want to synchronize two SQL databases. But since one of the databases only requires a subset of the data I am looking for a filter option. Is there a possibility to add a Filter to the SyncOrchestrator or do I have to add the filter to the SyncProvider? According to this: http://social.microsoft.com/Forums/en-US/uklaunch2007ado...

using "in" with a case statement

Hi Is it possible to write something like Case when a in (value1,value2) then b else a end ...

Using Stored Procedure into Select (T-SQL)

Hi, I need to access result of a stored procedure withinf a select statement (ie : SELECT * FROM [dbo].[sp_sample]) in SQL_Server 2005. Regards, Stéphane ...

SQL statement to grab table names, views, and stored procs, order by schema

Is there a SQL statement that will list the names of all the tables, views, and stored procs from MS SQL Server database, ordered by schema name? I would like to generate an Excel spreadsheet from this list with the columns: schema, type (table, view, stored proc), and name. ...

SQL Server database backup: Network Service file access

When trying to run the following database backup command from my code I get an "Operating system error 5(Access is denied.)" error. This is because the log on account for the SQL Server Windows Service is 'Network Service' and that does not have access to right to this folder. BACKUP DATABASE [AE3DB] TO DISK = 'c:\AE3\backup\AE3DB.bak' ...

Convert Yes/No/Null from SQL to True/False in a DataTable

I have a Sql Database (which I have no control over the schema) that has a Column that will have the varchar value of "Yes", "No", or it will be null. For the purpose of what I am doing null will be handled as No. I am programming in c# net 3.5 using a data table and table adapter to pull the data down. I would like to directly bind the...

What is the difference between cubes and the Unified Dimensional Model (if any)?

I'm currently researching SQL Server 2008 as a business intelligence solution, and currently looking at Analysis Services (and I'm pretty new to business intelligence as a whole...) I'm a bit confused by some of the terms in SSAS, particularly the conceptual differences between cubes and MS's Unified Dimensional Model. I believe tha...

SqlException constraint violation

Hi there I', working on an asp.net app. Is there a way to, when catching a SqlException, to now which constraint was violated? tks ...

SQL cast datetime

In SQL Server 2005, why does: PRINT Cast('' AS datetime) display: Jan 1 1900 12:00AM I would have thought it should be null? ...

Disable all non-clustered indexes

I select a number of non-clustered indexes from my database with the following: SELECT sys.objects.name tableName, sys.indexes.name indexName FROM sys.indexes JOIN sys.objects ON sys.indexes.object_id = sys.objects.object_id WHERE sys.indexes.type_desc = 'NONCLUSTERED' AND sys.objects.type_desc = 'USER_TABL...

Inserting timestamp value in SQL Server

I am trying to copy data from my MYSQL table to SQL Server using PHP. I have a TimeStamp value that needs to be copied. While I am trying to copy the fields, it gave an error that timestamp value cannot be inserted. Is there any way to insert the timestamp value? Is it is not possible, then declaring the column as nvarchar will inser...

trying to figure out how to send multiple file attachments with sp_send_dbmail

MSSQL 2008 Maybe i'm just looking in the wrong place. Here's the scenario: We have files stored in a varbinary(max) column. I'd like to select these as individual files, along with the names of the files stored nearby, as individual file attachments. can this be done? it's easy enough to send the results of a query as an attachment, ...

Is there a way to find out the user that submitted a SQL query?

I am interested in writing a trigger that would ignore queries from a specific SQL user but take action on all others. Is there any way to do this? A note, though: the user will need to do some queries, so I will need to allow some queries/deletes/etc. ...

sql 2008 metadata modified date

Is there a way to identify the timestamp when an object(table/view/stored proc...) was modified ? there's a refdate in sysobjects but it's always the same as crdate atleast in my case and i know that alter view/alter table/alter proc commands have been run many times post creation ...

sql server script generater stored proc

anyone know of such stored procs to generate scripts for tables/indexex/views/sp's etc. ...

MS SQL share identity seed amongst tables

In MS SQL is it possible to share an identity seed across tables? For example I may have 2 tables: Table: PeopleA id name Table: PeopleB id name I'd like for PeopleA.id and PeopleB.id to always have unique values between themselves. I.e. I want them to share the same Identity seed. Note: I do not want to hear abou...

Character set issues with Oracle Gateways, SQL Server, and Application Express

I am migrating data from a Oracle on VMS that accesses data on SQL Server using heterogeneous services (over ODBC) to Oracle on AIX accessing the SQL Server via Oracle Gateways (dg4msql). The Oracle VMS database used the WE8ISO8859P1 character set. The AIX database uses WE8MSWIN1252. The SQL Server database uses "Latin1-General, case-i...