Hello, I've a normalized table where i store items as records like: id | type | value 1 | begindate | 01-01-2004 2 | enddate | 31-01-2004 etc... How can i transform it so that the data looks like this: begindate | enddate 01-01-2004 | 31-01-2004 Can this be done within sql ...more >>

Hi all, How can I get a DTS package to start when a files arrives to a certain FTP-area? Is this possible without using the scheduler? /Kent J. ...more >>

There're 2 related, master-detail tables: Service and ServiceBill. To get some information about "service", I need to SELECT all columns from the master table and some aggregate value from the detail one: SELECT s.*, SUM(b.Value) FROM Service s INNER JOIN ServiceBill b ON s.ServiceID ...more >>

Can I do a UNION ALL between 2 tables followed by a JOIN to a 3rd table from the result of the union? I am having problems getting the syntax right Thanks Stephen ...more >>

Hi, When I create this table: create table #tmp( c1 varchar(8000), c2 varchar(8000)) I receive this warning: Warning: The table '#tmp' has been created but its maximum row size (16025) exceeds the maximum number of bytes per row (8060). INSERT or UPDATE of a row in this table will fail if ...more >>

I run this code twice. The first time it works, the 2nd time it crashes on line 6, says There is already an object named 'MyJunk' in the database. IF DB_ID('MyJUNK') IS NOT NULL BEGIN DROP TABLE MyJUNK END CREATE TABLE MyJUNK(ClientId nVarChar (3), ProdGroupId nVarChar (40), OwnerId nV...more >>

Hi, This command generates error: select dateadd(day,-226898,getdate()) Server: Msg 517, Level 16, State 1, Line 1 Adding a value to a 'datetime' column caused overflow. Is there any solution to calculate my required date? Thanks in advance. Leila ...more >>

I have an SQL Trigger which update data in a table. The table is linked in Access When updating data in Access, if the trigger is fired, I got the following message: "This record has been changed by another user since you started editing it." How can I update the data anyway, with the result o...more >>

I am trying to make a select that access 2 databases at the same time. Those databases are located at a different servers. My problem is that I get an error message saying that the remote server is not configure for DATA ACCESS. So, I try to make a serverlink to see if that solve my problem. Bu...more >>

I have a parsing function that accepts a varchar(8000) string. Unfortunately, the incoming string is now longer than 8k, so my only option appears to be to convert it to "text" datatype. However, one of the things I do inside my parsing function doesn't accept a text datatype. Can anyone t...more >>

I want to insert data from one table into another in the following manner Insert Into User_Group ( UserID, Fname, Lname, Rank ) select UserID, Fname, Lname, ***** from Users Where OfficeID in (2, 3, 10) order by CreateDate I want the RANK column in User_Group to have sequential num...more >>

I'm having a problem with recursion. I am creating data items that are choices (if <data><operator><number/string/null> then list of <data>) and the data items inside the choice can themselves be choices. It is not only data items table I need to get back. Aswell I need the relations for t...more >>

Is there a way to create a view in one database that uses a table in another database on the same SQL2000 server? ...more >>

Hi, could somebody help me with this simple problem: declare @databasename varchar (30) set @databasename = 'xxx' use @databasename Why isn't it working or what is the right syntax? Thanks, Tudor ...more >>

Hi, Say I have this example select query. Select field1+field2 as Calc1, field3+field4 as Calc2, Calc1 + Calc2 as = Calc3 From myTable This produces an error, but is there a way I can do this? I'd really = hate to have to repeat my calculation each time I'd like to reuse it. Thanks f...more >>

Can I store an excel document in a table using Sql Server 2000? If so can anyone provide samples or websites to read more about it? Thank you in advance ...more >>

Is there a way when copying query results to capture the column names at the same time? Thanks! :) ...more >>

I am trying to update a column with a count of sales transaction minus the return transaction. Sales transactions are TransType=1 and return transactions are TransType=2. Can I do this in one query? This is what I have: UPDATE Reporting_ByProgramMembership SET MembersWTransactLW ...more >>

