Hi all, SQL 2000 I am cracking up in work with what appeared to be a simple task. I have a script in work which returns a data set concerning duplicate data. This is by design and a third party then takes the results and goes of and decides using their head which duplicates should be del...more >>

SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE TABLE [dbo].[CANDCRITERIAMATCHTYPE]( [TYPE] [int] NULL, [CAND] [int] NULL, [MATCH] [char](1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL DEFAULT ('V') ) ON [PRIMARY] ...more >>

select isdate(2000) if im excuting return 1 whether it is correct or not actullay it is not valid date ...more >>

Hi folks, Can I store more than 8 K row in a table in SQL 2000. If yes, does it affect performance or anything. Thanks in advance. ----- *** Sent via Developersdex http://www.developersdex.com ***...more >>

I have a VB program that uses an ODBC connection to load data from a flat file into the database. The VB program is invoked on server"A" and the database is on server "B" it uses the ODBC connection from server "A" to connect to "B". I'm not starting to see a few records a day that get dropped...more >>

Hi, I have a query which become slower as the number of item in the temp table increase. Here's my code: CREATE table #regions ( ipFloat float null, countryId int null, ) insert into #regions (ipFloat) select distinct top 100 ipFloat from tbl_logs -- This is where the code gets...more >>

I am writing a simple installation program to deploy a database by using SMO. Here are the tasks in the installation (1) if db exist, delete it first (2) add server logins and change server settings (3) restore the db for deployment I am using TransactionScope to do the transaction control so ...more >>

Hi, I want to capture the Create, Alter and Drop Events of all the database objects, and in addition to that I have other events too. I was trying to use the "Audit Object Derived Permission Event" event for capturing the Create, Alter, Drop of Tables, SP's and Views. But it doesnt capture th...more >>

Hello, Is there a way to merge 10-15 similar tables that have most of their columns identical, but some have more then others and columns are in different order? I have them exported in Excel worksheet if you would like me to send it to you. Thanks! -- Deki PA ...more >>

Using both SQL Server 2000 & 2005. Given: Column "ID" is the primary key in both table "A" and table "B". Some of the values in table "A" are in table "B", therefore, some are not. I want to delete from table "B" the rows that have an ID that is also in table "A". How do I write that query...more >>

I'm having problems calling my second proc (MyProcedure2) from within an existing proc. MyProcedure2 does not seeem to fire this way. My code is below. Help appreciated. Thanks, Trevor ALTER procedure dbo.MyProcedure1 (@newsletterid int) AS SET NOCOUNT ON -- Return Subscribers declare ...more >>

Hello there I have some quries that do distinct part of some data I'm wondering in this case what is better: select distinct ... from ... or select .... from group by ... ...more >>

Hi, I am wondering why the output is so different given the following two scripts: 1. return 2 rows select S.Fee, P.* from TestProcess P inner join ServiceItem S on P.ItemID=S.ItemID where S.TestNo='0611CAT065' AND S.ItemID='31101004' 2. return 45 rows select S.Fee, P.* from Test...more >>

I'm using OPENXML in SQL Server 2000, with the following code, the line "SET @strXML" needs to get an XML doc from a web address, How can I load in the the remote XML page , to then allow the remaining code to process: CREATE PROCEDURE dbo.TestOpenXML( @strXML VARCHAR(2000) ) AS SET @...more >>

Hi, I have a table called outcomes with 2 columns (win, date). Code and data below. I need to find the maximum number of consecutive wins (win=1), and the maximum number of consecutive losses (win=0) ordered by date. Thanks in advance for any help. Bob create table outcomes (win bit,...more >>

I am trying to create a cursor with a simple select on a table. Easy enough but in this case I do not know the table name at design time. Example: DECLARE @tbl_name varchar(40) DECLARE @curr_name varchar(100) DECLARE @sql varchar(1000) SELECT convert( varchar, @@SPID) SET @tbl_name ...more >>

this is in reference to sql server 2005- what is the recommeded column datatype to select when storing a time field that is in the format HH:MM:SS? i don't want to use datetime because my data does not use the date portion- just the time. tia jt ...more >>

Saying that - a trigger may not be the best solution. I have a project database that has various tables related to the main 'Project' table. In that table I want to store the most recent update date/time for many of the tables in the database. In other words I want this update datetime to ...more >>

Hi, i'm trying to write a simple parameter query in SQL-SERVER 2005 I want to return the number of orders that belong to a particular company. the user is prompted to enter the name of the company he wants to check: select count(order_id) as [Number of orders] from Orders where company...more >>

Is ther a function is SQL Server to convert Null values to "0" or some other specified value, similar to MS-Access? ...more >>

I'm baffled. I have a column in a SQL Server Express database called "Longitude," which is a float. When I view the table in a DataGridView, some of the numbers, which only have two decimal places in the database show up with *15* decimal places and are ever so slightly off (in the example in th...more >>

I'm baffled. I have a column in a SQL Server Express database called "Longitude," which is a float. When I view the table in a DataGridView, some of the numbers, which only have two decimal places in the database show up with *15* decimal places and are ever so slightly off (in the example in th...more >>

Hi all, Is it possiable to join or union to results sets from 2 exec statements of the same Stored procedure? Will not no the column counts untill stored procedure is Exec thanks gv ...more >>

Hello, I have a stored procedure that gets it's data from a View in SQL server: Select "WO Number", "Part Number", "Complete Date", "WO Quantity" >From [AWO_VIEW] --Select the View Where [Work Center] Like @WorkCenter Order By [Complete Date] ASC I have code in my client app that retur...more >>

Hi all, first, here is the VB6 code I am using to set up a Parameter. As you can see, depending on the number of "Criteria" gets looped, then each "sParmName" (or "@spec_type1", "@spec_type2, etc...) will get created. Dim spec_ct As Long spec_ct = 0 Dim str_type As String ...more >>

Let's say that there are 3 tables: A, B, and C defined as follows: A.AID (PK) A.Col1 A.Col2 B.BID (PK) B.Col1 B.Col2 C.AID (FK) C.BID (FK) Table C is references primary keys in table A and B respectively. There is a cascade-delete constrain on C.AID. What would be the best way t...more >>

Is there a way to use xp_cmdshell (or any other method) to execute an SSIS package residing on a SQL Server 2005 from a stored procedure on a SQL Server 2000 database? I used to have a procedure that used a COPY SQL Server Objects tasks to copy data from tables from a source (SQL Server 2000) ...more >>

Hi i have table named mytab creat table mytab(custno char(4),custname varchar(30),pno char(10)) go i have aproximately 10000 rows in it now i have added another column to the table custseq. alter table mytab add custseq int how can i populate sequence no starting for 1000 onwar...more >>

I have this function to calculate the ISO week, which works jsut great, but I wanted to modify it to give me the ISOYear for any given date passed to it. Ive been trying to understand it and there are some things which I cant understand a) The significance of the number -53690 b.) aa.Ja...more >>

Hi, I have a table with several columns. Index #1) There is a NON-clustered, unique, primary key on "ID" column. Index #2) There is a clustered, unique index on the "CustID, ID" columns I am not suggesting that there are the "optimal indexes", however... but... The scenario: When I ...more >>

Hi how can i add a counter to my result set e.g counter name 1000 ali 1001 hira 1002 maira 1003 samia .. .. .. .. regards jami *** Sent via Developersdex http://www.developersdex.com ***...more >>

Hi, In BOL it is mentioned that we can control the access of users to file system resources by using impersonation (WindowsIdentity.Impersonate). Imagine I have a SP that creates a file on disk but the user sends the path as a parameter. Although I can control the user to access only to parti...more >>

Hello, I need to have script to run to give me a list of Backup jobs and there schedule on a SQL Server. can you help Thanks in advance, Omran ...more >>

I recently changed my audit level to record failed attempts to authenticate. Everyday I am getting following log "Login failed for user 'sa'" about 30 times a day. Looking at the time, it's very erratic, so i am pretty sure that someone is trying to hack into the system. I know for sure that ...more >>

Hi, I'm trying to write a query on a SQL 2000 Server. What I want is to combine the results of one select query with that of another returning all rows from both tables. Here is what I tried and it mostly works: SELECT pt.patient_id, CAST(RTRIM(pt.last_name) + ', ' + RTRIM(pt.first_na...more >>

Hi! i have two tables tab1 and tab2 create table tab1 (Val as int) create table tab2 (Val2 as int) insert into tab1 values(500) insert into tab1 values(800) insert into tab1 values(1500) insert into tab1 values(2300) insert into tab2 values(22300) insert into tab2 values(2200) insert i...more >>

Hi I am using .Net 2005 (VB.Net) and sql server 2000. I have a stored procedure in which we pass a number of records to be updated. Considering this to be the best option, we are using XML as an interface between the front end and the stored procedure. But the challenge we are facing it that ...more >>

I was making a copy of a database with DTS and during the process, all data from the Source database got emptied. Anyone know of this happening before and possibly why? Thanks, Trint ...more >>

What's the easiest way to get multiple server logs in one place so I can monitor them? Thanks ...more >>

Hi! i have following script Create table mytab (pname varchar(30),fname varchar(30), sname varchar(30)) go insert into mytab values ('Jhon',NULL,'Simmone') insert into mytab values ('Terrif',NULL,'Jane') insert into mytab values ('Recoh','David',NULL) insert into mytab values ('Kirsten...more >>

Hi all, is there a way to avoid the inheritation of the model database settings when using a "CREATE DATABASE ..." - script to generate a database on customers sql server? I want to protect the structure of our database, which otherwise could be "contamined" by customer specific model db se...more >>

Hi, I have some SQL statements which I am using in SQL Server 2000 and they are using both the DatePart and the Cast functions for example: CAST(DATEPART(yyyy,IssueDateTime) as varchar) However, I must make sure this works with other database sources as well e.g. Oracle, Microsoft Excel and old...more >>