I have created the following VIEWS in my database. (Sorry, they're quite long, but I've reproduced them exactly below.) In a nutshell, VMSGRECIPIENTSA and VMSGRECIPIENTSB return the same record structure. VMSGRECIPIENTS simply pulls these two together via a UNION. (For brevity, I'n going to call...more >>

How can i store in a Sql table an attachment to a document. For example my application manage a word document and i have to store it in a table as ole object Thank's...more >>

when creating a stored procedure or a user defined function using enterprise manager, how to determine the settings for the connection under which the create sql is being executed .... i'm talking about settings such as ANSI_NULLS enterprise manager : <database name> : properties : options ...more >>

I'm having problems converting the following query (called through ADO, SQLOLEDB) from Access to SQL 2000 and making it updateable: SELECT tblBalances.* FROM ( SELECT tblTemp.ID FROM tblTemp WHERE (((tblTemp.SessionID)=263532309) AND ((tblTemp.Func)=3)) ) AS sqlTempID INNER JO...more >>

that´s all thanks .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .....more >>

I have a table with an identity column which is also it's primary key. An application inserts a new record (whereby the identity column gets incremented -- or so it's supposed to do). The the application re-retrieves the row it just created and a completely different, pre-existing record p...more >>

Hello, I need an help file (*.chm is preffered, but any will be fine) of Sql-Server, please. Where can I find such one ? Thanks :) ...more >>

I want to replicate one table as another table with a different name in the same database. Same schema, same default values, and same key. Using SQL Server 2000. What's the best way to do this? I tried DTS, but I seem to lose the default values. If I attempt to do a transfer objects option...more >>

Hello: I have a table T1 like this: NameID | F1 | V1 | F2 | V2 | ------------------------------------------ 1 A 49% B 51% 2 C 35% D 65% 3 E 20% B 80% 4 D 10% A 90% I need a query to transform T1 ...more >>

I have created a stored procedure and I need to prompt the user for a date and I am have trouble getting the syntax right. Can anyone help me with the sytax or point to where I can find info Regards Newman ...more >>

Is it possible to have a stored proc make a call to an asp on a web server? Thanks ...more >>

Hi, I am very new to stored procedure. Currently, I have linked my tables in Access to SQL server. I use VBA to do the calculations for my forms in Access How do I pass a variable from VBA to stored procedure e.g.: a = "Select ID from Employees Where Name = 'Peter' How do I pass thisd var a to ...more >>

Hi guys, I'm using SQL Server 2000 Std Ed. I'm trying to build a simple SQL view which I've simplified as follows: SELECT PersonID, IIF(CreatedBy IS NULL, 'yep', 'nope') As something FROM tblperson The problems are that SQL Server... a) doesn't recognize IIF as a val...more >>

I have 2 tables: POOrderDetail: pdtl_order char(15) NOT NULL, pdtl_line int NOT NULL, pdtl_orderqty int NOT NULL POReceive: prec_order char(15) NOT NULL, prec_line int NOT NULL, prec_quantity int NOT NULL, prec_infull bit POReceive ties to POOrderDetail by prec_order=pdtl_order A...more >>

I have an SP which does the following (POOrderDetail is tied to POOrderHeader by pdtl_order=phdr_order): CREATE PROCEDURE dbo.pPOPostRecv ( @in_batch integer ) AS DECLARE @intErrCode integer SELECT @intErrCode = @@error BEGIN TRANSACTION IF @intErrCode = 0 BEGIN -- deletes records...more >>

Hi, I have the following problem which is annoying me for some days... I have build a web application with C# and ASP.NET which is a controlling instrument showing key success factors in a balanced scoredcard view. There are departments in an hierarchical structure and it is possible to agg...more >>

I'm using the rollup operator to show the data and summarize them. Example: Select product_id,product_name,sum(quantityWeek),sum(quantityMonth),sum(quantityOrd er) FROM products.... group by product_id,product_name with rollup Now I get the 2 rows of summarized data, one for product_Id and...more >>

Not sure what group to start in, so giving this one a try. I what to get data from Active Dir. and use in sql applications. My goal is to get users info from AD and place in a SQL table. I tried an exmple using this: sp_addlinkedserver 'ADSI', 'Active Directory Service Interfaces', 'ADSDS...more >>

Can an SQL Stored Procedure return an updatable recordset and if so, how? THanks! -- Michael Tissington http://www.tabtag.com http://www.oaklodge.com ...more >>

Hi Everyone, I am not able to pass strings of size more than 32767 characters from my C# code to stored procedure. SqlCommand.ExecuteNonQuery() is throwing SQLException with the Message -"A severe error occurred on the current command. The results, if any, should be discarded." Is the...more >>

In Profiler i see lot of messages sqlbatchcompleted: SELECT N'Testing Connection...' Application: SqlAgent Alert Engine will these cause any performance degrade. Thanks...more >>

