Hi I am relatively green at this whole MS SQL stuff, I came across some code that does this bit of magic: SELECT Count(*) AS CountResult FROM [tbl_Page_Views] WHERE FORMAT(Date_Time,'w') = '1' given that Date_Time is a field of type datetime I also need the format codes for day, week, ...more >>

Can anybody suggest way to delete a row in the master table that has foreign key on other table? I used the delete statement but it takes lots of time. Thanks...more >>

Hi - i'm trying to create a script which ensures primary key's and indexes are placed on an existing database. I'm finding that if i use the following, it creates the Primary key and the index IF a primary key doesnt already exist. If a PK does exist the constraint is not applied. How can i ...more >>

I need to import data from an as400 database, what is the best way to do this? Regards, Sean Gahan ...more >>

Hi folks, I have been tasked with opening up an old product to make a modification. It runs on top of a MS SQL Server 4.2a database. Here's what I'm trying to do, and although I believe I know how I would it with cursors in a more contemporary version of SQL, I'm stymied as to how I can do it...more >>

I am new to the SQL world and programming in general. I am working on a database that at the beginning of each month, I have to update one table that stores fiscal dates in the format yyyymm. Is there any way to automatically update that table using getdate? Can this even be accomplished with the da...more >>

I am using something like the following and am having trouble controlling the format of the output The select * returns one column of information however, I do not understand why there is a large space after the output of each row. I have modified the width variable from low to high and do not see...more >>

