I have a table that is used primarily for credit card transaction processing where the date is stored in a text column in the format MM/YY. The credit card processing software will not accept it any other way, there must always be two digits for the months and two for the year. Instead of sto...more >>

Hi, The following sql command fails: select job_no,job.udf1,cast(job.udf1 as decimal ) from job where IsNumeric(job.udf1)=1 Because if job.udf1 contains '.' or ',' then IsNumeric(job.udf1) is equal with 1 but cast(job.udf1 as decimal ) returns error. Obviously the following commad al...more >>

I want to create a Stored Procedure which will extract some data, and if a particular condition exists within that data, I want to sent an email to someone within the procedure. Can this be done? Thanks in advance for your assistance!!! ...more >>

I am converting access database to Sql. In the access database the CLng function is used to convert a field to an int. There is some junk data in the field which has characters like '/' or apha characters. My problem is that in access this CLng command igores that data but when I use the c...more >>

Hi, I thought this was going to be straight forward (I avoid using the word easy!) maybe its because its late at night, but I just can't get my head around this :- What I would like to do is monitor the size of tables in my database on a daily basis. I would like to have a stored proc whic...more >>

I know how to stop SQL through SQLDMO, e.g., create sqldmo.sqlserver object object.name = "myserver" object.stop() But how do I stop Agent? Can anyone show a specific example? Thanks! ...more >>

Hello, I was wanting to take the putput of a System Stored Proc, such as SP_Who and take certain information off and build a table. How would I go about this? Thanks in advance for your assitance!!!!!! ...more >>

I have two tables, Tasks and Machines. The tasks table looks like this: create table Tasks ( TaskNumber integer, TaskName varchar(100) ) The Machines table looks like this: create table Machines ( MachineId integer identity, IsDown bit ) The number of rows in the T...more >>

Create Table Tempdb.dbo.Tester ( TesterId int Identity (1, 1) Not Null , Tester varchar(5) ) On [Primary] then from dos, isql /U sa /P /q "exit(insert tempdb.dbo.tester (tester) values('test2') select scope_identity())" how to get this identity into a variable in a batch file, without...more >>

I have a web application running in ASP.net/c# which should support multi languages. I have set the column data type as nvarchar, ntext respectively. While executing insert/update queries do i need to encode and save the data or just the normal insert/update is fine. sample queries are a...more >>

I am converting an access report to SQL Server 2000 and I am having problems converting the IIF statement used in acces to work in SQL The statement is Sum (IIf (REQ.PRICE_QTY = 0 ),0,([QUANTITY]*[PRICE]/ [PRICE_QTY])) AS [Value] Can anyone help ...more >>

hi i have a table with an image data type how do i store images in it? currently i am doing it thru a program but how do i do this using sql statements? thanx ...more >>

Hi, I want to monitor locking levels in real-time. I know I can see the locks using sp_lock, but was hoping to use something more real-time like Profiler. I checked the Profiler documentation but did not see anything that would seem to return the lock type used when excuting a statement. A...more >>

Hi all, I'm trying to use partitioned views to improve performance. We have tables having 'type' column distinguishing objects to several kinds, e.g. 'Discrepancy' may be of type Price or Quantity. table 'discrepancy' ( id int identity primary key, disc_type int, .... ) As there are (cu...more >>

Hi, If Assign ASP.NET User as db_owner to the login user, can possible the login user do some bad thing to my db system? Thanks... sp_addrolemember 'db_owner', <ASP.NET User Account>" ...more >>

when will the trigger cannot fired ...more >>

Hi all I'm using a linked server with ADSI to retrive user information from an Active Directory. My query below is working fine but i'm not able to exclude users that have a disabled account. I strongly suspect :-) that "DisabledAccount" in my where clause is wrong but i can't find the correct...more >>

hello, is there a way to select rows with distinct values in a particular column? something similar to the DISTINCT keyword but for a subset of columns? i have a table: (item1, description1) (item1, description2) (item2, description3) (item3, description4) i would like to select: ...more >>

Any suggestions? Thanks....more >>

