Hi Group! I've two fields of type datetime and I want know how to calculate difference in fraccion of hours, if I execute this select datediff(mi, convert(datetime,'2004-09-14 11:30:15.000'), convert(datetime,'2004-09-14 12:00:57.000') ) the result is 30, but if I execute this selec...more >>

I have a CSV file with the first row in the CSV file to be the column headers. The file is as follows: Material, Customer, month,qty, sales 10000,19ABC, 122004, 90.5, 10000 20000,20ABC, 122004, 80.5, 12000 30000,21ABC, 122004, 70.5, 14000 40000,22ABC, 122004, 60.5, 16000 50000,23ABC, 1...more >>

I 'v gotta stored procedure runs in a production database which creates a report. Within the stored procedure, I 've gotta curser which grabs user names from a temperory table and pass into another SQL query which dinamically building a cross tab query. I found rearly this query produces an ...more >>

Hi All, How to import the data from Excel file to a sql server using scripts. Note: 1. The Excel File contains multiple sheets and i need to import all 2. It is should be done without using any frontend application or dts With Regards, Anand...more >>

Hi All I have made a recursive scalar UDF which has some cursors running inside it. The strange thing happening is that when the outermost cursor doesnt have any records, the function takes an indefinite time. I am using the @@fetch_status condition but apparently that doesnt end it. Also...more >>

It is my understanding that the amount of space taken by a varchar is directly related to the size of the data actually being saved. Therefore, in designing a table, what are the advantages, if any, of keeping the size of the varchar max size as small as possible? Thanks, -- Rick...more >>

Does anyone have a script that would create a table and poulate it with every day in the year? For example, I want to populate a table with everyday in the year 2004 thru 2006. There has to be a better way then manualy typing them all in. I know I could create an excel spreadsheet and eas...more >>

I have function with 5 parameters and which returns table. Why I can't do like this: SELECT * FROM myFunction(SELECT par1,par2,par3,par4,par5 FROM parameters) How I can do this? Thank you, Simon ...more >>

I need help build a query/funciton. I have a table that contains event information for various parts of my web site. Each calendar has a set of events that are independent from other calendars. I need a way to fetch prior and next event dates for a given event date. Here's the table definition...more >>

Hi, I am developing a pretty much standard ASP.NET web application with an SQL Server database. Last week we had a more serious test period for cc. 3 hours. The users used the application from time to time there were minor problems (ie. the IIS dropped a dozen connections), but nothing big pro...more >>

Hi all. When my C# application is trying to insert a large string containing an xml document, to a table in my database (SQL Server 2000), i get Timeout Expired. After setting the connection timeout to 60, i still get the same exception. What can i do, besides reducing the string size and setti...more >>

Help... I have FILEA that contains a date in FIELDA - CHAR(11) that reads "Dec 31 2002". I want to join this to FILEB, but the field FEILDA - SmallDateTime that reads "31/12/2002". Any idea how I would join the 2 files/fields inside a SELECT statement? Thanks Jim ...more >>

Hi Guys, There has been a change requested here, whereby data in our tables is going to be encrypted. This will impact heavily, as joins as well as data viewing, using encrypted data could be problimatic. A suggestion has been made: 1) Allow for encrypted data, and have all tables renamed ie...more >>

