Hi, We have a DTS to create an excel file from a select statement, all it is ok, until we have to add an elaborated header to each Excel file created. To create a macro would be very cumbersome to maintain. then we decided to create an Excel file with only the header to insert. How Can we d...more >>

Hi I have an inventory database with table names like: Win32_Account Win32_BaseService Win32_BaseBoard each table has a common field [idHistory] I need a procedure that will get all table names from sysobjects where Left(name,6)='Win32_', and then for each table, return the table name ...more >>

Hello: I have a table T like this: ID | VAL | Startdate | Enddate 1 | A |1992-01-15 | 1998-12-31 1 | B |1998-12-31 | 2000-03-31 2 | C |1991-04-08 | 2003-01-25 2 | D |2003-01-25 | 2003-06-30 2 | E |2003-06-30 | 200...more >>

Hi I have an inventory database with table names like: Win32_Account Win32_BaseService Win32_BaseBoard each table has a common field [idHistory] I need a procedure that will get all table names from sysobjects where Left(name,6)='Win32_', and then for each table, return the table name ...more >>

Hi All, I have a table with a DATETIME field that was used to store only a date. The table also has a TIME field used to store a time value as hh:mm:ss I need to convert the datetime field to represent the date that it currently holds AND the time value in the time field. Can this be do...more >>

I am trying to understand this whole permissions thing. So I have an example that is confusing me. I have a database - VDW. I have 3 users: Name Login ID Database Access dbo sa Permit ...more >>

Hi *.* I plan to crate a database and want to know if it's good to use autonumber columns as my primary key or I should generate the numbers myself? Also, If I want to replicate the DB, is it ok to use autonumber columns? At last, Is there any way I can manage the autonumber start number?...more >>

i'm getting a cryptic error when i try to save my UDF: Error 170: Line 24: Incorrect syntax near '.'. The full DDL is below, line 24 being marked. i don't see anything wrong with my DDL, so i assume that i just cannot perform a JOIN to a UDF's return table. i've tried creating a temporary...more >>

Hi, I used sp_addlinkedserver to link to a remote server through ODBC. When I execute select count(*) from LinkSrv.SI.DBO.SIHeader in SQL Query Analyzer. It returns 13705 records. But when I execute select * from LinkSrv.SI.DBO.SIHeader. It only return 885 records. If I specify some columns, ...more >>

Hi, I used sp_addlinkedserver to link to a remote server through ODBC. When I exec select count(*) from LinkSrv.SI.DBO.SIHeader in SQL Query Analyzer. It retures 13705 recouds. But when I exex select * from LinkSrv.SI.DBO.SIHeader. It only return 885 records. If I specify some columns, select...more >>

I have a column, type varchar(1000). I use access project as interface to interact with sql server. When I input value into the column, the server change my input. It only store the first 100 something characters right. After that, it replaces my input with the first 100 characters. Basically, it r...more >>

I have a question regarding how to, in the most efficient way, determine if I have duplicates in a grouped query. Running the following: select lot, transtype, condition from [transaction] group by lot, transtype, condition having transtype = 'production' order by lot, condition Returns: ...more >>

Hello, I have a huge dynamic sql-select and DECLARE @sql varchar(8000) is too small. But with DECLARE @sql text I get an error message. Is there a solution? Thanks in advance for any help! Andreas ...more >>

Hi, can someone help me here- what is wrong with this: INSERT INTO #temp ( code, city, compnum, lastdate ) SELECT DISTINCT A.ArrAprtCd, LEFT(A.ArrCtyCdT, (CHARINDEX(',', A.ArrCtyCdT) - 1)), A.CompNum, LEFT(A.ArrTime, 8) FROM cwtAir AS A, cwtItinerary AS I WHERE (I.It...more >>

If I have a variable - @test = "vcd_theCode" or @test = "vcd_joesDemoProgram" I want to take out the "vcd_" from the variable. I assume I would do something like select substring(@test,4,len(@test)-4) Is there a better way to to say - start from 4th character and go to the end of the s...more >>

I was just assigned the task of estimating the fine tuning our SQL2k DB. = I am a developer not a DBA. We recently moved off of Sybase onto = SQL2k. =20 I'm interested in the best approach. I had in mind doing the following: 1.. Request the DBA to do a trace and profiler our producti...more >>

