I have a table defined as : CREATE TABLE [dbo].[job_13_event] ( [id] [int] IDENTITY (1, 1) NOT NULL , [recipient_id] [int] NULL , [event_type] [int] NULL , [mail_type] [int] NULL , ) ON [PRIMARY] There is about 5 million rows in this table; 1,063,337 have event_type=3; When I run ...more >>

Hi, I've created a view that uses a CASE statement to set the value of a field depending on the value of another field. The view is created ok however I need the view to be updateable and this isn't the case at the moment. Is there another way to approach this problem so that I do get an u...more >>

Hello, I have a table with following data in it: Company_name | Date | Old_company_ID | New_company_ID ---------------------------------------------------------------------------- ---------------------------------------- A |2001-12-20 ...more >>

Hi This is probably pretty basic, but what do I need to include in my Visual C++ STDAFX.H file in order to use the dbserverenum function? My compiler doesn't recognize the function. I am using it to look on a network for an SQL server instance so I can update a database. Thanks for any help Alla...more >>

I'm moving my VB application from Access to SQL Server and have some problems. I have Sheridan Grids on several screens. They are working in unbound mode, which means I create an updatable recordset with SQL statement in VB. Sheridan Grid has special events such as UnboundDataRead, UnboundDataWr...more >>

I am reading up on doing a TSQL Backup. I have some questions. My log file will keep growing if not truncated I have read. If I do a full database backup does this backup the database and the log or just the database? If just the database then I assume to get a good backup I should Do ...more >>

Hi, I have a query that needs to sum selective wors out of a table (which is linked to two others for the query). When I select just the value that I am going to sum (SELECT Value FROM...) I get three results and it takes about 3 seconds. When I ask for the sum (SELECT sum(Value) FROM...)...more >>

Below is a line of code in my view that does not work. Can anyone help? For some reason, when the firstname is null it does not work and I get a blank field. Customer = CASE COALESCE(dbo.Customers.LastName, '') WHEN '' THEN dbo.Customers.CompanyName ELSE dbo.Customers.LastName + ', ' + dbo.C...more >>

Hi All, Can any one tell me how many column can be accomodated inside a Table of SQL Server 2003. In SQL Server 200 we could add 256 columns in a Table. What about SQL Server 2003.?? Hoping to hear frm you Thanks Vikash ...more >>

Hi all, I am building queries joining data from many tables (5, 6 and more) As I am afraid of performance consequences of joining so many tables together (and so much data for some tables), I would like to know what kind of policy I should follow when I join tables together. Usually most of...more >>

H I am wanting to construct a query to retrieve a list of tables and views that a user and it's group have access to. This need to include default group-level permissions, such as ddl_admin, etc for instance. I have a user "A" who is part of Group "XYZ". I want to retrieve a list of views and ta...more >>

Hi all, Maybe you can help me with my problem concerning a column with datatype money: When I use Cognos ReportNet to retrieve the data from Column_A (datatype: money) all values are ALWAYS displayed as xxx.00 For example: the value stored in the database is: 17.95 (. is the decimal sign) then...more >>

I have data that I need to strip out the dollar sign and parenthesis. The parenthesis are indicating a negative number. A B C $2.78 $11.20 ($8.42) $2.78 $11.20 ($8.42) $2.78 $11.20 ($8.42) $3.58 $11.20 ($7.62) My Select statement: ...more >>

I have a question about a transaction that I have. Here is part of it: begin transaction declare @hstId int declare @curId int declare @newId int select @curId=max(id) from hdrData select @hstId=max(id) from histhdrData if @curId > @hstId select @newId = @curId + 1 ...more >>

this question seems basic and trivial but i'm stumpe code snipped from UDF... declare @rtnval in set @rtnval = if @pm < getdate() set @rtnval = when attempting to create the UDF i get the following error "Invalid use of 'getdate' within a function why the only thing i want to do is t...more >>

