Hi folks! The SELECT statement reports following error: Invalid object name '#tmp_descriptions' Why is that? When I create this table without using the dynamic sql it works. Cheers, Adnan DECLARE @SqlString nvarchar(512) SET @SqlString = N'CREATE TABLE #tmp_descriptions ...more >>

create table parent_tb ( department_id int identity (1,1) primary key, department_nm varchar(25)) insert into parent_tb (department_nm) values('finance') insert into parent_tb (department_nm) values ('marketing') insert into parent_tb (department_nm) values ('sales') insert into parent...more >>

I have imported data from excel file. When data came to SQL table, the type of AMOUNT column was varchar. I tried to convert and cast amount type of amount column to number type but it does not allow me to convert. What is the best way of importing data into SQL and type stays the same as ...more >>

Hello, If I have a column length set at 150, I am assuming (though I know I am wrong) that the maximum length of that column is 150 characters. I have heard that it is in fact 150 bytes. Basically my question boils down to this: I want to be able to set a length in the database relevant t...more >>

Hello, What are the steps to migrate mainframe data to SQL server DBMS? The logic steps that I can think of are: 1.. Import all data to SQL server as-is. 2.. Scrub imported data to my [imported source data table]. 3.. Insert desired data to my [destinations table]. Is this t...more >>

Hello, I need to write something that can extract newly added and updated records from other department SQL 2K server to my department SQL 2K server on a MINUTE BASE. These two servers are not linked and I have only ODBC connection to the source data. My questions are: 1.. Wha...more >>

Dear All, Using SQL 2000 I get the following error message when passing the date parameter in the following source Server: Msg 8152, Level 16, State 4, Line 1 String or binary data would be truncated. The statement has been terminated. Any guidance will be helpful Thanks Clive ...more >>

How would I retrieve the datalength (set column length) of a column if I had the column's name? I looked at sysobjects and syscolumns but this didn't seem to have the information I was looking for. Thanks! Best Regards, Brad ...more >>

Hello: I am trying to implement a databse that calls for a hierarchical data structure: There is a possibility that a child of an element may have one of its ancestors as one of its children, If this happens, I cannot build the tree because it will go into an eternal loop. I cannot...more >>

Help says that decimal and numeric types are interchangeable. However, if I have a company table with companyID as decimal(18,0) (primary key) and an employee table with companyID as numeric(18,0), I get an error when trying to set a foreign key relationship saying type mismatch. At the same ti...more >>

Hello, Does anybody know if you can disable a foreign key constraint between two tables, but only disable it for DELETE statements? I mean, I want the constraint to keep enforcing referential integrity for INSERT and UPDATE statements, but not for DELETE statements, as I want to take care of t...more >>

I need to update an image datatype on a remote server. I must make the remote servers image column match the local servers image column. A standard update does not work for image data types. I have views to each table on the local server pointing to the remote servers tables. I have tr...more >>

Is there any benefit to using IF EXISTS(SELECT LineID FROM tblOne WHERE color = 'red') BEGIN DELETE FROM tblOne WHERE color = 'red' END Instead of just DELETE FROM tblOne WHERE color = 'red' ??? -- William Morris Product Development, Seritas LLC ...more >>

I have update problem with this query, basically, I need to update all employee ids to new employee ids . how come I get a syntax error? update employees a1 set a1.employee_id = (select new_employee_id from employees_id_updates where old_employee_id = a1.employee_id) ...more >>

How to pass a Parameter to an MS SQL Stored Procedure - which expecting a date type parameter - from within an VFP Application by using SQLExec How to ensure that the Said SP has been completed ? Assume that gdBillDt = {01/02/2003} -- DD/MM/YY I can call lthe SP in question from Query Analy...more >>

Hello, Before the weekend I posted a question on this newsgroup titled "Greatly different executing times for EXACT same query". I had a few people respond with some great ideas that on the face of things seemed to be the answers for what I was describing. However I have since tried all thos...more >>

