Can anyone point me to any resources on how to programmatically add databases to maintenance plans through SMO on SQL 2005 preferably using VB? Thanks! Brad ...more >>

Hi, We have checksum function in sql server 2000,i would like to implement same in .Net.Can some one provide checksum algorithm. Thanks ManDev. ...more >>

There is MSDN article "Using XML templates" at http://msdn.microsoft.com/library/en-us/xmlsql/ac_xml1_2cx1.asp. Does there exist formally defined XML schema for XML templates?...more >>

Hi, I was profiling the Audit Schema Object Access Event in SQL Server 2005, and when I executed the system stored procedure "sys.xp_msver" I found that the database id of the this procedure is shown as "32767" which doesn't exists in the sys.databases view ? Any idea to what database it ...more >>

Hi, Given a table EmpeeTrans with the following structure CompanyID char(1) EmployeeNumber varchar(16) WeekNo int Code varchar(20) Hours decimal(18,4) Rate decimal(18,4) Sample data 4,1234,1,"BASIC",40.25,12.3456 4,1234,1,"T+1/2",7.50,20.1234 4,1234,2,"BASIC",40.25,12.3456 4,1234,...more >>

We are changing our numbers from 7 digits to 5 digits and what to copy the data from one field to the other while triming off the last two digits. We have determined that we no longer need the last two digits. Both fields are in the same table. Can I do this with a SQL statement? Please see...more >>

http://www.sqlmag.com/Article/ArticleID/93462/sql_server_93462.html Cheers, -- BG, SQL Server MVP www.SolidQualityLearning.com www.insidetsql.com Anything written in this message represents my view, my own view, and nothing but my view (WITH SCHEMABINDING), so help me my T-SQL code. ...more >>

In a stored procedure, I have the following TSQL statement: EXECUTE ('Insert Into Federation.dbo.FederationData (FederationData) Values (?)', @Buffer) AT [HostBox.abcd.mydomain.com\SQLServer2005,1563]; This works fine. However, I would like the linked server name to be determined at run-ti...more >>

Hi; I am trying to execute a DTS package from within a vb.net module using VS 2003. I understand that you must add a reference to a com library. I tried using a reference to Microsoft.DTSPackage.Object Library Then doing " Dim oPackage As Object = CreateObject("DTS.PAckage2") I m...more >>

Dear all, We get the following error *periodically* on our site... Server: Msg 8623, Level 16, State 1, Line 8 Internal Query Processor Error: The query processor could not produce a query plan. Contact your primary support provider for more information. When it occurs it happens all the ...more >>

Im trying to access the system tables for SQL 2005. I understand I can no longer access the tables directly, and I’m fine with that. But I cannot see the System Procedures folder or System Databases folder that several sites talk about in order to get to all the processes MS created for read...more >>

I have a table that records temperatures every minute or so. There is a datetime (Sample_Date) that stores the time a temperature was read and a float (Sample_Value) that stores the temperature. I'm trying to write an UPDATE statement to calculate the average of the previously 5 minutes wo...more >>

I have two tables with a one to many relationship existing between them. I want to be able to select in a Flag in my result set that indicates that the "one" record has one or more records pointing to it (excuse my lack of relational terminology), I mean related to it. My solution is a left ...more >>

Hello. Does anyone have a script or point me to a link that shows how to create a script that can use existing Windows active directory accounts to be set up as new Windows authentication logins for SQL Server 2000? We have a rather large list of active directory accounts that need to be set...more >>

Isn't there a special function in SQL Server similar to DB2 where I can extract rows based on "CURRENT DATE"??? I have this query... SELECT column_1, column_2, column_3,... FROM TABLE_NAME WHERE column1 <= CURRENT DATE Any help is GREATLY appreciated... Th...more >>

I need to format monetary values to s9(6)v99 format with leading zeroes. For example: 20.00 -> 002000 20.56 -> 002056 Negative values will have a minus sign -20.00 -> -02000 -20.56 -> -02056 Is there any easy way to do this? Any pointers would help. Thanks in advance. ...more >>

Hi All, I have a Master Stored Procedure, which internally calls other procedures as needed. This master proc is invoked from a Web Application (ASPX). Sporadically I get error messages Error Detail: System.Data.SqlClient.SqlException: Transaction (Process ID 60) was deadlocked on lock resources...more >>

I need to execute CHECKPOINT command for a specific database in a stored procedure. The [USE] statement is not allowed in proc: use database_name checkpoint What are my alternatives?...more >>

When using dynamic SQL in SQL Server 2000, how do I confirm that my SQL statement executed successfully? I have my statement in var @sql, and execute it with: EXEC @ck = sp_executesql @sql N'[...parm defintion list and values list...] but I think var @ck will be non-0 only if sp_executes...more >>

I have a tsql query of this form SELECT ... FROM a JOIN b ON ... JOIN c ON ... LEFT JOIN d ON ... WHERE ... I see that the query optimizer has decided to do full outer joins for the first two joins. My interpretation of the documentation is that these should really be inner joins....more >>

Hello, I wonder if there is a quick way or a simple sql to accomplish the following: I have a lump sum of money which spread thru' the start date and end date, and eventually the money spread evenly in 4 quarters, e.g. Jan-Mar =Q1, Apr-Jun=Q2, July-Sep = Q3, Oct-Dec = Q4 For instance, if...more >>

