Hello folks, I'm using a port other than 1433 for my SQL Server 2000 database and am trying to write a vbscript to automate client's ODBC DSN creation. Is it possible to specify the port used in such a script? I have "Database", "Descriptions", "Driver", and "Server" additions to the cl...more >>

Id like to dts a lot of data( around 100GB) from one table to another.. How can I do so without blowing the Tlog ? Basically I guess I need to commit more frequently and dont know how to do so Or maybe I could use an insert select statement to do this instead of DTS But how can I commit afte...more >>

I am trying to track down a problem of calling a stored proc from some ..NET code. I would ultimately like to be able to examine the log files to see what SQL Server is trying to run. I see from several other postings that examining the log file is not feasible. Since I am doing just devel...more >>

I am bcp'ing a table and I am getting this error when the bcp actually finished its task. Its doing the task and spitting out this error to the client? How can I resolve this? Here is my bcp stmt DECLARE @bcp varchar (2000) DECLARE @result int SELECT @bcp = 'bcp "select t2.* into db...more >>

I have a SP which I have had running for several weeks now. It run in ~20 seconds every time, until last night. Last night the job that runs the SP started, but did not complete after over 7 hours. I killed the job at that time. There was no blocking, nothing looked out of the ordinary...more >>

I have a view that works when the compatibilty level is 65 and not when 80 is selected. Here is the view: ...more >>

Select col1 From (Select * From tbl1 Where somecondition = 1) I am getting a syntax error when I try a query as above. I could swear I have done this before. Could someone point out what is incorrect above? Assume col1 exists in tbl1. Thanks, Rich...more >>

I updated my SS2K password to something longer and nastier. I'm getting tired of typing it every time I bring up a fresh instance of QA. Is there a way I can get QA to log in automatically from a password I supply once, like in EM? Anything involving storing the password in plaintext is not an o...more >>

I have a stored procedure, which in turn will call three child stored procedure. It runs exactly correct, and produces the exact results desired when called from Query Analyzer, and takes about 1.5 seconds to run. The exact same query, with the exact same parameters, will execute from some ...more >>

I understand the "create unique" option when creating a new index. I don´t understand the diference between the two next options "constraint" or "index". Does a "create unique" with "constraint" works like a index for accelarate queries ? And the difference between index with "ignore ...more >>

We are currently experiencing a blocking issue on one of our SQL Servers. The majority of the time, our database is running fine and our stored procedures return in less than 4 seconds. For the last 4 weeks, once a week, locks begin to occur and they are endless. The locks are not deadlocks...more >>

Hello - I am having a brain glitch. Picture this - three tables: tblMain, tblType, tblMainType (tblMainType is the ajoining table). I just inserted 376 records into tblMain. tblMain has MainID as the primary key. I now have to insert 376 entries into tblMainType. tblMainType consists...more >>

