How can I copy the data from varchar to nvarchar from one table to another? ...more >>

Hi, I read an earlier posting about hierarchical data which is a new area for me. I found Vyas article "Working with hierarchical data in SQL Server databases" closely resembles the solution of my problem but unfortunately his example doesn't generate any result for me. I did several approa...more >>

Hi, Can someone help me with the following I get a file each week with different name. Using Nothwind Example. Suppose I get a file CustomersA1. Next week I get the file with the name CustomersA8. This file that I get each week has to go in Customers table ..How do I tell DTS that the...more >>

How do I issue T-SQL for record level locking ? ...more >>

This was originally posted to comp.databases.ms-sqlserver, but no response over there, so... Can anybody tell me why a) when running a stored proc from an asp page to return a recordset the command succeeds if the sp queries an existing table directly, but b) if the stored proc populates resu...more >>

I am trying to change a field that is part of the primary key. The data was migrated from a different system (Real World). We had to convert the double quote to a char they don't use (@) and now I am trying to run a REPLACE to change the @ back to a double quote. For some reason, this is ca...more >>

I'm looking for a complete example on how can I recreate a table objects and columns? First drop everything and then with alter table, recreate them. I would like to see how you add PK, FK, contraints and indexes on columns. Also, I saw that it deletes the values for fields. What should I use...more >>

Is there a way in sql to do an bitwise or as a aggregate function just like a SUM or COUNT? Example: SELECT SUM(status) FROM LineStatuses WHERE GroupID = @GroupID But want something like this: SELECT OR(status) FROM LineStatuses WHERE GroupID = @GroupID I store a bitwise stat...more >>

I have a script where depending on the server name, it want it to execute against a different database. At the top of the script, I have code: if @@servername = 'WS-DELL' use [db1] else use [db2] GO .... rest of the script the problem is that if either of these databases don't exists on th...more >>

I think my problem is 'parameter sniffing' as I've read in other posts, but I've tried several solutions and it's actually gotten slower. I've tried WITH RECOMPILE, but no help. I've added local variables and set them to the parameters. With hard coded values this sp runs in <30 seconds. With...more >>

Hi, I read this tip in a article: "For very large joins, consider placing the tables to be joined in separate physical files in the same filegroup" How can I force SQL Server to create a table in filegroup on a particular file? As far as I know, I can tell SQL Server to create table on desire...more >>

What is the best way to determine if i need to create a regular index or a clustered index? Thanks. -- Michael Tissington http://www.oaklodge.com http://www.tabtag.com ...more >>

I have been trying to come to terms with using xp_smtp_sendmail and come up against a brick wall. The script below works and the mail arrives as it should from the Northwind DB, I am unsure on two things. 1. How can I get all the local variables @compID, @contact etc into the @message body, ...more >>

I have select: SELECT p.crossell,p.tsAd_ID,p.id_Izdelka,count(p.id_Izdelka)as kolicina, sum(p.vrednost) as prodaja FROM cpoNarocilnicaPost p INNER JOIN cpoNarocilnica n ON p.[ID]=3Dn.[ID] INNER JOIN cpoProducts i ON p.ID_IZDELKA=3Di.izd_ID WHERE p.tsAd_ID is not null GROUP BY p.tsAd_ID,p.id_...more >>

I'm stumped and I'm no db expert... I have an Order submitted from a website and I need to view the order information for a particular order number that is stored in 4 tables, with the possibility of multiple rows in two tables. If I were to do it with separate queries it would look like this...more >>

I have a table that has columns for value and time. I need to write a query that returns the row that is closest to a particular time, e.g. the row that has a time tag closest to 10/15/2004 12:00:00. How can I do this? Thanks,...more >>

I am trying to get the result of an equation (which has many decimal places) to round to the nearest whole number. But everytime I try casting it to round I get a zero. Here is the sql script: ----------------------- declare @lat1 as decimal declare @lon1 as decimal declare @lat2 as decim...more >>

I have created a report using the SQL Server Reporting tool. The report shows only the group header lines in which I have accumulated totals. I would like to alternate the shading of the lines. How do I alternate the shading for the group header lines....more >>

When i execute a Query from an Oracle Linked server i got 99 rows less then the original data. when i run The query in oracle i got 999 rows , when i run in SQL using openquery i got 900 rows. Any idea..? ...more >>

