Can I use select case in "stored procedure ??? If yes, can someone be kind to give me a sample ?? Thanks From SQL-newbie ...more >>

Hi, I have an SP that returns results as follows: UserName AccessAllowed Khurram Read Khurram Write Tim Write James Write Tim Delete How can I get the resul...more >>

Thanks Jacco. Your code makes more sense to me than the one by John Gilson. In some_table, there is actually 1 more column, "year". if select the year into the rest like this: SELECT t.[year], m.[Month], ..... the result shows only the year for the records that exist in some_table (obviou...more >>

to exhibit in a column in view, number formatted with two houses decimals and point of thousand separate, I ask for the kindness of posting here. ...more >>

Say I have two queries q1=select id, blah blah from mytable and q2=select id, blah2 blah2 blah from mytable These queries do fundimentally different things, one being an agragate query to sum up some stuff, the other is a query to obtain the max value of a field. Now I want to com...more >>

How can I write a query that adds records once a month? The query needs to add an amount to a register (for a service) based on a customer's current rate. So say, customer 1 has a rate of $80 and customer 2 has a rate of $85, at the end of the month I want it to automatically add records to the...more >>

Hi, The task is so hard that I even don't know how to start explaining it...( Let try to write a schema step by step. We have two basic tables (and a few related subtables) - with questions and answers. The tblQuestions database table is having the following fields (simplified): QID [...more >>

I have a varchar(50) field. Entries should be only letters or spaces etc but not numbers - is there a CHECK CONSTRAINT expression or a TRIGGER expression that can be written? Hope somebody can help Kind regards Ross...more >>

have a table called AppUser as defined below CREATE TABLE [AppUser] ( [Id] [int] IDENTITY (1, 1) NOT NULL , [ParentId] [int] NULL , CONSTRAINT [PK_AppUser] PRIMARY KEY CLUSTERED ( [Id] ) ON [PRIMARY] , CONSTRAINT [FK_AppUser_AppUser] FOREIGN KEY ( [ParentId] ) REFERENCES ...more >>

I've just download SQL Server 2005 Express and I am trying to get our application to work with it. I've attached the database and can successfully query the database using OSQL. But I can't get our application to connect. Are connection strings different for SQL Server 2005? Or is there some...more >>

Hi, Imagine I have a table with 3 records: Month, Value 1, 5 2, 8 6, 3 I wish to create a view that always shows months 1-12, with a sum (cumulative) of the value: Month, Value, Total 1, 5, 5 2, 8, 13 3, 0, 13 4, 0, 13 5, 0, 13 6, 3, 16 7, 0, 16 8, 0, 16 9, 0, 16 10, 0, 16 11,...more >>

Can anyone provide me links Advance SQL pragramming tutorials / pdf files panks ...more >>

Consider a SELECT like this: Select Category.Name As Category, SubCategory.Name As SubCategory From Categories Inner Join SubCategories On Categories.CategoryID=SubCategory.CategoryID A traditional kind of join between parent and child table that generates output like this: Co...more >>

Hi, I have this SP: CREATE PROCEDURE prSVUSERINFO @USRNAM VARCHAR(20), @USRPSW VARCHAR(20), @NAME VARCHAR(64), @ADDR VARCHAR(74), @EML VARCHAR(64), @ROLE VARCHAR(6), @RCOUNT INT OUTPUT AS IF NOT EXISTS( SELECT * FROM BLEK.USERS A WHERE A.USERNAME=@USRNAM OR A.USEREMAIL=@EML) BEGIN IN...more >>

I'm modifying some stored procedure scripts to alter the proc instead of dropping/creating it so that permissions are not lost. In doing so, I'm trying to use: if not exists (select * from dbo.sysobjects where id = object_id('dbo.ProcName') and OBJECTPROPERTY(id, 'IsProcedure') = 1) crea...more >>

Hi, What do you thik about this... i have this SP Create Proc prINSDATA @param1 varchar(20), ..... @paramN varchar(p) AS insert into table1(n, m, p, q, r) values(@param1, .....,paramN) Declare @IDENT int SELECT @IDENT = SCOPE_IDENTITY() insert into table2(, f,g,y,r)values(.param5,@ID...more >>

Can some one help me with this? I can't figure this out.... T1 T1_ID PK Name T2 T2_ID PK T1_ID Location T3 T3_ID PK T2_ID Building T4 T4_ID PK T3_ID Status I want to print out a report like this Name Location Building Count(T4_ID) Count(Status) ===...more >>

When I execute this command: USE master EXEC sp_dboption 'Operations', 'db chaining', 'ON' I receive this error: Database option 'db chaining' does not exist. I'm using SQL Standard on a Win 2K Server box. HELP!! Thanks Scott ...more >>

I'm trying to set up Cross DB Ownership Chaining as per the BOL onto a SQL Standard box. When I execute this command: EXEC sp_configure 'Cross DB Ownership Chaining', '0'; RECONFIGURE -- This is from Books Online I get this message: The configuration option 'Cross DB Ownership Chaining' do...more >>

Hi , I have the following SP: CREATE PROCEDURE prSVUSERINFO @USRNAM VARCHAR(20), @USRPSW VARCHAR(20), @NAME VARCHAR(64), @ADDR VARCHAR(74), @EML VARCHAR(64), @ROLE VARCHAR(6), @RCOUNT INT OUTPUT AS IF NOT EXISTS( SELECT * FROM BLEK.USERS A WHERE A.USERNAME=@USRNAM OR A.USEREMAIL=@EML) ...more >>

OK, if I have a sproc which has to return 2 values (a description and a date) to an ASP page, what is the best way to do that? I was starting with Output parameters, but then realized I wasn't sure how to call them from the ASP page that called the sproc. Any examples would be greatly appreciated...more >>

Hi I know that this cannot be done: select * from T1 inner join T2 on T1.a = T2.b OR inner join T3 on T1.a = T3.b what I'm trying to do is to get all the records from T1 where either the first or the second join is true. I know I could do something like this: select DISTINCT * fro...more >>

I'm fairly experience with sql, but not experienced at all with sql server. I have a problem at work where I have 2 tables. One table I have 3 important fields: one that is a text field (field1), one that is its primary key field (field2) and one that is a primary key of a second table (field3) (...more >>

Hi, I have Store Procedure which should return multi rows but Returns only one Row Always? Can any one point out why it always only one row -------------------------------------------------- SET QUOTED_IDENTIFIER OFF -- SET ROWCOUNT 100 GO SET ANSI_NULLS OFF GO ALTER ...more >>

Hi, I am storeing a number as a decimal (18,2) in the database, when I do a select the number always comes backed "padded" out to 2 decimal places with 0's for example 10 is 10.00 10.1 is 10.10 is there any way I can make then come back in myselect so that the additional 0's on the e...more >>

OK, I give up, there has to be a simple answer to this but I've dented the wall banging my head into it trying to figure it out. The following Query works as expected and the result set is what is desired (13338 records): SELECT * FROM OPENQUERY(wcw, 'SELECT Cust_Nbr, Order_Nbr, Ent...more >>

Hi! I need to build a where clause for my SQL query. Please see the code below: create table #Accounts (ID int, AccountName varchar(100)) insert into #Accounts values (1, '12345') insert into #Accounts values (2, '99999') declare @SQL varchar(100) declare @where varchar(20) declar...more >>

Hi There, I am trying to return the identity of the last inserted record, the output parameter seems to be null. What am I doing wrong? Moe -- page code myCommand.Parameters.Add(New SqlParameter("@listingID", SqlDbType.Int, 4)) myCommand.Parameters("@listingID").Direction() = Paramet...more >>

If I make a query that uses a join on a view, will the optimizer create a single "optimized command" or will it optimize only the query? example: mytable is a table, mytableview is a view built on top of mytable, mybasetable is a standard table: SELECT * from mybasetable LEFT JOIN mytable ON ...more >>

I am running the SP below, the database fields are varchar, still the insert gives the error below: I checked the values and its a regular address, nothing out of the ordinary. ------ ERROR ------ Technical Information (for support personnel) a.. Error Type: Microsoft VBScript runtim...more >>

Any help would be appreciated. I have a folder structure as follows: a root folder, subfolders from A-Z, each subfolder A-Z has subfolders that is the name of the client. Each client folder contains documents associated with the client. I want to convert this folder structure into a table wi...more >>

Sorry for this non-programming question but I might start doing some contract work soon. The contractor company is going to have my skills tested over the phone by someone, like a third party. I am confident in my ability but don't actually like tests. Has anyone had this happen to them...more >>

I've got a table with 4 columns called Dx1, Dx2, Dx3, and Dx4 (all varchars). I want the query to include the row if only one column has a value. Below is part of my query so far....is there a way to make it simpler? select <snip>...where (Dx1 is not null and Dx2 is null and Dx3 is null...more >>

Hi, I am working on databases and tables with long name and I know that I can use views to shorten queries. In Oracle we use synonyms to shorten query typing. Is there any similar capability in SQL server other than views? Thanks, Alan ...more >>

Celko says that you should not prefix tables and views with things such as (tbl, vw, table, view, etc); becuase that leaks to a user how the data is stored (a table or view). So if a table and view should be indisguishable, why can i not create a foreign key to a view; as i can to a table? ...more >>

I have added a new user in the db_owner role in SQL 2k. When this user creates a table or any other DB object, it is owned by the user and not by dbo even though the ID is a part of DBO. Is this a default behavior in SQL 2k or do I need to do something different in order for the object to ...more >>

Hi I need a help, Is there anyway to use CASE statement in Insert command. Here is example Table A Sl.no Value 1 2 2 3 4 NULL 5 3 Insert into table b select sl_no, if value null 99 else value from tab...more >>

I have a table called tbl_Employees with emp_LastName and emp_PrefferredName fields. I want to select both those fields into one sorta like SELECT emp_prefferredName & ' ' & emp_lastName as Fullname FROM tbl_employee but it does'nt work... I get this error "Invalid operator for data typ...more >>

Common Table Expressions introduced in SQL2005 is realy great thing. Now I wonder are there plan to add any new analytic SQL features which Oracle has since 9i version like: FIRST/LAST GROUP BY GROUPING SETS Composite Columns on ROLLUP/CUBE clause GROUPING, GROUPING_ID and GROUP_ID MATERIALIZ...more >>

After some research and learning about what an UPDLock is and how it works, I wanted to test it. I created a table, AAA, comprised one integer field, "Duh", which is defaulted to zero. Then I created code which increments "Duh" 10,000 times. Here is the code: SET NOCOUNT ON DECL...more >>

I am having trouble viewing XML queried results in Query Analyzer. I've set it to return 'Results in Text', but the results are still truncated after a set number of characters. What am I missing? ...more >>

Okay, another big question (This one is probably more specific to my SQL Server instance :) ). It was always my understanding that SPs are a great way to wrap up SQL commands without having to give security everywhere in the DB. The problem that I have is that I cannot only give EXEC permissio...more >>

I am planning to use an int datatype as a bitfield (1, 2, 4, 8, ...). Is this a good idea? I know SQLServer has bit operators (&, |, ~). I can't think of a better solution. The promblem is i have an order that can be picked by multiple methods. I was going to use the column and bitmath ...more >>

I have the SQL 7.0 MDAC desktop engine running on my desktop. I have the DbaMGR as my utility. I have received an email with an .mdf file and I am trying to attach to my database. This does not seem to work,, do I need the .ldf file as well? Further I have another enterprise tool to run que...more >>

Hi, Table1: PT_ID Pt_LN Pt_FN Address 3 ln1 fn1 123 street 5 ln2 fn2 111 street 8 ln3 fn3 122 street 9 ln4 fn4 1289 street Table2: PT_ID Protocol_ID 3 1 3 3 Table3: Protocol_ID Protocol_Name 1 Name_1 3 Name_3 We have many to many relationships with Table 2: ...more >>

Does anyone know how to obtain the last value of a column in a a resultset? Here is an example of what I am looking for: Resultset: a b c 1 1 1 2 2 2 3 NULL 3 4 NULL NULL I want to get the last value for a, b, and c so my query can bring back the following...more >>

I am executing storedprocedures from C# using SQLClinet. In the SQL Profiler, I get SQL:BatchCompleted for one of them and RPC:Completed for another. Can anyone tell me what SQL:BatchCompleted and RPC:Completed are? and why i am getting a different results for these two sprocs? The st...more >>

I am basic level user have not had much experience with databases storing large amount of data. I need expert opnion regarding the optimization of following query. SELECT distinct table1.fldA, table1.fldB FROM table1 where table1.fldA in (select table2.fldA from table2) table1 has aprox ...more >>

Help Newsgroup Is it possible to create a trigger in SQL 7.0, where if a delete occurs the trigger does nothing, but if an update occurs then something occurs? Many Thanks Tony C...more >>

Hi I want to created an indexed view, but get an error when I create the error. I want the index to be on both the LocationID and NetSalesAmt columns so it is a materialized view. Here are the details... << desired resultset >> LocationID NetSalesAmt ----------- --------------------- ...more >>

Hi All, I have one field in a table that is currently a ntext field as it was the only size big enough to put the data in that I wanted, the problem with the data is it hhas a lot of java script in it and all I want is the data that is between the brackets, I need to put each set of data bet...more >>

I have as table created using the adjacency list model (http://www.intelligententerprise.com/001020/celko.jhtml?_requestid=132953) and all works well however, I would like to populate a MS Tree control from the data in the list (http://support.microsoft.com/default.aspx?scid=kb;en-us;209891) ...more >>

I tried to convert some fields from my table to numeric, but I get a error message. I used the Enterprise Manager to change form varchar to numeric. Is there another way to change the datatype?? Greetings Jan...more >>

Hello Experts, How can I pass the owner name (@ownername) dynamically with the system function OBJECT_ID alongwith the name of the object(@tablename)? I need to do something like (i know this is not correct): SELECT name FROM sysindexes WHERE id = object_id(@ownername + '.' + @tablename) AN...more >>

Hi I am trying to recover a DTS package containing an ACtive X Script after my trial version of SQL Server expired. I was told that the script is held somewhere in the msdb database, after searching through this database I cannot find anything related to my DTS package or Active X Script, can ...more >>

i need to have a query that finds a particular record exists in a table... was wondering which is more efficient 1. select count(*) as myrecordcount from table1 where field1 = '12345' 2. select * from table1 where field1 = '12345' in my prog language for query 1, i just have to check if ...more >>

I have this query: Select b.caseid, a.userid from atts as a inner join cases b on a.caseid = b.id group by b.caseid, a.userid Results: Caseid Userid DEMO0021H 3 DEMO0021H 6 DEMO0021H 7 DEMO0021H 8 H1b-34234 ...more >>

Hi All, I have one field in a table that is currently a ntext field as it was the only size big enough to put the data in that I wanted, the problem with the data is it hhas a lot of java script in it and all I want is the data that is between the brackets, I need to put each set of data bet...more >>