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...
It occurs to me that if you have fields dependent on each other in an update statement, I'm not sure that one can guarantee the ordering (or that one needs to!). As an example, say you had the following Update: UPDATE Table SET NewValue = OldValue, OldValue = NULL Would NewValue always update first, then OldValue be nullified? Or is...
Hi, Is there any place where I can find all possible object type dependencies in Sql Server? What I mean by "object dependency" is a list of object that one object can depend on. For example, TABLE can depend on SCHEMA, TYPE, TABLE, FUNCTION, etc. ...
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...
What is the appropriate SQL commands (not through the GUI) to add a windows user account to permissions on a SQL Server 2008 Database? ie.. I want to give someone read access and another person read/write.. ...
I have a tblMachineReports with the columns: Status(varchar),LogDate(datetime),Category(varchar), and MachineID(int). I want to retrieve the latest status update from each category for every machine, so in effect getting a snapshot of the latest statuses of all the machines unique to their MachineID. The table data would look like Cat...
i need to copy the tables and data (about 5 yrs data, 6200 tables) stored in sqlserver, i am using datastage and odbc connection to connect and datstage automatically creates the table with data, but its taking 2-3 hours per table as tables are very large(0.5 gig, 300+columns and about 400k rows). How can i achieve this the fastes as at...
I have a scripted database SQL file that I need to execute inside Visual Studio to create a new database. I am so far unable to locate any function that allows me to do this. ...
We have a system that uses sp_getapplock to create an exclusive mutex any time someone opens an order in the GUI. This is used to prevent multiple people from making changes to an order simultaneously. Sometimes people will open an order and go home, leaving it open. This effectively blocks anyone from being able to make changes to the ...
I need to write a SQL-Server query but I don't know how to solve. I have a table RealtimeData with data: Time | Value 4/29/2009 12:00:00 AM | 3672.0000 4/29/2009 12:01:00 AM | 3645.0000 4/29/2009 12:02:00 AM | 3677.0000 4/29/2009 12:03:00 AM | 3634.0000 4/29/2009 12:04:00 AM | 3676.0000 // ...
How do I check that a stored procedure exists using ADO.Net? Is there a specific way to do this (apart from executing SELECT OBJECT_ID() and checking the result)? ...
I've created SQL command that use INNER JOIN for 9 tables, anyway this command take a very long time (more than five minutes). So my folk suggest me to change INNER JOIN to LEFT JOIN because the performance of LEFT JOIN is better, at first time its despite what I know. After I changed, the speed of query is significantly improve. I want...
We've realised in our SQL Server 2005 DB that some Foreign Keys don't have the On Delete Cascade property set, which is giving us a couple of referential errors when we try and delete some records. Use the Management Studio I scripted the DROP and CREATESQL's, but it seems that the CREATE isn't working correctly. The DROP: USE [Footpr...
Hello all , Can any one please give me the query to get the child name and his grand fathers name For eg - if i have a table Relationships in the i have childid and fatherid columns so how will i get the grandfather, i can easily get the father name by using a join but for grandfather i need to do joins 2 times so can any one help me wi...
I’m trying to display a list of shops each with 3 random items from their shop, if they have 3 or more listings, that are actively advertising. I have 3 tables: one for the shops – “Shops”, one for the listings – “Listings” and one that tracks active advertisers – “AdShops”. Using the below statement, the listings returned are random ho...
hi, is it possible to alias an aggregate function in a select clause as AliasTable.AliasColumn? The reason is because I want minimum modifications in my arrays (I'm using PHP). Thanks! P.S. I'm using SQl Server 2008 P.S.#2 Example: SELECT MAX(Person.name) name, MAX(Person.address) address, Farm.id, FROM Farm LEFT JOIN Person on Person....
Hi, If I am running an organization that has 50 .net developers and all are using SQL Server, what is the best way to make a single SQL Server available to them? Here is some of the concerns that I want to be careful about Should I configure database users per project or per user? or both? Should I provide single SQL Server instance?...
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 ...
Have built a small VB.Net app in VS2010. The connection string to the SQL Server is as follows: <connectionStrings> <add name="IWSR_DataDownloadWizard.My.MySettings.sqlConnection" connectionString="Provider=SQLOLEDB.1;Data Source=SQLServer;Integrated Security=SSPI;Initial Catalog=IWSROL" providerName="System.Dat...
We are writing unit tests for our ASP.NET application that run against a test SQL Server database. That is, the ClassInitialize method creates a new database with test data, and the ClassCleanup deletes the database. We do this by running .bat scripts from code. The classes under test are given a connection string that connects to the ...