create table #tbl ( id_no char(1), period char(1), pr_no char(5), amount float ) insert into #tbl values ( 'A','D','001',10) insert into #tbl values ( 'A','N','002',20) insert into #tbl values ( 'B','D','003',30) insert into #tbl values ( 'C','N','004',40) Using select amount...more >>

Hi, It is only possible to create a stored procedure, views, triggers and user defined functions if (a) the syntax is correct (b) the objects (tables, views, ...) used, exist in the db For example, when a stored procedure is created, it is possible to rename one of the tables that is used,...more >>

Hi all, I'm building a photo album application. I have a table like: PhotoID, Year (smallint), Month (tinyint), Day (tinyint) The reason why I don't store the date in one column of type datetime is because the user might not know the full date (e.g. they know the year and month only). ...more >>

Hi, I am creating a simple form that user will backup his database using that. I need to show a progress bar when the backup is in progress. How can I read a value that indicates that? I saw that BACKUP statement has a STATS option, but I don't know how to capture its value from the server and b...more >>

Hi I have following code in a query .....--beginning with select and from CASE ObjectTypeId --column type of tinyint and WHEN 1 THEN (SELECT Name From Table1) --Name is nvarchar (200) WHEN 2 THEN (SELECT Code FROM Table2) -- code is int .... END as Exp1 .....--continued The QA returns me an...more >>

Im running a querystring in vb.net and it seems as though SQL server decides on the order in which to run the 3 queries in the string.. Is this possible? I would think that SQL server starts with the 1st query, executes it, then goes to the next. For example: *********************************...more >>

Hey guys Lets say I have a database and a client file... Is there anyway that the database can somehow send a communication or some sort of message to the Client file to perform a query on the database? What I am trying to do is when a change is made to the database file, I want it to ...more >>

I am using this query to locate users with middle initials in my database, but I need a query that locates contacts with a middle initial that is also in the database without Example Akbabar J Rockefeller and also Akbabar Rockefeller. I need to find both users with middle and the same user without....more >>

Hi Friends, Does any one has a function or SP to convert a DEC to OCT and OCT to DEC ? Thanks in advance, Patrick ...more >>

Hi Friends, Does any one has a function or SP to convert a DEC to HEX and HEX to DEC ? Thanks in advance, Patrick ...more >>

I created a insert stored procedure but It was not working correctly Could you correct the code? I am trying to insert contract information on contract table but before that I want to check the studentID in student table and contactId in contact table if they exist I want to insert into the contr...more >>

I generally use the following code to call a stored procedure: sSQL = "Exec MySP " & param1 & ", " & param2 [Set oRS =] oConn.Execute (sSQL) .... or something like this. AFAIK it's quite an efficient way to query a DB via ADO - Less overhead than a command object. However, I'm not sure...more >>

Hi, I was wondering how I can execute and insert or an update based on a condition? Should I pass a parameter in 'INSERT' or something to that effect? Moe ...more >>

How do you guys design tables that have foreign keys to multiple tables and have them CASCADE UPDATE/CASCADE DELETE? (Is there another way to allow this to work without using triggers?) DDL located at the end of the post. Basically, this is what I have. (Primary Keys listed) Branch ...more >>

SELECT Orders.OrderID, Employees.FirstName, Employees.LastName FROM Orders INNER JOIN Employees ON Orders.EmployeeID = Employees.EmployeeID the above qurey comes from the northwind database in sql 2000 i need to write a update query that will change orderI...more >>

Can someone please point the way for me to insert a digit after the first digit in all my column values. The examples are 102 becomes 1001 205 becomes 2005 312 becomes 3012 I simply need to insert a zero so as to base my division numbering system on thousands rather than hundreds. It tur...more >>

Hi There, I want to use an order by clause in this query below, however in enterprise manger when I try to modify the procedure and save it i get the error "icorrrect syntax near order. Moe - thanks in advance for your answer SELECT A.Location,B.manufacturer,B.listprice FROM tbLocation...more >>

Hi, I have a field that is comma-delimited. I want to parse it out and have the results display in rows. For example: Before Table: CustomerOrders CustomerID Orders 3 2,45,33,44 4 1,12,29,54,7 5 ...more >>

Is there a way to build a 2nd result set based on the items in the first result set. eg. select ID from tableA where tableA.parm1 = @parm1 then, select col1, col2 from tableB where tableB.ID = tableA.ID In other words, there may be many entries in tableB for each ID in tableA. Thanks...more >>

This query is taking forever to execute. Does anybody have any idea? select c.Name, f.File_Name from Components c inner join Component_Versions cv on cv.Component_ID = c.Component_ID inner join Files f on f.Component_Version_ID = cv.Component_Version_ID inner join Media_Structure m on...more >>

Hi guys, I have a small question about following BACKUP command, which can FULLY BACKUP Database1 to location C:\Database1.BAK. (OVERWRITE MODE) How can I modify this statement so it only OVERWRITES BAK file (Database1.BAK) if Database1.BAK is more than 14 days old? I know how to do it ...more >>

