NHibernate elt field
Does anyone know why NHibernate generates a field named 'elt' of type int for a many to many mapping? I'm wondering why I need it. Thanks ...
sql-server-2005
How does normal SPs handle exception thrown by managed SPs
I read that SQL exceptions are treated as normal exceptions in managed SPs; I would like to know how is following scenario handled w.r.t to this. I have a normal t-SQL SP that calls a managed SP. managed SP throws exception due to some issue. How does normal T-SQL handle this. I have not tried this scenario yet as I do not have SQL s...
SQL Server Globally Unique Identifiers
When or why will one need to use Globally Unique Identifiers instead of the traditional Idenity column? Please help me understand ...
Can AWE use >4GB RAM on SQL Server 2005 dev edition on Windows XP 32-bit
Using SQL Server 2005 developer edition on Windows XP pro (32-bit) I notice that the check box to enable AWE (Advanced Windowing Extensions) is enabled. I have an Opteron workstation that I could easily upgrade to 8GB or more, although it is not practical to move off 32-bit Windows XP Pro for the development environment. As far as I ca...
SQL Server 2005 dependency cache without asp.net
Does anyone know a good online resource about using SQL Server 2005 dependency cache without asp.net (it's actually a console app). Using c# 2.0. ...
Select Parent Record With All Children in SQL
Let's say I have two tables, "Parent" and "Child". Parent-to-Child is a many:many relationship, implemented through a standard cross-referencing table. I want to find all records of Parent that are referenced by ALL members of a given set of Child using SQL (in particular MS SQL Server's T-SQL; 2005 syntax is acceptable). For example l...
Is it possible to ignore an output param of a stored procedure?
How can I ignore an output parameter of a stored procedure. I'm calling the sp from another sp. e.g.: DECLARE @param1 integer EXEC mystoredprocedure @in_param_1, @in_param2_, @param1 OUTPUT, what do I type here to ignore the second output param I'm using T-SQL (MS SQL 2005). Thanks for your help. ...
How do i drop a foreign key constraint if it exists in sql server 2005?
I can drop a table if it exists using the following code but do not know how to do the same with a constraint: IF EXISTS(SELECT 1 FROM sys.objects WHERE OBJECT_ID = OBJECT_ID(N'TableName') AND type = (N'U')) DROP TABLE TableName go I also add the constraint using this code: ALTER TABLE [dbo].[TableName] WITH CHECK ADD CONSTRAINT [...
Effectively transforming data from one SQL database to another in live environment
We have a bit of a messy database situation. Our main back-office system is written in Visual Fox Pro with local data (yes, I know!) In order to effectively work with the data in our websites, we have chosen to regularly export data to a SQL database. However the process that does this basically clears out the tables each time and does...
Why is this query returning unwanted results?
Good morning, I have a problem with this query: SELECT P.txt_nome AS Pergunta, IP.nome AS Resposta, COUNT(*) AS Qtd FROM tb_resposta_formulario RF INNER JOIN formularios F ON F.id_formulario = RF.id_formulario INNER JOIN tb_pergunta P ON P.id_pergunta = RF.id_pergunta ...
Select "where clause" evaluation order
In Sql Server 2005 when I have multiple parameters do I have the guarantee that the evaluation order will always be from left to right? Using an example: select a from table where c=1 and d=2 In this query if the "c=1" condition fails the "d=2" condition will never be evaluated? PS- "c" is an integer indexed column, d is a large ...
MS SQL Server: Check to see if a user can execute a stored procedure
How can you check to see if a user can execute a stored procedure in MS SQL server? I can see if the user has explicit execute permissions by connecting to the master database and executing: databasename..sp_helpprotect 'storedProcedureName', 'username' however if the user is a member of a role that has execute permissions sp_helprot...
SQL 2005 Linked Server Query Periodically Failing
We have a database running on SQL 2005. One of the store procedure looks up a user's email address from Active Directory using a linked server. The call to the linked server occurs in a database function. I'm able to call is successfully from my Asp.Net application the first time, but periodically after that, it fails with the followi...
What are your thoughts on breaking up a large SQL table into several, based on a certain category id?
We have a large table that services many different stored procedures on lots of websites, but generally only deal with data in the table that relates to the website using it. In other words, would it be wrong to split the table by websiteID? ...
SQL Server Script Wizard
Hi I am using SQL Server 2005. If I try to generate scripts for the dB using the Generate Scripts wizard in the management studio (Right click dB-> Tasks-> Generate Scripts) I get no option like IF EXISTS DROP condition in the list. Whereas some of the SQL Servers installed on different machines have this option enabled. Is the serv...
SQL Server, Converting NTEXT to NVARCHAR(MAX)
I have a database with a large number of fields that are currently NTEXT. Having upgraded to SQL 2005 we have run some performance tests on converting these to NVARCHAR(MAX). If you read this article: http://geekswithblogs.net/johnsPerfBlog/archive/2008/04/16/ntext-vs-nvarcharmax-in-sql-2005.aspx This explains that a simple ALTER COL...
SQL Server - What is the fastest way to copy table data to another server?
I have two MS SQL servers at different locations on the WAN. At the moment I use LINKED servers to copy table data from one to the other, but performance is toilet. It takes about 10 minutes to copy a 40 field table with 800 rows. What is the best 'simple' method of copying/replacing table data on a different SQL server. This solution m...
Check numeric in DerivedColumn Data Flow Task in SSIS
Is there any way where I can check if the field is numeric in derived column/Conditional Split of the data Flow task in Sql Server Integration Services 2005. Functionality should be like ISNUMERIC() function. Currently I'm using ((DT_NUMERIC,12,0)fieldname= (DT_NUMERIC,12,0)fieldname) in the derived column expression to check if th...
How do you fix queries that only run slow until they're cached.
I have some queries that are causing timeouts in our live environment. (>30 seconds) If I run profiler and grab the exact SQL being run and run it from Management Studio then they take a long time to run the first time and then drop to a few hundred miliseconds each run after that. This is obviously SQL caching the data and getting it ...
Does the 'Enable Advanced Performance' override FILE_FLAG_WRITETHROUGH on Win 2k3/SQL Server 2005
SQL Server opens files with FILE_FLAG_WRITETHROUGH, which appears to force writes to the physical disk. In SQL Server-speak this is called 'Forced Unit Access (FUA)'; a white paper discussing this can be found here. Many SANs appear to honour this and this might explain slow performance of an ETL process I am developing on a server con...