How would I go about telling SQL Server to execute a script from a C# application? Would I simply use ADO.NET's SqlCommand object - and have the command text be a string that is the script (i.e., read the text file containing the script into a string and set a SqlCommand object's CommandType...more >>

Hi, Whenever I try opening the localhost web project in VS.NET 2003 it gives the following error "The connection with the server was terminated abnormally". Can anyone tell me the solution for this? Thanks pinsu ...more >>

Arithmetic with integer and money data types produces invalid results. Is this a known SQL bug? Are there any guidelines for doing arithmetic with the money data type? The following operation select round(@amount *((@units*@unitPrice)/@invoiceTotal),2) returns an incorrect value if @amount,...more >>

Our client has a large table in our database that has over 1.3 million records. While upgrading our software, our system is trying to add 2 new columns to the large table, but is timing out. I know for a fact that there is no use while we try to run the update. Also, in the past when this hap...more >>

Hi, Should you put an index on a BIT field? Thanks in advance. ...more >>

I have a database table with 464473 records in it. The file is an opt-in list. Each record has a unique id number which are not sequential. I need to split the file in half or find the id number of the mid-point in the file. At some point I might need to split the file 3 or 5 ways. HELP. ...more >>

The same query run on two SQL2000 server returns different results. This is the offending line: AND invoice_hdr.customer_id NOT IN ('156616') On one server I still get records with customer_id of 156616. Table schema is identical customer_id is VARCHAR(12) There are no extra spaces that ...more >>

Why bother? Planning on building new servers in the next 6 months. Any point in dumping 32 bit? Bob Castleman DBA Poseur ...more >>

Hello. We have an easily reproducable situation illustrated by the following script: -- Create a table DROP TABLE Test GO CREATE TABLE Test ( Column1 int ) GO INSERT Test VALUES (5) GO -- Create a table-valued UDF on this table DROP FUNCTION dbo.MyUDF GO CREATE FUNCTION MyUDF()...more >>

Whenever a user enters an apostrophe ' the program bombs with the error "Unclosed Quotation Mark before the Character". I resolved the issue as follows -- replacing any apostrophes with a character that looks sorta kinda like an apostrophe from the Character Map (Start->Programs->Accessories...more >>

Executing insert into [NewDB]..DataTable select * from DataTable sporadically returns (EOleException): Could not find database ID 63. Database may not be activated yet or may be in transition Gory details: We are copying the contents of about 10 tables each time to another identical dat...more >>

I am having a problem using the stepping feature of the Query Analyzer debugger. I can run the debugger without break-points, but when I set a break point all the stepping buttons (step into, step over, etc) on the tool bar are greyed-out. What am I doing wrong? -- PRichards -- PRic...more >>

I was wondering how I can create a batch file that call a store procedure and put the output on a file. ...more >>

