Hello all, I try to write a stored procedure with a table_name input parameter and a nRow output parameter. As I can't use the table_name variable directly in a SQL statement, I have to generate a dynamical SQL command. However, I have problems because of the OUTPUT parameter: CREATE PRO...more >>

Hi guys, I've created a web application using ASP together with SQL Server as our db source, running through IIS 6 on a Winows Server 2003 platform. This application retrieves a list of customer codes from our db, so records returned could be as many as 2000+ for any single transaction. ...more >>

Hello, I would like to return a character string and a result set from a single stored procedure. How can I do this? Thank you, Anthony...more >>

Is it possible to define a primary key in a view? My View is a 5 tables. Table 1 holds a primary key that is used as foreign key in the other 4. It is a view i have made on an SQL 2000 server best regards Trond ...more >>

Hi! I have this scenario: One big table which will be populated with playlists; each playlist is defined by: 1) tv channel 2) playlist date Each playlist has many rows; each rows is defined by: 1) tv channel (as said before) 2) playlist date (as said before) 3) playlist onair hour ...more >>

Hello! I am using very simple query... Select * From Table1 Where Active = 1 Now I would like to preform Join if input parameter X is Not Null? Could someone provide me a sample..? I tried myself, but couldnt get it working. Thanks! James ...more >>

I have table with locationID,paletteNumber and producID as primary key. Now I would like to find, how many palletes are on some location. It's simple: SELECT locID,COUNT(DISTINCT paletteNumber) as paletteCount FROM table GROUP BY locID But now, I would like to create indexed view. COUNT...more >>

I've created a stored procedure that executes horribly slow when I run it (either from our application or from Query Analyzer) However, when I paste the code from the stored proc into Query Analyzer, I get the result sets back in a matter of seconds. I've recreated the stored proc with a dif...more >>

Hello group, I'm struggling on a simple function in SQL 2k. I'm trying to return the last day of the previous quarter for a given date provided it does not fall on a Saturday or Sunday in which case I take the day(s) before. For the end of year (4th quarter), I need to take by default a day be...more >>

