sql-server

How can I tell *IF* AND *WHAT* partitions are being accessed - sql server 2008 db engine

Setup Cost of Threshold for Parallelism : 5 Max Degree of Parallelism : 4 Number of Processors : 8 SQL Server 2008 10.0.2.2757 I have a query with many joins, many records. The design is a star. ( Central table with fks to the reference tables ) The central table is partitioned on the relevant date column. The partition schema is spl...

What exactly does the T-SQL "LineNo" reserved word do?

I was writing a query against a table today on a SQL Server 2000 box, and while writing the query in Query Analyzer, to my surprise I noticed the word LineNo was converted to blue text. It appears to be a reserved word according to MSDN documentation, but I can find no information on it, just speculation that it might be a legacy rese...

Can you use ANTS Performance Profiler to profile an application hitting SQL Server Express editions?

I want to know if it's possible to profile my web application when it's hitting a database on a SQL Server Express server with ANTS profiler. Out of the box, I'm not able to. It's complaining about: No event provider could be located for the SQL server instance 'SQLEXPRESS' Has anyone been able to get around this, or do I have ...

How do I get several highest values from a table?

I have a table like id f1 -------------- 1 2000-01-01 1 2001-01-01 1 2002-01-01 1 2003-01-01 And I want to get say the latest 3 dates in one row CREATE TABLE Test ( id INT NOT NULL, f1 DATETIME NOT NULL, ) INSERT INTO Test (id, f1) VALUES (1, '1/1/2000') INSERT INTO Test (id, f1) VALUES (1, '1/1/2001') INSERT INTO Test...

sql server 2008 - manipulating dates - week window.

If I want to make sure an operation can only be done once a week by a user, what is an efficient way to do it? If a user has carried out that operation on Friday, then he can still do it on Tuesday, because it's "next" week. ...

Creating server side cursors

com.microsoft.sqlserver.jdbc.SQLServerException: The system is out of memory. Use server side cursors for large result sets:Java heap space. Result set size:280,236,031. JVM total memory size:423,297,024. I am trying to fetch a big data set from SQL and I get following error. Let me know if anyone have seen something similar and wi...

How to determine sql server security mode in .net?

Does anyone know how to determine the security mode (Mixed Mode or Windows Authentication) in .NET? EDIT: I am using normal ADO.NET objects (SqlConnection, SqlCommand, etc) to connect to the database. Thanks for any and all advice. Steve ...

Creating Multiple Groups of Data in SQLServer

Been scratching my head on this one, but I feel I'm overlooking the obvious answer, and none of my search phrases have lead to any results.. I am compiling the results of a survey in a database, in the following structure. ID TestName Q1 Q2 Q3 Q4 -- --------- ---- ---- ---- ---- 1 test1 1 2 1 1 2...

SQL BETWEEN Operator

Why am I getting '2009' data? What am i doing wrong with the WHERE Clause? SELECT CONVERT(varchar, EventDate, 101) AS EVENTDATE, CONVERT(varchar, ClosedDate, 101) AS CLOSEDDATED, DATEDIFF(Day,EventDate,ClosedDate) AS DiffDate, FROM mytable WHERE (CONVERT(varchar, EventDate, 101) BETWEEN '04/01/2010' AND '04/30/2010')...

Importing HTML using creates odd characters from non-standard characters?

We're attempting to read in an HTML file that contains certain MS Word characters (such as that long hyphen). The problem is these characters, for example, are showing up as garbage in SQL 2008. The data column is varbinary, and am viewing this data by casting to varchar. Here is the code, verbatim: EDIT: Corrected definition of bad cha...

How can I sync my MSSQL database to MySQL?

I am using MSSQL as my transactional database. This database has 200+ tables with about 25 tables that hold 1M plus records. I want to replicate the data with the same structure into a MySql database for reporting. Is there a tool or method that can do this in a fairly real time manner? ...

app.config not found in SQL Server external assembly call

Hi, In SQL Server ,I created an assembly to run an external DLL. When I run the stored procedure that execute my function I got an exception that my configuration file is not found. Does any one know where the app.config should be located so that SQL Server knows how to load it. I have it right now in the same folder as my DLL. Thanks...

Script to save varbinary data to disk

I have some varbinary data stored in a table in MS Sql Server 2005. Does anyone have SQL code that takes a query as input (lets say the query guarantees that a single column of varbinary is returned) and outputs the bytes to disk (one file per row?) I'm sure this has been asked a thousand times before, but Googling comes up with mostly...

SQL - Most performant manner to conditionally exlude a join? (if it's even possible)

If I have the following table structure... Table 1: BlogPost PostId | Name | Text Table 2: Tags TagId | Tag Table 3: BlogPostTag PostId | TagId And the following stored procedure... CREATE PROCEDURE SearchBlogPosts @tagstring nvarchar(max), AS BEGIN DECLARE @searchTags TABLE (Tag varchar(50)); IF @tagst...

incremental SQL query

My application has a fixed set of SQL queries. These queries are run in polling mode, ever 10 seconds. Because of the size of the database (> 100 GB), and the design (uber normalized), I am running into performance issues. Is it possible to get incremental changes to a given query, whenever a CRUD event happens on the DB that changes t...

When and how SQL Server disposes variables from memory in stored procedures?

When we create a stored procedure propably we create some variable like this : DECLARE @UserCount INT We use that variable in stored procedure. But we don't dispose that variable at the end of stored procedure. We dispose Cursors with DEALLOCATE but we don't write ant lines for variables which we create. Another stuation is we creat...

SQL Server Primary file group max size

Hello All!!! I have a situation, I am trying to run almost 100 update statement on a table say 'XX' In SQL Server database. But it is giving me an error tat goes something like this... "Could not allocate space for object 'dbo.XX'.'PK_XX_3489AE06' in database 'MYDATABASE' because the 'PRIMARY' filegroup is full. Create d...

Why to add clustered index keys to all (intermediate) nodes on NCI?

Considering clustered table, Quassnoi wrote (the last phrase in answer): If the secondary index is declared UNIQUE, clustered key is appended only to the leaf-level records of the secondary index. This sounds like clustered key is added to (all) indermediate nodes of non-unique non-clustered index. And by the same logic RIDs are a...

How sequential traversing between intermediate leaves of clustered index used?

Intermediate leaves of clustered index are linked sequentially (next, previous) for faster access (between intermediate nodes) [2], [3], etc.: How this access is used? Why is it needed? [2] Clustered Index Structures http://msdn.microsoft.com/en-us/library/ms177443.aspx [3] Clustered Tables vs Heap Tables http://www.mssqltips.com/...

Save byte[] into a SQL Server database from C#

How can I save a byte[] array into a SQL Server database? This byte[] contains a HashAlgorithm value. The data is needed again for later use. So converting it and NOT getting it back in its original state, is not what I want. Thanks in advance! ...