I've seen this done, but I forget how. I have two databases on one server and I am dbo on both I want to insert data into a table on one database from a table on the other something like INSERT INTO DB1.PnL (ThisDay,GrossPnL,NetPnL) SELECT @Thisday,B.Gross,B.Net From DB2.TradeList B Wher...more >>

I have two tables, Table A and Table B. Table B references the identities of records in Table A. I'd like to duplicate the data in Table A and Table B, such that the new data in Table B points to the new data in Table A. In pseudo-code ... INSERT TableA SELECT TableA.EverythingExceptIdent...more >>

On some records - I can't say which since there doesn't seem to be a pattern - my insert from an asp page will insert the same record twice. When doing it stright onto sql using enterprise manager this hasn't happenned yet. It happens even when you click submit only once. Has anyone had ...more >>

I am wondering what could suddenly have made the SQL Analyzer extremely slow. Even the simplest query executed from SQL Analyzer takes a long time to run (minutes) such as Select @@version or just Select 1. Previously it run fine. The database is running fine. I can run queries thru TCP/IP and it...more >>

...more >>

Hi. I have application written on VB6 which connects to the SQL server 2000 via ADO.It works fine with one of our SQL 2000 servers. I created the same database on another server (using backup / restore from the file)- and I have a very strange problem. My application creates a connection, op...more >>

Dear All, An application is written in Delphi 5 using ADO to connect to SQL Server 2000. It is installed in 30 machines but in two of them, the program takes about 60 seconds to startup. It is believed that it is waiting for connection. Those two machines are running windows 2000 SP2. Any co...more >>

Hi Kalen Delaney, I am sorry for giving trouble to you. As I am not getting proper answers to my questions in Books Online I came to news groups. Now onwards I post my questions related to Indexes in .Programming news group. Thanks. Ray, If I have a primary key will it impact Insert...more >>

I need help with some sytax. I have a table with a large amount of data and I have created a temporary table to group the data with. My problem is that the report sorts on generla ledger account number and first letter of the customer code. with the temp table this is use to group the dat...more >>

Hi, i am using a local variable in a stored procedure to build an SQL statement that has to be executed with sp_executesql (MSDE 2000 btw). According to the docs. the nvarchar datatype allows upto 4000 characters. In my case, whatever i store in the variable the string will allways be cut t...more >>

Bear with me, I'm not really sure how to describe this one. First a couple little tables... CREATE TABLE Parts ( PartNumber varchar(50), TypeFace varchar(10), TypeRear varchar(10), BrdFtFace int, BrdFtRear int ) GO INSERT INTO Parts VALUES ('SQ12-105-120', '1324', NULL, 105, NULL) I...more >>

I need to know if it's possible to store the wildcards in the field, and run the like against a text that'll come in via the query/storedproc. Example: SELECT * FROM MyTABLE WHERE 'text to match' LIKE MyFieldContainingSQLWildCards If anyone has a suggestion on how to make an idea like th...more >>