We have an existing ASP application that is supported by SQL 2000. We plan on utilizing a third party COM object for which we'll be writing a wrapper. Initial thoughts are to write a VB6 COM wrapper around the 3rd party dll but we'd like to start a slow migration to .NET; I say slow because we do...more >>

Hello, Is there a way to compare the table structure to another table on a different DB? Thanks Lee...more >>

Hi, I need pass parameter to a view. Is this possible? See mu view: (where has a /* param */ is my paramiter. it is a variation of 1 to 12) IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = 'rptData_view') DROP VIEW rptData_view GO CREATE VIEW rptD...more >>

Hi, I have the following query that is taking over an hour to return data from a progress database. If a similar query (in progress language) takes 5 mins. Mybe something is wrong with my query? select archive.number,ti-date,pub.name,SUM(quantity + short - damaged) from archive,customer,prod,p...more >>

Hi everyone! One small question: is it possible to add a new column to an existing table NOT as the last one? Example: existing table {column A int column C nvarchar...} how can I add a column B (say nvarchar for example) as the second one, to make the new table: new table {colum...more >>

I am trying to adapt a stored procedure to be run on either of 2 servers and have a part of it always use a database on a particular server. Originally I tried using just a 4-part qualified name but read somewhere that this was not possible for DDL statements (e.g. in my case, CREATE TABLE). So,...more >>

Can someone please help me with this query? I don't want Line 2 (see result below) as I only want rows with maximum t_seqn for each group of ttibom010100.t_mitm, ttibom010100.t_pono Unique key is ttibom010100.t_mitm, ttibom010100.t_pono, ttibom010100.t_seqn How can I modify this query to ...more >>

Is there a way to test if a table already exists in sql? I want to run a stored procedure that would do something like this. Basically loop through until it found an instance of a table that did not exist, create one temporary and then Drop it. i = 0; Loop1 if i < 20 if table[i]...more >>

What effect does the optimizer play in this? If your running a lot of small queries and they are under the cost threshold, the optimizer won't generate parallel plans, right? Also, even if the cost threshold is breached, if the number crunching involves asnychronous processing and mutiple queries...more >>

I originally posted this in microsoft.public.dotnet.framework.adonet under the title of "IF" test in SP evals to True and False at same time using DataAdapter.FillSchema() I wasn't really getting anywhere. I thought I might try in these two to see if anyone else has any thoughts. microso...more >>

This should be an easy one, but I am struggling where to find it in bol. I have a colum with this for a value (Char datatype): RT:10380.00 MS: 0.00 I want to Select everything to the right of the 'RT:' and left of the 'MS:' (10380.00) Also, do I use CONVERT or CAST to change it i...more >>

Hi All, Here are my problem. I have the following table and I need to split the data into 3 fields based on space. But there are many problems with the data. For ex. with Canadian postal code (T7P 2C3) also with the country (U S A). Here is the DDL: CREATE TABLE [dbo].[DealerAddress] (...more >>

I have a need for a query to return a balance UNLESS the amount does not exceed 3% of a price, in which case the balance WILL be 3% of the price. Is there a way to structure a query in an if-then-else format? Since I'm binding the SP directly to a report, I'd rather not deal with this in the pro...more >>

Is there a way to either pass parameters to a view OR declare variables within a view? Thanks -- Warren...more >>

Greetings. I'm on a win 2000 domain environment. From my client box, using Access 2000, I'm trying to access store procedures on my SQL Server 2000. I cannot, this is what i do. On client machine: 1. Using MS Access 2000, I create New/Project(Existing Db) 2. Under Connection, I select t...more >>

Is there an easy way to get the last day of the month using T-SQL date time functions? THis will get me the first of the month: SELECT @EndDate= CAST(DATEPART(mm, GETDATE()) as varchar(2)) + '/1/' + CAST(DATEPART(yyyy, GETDATE()) as varchar(4)) But to get the end of the month I think I ...more >>

I have a users table that has an advertisingSource column and a buisnessType column. my boss wants a table that has the businessType as one axis and the advertisingSource as another axis. So I can "SELECT DISTINCT(businessType) FROM users" to get a table with the business types, but then h...more >>

Hi folks, Can someone help me out with a query, i'll try to explain in as much detail as possible so please forgive the long post. I have a table that stores some messages details, messages can be sent or queued, and can be sent from a variety of devices. So a simplfied version of the table...more >>

I execute following statement on my local machine's Query analyzer. xp_get_mapi_default_profile I got a message, "Could not find a default MAPI profile" My machine is windows XP, so I went to Control Panel --> mail --> Show Profile, I see MS Exchange settings as my default profile. ...more >>

I have few SQL Nightly jobs that run fine one night and other night they will either hung or break out with DEADLOCK ERROR. I looked into the queries and tried to use NOLOCK hint for select and provided index hint for update and still these jobs are victim on DEADLOCK issue. What are som...more >>

I have the following query: DECLARE @PartnerID int SET @PartnerID = 8899 SELECT DISTINCT(FM.FileNumber), CONVERT(varchar(50), FM.OpenedDate, 101) AS OpenedDate FROM FileMain FM JOIN FilePartnerRel FPR ON FPR.FileID = FM.FileID AND (FPR.PartnerCompanyID = @PartnerID ...more >>

Hi, Let say I have the following data for simplicity: ------------ x y x ------------ A 1 1 B 2 2 A 1 1 ------------ I want to compose a t-sql statement (hopefully just one statment) to get a count of 2. First and last are dupes. If I had one column, say...more >>

I just enabled this trace to find more information on deadlocks if they occur. Where do I find the information pertaining to this. Would it be in teh logs? BOL just talks about enabling it but not where to find the detailed info. Thanks ...more >>

I have the following query, SELECT CA.RegNo, CA.ApptDoctor, CA.ApptDate, CA.ApptTime, CA.ApptNotes, AC.ApptCatName, C.CliLName, C.CliMM, C.CliFName FROM CliDrAppts.dbo.tblCliAppts CA INNER JOIN CliCore.dbo.tblClients C ON CA.RegNo = C.RegNo INNER JOIN tblCliApptCat AC ON CA.ApptCatID = AC.App...more >>

Hi all, Xposting this in vb.Net also ..just to get some answers!! Has anybody tried doing this?? I followed this article to the T, but still have problems in the last step.. When I try the last step (as shown below) I get a "Invalid Class String" ....this tells me for some reason my VB6 wrappe...more >>

I'm deparing with a problem. I have two tables. The first one has a general information about article A. The second one has more detailed information about that article. This works fine but sometimes sql server doesn't save the second table. I know that this happens in the first record of the da...more >>

I'm running this query, and I don't really know much about the underlying database but here's the problem: It works fine without the Count() function and returns about six rows all nicely grouped. If I add the Count() function, I get about a hundred rows, each with a different count. Wi...more >>

I have an SP in which I want to fill a temp table with different values depending on the value of a parameter. The code I am trying is below. I get an error that '#temp" already exists in the database when I try to save this Sp? How can I impelement this functionality? Wayne ===============...more >>

I just bought and installed a quad-processor machine with a few terabytes of hard drive space and gigglebytes of membory. (so much memory it makes me giggle to think about it!) Anyway, my question is this: Is there any way to 'assign' one or two of the processors for certain work, and use th...more >>

Hello SQL Gurus! Several years ago I was chastised by a DBA for using sub-querys. And he would redo my query without it. Well, that was then, and this is now, and I'll be damned if I can remember/figure out how to do this simple thing without using a sub-query Basically I have 2 tables; T...more >>

How can you transfer encrypted Procs from one DB to another. I tried copy DB objects task in DTS and that didn't bring it over?? Is that the only thing that is used to Xfer Encrypted Procs or is there any other thing as well ?? -Nitin ...more >>

Hi All, We require double byte support for Japanese (and other similar style) character sets. Exchange Server 2000 supports 2byte OK and SQLS 2000 supports 2byte OK. Within their own environment we have no problems. We have developed some SQL Stored Procedures that generate automatic mail b...more >>

Is beta 2 out? ...more >>

Hi, Thank's for your reply I would like to know witch tables are being in use for a specific spid ! without using Profiler ! ...more >>

i posted this in programming but nobody answered so I thought I might try here aswell. Can anyone help us with the following... we've got lots of DTS packages stored on SQL server, however we have noticed that it takes the login credentials for what to save the DTS package owner as from windows,...more >>

My evaluation version of sql server recently expired. After attaching my database to another sql server i found that I had lost my dts package containing an Active X script. Can anyone tell me where this script is lurking in my pc? Thanks Dan ...more >>

I need to create (lots of) stored procedures that operate transactionally, but may call each other or be called as part of a larger operation. My understanding of the way that transactions nest is that I should be able to have each SP begin and commit a transaction within itself, and if ...more >>

I've got problem with WHILE loop. Here is the code: .... while (select (@k = field1) from table1, table2 where table1.ID=table2.tID and table2.tID=@tid1) begin Insert into table1 values (@k, @val2,@val1) end It seems that I can't do "select (@k = field1)" in the while loop. Is there...more >>

Hi, =20 I want to perform a search based on the content of a table: =20 CREATE TABLE [dbo].[tblTest] ( [id] [int] IDENTITY (1, 1) NOT NULL , [SomeText] [nvarchar] (50) COLLATE Latin1_General_CI_AS NULL , [SomeDates] [datetime] NULL , [SomeNumbers] [int...more >>

Sorry if the subject is not clear. What I mean is, when I want to assign sequence numbers (not just unique numbers) to a table, based on a unique key, I do the following: -- TABLE Source(k and some other fields) SELECT * INTO #T FROM Source CREATE UNIQUE CLUSTERED INDEX MyKey ON #T(MyKey) ...more >>

Hi There, I am pretty new to sql server and would like to know if there is a good resource online to explain datatypes and peformance tuning techniques? Moe ...more >>