Hi, The following SQL command: select job_no,job.udf1,cast(job.udf1 as decimal ) from job where IsNumeric(job.udf1)=1 Sql Server returns some rows and then breaks and Returns the following error: Server: Msg 8114, Level 16, State 5, Line 1 Error converting data type varchar to numeric...more >>

How can I edit sysusers.status columns ? should i use system SP to do this job I want to update sysusers set status=2 where name='alfashi' to lock a login account can you help me, thanks a lot ...more >>

I am attempting to use RETAINDAYS on my differential backups. I put a RETAINDAYS = 6. However, I now have 10 days worth of differential backups. BACKUP DATABASE Matrix TO DISK = 'D:\SQLBackup\Matrix_Diff.BAK' WITH DIFFERENTIAL, RETAINDAYS = 6 Am I missing something? How can I keep my...more >>

How can I ignore the time for a datatime field in a where clause? If I say where getdate()mydatetimefield this will fail due to the time element of getdate--sometmies. I just want all things with todays date reguardless of time. How can I do this? Thanks, Shane ...more >>

Hello: I have two tables, ELEM and ELEM_GROUP. When I insert a row into ELEM_GROUP and supply for Elem_ID a value that does not exist in the parent table, ELEM, I get a referential integrity violation error that says: [Microsoft] [ODBC SQL Server][SQL Server]INSERT Statement conflic...more >>

Hi, I new to SQL, I have managed to create a piece of program in SQL Query that runs fine. I need to convert it to a Stored procedure so I can EXECUTE it from another script. As VARIABLEs I want to send the Table name (since I perform the same task for multiple tables), so that in the d...more >>

Is there a way to loop through each record in a table without using cursor? What I'm trying to do is to get the records out of a table one at a time then do some parsing and modification on the fileds, then insert the records into other two tables. Thanks. Matt -- Posted via...more >>

hi I ahve problems with full text search. Im running Sql2k developer edition on win xp. I want to delete a full text cataolog. Enterprise manager wont let me, saying "The full text service must be running in order to delete a catalog" However it already is running, it appears in the...more >>

I am trying to change the format of a date befire I send it to a report. The date looks like this; 12/25/2003 12:00:00 I would like it to say 12/03 12AM or PM on whatever the time is. Thanks ...more >>

Hi, Is there any way to determine a stored procedure/table is system defined or user? OR I want to list all user defined stored procedures from a database. thanx Megan...more >>

Is there a way to select all records where the text in FieldA is in lower case? Thanks. ...more >>

Hi Could anyone please tell me why the first SQL statement is executeded within milliseconds while the second SQL takes about 30 seconds? SQL 1: SELECT * FROM errWarn WHERE eventtype = 16 AND ShownToUser = 0 Order by EventTime SQL 2: DECLARE @EType smallint; Set @EType = 16 selec...more >>

Is this a bug? Trying to create 2 web tasks of the same name in 2 different databases yields "Cannot insert duplicate key row in object 'mswebtasks' with unique index 'web_idxproc'." Shouldn't the web tasks be database specific? USE Northwind GO CREATE TABLE DBO.WEB_TASK_TEST (PKID INT IDE...more >>

getdate cannot be used. Is there any way except pass a datetime parameter Thanks...more >>

Hello: I have two tables, ELEM and ELEM_GROUP. When I run the DDL, the second constraint on ELEM_GROUP errors out. Can somebody tell me how I can overcome this problem? Essentially, I want to delete any row in ELEM_GROUP when ever a row gets deleted in ELEM which is either a parent ...more >>

I have been beating my head against the wall for the entire day, I need some help I are creating an accounting program that prints checks. Checks from many clients are inserted into a single table - 'checks'. There is a field named 'checknumber', this is the number that is printed on each ...more >>

I've got Update & Insert triggers firing data cross-server -- call them Server1 and Server2 -- data is inserted/updated into Server1, triggered across to Server2. I need to insure that Server1 writes data into the table being inserted/updated, even if for some reason Server2 is unavailable. I've...more >>