Hi all, I've got a fairly old Pentium2 machine running NT4 Server, 256Mb ram, SQL Server 2000. Our application runs on the same machine as the sql server, and it reads data from the network socket, writes to disk and performs a COM call to SQL Server to bulk insert the file. Every so often (appr...more >>

I have a cursor where I loop through a set of employee_ids. While inside the cursor, I have a second set of cursor, which gets the column_name from information "information_schema.columns" for a given table. The problem I have run into in the following code is: EXECUTE sp_executesql @stmt ...more >>

I have following trigger that creates 4 agents in my agent table. I am using this to hash my passwords UPDATE Agent SET passwd = dbo.ud_MakeSHA1(Passwd) If I place that at the end of my trigger it hashes all passwords in the agent table I need to only has the new entires as they are in...more >>

Good day, If I have the money datatype in the table, how to make the function that will show field: 6000.0000 -> 6.000,00 kn field: 1234.5678 -> 1.234,57 kn TNX in advance... -- Tomislav Bilic Escape d.o.o. http://www.escapestudio.net -- GSM: +385 91 577 1025 ICQ: 1824223...more >>

Hi, I am still not sure if everytime I backup the Transaction Log, the Log file will be truncated and removed all the Old Log data. If that is the case how come my original Log File is about 800 MB and after tuncate, it has about 200 MB not close to 0 MB??? Can someone explain to me plea...more >>

I have lots of database diagrams on the "big momma server". I have a copy of that database on my laptop for dev purposes. I would like to copy the diagrams across. I have used DTS in the past to do this with tables and stuff, but I can't see how to get it to copy the diagrams. Could I get ...more >>

Hi Friends, I have a db with more than 350 table, most of them have Relational Integrity with PK and FK we want to have this DB replication to another server. What are the potential problems and solutions? I had experienced problems and conflicts if db has Relational Integrity. Thanks i...more >>

How do you write a select statement that has a 3 table join, but you only want * from the first table (without listing out each column)? Select * from Table1 t1, join Table2 t2 on t1.id = t2.id join Table3 t3 on t2.id = t3.id where t3.sr = 1234 ...more >>

Hi All I have received an access database to import into sql server, but when I try to open it I get the message: - You do not have the necessary permissions to use the '(unknown)' object. Have your system administrator or the person who created this object establish the appropriate permiss...more >>

Hello. Quick question? I have a query that pulls the following results (which is also the table structure more or less): customer_id vendor cost 1 SM 32.00 1 S3 12.99 2 A2 ...more >>

Hi all. I have a litle chalange picking 5 rows sumarizing the price and group by the customer. I want my list to put my spesifik customer into the midle row (3) and the 2 customer that has shopped for more in the next rows (4 and 5) and the 2 customers that has shopped for less in the 2 first...more >>

I just ran sp_helpsort on one of our databases and I had the following output ri Server default collation ------------------------------------------------------------------------------...more >>

I know that it is relatively easy to return random records using the newid() function SELECT * FROM Northwind..Orders ORDER BY NEWID() However I was wondering what the best approach would be for paging of these results? My client web application would require to show 20 results at a ti...more >>

Pardon my newbieness. I'm coming from an Access world and trying to figure out how to do this in T-SQL: I have a Union query that looks something like this: SELECT CASE WHEN ConditionA THEN 'Result A' WHEN ConditionB THEN 'Result B' ELSE FieldValue END AS 'Groups' FROM T...more >>

How do you automate sql scripts to execute from either a batch file or inside Query Analyzer. Joel ...more >>

I use begin transaction statement inside a procedure called "test", "test" will insert one row or two to a couple tables. I opened 2 windows in query analyzer with this statement declare @i int while @i<1000 begin exec test @test_id = @i @i=@i+1 end I ran them simontaneously, two of sta...more >>

