sql-server

Does the SQL WITH statement work on Views?

I'm using the WITH statement on a view, and it doesn't work. However, when I create a subquery instead, it works. I was wondering why that is. Error: Msg 0, Level 11, State 0, Line 0 A severe error occurred on the current command. The results, if any, should be discarded. ...

How to delete parent and child record

Hi I need some help. I have a table that have LibraryID and LibraryParentID. LibraryID int Unchecked Name varchar(50) Checked Description varchar(200) Checked LibraryLevel int Checked LibraryParentID int Checked LibraryTypeID int Unchecked I want to write a sql to delete the currrent select parent and well as all child record, how c...

how do i connect to SQL server in SQL Server Mgmt Studio with SSPI Integrated Security

i have an asp.net web app that has this in the web.config file in the project server=[server],15001;Integrated Security=SSPI;Database=DB_NAME how do i connect to this database from Sql server mgmt studio since there is not use or password in the connection string. i am connecting from a remote machine ...

Why does SQL 2008 Profiler indicate that Stored Procedure run from EF4 is in Master?

Using Entity Framework 4 with stored procedures and SQL Server 2008 SP1... When running SQL Server Profiler (TSQL_SPs template), the lines that show my stored procedure call and its statements say that this happened in DatabaseID = 1 (Master) but it is actually happening in my application database. The procedures execute properly and r...

SQL Server - Missing Indexes - What would use the index?

I am using SQL Server 2008 and we are using the DMV's to find missing indexes. However, before I create the new index I am trying to figure out what proc/query is wanting that index. I want the most information I can get so I can make informed decision on my indexes. Sometimes the indexes SQL Server wants does not make sense to me. ...

Custom Code access in a SQL Server report

Hello. This is my first post to this forum. I have a SQL Server report that I have developed some custom code to return some data that is calculated through another dll (The calculation is extensive so I didn't want to re-create it). I 've referenced the code in the report and I'm able to call the method that returns the data on my loc...

Efficiently select top row for each category in the set

I need to select a top row for each category from a known set (somewhat similar to this question). The problem is, how to make this query efficient on the large number of rows. For example, let's create a table that stores temperature recording in several places. CREATE TABLE #t ( placeId int, ts datetime, temp int, PRI...

SQL Server error

I am getting a fatal error on following code. exec [sp_ExternalApp_UPDATEUSER] 'ZZZ', XXXXX','DDDDD','DDDFFDD','EREE', 'EREWWWWW',1,1,'[email protected]','DEFF','XXXX','DDDD' Following error occurred: Location: memilb.cpp:1624 Expression: pilb->m_cRef == 0 SPID: 79 Process ID: 2256 Msg 3624, Level 20, State 1,...

SQL server - extract and run queries on 20GB data

I do have a BKS file which contains 15GB+ size. How do I extract the data and run queries on this? I was told that this is SQL Server data. Should I try to upload this to SQL Azure? Any ideas. ...

Check if concatenating to a varchar(max) will go beyond max allowable characters

I am trying to concatenate a list of IDs into a varchar(max) to pass into an openquery for a bulk update of data. My question is, is there an easy way to see if a string is beyond the length a varchar(max) can handle aside from comparing it to the number? I have seen this: http://stackoverflow.com/questions/1761124/how-many-characters-...

limiting sql server constraint checks to only new data

I need to import a lot of data into a sql server database. Some of this data was imported from long ago and much of it does not maintain referential integrity but I want all new data to. I can turn off the constraint checks prior to the import but how do it turn them back on only for data that will be added later? Is this possible or do ...

Good way to allow people to select a lot of things?

I'm using jQuery, ASP.NET, SQL Server, and the other usual suspects to design a company CRM. After they put in contact info, notes, dates, places and so forth they have to be able to select many different people to be "CC'ed." A group of people will be required to be one either "CC'ed" or "ToDo." The rest of the people can be nothing or ...

using windows authentication to log in to a sql server

I have a web application set up on our intranet that uses windows authentication (successfully) that I am trying to link to a database running on a separate server. The problem that I am having however, is that when I try to use a trusted connection to log in to the sql server, instead of passing the domain/username of the person usin...

MS Sync Framework Client Server Scenario with two SQL Server

Hi As far as I understand the Client Server Scenario (aka Offline Scenario) it is supposed to use with a SQL Server as Server and a SQL Server Compact as Client. I have to implement a synchronization between a master and several nodes where the nodes communicate and synchronize with the master but not with each other. Master and Nodes a...

SQL Server backups and performance

Will the performance of SQL Server degrade over time if backups are not done? I assume the log files grow and are cleared out when a full backup is performed. Would this lead to degraded performance as the log files become very large (assuming recovery is set to FULL so everything is logged)? ...

Why does fn_my_permissions(NULL, 'SERVER') return different results depending on which database is in use?

fn_my_permissions "returns a list of the permissions effectively granted to the principal on a securable." use master select * from fn_my_permissions(NULL, 'SERVER') // 25 rows use some_other_database select * from fn_my_permissions(NULL, 'SERVER') // 25 rows use a_third_database select * from fn_my_permissions(NULL, 'SERVER') // 0 ro...

Huge performance differences between connections

I was trying to test the performance difference between inserting into a table directly versus inserting into a view in a different database which points to the table. I was shocked to see that a WHILE loop with 10,000 inserts into a simple table took 3 seconds but against the view it took 30 seconds. As I investigated, I found that if ...

How to create a sql query for sql reporting services 2008 to return the number of occurences of sick days.

How to create a sql query to return the number of occurrences of sick days. Where one occurrence is defined as consecutive days out sick. It would also be considered one occurrence if the a weekend fell within that time period or a holiday fell in that time period. Examples of what is consider one occurrence: A person is out sick Mon...

Set based way to classify rows based on dates fitting inside a rolling two hour window

So I have a set of data that includes a column of dates and an ID column. Here's a sample of the date data: 5/30/10 12:00 AM 5/30/10 12:01 AM 5/30/10 1:59 AM 5/30/10 1:59 AM 5/30/10 3:58 AM 5/30/10 3:58 AM 5/30/10 5:57 AM 5/30/10 6:57 AM 5/30/10 7:56 AM 5/30/10 7:56 AM 5/30/10 9:55 AM 5/30/10 11:54 AM What I'd like to do is create b...

ADO/SQL Server: What is the error code for "timeout expired"?

i'm trying to trap a "timeout expired" error from ADO. When a timeout happens, ADO returns: Number: 0x80040E31 (DB_E_ABORTLIMITREACHED in oledberr.h) SQLState: HYT00 NativeError: 0 The NativeError of zero makes sense, since the timeout is not a function of the database engine (i.e. SQL Server), but of ADO's internal timeout m...