I've having some trouble creating a stored procedure that conents to a linked server. I'm connecting to our IBM AS400 and I have to change the date from 09/01/2006 to 20060901. If I run the query below as is, it working without a problem. However, I want to create a stored procedure so I ca...more >>

The Database Design ROI White Paper is intended to help data modelers (regardless of their title) justify the cost and time of developing an excellent design even if it delays the project. http://www.sqlserverbible.com/files/databasedesignroi.pdf -Paul Nielsen SQL Server MVP PASS Board o...more >>

UPDATE Table1 AS t1 LEFT JOIN Table2 AS t2 ON (t1.BrID = t2.BrID) AND (t1.SID = t2.SID) SET t1.NAME = [T2].[NAME], t1.QID= [t2].[QID] gives Incorrect syntax near the keyword 'AS'. ...more >>

hey all, how do i create a script that will save the structure as well as the records? thanks, rodchar...more >>

How do I write some SQL which gives the result set for the records that are returned by one query and not returned by another query? Here are my 2 queries : 1)select distinct ups.SerialNumber, ups.Division, ups.Family, ups.Product, ups.ContractStart, ...more >>

This is the issue I am facing: I have a stored proc something similar to this: CREATE PROC spGetApplesAndMangoes @units int AS Declare @dynSQL nvarchar(500) Declare @apples int, @mangoes int -- contruct this dynamic sql statement using conditions etc. ...more >>

Hi, I have a table like this: TableA -------- RECORD_ID (primary key) Field1 Field2 Field3 Field4 Field5 In this table there are many duplicate records. (it's mean all FiledX values same [except RECORD_ID]) Example: RECORD_ID, Field1, Field2, Field3, Field4, Field5 -----------...more >>

I am current re designing one of our databases for SQL 2005. This database will have about 20 tables. Of those 3 will have over 4 million records and at least one more will have over 20 million records. I was looking at using partitioning, but as we use replication across half a dozen serve...more >>

Hello, I have a stored proc.. that is giving me problems.. seems straight forward??? '-----' CREATE Proc InsertEmailNewIStudy @IStitle varchar(150), @ISfacmember varchar(150), @IScontactinfo varchar(150), @ISexpiration datetime, @ISdescription varchar(6000) AS '-----------' ...more >>

Hi, Can any one recommend a tool (Free lisc, preferred) to compare SQL Schema (and may be data)? Thanks...more >>

Hi All, I am faced with a situation where i want to know the ID or name of a trigger that fires another trigger. For example say i have two tables namely tblSource and tblDestination. Now i have say one trigger(called trg_updateDest) for updating the tblDestination and assume that this trigge...more >>

I have a small table in SQL 2000 that has < 5,000 records. All of a sudden, I cannot run most queries against it...always get a time out. This is a CRITICAL production table, and I need to get this fixed ASAP. I only have two indexes on the table (on varchar columns), neither of which I can dr...more >>

I have the oportunity over the next couple of days to alter an aggregate table which we're using to report from to include a couple of primary keys from the original tables. These keys are integer identity columns. There will almost certainly never be a reason to join on these columns in pa...more >>

Hi, I'm trying to connect to a sql server 2000 box using c but cannot find a library for it, any ideas the best way to connect to it? Cheers c19 ...more >>

I compiled a sp using "EXECUTE AS" with ad-hoc user created to perform ddl operations and added permissions of db_ddladmin. So, I'm trying to execute a sp from a low-level permissions user impersonating the new one, db_ddladmin assigned role user. When I try to query something, I obtain "The...more >>

Hello , I am trying to Implement distribution of the Stock Quotes over the LAN(only within the Network) and showing the live changing stock Quotes on the front end (in datagrid) installed at each clients desktop.I am receiving the Stock prices over the TCP / IP from the Stock Exchange. I am reci...more >>

There is one stored proc 1. Inside that we begin one transaction and in that We call another stored proc 2 Inside that we begin another transaction and committed it Now in stored proc 1 we rollback first transaction Will this rollback, reverts the changes made by transaction in stored...more >>

[VS 2005, SQL Svr 2000] Hello all, I have a SQL 2000 database with 4 tables (A,B,C,D). I'm writing a program that will manipulate the 4 tables in the database. I'm planning on using disconnected ADO.NET. Each table will need a different set of SqlCommands used by a DataAdapter. I'm new ...more >>

Sql 2005: I have to query ALL views or tables from a database this way: select * from information_schema.views What I'm noticing is that I'm trying to connect to db using a low-permission user and when I query this views I obtain only the objects owned by this user. Is there a way to read...more >>

I want to keep all the value of a field in my table to the Upper Case, i.e. even if the user keyed 'abc', it will be stored as 'ABC'. I tried using UPPER(fieldvalue) in my update statement, but still the value is stored as lower case. What can I do?...more >>

how to convert MSACCESS query SELECT IIF(True,Null,'' ) as Dummyfield from yourtable to sqlserver syntax. ...more >>

I have an Inventory Application where Receipt and Issued of Quantity are entered, while Only Receipt Value is entered, how can I show the Average Value of Issued Item. Say my Sample Data is : CREATE TABLE test ( PRITC varchar(10) NOT NULL, QTY numeric(10,3) NOT NULL, RATE numeric(18,4)...more >>