The below function gives me an error "455: last statement must be a return statement". Can someone help me place the RETURN where it should be? CREATE FUNCTION dbo.getType (@sNotes varchar, @sPlant int) RETURNS varchar(14) AS BEGIN IF @sPlant = '1' RETURN (CASE...more >>

Which system table stores the last value for an say id_col -IDENTITY = COLUMN- and How can one Change it when all the records from the table = associated with it. Insert a catchy tag line or saying here Tanveer H. Malik Sr. S/W = Engineer WorldCALL PhoneCards Ltd. tanveerm@worldcall.net.pk t...more >>

Where should I go to get a classroom training on Transact & other programming techniques for SQLserver 2K ( and Yukon if possible)? THanks Bill ...more >>

Hi, I have a special program package, which required to it specific odbc DSN name, to setup on my 200 more users' PCs, can be Win98/95/me/2000/xp, etc. It is really a hard job to create DSN for this ODBC connection. So I want to write a script to do this. Can anyone tell me how to create ...more >>

I am trying to develop a set of routines that allows defining used-defined data that will be stored in SQL. For example, one first, defines a new data type, which is stored in the database, second creates an element of the user-defined type, which, again, is stored in the database, and third...more >>

Hi, What are the steps in inputting Unicode data into a SQL server table. I use the insert command and cut and paste some Chinese characters, and inserted into a ntext field. When querying, the other fields showed up fine, but not the ntext field (shows up as ???). So how to you properly ...more >>

Perhaps one of you SQL gurus can assist me with this problem. I have a table that looks like this: FIELD_ID FIELD_A FIELD_B ----------- ----------- ---------- 1 5 1 2 6 1 3 7 ...more >>

With an access database in vb.net. This works in access: SELECT JOBTRTID, JOBTRDATE, JOBTRUSER, JOBTRACTION, JOBTRCUSTNUMBER, JOBTRCUSTSHIPTO, JOBTRCUSTNAME, JOBTRCUSTCOMPANY, JOBTRITEM, JOBTRITEMDESC, JOBTRITEMQTY, JOBTRJOBNUMBER, JOBTRJOBSHIPTO, JOBTRJOBNAME, JOBTRJOBTYPE FROM JOBTRANSACTIO...more >>

Is there an easy way to convert 9/1/2003 to 20030901? Thanks. ...more >>

How do I set up the tables and relationship such that I can reflect the item desc. of all items in my shopping cart? I am having item1, item2,..., itemN in my shopping cart which all join to itemID in the items table. Since I have multiple items in the shopping cart how does sql know whi...more >>

We can have a table with a structure very similar to the following. How can we populate it with data. Sorry if I have bothered many of you. CREATE TABLE jobs ( job_id smallint IDENTITY(1,1) ) -- Tanveer H. Malik Cell: +923334227099 ...more >>

Hello, I'm interested in any feedback you may have on this situation.... I have two tables in my SQL 2000 database (see table info below). I'm storing form data in Table A. I run a validation routine against the records in Table A and write to Table B a list of errors associated with each recor...more >>

I am trying to set up a query for a lookup in a subform that returns a unit price for a specific product. I can't figure out how to get it to look at just the current record. It pulls up a parameter value dialog box because it doesn't understand which record to pull a unit price for.(As far as I can...more >>

Hi, Why this simple query returns wrong result for column min_BAD_date? Similar column with MAX function works fine. Is this bug? SELECT MIN(CASE WHEN [stor_id] = 7131 THEN [ord_date] ELSE NULL END) min_date, MAX(CASE WHEN [stor_id] = 7131 THEN [ord_date] ELSE NULL END) max_date, MIN(ISNU...more >>

I'm generating a very long sql string to pass to ODBC to perform a SQLExecDirect. My sql statement is actually a batch of insert statements or update statements. It looks something like this: "insert into foo (col1, col2) values ('aaaa', 'bbbb') ; insert into foo (col1, col2) values ('b...more >>

I have a strange problem with a view. I'm trying to create one on db1, that will access fields on db2 something like this: SELECT * FROM db2.dbo.tableondb2 It is giving Inavlid object name 'db2.dbo.tableondb2' Is there any fix for that? ...more >>

I'm selecting data from my database for an export. While I know how to do this on VB, I am wondering if I can do it in TSQL. My statement would be like this, if it is possible. Select Case Country When 'USA' Then 'United States' Case State When 'California' Then 'West C...more >>

Not sure if this is the right newsgroup, but I have an Access 2000 front end to a SQL 2000 DB. For the OrderID or the Order table, I have Identity set to "Yes" and the Identity Increment set to 1. In certain circumstances, when an error is generated from the Access form for which the Orde...more >>

How can I select the database name just like it can be done with the server name? SELECT @@SERVERNAME...more >>

Hello Everyone, Can anyone help me convert "2004-1-15" which is passed into the SP as a varchar value to date format so that it can be inserted into a datetime field in the DB. Thanks, Andrew. ...more >>

I'm trying to connect to my local server and db and query/insert into an offsite server that the user apit has only rights to one db 'apit'. 1. All of these simple queries to an offsite server fail when I add a registered server... select * from [206.71.25.126].apit.apit.order_apit select * f...more >>

Hello, Is there any way of determining if a procedure is executing as part of a scheduled job (and if so, which job)? I've looked at sp_who, sp_help_job, and sp_help_jobhistory, but there doesn't seem to be a way to tie back to the SPID. Thx, GW ...more >>

I am attempting to append data from one table (Ed1) to the end of another (Ed2). Both tables have the same schema. I tried a DTS import and it places the data at the top of my table.. this is not helpful in maintaining the PK sequence. I also tried: INSERT into Ed1 SELECT * from Ed2 Looks ...more >>

In a stored procedure, I've populated the temporary table defined below with data. CREATE TABLE #CONTACTS ( company_id int, date_contacted datetime ) So the data in #CONTACTS looks like this: company_id date_contacted 257 2003-07-25 00:00:00.000 155 2...more >>

I'm generating a very long sql string to pass to ODBC to perform a SQLExecDirect. My sql statement is actually a batch of insert statements or update statements. It looks something like this: "insert into foo (col1, col2) values ('aaaa', 'bbbb') ; insert into foo (col1, col2) values ('b...more >>

Is there a way other than using security/permissions to prevent any and all records in a table from being updated or deleted. I have a table, where once a row is inserted I do not want anyone, including the administrator, the ability to edit or delete that row. Thanks ...more >>

How can i add an identity column as the first column in a table. if i use alter table xtable add autoid int identity(1,1) it adds autoid as the last column. What iam looking for is when i do a select * from xtable the first column must be the identify column. appreciate any help. Thanks...more >>

when trying to run the sp_OAMethod with a call to GetFileVersion() on a sql server 7.00.1063 running NT 4 sp6a, I'm getting a message of 0x80020006 ODSOLE Extended Procedure Unknown Name Is there some update that I need to run on NT4 servers to support this call? Thanks, Wayne ...more >>

My tempdb appears to be fragmented and barring a stop and restart of the mssqlserver service, what is the T-SQL command to defrag a database?...more >>

iam using sp_dboption. How can i turn off this kind of message ? Checkpointing database that was changed. DBCC execution completed. If DBCC printed error messages, contact your system administrator. ...more >>

Does anyone know why I can no longer send attachments using XP_SENDMAIL? There is no formatting in the messahe body of email if I comment out "@Attach_Results = 'TRUE'. Besides, we are mandatted to send the email as an attachment. I have used this before and everything does work fine. I am...more >>

I converted my local time to UTC time and saved in database. I don't know how to convert it back to my local time when i read it from db. Is there any function like tolocalString() ... Thanks in advance....more >>

Hello everybody ! My question is very short and I think that solution is either very simple or very complex. Here it is: I would like to write a program which would help me analyze my queries against various configurations of databases. To do that I need to get data that I normally see thro...more >>

I am just wondering does MS SQL have similar functionality like table%ROWTYPE in Oracle or any way to declare a row type variable. Joanthan ...more >>

I am using xp_SendMail in a trigger to email alerts when there are certain updates. Is there anyway to set the value of the FROM address in the same way that you can set the TO address?...more >>

Hello all, I'm trying to create a stored procedure that updates a column based on one of the input parameters. This is the script that I'm trying to use. 1. is it possible (other than having two params for each month for a total of 26+ parameters), and/or 2. could someone please show me where I'm g...more >>

OK, I have looked through 6 different TSQL book, MSDN, and Technet. I know this must be really basic stuff, but I can't figure out a solution to my problem: I have a Job that runs once an hour to append data to table from an Access DB. I works wonderfully, but once in a while, if the Access DB is n...more >>

Is it possible to define a Trigger - Insert - which fires before data is actually inserted in a Table. That is a Before - Insert/Update - Trigger ? -- Tanveer H. Malik Cell: +923334227099 ...more >>

I have a 30 gig database that we added millions and millions of records to. It ended up being 42 gig when done. I then used DBCC DBREINDEX on each table. I then did an UPDATE STATISTICS <table> WITH FULLSCAN, ALL on all tables. But when I run queries and look at the plan, the plans have some...more >>

I need to call this stored procedures in different databases. exec master.dbo.xp_cmdshell 'dtsrun /Smy_server /Umy_login /Pmyp assword /Nmy_dts_pkg /Mdtspassword' Now, I don't want to have to change the database name each time I move this script to a different db. Is that possible? ...more >>

Given the following table: ID Amount RunningTotal ---- --------- ---------------- 1 10 1 5 1 10 2 5 2 20 ...more >>

I would like to take a nightly backup from the Production database (STAR_JET) and restore it to a Developement database (STAR_JET_DEV). I would need to create a job that will restore the nightly backup of the Production to the Development databases. Also I will need to execute a script...more >>

I have a existing view that I want to try to index. Can you index preexisting views? Can you index views with multiple sbu-selects within the view? If so how? Any suggestions would be greatly appreciated....more >>

I have a complex query (or set of queries) that I'm trying to solve... I have a workable solution, but it's kind of ugly, and I'm hoping for some better performance. Here's the problem: table DATA has a bunch of data that has been reported from various locations. I need to, for a particula...more >>

Hi, I am trying to send data from Sql Server 2000 to AS400. I am using the function OPENROWSET. The Sql statement which appears as under: INSERT OPENROWSET('IBMDA400','test';'loginid';'paswd', 'select * from test.test') Select '1','1','1',1,1,10122003,1,1,1 This statement gives an ...more >>

I have a basic sql select statement: select name,address from testtable How do I do replace a NULL value in address with a blank, or space or something? I have tried: select name, replace(address,null,' ') as address from testtable but this does not work. Thanks! *** Sent via Dev...more >>

is there an easy way to list all of the tables in a SQL db to print them...more >>

Hi, I want to insert data for a multiline field (such as Address, Comments). How to write an insert query to insert data in multiline fields? For example, Address should be of the format: Line1, Line2 Thanks, Ajit...more >>

I've trying to run a query on multiple dbs on a server. Its all working fine but I'm tring to use the USE function to dynamically change between the dbs. However I'm getting an error message when tring to pass the db name as a variable. Basically this bit of code won't work: declare @dbname1 char (...more >>

My Application got the below error [Microsoft][ODBC SQL Server Driver][SQL Server]Changed language setting to us_english. (5703) Any idea why this error occurs?. Thanks in advance...more >>

I have an ASP page which interrogates a call-logging database. It allows a user to request a report in Excel format for jobs logged within a user-specified date range. The Excel file contains all info about the jobs, including the free text details typed by the person logging the call. A cu...more >>

Hi people, I have a table which has duplicate values in it. I want to write a Stored Procedure to remove them. Sounds simple but I only want it to remove certain duplicates. Here is an example: Table with two fields in it called ID (nvarchar) & Time_Seen (varchar) with values such as ID ...more >>

Hello, I posted this yesterday however I forgot I did not show the entire results I wanted. I have a database table with the following ID Surname Forename 1 Smith Brian 2 Smith John 3 Jones Brian 4 Smith Brian 5 Doyle Susan 6 Jones Brian I need SQL to remove the duplicates based on ...more >>

i've created an update trigger to log modifications to another table. I've tried the following: declare @ChangedColumns varbinary(8000), @Size int, @i int, @ColName sysname set @ChangedColumns = COLUMNS_UPDATED() set @Size = DATALENGTH(@ChangedColumns) * 8 set @i = 0 declare @Value sql_variant ...more >>