I have the following code as a stored procedure CREATE PROCEDURE [dbo].[UpdateTblState3] AS Begin DECLARE @PersonID varchar(100) Set @PersonID = '(2, 3)' DELETE from tblState WHERE PersonID IN @PersonID End Go However it is not passing the syntax check. However if I make @PersonID a...more >>

Hi All, Once again, thanks for all the reply's on this one!!! As suggested here is the code that I am trying to run, this is taken from one of the web site's suggested. CREATE FUNCTION PROPERCASE ( @input varchar(8000) ) RETURNS varchar(8000) AS BEGIN IF @input IS NULL BEGIN RE...more >>

Hi Folks Can any one answe me the following questions How to print the records using a SQL in the following ways 1. The SQL has to print the out put like this 2 33 444 5555 2. The SQL has to print the out put records vertically like thi the normal query return the records like thi 1 2 ...more >>

Given a Date Of Birth how can I calculate the Next Birthday ? Ideally I need to do this in a single statement. -- Michael Tissington http://www.tabtag.com http://www.oaklodge.com ...more >>

Is it possible to import queries from Access to SQL. I have a database with a lot of queries. best regards reidarT ...more >>

Is it a common rule that you don't use Identy seed in SQL or is it an acceptable way of making records unique? best regards reidarT ...more >>

Hi, Can some one suggest me in detail how do I insert a row in a Temp Table by checking a certain condition.I am using SQL Version 8.0.My requirement is something like this: I have a Stored procedure which Selects a few fields by joining around 10 Tables checking various condition. I a...more >>

I am working on a migraton project in which we have to migrate the oracle's database to sql server. Problem lies with the query SELECT DECODE( parent_id, advisor_id, advisor_id ) AS advisor_i FROM marketview.adviso CONNECT BY advisor_id = PRIOR paren...more >>

Hi to all SQL Gurus, Well friends, I don't know I am seeing surprising error when I try to create the below mentioned table. My observations are as follows: 1. With a database having "SQL_Latin1_General_CP1_CI_AS" collation: Function gets created with no error. 2. With a database having ...more >>

For demonstration purposes I tried to capture the SP:Recompile event with SQL Profiler. I used for this 'WITH RECOMPILE' option to force recompiling a stored procedure at run time and since it didn't work I used sp_recompile with the same negative result. I know this has a funny side ( usually ...more >>

Hi All, There is a function in Access called "Last()" which returns the last record value in a group. For example. A B ------- 1 10 1 7 2 21 2 2 2 4 3 6 Here, Last() function In Access returns as follows: A B ------ 1 7 (the last value for A=1) 2 4 (th...more >>

I have 2 separate sql2k. There are not connected to each other because they are about 20 miles apart. I need to copy about 5 tables from one server and copy it back to 2nd server. Is there anyway that I can backup only selected tables ? Appreciate if someone please repond. ...more >>

Hi All! I like employee and department scenario from DBDesgin Q. It looks more intuitive than my previous sample. *********************************************************************** Business rule: Each employee works only in one department. Department is managed by only one of emplo...more >>

Hi, I know how to do this in ASP while using SQL server but I want to have it done by a stored prodedure and sql agent. Maybe somebody can help me out: I want to select records that are not older than 6 months. This is calculated on the value of a record called Date. So Date minus 6 mont...more >>

I need to find out the size of about 5 tables. How can I check it ? Thanks. ...more >>

Has anyone out there gotten this following message java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for JDBC]Invalid parameter binding(s). upon executing a PreparedStatement when using TEXT fields? I am not sure how to properly set my TEXT field parameter. Do I use stmt....more >>

how can I make a chain of computed columns in one teble? i.e col2=function1(col1) col3=function2(col1,col2) col4=function3(col1,col2,col3) (generally speaking) Is triggers the best way to do this? or what else? Thanks in advance ...more >>

Hi to all, Does anybody know how to pass the table's field name as a parameter to a stored procedure ??? f.e. ( calling the stored procedure from a pass-through query in access:) sp_name 'table_field_name' , 'some_value' how do i represent the first parameter in the stored procedure?...more >>