I am attempting to create an HTML formatted email and send it all from within SQL server. In order to do this I need to retrieve records from an orders database. I achieve this by: SELECT [tr].itemQty [td] , [tr].itemSku [td] , replace( products.VNDR_Item_Desc, '&', 'AND...more >>

Hi all, I'm using SQL Server 2K on Win2K to develop a window-based application using ..NET framework. Now I have to implement a function that query database and make backups, I want to close all concurrent connection on SQL Server before running the backup process. Please help me! Thanks for re...more >>

I'm trying to find a way to (using T-SQL) remove a user from all databases and then remove the login from the server. We have an employee table with a flag to indicate whether or not the employee is active. We want to, from time to time, run a script that will search the table for inactive em...more >>

Hi All, Could anyone help what is wrong with this code. @nvchrSql is a variable it has a value of 'op20PctTotalDollarsByOffice' which is a 'Bit' Data Type fied in the FactSubTeam_Event table. DECLARE @nvchrSql NVARCHAR(100) SET @nvchrSql = N'Top20PctTotalDollarsByOffice' ...more >>

We are experiencing an intra query problem receiving Parallelism messages and would like to fix this... We've found information about an query option hint "maxdop1" should be included in our scripts... Does anyone know how to write this?...more >>

I need to create a stored procedure that will return two fields, based on an XML string stored in a text column. Specifically, the column in question has data looking similar to the following: <NewDataSet> <Project> <Project_number>12345</Project_Number> </Project> ...more >>

Hi I studied the help to use extended object ,but can anybody help me how can I use the following example to feel the result and concept of it?( I tried it in VB6 but when I typed " Dim oSQLSvr2 as New " no word like " SQLServer2" appearred,should I install sth. before)? Using Visual Bas...more >>

Hi I have just read the article "The Curse and Blessings of Dynamic SQL" written by Mr.Erland Sommarskog. Below is a piece of his article: "Parameterizing the table name to achieve generic code and to increase maintainability is simply a programmer virtue.But it is just that when it com...more >>

Hi I'm using SQL server 2000,and I created a rule and bound some columns from different tables to it,now there's 2 question: 1- how can I find which column are bound to this rule(normally I should right click the name of rule and then select the properties and select the name of all tables o...more >>

How can I do create proc MyProc as --....proc logic go grant execute on MyProc to MYCOMPUTER\ASPNET I can do the 'grant' statement where the user name doesn't include a computer prefix - but the ASPNET account does! It keeps complaining, citing 'Incorrect syntax near \'. The following...more >>

Hi I have column FName of varchar type. FName ------ Mktg_2004_03_11Completed Fin_2004_03_10Test Acct_2004_03_09Failed I want to get get result set as FName ------ 20040311 20040310 20040309 I tried using replace, stuff but couldn't make it work. Plz. help Thanks Ste...more >>

[Select fails with "Arithmetic overflow", after indexing a decimal column] Does anybody know why a select could fail, after indexing a decimal column? Please find below a test case. OS: W2K SP4 MSSQL @@VERSION: Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Dec 17 2002 14:22:05 Do...more >>

I need to read one table sequentially and use some values from that table to check to see if the value exist in another table. If they don't exist I need to update the status field in the first table with the Letter 'A' for an add status. I am new to SQL Server programming. Thanks for the h...more >>

is it possible to add a @var to a string??? declare @dbname char(5) select this,that,the_other,thing from @dbname.dbo.tbl_test where id > 10 ...more >>

hi all of us, What works faster? This one: select count(*) or: select count(' ') Thanks a lot fellows, ...more >>

I've got a database in mode 'suspect'. I try to pass to offline but I can't. I obtain the following error: " Error while closing database (name) cleanly. ALTER DATABASE failed. sp_dboption command failed. " Any help will be well appreciated. Cheers, ...more >>

Dear all, With using a query on the table layout I have a performance problem: -Table name: Values -Columns: Values_LogTime (Date/Time) Datapoint_ID (Interger - Foreign key) Values_Value (Numeric 12 - 4) -Indexes on Datapoint_ID and Values_LogTime To report this data I'm using the fol...more >>

In MS Access I can do in one SQL statement a update if exists else a insert. Assuming my source staging table is called - SOURCE and my target table is called - DEST and both of them have the same structure as follows Keycolumns ========== Material Customer Year NonKeyColumns ======...more >>

From almost two days I'm looking for a way to convert a image variable to a float variable. I use a function to create a table and before this I must convert a image into a float value, by mean a loop cycle that read, starting from a pointer, all the bytes coming from the image(varbinary). T...more >>