Hello. Here is my problem. I have a CSV file on a server, let's say "server1". I have also a stored procedure, into a SQL Server database, which is located on another server, let's say "server2". I need to connect from the stored procedure to the CSV file, using OPENDATASOURCE, like this: O...more >>

SQL Server 2000 I need to work out the VAT (17.5%) to be charged on the net price of goods, and it should be rounded up to the nearest penny. For example 17.5% of £94.70 is 16.5725 so I need this rounded up to £16.58 Any help appreciated as I need to make sure I get it right :) A function ...more >>

Hi All I have a excel spread sheet that i need data from. This is the Data. MyID TextField DateField IntField FloatField 1 Ian One 01/04/04 10 10.0304455900 2 Ian Two 02/04/0...more >>

Hi Does anyone know how to write a script that can get the database name, all the tables in the database, the index names defined on these tables and the columns used in the index in one script? DATABASE; TABLE; INDEXES; COLUMNS I know i can get the database name from sysdatabases and the...more >>

hello i have 3 tables Issues, distribution, returns i would like to get out for each issue the sum of quantity in distribution table and the sum of quantity in returns table i perform this select but does not work well because the table distribution and returns have different rows results ...more >>

Hi folks! What would be the return type of the following statement : convert(datetime, getdate(), 111) ? I tried to stick this value into a varchar() and into a datetime data type but i didn't got any errors.. --- declare @s varchar(50) declare @dt datetime set @s = convert(datetime,...more >>

I am trying to grant permission to different tables that are passed in and I get an syntax error. The following works fine. grant select on vdw..empBasi to DailyProductionReport_Users This does not: ***************************************************** declare @theTable varChar(100) s...more >>

I need to access the value of dynamic SQL statement within a stored proc. Something like this... DECLARE @z int DECLARE @t varchar(10) SELECT @t = 'authors' SELECT @z=EXEC('DECLARE @c int SELECT @c = Count(*) FROM ' + @t + ' SELECT @c') The dynamic statement will execute and r...more >>

I need a simple way/script/batch/whatever to allow the following: 1. STOP SQL Server 2. Do a backup of database using a program that requires files to be closed 3. RESTART the SQL server Is there an easy way to do this? ta...more >>

Is here any alternative way to accomplish the function like "delete top 100 from table " eg. DELETE Table FROM (SELECT TOP 100 id FROM table) AS tablealias WHERE table.id = tablealias.id any other way more simple? ...more >>

Here is my result set A B C D -- -- -- -- 1 A2 2 100 <-- 2 E2 2 90 3 R1 1 80 4 E1 1 70 5 A1 1 60 <-- 6 R2 2 50 A is an incrementing column B is a text field with An, En, and Rn being to the same group C is An, En, and Rn's rank. A1's rank is 1 and A2's rank is 2. D is a field tha...more >>

Hi again! I'm trying to execute the follwing code from Excel. I receive an error at the execute line saying there is "Incorrect Syntax near 14". 14 is the current hour (24 hour clock). eDateTime is a value in a view that I've created. What am I doing wrong? (UserID and password both work...more >>

I am running a stored procedure which contains a temp table with a index with IGNORE_DUP_KEY. I am getting the error message that "Duplicate key was ignored" but it is an error, rather than a warning. Can this be suppressed or changed somehow? ...more >>

What do you think? Is there any performance difference between some_text LIKE '%some_str%' and CHARINDEX('some_str', some_text) > 0 where "some_text" is any expression, e.g. char column, constant, function return and etc? ...more >>

I am trying to insert record in a local table grabbing data from a linked server. I have teh same datatype in both the database (datetime) but when I try to run the insert statement I get the following error.. Server: Msg 7347, Level 16, State 1, Line 1 OLE DB provider 'SQLOLEDB' returned ...more >>

Hi all! Below is my View statement. When I execute it, I want the time to appear as 24 hour for eDateTime. However, it appears in the grid as AM/PM, even though I specify 114 and 120 in the Convert statements. I'm relatively inexperinced to T-SQL, and am wondering if I've done anything wron...more >>

I had to move/restore one of our dbs from ServerA (sql 2k on nt5 box) to ServerB. Windows authentication is used to authenticate users to the server. The SQL ServerB does not have an account for Domain\Staff (under Security/Logins), but the restored db has a user called 'Domain\Staff' and it...more >>

I changed the collation type on a database to SQL_EBCDIC037_CS_AS, but SQl server is still sorting by ASCII. Any help into why SQL is still sorting by ASCII and not by EBCDIC would be much appreciated. Thanks. Jeff...more >>

Hi All, All my stored procedures which use linked server to retrieve data from a remote server recently ran into this error: "Server: Msg 7391, Level 16, State 1, Line 1 The operation could not be performed because the OLE DB provider 'SQLOLEDB' was unable to begin a distributed transaction...more >>

Hi I have a rather complicated stored procedure which is really slow. I have now know where it is that the slowness occurs, its in the "Where Clause". Now i know that the where i have is extremely inneficient so im looking for some help to make it more efficient. Ok so my VB applicatio...more >>

I have articles table like this: tblArticle ---------- article_id article_title article_text article_title and article_text are part of full-catalog. And among those article records there is one record like this: article_id: 999 article_title: "Michael Moore..." article_text: ".......more >>

Hi all, I need to index local temporary tables in a stored procedure (SQLServer 2000). Is it necessary to use #-prefix in index name, too? (didn't find it in BOL). Cheers, Arto Ojala ...more >>

The sqlserver agent service depends on sqlserver service. For sql server stop/start alerts , two conditions must be met. 1) sqlagent should be alive as alerts depend on it. 2) Sql server should be stopped. How is it posible i one depends on other? How to test these alerts?...more >>

