Hi I have a simple select like this Select MyDate, MyID From Sales The select is returning the date like this: 01/06/2005 12:00:00 a.m. 02/06/2005 12:00:00 a.m. I'm showing it on a Datagrid in my app, my question is can I modify the select to return only the date without the time...more >>

Hi I have a simple select like this Select MyDate, MyID From Sales The select is returning the date like this: 01/06/2005 12:00:00 a.m. 02/06/2005 12:00:00 a.m. I'm showing it on a Datagrid in my app, my question is can I modify the select to return only the date without the time...more >>

Hi Folks, client company has around 25 sql servers . Each server has got 5 to 10 databases. Some of the databases are old. We don't know whether any applications using old databases or not. Is there any way we can find the unused databases in past 3 months or past 1 year using some Last...more >>

I can't seem to get the fn_get_sql function to work. Here's the script I'm using: declare @spid int DECLARE @Handle binary(20) declare active_spids cursor for select spid, sql_handle from master..sysprocesses where status not in ('background', 'sleeping') --and spid <> @@spid open active_sp...more >>

Hi all, Looking for a way to do this in the minimum # of stmts I can. Basically, this is doing a table1(1) to table2(many) update join. When the t2 row has myindex = 0, then update column t1.c1. The problem is that for each row in t2, it's index only matches 1 column out of the 5 ...more >>

Hi all, We have a third party application that allows users to take customer orders. This application has all the bells and whistles of the Titanic. Since we wanted our customers to be able to place orders themselves, we built a smaller light weight application to allow them to do just that. ...more >>

I am making an SP that uses a table as an array. I want the user to pass the table to the SP, where I will then iterate through it and pull out all of the IDs and place them into a string that I can use with the IN keyword. Once I get the table into the function, I will have no trouble. I ...more >>

Hi all, I am trying to determine some index characteristics from the system tables. 1. I assume when a sysindexes.indid = 1 that index is the clustered index, true? 2. How do I dermine which index is the primary key? 3. How can I determine if an index is unique? Thanks in advance. ...more >>

I have an app that must insert batches of transaction records every 30 min. Typically only a few hundred each time. We found that just appending a hundred or so INSERT commands together in a single query sent to the server was significantly faster than our first approach of preparing a paraemt...more >>

Hi, I have a MSSQL server on port 1434, the default is 1433 anyone who know how the should connectionstring looklike to connect to the post 1434? -- ----------------------- Marcin S....more >>

I have a table with follwoing feilds: 1)ClientID 2)EmailAddress 3)AddressDescription 4)Primary 5)BizRuleFlag there could be multiple address for each client ,but each client **MUST** have atleast one address with "Primary" flag set to "Y" (which mean that one is his Primary address) and ...more >>

Executing a LEFT JOIN and preserving values from select "on the left" I have this problem: If I have left table with for example column A = 'PIPPO' and in right table PIPPO is not present, of course I have NULL value because of the LEFT JOIN. But what about if in the right table I have 2...more >>

Hi, There are some rows been locked by other users…. In one window (Query Analyzer) I run: BEGIN TRAN SELECT * FROM NorthWind..employees WITH (XLOCK) WHERE employeeid = 1 WAITFOR DELAY '00:01:00' COMMIT TRAN In other window (Query Analyzer) I run: SELECT * FROM NorthWind..employees...more >>

Hi, I have got approximately 100 tables. Their all attributes are same. But, I want to change their one attribute. Such as my table; NAME nvarchar(50) SNAME nvarchar(50) SALARY float I want to change SALARY's property from float to money. It is possible to th is operation manually. But I ...more >>

We have this weird problem with our customers using MSDE. They keep running up against the 2 gig size limit and their database stops functioning because it can't allocate any more space. But if you examine the database, 75% of the allocated space is unused. It can't be recovered by shrinking t...more >>

Hi, there is a way to reset identity field of many tables via storeprocedure ? i try with the scripts below, but they don't work ! BACKUP LOG test_dbWITH TRUNCATE_ONLY DBCC shrinkdatabase (test_db) and also with create table #table( idTabella int, nome varchar(4000) ) insert...more >>