I have a statement that I need to dynamically update each of the rows in a table. However, some of the data also comes from the same table, and I need this info to select the rows to update. Here is the static statement: update dmdpg_pf set (shortqty, skdlym, skdlrrn) = (SELECT (dmdpg_pf...more >>

Hi all, I support an application that the users have requested I manipulate 1 field for better sorting. The vendor is having a hard time sending us a patch to correct the problem at the frontend. So I've been asked to run a script to change the data into a different date format(from mm/dd/...more >>

I am not a programmer but need to a write a store proc to make something work. I have a query that needs to refresh the table based on the following busines rules: 1. If the RunDate is between 1 and 14 then exclude the current month and update only 3 months before the current month (total...more >>

Hi Would any .net application developed for sql server 20005 server edition work on sql server 2005 express edition? This is in case the target client does not have a license of sql server 2005 server edition. Thanks Regards ...more >>

Hi In the context ox a vb2005/sql server 2005 app, how useful is it to use stored procedures? Thanks Regards ...more >>

I wanted to divide 2 integer fields to get percentage result like the following. MYFIELD1 INTEGER MyFIELD2 INTEGER "SELECT CAST(MYFIELD1 AS FLAOT)/CAST(MYFIELD2 AS FLOAT) AS MYRATE FROM MYTABLE" I got float result, but I would like it presents as "95.00%" or :19.12%" Are there any w...more >>

Hi, like the title says I want to delete the common entries between two tables. When I use: SELECT * FROM tblA WHERE EXISTS ( SELECT * FROM tblA, tblB WHERE tblA.Name = tblB.Name AND tblA.ID = tblB.ID ) I get the common entries, However when i replace SELECT with DELETE, it del...more >>

I am trying to write a nested cursor that will tell me first which columns in the database have 'smallint' as a datatype (this work so far)...than I have to print a list of the records in the database (related to the found columns) which contain values that are not between 0 AND -1...I don't nec...more >>

All, I am writing a stored procedure that dynamically creates sql to execute. The WHERE clause of the sql is pulled from a column that is of type ntext, the SELECT/FROM is static. My issue is with concatination of the local variables of 'char' type and the 'ntext' data held in the column(...more >>

Hi All I'm really stuck on this one so would appreciate any help you can give. In essence, I have 1 SQL 2000 table with rows of data logging stock movement. To differenciate between a stock sale and a stock receipt the table has a TRANSACTIONTYPE field so that 8,7 equal invoices and 3 equ...more >>

Can I get date part informaiton from datetime datataype? I just wanted the date part like "07/11/2005" from a datetime data type. I wanted to user datepart, but it only gets a part of date time like date, month, or year. I tried to use CONVERT, but it is no more date type. It is a string ty...more >>

....or perhaps rather the programmer....? I am experimenting with how to create a table, with the table on one filegroup, and the primary key index on another. When I enter the following into Query Analyzer: USE MyDatabase GO CREATE TABLE MyDatabase.dbo.t_Bands ( Band_Id int IDENTITY(...more >>

I found a few posts on this subject but no answers. I am an Oracle DBA who has been tasked with writing a script to obtain the status/availability for all SQL Server databases during our DR test. Needless to say I don't know what I'm doing. If this were Oracle I'd simply query file status fro...more >>

Hello, I have an application that provides a monitor of active help desk calls. It uses a constant ODBC connection. The user does nothing--sends no data to the database. Intermittently this display fails with a yellow triangle and an exclamation point, and no further information. Restartin...more >>

Hello, Is there a way to get a column in Table2 to be have a foreign key constraint referencing a primary key computed column in Table1? I get an error (1784) when trying to create Table2, telling me that the foreign key cannot be created because the referenced column is a computed colum...more >>

I'm try to open a table from Enterprise Manager on SQL server but it seems to be locked by someone I think the problem was caused by a bad ASP script. How can unlock the table? Do I have to reset IIS where the script was running or is there any other better solution? Thanks ...more >>

Hello, I am looking for a way to query for the members of a group from our Active Directory. I have looked around on the web and have seen that one way to do this is to create a linked server. I was wondering if there was another way to do this without creating a linked server. Also, d...more >>

Just after starting the server every thing is OK, but After one day of normal work I can not generate a report, i get a time-out, all i have to do is to stop sql server and start it again, in order to being able to generate the report and so on. this happens only with the most demanding repor...more >>

I wanted to update my table depends on the week doy of today. For exampl: "UPDATE MYTABLE SET MYVALUE = 1 WHERE CASE WHEN DATEPART(WD, GETDATE()) = 1 THEN DATEDIFF(DAY,MYTABLE.MYDATE, GETDATE()) = 3 ELSE DATESIFF(DAY,MYTABLE.MYDATE, GETDATE()) = 1 END" I wanted to update last work...more >>

Products database CREATE TABLE [dbo].[Products] ( [ProductID] [bigint] IDENTITY (1, 1) NOT NULL , [CategoryID] [bigint] NULL , [ProductTitle] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ) ON [PRIMARY] GO CREATE TABLE [dbo].[Categories] ( [CategoryID] [bigint] IDENTITY...more >>

I have two tables. Both have an insert trigger. Table A inserts a row into Table B via the trigger. Table B's insert trigger has a line: Insert Into A (x,y,z) values (1,2,3) Where 1=2 Now, this statement should not be inserting any rows into table A, right? However, attempting to add a...more >>

Hi I have a table that contains order details (DDL at bottom). I want to be able to sum the quantity and group by a user specified time period (e.g. weeks, months). This is straight forward when using weeks or months, because I can do something like: SELECT ProductCode, SUM(Quantity)...more >>

Hi, When I look at the Server Trace in QA, I run two different queries but one with WHERE clause and one doesn't e.g. Select a.* from customers a inner join orders b on a.customerid = b.customerid The Server Trace shows Duration CPU Reads Writes 15 16 51 ...more >>

Hi all, i do have a production database as well as a test database. the production database goes on frequent modifications, at the end of the day, i do have to make the changes in the test database. I generated a script of all the objects and tried to execute in the test database, but...more >>

Just trying to understand transactions and how they relate to locking: Suppose I have a db with 5 tables. Then I have an application that makes use of ADO.NET to interact with this db. The application starts a transaction then proceeds to insert or update rows in each of the 5 tables; first ...more >>

Hi All, Im trying to create an hyperlink inside a result column from an sql query inside an excel worksheet. If I type the text manually into a cell, as in (example) =HYPERLINK("#Sheet1!A1", "Value") it works fine, that is, it shows as "View" underlined, just like a hyperlink, and whe...more >>

Hi I would like to know if there is a way in T-SQL to detect an error in a stored procedure that calls another stored procedure. I would like to catch any error in the inner sp on the outer sp. like this exec sp_inner if @@error <> 0 'do something this does not work, because when ...more >>

Some one posted a link to an artical covering best prectices for working with calendars, and storing dates. I have always stored dates as integers using YYYYMMDD format, but am open for alternatives, esp if they are better options. If you know of the link or have any ideas please let me know ...more >>

Anyone ever seen this one. I have a stored proc that uses SELECT… INTO against a linked server to create a couple of temporary tables, then a little further down it does a SELECT against those temp tables. First thing in the morning I have a BCP that uses QUERYOUT to execute that stor...more >>

Hi Folks, I'm working on a WIN XP SP2 station with SQL2K SPMax (3 or 5 ?) I'm bulk inserting right thousand of lines in my db but the third one below generate error 4864. That's the 2003-29-12 value that seems to be in charge for this problem. MAG|FR|68|0000000Q80977|1|2005-28-6|2005|6|20...more >>

Hi, we have the following two tables in the pubs database of SQL Server authors --contains the list of all authors, with each author having a unique au_id titles ----contains the list of all titles, with each title having a unique title_id titleauthor ---a relationship table contain...more >>

Dear all, I've got a dinamic statement which is failing due to ' character and I don't know why. What's happening? declare @cont2 integer declare @sql varchar(303) declare @operacion char(14) declare @veces integer set @cont2 = 1 set @operacion = 'Actualizacion' set @veces =...more >>

Consider the following code: ------------------------- IF(SELECT SYSTEM_USER)='SQLServerUserName' BEGIN GOTO lblTrue SELECT 'True' END ELSE BEGIN GOTO lblFalse SELECT 'False' END lblTrue: PRINT 'Skipped the SELECT statement under IF' lblFalse: PRINT 'Skipped the SELECT state...more >>

Dear all, I perfectly know that this issue is very strange. I've got a stored procedure which launch a dinamic set of intructions through EXEC statement inside a loop. I enclose you the following snippet: declare @cont integer declare @cont2 integer declare @cont3 integer declare @sq...more >>

Hi, Can anyone explaim to me if a Primary key is a Index? I did try crate this index in this table: CREATE UNIQUE CLUSTERED INDEX UFIdex ON UF(Rotulo) WITH DROP_EXISTING CREATE TABLE UF ( UF char(2) NOT NULL, Rotulo varchar(25) NOT NULL, Constraint PK_UF ...more >>

Hello All, When I restore a backup on a different Server, It automatically creartes the records in sysfulltextcatalogs which refers the Catelogue path of old database. How to come out of this Proble? If I remove the records from sysfulltextcatalogs, then while enabling the Database for Ful...more >>

Hi, I'd like to insert several rows into a table in a multi-user environment and afterwards uniquely identify the new rows without having unique keys defined in the table (except "ID INT IDENTITY PRIMARY KEY"). I know this is not possible in SQL Server 2000. Does anybody know if SQL Server ...more >>

Hi Our app runs on both Access and SQL server. We've a DB layer to manage the differences. I've hit a problem with the SQL Server Date functions, DatePart/DateAdd/DateDiff etc in that the datepart parameter needs to be 'not single quoted' (or depending on DB settings unquoted). I don't know SQ...more >>

I WANT to make a view that import from FoxPro. Can I do? ...more >>

Hi Can some shed some light on this query? I have a table with the following data: Table A ID DATE VALUE =================================== 1 12/07/2005 10:05:22 15 2 12/07/2005 11:00:02 3 ...more >>

I keep trying to set up a 1-many relationship between two tables, but the database keeps insisting on making it 1-1. How can i force this relationship to go in as 1-many? The 1 table will always have a record, but the many table may or may not have a record. the one table has a PK of ID th...more >>

I want to create a view like Crosstab Query at ms. Access. Can I do it? at ms. Access, I ever made query like this: TRANSFORM First([Query1].[FCTIME]) AS FirstOfFCTIME SELECT [Query1].[FCIDNUMBER], [Query1].[FDDATE] FROM Query1 GROUP BY [Query1].[FCIDNUMBER], [Query1].[FDDATE] PIVOT [Query...more >>