I want to generate some XML in a stored procedure based on data in a table. The following insert allows me to add many nodes but they have to be hard-coded or use variables (sql:variable): SET @MyXml.modify(' insert <myNode> {sql:variable("@MyVariable")} </myNode> into (/root[1]) ') So I coul...
In SQL Server 2005, are there any disadvantages to making all character fields nvarchar(MAX) rather than specifying a length explicitly, e.g. nvarchar(255)? (Apart from the obvious one that you aren't able to limit the field length at the database level) ...
Hello all. i work with sql server, but i must migrate to an application with Oracle DB. for trace my application queries, in Sql Server i use wonderful Profiler tool. is there something of equivalent for Oracle? Thank you for any Advice. ...
Hi, I have an ASP .NET 2.0 website connected to a SQL Server 2005 database. The site is pretty simple and stores information about staff, including salary. What is the best way to encrypt the salary value so no-one (including myself) can ever see what it is, except for the authorised staff using the web-app? I don't want to encrypt /...
My everyday IDE is Eclipse which has a wonderful Open Resource feature (CTRL+SHIFT+R or Navigate > Open Resource) which allows the user to search for files/resources across multiple projects. I can't find a similar feature in SQL Server Management Studio, is there one? ...
I'm trying to do a simple update. I've done this kind of thing thousands of times. update articles set department = 60 where type = 'Top Story' Today I get a strange error. Describe Error: Failed to retrieve execution plan: Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= ,...
I'm not sure if this is something I should do in T-SQL or not, and I'm pretty sure using the word 'iterate' was wrong in this context, since you should never iterate anything in sql. It should be a set based operation, correct? Anyway, here's the scenario: I have a stored proc that returns many uniqueidentifiers (single column results)....
Does anybody know what hypothetical indexes are used for in sql server 2000? I have a table with 15+ such indexes, but have no idea what they were created for. Can they slow down deletes/inserts? ...
The IT department where I work is trying to move to 100% virtualized servers, with all the data stored on a SAN. They haven't done it yet, but the plan eventually calls for moving the existing physical SQL Server machines to virtual servers as well. A few months ago I attended the Heroes Happen Here launch event, and in one of the SQL ...
We have a Windows machine running SQL Server 2005, and we need to be able to run some database queries on it from a Linux box. What are some of the recommended ways of doing this? Ideally, we would want a command-line utility similar to sqlcmd on Windows. ...
I've got a 'task list' database that uses the adjacency list model (see below) so each 'task' can have unlimited sub-tasks. The table has an 'TaskOrder' column so everything renders in the correct order on a treeview. Is there an SQL statement (MS-SQL 2005) that will select all the child nodes for a specified parent and update the TaskO...
This question comes close to what I need, but my scenario is slightly different. The source table and destination table are the same and the primary key is a uniqueidentifier (guid). When I try this: insert into MyTable select * from MyTable where uniqueId = @Id; I obviously get a primary key constraint violation, since I'm attemp...
I have several stored procedures in my database that are used to load data from a datamart that is housed in a separate database. These procedures are, generally, in the form: CREATE PROCEDURE load_stuff WITH EXECUTE AS OWNER AS INSERT INTO my_db.dbo.report_table ( column_a ) SELECT column_b FROM data_mart.dbo.source_table WHERE ...
I have a query that I'm executing from a .NET application to a SQL Server database and it seems to take quite a while to complete (5+ Minutes). I created a test app in c# to try to see what was talking so long (the query should return quickly). As I was reconstructing the query by adding in elements to see which portion was taking so...
I was helping out some colleagues of mine with an SQL problem. Mainly they wanted to move all the rows from table A to table B (both tables having the same columns (names and types)). Although this was done in Oracle 11g I don't think it really matters. Their initial naive implementation was something like BEGIN INSERT INTO B SELECT...
I was recently tasked with debugging a strange problem within an e-commerce application. After an application upgrade the site started to hang from time to time and I was sent in to debug. After checking the event log I found that the SQL-server wrote ~200 000 events in a couple of minutes with the message saying that a constraint had fa...
The problem itself is simple, but I can't figure out a solution that does it in one query, and here's my "abstraction" of the problem to allow for a simpler explanation: I will let my original explenation stand, but here's a set of sample data and the result i expect: Ok, so here's some sample data, i separated pairs by a blank line -...
It seems like Sql Reporting Services Server logs information in several places including web server logs and logging tables in the database. Where are all the locations SSRS logs to, and what type of errors are logged in each place? ...
Hello people! My company is heavily invested in the MS BI Stack (SQL Server Reporting Services, -Analysis Services and -Integration Services), but I want to have a look at what the seemingly most talked about open-source alternative Pentaho is like. I've installed a version, and I got it up and running quite painlessly. So that's good....
There are multiple delete rows questions here, but this one is a little unique (not difficult, just unique). I have already ready the other thread found here: http://stackoverflow.com/questions/18932/sql-how-can-i-remove-duplicate-rows Question: I have a table with rowID,longitude,latitude,businessName,url, caption. This might look lik...