I'm looking for financial function in T/SQL. I need to implement the Rate-Function like in VBA. Thanks Eric ...more >>

Dear all, I'm executing a DTS which own a sql server task that launch a stored procedure. This stored procedure run a WHILE sentence and then launch a bcp command dinamically. Well, everything works debugging that stored procedure or simply launch it throught Query Analyzer with its pa...more >>

Hi All, What is the correct storage type for VAT 2 or 4 decimal places?? ...more >>

Hi to all, I have a big performance problem with looping threw rdoresultset. I made some very complex sql queries. They link like 5 tables and uses a couple subqueries. The number of returned rows is something like 1000. Query execution takes about 1 second, which is completly acceptable. Bu...more >>

I am new to SQL. I want to update a field based on values in another field for the same row in the same table. For example, if I have a table like this: FieldA FieldB FieldC FieldD 1 AA Cat 2 BB Dog 3 CC Mouse I wan...more >>

Hi, I run query in Sql server 2000 below: select rand(datepart(ms, getdate())), datepart(ms, getdate()) Everytime I run it, I only get 0.7xxxxxxxx as random result. Is it thw way how it is desgined? If yes I will think the number is NOT so random. -- WWW: http://hardywang.1accesshost.com...more >>

Hi, I am trying to retrieve the code for some stored procedures. I was able to select the routine definition from the information_schema.routines for the procedures that had less than 4000 characters, but I can't get the larger procedures. Does anyone know how to pull the entire stored pr...more >>

What's a way I can determine the last time any record in a table was updated? Is there a table property I can get this from or do I need to establish a field in the table that I can query from? I have some shared tables that I want to give users the option of storing queried results in the...more >>

I am working on an application where I want the user to be able to "Duplicate" a Contract item. To do this I have to duplicate the row in the master table and duplicate all related rows in the related "children" tables (of which there are twelve..) I was wondering if there was an SQL clause ...more >>

Dear all, I'm fed up with this problem. I hate that behaviour. When I create objects from a query analyser session in my own workstation, then these objects doesn't visible from my MMC. A lot of times I am obligated to close and open again. I've got a MMC with 13 Sql Server and some of th...more >>

Here is my problem: I am trying to generate stored procedures that will handle filtering and ordering on all columns. I almost had it working but I ran into problems with the case statements; I would get conversion errors. The errors can be reproduced if all columns are combined into one cas...more >>

Hello, I am trying to find the list of all available sqlservers...with something like this and making it a .vbs and running it from the command promt on my windows 2000 and write to text file. can anybody help me with that. Dim oListSvr Set oServer = CreateObject("SQLDmo.SqlServer") ...more >>

I've got a proxy account set up that is working fine for DTS packages, etc. I've also got an environmental variable set up for osql setting the user to the proxy account. The problem I'm having is within a scheduled job, when I execute this statement: EXEC master..xp_cmdshell 'osql.exe -S...more >>

Hi. I have run xp_cmdshell from within a stored procedure, and it has hung. Is there any way to kill the process, other than rebooting SQL Server? Cheers Neil...more >>

I would like to add an index to a table that my application writes to/reads from and change an existing stored proc. Before I do, I would like to take some measurements on the existing table structure and proc. Specifically, I'd like to know how many pages the proc needs to access to retrieve ...more >>

Ok, here it goes. I'll try and explain my situation as best as I can. I would like to dynamically create variable names. We store our data in monthly partitioned tables.........table_200501, table_200502.... I am creating a view that is a union of all of the partitioned tables. So in or...more >>

Hi, I am currently inserting a record using: insert into TABLE(FIELD1,FIELD2) values ('%s',%d) But, the problem I have is that the table I am connecting to can either have 46 fields or 47 fields depending on its version. Is there a command that would allow me to use an existing record as...more >>

Hi, I executed xp_readmail stored procedure. It brought some records as result. I do not know from where it brought those records. Those were not available in my inbox. It is supposed to retrieve the first unread message in my inbox. Isn't it? Where do I find the SQL Server Mail box? ...more >>

