sql-server

Transact SQL - Get Identity?

How can I get the last or next Identity of a table? ...

MDX query analyzer vs Cube Browser

I have database where i have all sales data. And I need to calculate average sales qty for previous 3 months for current year Let's consider that we are browsing the cube1: select AdjSalesQty1, SalesQty on rows & Calendar months on columns condition is get the average for current year and where dimension for previous 3 months is set...

Sql Server Dynamic Database Partitioning

Hi, Is there anything called dynamic partitioning in SQL server? If so how can i implement it? ...

Currently selected database in MSSQL

I am currently tasked with porting our website code onto a linux server (from Win2003). The websites run on a MS SQL database. The original dev created a wrapper to contain all interaction with the database including selecting the correct database (the application spans multiple databases) The problem code is as follows... if (strcmp($t...

Framework for loading flat files into SQL Server

Hello, I have to import flat files into SQL Server. So far, I've used SSIS packages and Delphi programs to do the job but I'm getting more and more files to load. Many are very wide (more than 90 fields) and they all have their own specifics: some are fixed-width, some are delimited with a wide set of characters used as field and row de...

Ignoring Apostrophes In Contains

Simple question, perhaps a difficult answer. We're using CONTAINS but when matching criteria containing apostrophes (because these are for French road names), then obviously they don't match. So for example, searching for "Lislet" when the database contains "L'islet" yields nothing. I've looked into using dictionaries and thesauruses ...

delete duplicate records in SQL Server

Consider a column named EmployeeName table Employee. The goal is to delete repeated records, based on the EmployeeName field. EmployeeName ------------ Anand Anand Anil Dipak Anil Dipak Dipak Anil Using one query, I want to delete the records which are repeated. How can this be done with TSQL in SQL Server? ...

insert query now causing other queries to no longer work

I ran this query on my SQL Server database: begin transaction; insert into [db].[a].[Table1] ( [IsDeleted], [itemId], [regionId], [deskId], [LastUpdated], [LastUpdatedby]) select [IsDeleted], [itemId], [regionId], 11, [LastUpdated], [LastUpdatedby] from [db].[a].[Table1] where deskId = 12; update [db].[a].[Table1] set deskId = 3 where...

SQL Server - Determining Missing Indexes - DMVs

I am using the following query to determine missing indexes: select db_name(d.database_id) as DatabaseName, object_name(d.object_id) TableName, d.index_handle as IndexHandle, d.equality_columns as EqualityColumns, d.inequality_columns as InequalityColumns, d.included_columns as IncludedColumns, d.statem...

VS2010 Schema Compare bug

I'm using VS2010 Schema Compare to compare two databases - let's call them 'PerfectDB' and 'DodgyDB'. I want DodgyDB to have its schema altered to match PerfectDB. I select PerfectDB as the source and DodgyDB as the target, and compare. When I hit the Write Updates button, I am getting the following error: SQL01268: .Net SqlClient...

Set NOCOUNT OFF at database level?

Hello all. Can I set NOCOUNT to be OFF by default for a particular database? I need it for NHibernate (if it's ON, I get exceptions when saving). I can see the setting to disable it for an entire server (where it's set ON), but we're sharing the server with other databases. Is there a way I can set it to OFF just for my database? Or i...

How to implement logging and error reporting in SQL stored procedures?

Background I'm currently working on a project that heavily utilizes SQL stored procedures. Some tables have as many as few hundred thousand procedure executions every day. Because the project serves critical business function for our client logging and fast error detection is crucial. Question How to implement logging and error report...

Why do SUBSTRING or LEFT make the query much slower?

I have a blacklist of people that should never be contacted. When I want to see if a person is in this list, I do the following: -- Query 1 SELECT * FROM bldb.dbo.blacklist l WHERE l.matchcode = dbo.fn_matchcode('12345','Sesame Street','1','Eddie','Bert') The query runs very fast, since there is an index on matchcode column, and ...

Troubleshooting Missing Rows with MS Sync Framework

I'm using the Microsoft Sync Framework version 2 to synchronize data between a server database running SQL Server 2008 and a SQLCE 3.5 database on an occasionally connected WPF app. This is working fine for the most part, but rows from a particular table don't seem to be getting pushed back up to the server database when inserted on the...

what is the quickest way to run a query to find where 2 fields are the same

i have a table with id, first, last and i want to run a query that says give me every record where the combination of first and last exists more than once (i am trying to find duplicate records) ...

Redgate SQL Tools

I am testing the Redgate belt and the set of tools seems pretty amazing... but before I buy anything... could you recommend any other tools similar to these? Thanks ...

Simplest way to convert XML file and/or web service to a flat file

I am starting a project in which I need to get two sets of data into flat files. Source A is a web service that is updated daily, so I only need to hit this once a day. Source B is an XML file on an FTP site that I will also retrieve daily. I have never converted XML to a flat file before. I'm a SQL Server guy, so my initial thought was...

Select rows and Update same rows for locking?

I need to write a procedure that will allow me to select x amount of rows and at the same time update those rows so the calling application will know those records are locked and in use. I have a column in the table named "locked". The next time the procedure is called it will only pull the next x amount of records that do not have the "...

save DataTable to database

Hi, I am generating a DataTable from a webservice and i would like to save the whole DataTable into one database table. DataTable ds = //get info from webservice The DataTable is getting generated but What to do next .I am getting stuck .Show me some syntax.I dont really need the select statement there either, i just want to insert al...

Get list of dependent Tables, SQL Server 2005

lets say I have table1 = 'foo' and 4 other tables fee1, fee2, fee3, fee4 now say the primary key of foo is a foreign key of fee1 and fee2. Given the name 'foo' how will I get to know that fee1 and fee2 has foriegn key dependencies on foo. Please help, a psuedo query would be helpful. However, I know how to figure out given the name f...