Hello! I need to import 6.5MB .xml file in SQL Server 2005. So, I've created a table: create table eb_main_itemvalues_xml (id int, v xml) And executed command: insert into eb_main_itemvalues_xml (id, v) select 2, * from openrowset ( bulk 'C:\eb_main_itemvalues_export.xml', single_bl...more >>

I'm only a few months into SQL 2005, but I'm at a stopping point with a small project I'm working on. The objective is to allow testers to run packages that can load sample data into tables without having to bother the developers. So far, my solution is to deploy the packages to the file syst...more >>

Hi I have a table (A) with various columns however one column I wish to GROUP BY has numbers (N) from 1-10. I want to group the numbers 1-3, 4-6, and 7-10. It would be nice to call them Group1, Group2, Group3. So I thought of this for starters? SELECT N FROM A WHERE (N = 1 OR N = ...more >>

I want to assign a variable from a sub query and then use that query to do a calculation in the main query, for example: Declare @Var1 decimal (5,3) Select var_id, name, forename, rate, @Var1 = ( select top 1 from exch_rate where rate_id = var_id and exch_date > order by var_id ), rat...more >>

Hello, Thanks for reviewing my question. I have a table where some of the rows have NULL in a particular column. I would like to select only these but when I put WHERE Acolumn=NULL none are displayed. What am I doing wrong? Many Thanks Peter...more >>

I have the following tables and query that I'm working with. At the bottom is the simple query that returns 2 rows and a couple of (poor) try's at getting the result I need.: 1 A001 Acne 1 3 1 1 A001 Face Excluding Eyes 4 5 2 However, the Taxonomies table contains a FK that refers to itse...more >>

I wrote a stored procedure that uses a cursor and a temp table to return values. I would like to change this to a single query if it is possible to improve performance. The source data looks like this. Sorted by ID then DateValue. ID DateValue TextVa...more >>

A client keeps sending me .BAK files. A couple have worked, but I have gotten this error on many: Error 3241 Severity 16 State 1 The media family on device '%ls' is incorrectly formed. SQL Server cannot process this media family. If I view the contents of the .BAK (in Enterprise Manager), ...more >>

Back in the day, when we used to use Enterprise Manager, I could right click on "Diagrams" and then click on "New Database Diagram". This allowed me to get some nice table layouts I could use. We have recently moved over to SQL Server Management Studio and when I right-click on "Database Di...more >>

Hello, I am trying to create a comma delimited list of values from a column table containing a changing number of rows, could one, two,....,n number of rows. This column contains descriptions of projects. Insert the result of this select into one column one row temptable that would ho...more >>

hi, with sql 2005, how to script out a table definition to a file programmatically (tsql, or smo, or?) Thanks ...more >>

Hello experts, I am trying to select row values and then updating it to each column on a different table. Let me take an example to describe it clearly create table #A (RecID tinyint, String varchar(1)) create table #B (col1 varchar(1), col2 varchar(1), co...more >>

What is meant when someone refers to a SQL "Instance"??? Thanks in advance for your help. PSULionRP...more >>

One trigger in my table checks a Date field on data insert. If the Date value is incorrect then rollback the entire transaction during a data Insert and display an error message. If everything is correct for the dataset being inserted then the Instead Of trigger is supposed to insert the ro...more >>

We've encountered something odd when testing queries on a customer's database. i'll supply more detailed dll later, but the basic problem is that SELECT * FROM Table WHERE Field = Value performs MUCH differently than DECLARE @SomeGUID uniqueidentifier SET @SomeGUID = '{a guid goes her...more >>

Hi all, I am getting this error: [Microsoft][ODBC SQL Server Driver][SQL Server]Could not allocate ancillary table for view or function resolution. The maximum number of tables in a query (260) was exceeded. I know it's because in my application I have views on views on views and I need ...more >>

Hi everyone, Having a problem with a select distinct. Table: Stories StoryID | Headline | PrimaryTag --------------------------------------------------- 1 | 'Story Heading 1' | 'COM' 2 | 'Story Heading 2' | 'EDU' 3 | 'Story Heading 3'...more >>

I would to write a query that returns 0 row if any of the rows contains StatusID = 1 and returns the row with StatusID = 4 when there is no rows with StatusID = 1. I thought it would be really easy ( I am sure it is for db folks), but I am pulling my hair here. Any help will be greatly appreciat...more >>

How do I create a stored procedure that exports xml data and saves it to a file? I created a stored procedure that takes a xml file on the network and saves to a SQL Server 2005 table. Specificaly, it saves the entire xml file into a xml datatype column. I know want to reverse this trans...more >>

Hi One of my managers has a SQL view that returns the following columns from a table in Microsoft CRM activity (uniqueidentifier) recipient (uniqueidentifier) importance (int) between 1 & 4 For each activity, there can be many receipients, each of which will have been given import...more >>

Within SQL Server Management Studio and Object Explorer, if I open a table within Object Explorer, is there any way to change the results to Text rather than Grid so that I can copy and paste the results into an Excel spreadsheet??? The "SQL Editor" buttons are not available and thus the i-...more >>

Hi I have a situation where I think NOT IN is the best way of achieving a solution. However, I've heard it's bad (slow), so I wondered how else I could do it? I need to SELECT the top 6 items in a table, AFTER the 1st one. So.. SELECT pk, [fields] FROM [table] WHERE pk NOT IN (SELECT TO...more >>

Hi, I have this table: CREATE TABLE [dbo].[tbl_logs]( [id] [int] IDENTITY(1,1) NOT NULL, [dateAdded] [datetime] NULL CONSTRAINT [DF_tbl_logs_dateAdded] DEFAULT (getdate()), [localTime] [datetime] NULL, [isPageView] [tinyint] NULL CONSTRAINT [DF_tbl_logs_isPageView] DEFAULT ((1)),...more >>

Within SQL Server Managment Studio and Object Explorer, you can explore Tables. But unlike Enterprise Manager, it doesn't seem as though you can right-click on the table and query it and sort it. My biggest problem is how can you sort a table within Object Explorer in SQL Server Management Stu...more >>

I have just been handed the role of DBA and, while I think I'm pretty solid on SQL security within the domain (I'm a Windows app developer), the web side of things is fairly new ground for me. I would really appreciate some help. We have an IIS 6 server in our DMZ running web sites and web se...more >>

Hi, We have a database with very few primary keys defined. In order to implement transactional replication with updateable subscribers we need to define primary keys on all tables. I need some advise as to whether it's better to use natural primary keys or introduce artificial identity colu...more >>

We have a simple problem: We have a table Assets[asset_id, title] and a table Asset_Versions[asset_id, version_no]. In addition, there are columns added by replication for storing the ROWGUID. After synchronzation, assume both Node1 and Node2 have Assets(1,'XYZ') and Assets_Versions(1,1) Bef...more >>

I am using the coalesce function in sql server to combine the values in a table column into a single comma-separated string. If I write the code like this, it works perfectly... DECLARE @OrdersList varchar(1000) SELECT @OrdersList = COALESCE(@OrdersList + ', ', '') + order_type ...more >>

Hi , I just finished a VS.Net program wich uses 85 stored procedures \ it works fine however not at user level , this is due to Access rights i was so stupid to forget to grant Public execute rights to these stored procedures there are three servers with these 85 stored procs my...more >>

Is there any way to declare your own global variables unique for session in sql server? regards Kjell Brandes...more >>

Dear group, I am having trouble updating a column (in SQL 2000) of the type 'text' from another table. For example, let's say I have two tables: tblDest with columns: IDNum as integer primary key, TextDest as text == ======= 1 OldData1 2 OldData2 3 OldData3 4 OldData4 tblSrc wi...more >>

Hello, Just wondering how you can execute a batch file from a stored procedure. Can I find out what the syntax is assuming the batch file sits locally on the SQL Server? Thanks! Regards, -Alf...more >>

Hi all, I am searching for a way to notify my application that a 'change' has happened in my DB .. i.e. insert happens, trigger fires, slicing and dicing then tell my app things have happened. I assume this is not an uncommon sort of thing to want to do rather than have an app poll th...more >>

Hi All, We have a unique problem at hand. We have a large table with close to 200 fileds(all types int, bool, character). Now there are lots of records(more than 100000). Each record has two uniq fileds (DOCID, ITEMID). Now DOCID's representats the Document ID which hosts all the relevant ...more >>