Hi NG, I'm making my application ready for distribution and I search for a way to create tables and table-content by a install-script (ASP.Net) I found out, that SQL Query analyzer can generate CREATE TABLE code, but I have a lot of table content I have to distribute also. Is there a tool t...more >>

CREATE TABLE [dbo].[account] ( [Company] [varchar] (50) NOT NULL , [salesrep] [int] NOT NULL , [repmanager] [int] NOT NULL ) ON [PRIMARY] GO insert into account values ('ABC Company', 1,3) insert into account values ('Microsoft', 2, 3) insert inot account values ('Oracle', 3,4) Go ...more >>

Hi, I am trying to alter a table with about 1000000 records adding a numeric field with a default value of 0. Here is the alter table sql. alter table test1 add test_col NUMERIC (15,2) NOT NULL DEFAULT 0 This results in the transaction log being filled up and query fails with a disk full error...more >>

Hi, I call a stored proc in vb client application that bulk inserts several large files into the database. Can you raise events or otherwise monitor the progress of a stored proc and/or bulk insert? Since the whole bulk insert can take over 5 minutes, it would be nice to show the progress to...more >>

Hi All, I got that idea from "Unlocking Active Server Pages" which I'm enjoying a lot. It's an old edition but it's working for me. It provided a script that included the statement: Create Table dbo.Events ( ... It defaulted to the Master database. I have two questions about this....more >>

Hello Can Decimal values be defaulted to a NULL value? I seem to be getting a 0.00 everytime instead of a null string. ...more >>

What is the sequence of making query in SELECT * FROM Product pp inner join CategoryXProduct pc on pc.ProductID = pp.ProductID inner join dbo.TN_Category cc on cc.CategoryID = pc.CategoryID INNER JOIN FREETEXTTABLE(Product, FullIndexField, @FullIndexField) AS kt ON pp.Prod...more >>

Hi! I have table with [EntryDate] field in it. I have problem creating where clause with selection for last 30 days starting from today's day. Any help appreciated. Thanks ...more >>

We have a table of registrations (regs) that is used to track all registrations to various events for individuals. We have a front end that allows viewing and altering what events each person is registered for. When we "Save" from that front end the program 1st deletes all entries in the regs ta...more >>

Hello all, I've got an occasional error (Error converting data type varchar to numeric) occurring in a stored procedure on what looks like to be valid numeric data. What's even stranger is if I copy the contents of a "suspect" record to a new record, the SP works fine - however, merely typing...more >>

Hi all, I would like to schedule a nightly DBCC CheckAlloc on 10 different company Databases. Do I setup a job in EM with 10 steps in it Each step would read: DBCC CHECKALLOC ('DBName') I would also like to schedule a weekly run of DBCC CHeckDB Would I set that up the same way? T...more >>

Hi all, I'm trying to insert some rows based on a query. At the moment I have.... INSERT INTO umc_messageTransactions SELECT @mUID, DistID, FROM hbitln.Distributors WHERE ( DistID IN (SELECT distid ...more >>

I have to build a table that uses the date in it as part of the where clause. Before getting into the guts of the stored procedure, I have the following: declare @bdate varchar(12) set @bdate = datepart(mm,getdate()) where I set the variable @bdate to be the month prior to the one I'm cu...more >>

How can I convert my column "notahosa" to Time Format in this String? SELECT notahosa FROM DBO.notas ------------------------------------------------- Marcus Vinícius M. Montezano Programador Digiexpress ------------------------------------------------- --- E-mail verificado ...more >>

I have been trying to find duplicates in a table that have records where the Date and Time range conflict. I wrote an SQL below but is not working. Any suggestions on how to accomplish this? I have a and event_start_date, event_end_date, start (time), end (time). ----------------------------...more >>

I'm studying for the 70-229, and a question in MS 70-229 readiness review is about configuring and optimzieing the system for best throughput... System contains two RAID controllers with three channels on each controller.... The correct answer contains this text: "When you create the database...more >>

