I'm linking to a 3rd party SQL database where I only select information out, never update/delete. This 3rd party told me to always use NOLOCK when I'm selecting out of their database. Some other people I work with said don't worry about using it. Does the WITH (NOLOCK) affect much if my select statements are going to execute in under 1...
I am trying to update a column inside of a table variable based on a condition, the condition being that the ID of the table variable does not exist in a different table: DECLARE @BugRep TABLE(BugCode VARCHAR(50),DevFirstName VARCHAR(50), DevLastName VARCHAR(50), BugDate VARCHAR(20), IsValid VARCHAR(1)) UPDATE @BugRep SET IsValid =...
I've got a stored procedure that puts a message on a SQL Server Service Broker message queue. I need to return an error message from the stored procedure if something goes wrong and the message doesn't get placed on the message queue. The problem I am seeing is that even if the SQL Server Service Broker is disabled (which is how I'm tr...
Hi all This is a duplicate question, but the other one didn't get any answers, so I'll have another go. I have SQL Server 2008 Client Tools installed, and would like to use SQL Server's query designer(s) in my application, for working with queries, views, SPs and functions. I'm pretty sure Access ADP does this, can I? I guess what I a...
I'm trying to access the KPI metadata via ADOMD.NET (using MDSCHEMA rowsets) with SQL Server/SSAS 2008. When I get a given KPI VALUE field, for example in Adventure Works, it returns what I believe is the hidden measure associated with the KPI (e.g., [Measures].[Net Income Value]). But the Value has areal MDX expression associated with ...
Hello everyone, I am using SQL Server 2008 Enterprise + C# + ADO.Net + .Net 3.5. I am using sp_who2 or sys.dm_exec_connections to find active connections numbers (let me know if my method to find active connection numbers are wrong). For some heavy database consumer application, I can find even active connection count > 1000. I am wond...
Here's my deal: I've got a site running PHP 5 (5.2.5 specifically) on a windows server, that's talking to a MSSQL 2005 database for it's data. I'm accessing the database using the latest version of the ADODB framework. I have a table with a unique id and a text field. This all works well and good until the text field reaches a certain si...
Hello everyone, I am using SQL Server 2008 Enterprise + C# + ADO.Net + .Net 3.5. I am using connection pool at ADO.Net client side (the default behavior of ADO.Net). I am using sp_who2 or sys.dm_exec_connections to find active connections numbers (let me know if my method to find active connection numbers are wrong). If each time after...
I have 2 tables that are similar but not the same so a union is not a possibility. I need to combine the tables bearing in mind there's about 40 columns where only 20 are common to both. Any ideas on the best approach? Table1 ActivityCategory ActivityType Nationality Language ----------------------------------------------------...
The purpose of this query is to bring back products and their prices for products on sale and the price should be from the date closest but not equal to the date passed in, essentially the most recent price available. There are not price records for every day. Something feels a little wrong about having the aggregate select statement i...
I want to generate script to assign an user account to some securables, e.g. Table:Select. How to do this? ...
I have a table in SQL Server where I have a unique SiteNumber for every unique location_id. What I'm trying to do is increment each sitenumber by 1 if the location_id is different. For example: SITENUM LOCATION_ID 1234 8801 4567 8802 8910 8803 ... and so on Lets say, I hav...
According to BOL (SQL Server Books Online) on sp_dbcmptlevel, This feature will be removed in a future version of Microsoft SQL Server. Do not use this feature in new development work, and modify applications that currently use this feature as soon as possible. Use ALTER DATABASE Compatibility Level instead. Now, the only TSQL way...
Hello, I need to give data to a data processing windows service (one-way, loosely coupled). I want to ensure that the service being down etc. doesn't result in 'lost' data, that restarting the windows service simply causes it to pick up work where it left and I need the system to be really easy to troubleshoot, which is why I'm not using...
I am having trouble with the following query which returns the following error: 'Article' is not a recognized option. Incorrect syntax near the keyword 'ON' Is there something obvious wrong that you can point out?? ( @PageIndex int, @PageSize int ) WITH Article AS ( SELECT tbrm_Article.ArticleID, tbrm_Article.CountryID, tbrm_Arti...
I am working on a web project where I have to retrieve (let's say) employee records. In some cases I have to retrieve a single record by providing an EmployeeID. In other cases, I have to retrieve multiple employee records by providing a SectorID. This logic could be expanded to cover additional scenarios: get all employee records, get e...
Hello there! I have a database in SQL Server 2008 that supports various applications. I am now starting the development of a new application and, from that database, I require data that fits in one table (though there is some replication). The data I way may be extracted to a view (a few inner joins) with 6 columns. I'm developing a we...
The below cross apply sql statement is the only part of a stored proc i cannot get to work. The error says that there is incorrect syntax near the keyword 'ON' Can anyone see the problem? The select statement works fine if that helps at all. CROSS APPLY ( SELECT tbrm_Votes.ArticleID, AVG(tbrm_Votes.Truth), AVG(tbrm_Votes.Relevanc...
Using SQL Server 2000 Having Two Tables Table1 Date ID 20090101 001 20090102 001 …. 20090101 002 20090102 002 … 20090101 003 20090102 003 … So on.., Table2 ID Name Date 001 Raja 20090408 001 Raja 20090502 001 Raja 20090503 002 Ravi 20090312 002 Ravi 20090522 003 Saga 20090802 003 Saga 20091022 So on.., Query SELECT Table...
For Sql Server Analysis Services. What im looking for is if anyone has example code of a class or function or a precompiled dll that you can pass columns, rows, pages, with filters etc. This then generates the mdx string. im working in asp.net but should be able to convert from an example in any other language ...