I have a stored procedure which is returning me about 50 columns. I want to write a query, where I will be able to select a particular column from the list of column returned by the SP. I tried writing select RSA_ID from exec(uspRisksEditSelect '1') But Its throwing me an error. I think we need to write some dynamic sql for it. But I am...
I have a stored procedure that inserts into a table (where there is an identity column that is not the primary key- the PK is inserted initially using the date/time to generate a unique value). We then use SCOPEIDENTITY() to get the value inserted, then there is some logic to generate the primary key field value based on this value, whi...
Why is it necessary to remove and then re-add a user to a SQL Server database after restoring it from a file? If I don't do this, I get a "User login failed" when trying to access the database using this username from apps. ...
Based on the following data structure. CREATE TABLE [Parent] ( Id INT, Name nvarchar(100) ) CREATE TABLE [Child] ( Id INT, ParentId INT, Name nvarchar(100) ) INSERT INTO [Parent] VALUES (1, 'a') INSERT INTO [Parent] VALUES (2, 'b') INSERT INTO [Parent] VALUES (3, 'c') INSERT INTO [Child] VALUES (1, 1, 'a1') INSERT...
Is there any disadvantage of locking a record before updating it? ...
My sql query gives the columns: product_id (which is an integer) pnl (which is float - can be negative) I get more than 100 rows. I want to filter out the top 40 rows based on abs(pnl). But the results should be ordered by pnl column only and not by abs(pnl). I want to do this for MSSQL 2005. Is there a way to do this? ...
I have been getting these intermittent ColdFusion Database connection reset errors and was wondering if anyone had experience with this and had a particular solution that worked? Here is the error: Error Executing Database Query.[Macromedia][SQLServer JDBC Driver]A problem occurred when attempting to contact the server (Server retur...
Ok... I don't understand why this query is taking so long (MSSQL Server 2005): [Typical output 3K rows, 5.5 minute execution time] SELECT dbo.Point.PointDriverID, dbo.Point.AssetID, dbo.Point.PointID, dbo.Point.PointTypeID, dbo.Point.PointName, dbo.Point.ForeignID, dbo.Pointtype.TrendInterval, coalesce(dbo.Point.trendpts,5) AS TrendPts,...
XML is used as one of our main integration points. it comes over by many clients at a time but too many clients importing at the same time can slow down our database to a crawl. Someone has to have solved a problem like this. I am basically using VB to parse through the data and import what i want and don't want. Is there a better w...
Hi, I've been handed a dataset that has some fairly basic table structures with no keys at all. eg {myRubishTable} - Area(varchar),AuthorityName(varchar),StartYear(varchar),StartMonth(varcha),EndYear(varchar),EndMonth(varchar),Amount(Money) there are other tables that use the Area and AuthorityName columns as well as a general use of M...
Can I assign a value with EXISTS in the query. Currently it is giving me error Incorrect syntax near '='. But when I uses this query only it runs as expected. IF EXISTS (SELECT @PK_LOGIN_ID=PK_LOGIN_ID FROM dbo.M_LOGIN WHERE LOGIN_NAME=@LOGIN_NAME AND PASSWORD=@PASSWORD AND FK_ROLE_ID=@FK_ROLE_ID) RETURN @PK_LOGIN_ID ...
i have a transactional replication with push subscription set up in my system. I am trying to develop a simple gui to display the publication name in a listbox and user will select the publication name in the listbox and synchronize with a single button click using C#. i have already harcoded publisher name,subscriber name,subscription ...
I have an application which will start a program flow with the arrival of an email. I will need to link the email to my application, which I'm going to do via a custom property on the message. I will then need to store the email for reference forever. As Exchange 2010 supports only 10Gb and 100,000 items in a mailbox (without using PS...
When creating a record "grid" with custom paging what is the best/optimal way to query the total number of records as well as the records start-end using C#? SQL to return paged record set: SELECT Some, Columns, Here FROM ( SELECT ROW_NUMBER() OVER (ORDER BY Column ASC) AS RowId, * FROM Records WHERE (...) )...
I have a column which has surname and firstname plus salutation in. e.g. Bloggs,Joe,Mr I need to break this out into Bloggs Joe Mr as 3 separate columns. Any ideas appreciated. The other thing is I won't know how many commas are in the initial column. ...
Is there a way in SQL Server to parition over a date range? I'm looking for something along these lines: SELECT ROW_NUMBER() OVER (PARTITION BY RANGE(DateTimeField) INTERVAL(1)) I want to group rows that are within one day of each other. ...
Based on following table (I have kept spaces between the rows for clarity) Path ----------- \node1\node2\node3 \node1\node2\node3\node5 \node1\node6\node3 \node1\node4\node3 \node1\node4\node3\node7 \node1\node4\node3\node8 \node1\node4\node3\node9 \node1\node4\node3\node9\node10 I want to get all the paths containing leaf node. So ...
I need to manage the trace files for a database on Sql Server 2005 Express Edition. The C2 audit logging is turned on for the database, and the files that it's creating are eating up a lot of space. Can this be done from within Sql Server, or do I need to write a service to monitor these files and take the appropriate actions? I found...
I have to query two different servers from a dynamically built query. It basically gets data from one server, treats it, and inserts it into another server. The only problem is I have to be sure it works for both situations: If both the source and destination databases are on the same server, and if they're not. I understand the conc...
Lets say i installed two named SQL Server 2008 instances e.g. A and B, will i have two services of each type e.g. two analysis service, two reporting service and so on, one service for A and the other for B? If yes, then it is known a service listens on a port number, how two same services going to listen on the port? I hope this is cle...