sql-server

When using SQL server management studio generate scripts, not putting guard around triggers.

I'm seeing an interesting behavior difference between my instance and my boss's instance of SSMS. When he uses Tasks->Generate Scripts... for tables, it puts guards around the triggers for that table (If exists...) but when I do the same thing using the same options SSMS doesn't put the guards around the triggers so I get errors if I run...

Database Model Dependent Relationship

A picture does more justice so I'll start with that. So in my Relation_Type table I have several different Types (Owner, Reviewer, Approver, etc). In my Relation_Status table I have different status' for some of the types: Reviwer: (Pending Feedback, Feedback Received) Approver: (Pending Decision, Approved, Denied) My prob...

using cursors and displaying row numbers

Hi I have declared the following cursor and used a local variable @RowNo to print the number of each each row. declare TheCursor cursor for select productName from products declare @RowNo int declare @productName nvarchar(50) set @RowNo = 1 open TheCursor fetch next from TheCursor into @productName print @RowNo prin...

connecting to ms sql 2005 from java

Can somebody help me connect to mssql 2005 with java. Can somebody post me the link or sth to find jar and the url format. What about jtds, what is the difference with microsoft? Which one to use better? Thanks ...

What could create a syntax error if you take a SQL query and perform an UNION with itself ?

I have this strange error in SQL Server 2005 where I take a working query, add the UNION keyword below it and then copy the query again. In my opinion, this should always be working, but it is not. I get the message 'Incorrect syntax near the keyword 'union'. What could create this problem ? To be more specific, here is the complete q...

Paging in T-SQL

I am trying to implement paging using Row_Number() method in this query but with no luck. The following Query uses recursive way to get Sites for a set of categories. WITH hierarchy AS ( SELECT yt.id FROM [dbo].[TH_Categories] yt WHERE yt.ID = @topicID And CultureID = @cultureID UNION ALL SELECT yt.id FROM [dbo]...

When to keep an index - Analysing index usage stastics in SQL Server 2008

I'm doing an index report on my MS SQL 2008 database (Right click database -> Reports -> Index Usage Statistics) It tells me that one of my indexes uses: 88 user seeks 0 user scans 6,134,141 user updates Can someone explain to me: What the difference between user seeks and user scans are? How should I determine when to keep an in...

Sql Server Displaying Items in specific order

I have a list of items ItemName Manufacturer TopSalesUnit Item1 A 100 Item2 A 80 Item3 A 60 Item4 B 70 Item5 B 50 Item6 B 30 Item7 C 10 Item8 C 05 I would like the re...

Could not open a connection to SQL Server

I have problems connecting to my database server. The database server is not local, I am connected via its IP address. It works fine in my development machine. After publishing the website to my server, it can not connect to my database server. A network-related or instance-specific error occurred while establishing a connection t...

Copy SQL Server Express Database to Another Computer

I am upsizing the back end of a client's Access app tomorrow and need to be ready with a plan for a particular problem with this client. The boss needs to be able to take the data file away from the office where the SQL Server is on a regular basis (usually monthly) and run statistical analyses on the data. I've looked at Easiest way to...

Degree of C# support in SQL CLR user-defined function?

Hi, This may be naive but I cannot get any confirmation of this: When I write a SQL function via the SQLCLR and as a C# SQL Server Project, can the SQL function include any method/class/namespace in the .NET BCL? Are there any restrictions on what the function can do? I have full control of the SQL Server and its hosting OS, so I can am...

SQL server 2008 multiple "LIKE" problem

I'm using MS SQL 2008 and I'm facing a challenge for a few day's now. My SP parameter can contain one to three words in a string (nvarchar) and I have to return matching LIKE %phrase% records for each word in a string. Example. My parameter is: "stack overflow" Records that must be returnd: miSTACKon noOVERFLOWon STACKit poOWERFLOW...

Implement ROW_Number() to a Specific query

I have the following query that returns a set of rows based on some input parameters : WITH hierarchy AS ( SELECT yt.id FROM [dbo].[TH_Categories] yt WHERE yt.ID = @topicID And CultureID = @cultureID UNION ALL SELECT yt.id FROM [dbo].[TH_Categories] yt JOIN hierarchy h ON h.ID = yt.Pa...

SQL Server 2005: what LINQ generated query is better - with "Where()" or "Any()" ?

var query = from c in db.Customers select c; query = query.Where(c => c.Orders.Where(o => o.OrderItems.Where(oi => oi.SellerID == sellerID).Count() > 0).Count() > 0); that generates SQL: SELECT [t0].[CustomerID], [t0].[FirstName], [t0].[LastName], [t0].[Email], [t0].[Company], [t0].[BillingAddress1], [t0].[BillingAddress2], [t0].[Shi...

Indexing individual fields of SQL Server composite keys

I'm upsizing a Jet database to SQL Server Express 2008 R2 and before doing so, I'm re-evaluating the schema (it was designed in 1997-98, and the guy who designed it (i.e., me) was something of a moron!). My question is about N:N join tables with a two-column composite key. In Jet, joins on the first column of a two-column composite key...

SQL Server Query to return Trustee Name, and the first occurence of an Event each day within a Date Range

Current query gives me all event_type = 1, for every day after 8/1/2010 I only want the first occurrence per trustee of event_type 1 for each day after 8/1/2010. SELECT trustee, event_time, event_type FROM mytable WHERE event_type = 1 AND event_time > '8/1/2010' Order BY event_time; ...

One INSERT with UNIONS or multiple INSERTS?

I have a sql function that returns a table. The table is populated via 6 or so reasonably complex statements. Is it better to UNION together these statement so there is only 1 insert or are these better kept separate? Or does it make no difference whatsoever? ...

TSQL Parent > Child > Sub-Child duplication without cursor

I am creating a SQL 2008 R2 stored procedure to duplicate a row and all it's children. It's a 3-tiered setup with a Parent, Child and Sub-Child Given the ID of the parent I need to create a duplicate. I have solved it using a fast_forward cursor. I know I can also do it with a while loop through rows but I do not believe that will be...

Error converting data type varchar to bigint. Inline sql statements

Hello, This is the code i have used to get LocationID from database with MerchantID. I am getting an exception from Fill dataset part. Please help me out,. The error is Error converting data type varchar to bigint. public DataSet getLocationID(long MerchantID) { //long LOCID = null; try { SqlParameter[] parameters =...

SQL Server management studio cannot save to SharePoint

If the file already exists I can save changes to the file. But I cannot save new scipt files to a SharePoint 2007 drive. Any suggestions on fixing this? --------------------------- Microsoft SQL Server Management Studio --------------------------- The specified URL could not be found, or you do not have appropriate access permissions. -...