Hi, I have got approximately 100 tables. Their all attributes are same. But, I want to change their one attribute. Such as my table; NAME nvarchar(50) SNAME nvarchar(50) SALARY float I want to change SALARY's property from float to money. It is possible to this operation manually. But I ...more >>

Hi. I am looking for a list of all methods available to sp_OAMethod. Is there such a thing? Cheers, Neil...more >>

HI and TIA. I posted this to a vs.net NG but after looking thought it might fit in here better. Using VS.Net 2003 I built an ASP.Net Web application using VB.Net. I connect to an SQL db(SQL Server 2000) and am setup to write/read/edit data no problem. I'm having a problems with the followi...more >>

Hi All, I am having a small problem with varchar(max) type variable. Please look at the code below. DECLARE @varcharmax varchar(max), @varchar varchar(8000) SET @varcharmax = REPLICATE(‘D’, 10000) – this will end up with 10000 lengthy string SET @varchar = REPLICATE(‘D’, 10...more >>

Hi there. Is there an easy/correct way to move a database's data and log files(.mdf, ..ldf) to a new location? I know there must be a simple way (system stored procs or something) - I've just been stumbling around manually copying, creating new databases, restoring, renaming etc.... Not...more >>

Hi, I need to create a primary key on my already existing tables. But there are some (just a few) rows in my desired primary key columnt that contain duplicate values. Do someone know how to find duplicate values and what values, so that I can delete just those rows with duplicate values? P...more >>

Hi, I have the following problem. I create a small request to truncate a column (see below). When I run it, I get an error 8152. I don't understand why. I have no null value into the table. I would appreciate any help tia Samuel ALTER TABLE [dbo].[ArticleNumber] ALTER COLUMN [Number] ...more >>

I have a table that gives a hierarchical representation of a master table. It has two integer keys ID1 and ID2 providing the hierarchy, where ID2 is the child of ID1. What I am trying to do is to create a query that will get all the nested joins. Eg. ID1=1 and ID2=2, ID1=2 and ID2=3, ID1=3 and...more >>

I wrote a post earlier, but my manager has asked that I do i differently. I have a table with employee hours for particula projects. (I work for a telemarketing company Table1 Project Task Emp Hours A1 TPV 1 5 A1 TPV 2 A3 AUDIT 3 TOTAL: 1 A2 ITM 4 10 A2 ITM 5 1 TOTAL: 25 I nee...more >>

hi! i want to transfer data from MS Excel Sheets to SQL Server. is there any way. Thanks Ahmad Jalil Qarshi ...more >>

Hi! I'm not a good friend with the insert command. What I am trying is the folowing... The insert statement: INSERT INTO MyTable(field1, field2, field3) VALUES(?, ?, ?, ?) Data to be inserted. '1', 'Customer data', 'Hi, this is a sample text to be inserted' When executing, for s...more >>

Hello, I always use int32 as keytype. Now I see (asp.net 2.0) that there also is an uniqueidentifier type. Can somebody tell me a little bit more about this? For example: I have a website with news. When you select the title you can read the complete newsitem, this is done by the news-ite...more >>

Hi Everybody, I need to filter out string value that containing "_." or "._" Based on the following test sample, it should return only row 1 and row 2 but somehow the like clause is not working. select * from ( select 1 as row, 'sfsdf_.sdfsdf' as t union select 2 as row, 'sfsdf._sd...more >>

hi, this is a simple Qn My Employee_Salary table contains EmpID, DeptID, and Sal I want to find the top 2 Employees from each dept, who are taking the highest sals in each dept regards Lara ...more >>

what is a workload file and how do i create one? ...more >>

Hi, I have got approximately 100 tables. Their all attributes are same. But, I want to change their one attribute. Such as my table; NAME nvarchar(50) SNAME nvarchar(50) SALARY float I want to change SALARY's property from float to money. It is possible to this operation manually. But I ...more >>

Dear All, I have a single table name "Remark". It contains the item code, date, status and remark. If I want to build a query that select items out which the latest status is still in "pending" for example. How do I make it? Thanks Best Rdgs Ellis ...more >>