is there a way to get the difference between two fields using t-sql without having to add 1 to the difference? ie. (endrange - startrange) + 1 (what i mean is if startrange = 10 and endrange = 20, if you're counting how many of the items exist it's not just 20-10 because 20 and 10 are included, ...more >>

Hi, I've 2 questions: 1 - do you know any better way to send mails using MS SQL Server than xp_smtp_sendmail? 2 - message I want to send by xp_smtp_sendmail contains national chars. I've tried to set @codepage parameter, but there is no difference: all national chars are replaced by "pure"...more >>

Anyone have any code, given a sproc, to generate the corresponding ADO parameter collection? Jeff ...more >>

How do you reference a form or stored procedure in sql? I can't find any syntax reference to find out how to write it. varible=FormName!fieldvalue.value etc....more >>

Is there a way to create a list of all databases and their users? It would be great if it would return that user's permissions to the database as well. Thanks. Joshua ...more >>

Hi there, Im wanting to write a SQL Script that looks through the database and checks if tables exists, if they do i want to check all the coloumns that should be in it exist, if they dont then add the coloums, and if the table doesnt exsist then create it!! I know it seems alot todo but i ...more >>

Setting... SQL Server 2000 1 table...tblBusinessListing Fields ID BusinessName ContactFirstName ContactLastName Phone Fax Email My problem is some of the contacts have multiple business and multiple fax numbers. I wish to send only one fax to each individual contact. In the r...more >>

Hi Freinds, I know how to get the .mdf filename just by running select * From dbo.sysdatabases is there any way ti know the ldf filename too? Thanks in advance, Pat ...more >>

I want to write a Query Which takes the input parameters as StartDate EndDate FirstPymtDate LastpymtDate Depending on the StartDate and EndDate I have to Check if the FirstPymtDate and LastpymtDate was withing the Current Quarter. Now if the user send me the Startdate and Enddate which ...more >>

i have a few different SQL servers. one is a combination of all, and the others are stand alone in diff locations. I need a query that compares a table in my centralized database and depending on the Branch Name I need to query against that locations table. I can use 20 if statements in a ...more >>

Hi all, Im trying to get cerating dated values for a table which has the following schema: TableA(name, entrydate, floatvalue) It's a historical table, so has 100 items (say) for each day in the last year. What I need is the following: All floatvalues for any names that have an entryda...more >>

Hi Friends, How can I know through script that who has the SA level account on any given server? Thanks in advance, Pat ...more >>

Hi, Can anybody tell me if I can find if the given table has an identity column, through the Transact-SQL? Arun ...more >>

i am trying to create a column which calculates an index of another column. can anyone help? for example, what would the sql code be to create column 2 below? values index 4 100 1 33 2 67 5 167 3 100 ...more >>

We have a job that drops table1 then recreates it. The problem is that this job often runs 1.5 mins to 2 mins during which any queries run against table1 often produce erroneous or incomplete results. Is there a way to make this job select into a holding table then drop table1 and rename the h...more >>

This works: select smplProfile.SampleSourceArchiveKey, AltColoc.CompanyLocationKey, smplProfile.CanonicalCompanyName smplCompanyName, AltColoc.CanonicalName AltCompanyName, smpl.CompanyAddress1, AltColoc.AlternateAddress1 as AltAddress1, dbo.GetFirstWord(AltColoc.AlternateAddress1)...more >>

I am trying to track down where the description field is stored when creating a table in enterprise manager. Someone mentioned to me "extended properties" and a function called "fn_listextendedproperty" (where do I find that?); anyway, what I did was generate a sql script for a table with a des...more >>

Hi all, We are currently working on a version upgrade script for our application. As part of the upgrade we need to increase the size of a VARCHAR column that is used as part of a PK. BOL states: "ALTER COLUMN... .... The altered column cannot be: ... * Used in a PRIMARY KEY or [FORE...more >>

I was Bcp-ing out from a table using -b 5000 and in other case 'h "ROWS_PER_BATCH = 5000" and neither one of them worked. I was seeing the output in command prompt and it was saying 1000 rows copied. Why did bcp not take 5000 rows in a batch to process?? Am I missing something? Thanks in advna...more >>

I have this query: Insert into Products Select max(numOfItems) From Orders Is there a way to ensure the numOfItems is always at a maximum? Thanks in advance ...more >>

How is it possible to convert MM:SS to SS? E.g. 160:40 to 42.67. Thanks. Jay. ...more >>

When showing the execution plan - which is a bad thing... Bookmark lookup Index Seek Index Scan what are the differences? thanks, Justin ...more >>

read a tip about MODELING COLUMNS AS ROWS. i think it is a nogooder. other opinions? /k The tip: MODELING COLUMNS AS ROWS If you think about it, the logical consequence of normalization is that you should model a table's columns, aside from the Primary Key (PK), as rows, not colu...more >>

I have set up a link server to the AS400and I can see the tables in the Enterprise Manager, How do I query it so I can use the data in my SQL Server database....more >>

Hi, My ROUND function is not working. I get the foll error Server: Msg 189, Level 15, State 1, Line 4 The round function requires 2 to 3 arguments. This is my query select store,name,quantity1,prevquan,modif, select store,name,quantity1,prevquan,modif, round(cast((sum(case deli when 0 t...more >>

hi guys: I need to format a string to Date Type. looks like buttom sample: 20020202 ---> 2002/02/02 which function can achieve this use Sql. thanks a lot jiangyh...more >>

Hello, I have a strange situation. When I use variables for search criteria SQL server selects not quite good plan as if I use constants. For example. 1) scenario ----------------------------------------------------------------- declare @OrderID1 int select @OrderID1 = 51244372 declare ...more >>

Hi All, We are using SQL2k. Recently one of our tables lost all its data...Is there a way we can find what happened which caused all this? logs etc...where can I find trace information pertaining to this data lost. TIA ...more >>

It looks like my yesterday post did not make it. Trying again: I need to modify an existing simple database catalog, where optional product version attributes are all listed in a text/string field. And turn it into a shopping cart-type catalog where a user will be presented with a list of "...more >>

Anyone know how I can delete all of the rows from every "user table" where the tablename ends with an underscore, i.e. USE DBName DELETE * FROM tables WHERE tablenames LIKE '%_' GO -- Patrick Rouse Microsoft MVP - Terminal Server http://www.workthin.com...more >>

select custnumber, total_amt, state, city from sales group by state,city, order by state,city, total_amt desc How can I modify the above query to show the top five spending customer in each city it seems simple but I can't work it out thanks for any help ...more >>

I'm having trouble exceeding 256 characters in a variable I declare in my stored procedure. I've included the stored procedure below as well as the output. The reason that I'm writing my sp this way is that the input variable, @LeadList, is a list of comma seperated numbers. It is the @SQL ...more >>

I'm finding myself repeating the following code in my stored procedure to check for NULL and an empty string, and wondered if a function was in order: (@InfoDate varchar(20) is an input parameter to the sproc) if @InfoDate is not null and @InfoDate <> '' begin ... Is there a way to co...more >>

Hi, I have created a simple stored procedure to handle updates to a SQL table. I'm not sure what the best way is to handle errors. For example, a couple of fields are required fields and hence do not allow nulls. I have written a front end form in Access and am not sure how to handle err...more >>

Hi I am trying to write an update stored procedure that would change the value of the first ten rows of a int column with a NULL value to the number 1. John ...more >>

I have the following adjacency list model in three tables: CREATE TABLE [dbo].[tblGRANTGrant] ( [Grant_ID] [int] IDENTITY (1, 1) NOT NULL , [Grant_Name] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [Year_ID] [int] NULL , [AprvlAuth_ID] [int] NULL ) ON [PRIMARY] GO CR...more >>

I want to restore to a point in time. However, the checkbox is not enabled. Does anyone know what is wrong with my setup. Thanks...more >>

Is there a function that determines the number of days for each month so I can calculate the average monthly value of an account? Select avg(Value) (By Number of Days in a month) Group by Acct# Acct# Value BusDate 1101 $2200 Oct 18 1102 $2025 Oct 18 1101 ...more >>

I have a relatively simple query against a table with 26 million rows. The EM shows 5 spids running with several waiting on CXPACKET or PAGEIOLATCH_SH events. Running W2K, SQL2K, and SP3. All the KB articles say these sort of parallism problems were taken care of in SP1. Takes 8 minutes to ...more >>

I've two different sql-server Db on the same server, i need to pass data between the 2 db. If i call a stored procedure on one of this DB tha begin a tran, select insert and update some table from both the DB, this transaction has effects on both DB or only in the DB in which the stored proced...more >>

I have a SQL Server database and have discovered that due to the use of transactions in applications accessing it I need all the SELECT statements in my stored procedures to use the WITH(READPAST) optimizer hint. I could go through and change all my stored procedures to use this, however they ...more >>

Hello In making an extended stored proc, I am trying to use a wizard-generated ATL OLEDB consumer class to access data. I've tested this and it works fine when run from a standard windows application. When I debug the extended stored proc, it connects fine (it is connecting to a remote SQL s...more >>

Hi There I need to view data in a differet format : My Database looks as follows : Date DayName 2/3/2008 Sun 2/4/2008 Mon 2/5/2008 Tue 2/6/2008 Wed 2/7/2008 Thu 2/8/2008 Fri 2/9/2008 Sat 2/10/2008 Sun 2/11/2008 Mon 2/12/2008 Tue 2/13/2008 Wed 2/14/2008 Thu 2/15/2008 Fri...more >>

Hi, Can char index funtion be used on text data types. There are no compile time errors, but when i give a string > 8000 then the char index returns 0. any help on the context is much appreciated. thnks, anu...more >>

I am having a problem with syntax on an INSERT INTO statement. I may be using the wrong statement to perform the task, but am not an expert and am still trying to learn the right way to write SQL. I have created a table and added it to an existing database. Now I need to perform some work. ...more >>

I get various material numbers like the following: 20890 138902 DEDKL2 XYFGAB Now what I mean by alpha conversion is if the string a purely numeric the left hand side must be padded with zeros if it is alpabetic or alphanumeric the left had side must be left as it is. In the above c...more >>