sql-server

Bulk Insert a File with a Text Field with Carriage Return (Enters)

Hi!, I´m having trouble with bulk insert a file that contains a field in data type text, and it holds enters and the delimiter for the fields are pipes "|" and the row terminator is "|\n" I get an error of truncation for the fields next to the text field. I think that the bulk insert thinks that the enters in the second field are the n...

GET ISNULL from COMPLEX FUNCTION

I have a SQL Table UDF That gets a Standard Deviation from a 20 day moving average of values... The table its computing from is: Tickers([date] datetime, [close] numeric(7,2)) The Function computes a Table GetStDev([date] datetime, stddev numeric(7,2). The last row of the stddev column is always NULL (due to STDEV calc?)... I need to r...

Adding Row Numbers To a SELECT Query Result in SQL Server Without use Row_Number() function

Hi, i need Add Row Numbers To a SELECT Query without using Row_Number() function. and without using user defined functions or stored procedures. Select (obtain the row number) as [Row], field1, field2, fieldn from aTable UPDATE i am using SAP B1 DIAPI, to make a query , this system does not allow the use of rownumber() function in th...

Why won't SQL Server register my assembly as SAFE?

On SQL Server 2008, I'm attempting to register an assembly that seems to only reference the supported libraries. Here is the T-SQL code that I'm using to register the assembly: create assembly MySpatial from 'c:\Spatial.dll' This results in the following error: Msg 6509, Level 16, State 31, Line 1 An error occurred while gather...

Has anyone ever seen this SQL Server Error? Failed Assertion = 'm_iteratorInSafeState'. I can't find anything online to help.

We have a client running our .NET application which connects to SQL Server 2005 on the backend. A few weeks ago the database crashed and the whole server had to be rebooted. This happened again today and I got the log file from their DBA which seriously offered no clue as to what's really going on. I searched on Google and Bing and onl...

Best way to connect gridview to data source

My DAL is created using Linq and all is fine. However, on one page, I'm to display a table (gridview) which pulls data from a SQL Server database table. However, I've heard many warnings about staying away from using any of the built in controls (ObjectDataSource and SQLDataSource). Is there any truth in this, regarding scalability/effic...

Using SQL Account Store in Geneva

Geneva has me pretty hot and bothered as a potential to solve complicated auth and SSO issues that have been presented to us by our customers. I understand that Geneva supports custom and SQL-based attribute stores. However, I need support for custom and SQL-based account stores. Unfortunately, I can't find any solid confirmation or de...

Eliminating record duplicates in the Entity Framework database

I work with Entity Framework - add records in the database using classes that map to the DB. But i need to make sure I dont add duplicate records in the database. I assumed that when SaveChanges() is called, it sorts out all the duplicates, but it doesnt. Is there a way to eliminate creation of the duplicate records? ...

Query xml data from SQL2000 (xml saved directly in a cell, multiple rows)

Hi I get problem retrieve XML data from SQL2000 server. The table structure is as following: ID Name XML 1 Name1 <Root><DATA1>1-Data1</DATA2><DATA1>1-Data2</DATA2></Root> 2 Name2 <Root><DATA1>2-Data1</DATA2><DATA1>2-Data2</DATA2></Root> 3 Name3 <Root><DATA1>3-Data1</DATA2><DATA1>3-Data2</DATA2></Root> How can I get th...

T-SQL - GROUP BY with LIKE - is this possible?

Is there a way to include a LIKE expression in a GROUP BY query? For example: SELECT Count(*) from tblWhatever GROUP BY column_x [LIKE %Fall-2009%] column_x: -------- BIOL-Fall_2009 HIST Fall_2009 BIOL Spring_2009 Result: ------ Fall_2009 2 Spring_2009 1 ...

Can Sql Server 2008 Stored Procedures (or Triggers) manually parallel or background some logic?

Hi folks, If i have a stored procedure or a trigger in Sql Server 2008, can it do some sql calculations 'in another non-blocking thread'? ie. something in the background also, can two sql code blocks be ran in parallel? or two stored procs be ran in parallel? for example. Imagine we are given the job calculating the scores for each St...

what is the query to get "related tags" like in stack overflow

i have 3 tables: links (id, linkName) tags (id, tagName) tagsBridge (tagID, linkID) i am trying to support showing related tags like in SOF. so if you click on tags "XYZ", right now i am showing all the links with tag "XYZ" but i also want to show the distinct list of all other tags that people have tagged those items that also h...

what does a B-tree index on more than 1 column look like?

So I was reading up on indexes and their implementation, and I stumbled upon this website that has a brief explanation of b-tree indexes: http://20bits.com/articles/interview-questions-database-indexes/ The b-tree index makes perfect sense for indexes that are only on a single column, but let's say I create an index with multiple colum...

Use openrowset function in sql server 2005 (x86) on Windows server 2008 (x64)

I am trying to access xls file from the disk from sql server stored proc, it is working perfectly in dev. enviornment (win xp (x86) / SQL server 2008) but it provides me error as Microsoft.Jet.OLEDB.4.0' provider is not registered on the local machine ...

Restore Partioned database into multiple filegroups

does anyone have any query to restore partioned db that having multiple file groups,In the restore option in the SSME i need to edit manually all the path of the filegroups restore as option it little bit tedious as it having more than 150 filegroups eg:USE master GO -- First determine the number and names of the files in the backup. RE...

Alter column drops index

I have a table CREATE TABLE table1( [classe] [char](30) NOT NULL, [code] [char](30) NOT NULL, [description] [varchar](255) NULL, [codelangue] [char](2) NULL ) ON [PRIMARY] with the index CREATE NONCLUSTERED INDEX [table1_id1] ON [dbo].[table1] ( [codelangue] ASC, [classe] ASC, [code] ASC ) INCLUDE ( [description]) WITH (PAD_...

Convert query/stored procedure result set to XML

I'm looking to take the result of a SQL Server stored procedure and transform it into XML in .NET/C# What I'm interested in is what standard libraries there are in .NET to help with the specifics of generating XML and if there any external libraries or tricks that can help automating the transformation from result set to XML. I've look...

Equivalent to VB AndAlso in SQL?

Is there an equivalent to VB's AndAlso/OrElse and C#'s &&/|| in SQL (SQL Server 2005). I am running a select query similar to the following: SELECT a,b,c,d FROM table1 WHERE (@a IS NULL OR a = @a) AND (@b IS NULL OR b = @b) AND (@c IS NULL OR c = @c) AND (@d IS NULL OR d = @d) For example, if the "@a" parameter passed in as NULL ther...

Problem with inserting a new row into multipleTables or editing a row using SqlDataAdapter

Hello, I've to insert a new Row(user) into the Database using SqlDataAdapter. I also have to edit any desired row (whose PID is given). Problem is that both of them are not working. I've Created an Array of selectQuries[] with quries of the form for INSERT: "SELECT X,Y,Z FROM TAB1 WHERE 0=1". for Editing: "SELECT X,Y,Z FROM TAB1 WHER...

Querying a view that's not located on the same server (SQL Server 2005)

Hi there ! I'm trying to query a database view that's not located on the same server as the stored procedure I'm running. I heard about using "linked servers", but I have no access to the server's configuration at all ... Thanks in advance ! ...