Error: "Disallowed implicit conversion from data type varchar to data type money, table 'EVARTE.dbo.NotaProdutos', column 'NoPrValo'. Use the CONVERT function to run this query." I'm tried to insert a record in Table NotaProdutos.. and it show me this error message .... what does it says?? ...more >>

Hi There, I'm having a problem configuring the SQL Agent Proxy account so I can use xp_cmdshell for non-sysadmin users. I've tried using the extended stored procedure xp_sqlagent_proxy_account and I've also tried changing it in EM in the SQL Server Agent properties in the Job System tab...more >>

I had a discussion regarding database design with the network guy here, and he claimed that if I normalized the data more and used views it would be a bad thing as views are "hogs". (This all started because I was going to do a join to get a two byte field from another table, and my discussion ...more >>

is there a way to programmatically turn off a foreign key restraint before an insert, then turn back on after? tia jt...more >>

I have created a trace file, split it in 3 files of 669 Kb, 9478 Kb, and 6557 Kb. The frist one, ran succesfully giving me an improvements of 61 %. The other 2 files I was not able to analized in ITW, giving me the following error message: "The workload does not contain any events or queries th...more >>

I have created a trace file, split it in 3 files of 669 Kb, 9478 Kb, and 6557 Kb. The frist one, ran succesfully giving me an improvements of 61 %. The other 2 files I was not able to analized in ITW, giving me the following error message: "The workload does not contain any events or queries th...more >>

I have a hierarchical table as the following shows (DDL is at the end of this post). How should i remove the 3rd node, the one with Val = 3? Since it is referenced by the 4th node, and the 4th node is referenced by the 5th, 6th, and 7th node, and so on, simply deleting the 3rd node will resul...more >>

Please help me fix the following syntax. I want to update tk_unpumpable in table fm_tank with tankunpumpable in the view vw_tankcorpid where tk_prft_ctr = stationID and tk_num = tankID. The syntax below produced multiple values according to the SQLserver error message (which is not true). Am I...more >>

Hello... An algorithm drops a tempTable named CurrentUser + 'UnitHistory'. It then re-creates it with the requested history for the unit selected. My problem is that as users are added, if their is no table created with that name.... the Drop table part of the stored proc fails. My q...more >>

I am loading data using bulkinsert into a table with single column. The size of datafile is in 10s of MG just wondering whether there is a smarter way to add computed columns based on the first row. This is just a csv data file. There can be other separators used. TABLE RAW_COLUMN ---------...more >>

Hello, I have a table in a SQL 2000 database that will contain demographic data, and test answers scanned in from a scantron form. Some of the data may be missing and some may be inaccurate (wrong code provided on form). In any event I need to perform some validation on the data in this table ...more >>

Hello everyone I hope somebody can answer the following doubt: Suppose I have a table with an image data type field (let its name be 'img') and the image 'c:\picture.jpg' stored in my harddrive, How do I upload this image into the field of my table? Can I do it from the query analizer, ...more >>

Does anyone know how to convert a varchar field [DOB] having datetime values like 19540219 (YYYYMMDD) be converted into a generic SQL Datetime field to enable a basic query manager to query upon? Can this be done during importing the flat file into SQL using DTS manager or do I just impor...more >>

Is there any method to Debug a SQL Server Stored Procedure ? ...more >>

Hi, I am writing a report (Crystal Report 8) which uses a sub report. The data for this sub report comes from a stored procedure that needs a parameter. This parameter depends on the account being displayed in the main report. I can't find a way to set this parameter for the sub report. I alr...more >>

Hi All Is that true if I do not you "sp_" with my store procedure then there will be profit in speed because all store procedure those name start with "sp_" will be first search in master database first Mr Bug ...more >>

We are building an application that will need to access a SQL Server that is on a different domain than the application's IIS server. What will we need to do to enable this application to connect to the SQL server and what method of authentication do you recommend? We are considering using an appl...more >>

Anyone help? I get the following error in the below script Server: Msg 137, Level 15, State 2, Line 2 Must declare the variable '@adm_fax'. I'm trying to pass values from a cursor to my dynamic sql My variable @sqlstrupdate= Update wphb_person SET wpape_fax=@adm_fax,wpape_main_fax=@ad...more >>

I'm writing a user function which will select the Nth row in a recordset. The only two ways I can see how to do that are to create cursor and loop through N times, or to grab the top N rows, reverse the sort order and grab the top row. Still seems kludgy. Is there anyway to limit the SELECT ...more >>

Hi, =20 I need to monitor a table containing actions users have to perform at = specific points of time. Please imagine the following table: =20 UserID | Action | to_be_done_till -------------------------------------- 1 | do this | 10/31/03 2 | do that | 11/...more >>

Hi, =20 I need to monitor a table containing actions users have to perform at = specific points of time. Please imagine the following table: =20 UserID | Action | to_be_done_till -------------------------------------- 1 | do this | 10/31/03 2 | do that | 11/...more >>

OK... granted this should be simple... maybe I just haven't had enough coffee this morning... I have a table with a Identity column as the key.... I want to be able to select a single record: EmployeeNumber = 1234 or all the Records EmployeeNumber = % Of course, % does not return al...more >>

Hi, I have a sql server script which calls few stored procedures and return success/failure code. I use an NT batch job to run this script by ISQL utility. Is there any way to capture the exit status in maestro, and determine the failure/success of the job. I would appreciate your resp...more >>

Thanks. Follow the article, I have built my stored procedure. Now I run into a weird problem: Using SQL analyzer, the stored procedure works just fine. But when I call it from my C# application, the first and the last elements in array get lost. E.g. if my input string is 'a,b,c,d', Only b ...more >>

New to database handling. My firm has an intranet running on IIS and sql server at the backend. The sql server stores all people who have access to the intranet. There is an HR package software running on some other m/c where all the firm's employees are stored. I have been asked to write an hr ...more >>

hi i try to check with "server.ExecuteWithResults("sp_helpreplicationdboption 'TEST1' ")" while server is an sqldmo object. howto get the results from the stored procedure now?? please help thx gretts peter...more >>

I am trying to use obtain a user2 object in SQLDMO while looping through a Users collection, but I run across a type mismatch each time because the Users colleciton is returning a User object. I read through the BOL piece (Programming Extended SQL-DMO Objects) about being "precise" in the...more >>

Hi All, I just read a script reportedly generated by Enterprise Manager for (re)creating a database table "Events" that began with: if exists( select * from sysobjects where id = object_id('dbo.Events') and sysstat & 0xF = 3) drop table dbo.Events ) Apparently if the low-order fo...more >>