Hi all, I'm using asp.net and sql server 2000. I will outline below the exact situation so that it is clear what I'm = after. I have a table pulled in from an AS400 into SQL server on a daily basis: Father Son Coef SW %Solid ------- ------- ------ ------ ------ 1010012 ...more >>

We just got a new server that needs to be configured for reporting. This server will have writes only in the middle of night for refreshing data and some denormalization process. Rest of the time, it will have READS ONLY. I need to know if there are specific configurations that need to b...more >>

DECLARE accounts_cursor CURSOR FORWARD_ONLY FOR exec getAccountsData @AccountId getAccountsData is a stored procedure. Is this not possible? I cant execute a stored procedure and open up a cursor on that? Thanks, Girish ...more >>

Hi all, I have a Stored Procedure (named spTest) that performs a SELECT statement at the end (returning a recordset). I can access this recordset with no problem from an ASP script. However, in addition, I need to access this recordset from a different Stored Procedure. I've tried the fol...more >>

What happens if a user changes data during a backup? Say a child record gets backed up and then the child and parent records are both deleted before the parent table gets backed up, will orphan data exist? Or does it somehow remember the state of the db when the backup starts and not backup record...more >>

How do I use the results from a stored procedure, as a subquery? I tried using OpenRowSet, but docs say I can't use variables, and I need to be able to change the connection string. I then used a temporary table, but the execution time went high up. I guess I could create a string with everythin...more >>

Is it possible? Here's an example: SELECT * FROM Table WHERE nom LIKE '%Belanger%' I got two type of Belanger in my table, one with an accent ('Bélanger'), the other, no accent ('Belanger'). Right now, it only returns me 'Belanger' with no accent... Is there any way I can ignore the acce...more >>

I'm strongly considering changing a stock inventory system's method of keeping on-hand balances. There are two tables involved, an item master and an inventory transactions log. Inventory levels are maintained in a column in the item master with insert triggers on the transactions table by summin...more >>

Well, I thought we has provided a great solution for our users who are constantly wanting greater access to our data for their reports. We created a process to allow the user to set the criteria for the data they wanted and to have them sent a CSV file with the requested data. But now, our us...more >>

Is it possible to set a default value for NOCOUNT? I have collegues that have assumed that it always defaults to OFF, however clients seem to insist they have it always set ON on there servers. However I can't seem to find where this can be set other than running the SET NOCOUNT command in t...more >>