this seems like a very basic and trivial thing but, i could use some assistance code snippet within a function..... declare @rtnval in set @rtnval = if @pm < getdate() set @rtnval = when creating the function i get the following error. Why Invalid use of 'getdate' within a function ...more >>

this is how my syntax looks like: SELECT distinct(Field1) as Div, sum(case when dt between '08-Mar-2004' and '14-Mar-2004' then '1' else '0' end) as A, sum(case when when dt between '01-Mar-2004' and '31-Mar- 2004' then '1' else '0' end) as A from test group by Field1 output: DIV ...more >>

Is it possible to create a view in SQL Server which incorporates tables from an Oracle database? It can be a ODBC connection, I don't care much about speed etc. Just is it possible? :) Any tips/suggestions/links to more information etc. would be great! Thanks. Sincerely, Nils Magnus Engl...more >>

Hi all. Forgive my ignorance here, but I haven't had to deal with this before. I recently encountered a process blocking two others and want to understand how it happenened so I can stop it happening again. I believe it was caused by a select statement in a trigger, but if so I would like to...more >>

Hello all, I am having a problem in a Stored Procedure that is using exec master..xp_cmdshell 'dir /b c:\temp\*' (this directory path does exist on my machine). When I executed this on the Query Analyzer (logged in under the sa login) I get: The system cannot find the file specified. NUL...more >>

Hi, I'm looking for a way to check the results of multiple unique constraints in one action. Consider the following table: create table t1 ( f_id int identity(1,1) not null, f_code char(10) not null constraint code_unique UNIQUE, f_desc varchar(50) not null constraint desc_unique U...more >>

I'm wondering if there is a way to base a constraint on a condition in another table. For example: CREATE TABLE Orders ( OrderNumber int, RepID int ) CREATE TABLE Reps ( RepID int, CreditApproved char(1) CONSTRAINT CK_CreditApproved CHECK (CreditApproved = 'Y' OR CreditApproved = ...more >>

Do CHECK constraint names have to be unique within the table or within the database?...more >>

