I have an old app that used sql-dmo to get some data about sql databases. I need to port some of that old code to a new c#3.0 app and was wondering if there was something new is sql2005 that replaces the old sql-dmo libraries. Thanks. -- moondaddy@noemail.noemail ...more >>

DELETE FROM PlayerRevisionScores WHERE RevisionID IN (SELECT RevisionID FROM Revision WHERE RevisionDate < (SELECT RevisionDate FROM Revision WHERE RevisionID = @nRev) AND ClubID = @nClubID) Revision Table - Primary Key is RevisionID, an Integer. Player Revision Sc...more >>

I haven't had to do much funky SQL in a couple years now, mostly just straightforward stored procedures for use in .NET code. However, I have a query that is giving me fits or I am just having one of those days. I will not share my multitude of attempts so I do not get you thinking along th...more >>

Hi We have a table that is growing very rapidly in size and I am wondering what is going on with it. Here is its definition (names changed to protect proprietary info) CREATE TABLE [MyTable] ( [Column1] [uniqueidentifier] NULL , [Column2] [uniqueidentifier] NOT NULL , [Column3] [date...more >>

Hi All, I have recently restored a database from my live server to my development server. On the development server, I have to qualify the database name for queries to work. e.g. Query: Select * from customer Result: Invalid object name 'customer'. Query: Select * from data...more >>

Hi, I have a client who has used the ROWGUID in 2000 with a NewID() in the default section. It worked fine in 2000 and creatd the ROWGUID value for them but after having moved to 2005 this no longer seems to function. Any ideas? Shawn ...more >>

I have some data in sql2005 and I want to access them with sql 2000 with linked server or similar. Is that possible? regards,Simon ...more >>

whats the difference between deadlock, locking and blocking thanks...more >>

CREATE TABLE [dbo].[Category]( [CatID] [varchar](20), [Category] [varchar](50) NULL, [CatType] [varchar](5), [Amount] [float] NULL ) ON [PRIMARY] insert into dbo.Category values('12345', 'C1', 'A', 2.55) insert into dbo.Category values('12345', 'C1', 'A', 3.22) insert into dbo.Ca...more >>

Hi All, I know the 'Concatenating row values' question has been asked a million times, however I have not seen how to make a slight adjustment to the results. If I run the query below on Northwind, rows 6 and 7 have trailing delimiters. Usually I would handle this in the asp.net code bu...more >>

Hi, Data access in our apps is not very significant and although we've been developing apps for many years we are not that strong on the dba side. We have recently switched to SQL Server Express from another DBMS. One of our potential customers insists on role based security. Because we ...more >>

I thought I had mentioned this before, but... It would be GREAT if we could change a user-defined type even when columns in tables are using that type. In fact, it seems more likely to want to change a user-defined type when lots of columns are defined using that type. It really detract...more >>

I have various files that I get from clients that I send through a custom "scrubbing" program. I want to upgrade the program to import the data straight into my sql server instead of using an interim format (dbf/text/csv/etc). The program is written in C++ and could easily be ported to C# to t...more >>

Hi, I have two table, Customers and CustomerOrders. Customers has columns like CustID, email, firstName, lastName, etc...where CustID is the primary key for each Customer. CustomerOrders has the Order info for each customer, so if a customer bought 5 times it would have 5 records, CustomerOrd...more >>

I need to alter a table in many instances of the same database - many clients use the same structure of the database. I wrote the script: ALTER TABLE [dbo].[TakeOffSummaryItem] ADD [ListPrice] [money] NULL , [DirectCommissions] [money] NULL , CONSTRAINT [DF_TakeOffSummaryItem_ListPrice] ...more >>

I have an integer in a table and it has to be converted into a varchar with 2 digit format showing the leading zero if there is one. I can't get the "style" parameter of the CONVERT function to work. Basically this is what I want: CONVERT(varchar(2), SomeInt)) + 'ABC' gives me for example 8...more >>

I am using sql server 2000 and would like to return only the top 3 values for each group from the data below. Is there anyway to do this? If the square feet is the same, as in loannumber 1, I want to return either 'verizon' or 'home depot', it makes no difference which one. CREATE TABL...more >>

Trying to convert a Date that is represented like this :: 'Feb 19 2006' I tried the following..........................if you see my UPDATE query....this is where I am having an issue CONVERT the @PriTBS_SinceDate to the correct value to be inserted into my sinceDate field inside the orgtbP...more >>

hey all, I have the following table in which i need to get a count, percentage, average salary. EmpID,Salary,Salary Date,Gender 1,1.00,1/1/07,M 1,2.00,3/1/07,M 2,1.00,3/1/07,F 3,1.00,3/1/07,M The problem i have is the employee that has 2 salary records. i only need the most recent sal...more >>

Lets say I have a number of web servers that all feed data to a common SQL Server database, which processes and sends to a master SQL Server. I am looking at a solution that involves Service Broker at the higher levels and that all works fine. My question is: what is the recommended way to get...more >>

I want to enable AWE on my production box. Currently we have active/ passive 2 nodes. both of them are running at 8 GB of ram and quad processors with windows 2000 advance server our database is 2 TB but as per talk with Microsoft support team they said there is a lot of memory pressure on thi...more >>

I'm using SQL 2000 sp4, on a windows 2000 sp4 server. I'm trying to find a way to write a query that will return all database names on an instance, along with what recovery model is being used for that database (ie simple, full, or bulk-logged). Does anyone know an easy way to get this wit...more >>

Hello, I'm working on a component that takes streaming data and writes it to a database. Each item in the stream has a key, and multiple items per key will be seen. They must then be inserted/updated in a particular SQL Server 2005 table. The schema of items is fixed and maps to the tabl...more >>

Hello, I am trying to use the SqlDependency object to maintain a current dataset on my client code. However, whenever I try to implement it, the event fires with the following Arguments in the SqlNotificationEventArgs object: e.Info = Invalid e.Source = Statement e.Type = Subscribe T...more >>

Is there any way to run an INSERT query into a SQLCE Database and if a unique index (constraint) or primary key is broken to ignore the query? create table ingredient ( id int primary key, name varchar(80) ) insert into ingredient(1, "Green Beans"); insert into ingredient(2, "Salt");...more >>

SQL Server 2005 & Express All, What is a best practice for generating snapshots in my merge replication topology? In my topology subscriptions will be coming and going over time. There will be @150 users with new employees coming online over time. Some of the users share hardware and s...more >>

I know Visual Studio Team Database Pro can import a schema from a database, but can it do it from a Visio database diagram? Or must I first export the scripts out of Visio, run them against a blank database, and then import the schema from the database into Team Database? That sounds a bit ...more >>

Hi i have the following stored procedure : Basically i select staff availability from an availabilty table v_availability_all where it fits the criteria specified below which works fine. However if a staff member is already booked this is NOT taken into consideration. I would like this to be t...more >>

I have a table with a date_created and date_expired and I want to look at the 15th of each month and every month since the system started and count up how many accounts were active at each of those points in time. Will have to assume that if there's an expiry date bigger than today then that ac...more >>

hey all, if i had a number like the following: 0.28571 how do i round to .29? thanks, rodchar...more >>

Hello, I have a table with 3 columns: "Title", "Description" and "Html" I want to select all records but renaming the columns to: "PostTitle", "PostDescription" and "PostHtml" How can I do this? Thanks. Miguel ...more >>

Hello, I have two tables with the following columns: Posts > [PostId][AuthorId][PostTitle], [PostHtml] and [PostDate] Users > [UserId][UserName][UserEmail] [AuthorId] in Posts is the FK which "linked" to PK [UserId] in Users. I need to create two SELECT codes: 1. Get a POST from POSTS w...more >>

I need to retrieve the third row - my recordset is ordered by a column Col10 - that is after the row that matches my condition. How do I do this without cursor? Thanks, Carly ...more >>

I would greatly appreciate help with the following scenerio. This problem was posted once before and the solution offered below was close but not quite right. This is using sql 2000 I have 2 example tables here: create table tblloans ( loannumber int primary key ) insert into tbllo...more >>

Hi All I have constructed the following query from Sql 2005 help sample. Pivot tables are new to me. Essentially, I have data the looks like this: BDMName DateCollected ActiveMembers ------------------------------------------------------------- ----------------------- ------------- ...more >>

Hi all, For security reasons, we need to drop all the extended stored procedures from the master database. Does this causes any malfunctioning in SQL server 2K? Any comments is appreciated. Roy...more >>

Hello All Here are my requirements: get the latest (period) value (recordable) for an existing indicator. Here is some DDL and sample data. I hope that you guys can create the table and I tried to create some sample data hope you dont have many problems. CREATE TABLE [#i] ( [Title] ...more >>

I have this function that will try to count ' ', (spaces). Now if I loop through the string, it tells me that at 6 and 12 I have a ' ',(space). How can I group the result and then count it, so that I know I have (2) spaces in my string. It would allow me to place it in a UDF. thanks ...more >>

I have just set up Database Mail on my development system. I'd like to know early on whether the following requirement could be met using this. The system that I am developing allows users to create quotes that need to be able to be emailed to customers. The return address of the email will ...more >>

how to design capacity planning and estimate database objects space? is it depends n daily transaction? thanks Farrukh...more >>

Hello, I was wondering if there is any constant that defines the min range of a DATETIME type in SQL server. I know it's: 1753-01-01 00:00:00.000 or -53690.00000 but I prefer using a defined system constant (if any). I am looking for a way to set a new DATETIME variable using system functi...more >>

I've encountered a problem where a database context switch fails ( ie USE statement ) and scripts are ploughing on. What's a good way to prevent this from happening? Thanks wBob Example, with multiple batches: USE master GO -- Do something in master ... USE _otherdatabase GO ...more >>

Can someone tell me options to do this statment because this one does not work! SELECT Name FROM tbl_J WHERE J_ID IN (SELECT J1, J2, J3, J4, J5, J6 FROM tbl_CJ WHERE CJ_ID =23515) ORDER BY Name ...more >>

hi, i had a question on how to possibly select two things, not in the same query. 1. We have data in a few columns of bit type, we enter in either 1 for yes or 0 for no, is there anyway to do a select so that rather than getting a 1 or 0 back the query returns a Y or N? 2. We also need to ...more >>

Hi all, I've been tasked to develop a system to pick live prices up from various sources (various APIs) and store them in a database (SQL server 2005). All good so far - but as new prices come in (which can be anywhere between once per second and once per hour on illiquid days) I al...more >>

DECLARE @COMMAND VARCHAR(5000) DECLARE @Day VARCHAR(5) Set @Day = 'Day01' SET @COMMAND = 'ftp open 192.168.253.9 ftpuser ftpuser prompt off cd Report\Day01\ABC\ mput \Data\'+ @Day + '\*.txt bye' --print @command EXEC [MASTER]..XP_CMDSHELL @command why I cant execute this F...more >>

Hi I populating a report in a VB app by running numerous queries and, potentially, creating/deleting numerous temporary tables. I pass the following SQL to ADO and onto the sql server db SELECT d1.Field1,d1.Field2,d1.Field3,d1.Field4 INTO #MyDataTable3 FROM (MyDataTable d1 INNER JOI...more >>

Hello there I use sql server 2000 SP4. Is there a way on query anlyzer to make the action of generating sql script so i can get some of the script for me? i need it to get script of creating references in order to build store procedure that removes all the references and recreate them a...more >>

Everyday I have to go to a website https://www.fededirectory.frb.org/fpddir.txt and save the page as a text file to a directory on our network. Is there a way I can create a DTS package or just an easy job to go to that page save it as a text file and store it in a set network location? T...more >>

How can I call web service from SQL server, any example or url? Thanks, S ...more >>

Hello there I would like to build simple and fast sql that return list of all the tables in my database and how many records are in each. it should look like this: TableName RecordCount Customer 1000 Employee 54 FutureUser 534 ...more >>

Hi I was wondering, is it possible to have a parameter prompt, to ask the user to supply a month and year parameter, to used in the execution of a DTS package? Kind regards Ricky (WIN2K / SS2K) ...more >>