Hi! Instead of creating one stored procedure for insert and another one for update, i did one with both. Should I create one stored procedure for each? Should i create one stored procedure for Insert/Update/Select ? ALTER PROCEDURE dbo.[Inserir Atualizar Conta Bancaria] ( @ContaBanca...more >>

Hi, I need to use the identity function as below except SQL server doesn't appear to like it unless I specify the seed as a literal. select identity(int, @seed, 1) into .... Is there a possible work around? Thanks, Bryan ...more >>

Hello! Here's my situation: t1_2005_1 t2_2005_2 t3_2005_3 All the tables are the same but each of the table holds data for it's obvious date. So t1_2005_1 has data for January, 2005 etc. All tables have field 'start_time' (datetime(8)). They also have clustered index on this field an...more >>

Hi, I'm using SQL 2000 and have made a stored procedure that inserts a record in a table and returns the primary key back to my Active Server Page application. My task is to create a new logtask when a user have made an action on the application. I want to tell what my intetion by this sto...more >>

What they heck are they? I was talking to a copmany that does extraction of data using what he called triggers on shadow tables. I had never heard of them and couldn't find anything on them in BOL. Any info would be most appreciated. Bob Castleman DBA Poseur ...more >>

Does anybody have a good way of generating a UNIQUE 6 digit random number ? I have a primary key on the table. Thanks, Justin ...more >>

Hi, friend, thanks for your help! To make it short, the problem is: CREATE TABLE [dbo].[OrderDetail] ( [itemID] [int] NOT NULL , -- primary key [orderID] [int] NOT NULL , -- foreign key (1 order id could have multiple itemID) [productID] [int] NULL , [quantity] [int] NULL , [price]...more >>

I am trying to add a new view to my database in server explorer. But when I right click the Views node in the database I dont see the "New View" item. I just get the refresh and propertys items. Is there some setting I need to change to be able to do this?...more >>

I have a quite lengthy Update query that is working in its current condition BUT I don't feel that I have coded this query as effectively as I should to minimize the transactions because of the many update statements that I'm doing. The Updates all occur within a single table but I don't ...more >>

I have a large table with a column in all-caps. I want to capitalize the first character and make the rest lower case. I understand that this will not work for things like "MCALLISTER" where the "A" should be capitalized; I only need the basics. Can anyone recommend a simple statement that ...more >>

Hi I have a table create table tmp_sales ( c_code char(6) NOT NULL, c_type char(6) NOT NULL, c_period char(3) NOT NULL, c_prd_yr int NULL, c_prd_mon smallint NULL, c_prd_num char(1) NULL, c_n...more >>

How do I format a date to display as 'dd month yyyy'? ex. '31 October 2004' Thanks...more >>

Here's an easy one for somebody. Consider this as my table (Table1): RecordNum NoteNum NoteText 100 1 I'm here. 100 2 I'm there. 100 3 I'm everywhere. I realize a better idea would be to not have a table like this, but I only query the table, I ...more >>

Just a sanity check here, it *seems* as if within an update trigger the UPDATE(colname) function is going to be true if that column appears in the update statement, EVEN IF THE VALUE ASSIGNED IS THE SAME AS ALREADY IN THE DATABASE. To determine if the value has changed, one can do a join from ...more >>

(Not sure "flattening is the correct term, but anyway)... Given the following DDL (not the original, but simplified for the sake of the example): ---snip--- CREATE TABLE [dbo].[ChildTable] ( [ID] [int] NOT NULL , [ParentID] [int] NOT NULL , [ChildValue] [varchar] (50) NOT NULL...more >>

Can I have more than 1 statement fire on an insert trigger as I have illustrated below, or do I use 2 insert triggers? .......or is there a better solution? Thanks Soc. +++++++++++++++++++++++++++++++++++++++++++++ CREATE TRIGGER [TRIG_trig1] ON [dbo].[table1] FOR INSERT AS update tab...more >>

I would like to know if it is possible to write query on other way - more performance. Instead of "s.ART_SIF IN(SELECT ART_SIF FROM OBJSTA WHERE sta_ozn=@STA_OZN)" some kind of JOIN. Query is: select l.LOK_OZN,s.ART_SIF from lokStanje l LEFT JOIN objSta s ON l.LOK_OZN=s.STA_OZN AND s.A...more >>

Is it possible to select the results from a stored procedure like a table? Select * From SP1 Obviously, this does not work. But is there a way to do something like this? If yes, what would that look like? the idea is to package a bunch of tsql code like into an sp and use the sp lik...more >>

Hi all, Is it possible to add a column to a table in a particular position? For instance, if I had a table with columns A, B, D, E and I want to insert a new column called 'C' but I want it to be added between columns B and D instead of at the end of the table, is this possible? Thanks!...more >>

Hi I am creating a temp table in a sp but when running the query it returns no records. This is a bit of a messy SP but can someone just have a glance and see if something is out of place. Thanks. alter PROCEDURE qfm_GetEventSearchResult1( @param nvarchar(4000)= NULL, @maxRecords nvar...more >>

Hello all, I know this can be done, because ive done it in other instances, but i cannot get this one time to work, and i cant figure out why. I have a table which houses iis log data. one of the fields is csUriStem, giving the whole page requested as such: 'http://www.microsoft.com/ital...more >>

Hello, i have a trigger on every table to check that only one row is to handle. If there are more rows i want to show an error message with the name of the trigger in which the error occured. Now i have nearly 100 tables and also so much triggers which are all identically from the statements ...more >>

Table TERMIN'. Scan count 1508, logical reads 3492, physical reads 16, read-ahead reads 93. What are Scan count, logical reads, physical reads, read-ahead reads? What values are good and how to fix bad values? ...more >>

Help! When I run the following query from query analyzer it successfully retrieves rows from an Oracle database. select * from openrowset('msdaora','devods';'userx';'pwordx', 'select * from tbl_cust') But when I try to do an update queries it fails: select * from openrowset('msdaora',...more >>

I am trying to migrate a set of MS Access queries to SQL that call Access VBA code. For example, this is typical: SELECT Name, DOB, VeryComplexFunction([Value]) AS NumericResult FROM Table where, VeryComplexFunction is a function that is in an Access VBA code module All the A...more >>

Thanks to help I've already received from people through this group, I've made quite a lot of progress in generating and running large pieces dynamic SQL. I'm very grateful for that, and would like to ask another question. Currently, I have managed to write SQL statements of many tens of thou...more >>

I have 18 tables that are all related by the primary key. When I join all the fields together for reporting, it only shows the records that have all the data filled in. How can I find which tables don't have a record? Thanks, Drew ...more >>

Hi all, I have a folder called Attach which contains a subfolder folder each userid. Everyone has full access to the the files in the Attach folder. But the subfolders are private to each user. I need to write a sql stored procedure to copy files from the subfolders into the Attach folder ...more >>

Can someone show me how to convert from a 12hr timestamp to 24hr in a select statement? ...more >>

No matter what I do I cant get this update statement to work.. UPDATE MedOrder SET AdditionalRefills = 0 WHERE AdditionalRefills IS NULL I get this error message: Server: Msg 512, Level 16, State 1, Procedure MEDORDER_MODIFIED, Line 6 Subquery returned more than 1 value. This is not ...more >>

I have a table with 5 columns. Column 1 is the ID and is unique Column 2 is a number and has many duplicates Column 3-5 are just desicrptions It holds 60,000 products but many man of them are duplicates, the way I know is that they have the same code in column # 2 How can I select only ...more >>

Does anyone know how to get the Schedule_ID of a job running from inside the job (e.g. system variable for current schedule executing like the @@procID for procedures). Specifically I am trying to associate parameters with different schedules and stamp records based on the schedule ID they wer...more >>

I would like to create a computed column (Sample_Date) on Table A on Column Name Date_Time listed below. I would like the Sample_Date column data type to be in Datetime format. Also to trigger on a select statement to execute the computed column (Sample_Date) Please help me with this...more >>

Hi, Would someone please evaluate this trigger and tell me what we've done = wrong. It's not 100% of what we need. Our objective is to never allow = the record with spe_pk =3D 0, pequid =3D 0 and peqdesc =3D 'Not = Selected' to be deleted, renamed or updated. It's a key record for = legacy ...more >>

Hi I need to format the output of a numeric value from SQL-server with a chosen thousand-separator and decimal-separator. The Access equivalent would be something like: Select format(myNumber, "#.###,##") as myFormattedNumber from myTable; I have searched the net for a solution, and foun...more >>

Hi, friend, I am using a cursor in a stored procedure like the follows: DECLARE prevOrder_cursor CURSOR FOR SELECT orderID FROM Orders WHERE catID = @catID ORDER BY orderID DESC OPEN prevOrder_cursor FETCH NEXT FROM prevOrder_cursor INTO @candOrderID WHILE @@FETCH...more >>

Hi, I want to query few rows from a table. I don't want to get a row, where certian column has a NULL value. How can I do that?...more >>

How can I update field labor_day_count in Calendar table (with also includes fields cal_date, working_day) with the work day number? For example, I need to update all the dates for July 2005 so for '7/1', Calendar.labor_day_count=1 ('7/1' is the 1st working day of july), '7/5' = 2 ('7/2', '7...more >>

I have three tables, [Versions], [RCF Numbers] and [Call Counts]. [Versions] is in a one to many relationship with [RCF Numbers], and [RCF Numbers] is one to many with [Call Counts]. I need to update [Versions].[Number Months Reported] with the highest value in [Call Counts].[Month Number] ...more >>

Hello, I want to know if there is a specific name for distinguishing between these two sql statements. I don't believe the first one would be valid in oracle, but both would be valid in sql server. Thus, I want to know the name used to talk about the specific sql server syntax identified i...more >>

Dear gurus, I was wondering how could I spend less time doing DML sentences, e.g, select: instead of to have write this sentence time and time: select * from tblwholesalers be able to do this: s tblwholesalers Thanks for your support,...more >>

Hello. I am looking to write a query that will allow me to count all pos_entry_code = 921 at all stores (card_acceptor_identification). Then have a total for each store. Is this possible? This is what I have so far. Do I need a group by and is there all way to list all the stores just not...more >>

First thanks to Mike and Baie for helping me before. I have a set of data similar to the below one. issueID IssueDate 1 1/17/2005 2 1/18/2005 3 1/19/2005 4 1/24/2005 5 1/27/2005 6 2/7/2005 I need to query the set between 2 dates for example, 1/3/2005 and 6/5/2005 and what I ne...more >>

Hi What are the steps EXACTLY in order to change a database model from bulk insert to Full or vice/versa? ... so that it is immediately becomes effective i.e. Stopping/starting service? Do you have to delete or backup the old transaction log first along? Could this be done without any do...more >>

Hi, I hope someone can help, I need to produce an incremental figure in a query. I have turned it in evry single way and I don't know how to do it. In a table I have the following: Order ItemNumber Abcd 35188 Abcd 65461 Abcd 78890 Cdef 32182 Cdef 65498 Cdef 21549 … … In ...more >>

Hi, If we use join in query and put where condition at end of the query statement. select * from tab1 t1 inner join table2 t2 on t1.id=t2.id where t1.name='xyz' And second option is condition put with join with and condition. select * from tab1 t1 inner join table2 t2 on t1.id=t2.id ...more >>

Hi, We use temporary table for storing data in stored procedure. If instead of temporary table we use table variable then any problem (because data may be in bulk also so, and table variable dump it into memory). Milind,...more >>

Hi ! I'd like to filter only the stored procedure when executing sp_depends on a table. How can I do that easily ? Thx for your help. Jeff...more >>

Hi I need to select 2 different results from the same table, the first is the values at one time of day, the second is the same values at a different time of day, then perform a subtraction of the values which go in a seperate column, and if possible show me the values before the subtraction...more >>

Dear All I have a database table in ServerA called MyTable, which contains a column called ID. I would like to compare it against a table called MyOtherTable in ServerB comaring the ID field in there, somthing like SELECT ID from MyTable where ID not in (SELECT ID from ServerB.MyotherT...more >>

I've found lots of information about using OPENDATASOURCE() to access to Excel files. And I noticed that the value of "Extended properties=" part would be "Excel5.0" or "Excel8.0". Now I have no idea about the differences between these 2 values. I guess there must be some differences but I still ...more >>

I need to return rows for the last five years. So, if today is 7th April 2005, the row with the datetime value '2000-04-07' should not be returned but the row with '2000-04-07 00:00:01' should. I've come up with the following: SELECT * FROM MyTable WHERE CONVERT(CHAR(19), my_datetime_co...more >>

Hi I am reading AD Users, who are organised in different OU in my AD. In the OU i have some organisational information that i would like to link together with my AD Users when i make my SQL view. How can i join these 2 information in my SQL View ?...more >>

Hi, Is there any way that I can find where the SQL server stored our Create View or Create Procedure statement (the text), I try to find on all system tables but can not find it. Thanks, Ricard...more >>

Hey. I just wanted to know what the best way of storing images is- scores of quite large jpegs. Are there any best practices I should know about? Thanks Cheers Justin Dutoit ...more >>

Let's imagine that we have "conditional" start of DTS package (for this post I will call it DoIt) DTS package DoIt must be started when particular event occur. Event is: we have an empty folder on the disk. When an outside program send a file (for example message.txt) in that folder - it is a...more >>

Hi, Execution plan diagram in Query Analyzer , How can i know follwing point : 1. Total execution time ? 2. Individual T-SQL Execution Time ? 3. What Cost % indicate ? ...more >>

Hello I'm using XML Templates to retrieve data from MS SQL Server 2000, and return it as XML (Using FOR XML). Is there an overview of what I can do with XML templates anywhere? I need to add a bit of logic to a couple of templates. Example: A user database. I've made a template that takes 2...more >>