sql-server-2008

How to reference one CTE twice?

I have a very fat common table expression which includes row numbers so that I can return a paged result set. I also want to return the total number of records that match the query before I page the result set. with recs as (select *, row_number() over (order by id) as rownum from ......) select * from recs where rownum between @a and @...

SQL Query Source Code

Hi all, I have 3 tables. One table has all the people, [Pat], each with a unique [PatId]. The second table has all the insurance company information, [Ins], each with a unique [InsId]. The third table has the patient insurance info, [PatIns]. In the [PatIns] table, some patients (also [PatId]) have secondary or 3rd insurance and it deno...

Design principles for designing database architecture of financial transaction system?

Hi, I want to design a database which will keep record for financial transaction.I want to design it as a product so that it can be used for any type of financial transaction.Are there some design principles specific to financial transaction database design that can help me out to make database more durable for long term with minimal arc...

Is there a way to filter sql execution plans by specific operator?

I have a great bunch of sql server statements that run on sql server 2008 I try to inspect its execution plans. Actually it generates a great deal of execution plans. I want to show only the execution plans that have a specific operator(s). e.g I want to get the execution plans that have Table Scan operator. Any way to do something like...

Cannot create a row of size 8074 which is greater than the allowable maximum row size of 8060.

I have already asked a question about this, but the problems keeps on hitting me ;-) I have two tables that are identical. I want to add a xml column. In the first table this is no problem, but in the second table I get the sqlException (title). However, apart from the data in it, they are the same. So, can I get the sqlException becaus...

another way instead trigger

hi how do I get the type of automatic actions (similar to the once done by triggers) using stored procedures instead, or any other way to this, because i need when i (insert or update or delete) a record in tableA ,or tableB i need to do the same operation in table C, i don't want to use the trigger because some Disadvantages such as :...

SQL Server 2008 Peer-To-Peer Replication not working all the time

Has anyone successfully set up Peer-To-Peer replication (not merge)? We have set it up exactly per BOL documentation. All the inserts to a node are replicated to another node with no problems. However, some updates do not get replicated - we can profile that the MS update proc (the proc that gets created when P2P replication is set up...

changing the ID of an SSAS database

I want to rename my SALES cube to SALES_2009 and put a new cube called SALES. When I rename it, the ID remains as SALES so my new cube now is called SALES, but it's internal ID is SALES_2010 (and my old cube is called SALES_2009, but the internal ID is SALES). Can the ID of a SSAS database be changed? I have tried the properties windo...

Fluent NHibernate : How can i use Int64 as ID?

Hi guys, I'm quite new to the whole concept of ORM, NHibernate and FluentNH, and i'm trying to accomplish something that seems so simple... I'm trying to retrieve an object that has a field defined as Int64 in it's class. This field would be the ID, as defined in the Map file. When trying to retrieve a record from the DB, NHibernate r...

Ensure that text field in Sql Server database uses specific font

Short Background: I finally isolated a bug I'm having. The problem is web users of mine who are accessing my ASP.NET application using Safari are storing their content in a different font than all other users. This is a problem because the 3rd party application I am using to dynamically create PDF's only recognizes 2 fonts (arial and tim...

[TSQL] how to write a efficient to get the difference from two table

hi i am using SQL Server 2008, want to create a view to list the different between two tables. for example t1 id name ---- ------ 1 John 2 peter 3 mary 4 joe 5 sun t2 id name --- ---- 1 john 2 joe how to create a view to list all the name in t1 but not in t2. i tried to do that, and alwa...

Error:insert hierarchy values using storedprocedure

I'm getting this error when trying to run a query that inserts results into a table in sql. im passing the table name as parameter,how to give the hierarchy value to the insert statement. here is my code: declare @pathhere hierarchyid select @pathhere=Path from SectionDetails where Sectionid=@sectionid and SectionName=@sectionname and...

TSQL INTERSECT Keyword

I have known about the SQL Server 2005/2008 keywords EXCEPT and INTERSECT for some time but have never found a compelling reason to use INTERSECT over the good old UNION keyword. Can anyone explain when or why you would use INTERSECT instead of UNION? ...

Error importing SSIS package with Konesans File system Watcher task into SQL Server 2008

I am importing SSIS packages to SQL Server 2008 that were originally built for SQL Server 2005. I upgraded them in VS2008 and them imported them. They all import and work except for the one with the Konesans File system Watcher task. I installed in the setup exe for Konesans File system Watcher SQL Server 2008 on my dev pc and the produ...

How do I copy SQL Server 2008 database via C# code

Hi! I want to copy an existing SQL Server 2008 database schema, but not the data, to a new database on the same server (i.e MyDB => MyDB_Test) using C#. I found some sample code at http://www.codeproject.com/KB/cs/CopyDBSchemaUsingSMO.aspx that seems to do exactly what I want but it kept throwing the following error on the ddatabase....

SQL Server unique-identifier equivalent in C#

What datatype should I use in C# to work with the SQL Server uniqueidentifier. Do I need any conversions etc ? Thanks ...

Performance of bcp/BULK INSERT vs. Table-Valued Parameters

I'm about to have to rewrite some rather old code using SQL Server's BULK INSERT command because the schema has changed, and it occurred to me that maybe I should think about switching to a stored procedure with a TVP instead, but I'm wondering what effect it might have on performance. Some background information that might help explain...

Debugging in SSMS exits without stepping in to code

Hello, I've recently put Windows 7 on my development PC, and I have Visual Studio 2008 Team System Development Edition, and SQL Server Management Studio 2008, and for some reason I can't seem to step in to any SQL code for debugging in SSMS. Before I formatted in favour of Windows 7, I was able to debug on the same two SQL servers I'm ...

Passing database name as variable to SQL from C#. Is it possible?

I've 2 tables having exactly same columns. But they are stored in Database1 and Database2 (on same server). Can i pass database name as variable? For example: SELECT [SomeValue] FROM [Database2].[dbo].[Klienci] SELECT [SomeValue] FROM ...

Determine if a POINT is between two other POINTs on a LINESTRING (SQL Server 2008 Geography)

I have a SQL Server 2008 GEOGRAPHY data type in my database containing a LINESTRING. The LINESTRING describes a potentially curvy road. I have another table that contains a start point and end point, both GEOGRAPHY POINT's, on the road. I need to know if a third point falls between those two points on the road (LINESTRING). Currently, ...