Hello all, Before I go into all the gory details... Is there any reason that a Stored Procedure could not be called from VB? I have one that runs perfectly in Query Analyzer, but will not run in VB using ADO. Not sure why. I run all my other SPs the same way and they work fine. The ...more >>

I have a table Pub_Tbl with PubID as PK, Name, Address and Zip Code as other fields. I have created a index on PubID and ZipCode. Is there a way for me to drop these indexes and transfer new data from Other_Tbl and then after success...recreate Index again on PubID and ZipCode through SQLStatemen...more >>

Using T-SQL I would like to create a temporary table with a variable number of columns. I was hoping to use something like this to generate the names of the columns Declare @Range int Declare @CurrentColumnIndex int Declare @DateCol smalldatetime Declare @StartDate smalldatetime Select...more >>

How would a query be written that concatenates a string field that meets the specified criteria? Here is some sample data. I need to concatenate the ItemDesc field where the ItemNo is equivalent: ItemNo SeqNo ItemDesc 02677 001 CONNECTOR,0.100x0.100 (2.54x2.5 02677 002 4),10Vrms,1A,-55-10...more >>

Hi All, I recieved this error when I was running an update from a linked server. Does anyone know what this is? "Server: Msg 7391, Level 16, State 1, Line 6 The operation could not be performed because the OLE DB provider 'SQLOLEDB' was unable to begin a distributed transaction. [OLE/DB...more >>

Hi, We have a active/passive SQL cluster which has fiber mode enabled. One day when trying to do something with Sharepoint 2003 the following message appeared: XML stored procedures are not supported in fibers mode Ofcourse this feature is needed so we have disabled fiber mode. Is there a...more >>

Hi, I want to create a stored procedure that returns the first field of a recordset like this: SELECT COUNT(*) FROM Table WHERE [condition] Do I have to declare a local variable in my stored procedure code or can I return the result directly? If so, how do I declare my parameter in my V...more >>

Hi there, I use SQL Server 7.0 and VB6. I need to dynamically generate a view at run time using a stored procedure. the select statement is dynamically created. Similiar to the following: CREATE PROCEDURE SP_FetchPersonInfo AS DECLARE @sql as varchar(8000) DECLARE @NoOfUDF as int DECLAR...more >>

