I have: a table with an xml type column (list of IDs) an xml type parameter (also list of IDs) What is the best way to remove nodes from the column that match the nodes in the parameter, while leaving any unmatched nodes untouched? e.g. declare @table table ( [column] xml ) insert @table ([column]) values ('<r><i>1</i><i>2</i>...
Is it possible to create an indexed view with SQL Server 2008 which selects from another indexed view? create view V1 as (select 1 as abc) create view V2 as (select abc from V1 group by abc) ...
Hi, I have an SQL Query that i'm running but I only want to select a specific row. For example lets say my query was: Select * from Comments Lets say this returns 10 rows, I only want to select the 8th record returned by this query. I know I can do: Select Top 5 * from Comments To get the top 5 records of that query but I only wan...
I am working on a console application to insert data to a MS SQL Server 2005 database. I have a list of objects to be inserted. Here I use Employee class as example: List<Employee> employees; What I can do is to insert one object at time like this: foreach (Employee item in employees) { string sql = @"INSERT INTO Mytable (id, name,...
If I've been told a table (or proc) name, but not which connected database the object is located in, is there any simple script to search for it? Maybe search somewhere in the System Databases? (I'm using SQL Server 2005) ...
I have a result like this from a sql query Month Day Customer Item ------------------------------------------ January 1 John Pencil January 1 Jack --- January 1 Steve Stapler January 2 John --- January 2 Jack --- January 2 Steve VisitingCard January 3 John...
What is a good way to learn to develop SQL Server(2005) stored procedures? I am primarily a java developer but need to take on SQL Server stored procedure development. I have some basic SQL in my background but nothing major. Any good specific tutorials maybe using the adventure works schema? Or just good reference sites that contain alo...
For several years I have been using a testing tool called qmTest that allows me to do test-driven database development for some Firebird databases. I write a test for a new feature (table, trigger, stored procedure, etc.) until it fails, then modify the database until the test passes. If necessary, I do more work on the test until it f...
I see some stored procedures in one database I'm managing that have the regular stored proc icon, but with a little padlock next to them. The differences I see is that I can't "modify" them, and if I try to script them, it says "Text is Encrypted". Is this because these are CLR stored procedures? Are they "regular" procedures, but encry...
Keep getting this error after inserting a subdatasheet into a query and trying to show it by clicking on the + Column (Cost) was used in a CALC expression but is not defined in the rowset. What is confusing, is that there isn't even a column named 'Cost' anywhere in the database. Although there is a column that starts with 'Cost' and ...
Hi, I'd like to list all objects of a particular database in SQL Server 2005. I created a query as shown below: select name, type_desc from sys.objects WHERE type in ( 'C', 'D', 'F', 'L', 'P', 'PK', 'RF', 'TR', 'UQ', 'V', 'X' ) union select name, type_desc from sys.indexes order by name However, this query list all objects of ALL d...
Is there a way to run a query and then have SQL Server management studio or sqlcmd or something simply display the datatype and size of each column as it was received. Seems like this information must be present for the transmission of the data to occur between the server and the client. It would be very helpful to me if it could be dis...
I've got a table of 5,651,744 rows, with a primary key made of 6 columns (int x 3, smallint, varchar(39), varchar(2)). I am looking to improve the performance with this table and another table which shares this primary key plus an additional column added but has 37m rows. In anticipation of adding a column to create the hash key, I did...
I have a SQL Server query that I need to convert to MySQL. I've never used SQL Server/T-SQL before, so I have no experience with FOR XML PATH. There's surprisingly little documentation on this sort of thing. If I remove the FOR XML PATH statement, MySQL returns the error "Operand should contain at least 1 column(s)." It seems like the n...
Hello, I can't seem to figure out how this is happening. Here's an example of the file that I'm attempting to bulk insert into SQL server 2005: ***A NICE HEADER HERE*** 0000001234|SSNV|00013893-03JUN09 0000005678|ABCD|00013893-03JUN09 0000009112|0000|00013893-03JUN09 0000009112|0000|00013893-03JUN09 Here's my bulk insert statement: ...
Following the instruction found here: Insert a datasheet into a form I get an error when trying to insert a query as a datasheet in an Access Project (SQL Server 2005 backend) The form name you entered doesn't follow Microsoft Office Access object-naming rules I selected the view View.dbo.viewname from the dropdown in 'Source Objec...
Msg 701, Level 17, State 123, Line 1 There is insufficient system memory to run this query. I have a query that uses Table-Valued function. I am calling the function several times. Does this error mean I am running out of stack space? Or can I tune my SQL box memory settings to fix this? Any ideas? ...
Hello, I am struggling with a strange problem using Sql Profiler. While running some performance testing scripts, I run profiler to find bottlenecks. One particular statement seems to be taking a lot of time - with CPU 1407, Reads 75668 and Duration of 175. However when I run the same statement in Management Studio, SQL Profiler return...
We keep getting this error randomly in our web application. System.Data.SqlClient.SqlException: A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured t...
I've been looking all over the internet and I can't find an acceptable solution to my problem, I'm wondering if there even is a solution without a compromise... I'm not a DBA, but I'm a one man team working on a huge web site with no extra funding for extra bodies, so I'm doing the best I can. Our backup plan sucks, and I'm having a re...