hey guys, i have this Applicaation that is hosted on one server. This makes a call to a DTS which is on teh DB server on another Domain which is behind a firewall. When the app calls it the call never excutes the DTS. Thought could be a problem with the DTS but the DTS has error loggin but t...more >>

What are the major differences among data type VARCHAR, VARCHAR2, and CHAR? please advise. thanks!! ...more >>

how to avoid inserting duplicate key to the table? For example, the EMPLOYEE TABLE has field EMPID (primary key) and NAME If I execute the following sql statement one by one, I will get error "violation of primary key contraint. cannot insert duplicate key in table employee." INSERT INTO ...more >>

if there is any SP to check how many users are connected to my sql server? This is good in case I need to know who is connected or not, so I can bounce the server or do some maintenance without interfering with users's connections. Thanks ...more >>

What the heck is with these Ns that SQL script generator sticks in here everywhere. It is enough to make me quit my SQL Farm Managerial position. Why do I have to deal with Ns everywhere?? EXECUTE sp_rename N'dbo.Tmp_Table1, N'Table1', 'OBJECT' ...more >>

DECLARE @inserted TABLE ( RowNumber INT IDENTITY(1, 1) NOT NULL, BadgeNo CHAR(6) NOT NULL, LastName VARCHAR(35) NOT NULL, FirstName VARCHAR(25) NOT NULL, Street VARCHAR(125) NOT NULL, City VARCHAR(40) NOT NULL, State CHAR(2) NOT NULL, ZipCode CHAR(9) NOT NULL, HomePhone...more >>

Can someone send me a query example on how to force join hints ? Id like to try different options such as nested, merge,hash Thanks ...more >>

Gang, I'm looking for a SQL Server Users group in San Diego. -- Patrick....more >>

Hello: I would like to add filters to some Sql Server 2000 queries and was wondering if someone might be able to recommend a proven design pattern. To be more specific, like most applications, I have several queries that reduce the number of returned records through the use of filtering. ...more >>

SELECT [itemVal] FROM #TempTB WHERE [ID] > (the fourth Record in the #TempTB) Thanks Tdar ...more >>

Hello: If I have a table named venki.TableA and if I log in as Venki, is the following syntax valid: SELECT * FROM TableA I tried it and it complained about an invalid object, TableA. However, the following works no matter who has been logged in" SELECT * FROM Venki.TableA I thou...more >>

Hello, Does anyone have an example of an SQL Server stored procedure that updates a record, where one of its field is of type "text"? My procedure is /* ** Update the client note and production cycle in a Sites record. Set the ** When_submitted field to the current date/time. */ CRE...more >>

Hello, Will SQL Server alert you to the fact that you are trying to put more characters into a nvarchar column than is allowed or will it truncate the data? Thanks in advance. Any help would be greatly appreciated!...more >>

is there a keyword in sql that will allow me to run a delete statement that delete's 1000 rows (example) but breaks the processing into deleting 100 rows 10 times? TIA, KevinE. ...more >>

Can I write a custom aggregate function? something like bult in 'SUM' function. The reason is that I want to culculate a sum of integer column as bitmaps when I have a 'group by' clause. Another question. Which way is better to write SELECT statement with multiple tables? Use 'inner join' f...more >>

select Addresses.ID, Addresses.Address1, Addresses.Address2 , Addresses.City, Addresses.State, Addresses.ZipCode, (select top 1 Phone,Email from Attributes where attAddrID=Addresses.ID), Addresses.AreaName how do I select multiple filelds in the sub query. Analyser will only let me selec...more >>

Hi, I realise this is a tall order for one group, hence the cross-posting. I have data in a web server which I need to process and package as a csv and then upload to a local machine so that a mail merge can be printed off with the data that has been created. My host appears to have dis...more >>

Hello I have a table called log and the schema is below Create table [log] ([AutoID] [int] IDENTITY (1, 1) NOT NULL , [Timestamp] [smalldatetime] , [PageSource] [varchar] (100), [Domain] [varchar] (100), [Faxedby] [varchar] (5), [Agent] [varchar] (10) , [FaxPhone] [varchar] (14)) ...more >>

how to find out when was last time the sql server was down? ...more >>

I have a procedure in a db on Server A that I want to have execute a RESTORE statement to restore a database on Server B. Is this possible programmatically? Server B is a Linked Server on Server A, but how can I execute the RESTORE? Thanks, E2TheC...more >>

sql2k I could have sworn there was a way to see how many records will be returned from a query in Query Analyzer. Either under "Query" or "Tools"? Ive tried and cant find it. Is there such a thing? TIA, ChrisR ...more >>

Hi, when I tried sp_who2, I see connections that are old, please take a look at this and suggest what I need to do - (I have connections showing up since 6/27), how can I clean them up and how did it stay so long? SPID Status Login ...more >>

given the following simplified tables: create table apples ( appleid int not null, column1 varchar(50) null, column2 varchar(50) null, orangeid int null) create table oranges ( orangeid int not null, column1 varchar(50) null, column2 varchar(50) null) ...more >>

Hello, I have a vb programm that creates a fax and send it to the fax server, here is the important part: JobID = objFaxDocument.ConnectedSubmit(objFaxServer) MsgBox "The Job ID is :" & JobID(0) So the ID is stored in an array. I transfered this code to the SQL server: .... EXECUT...more >>

we have need to use mscomm from within sql server to do a simple output. using sp_OA everything works fine with the exception of setting the Output property. we are getting the error 0x800A017C Invalid property value I assume this is because the proprty expects a Variant and we are sending a st...more >>

i'm using sql2k i have a big table with client names in it. first and last names are seperate fields. users would like to search on either first or last name, or both at the same time. what's the best way to create indexes for the best search performance? one on first name, one on last name, ...more >>

I need to implement some complicated hierarchy manipulation features. To make this happen I can no longer maintain expanded structures relationally - doing it purely in SQL would be a nightmare. I'm going to move the logic that maintains them into OO; this will force me to do row updates individ...more >>

Hi. I'm working on a trigger for logging changes made to a particular table. For ugly legacy reasons, the table has 150+ fields, and currently the trigger is a long string of 'If UPDATE(Fieldname)' statements. Is there a better way? I am considering trying to make a select statement that finds...more >>

Someone is running a big batch in MS Access connected to some SQL Server table(s) -- What Access calls "linked tables". I am monitoring a batch running on my local box with SQL Profiler. I can see this other user's process spewing gobbles of calls like this: exec sp_execute 8, 7485 sp_...more >>

can someone point me to an article or give me a brief explanation of how sql server 2000 assigns a value to @@spid? does each stored procedure get its own spid? what if a stored procedure calls another stored procedure? do each of these get the same spid? thanks, jt ...more >>

they reckoned, I am executing these commands in slqanalyzer: set @ruta ='\\aBsrvNET\reportes_TC\' + @mess + '\' + @mes + dbo.f_it_fill_campo(convert(varchar(2),day(@pp_fecha)),1,'0') + convert(varchar(4),year(@pp_fecha)) + '_LISTADOS\OTROS\' -- BUSCA EL ARCHIVO PARA SABER -- SI YA SE...more >>

I have a POITEM Table which has just three columns InvetoryID, DateofOrder, VendorID I have under the same InventoryID ,multiple DateofOrders and VendorrID;s. SO for eg here is a couple of lines of the data, InvetoryID, DateofOrder, VendorID ACCKAPCONS01 05/05/2005 ALTPRO0...more >>

I create a temp table Create Table #temp... and do stuff - without dropping #temp. Then I tried using the following in Query Analyzer if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[#temp]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[#temp] ...more >>

I just started to work with sql server 2000. I want to write a query against "datetime" columns in the sql server 2000 database. I am to be able to do the following with the sql server 2000 "datetime" columns: 1. Be able to access the date portion only. 2. Be able to access the time po...more >>

I have a field in a table which I need to set a default value for. The default value needs to be a string concatenation of two fields from a DIFFERENT table. Anyone know how to do this? Thanks! SQL Server 2000. ...more >>

can we connect named instance through IP like in the network utility I just defined the IP of the server where the named instance installed and define the port.... Client Netowork Utility > Alias > Alias: MyServer Server: 209.45.23.55 Port : 1434 Protocol: TCP/IP any thing else I n...more >>

Hi all, Just wondering.. is there currently a function in SQL to be able to return a specfic row number from a record set. For example, from my Select statement, I only want the 5th record. Just wondering if there is any function already available that means that I dont have to iterate ...more >>

I have a problem and I am trying to decide the best solution. I have a production database that is running very slow because of the = volume of data. We don't need all this data so we have a purge process. = However, we need to keep the purged data so we can run historical = reports. What i...more >>

Can someone please tell me why this doesn't work! I'm trying to migrate some data from an old database into a new database but it keeps returning the error: Server: Msg 208, Level 16, State 1, Line 1 Invalid object name 'dbo.cpncms.tblPageContent'. Server: Msg 208, Level 16, State 1, Line ...more >>

I am trying to connect named instance of remote machine into my local machine but I have failed to do so..."sql server doesn't exist or access denied..." I am successfully registered the default instance of remote machine in my client machine... Can any one guide me.... what the problem wou...more >>

Hai all, We have been using SP3 and now we are updated to SP4, due to which we get some problems in the queries that was working fine with SP3. (FYI: we are working on SQL 2000 and application developed using ASP) Can i know any site which clearly states the difference? Thanks, V.Boome...more >>

I wanted to update record only the field is empty (null), or nothing in it. If there is anything in the filed then I wnat to leave what it is. I have following SQL it seems does not update at all, if I take out the case statement then it updates all the time. "UPDATE MYTABLE SET MYVALUE ...more >>

Hi All, Please consider the following details [Env.: sql server 2000]: User "A" is having "db_owner" database role permission; it executes a procedure (owned by dbo), this procedure create a view "vw1" using dynamic query (its owner is user "A"), but when I search this "vw1" view in the sam...more >>

Hi Wonder if there a general way to build a database with support for multilanguage. Say I have a Product table. CREATE TABLE ( Id Title Info Price Quantity ... ) Here I want Title and Info to be stored in x languages. Should I just add columns like: Title_D...more >>

I have a table with a few columns, and one of the non-PK columns is indexed and defined as nullable. Most of my selects happen on this column, but I notice that as the no. of rows in this table increase, the time for selects keeps going up. This happens while accessing this table using jdbc fr...more >>

I have a table that doesn't perform. The table gets 200-300 new records a day. The table gets cleaned up and trimmed down once a month. Now I can't even do select count(*) from the enterprise manager for this table, which results in a timeout error. If I copy the whole table to the development...more >>

Running the following code (on SQL Server 2000 SP4, build 8.00.2040) results in an "Internal SQL Server error" message, at the last UPDATE statement: CREATE DATABASE Bug GO USE Bug CREATE TABLE dbo.Conturi ( Cont varchar (20) , ) GO CREATE TABLE dbo.Perioade ( Cont varchar (20) , ...more >>

Hi all, I am looking for the development of a reporting tool (something like Crystal Reports). Can someone provide links and material which will help me in developing this tool. I wish to use SQL Standards for the queries that will be generated intermediately , so no cube , rollup etc. ...more >>

As it is possible to connect two different databases within same server from a single query, I want to whether connecting two different Sybase servers from within a query is also possible or not. If yes, Can anyone suggest me how to accomplish it. ...more >>

Can someone explain me the logic behind the following query which makes use of SELF JOIN especially the 'ON' clause (the resultset retrieves 11 rows)? --------------------------------------------- USE pubs SELECT au1.au_fname,au1.au_lname,au2.au_fname,au2.au_lname FROM Authors AS au1 INNER ...more >>

I have a problem in adding a field to the table which has 4 million records. When I do the above process, it taked around 12 hrs and the transaction log is getting full. So I am not able to add the field in the table. Is there any way to add it in one min for a table which has 4 million rec...more >>

Hi Friends, I have the following query, i am using the column alias by this way, -*select work_Code as "Work Code",work_nature as "Work Nature" fro sb_cm_work_nature*- it works fine and i get this output. Work Code Work Nature 1 External ...more >>

Hello, I'd like to do something like these : Select MyColumn AS "MyColumnName " (1) Select MyColumn AS "MyColumnNameIsEmpty" (2) FROM .... WHERE .... If my query returns a record then I'd like to use (1) else (2) Is it possible to do this ? And how ? Thx for your help. Jeff...more >>

Hi to all, excuse my english ( i'm an italian student... ) I have the necessity of make a stored procedure that convert one parameter passed from base64 to binary before store in a field image. Is possible? How? I have this necessity because a lot of page made in ASP get the binary and p...more >>

Hello folks, I'm attempting to set-up a DSN-less connection Access template for clients who may not be particularly adept at handling appends and update queries. So, I was toying with the idea of making a client-side table that they could mess with, add records, change other records, et...more >>

Hi, I have SQL2000-SP3. I defined a proxy account to execute the xp_cmdshell instead of the simple sql account which I don't want to define as sysadmin. I don't succeed to execute the command as I get an unprivilaged error. If I define the simple sql account as sysadmin, everything works... ...more >>

I have a strange situation. I have a stored procedure that is hanging upon execution, but only some machines and not others. The db is an Access 2000 MDB using ODBC linked tables and a SQL 7 back end. The sp is executed as a pass-through. The sp is fairly simple: UPDATE CUSTOMER SET Las...more >>

Hello, I have two tables, TABLE 1 ---------- AccRef Debit Credit ------------------------- LL001 100 200 LL002 150 300 LL003 300 250 TABLE 2 ---------- AccRef Dbt Crdt ------------------------- LL001 300 400 LL002 950 ...more >>

I need to create a DTS package , which will export data from excel into temp table in MS SQL and then use stored proc to manipulate this data from temp table and some permanent tables. Any help will be appreciated. Regards, Ilin -- Message posted via SQLMonster.com http://www.sqlmonst...more >>

Is it possible to reserve a number of identity values before inserting into a table in a multiuser environment ? ...more >>

Hello, SELECT B.CODE, C.CURCODE, SUM(A.AMOUNT) AS TL_TOPLAM, SUM(A.TRNET) AS DOVIZ_TOPLAM, SUM(A.TRNET * (SELECT RATES1 FROM L_DAILYEXCHANGES WHERE LREF = 1) ) AS FARK FROM LG_001_01_CLFLINE A LEFT JOIN LG_001_CLCARD B ON A.CLIENTREF = B.LOGICALREF ...more >>

I think a simple (but fictitious) example is the best way to express what I am trying to do. I have 4 tables. Family: ID Description Person: ID FamilyID Name Person_Car: ID PersonID CarID Car: ID Make Model Year This setup allows me to have many common types of cars...more >>

Hi, I have one big table with 5 million of data. If applocation works on that table, it's very slow. So, I should break one huge table into smaller tables, for example one for each year. Lets say, I have tables: table2003 table2004 table2005 Then, should I create partitioned in...more >>

Hi. I use SQL Server 2000 SP3a on Windows 2003SP1. I'm creating a stored procedure which should do the following: a. If no database name is supplied to the sp then it should loop over all user databases on that server and do what it does when a database name is specified. b. If a data...more >>

Hi I need to define a SP which returns the frequency of occurrences of Events in a certain range of dates. Parameters: range of dates (DateFrom ,DateTo) Input: An events table which its relevant fields are: EventCode, EventDate Output: For each eventcode return the average Frequency ...more >>

I have the following table. I want to copy(merge) all ID's being 1 and 2 into ID 3 producing the result set shown. A requirement is to add the QTY columns for C1 being the same in both ID1 and ID2, otherwise just insert a row. What would be a good approach to do this? Would one update stat...more >>

Hi all I have a application that uses MSDE as database. you know when I give my application to someone he/she can see design of my tables. is there any way ,like encription of stored procedure,to prevent user from seeing deign of tables?? thx ...more >>

Here is a code excerpt from a large proc I have inherited. It runs from within an application, but when I take it outside and try to run it in Query Analyzer I encounter problems... (Note that these are only code sections and I have included all relevant lines) DECLARE @Entity varchar(12) D...more >>

in the Enterprise Manager, if you have a remote SQL Server registered, you could right-click the database and choose 'New Database', in the 'Data Files' tab and press the 'Loacation' button, you could get the remote server's directory. I wonder if I could get it by API's or other. (I know ...more >>

Hi, I altered a table by moving it from one database to another. In the = process it lost it's all dependencies. When I do a sp_depends on that table, it says "Object does not reference = any object, and no objects reference it." I know that there are dependencies for this table. Is th...more >>

how i can import data into sql table from a delimited text file? thanks ...more >>