I have complicated select : SELECT T2.* FROM (select s.ts_medID,i.izd_id,t.adv_start,t.adv_end FROM .....) as T2 and it works. Now I would like to use this result to get one more column: SELECT T2.*, newColumn=3D(SELECT T3.value FROM (SELECT value FROM .... .... WHERE n.id...more >>

hi, I am trying to pick a training class between VB.net and C#.net. Could anyone please give me some example pros and cons between the two (as in which language is the way to go for the future? Which one provides more functionality and power?, etc) many thanks, JJ...more >>

I'm trying to update a temporary table in a loop by using a declared = variable (@H) to dynamically specify the target column in the temp = table's specified row. The problem I have seems to be that although the = code does not generate an error, it does not set the values. I'd appreciate any...more >>

Code: SELECT Docs.Id, WebParts.tp_PageUrlID FROM Docs, WebParts INNER JOIN SearchContent ON Docs.Id = WebParts.tp_PageUrlID GO Error: The column prefix 'Docs' does not match with a table name or alias name used in the query....more >>

I have a SQL 2000 Database sitting on a Web Server called "myTestDatabase." Inside Visual Studio 2003, I went into Server Explorer, and created a Data Connection to myTestDatabase. When I double-click the Data Connection to myTestDatabase, I'm prompted for a password, and then I can see the ta...more >>

I am new to SQL Server programming, so please excuse me if this simple. I want to perform a SELECT statement with several WHERE conditions. When the results are returned, I only care about the first record. Is there a way to do a SELECT FIRST or SELECT 1? For example SELECT * FROM ...more >>

I was reading a monster thread on whether DBMS or RDBMS. Many of them said that since MS Access allows the users to create table without primary key, therefore it is not RDBMS. My Question even SQl Server allows tables without primary key. So it also not RDBMS ? Thanks ...more >>

Hi All, I got basic question about locking. I want while one user is updating a data, other user should able to read a data (NOT DIRTY DATA). For example: Say User A issues the following command: Use Northwind Go Begin Transaction Update Region with Set RegionDescription = 'South' wher...more >>

Hi, Is there any built in function or stored procedure to encrypt / decrypt MONEY field in SQL Server? Thank you...more >>

Code: SET IDENTITY_INSERT SearchContent ON GO INSERT INTO dbo.SearchContent (LeafName, tp_PageUrlID, Data) SELECT Docs.LeafName, WebParts.tp_PageUrlID, RIGHT(CONVERT (varbinary(8000),dbo.WebParts.tp_AllUsersProperties),400) AS Data FROM Docs, WebParts Error: Server: Msg 545, Leve...more >>

I am writing a StoreProcedure which has a calulation formula in it i.e Select name as name, salary as salary, If (Select sum(b.XAmount)<> 0 or sum(b.YAmount) <> 0 ) Convert(numeric(30,2), ((sum(b.XAmount)/sum(b.YAmount)+ 1) *100) as Percentage Else 0.0 as Percentage , Address1 a...more >>

Hi! I am trying to calculate the value of "locationDescep" column in one SELECT statement but don't seem to succeed. If the Type = 'FILE' then DOC table will join to FILES table to get the description. If Type = 'ROOM', then DOC table will join to PLACES table to get the description. Can I h...more >>

I have a sp which i am using to build a temporary table. in SQL2k I want to loop throught s string of input parameters @ID which i can get froma csv and split then pass to the loop and runa select query. This select query will return one record at a time and i need to access the individual...more >>

Is there an easy way to OR two varbinary. I want to save the COLUMN_UPDATED() value in a field in the table. If fields 1 3 6 are updated the first time and fields 4 6 3 are updated next I want to "OR" the new value with the old. -- ____________________________________ Wayne Blosat VP of ...more >>

I have some graphic templates stored in WORD documents that currently are stored in an OLE column in Access. In converting to SQL Server, I think it would be better to design a column of file pointers (as opposed to an image type)...I've searched for information on how to set this up but I must...more >>

Hello all! Not sure if this is the right forum but... Does anyone have suggestions for "Best Practices" or some benchmarks for what good documentation standards are for Dbase designs in the IT World? Thank you in advance, Mike...more >>

Hi, The ultimate mission I am attempting to accomplish, is to change the collation of a database from SQL_Latin1_General_CP1_CI_AS to Latin1_General_CI_AS using a stored procedure for it will have to execute at night when the server is not being accessed. In doing so I have had to first re...more >>

Can SQL server 2K (standard version) be installed in XP (Professional) ? ...more >>

Hi, I have table CREATE TABLE [Credit] ( [CustNo] [nvarchar] (10) COLLATE Croatian_CI_AS NULL , [Code] [nvarchar] (3) COLLATE Croatian_CI_AS NULL , [Quantity] [int] NULL , [Sales] [int] NULL ) ON [PRIMARY] INSERT INTO Credit (CustNo,Code,Quantity,Sales) VALUES (1,'A',3,100) INSERT ...more >>

I've a function in SQL Server which accepts two input parameters, @StartDateTime and @FinishDateTime. Both are declared as datetime. Example:- @StartDateTime = '2004-10-06 08:00:00' @FinishDateTime = '2004-10-07 16:30:00' I now want to run a query that will treat the input parameters as j...more >>

From my query, I want to return the rows that DO NOT exist in the rightmost table to the INNER JOIN. For example: dbo.Clients INNER JOIN dbo.PaymentPromises ON dbo.Clients.ClientID = dbo.PaymentPromises.ClientID I want to return ONLY the Rows in dbo.Clients that DO NOT have a matching ...more >>

Trying to create a trigger that writes updated values to another table but I'm getting an error when checking the syntax cannot use Text, Ntext or Image columns in the 'inserted' and 'deleted' tables. My first table is CREATED BY CREATE TABLE [dbo].[TestTable] ( [IDMainTAble] [int] IDENTI...more >>

I have scripted the following five tables and their relationships. There are expenditures, then categories, then subcategories, then subsubcategories, etc. The difficulty is that the categories are all in one table, with a link to itself so that if it is a subcategory, its parentID is set to th...more >>

I have two tables first one is created by following script CREATE TABLE [dbo].[TestTable] ( [IDMainTAble] [int] IDENTITY (1, 1) NOT NULL , [FKOtherTAble] [int] NULL , [DataField1] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [Datafield2] [int] NOT NULL , [DataField3] ...more >>

Folks, I use SQL-DMO's application object to get the list of available SQL servers. Although rarely, sometimes ListAvailableSQLServers does not see some servers. When that happens, I am able to see those servers with Query Analyzer. I always thought that Query Analyzer uses DMO. If that is the...more >>

I query a money field that looks like this 309.0000 what do i have to do to eliminate the extra zeros, like this $309.00 i've played with converts but have been unsuccessful Thanks! ...more >>

I am trying to insert records from a table to the same table. This is my query: declare @counter int set @counter = 0 while @counter < @wo_count Begin set @counter = @counter + 1 INSERT INTO wo_audit (updateid, TableName, entry, ColumnName, OldValue, NewValue, Dat...more >>

Is there a way to access the inserted table from a stored procedure call from a Trigger? When I try to do select * from inserted in the called procedure I get invalid object name 'inserted'. Thanks -- ____________________________________ Wayne Blosat VP of Information Services PRO Cons...more >>

There is a user who is trying to extract data from a SQL database (SQL 2K, 4 CPU Xeon 3.0 Ghz CPU, 2 GB RAM server), into an Excel pivot table using the following view: Select US.UserID as Usr, SR.SeriesID as SeriesID, SC.[Description] AS Scenario, SR.Sku as Sku, SR.Customer as [Primary Cu...more >>

Just a quick question. I have data in a field that is mixed characters and what I need to do is remove the numeric characters from the field. Is there an easy way to do this? This is what the field looks like Harry 2345 Tom 2115 Cathy 5867 Jim Smith 9283 Alice Flowers 6745 5769 Ji...more >>

HELLO EVERY ONE what the function that get the smallest number in the list in oracle the function is LEAST THANKS...more >>

Does anyone know how to figure out the age in years given a birth date...I'm trying this as an example: select datediff(Year, '10/6/2003', '10/5/2005') But it comes up with 2 years when the person is still only 1 and will turn 2 the next day..any ideas? thanks -Jim ...more >>

I have created a UDF that returns a table. Code is below. Here are my questions: 1) Is this the best way to set this up? If not, what would be a better way? 2) If the SELECT statement yields multiple rows, do I even need to setup and return an explicit table? 3) Which approach (explicit...more >>

Is there any feature in SQL server 2000 which allows statistics about index utilisation on a database to be reported on? What I'm after is a report which for every index in a database, tells me how many times it was chosen by the optimiser ina specified interval. What I'm trying to achieve...more >>

i want to run 2 query first one getting only one row from my table.. i take it as sa i want to use this value in second query.. i tried it like below it but it gave an error Invalid column name 'sa'. how can i solve it or how can i get a value from first query and i can use it's value i...more >>

Is there any guidance out there as to how often we should be rebooting our server? At PASS I heard someone say daily but didn't hear any specific reason why. We have a pretty low volume SQL Server running on Win2000 but we do have issues where a database seems to take a long time to init...more >>

Hi there, Got a nice challenge here .. we must implement a security system, that should enable us to handle : a.. Application Security (that is, what sections of the application a user can "see" . menu options, screen controls and so on) b.. Data Security (that is, what...more >>

I am writing a report in COBOL with MS SQL in the back end. This report should have an option of writing its output in Excel. My plan was to create a temp table with the output data and than export its content in Excel using OPENROWSET . However, I ran into a problem with columns in Excel....more >>

Hi to all, Im getting an error 8120: Column <column name> is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause. In my procedure i want to select from two tables CCList and CCThemes some values, and all group by CCList.Calculatio...more >>

How would I modify my script listed below to delete differentail backup files older than two days old. Next, before creating the weekly full backup file, delete the present full database backup file. There would not be enough disk space for two full database backups. Please help me m...more >>

Here is my situation: I have a SQL server 2000 with 2 sets of harddrives C and E. I installed SQL server on C drive and data on E drive. Now the data drive E is near its capacity. I put in a new set of drives G as the new data drive. I would like to put all the database to G drive and remo...more >>

We have a dozen or so DTS jobs that run on our SQL Server. They run independently and sometimes a particular combination occurs that causes a deadlock. Our biggest problem is that we don't know why SQL Server is locking the tables at all. In some cases we're doing a data driven query and it l...more >>

How can i replace Null with a blank field in the select query? Manny...more >>

Where can I find information regarding the following: 1. that explains how user defined functions are processed by the SQL Server engine. 2. performance considerations regarding the different types. ...more >>

Hi Guys, there are columns in my database which shows <NULL> in the columns content. When i write query select * from [tablename] where [columnname]='' , it produces 0 results and replacing the where criteria with <NULL> produces errors. Can you please tell me how i can query for null colum...more >>

How do I copy data from on database on an SQL 7 server to a different database on a SQL 2000 server? I have data in a table in one database that I want to copy to a differently structured table on the SQL database. I'm sure there is a way to do it but I'm just not quite sure! Thanks for any...more >>

Hello, I just wanted to clarify a little more on locking... SELECT statement perform locks according to DB default unless otherwise specified with the WITH statement UPDATE / INSERT / DELETE statements perform what sort of locking? Thanks -Steve ...more >>

Hello All, I have a table like this: Emp Day Time 100 1 12 100 3 8 200 1 10 200 2 5 200 3 8 200 4 8 200 5 10 300 1 5 Can we write a query which shows the output like this: Emp Day1 Day2 Day3 Day4 Day5 Day6 Day7 100 12 0 8 0 0 0 0...more >>

I'm trying to duplicate a row using a stored procedure but the only thing I want changing is the primary key, which would be appended which a parameter. I've tried the following but recieve an error saying that the table cannot have a duplicate primary key INSERT INTO account (Id, Name, Add...more >>

How bad is it - really - to use an IDENTITY property as a primary key? Before answering, please understand that I'm not an old timer who thinks in terms of sequential I/O and I don't care and never have cared about the sequence of rows in a table. Having said that, it seems to me that there are...more >>

I wrote a code that getting data from system and insert it into sql2000 table (for example tableA). But i want to write second table (tableB), that will the same table, but transposed. First record of of every row of tableA will be name of column in tableB. Anyone can help me? *** Se...more >>

According to BOL, quotename will only allow up to 258 characters string to be processed. Is there a possibility to increase this? I want to use QUOTENAME() for a larger nvarchar record. Thanks! ...more >>

Hi Sql Gurus, Suppose there is a table Employee with three columns: FirstName, MiddleName, Surname. The table does not have any primary key column or identity column. This table is populated with say 1000 records. Is there any way to fetch 101 to 200 records ? Thanks and Regards, Amit...more >>

I am trying to migrate an application written in powerbuilder which currently uses a Sybase DB to SQL SERVER 2000. First problem - I use today() to get current datetime what is the syntax in SQL 2000. Any other obvious tips would help !...more >>