Hi Group! Please help. Suppose the number 1069258269 represents a date value. Hot to convert it to a datetime datatype? Tried CAST(IntegerColumn as DateTime) AS Something but retuns arithmetic Overflow... :( Also if the number 1069258269 represents a Elapsed time in seconds how to conve...more >>

Hi, I am trying to do a distributed query from within my SQL 7 database, the server has been linked inot mine and I have done a stored procedure which is on the linked server which works perfectly, however when I try and put it onto mine I can;t seem to get it right. Here is my code, would ju...more >>

Hello, I have a Vb.Net client and I have some fields that I am attempting to load with a value of "null" in a parm which is passed to a stored procedure. In the Vb.Net client I am looking at a text box and if that text box has nothing in it (lenght is 0), I do the following.... di...more >>

Hi, I'm experimenting with triggers and I was hoping that some kind soul could help me out. I'd like to create a trigger to do the following. I have a table named "STOCK". When a record is inserted to this table or the "Customer" field in STOCK is updated, I would like a trigger to includ...more >>

Hi I have stored procedure for traversing the work order hierarchy of a pulp and paper plants maintenance management system. The procedure builds up a the fullstop delimited workorder number hierarchy for each row starting with the parent workorder and ending with the workorder number for th...more >>

Hello, I am trying to post ddl. I am trying to get a report from the following three tables; tblProperty, tblBldg_Sec and tblInspReq. CREATE TABLE [dbo].[tblProperty] ( [Prop_ID] [int] IDENTITY (1, 1) NOT NULL , [Prop_Name] [varchar] (70) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , ...more >>

Hello, I have a pretty big batch that I need to execute on schedule basis. I alters several views, then creates a table and updates this table using these modified views: ALTER VIEW vuMonthlySalesOpenOrds AS SELECT TOP 100 PERCENT CAST(CUSNO AS varchar(6)) CUST_NBR, MTD_Open_USD=CAST(Su...more >>

Hello all, I have an online warranty project to do, and I am in the process of making the Database and Tables. Since I am a novice, I would like some direction if possible on how to architect my relationships, and as well as criticism regarding my assigned data types and date lengths. ...more >>

Hi, I'm new to indexing tables and their associated columns. And was wondering if I had one search textfield to search a relatively small database, would indexes be a concern for me??? Eg clustered index, non-clustered index? So if you imagine my database with the following tables: pro...more >>

The computer takes two minutes with the cpu at 100% to run this; yet, the cpu time and elapsed time show as zero. --drop function WhatAboutMe create function WhatAboutMe() returns int as begin declare @x int declare @y int set @x = 0 while @x < 1000 begin set @x = @x + 1 ...more >>

I can't find it in EM. Where is it? ;-( ...more >>

I am working on a project that has 60-70 tables or so. The applications that make use of these tables do a lot of joins. We have found that we can increase the performance of our applications with just a few simple indexes. I've done this for a few tables, but I'd like to review the whole DB, loo...more >>

Hi everyone, I created this stored proc and want ot rollbak it if one of the query return me an error. Simple question, How do I do That? Delete FROM TUQCReponsesLangue WHERE IDReponse In (SELECT Id_Reponse FROM TUQCReponses WHERE ID_Reponse IN (SELECT Id_Reponse FR...more >>

Access XP, Office XP. I have a view defined in SQL-Server 2000. Using the Enterprise Manager, it returns data as expected. I get a list of individuals and the county(ies) in which they work - one row per individual/county combination. When I link to that view via MS Access, the differening...more >>

Hi, Here is a line of code in SQL which I have written that converts a field into a varchar and also checks to see if it is null:- isnull(convert(varchar(10),paon_end_num),'') however I need to be able to say that if paon_end_num = 0 then display it as null. Any ideas on how I can do this -...more >>

When I run my stored procedure with this at the beginning and end: SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO I get error 16937 and 16945. When I remove it from the end of the stored procedure, I dont get any errors. Other stored procedures have this and they do not cause ...more >>

Hi all. I am trying to use the ADO Command object to insert a value into a table with a text field. My command text looks like "INSERT TABLE (TextField) Values (:name)". Then I assign the value of the parameter to a string. Whenever I try to execute the query I always get the follo...more >>

is there any way to get/calculate the UTC time in SQL Server 7 Thanks Ionut...more >>

has anyone written an SQL query that I could run on a database that will return a resultset of rows that are all the field names from user defined tables in my database, I would like something like this... TableName FieldName PrimaryKey tableA field1 tr...more >>

Where is the description column information kept? I'm trying to get the = info on the columns in a table using the below query but do not know = where the Description that I enter is the Enterprise Manager's Design = Table column properties is kept. select so.name as tbl, sc.name, st.name as T...more >>

Greetings, please see code below... ----------------- CREATE TABLE TblA (CustID int, OrdID int, OrdCode varchar(10), Product varchar(20)) INSERT INTO tblA (CustID, OrdID, OrdCode, Product) VALUES (1000, 250101, 'ABC', 'apples') INSERT INTO tblA (CustID, OrdID, OrdCode, ...more >>

Hello, Someone knows the way to correct this please : EXEC sp_attach_db @dbname = N'Rad', @filename1 = N'E:\mssql7\data\rad_Data.mdf' -- Out Error Server: Msg 823, Level 24, State 1, Line 1 I/O error (bad page ID) detected during read of BUF pointer = 0x1421d500, page ptr = 0x4186...more >>

I am wondering how (if it is Possible) to do something like the = following query, but doing it using a query and not a (Where not In = clause) .. Select * from SomeLookUpTable where id not in (Select ID from SomeTable) I am wondering if the same results can be done using a join. Somethin...more >>

Hello, I am thinking to do version control for database objects, such as stored procedures, tables, and etc. I need to add these data objects to Visual Source Safe. This is the situation: From EM, I picked a databdase. Right click, took All Tasks and Generate SQL Script. I selected the obje...more >>

Dear all We have two machines both running SQLServer 2000 on Win2000 server boxes and running the same web application. Both SQLServers have, as far as I can tell, been set up identically. Both Windows machines have, as far as I can tell, been set up identically. We have a DATE problem w...more >>

How do I execute a stored procedure as a scheduled Job in Win2K?? ...more >>

This kind of goes against the grain of SQL, but maybe SQL Server has an extension.. I have a query that returns one column and several rows. I would like, instead, to have a query that returns one column and one row, and the values currently in different rows would be concatenated together in ...more >>

I can't remember a SQL command to create a table with only schema, but no data. It is like: select * into tableA from tableB truncate table tableA ...more >>

I know that error 16937 refers to "A server cursor is not allowed on a remote stored procedure or stored procedure with more than one SELECT statement. Use a default result set or client cursor." I checked the stored procedure and there are no SELECT Statements at all. Here is the RPC th...more >>

Hi, I need a pro help. I restored db, ran "dbcc checkdb" and found some errors: Server: Msg 8928, Level 16, State 1, Line 1 Object ID 957246465, index ID 0: Page (4:762476) could not be processed. See other errors for details. Server: Msg 8944, Level 16, State 1, Line 1 Table error: Ob...more >>

How do you check the Collation Type of just a Coloum in SQL 2000. I know how to check the Database, and the SQL Server its self, but is there a way to check the columns them seleves. Thanks...more >>

Hi, I have used code from an article on 4Guys to page through a recordset, what I would like to do is find the last record in the temp table so as to show "page 6 of 3383" but whereas I would normally do something like: select max(row_id) from tablename This doesn't seem to work, I have p...more >>

My interesting problem is as follows: I would like to dynamically navigate relationships between indirectly-related tables. For example, I would like to perform a join on tables T1 and T4, however there is no direct relationship between them. To get to T4 from T1, I have to follow this path...more >>

Hi, Thanks for all the posts, on the UPPER topic, I have tried using the examples that were on the pages, but I cant even get them to generate it's saying there is a error with the syntax while trying to create the function. This is prob. me as I have never created a funtion before. If the...more >>

Hi, I have a simple sproc on a SQL Server 7.0 that has massive varitions in execution time. After profiling its duration over a day the duratioon ranges from 200ms to 800,000ms. I've no idea why as its an extremely simple query on a table wth only 1400 records that is not updated very o...more >>

Is there a system stored procedure or ANSI-Compliant view that will let me know if a particular foreign key is active? I have a field whose value could point to one of three tables. I created three foreign keys on this field -- one for each table. I then altered the table to set two of them to NO...more >>

I usually use SERVER=myserver;UID=<my userid>;PWD=<mypassword>;APP=<my application> is this bad? is it better to use SERVER, Initial Catalog, User ID, Password ? And what is the equivalent of APP in the latter? ...more >>

Is there a way of getting a summary Table or print of all the column names within each User Table in a specific database on the Server?...more >>

Hi All, I am sorry if anyone has already seen this message but I posted earlier but my post was never put up. I have a table with 2 fileds in it, one with name and one with address, I want to Convert the first letter of the words in the name and the first letters of the address. e.g. ...more >>

Hi All, Have quite a simple question, well hoping it is, I have some names and address's stored in a table and all I want to do is change the first letter of the name (first and Last) to upper case, as well as doing the same for the address, e.g. mark hughes - Mark Hughes 13 liver S...more >>

I have a need to use a variable as an object within T-SQL code but cannot find a method to accomplish this. For Example: DECLARE @tablename ????? SET @tablename = mydatabase.dbo.tblcustomers SELECT * FROM @tablename While something similar to this can certainly be accompli...more >>