When I put the: right (MyCol, len (MyCol) - charindex ('\', MyCol)) in a where statement: select First_name from Employee, Name where ltrim(rtrim(right (Analyst, len (Analyst) - charindex ('\', Analyst)))) = ltrim(rtrim(ID)); NOTE: The Analyst is a field in the Name table and ID is a fie...more >>

I have a TableA and if UserA is reading and modifying some data, then I want UserB and all the rest of the users must wait until UserA is done modifying that row of data. How this is done in SQL DATABASE? Please advice. Please give me an example. Thanks...more >>

can someone explain solution to the following syntax error : The following error occured while executing the query: Server: Msg 156, Level 15, State 1, Line 13 Incorrect syntax near the keyword 'IF'. ============================================ create FUNCTION fn_test_yaks ( @admin nvar...more >>

I want to monitor the activity on a specific table, but I can't seem to get the Profiler to cooperate. I know this table is active because it is a locking table that we use to lock interdependant modules from conflicts so it is constantly being hit with inserts, updates and deletes. I tried ...more >>

I'm trying to find gaps in sequences of year + month values, but there's a twist - I want to return the 'reverse' of the gaps; or the year + month values that create a sequence Note below that a 'sequence' can be a single month if it is bounded by 'gaps'; i.e., ('NJ', 2002, 5). Here's the DDL...more >>

Hi ! I hope you can help me with next problem ... I've a query like this: 1. select <columnTable1, columnTable2> 2. from <3 joined tables> 3. group by <columnTable1, columnTable2> The tables have 11500000, 1500000, and 10000 rows. When I prove 1 and 2 only, not problem, it runs ve...more >>

Hi i'd like to grant execute permission to my store procedure, is it possible do a loop to do that?? while Grant Execute ON @name_sp TO YourAccounttoGrant for all the sp LIKE 'c%' thanks Caròo ...more >>

Hi, I am trying to save the query results from Query Analyzer into a CSV file, but it does not contain the field names, it only save the data and not the corresponding field names. is there an way to do this withing Query Analyzer or via a T-SQL statement. any sugestion are very much ap...more >>

I have an ugly query that is left joining a parent table with multiple children tables. The actual query runs slowly in Query Analyzer (QA) e.g. 3 secs, and horribly slow e.g. 30 secs when its run inside a stored procedure using exec @myquery, @parmdef, @param1, @param2... @paramn Platf...more >>

Hi, I am able to create an additional table by selecting fields from another table, thing is I need to create table # 2 in a different database on the same server, this is my sql: Select userid, TEMPFleischutID into ZZFLEISCHUTEMPIDS from users This will create the table on the sa...more >>

Where can I find help files (.CHM) or other docs on the use of the various osql.exe command line parameters ? ...more >>

Hello World, Is there a way not to drop and create the table with temp table to alter a column as in the subject? Thanks, C TO...more >>

Hi i need to assign an execute permission to a number of store procedure, i'd like use a script to do that... can anyone tell me how?? thanks carlo ...more >>

I have a stored procedure that I use to insert data, and I want the sp to return the newid created. I used to be able to do with select @@Identity with integer fields as the identity field, but it returns nothing when using uniqueidentifier as the type. Thanks. ...more >>

Hi everyone, I'm having problems with this and hoped you might be able to help. I am trying a bulk insert of a CSV file to sql server. No problems there, however, the application i am writing is web based (asp.net), so a client may upload a CSV from their local PC to a web server. I then nee...more >>

Hi, This might be a simple one .. I have a table with two fields (There are more but Ill concentrate on this ones). One field is 'address', the other is 'address2', both are nvarchar If I want to select both into another table but 'concatenate them' how can I do this ? At the moment ...more >>

sql2k is there a system variable or function for finding out hostname? (not talking about sp_who2 here). i'm looking for something like 'select system_user' returns current login user, 'select gettime()' returns the current system time. thank you. ...more >>

I have a table with the following columns: item site vendor 1 ME company1 1 NH 2 VT company2 2 NH 2 ME company2 3 NH 3 ME comapnay3 I want to update the vendor field so that they...more >>

Say I have the following output from a view on SQL Server 2000; RegID StudID SeqNo EnrolNo Name Status Prog AttendDate AttendCode 20 0123036 01 0123036/1 COLIN ANSELL C ENCOMPOL/D1/A 8/8/2005 X 20 0123036 01 0123036/1 COLIN ANSELL C ENCOMPOL/D1/A 15/8/2005 X 20 0123036 01 0123036/1 COLIN AN...more >>

Hi everyone, Using SQL Server 2000, what would be the best way to get a dump of a database that is on a server at a client? I need to be able to say "Here, run this script/app/command" and it has to be simple enough so that the person there is able to complete the task. Then, the person will...more >>

I have a database with one large table in it. My process drops this table and recreates it with data from another set of db's. This process has worked for a long time, but recently when I try to drop the table, I get this error: Server: Msg 1204, Level 19, State 1, Line 6 The SQL Server cannot...more >>

I have noticed that when I use Transactions it takes up a lot of Resourses and time when I make a call. If I remove transactions it is almost instant. Is there a way I can speed transactions up so that I can use them? Daryl ...more >>

I have a table (SQL Server 2000) that stores icon files in an Image field. I have no problem storing and retrieving the images on my system. When I back up the database however the rows in that table do not seem to get backed up. In other words when I restore the database on another server t...more >>

Is that possible? Thanks a lot,...more >>

I have a varchar column, I want to find the records where the value is a number only. TIA CD ...more >>

I want to EXEC sp_grantlogin from within a stored procedure. I'm passed a username, but also need the domain - as per the BOL ===================== Syntax sp_grantlogin [@loginame =] 'login' Arguments [@loginame =] 'login' Is the name of the Windows NT user or group to be added. The Window...more >>

I have setup a case insensitive database that seems to work in all cases except when dealing with the letter a. I suspect this has something to do with the collation. The SQL code at the bottom of this message can demonstrate the problem. The select * from cameraassignment fails when the a ...more >>

Hi, I would like to be able to hide one of the SQL Server on the network so that no one can see it whey they register the SQL Server. How can i achieve it? Thanks Ed...more >>

I have heard that the most time consuming task into a DB is the when we use the Insert Statatement, Is that true? Ok I have an application that sends from 10 to 500 rows to the DB, those rows are compared again a fixed value and then they are inserted to the DB if the comparison is ok. ...more >>

I have heard that the most time consuming task into a DB is the when we use the Insert Statatement, Is that true? Ok I have an application that sends from 10 to 500 rows to the DB, those rows are compared again a fixed value and then they are inserted to the DB if the comparison is ok. ...more >>

After DBCC SHRINKFILE(dbname_Log,10) The message: “Cannot shrink log file 2 (dbname_Log) because all logical log files are in use.” had been appeared. How to decrease the log file size ? ...more >>

Dear all, I've got a couple of MDF which I would like to migrate to Jukon. I was wondering if there is any problem, hindrance or incompatibility once done detach from Sql2000k and then, attach from Jukon. Best regards...more >>

Hi All, I am working on a multithreaded application where 10 threads, take top 10 records from a table they pick those records (simultaneously) , update status with U ( Under processing ) After processing, update status with D ( Done ) so as multiple threads are working on same table ( co...more >>

Hi, I'm trying to create a new table by merging two files together. They both have exactly the same table structure. I.e. they are both got 1 field called ref varchar(255). The code I'm using is: INSERT U_T_XmasOnly(REF) SELECT DISTINCT ref FROM U_T_AttXmasOnly UNION SELECT DIS...more >>

Yesterday, I found I could not do a select into in my development staging database. I received the following message.... Server: Msg 9001, Level 21, State 3, Line 266 The log for database 'DB_ST' is not available. Connection Broken I found that there was no log file!!! I have no idea why n...more >>

Hello When I run this cursor I get the value 01042005 inserting into my table twice when the cursor gets to the end of the recordset. How can i store just one instance of this value? I thought coding WHILE @@FETCH_STATUS = 0 would stop it from looping again when it got to the end of the recor...more >>

We had a DTS package that was working fine until yesterday and since today it does not work and we get the following error message: [Microsoft][ODBC SQL Server Driver][SQL Server] Login failed for user '(null)'. Reason not associated with a trusted SQL Server connection. Please clarify ...more >>

Hi, I have a mail box in one machine. I just want to download all the mails from that machine to the server which is running SQL Server 2000 and store them in the database. Is this Possible? If so, let me know how this can be done efficiently. I would like to download mails automatic...more >>

Hi I was wondering if it is possible to run a scheduele from the command promt. I have a job that runs every night that updates my sales in my datawarehouse. That jobs executes several DTS-packages. But sometimes some of my users wish to update the sales in the middle of the day. So now I ...more >>

Hi All, I need some info regarding the TimeStamp column. I am using the timestamp column to mark a row as unique. SQL Books Online says that the binary generated is unique throughout the whole database. But my question is, does timestamp generate the "unique" binary numbers in an incremental f...more >>

Hi, I have an UDF like this: ---------------------------------------------------------------------------- ---------------------- CREATE FUNCTION F_GET_ID(@TABLE_NAME VARCHAR(128)) RETURNS BIGINT AS BEGIN DECLARE @SQL NVARCHAR(1000) DECLARE @RESULT INT SET @SQL = 'SELECT @RESU...more >>

Hi, At link http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql7/html/queryproc.asp chapter Query Optimization there is description of "Moving GROUP BY Clauses". How it can be controlled programmatically? I'm using SS2000 Enterprise edition SP3 on Win2000 Advanced Se...more >>

Hi, I have a task that needs to filter large datasets by date, ignoring the time. However, we do need to store times alongside the dates for other uses. The way that had been implemented was to convert to a varchar and then back again e.g. convert(datetime, convert(varchar(10), col_n...more >>

Hi, I've a column (type: varchar) in a table. I need to add single quotes in the value while inserting the rows. But I get an error. Please see below: create table #t1 (address varchar(20)) go insert into #t1 values ('St John's street') Error: Server: Msg 170, Level 15, State 1, Line...more >>

I work for a telemarketing company. I have a table that looks like this: Proj Emp Task Hours A 1 sales 10 A 2 sales 15 A 3 sales 5 A 4 sales 20 A 5 QA 10 A 6 Audit 5 For project A, I need the total h...more >>

Below is a statement that will return both TRUE and FALSE cases: select (mtdat-mldat<=4) as PassIt from LpsMasFl where year(mtdat)=2005 and month(mtdat)=5 How can I modify the statement above so that it returns only the count for TRUE cases ...more >>

I tried to read the documentation for COLLATE but all it say it is used to defined a collation. Which I have no idea what it is... Anyone could shed some light on this mystery? ...more >>

Hi All, I have a problems with my query on how to come up with my desired results. Below are my two tables with sample data: tableRegion ------------------- ColPrefix | ColRegion ------------------- 0044 | UK - National 00441 | UK - Mobile 001213 | USA - California 001 | USA tableN...more >>

Ok peep, here's a real techie question which I'm currently attempting to answer for myself. I have a SQL 2000 database which contains indexes. However, the database wasn't properly maintained and the indexes are not named properly. To make matters worse there are several seperate instanc...more >>

Hi, guys we just encounted a big problem of transactions locking during we test our program under ms-sqlerver2000 database. we update a record in a table named Tab1 in a transction but without commit it, then in another transaction, we tried to "select * from Tab1", but it got locked. after...more >>

Hi I need a procedure that could take a parameter that actually is EITHER varchar or int. I use this: CREATE PROC prc_1 @strParam varchar(50) .... DECLARE @intParam int DECLARE @varParam varchar(50) SELECT @intParam=CAST(@strParam AS int) IF @@ERROR<>0 ^^^^^^^^^^^^^^^^^^^^^^^^^...more >>

Hi, I am designing a Maintenace contract database. tblAgreement, tblMaintSystems, tblSchedule,tblVisits etc. Each is joined by the FK. Since service contracts expires after a certain time, i would like to move the expired contracts based on either time or by a string condition like 'Expired =...more >>