I am trying to implement hierarchyID in a table (dbo.[Message]) containing roughly 50,000 rows (will grow substantially in the future). However it takes 30-40 seconds to retrieve about 25 results. The root node is a filler in order to provide uniqueness, therefor every subsequent row is a child of that dummy row. I need to be able to ...
How do you pass a value from your DAL to your sproc so that the ISNULL function will do it's job. Particularly the DATE value coming from my .NET assembly. In T-SQL an INSERT STMNT and in the VALUES clause, the line of interest goes like this; ISNULL(@myparm_forcolumn9, @myparm_forcolumn9) What value do I pass from .NET to make t...
Consider a SQL Server database and its two stored procs: *1. A proc that performs 3 important things in a transaction: Create a customer, call a sproc to perform another insert, and conditionally insert a third record with the new identity. BEGIN TRAN INSERT INTO Customer(CustName) (@CustomerName) SELECT @NewID = SCOPE_IDENT...
In TSql, how do you turn off a specific bit in a bitmask without having to check to see if the bit is set or not? ...
Hi, Let's think that a stored procedure takes an out parameter. When this stored procedure is called with this out parameter is already initialized to something, does this situation cause an exception? thanks. ...
Hey all I have a stored procedure sp that calls a table-valued function tvf. Sometimes I modify the tvf but when subsequently executing sp, the output from sp is the same as before the modification. It seems like it is cached or compiled or something. If I make some dummy change to the sp, then I get the right output of the sp. Is ther...
Whenever I do a: $this->Job->find('all', array( 'group' => array('Job.some_field'), 'recursive' => -1 )); I get a : SQL Error: Column 'jobs.id' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause. With MySQL it works fine but with SQL Server 2008 it se...
I heard opinion that it make no sense to create indexes when field has less changeability. Eg if it stores only A,B,C,D values then there wouldn't be benefit of having such index, and further more SQL server will not use it at all when executing query. I wonder your opinion on that matter? EDIT Sample usage Select * FROM Table WHERE ...
I need to automate the creation of a duplicate db from the .bak of my production db. I've done the operation plenty of times via the GUI but when executing from the commandline I'm a little confused by the various switches, in particular, the filenames and being sure ownership is correctly replicated. Just looking for the TSQL syntax f...
I have a form with many project questions and in some of those questions are about 20 CheckBoxes to be checked. I want to create a SQL structure for these projects. The majority of data is located in a Project table. But I don't want to have all these CheckBoxes as columns of the project table, instead I want to make a table where all ...
Regarding to SQL performance. I have a scalar valued function for checking some specific condition in base, it returns BIT value for True or False. I now do not know how I should fill @BIT parameter If I write. set @bit = convert(bit,1) or set @bit = 1 or set @bit='true' function will work anyway but I do not know which met...
When looking at the actual execution plan for a query in SQL Server Management Studio (SSMS), how do I determine what an expression such as Expr1052 represents? When I identify the costly parts of the query and look at the properties of that operation, there are often references only to these Expressions, or scalar operators. I want to...
I'm trying to make a query that looks at a single table to see if a student is in a team called CMHT and in a medic team - if they are I don't want to see the result. I only want see the record if they're only in CMHT or medic, not both. Would the right direction be using sub query to filter it out? I've done a search on NOT IN but h...
Similiar to question: http://stackoverflow.com/questions/2266132/how-can-i-get-a-list-of-element-names-from-an-xml-value-in-sql-server How would I also get the values for the list of elements. For example: <Root> <A>a1</A> <B>b1</B> <C>c1</C> </Root> Would return Element | Value A | a1 B | b1 C | c...
I'm wondering how SQL Server orders data that is returned from a query and the Id columns of the respective tables are all of type uniqueidentifier. I'm using NHibernate GuidComb when creating all of the GUIDs and do things like: Sheet sheet = sheetRepository.Get(_SheetGuid_); // has many lines items IList<SheetLineItem> lineItems = sh...
I'm looking to restore one field from a backup and can't find the syntax for an update statement that can look at 2 different catalogs. Seems like it should be something fairly close to: update users set idUserCompany = (select idUserCompany from .myBackup.dbo.users uT) where uT.idUser = idUser Note: Backup used here in a generi...
I need to start a SSIS package via a stored procedure. I chose to use 'exec dtexec' instead of starting a job to launch the package, so I can have the ability to set variable in the package. My problem is that I need the package to run asynchronously so that the stored procedure will return instead of it hanging or timing out. What is ...
Hi Based on the following table Path ---------------------- area1 area1\area2 area1\area2\area3 area1\area2\area3\area4 area1\area2\area5 area1\area2\area6 area1\area7 Input to my stored procedure is areapath and no.of children (indicates the depth that needs to considered from the input areapath) areapath=area1 children=2 Above sh...
Hi For the below data (well..there are many more nodes in the team foundation server table which i need to refer to..below is just a sample) Nodes ------------------------ \node1\node2\node3\ \node1\node2\node5\ \node1\node2\node3\node4\ \node1\node2\node3\node4\node5\ I was wondering if i can apply something like (below query does n...
Hi, I am wondering why I cannot use variable column name like that: declare @a as varchar; set @a='TEST' select @a from x; Thank you ...