sql-server

SQL Server 2008 Geometry not working?

Hi There. I'm having an issue with SQL Server 2008 and the geometry functions. I'm trying to create non-overlapping shapes using the STDifference function... but it does not seem to be working 100% of the time. Here is all the sample code to demonstrate the issue. Anybody have any idea to make these non-overlapping polygons? (except ...

How to return bool from stored proc

Hi, I'm trying to work out how to write a store procdure which returns a boolean value. I started off writing the following one which returns an int. USE [Database] GO /****** Object: StoredProcedure [dbo].[ReturnInt] Script Date: 09/30/2010 09:31:11 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER procedure [dbo].[Ret...

How can I speed up multi-row inserts to SQL server?

I am inserting about 400 rows into a table at a time (SQL Server 2008). I have tried UNION ALL, but that slowed things down considerably. I settled on the following query: INSERT INTO table VALUES (a, b,...),(c, d,...),...(x,y,...); When I make this query through ODBC it takes roughly 400ms (I use C++ and time the single call to sqlE...

SQL Server function to return minimum date (January 1, 1753)

I am looking for a SQL Server function to return the minimum value for datetime, namely January 1, 1753. I'd rather not hardcode that date value into my script. Does anything like that exist? (For comparison, in C#, I could just do DateTime.MinValue) Or would I have to write this myself? I am using Microsoft SQL Server 2008 Express. ...

Bulk insert fixed width fields

How do you specify field lengths with the Bulk Insert command? Example: If I had a table named c:\Temp\TableA.txt and it had: 123ABC 456DEF And I had a table such as: use tempdb CREATE TABLE TABLEA( Field1 char(3), Field2 char(3) ) BULK INSERT TableA FROM 'C:\Temp\TableA.txt' SELECT * FROM TableA Then how would I specify the lengt...

Combining multiple datasets in PowerPivot

Essentially what I want to do is to create an interactive dashboard using slicers in Excel 2010 PowerPivot. I have 3 different data sets from a sql database which share "dimensions." Incoming Orders Sales Order Backlog - essentially what products are currently in production and waiting to ship or invoice Invoiced Sales Each data...

How can I combine sql aggregate functions with distinct more cleanly?

I've been running into the following case repeatedly lately, either I need to apply MAX() or SUM() to one column on a table, but I need DISTINCT sets of values for other columns. For example consider the following table and associated columns representing details of a login to StackOverflow. SoUserLogins (OpenIdToken, Name, IpAdress, L...

Why do my results not stay consistent?

I have the following stored procedure that I working on. I have noticed that every 5th or 6th time I refresh my results there are new values in there. Which considering that the data is in a static environment and no one is making any changes to the data at this time I really can't understand. Can someone please enlighten me as to why I ...

how do you pivot sql data without aggregating a column

Hello, I have the following output in a query. SKILL LEVEL SCORERANGE ----------------------------------------------------------------------------- Stunts LOW 0.0 - 4.0 Stunts MED ...

MS SQL database Admin/IDE

I'm using MS SQL for a project, but have always used MySql in the past. MySql has a really nice interface which lets you build queries, offers help, backup etc. What free programs are there for MS Sql that can do this. I'm currently a student so I have free access to MS Sql but the only interface I have for it right now is Visual St...

Select statement with in ISNULL

I am writing a stored procedure and within this procedure I am using isNULL. If the value is null I want to use a select statement as the replacement value is this even possible? IF ISNULL(@v_FilePrefix, (SELECT @v_FilePrefix = TransactionTypePrefix FROM [ConfigTransactionType] WH...

Take db script in sql server 2008 (only updates)

Hi, I want to generate the database script of sql server 2008. I want to generate only updates from particular date. how to do this? ...

Using typed bound parameters with PHP PDO-ODBC, unixODBC and FreeTDS

Hi, I'm using the following setup to access a MS-SQL database from a PHP application RedHat Enterprise Linux 5 PHP 5.2.14 with PDO and PDO_ODBC unixODBC 2.2.11 FreeTDS 0.82.1.dev.20100810 Unparametrized queries work fine. The only issue is being forced to close cursor on single result statements (with PDOStatment::closeCursor) to av...

Relative Performance in SQLServer of Substring vs a Right-Left combo

This is a performance based question, not a "I don't understand" or "best practice" question. I have a varchar field in a SQLServer database that is guaranteed to be longer than 7 chars. I need to extract a char(4) field consisting of the 2nd, 3rd, 4th and 5th chars in the varchar. For example if the varchar had the value 1234567890 I ...

"A network-related or instance-specific error occurred.." on Production Server Deployment

This is probably something simple but i just cannot figure it out so i'm just going to put it out there. In my c# class library i have a connection string Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\As.mdf;Integrated Security=True;User Instance=True And this works fine in my local development environment. However this cl...

What is a Key Lookup in a Database and what's the difference with Business Rules ?

I was wondering what really is a Key lookup in a database ? And what are the differences between Key Lookup and Business Rules? Thank you guys ! ...

SQL select print out results of stored procedue

My businesses application supports only reporting with selected data from SQL server.In one business process I have very complicated stored procedure which using others stored procs and it was designed to print out results as log of job done. What I want to catch that print out and select it as varchar(max) so my app can handle that data...

.NET database calls slow when using COM Interop, fast via VB6

Hi, After searching couple of days (here and here), I believe I've found the problem and it seems identical to this one. However, I have captured two execution plans to compare it but, they seem to be identical except execution time. (The Database server is SQL SERVER 2000) The Query executed in VB6 : EventClass : SQL:BatchCompleted ...

How can I clone an SQL Server database on the same server in SQL Server 2008 Express?

Hi, I have an MS SQL Server 2008 Express system which contains a database that I would like to 'copy and rename' (for testing purposes) but I am unaware of a simple way to achieve this. I notice that in the R2 version of SQL Server there is a copy database wizard, but sadly I can't upgrade. Thanks in advance UPDATE The database in qu...

SQL Server unique constraint (but only sometimes)

Hi, Imagine I have a table with 3 columns: ID (unique ID) Description (varchar 2000) IsDeleted (boolean) I want to add a unique constraint so that all descriptions are unique. BUT only for active records i.e. IsDelete = false. If a user flags a record as deleted, they can re-add a record with the same description as a deleted record, b...