Is it possible to catch the result set of the command RESTORE LABELONLY FROM MyTape into a table or cursor? I want to check in a stored procedure, whether the current loaded tape (MediaName) is the right one. Thanks....more >>

I am trying to change the Identity property on a table column programmatically (SQLDMO or through T-SQL). SQLDMO does not seem to allow these type of modifications and I cant find any T-SQL that will allow this either. Any ideas?...more >>

I have a stored procedure (call it SPA) that uses an Insert...Exec command that calls a stored procedure (call it SP1). SP1 in turn uses another Insert...Exec command to call another stored procedure (call it SP2). When I try to run SPA, I get the following error message: Server: Msg 8164, ...more >>

Hi All, Could you please point me in right direction as I want to take sqlserver exam as part of my mcsd .net course. Any books,link ,sample questions,tips in this regard would be highly appreciable. Many thanx Nav ...more >>

I have a trigger for insert and i need to make some controls on inserted data. I need to assign the result of a query to a variable named @DOCVALIDI (integer) for make my controls and my query (@QRY) must be composed using 2 variables. The variables are named @DOCUMENTTYPE and @CONTROLCODE and ...more >>

tentaively, got: create #table(a,b,c,d) insert into #table select A,B,C,D from s,t,x,y,z where y.price > 20 select *, (select max(D) from (select distinct top 2 with ties from #temp v where v.B=W.B and v.C = W.c), (select min(D) from (select distinct top 2 with ties from #temp v where ...more >>

What is the syntax for creating a unique constraint on the combination of several columns eg. UNIQUE( COL1 + COL2)?? ...more >>