We currently have our custom application that is being distributed with our database (SQL 2005/2008). It is an easy task, before we release a new version we just pack our database into SQL initialization scripts (these create tables and populate data). We use SQL Management studio to generate these scripts. As a next step we would like ...
Trying to select records that are all for the same customer, but where the address is different. So I can later let the user choose Bob Yonkers, then choose to update all of Bob's records to a specific address. So I want to show all the available records. Data Example: CUSTOMER_NAME, CUSTOMER_ADDRESS Bob Yonkers , 42 Satellite Cir ...
Hello, I am writing an events calendar, and the data for the calendar I will need to pull from another database and make some changes to before committing to mine. What are some basic approaches for this that you would suggest? This data is not super sensitive or in need of enterprise solution. I am thinking of writing a service whic...
How can I rewrite this t-sql to avoid using IN - and use joins in stead? --Members with membership in 2008 but not in 2009, using IN and NOT IN SELECT * FROM @Members WHERE MemberId NOT IN (SELECT MemberId FROM @Memberships WHERE [Year] = 2009) AND MemberId IN (SELECT MemberId FROM @Memberships WHERE [Year] = 2008 ) I want to fi...
is it possible to get a numeric parameter to my udf and do stuff according to its type, like: if type of @p1 is decimal(10,3) ... else if type of @p1 is decimal(15,3) ... else if type of @p1 is integer ... ...
There are two tables Person and Address. I have a mapping table PersonAddressMap which contains an additional column IsCurrent other than the two foreign keys (no explicit primary key column). I have created three mapping classes one each for Person, Address and PersonAddressMap. I need to access all the Addresses of a Person and also ac...
I have a merge replication scenario, with SQL2005 SP3 as the publisher and distributor and windows mobile SQLCE 3.5 SP1 as the subscribers. The replication filter is such that rows from one of the tables (tblJobs) only go to exactly one device. After the device has replicated it executes sql to set the IsSynced boolean column on all th...
Hi, Where stockView is an indexed view with a full-text index, I receive the error message below. The database is running on a 2008 Express engine in 2005 compatibility mode. Code: with stockCte (title, grade, price, weighted) as ( select sv.[title] , sv.[grade] , sv.[price] , (case when sv.[issue] = ...
I already searched for this and I did not find the answer I was looking for. I am writing a windows forms program with C# .NET and Visual Studio 2008. I am using Reporting Services and rendering the reports using the .net provided report viewer. The data source for the reports is SQL Server. I am rendering the reports locally. I am ...
What is the best way to return the result of the execution of a SQL Server stored procedure back to the .NET code? If I want to return the success or failure of a bunch of deletes in a transaction, should I 'Select' something, or maybe something like RETURN(1) or RETURN(0), or do I communicate number of rows affected? Then will this be...
I'm currently inserting a record into a SQL Server Table and then selecting the auto-increment ID as follows: (@p0 int,@p1 nvarchar(8))INSERT INTO [dbo].[Tag]([Some_Int], [Tag]) VALUES (@p0, @p1) SELECT CONVERT(Int,SCOPE_IDENTITY()) AS [value] (This was generated using Linq-to-SQL). For some reason when I run this code inside a trans...
I am trying to automate order processing for a client their POS vendor doesn't have any sort of API with their software so I was hoping to be able to insert orders directly in the database. so what i want to do is take a snapshot of the database, enter an order manually and then take another snapshot and compare the 2 snapshots to see w...
I am using a table with an XML data field to store the audit trails of all other tables in the database. That means the same XML field has various XML information. For example my table has two records with XML data like this: 1st record: <client> <name>xyz</name> <ssn>432-54-4231</ssn> </client> 2nd record: <emp> <name>abc</...
is it possible for a udf to return the same data type as one of its parameters? i would like my udf to accept a decimal of any precision and scale and return the same type. ...
We have a few customers with large data sets and during our upgrade procedure we need to modify the schema of various tables (adding some columns, renaming others, occasionally changing data types, but that's rare). Previously we've been going via a temporary table with the new schema, and then dropping the original and renaming the tem...
I am doing a MS SQL Server Full Text Search query. I need to escape special characters so I can search on a specific term that contains special characters. Is there a built-in function to escape a full text search string ? If not, how would you do it ? ...
I am creating a stored procedure in Sql Server 2008 database. I want to return the number of rows affected. Which is a better option SET NOCOUNT OFF or RETURN @@ROWCOUNT? ALTER PROCEDURE [dbo].[MembersActivateAccount] @MemberId uniqueidentifier AS BEGIN -- Should I use this? SET NOCOUNT OFF; UPDATE [dbo].Members SET acc...
I have two tables: CREATE TABLE [dbo].[Context] ( [Identity] int IDENTITY (1, 1) NOT NULL, [Naam] nvarchar (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, [Code] nvarchar (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, [Omschrijving] ntext COLLATE SQL_Latin1_General_CP1_CI_AS NULL) ; ALTER TABLE [dbo].[Context] A...
I need to copy data into an MSSQLServer 2005 database table which has an identity column. I've seen how to disable the identity column by executing SET IDENTITY_INSERT <table> ON before the insert queries. How can I do this when I'm using PreparedStatements to do batch inserts and I can't change the statement during the operation? ...
Does T-SQL accomodate for array values as parameters for stored procedures? If so how can this be achieved. ...