Hey all, I have run into a problem and I am completely lost.. I wrote a couple stored procedures to be used by a little helper program to delete and remake a table, upload some data to the DB from a text file the program read in, and finally, apply a couple indexes to the newly made table (Te...more >>

Hi All I have the following sql statement: - Select Company.Name As CompanyName From Company Order By CompanyName Which causes an error. I do know that this would work: - Select * From (Select Company.Name As CompanyName From Company) As Tbl1 Order By CompanyName But is there a way to ge...more >>

Hi, We access a website that only provides its info in an HTML table. I would like to be able to write a function that would access the URL, strip the HTML info, and return the results in a true-to-life SQL table, so that we can interact with the data in real-time rather than having to g...more >>

I am running a restore on SQL2000 sp3a. The restore states complete but the status remains (Loading). I have tried restoring through EM and Analyzer. I'm currently trying to restore the backup files on another server to verify the integrity of the backup, but pretty sure that it will be...more >>

I am trying to pull information from inserted values on tables that have = fields with Text datatypes.....I am having strange results even if I am = not selected the field with the Text datatype Tables: Table1: test [seq] [int] IDENTITY (1, 1) [field1] [varchar] (50) NULL [field2] [te...more >>

Can an IDENTITY column be reset to Seed 1? i.e. if you have an INT IDENTITY column that is is topping out can you reset the identity to 1 and have it count up from there? Would all records in the upper range of values need to be DELETEd or shifted down to the lower end of the scale? TIA -- ...more >>

Hi all, I am trying to generate insert scripts for master tables in SQL Server 2000 Developer Edition but when I go to 'All Tasks' -> 'SQL Scrtips' it generates only the Create table scripts. It doesn't generate scripts for the data in the table. I tried using DTS also, but there is no way to...more >>

Hello All I inserted data into my SQL Server table using BCP. THis went fine. When I tried to index, I got the following error. Please advise as to possible causes and solutions. Server: Msg 823, Level 24, State 2, Line 1 I/O error 23(Data error (cyclic redundancy check).) detected during r...more >>

from access 2000. I'm trawling through some of the queries and upgrading the big ones to SQL views. My question is, in Access you can use the following kind of statement: Since: IIf([FundSpecificText] Is Null,Format ([ConnectedFrom],"mmmm yyyy"),[FundSpecificText]) In other words, i...more >>

if a user changes a date field, I would like to display the old date as well as the new date in an xp_sendmail, on an update trigger how can I store the old date data ? cheers ...more >>

Hello, I have a table in my SQLServer1 (SQL2000) this table has a Trigger For Delete The content of the trigger is: insert into SQL2.myDb.dbo.myTable select * from deleted The SQL2 is a linked SQL2000 Server But, when I delete one row from myTable the Enterprise Manager hang and stop ...more >>

Hi all, I have a column with data type as "smalldatetime". how to retreive values grouping on this columns without considering the time part. i.e if this columns contains values like 7/15/2004 5:48:00 AM, 7/15/2004 7:48:00 AM... it should return me as only one record of 7/15/2005. Note: I need this...more >>

Hi I'm trying to write a query that will only show me only records where there are character returns, line feeds etc. I can do an update and do a replace but cannot seem to work out how to do a straight forward select statement. I'm sure its simple but any advice would be appreciated. Thanks ...more >>

Hi there, Sorry I’ve taken so long to get back but it’s uber-busy here. I asked: “I've created a view using most of the columns from one table (tblADCStudents), half a dozen Case statements and one column from another table using a join, I can enter new records into this view but when I tr...more >>

I am trying to write an update statement which should update table shipmentitems, column partrevisionid with '01' where the inner join on the partid is equal to partnum in the allpartmaster table. I only want the update to occur if the prodgroup in the allpartmaster table is equal to 'IMPLANT' I...more >>

Hi Gurus, When I execute the following statements, I got the error as: ------------------------------------------------- Server: Msg 170, Level 15, State 1, Line 16 Line 16: Incorrect syntax near '>'. Server: Msg 170, Level 15, State 1, Line 24 Line 24: Incorrect syntax near '>'. -----...more >>

I have developed a Windows Forms Control that renders ReportingService reports as a metafile, This way the reports can be shown on as 'Crystal Reports' does in the wysiwyg way. But when the reports contain some hundered pages (and that happens often) the rendering of one page takes up to 3 seconds. ...more >>

Hello, I am using sp_executesql to execute statements stored in a table column. I want to execute a statement containing a 'UNION'. The statement stored in the table column is: SELECT @RESULTA = PATENDATE FROM PATENTRY P1 ,TBLNOSHL TB1 WHERE P1.SUPPID = TB1.ITEMID AND P1.PATCODE = @P...more >>

Hi I write a store procedure that used the full-text I try to use the freetextable option: Select Rank,conceptid From freetextable (concepts,*,'anyword') as ft Join concepts as con On ft.[KEY] = CON.CONCEPTiD But I get the error: 'concepts' is not a recognized OPTIMIZER LOCK HINTS opt...more >>