The following SQL will find rows in my primary key table that are not referenced in the foreign key table. It seems sluggish on large tables. Is there something more efficient SELECT * FROM PriKeyTable WHER MyKey NOT IN (SELECT DISTINCT MyKey FROM ForKeyTable ...more >>

Sorry, I think this is a basic question. What is wrong with the following statement? select * from (exec sp_helpdb) I appreciate your help. Tomtom. ...more >>

I detached a db, can I still insert data into it? or I must attached it again then do insert? ...more >>

Hi all I need a single report that brings to me a summarized of all my items in invoice. The itmms have several caracteristis thay I get from related tables ie Character Table Table 1: CAR TRUCK MOTERCICL Table 2: PETITE SUV UTITLITAR Table 3: ALL-TERRAIN SPORT VAN FAMILIA Table 4: RE...more >>

Hi all I need a single report that brings to me a summarized of all my items in invoice. The itmms have several caracteristis thay I get from related tables ie Character Table Table 1: CAR TRUCK MOTERCICL Table 2: PETITE SUV UTITLITAR Table 3: ALL-TERRAIN SPORT VAN FAMILIA Table 4: RE...more >>

ive been using sp_helprole to return all roles for a database but is it possible to return only the roles that have been added by a user, and not the existing system roles such as public, db_owner etc? Cheers, Craig ...more >>

Hi all I have 2 tables ,(t1,t2) i want to update specific column in t2 only if a field in t1 match the value of a field in t2, how i can do that? if t1 is a view, it will be same procedure? thanks in advance. Samir R. Ibrahim ...more >>

Hey all, How do I change the Column Name or Heading of the column returned with a COMPUTE BY Statement. This line is this last statement from my query but it returns two Grand Total Columns both named "sum". How do I change the column headings to GT_Sales and GT_Units?? COMPUTE SUM(I...more >>

SQL 2000 & vb.net Since .net is designed as a "disconnected architecture", is it possible to use application roles? My understanding of application roles is that they are set for the duration of the connection. If a .net program is disconnecting after every db call, you would loose the ...more >>

If you are trying to insert rows in a table from another table and you use syntax like: INSERT INTO SODetail (SOID, PODetailID, ItemID, MarkAs, Quantity, UOMCode, PurchaseRate, CreateUserID, CreateDate) SELECT subSOID, PODetailID, ItemID, MarkAs, Quantity, UOMCode, PurchaseRate, subCreateUser...more >>

I have a table - "members with columns contactI FirstNam LastNam Emai The data i ContactID FirstName LastName Emai -------------------------------------------------- 4 john smith john@conway.co 5 j. smith ...more >>

One dba saw blocking of users when a table with an insert/update/delete trigger was firing. They describe it as Tab Sch-S on the trigger table. I have a trigger I want to use on another table and their experience with this one has them shy about triggers. I can't see anything specificall...more >>

Hi, I've been trying to track down why this one sproc is very slow lately. I've reduced the query down considerably to find what the problem may be, so it may look a little strange. The Planner table has about 85000 rows in it now and has non-clustered indexes on the emp_id, week_of and pl...more >>

On two of my servers, when I execute exec master.dbo.xp_enum_oledb_providers I get the message: "No such interface supported". Any thoughts? Brian ...more >>

Hi, I have a problem concatenate Uniqueidentifers. I weant to get all SalesRepIDs in one string which I can pass then to another sp. Here is my stored procedure to do this: CREATE PROCEDURE usr_COLLECT_SalesRepIDs AS DECLARE @TmpID UNIQUEIDENTIFIER DECLARE @CollectedSalesRepIDs NVARCH...more >>

I saw long ago, a piece of code written in SQL that verified whether a given credit card number was valid for that card type. Has anyone got an example of this? Thanks for any help.. For those who may not know - for each credit card type (Visa, MC etc) the 16 numbers must adhere to a spec...more >>

Hello, In SQL 2000 when you open a Stored procedure, it is opening in a small screen which cannot be maximised. Is there a way to see the SP in maximised screen? (I hadn't experienced this in SQL 7. ) Any setting to be done? regards Sathian ...more >>

Hi, all. We need to be able to do a SELECT to get all the dependencies of a particular table. We found an undocumented sp, sp_MStablerefs, but it only returns a few dependencies. When we right-click the table to be renamed, under All Tasks > Display Dependencies, we can see a ton of stored proc's...more >>

I would like to execute dbcc sqlperf(logspace) against a remote sql server. How would i do this I have setup the remote server as a linked server but do not know the syntax to do this help?...more >>

How do I drop a Column constraint which is a Default? in SQL 2K This is how the defaults are defined: ALTER TABLE [dbo].[T91_omm_matter] WITH NOCHECK ADD CONSTRAINT [DF__omm_matter_mt_processed] DEFAULT ('N') FOR [mt_processed]...more >>

Hi I have a SQL SERVER Table :- create table resumetest ( filename varchar(200), thefile TEXT ) I'm trying to read all the rows and convert to text files, using this code ...snip... con.Open() da.Fill(ds, "resumetest") Dim myRow As DataRow myRow = ds.Tables("resumetest")...more >>

Hi Can anyone show me how to save the output from a FOR XML sproc to a file using T-SQL? Is there a system sproc I need to use? I would like to avoid xp_cmdshell, but will use it if necessar Thank John...more >>

------------------------------------------- create table #temp_joe (cid int, measure char(50)) select MAX(cid), MAX(measure) from #temp_joe WHERE cid IS NOT NULL and measure IS NOT NULL drop table #temp_joe ------------------------------------- right now, it returns one row: NULL, NUL...more >>

About two weeks ago I had an accident and have broken my left elbow and left wrist. For doing things like Word or e-mail (I use Outlook for) I have been using Microsoft's speech recognition and that has been working fine. However as a professional programmer I need to be able to type. Right now...more >>

I am trying to create a stored procedure that involves dates. The dataset will run on the data range queried from the database unless the user says no, I want to designate these specific dates. Therefore, I've created a parameter called Defaultdate char(1). A simple yes or no. I want to cre...more >>

Hi all, I have a string sometime Str1= '2' or str1='2,20' or str1 = '3,45,98'. The max is four numbers. I want to extraction this string into integer variable: If I have str1 = '2,20' then I want myInt1 = 2, and myInt2 = 20 If I have str1 = '3,45,98' then I want myint1 = 3 , myint2 ...more >>

Is it possible to create a function in this way? create function dbo.udf_world returns smalldatetime as begin set dateformat ydm -- error!!! end Is there a workaround to use this set command in an UDF or an alternative way to do this? Thx -- Fabrizio Maccarrone -- ...more >>

I am entering data into two tables. The second has a many-to-one relationship to the first. Both tables utilize an Identity Column for an artificial key. The current way I store data is to enter the row in the first table, get the identity value, and then enter associated rows into second tab...more >>

I am a learner at writing triggers and have come across a problem and cannot uderstand why it happens. I have a trigger when a unitinstock field changes if it falls below 1 then a date is inserted into another field 'StockZeroDate ' in table. if it rises above 0 then the date field is set back t...more >>

Hi I'm trying to make a store procedure that will count the number of rows in a table. I want to make it a generic procedure that can be used on any table by passing two parameters (the table name, and the name of the column to count). I've tried this code: \\\ CREATE PROCEDURE _spCoun...more >>

Hi all and thanks in advance, I am getting an error when i call a stored procedure in a cursor loop. I don't understand why this is causing an error or whether I am doing something wrong. if anyone can give me any suggestions I would highly appreciate it. This is the code of what I am doing...more >>

Hi, I've a user who has all permissions (select; insert; update;delete) to database1. Within table1 I've defined a delete trigger which will check of the deleted ID occurs in another database (let's say table1 in database2 on the same server). This is my code in the delete trigger (in table1...more >>

They just implemented a server farm and my stored procedure that uses = @@identity no longer works.Here's a sample from books online of what I = am doing from a code point of view.INSERT INTO jobs = (job_desc,min_lvl,max_lvl) VALUES ('Accountant',12,125) SELECT @@IDENTITY AS 'Identity'There are...more >>

Is it possible to assign on a column an UDF that I create that is based on another column value? Any help appreciated. Regards -- Fabrizio Maccarrone -- YAMSSQLU (Yet Another MSSQL User) ...more >>

I frequently come across a small problem with my stored procedures; there are plenty of way around it, but I'm figuring that maybe my approach is subtly wrong. My typical code to call an Stored Proc in ASP is as follows sSQL = "Exec MySP 'xxx', 111, 'yyy', 222" oConn.Execute sSQL Usually...more >>

I have the following existing trigger that updateds i rw at a time and need to redesign it to be able to update several rows when a Insert or update statement is executed. The old trigger: CREATE TRIGGER PctComp ON dbo.audComplianceStatus FOR INSERT, UPDATE AS Declare @SID int Declar...more >>

Hi, I have 3 databases that are currently located on the same server. I need for all of them to communicate with one another. I am using the three part name [database.owner.object]. The joins and data seem to be returned perfectly. What type of problems does this create, if any? Also, I...more >>

I am trying to help out my poor users. When they are blocked they do know this, and eventualy the query times out if the block is not released. I want to execute the query with async and then query sysprocesses to check if the first query is blocked. I use a seperate connection to check if the first...more >>

Hi guys, I'm finding that replication between the two SQL Server 2000 boxes we have explodes when we alter any of the tables in the database being replicated. Is there a hint or procedure to ensure doing something like: ALTER TABLE [dbo].[public_users] ADD date_registered DATETIME GO ...more >>

I need some help writing a select statement that returns a numeric value for each ID in the table. The conditions are as follows The COUNT of Status where the values is = 1 bit datatype. The COUNT of the PID int datatype, of all rows for each ID this currently = 109 for each ID but can chan...more >>

Is there anything worng with the following functin? When I call this function, it always return NULL. ---------------------------------------------------------------------------- ------------------------------------------------------ CREATE FUNCTION fn_GetProcessgroupAndGeneGroupString (@proce...more >>

I have a table that has the following, APP_NO -- application number FirstName LastName Sin Birthdate I want to select distinct firstname,lastname,birthdate from the table but at the same time bring back all the rest of the columns for those records that are unique based on my distin...more >>

Hi I have a table that I want to create a Foreign Key constraint on This column has NULL values I want to create the Foreign Key with a CASCADE DELETE NO ACTION I have done this through the script below as I am not sure if this can be done through the GUI in Enterprise Manager. I CAN create ...more >>

Can anyone shed some light on this? We're having a problem one large-ish (300,000 record) lookup table running on 2000 sp3a. This table data is static and is refreshed overnight although it is used heavily (read-only) during the day. The problem is that we seem to have intermittant but pers...more >>

Dear all, I have created a CmdExec job step that call cscript.exe to run a WSF script. The task is owned by a user not in the SysAdmin group. When I start the job, it fails with something like this: CScript error: Unable to load configuration. (Access denied) It works if I change its own...more >>

Hi, I removed the "builtin\administrators" user account from the sa server roles. However, I can't remove the "builtin\administrators" user account from the database. This is the error I get when trying to do so. 'database owner cannot be drop' Any advice??? ...more >>

I read on this newsgroup that a debugger exists for the analyzer. BOL explains the toolbar and commands and that's all. The debugger is nowhere to be found in the analyzer's interface. Did I miss selecting this as an option when I installed SQL Any help would be appreciated....more >>

Hi All, Here's what I'm doing: We have a system running on SQL 2000. Client systems need to access the data (via webservice). However they need to be notified of changes to the data for some critical areas. I want to use a MSMQ to store all changes to critical areas and have a service that ru...more >>

Could someone recommend a query designer capable of CASE? thanks, Vadim...more >>

I have an assembly written using VB.Net that I need to call from a SQL Server 2000 stored procedure using sp_OACreate. The assembly has been registered for COM Interop using regsvcs.exe and installed into Component Services in a Server application I have seen discussion threads that talk about whe...more >>

Hi there I have a large dataset that currently stores dates as text in the format mmm-dd-yyyy. The problem I'm having is that I can't query ranges as the dates are not stored as datetime. I also get an arithmetic overflow when I try to convert to datetime datatype. Does anyone have a suggestion...more >>

Hello how is possible write something such as declare @tab varchar(50 set @tab='temp_05222004_110045 drop table @ta Thank for every help...more >>

Hi, I'm using Access 2002 with Sql 2000. In a query I'm trying to concatenate the following: Lname + N', ' + Fname + N', ' + MidInital The problem I'm having is if someone doesn't have a middle initial. If they don't have a MI the whole field is empty. All the other data that pertains...more >>

It is possible to set a time that the SQL Server executes a checkpoint ?? Thank's in advance...more >>

Hi, I have the following statement: ALTER TABLE SomeTable ADD SomeDate datetime NOT NULL DEFAULT ('01 Jan 2000') I am trying to add a column to a table and set the default values of the column. However I would like to be able to specify a condition for the default values. Is it possible?...more >>

Dear all This bloody query not works, I mean, @cont variable not increment and I need in every loop raise 1, Why declare @cont as int, @cont2 as in set @cont = (select max(sinWholeSalerGroupItemsID) from tblwholesalersgroupitems set @cont2 = @cont + (select count(*) from tblwholesalers where s...more >>

im using the following query to generate a recordset that shows if a clinet has a report waiting to be processd. Select t1.Licence, t1.CompanyName, t1.Software , Case When Exists (Select * from dbo.Addacs as t3 Where t3.Licence = t1.licence and t3.CBIDispatchedDate Is NULL) Then Cast(1 as Bit)El...more >>

hi, I'm searching for an C++ sample how to fill an OUTPUT cursor parameter from an extended procedure to return a resultset which can be fetched. T-SQL sample: DECLARE @CrsrVar CURSOR exec xp_my_ext_procedure @OutCrsr = @CrsrVar OUTPUT FETCH NEXT FROM @CrsrVar ..... CLOSE @CrsrVar DE...more >>

Hello Experts, Is it safe to execute the stored procedure sp_refreshview? Thanks in advance!...more >>

Hi there, I'm writing an application that calls a number of updates to a SQL Server database, wrapped in an ADO.NET transaction. One of the stored procedures implements T-SQL transactions - what i was wondering is if the T-SQL encounters an error and ROLLBACK TRANSACTION is called, will the f...more >>

SQL "SELECT" (via Ado.net) gives results in sorted order by default. IOW, "ORDER BY" is turned on by default (seems to use the primary key). I'd like to retrieve rows in the order that I originally inserted them. Is there any way to turn off the default sort? VJ PS: I've hex-dumped the da...more >>