I have a bit of an issue with my usually excellent isp. I have 4 web sites all with an underlying sql db which have worked perfectly for 2 years until the sites were moved to new servers (Win2003), and sql was upgraded from SQL7 to SQL2000. One site mostly remains up (which holds the SQL accou...more >>

I appreciate your guys ideas from earlier. But if I wanted to delete rows from a table in blocks of 1000 to avoid long locking/ deadlock issues, would this work for me? while (select count(*) from Table1 where ColumnDate > '2- 29-2004') > 0 begin set rowcount 1000 delete Table1 where C...more >>

How do I go about updating a field with below value Amalio juan Alonso sebastian to this: Amalio Juan Alonso Sebastian Thanks.. -Melounjan ...more >>

Hi, Can anyone tell me how to make sqlqueries case sensitive. let me give u a example. Suppose i want to check for name and last name in a table. I want check for name 'SuRaj' not 'suraj' and last name 'oak' not 'Oak' So if passed query is select * from emplist where name ='suraj' and las...more >>

Hi, I have SQL Server 2000 running on one box and IIS running on another box. I am connecting to the SQL Server via ASP pages on the IIS box. My concern is security -- currently I am using this kind of code to connect: var setListings = Server.CreateObject("ADODB.Recordset")...more >>

I have a query that will perform poorly from time to time. The query plan itself changes. The main culprit appears to be a column in my table called CHECKSW which is an integer with a default of zero and can only zero or one. It is not part of any index but is used in WHERE clauses. When...more >>

Are there any physical size limits on a DB using 2000 standard edtion? As there are on exchange 2000 standard editon (16 GIGS)...more >>

I always try to write my code as portable as possible. Even though the queries I'm wirting are intended for a SQL Server 2000 environment, I always keep in mind that there might one day be a need to move to another platform. Therefore, I try to limit myself to using only ANSI-compliant queries. ...more >>

When I syncronize my tables, I get this error: Cannot insert duplicate key row in object 'POHFIL' with unique index 'IPOHFIL0'. (Source: CMTSQL2 (Data source); Error number: 2601) What do I do? the help was not much. I am trying to replicate tables between two SQL servers. Thanks for an...more >>

Hi All, I have questions related to ANSI Join Style (See query 1.1 and 1.2) -What is the performance difference in ANSI Join Style and MSSQL Join Style? -How both join internally works? (See query 2.1 and 2.2) -What is the performance difference in ANSI Join Sty...more >>

I am really need your help... How to pass a date with format "yyyy-mm-dd" to a stored procedure? I have tried many method 1. the data type of the date_variable in stored proc have been set to "DATETIME" using parameter object of ADODB.command to password a string with a type adDBTimeSta...more >>

I'm using Classic ASP with SQL Server to read in XML data. If I return about 5 records everything seems fine but returning 10 records, the XML seems to cut off towards the end. Is this a problem people have come across before and if so how can I correct it? Code and sample output below AS...more >>

Greetings, I have received a request from my client where they'd like me to allow THEM to add their own fields on the fly, and have the column dynamically available for use right then and there. I've been developing for decades and I haven't quite heard of such a request but I am aware that...more >>

How do I get the Monday and Sunday of the current week? Thanks, Edgard ...more >>

Hi all I like to know the syntax to start a job on server2 from within a job in server1 Thanks in advance Ray ...more >>

Which is the better structure if I have 100,000 parts? My users need to be able to change the description of the part type if need be. I have intentionally left out the part description and other part information for simplicity of this example. There will not be over 255 part types. If so ...more >>

I have a table from which I want my users to get information that is applicable to them but nothing else. I just tried this on my test server. I denied my test user all permissions to the table. I wrote a proc that has one parameter that will limit the data selected to the data for that pe...more >>

Hi i use sql2000 in win 2000 server with Mdac 2.8 i'm connecting with provider : sqloledb from Asp pages, thru ADO calling stored procedures the question is that time to time there is an error 800a01fb in : 1) set cmd.activeConnection= cnx either in 2) cmd.execute then, doing a SP_WHO ...more >>

I have a procedure that update records if the values are different. Is there a function that I can use to find out how many records have been updated and store that number into a variable? Thanks, Bruce...more >>

Probably an easy solution but I can't seem to get it. I have numbers from 1 to 12. I would like to display the 3 character month abbreviation. I have tried DATENAME but it always returns Jan. Could anyone help?...more >>

Hi all! I'm working on a meeting room booking system which, apart from the lack of recurring bookings, is almost complete. In addition to the user tables (which aren't really relevant for the query), I have two tables (somewhat simplified): rooms id (PK, integer) name (varchar(50)...more >>

Hi I'm working on a project to optimise the retrieval of data via Stored Procedures I intend to run a particalur SP 10 times in a row to calculate the mean running time However, i am aware that the SP will make use of the buffers after the initial execution I need to test the average time of...more >>

I am new to real sql server stuff. I am coming from an access background. In access, I could easily export a query to a spreadsheet by right clicking on the query object and choosing export... or something along those lines. In SQL I have created a 'view'. How can I get that view into a spreadshe...more >>

Hi, For performance reasons we have a little weird architecture: ASP -> COM -> bcp_exec -> data file We have problem to make it work with the several concurrent users. The bcp_exec function return codes are FAIL, SUCCEED or SUCCEED_ASYNC In our case it fails with the return code -2147352567 If...more >>

I have two SQL server 2000 (sql_1 and sql_2). I try to use follwoing statement to access sql_2 in the program located in sql_1: SELECT * FROM OPENROWSET ('SQLOLEDB','sql_2';'uid';'pwd','SELECT * FROM svr2.dbo.users') But I alwat get error: SQL Server does not exist or access denied. Even ...more >>

I've installed Microsoft Reporting Services, and we want to be able to give our clients the ability to create their own reports. Obviously we need to let them view only their own records. Currently we have an Accounts table, and users login via ASP Post where we match their username and password ...more >>

Hi evererybody, i have a question. I'm using sqlserver 2K. I have two servers (A and B). They are linked and would like to know how can i check from a SP stored on A that B is available ? Thanx. Franck. ...more >>

I have a 44GB log file, any ideas how I can shrink the size? -- Kind Regards Rikesh (SQL2K-SP3/W2K-SP4) ...more >>

I have collation setting of my database: Slovenian_cl_as Then in application I have settings of my connection: Locale Identifier=1060 ;Current Language=Slovenian; So, everything is set to Slovenian. And everything works fine just in one example when I call procedure e_olap_prenosPodatko...more >>

is it possible to create a login in MS SQL that will prevent users from UPDATE/DELETE multiple rows? i know i can restrict UPDATE/DELETE altogether, but my concern is preventing accidental deletion of multiple rows. i.e. DELETE FROM TABLE WHERE KEY=123 and we put WHERE KEY>123 my accident....more >>

I'm using QA to write some queries, and I'm more comfortable with queries MS Access. In Access, I could create a query, save it, and use the results of the query in another query. Is there a similar way of doing this in QA or do I need to create a view in Enterprise Manager? (A subquery won't work...more >>

sql2k sp3 Ive got a huge delete to do. (over 1 million records) delete table1 where DateColumn1 between ThisDate and ThatDate I want to use begin/ commit trans and go maybe 1000 rows at a time to avoid long locking/ deadlock issues. However I dont want to have to sit here all day a...more >>

Using stored procedures to retrieve/update data would be more secured than enabling a user to do it. Truth? Myth? -- .~. Might, Courage, Vision. In Linux We Trust. / v \ http://www.linux-sxs.org /( _ )\ Linux 2.4.22-xfs ^ ^ 8:16am up 3 days 11:41 load average: 1.00 1....more >>

--SQL Server 2000 I execute a BCP import from the command line, which works great.... I import 96 files but I notice that it skips a few lines at the BEGINNING of some files... thinking it was something in the file, I import a small group of files including the ones that had a problem, bu...more >>

Hi, I,d like to know how to pass a ntext variable as the @statement parameter to sp_executesql since SQL does not allow to create a ntext local variable. Thanks!...more >>

Hi all, I am a new SQL user. Here is my problem. On a SQL2000 server I do daily full backups. Now I would like to restore Dec 31, 2003 data to the same server but with a different name. I don't want to replace the current production data. I copied back the old backup file to a temp directory...more >>

Hi all, I have this DDL below: I need advice what's best way to do this!! Please look at my DDL and sample data. I believe that unique key is PtNumber and givenDt. I want to do following: 1- For each patient, by looking this raw data, I like to fill the UnitNumber Column by taking FIR...more >>

The more I learn, the more confused I get. Sometimes there are two ways to do things and I'm not sure whych is better. It might depend on the conext of usage. An example is GETDATE() and CURRENT_DATE. Which is better to use, the BOL say they do the same thing. Is one preferred over the other? ...more >>

Hello ALL, How to restrict the user, to access my DB only from my application. Any help will be appreciated, Thanks you. ...more >>

Hi All, Example Trigger with IF UPDATE: IF UPDATE(prorate_type) BEGIN UPDATE table1 SET prorate_type = 0 FROM inserted i JOIN dbo.table1 c ON c.objid = i.objid END Example Trigger without IF UPDATE: BEGIN UPDATE table1 ...more >>

Hi how is it possible to return the sapce used for each row, considering each row has a text datatype column plus others. What I am aiming to do is see the intermediate report size for each report in reporting services TIA JB...more >>

Hello all, I have currently existing code that exports my entire SQL Server 2000 application database to many extract files each night. The process is made up of a series of custom DTS packages that splits up the extracts by table. The tables that are too large for one file are split up by id...more >>

Using T-SQL how can I grant EXEC permissions to a user in the same database? Is there a system stored procedure that will do this? GTaz21 *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it!...more >>

I have just got my company domain and I would like to register sql server database on my domain to my enterprise manager on my laptop, my domain name is: www.pearlsoft.com.pk. How can I register the database whose name is : pearlsoft Best Regards, Luqman ...more >>