Hi all, IsProcedureModule Column will only contain a possiable of 3 values ('Blank','yes','no') I would like to Select all rows ordered by the value 'no' first, then 'Blank', then 'yes' can someone please help. CREATE TABLE [ModuleList] ( [ModuleID] [int] IDENTITY (1, 1) NOT NULL , ...more >>

My SP returns 0 and should return 14;20;12;55;99;1255;589;66; as rowsource for a listbox that shows the count of orders per phase. instance = set of phases ( @RowSource = '14;20;12;55;99;1255;589;66;' = for an instance having 8 phases) @CountPhases = number of phases in this instance Tha...more >>

I have 3 tables.... Main table is [Stock] which contains columns [ItemNo], [Description], [Type], and other columns.. 1st Related table is [Songs] which contains columns [ItemNo], [SongTitles], [Singers], and other columns.. 2nd Related table is [Equipment] which contains columns [ItemNo], [Fe...more >>

Hello, I cant seemt o figure out how i can output a YES or a NO value rather then the standard -1 or o output. I have a table in SQL server and unlike access 20oo were i can actually selected from the list of output to be YES or no.... but in SQL server, the data type to select from i...more >>

How can I execute xp_cmdshell from remote location? Thanks culam...more >>

How can I delete the following information and keep the records that I need. Please refer to result want. Thank you very much. if exists (select * from dbo.sysobjects where id = object_id(N'[Test]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [Test] GO CREATE TABLE [Te...more >>

Hello, Can somebody explain to me what transitive dependecy is? I know in textbook they are explain very dry and I find difficult. Mr. Celko are you there? Thank you very much ...more >>

how can i get server and instance name using tsql.? ...more >>

Hi, i have make a copy of sp_spaceused in sp_toto ( in master database ). When i execute sp_toto in other database, i have not the same results as sp_spaceused ( sp_toto always get the master.dbo.sysfiles ). I don't understand why ? Could you help me please ? Sorry for my english. regards...more >>

Hi , Is it possible to see the trigger status whether it is enabled or disabled . Regards Ajay ...more >>

I am trying to figure out a way to determine if the current day is an even (2,4,6) or an (3,5,7) odd day. Please advise... Thanks, Brett I am using sql server 2000 ...more >>

I thought I would post this here before I open an incident with microsoft. Perhaps there is something that I was not aware of. The issue resolves around passing in the DEFAULT value for a scalar UDF. When I put the scalar function in some queries, it misbehaves by returning the value 0 (zero...more >>

HI, With in ISQL, I can choose not to displace Report Header and space delimited between field at my machine. However I would like the user(other developers) to run the queries without displaying the Header and space delimited between fields? Can I do it in TSQL? culam...more >>

Col1 (varchar) --------- 111 222 333 ABC 4DD SELECT * FROM T1 WHERE ISNUMERIC(Col1) Server: Msg 170, Level 15, State 1, Line 1 Line 1: Incorrect syntax near ')'. How do I use ISNUMERIC within a WHERE clause? I'm joining two tables, but only where a certain column is numeri...more >>

Please find the necessary SQL scripts to generate a small version of my database and some data at the bottom of this post. Here's a short description of what the database is all about: It's a project tracking and management system. Contracts go into the tblDeals table. Because each project may...more >>

I need to import data into SQL that has been collected from a clinical study. In most cases I could handle that pretty easily, but this data is stored on a network drive, each record in an individual Word (.doc) file. Each file was created with a template, so the data should always be in the same ...more >>

Hello all, I have multilanguage application used MSDE. I've added messages for three languages using sp_addmessage. All of messages have format clauses. I've checked for all languages. When I set other then us_english languages I haven't message text. If I delete format clauses...more >>

Hi, have inherited a server that contains about 250 databases. All of these databases are backed up by a single maintenence plan. This is fine except the transaction log just keeps growing and growing. All dbs are in Full recovery mode. Part of the plan does the back up of all databases. ...more >>

my output have a lot of NULL, I would like to replace it with blank. Below code does not work! REPLACE(ms_loan_adjustable_payment.index_cd, NULL, '') Please help and thanks in advance, culam...more >>

Hi, When i try to execute this line : EXEC xp_cmdshell OSQL -S server -U Sa -P password -d DB1_SQL -i C:\SQLScript\ConfigurationTestIntegrationDisplayOnlyAddition.sql i keep getting same error message : Cannot open input file - C:\SQLScript\ConfigurationTestIntegrationDisplayOnlyAddit...more >>

Hi everybody! This code: select case cast(2*rand() as int) when 0 then 0 when 1 then 1 when 2 then 2 when 2 then 3 end evaluated by SQL preprocessor to: Compute Scalar(DEFINE:([Expr1000]= If (Convert(2*rand(NULL))=0) then 0 else If (Convert(2*rand(NULL))=1) then 1 else If (Conve...more >>

Hi, I want to insert record into SQL Server by using openxml, but some values wanted to pass values not from the xml string. is it possible, say JobID and XMLRequest. I want to pass values from the incoming parameters fo stored procedure. Thanks! ALTER procedure procMultiInsert (@d...more >>

Please help me with the follwowing queries. Here is the business rule and result want. Thank you very much in advance. if exists (select * from dbo.sysobjects where id = object_id(N'[Test]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [Test] GO CREATE TABLE [Test] ( ...more >>

I have a user_defined_function and a stored procedure that calls the function. They both compiled fine (not debug yet). But When I try to run the stored procedure, I got the error says "Invalid object name 'dbo.ARMS_FindLevels'. Attached are the function and the stored procedure. CREATE...more >>

I need to transfer data from one table to another. I am having problems transferring the data inside columns defined as "text". I need to move Table1.TextData1 to Table2.TextData2. Any help would be appreciated. Sam...more >>

I want to get the list of all accessable SQL servers as the list of servers provided by MS SQL SERVER SERVICE MANAGER Dialogue box. thanks....more >>

Q? How to count in-flight concurrent queries with SQL 2000 We are working with www.wintercorp.com to publish our database information for their annual contests. They are insistant that we be able to produce a count of "In-Flight Concurrent Queries" against a database. Research on google shows that...more >>

Consider my table (simplified) that stores my wine bottles: create table WineBottle ( BottleName varchar(50), GoodtoDrinkAfter int, GoodtoDrinkBefore int ) INSERT INTO WineBottle ([BottleName], [GoodtoDrinkAfter], [GoodtoDrinkBefore]) VALUES('Bottle1', 1998, 2005) IN...more >>

Can I create a text file at specific location from TSQL? Thanks, Lam...more >>

This is somewhat of a general design question, so I have no DDL to post. I am creating an exam , with potentially 100 questions. Each question has 4-5 choices. Traditionally, I've joined the "Question" table and "Choice" table on some id like questionId, and then retrieved the recordset. As ...more >>

I'm going to be creating a stored procedure which will be taking in a comma delimited string as a parameter. In short, I want to return records that match the values passed in. However, I can't seem to get it to work. Here's my sample table: create table names ( fname varchar(30...more >>

I was recently restoring a database on our live server with sql2000. An error said I didn't have exclusive rights. The only way I know to get around this is to detach the database, clear connections, and attach the database. Is there code to kill all spid's and give me exclusive rig...more >>

Hi, I have two tables A and B Table_A = col, colA, colB... Table_B = colA, col1, col2, col3, col4..... therefore Table_A has reference to Table_B via colA. what i am trying to do is..... select Table_B_column_names_as_data from Table_B where Table_A.colA = Table_B.colA and T...more >>

Okay this is the first time I have run into this. I have a program that I have recently converted over to SQL Server 2000 from Access 2000. I have this complicated query that takes about 7 seconds to run on the Access version and it takes 14 seconds in my converted to SQL version. Th...more >>

Hi, I will be having a pop-up questionnaire, and I was wondering if anyone can give me pointers on the table design. 1)Number of children in household: 2)Name of Children in household: 3)Birth Date of Children in household: 4)Gender What do you guys think? What type of table desig...more >>

I had a SQL database file in my hard drive: it is called jinuacademy. However, two days ago, my computer crashed, and I barely made to recover old files I have had in my computer. Actually, I had another hard drive, and I switched my original hard drive and new hard drive, and installed Windo...more >>

Hi, I just created a procedure on my sql2000 server, and noticed that the "IsMsShipped" bit is set. How come? I thought this bit was supposed to help distinguish the system object from the user objects? regards...more >>

Hello, Can anyone advise on the best way to create a file from running a SQL Statement that does not use DTS ? The reason why not DTS is there is a lot of control and add character used as the column and row delimeter that DTS doesn't like. Thanks J...more >>

Hi, I have an Audit Database say A Which has stored procedure calls to another Database say x So in A It might call x.dbo.myStoredProc There could be multiple x's or x could be called something else is there anyway of flexable programing A for the name Prefix of x or whatever its ca...more >>

Hi there, I have a Stored Procedure called SP1 which gives a select * from ##temptable (this is the return value from the SP1). This ##temptable has a warning in the creation itself. The Warning message is row size exceeds the maximum size etal... and then the Stored Procedure doesnt retu...more >>

Hi.. The problem is that i got a numberfield (fnr - 11 numbers) where i want to see if number "7" in the field is either got value 8 or 9. Gonna build it into a view. I figured out i cannot use substring like this : SELECT fnr from table where substring(fnr,7,1). What other ways can i d...more >>

Hi there, I'm using a SELECT name, city, dateofbirth INTO ##tmpTable. Some of the dateofbirth (defined as datetime) have a value of 0:00:00. After executing SELECT INTO statement, those values of the dateofbirth in the #tmpTable have changed to the value 1899-12-30 00:00:00.000 I recognize th...more >>

hello, I have a problem when trying to call Set RowCount @LocalVariable from a user defined function It Generate the following error message. Invalide use of UNKNOWN TOKEN in the function I Thought of using Top instead but i want to set the row count at run time by a variable pass...more >>

I have got 2 tables. The donors table and the business type table <<Business_Type> Business_Type_ID, Parent_ID, Business_Des 1, 1, ‘A Type†11, 1, ‘Subtype of A†2, 2, ‘B Type†3, 3, ‘C Type†13, 3, ‘First subtype of C†14, 3, ‘Second subtype of C†15, 15, ‘D Type†16...more >>

Hi I have a column with email addresses but I want to select the name before the @ sign and output only this data does anyone know of a way to do this thankyou for any help Shaun...more >>