Hello All, Here is my situation... I have a table (named CUSTOMER) with the following makeup: rec_id (varchar 100) fname (varchar 100) lname (varchar 100) address1 (varchar 100) address2 (varchar 100) city (varchar 100) state (varchar 100) zip (varchar 100) gender (char 1) ('Y/N' valu...more >>

I created and saved DTS which copy certain table to another sql server. When I try to copy first time it goes fine but second time it error out because the record is already there. Each time I want to overwrite the existing table. How can I overwrite the existing table thru DTS or any other ...more >>

I am hoping that someone will be able to show me a way to avoid using dynamic sql. The problem is that my 'where' clause can change for as many as 200 columns and can be in the where clause as an =, >, <, <=, >=, <>, in, like .. So we basically need to feed the where clause in as a paramete...more >>

when should packet size (ado.net, sql server) be changed? we have a vb.net application that updates/inserts/deltes a sqlserver 200 database; but we notice that at times; it sems to be pausing betwen transactions. this behavior is intermittent. can increasing packet size help? what lese could b...more >>

Is there another way that I could use something similar to a LIKE statement in my WHERE clause? I am looking to optimize performance... the LIKE statement '%name%' slows my query down. Any suggestions? Thanks, Brett ...more >>

I have a trigger that updates the table tblSchJobRunColors when the contents of tblEbook is changed. How do I set it to insert into the other table (tblSchJobRunColors) if a new record is inserted into tblEbook? Here is my trigger below. CREATE TRIGGER [ScheduledJobColors] ON [dbo].[tblEBook] ...more >>

I am using a like statement in my where clause. What kind of index (if any) should I put on my table? FROM [Users] WHERE lname LIKE('%raj%') Please advise.. Thanks, Brett ...more >>

I have one table in SQL called user and it list all the user names. I have another table that has information about the document being scanned in such as document, date, department, username and etc. I am trying to have a trigger fill in the name of the person that scanned the document from the...more >>

I want to use a dynamic cursor in my stored procedure. Let me tell u my requirement. I want to process a report for this data will come from the table but as my criteria list is too big so I just want to proview whereclause in the select statement for only those columns which are filled by user ...more >>

Code :1 ====== IF EXISTS (SELECT EventId FROM BTSeamless WHERE EventId = @EventId) BEGIN delete from BTSeamless where EventId = @EventId END Code :2 ======= delete from BTSeamless where EventId = @EventId Which is faster ? code 1 or code 2..Assume there is an index on Ev...more >>

HI, I have a table with two columns (Code, Desc) , the first is primary key, the second is my index... My question: When i execute a "Select * From table" without Order by, the rows order by index, why? Why not order by Primary key??? ------------------------------------------------- ...more >>

Why when I create an Index in a varchar column, and execute "Select * from table" The return is ordered by the varchar column and not by the Primary Key column (numeric column) Note: My table has 2 columns Tab1 - Numeric - PK Tab2 - Varchar(50) - NOT NULL Thanks ...more >>

Hi, i have a field wich indicates a state of the record, values are ' ','A','B','C' for example now if i want to look for 'A' i also need the results of ' ' cause they apply to all states so a where clause for 'A' or ' ' now i'm thinking about mapping these values to bits so i can perform the ...more >>

hi! I have a doubt when the performance of some commands distinct and group by, which is faster some times group by is faster than distinct, somebody knows of something? tanks joao ...more >>

SQL2000-SP3 Server running on Win2K Server. I can debug any stored procedure using Query Analyzer from a Win2000 workstation (which has SQL2000-SP3 installed). However, I cannot do the same thing from ANY workstation running Windows XP, regardless of whether they have SQL2000-SP3 insta...more >>

I can't seem to use the shell command on form_load() Shell ("REGEDIT.exe /S express.reg") or a = Shell("REGEDIT.exe /S mailoutlook.reg", vbNormalFocus) .. I am trying to change the mail program registry on the program when the vb code is excuted. However, it only works when button i...more >>

Hi, I have a field (Decimal datatype), that I need to change into a text field without the decimal point. When I use the replace function ('.','') it removes the '.' but replaces it with a space. How do I remove the decimal point in a decimal field? 987.00 --->> 98700 All help is gr...more >>

Does anyone know how to easily script all constraints for creation on a database. Thanks.... ...more >>

I want to query records based on a certain string in the description field. How do I do that? Thanks ...more >>

Hi there, We are having a debate here with some collegues about the advantages and disavantages of datatype Varchar over Char in table columns (and viceversa). I am listening to both sides of this debate, and see both of them have good arguments (for example, some folks here say using Varcha...more >>

We have a server product which talks to SQL Server. One of the tables we maintain is a session table. So a session row is added when login occurs, it is looked up on subsequent requests and at certain points may be updated to reflect recent activity (meaning the session's lifetime is extended)...more >>

I want to pull out records that have a certain word in the description. How would I do that in a select statement? Thanks ...more >>

Hi, I'm working on optimizinf an update with lot of rows and data to change: UPDATE #tempProductivity SET equip = (SELECT SUM(ISNULL(quantite,0)) FROM equipement_liaison equip1 WHERE equip1.ent_id = tt.ent_id), equip_cnc = (SELECT SUM(ISNULL(quantite,0)) FROM equipement_liaison equi...more >>

Hi, I would like to know, how can I create a view from two or more tables in two diffeent databses on a single SQL server? Thanks,...more >>

(Repost, Sorry, no answer on the first post) Hello All, I have created a custom task in SQL Server 2K (MSDE) and I am executing the task using VBA and the "Microsoft DTS Package Library" in an Access2K project. The scenario runs fine on the workstation where SQL MSDE is installed. I the...more >>

Say table x has column a, type float, no default. Later, a new column was added with a default: ALTER TABLE x ADD b float DEFAULT 0 WITH VALUES Now, I want to change both field a and b to decimal. For b, using: ALTER TABLE x ALTER COLUMN b decimal(14,2) This produces an error saying that...more >>

Hi all, I have a table and there is ID column (Primary key and it is Auto number, increment by one) Is this possible that SQL Server skip a number? Please look at my insert statement, what possible wrong with that statement, why it skipped a number? I don't get it. If my insert statem...more >>

I have a VIEW as follows: CREATE VIEW dbo.System_Data_Dictionary_Tables AS SELECT INFORMATION_SCHEMA.TABLES.TABLE_NAME AS Table_Name, (SELECT REPLACE(CAST (sysproperties.value AS NVARCHAR(255)), ',', ';') FROM sysproperties WHERE ((sysobjects.id = sysproperties.id) AND (sysp...more >>

I'm aware that a UNIQUE Contraint is enforced by means of the creation of an index, but I've heard that UNQIUE Constraints have less performance overhead than a UNIQUE Index, and vice versa. Does anyone know the difference between the two (besides the obvious fact that one is a Constraint...more >>