Hi, guys! I want to build a scheduled job to kill any connections from server 'WS1187' built by some VB applications. The code reads something like this: declare @id int begin set @id = (select spid from sysprocesses where hostname='WS1187' and program_name='Visual Basic') kill @id end ...more >>

Folks ... I have a pipe-delimited ASCII text file with a lot of different non-printing characters. Rather than try and figure out all the non-printing characters that exist in this 17+ million record database, I was hoping someone might have already written a script they'd be willing to share th...more >>

Hi All, I am trying to change column name on an existing table. I am using SQL Server 7. As the table is quite big, it is taking quite long time to do it. By the way I could change the column name only through the Enterprize Manager. Is it possible to change the column name using SQL scrip...more >>

Am using SQL Server 2000, and am writing some audit triggers. How do I get the the name of the user, eg. user_id or current_user. Just spent an hour looking through the SAMS book for this, and can't find any reference. ...more >>

I have a text field that contains abstract information formated in HTML, I'd like strip the HTML and insert the data in another Text field within a DTS package. Is this possible? any suggestions would be appreciated Matt...more >>

Hi, I'm a newbie to sql server and this may be a really dumb question for some you. I'm trying to find some examples of sql server triggers that will set columns (e.g. the created and modified date columns) if the row is being inserted and set a column (e.g. just the modified date column) ...more >>

Does anyone have any basic, simple scripts of sp's that I can give my computer operators to use to monitor for serious conditions on our sql servers? We are new in the ms-sql arena, a small shop and we cant really purchase any tools to monitor these servers, but we need some basic checks that we...more >>

Dear All, I got a wierd problem which I haven't been able to explain. I am working on MS SQL 2000. I don't know for what reason, the relationship between Parent/Child table is getting deleted. When I open up the ER diagram in MSSQL Enterprise Manager, I see the relationship line come up for...more >>

What's the deal with SQLServer 2005 Beta? I've just got a magazine with a free cover disk with VB 2005 beta, and also includes SQLServer 2005. I've installed it, but I can't get it to do anything. It seems to have installed ok, there are the data files for allthe usual system and example da...more >>

I have an asp drive web page that writes a row to a table on sql server 2000. The web site is set to use windows authenication and the sql server is set to use windows authentication. This process works fine on windows xp sp 1 machines but on win2k sp4 machines logged in as the same user i get...more >>

i have to update two tables from ASP pages with same data but i want that both of them should be updated at one time. If either of them is not updated then my transaction should roll back.I want this thing to be in a stored procedure. so that i have to write an execute statement only on the ASP ...more >>

Hi, guys! Some of my applications are sharing same SQL login/password to connect to a database called "MyDB" on server "MyServer" . The password is encrypted and stored in registry or some configuration file the applications use. The applications use certain arithmetic to decrypt the password...more >>

Hi, I would like to use keywords inside a record to search for - like a searchengine. How can I design the table? Right now I would store data in Memo-Column in a format like ";apple;banana;lemon;". I would use a INSTR(ring)-function - but I wonder if the performance is still OK then - even ...more >>

With my understanding of indexed views and according to books I read "indexed views" are supposed to perform much better than "temp tables" (temp table having primary key and indexed view with clustered index on the same keys). But when I tried in my system I am getting opposite results. With ...more >>

When you restore a backup from a point in time, how do you then know which transaction ID to start with when you want to roll forward from that point in time to another point in time?...more >>

Is there a way to audit 'select' on a given table;...more >>

A test database that we used in one of our implementation pilots was abandoned around 4 months back. The database when abandoned had a log file size of less than 500MB. The log file has been steadily bloating (just came to my attention) and has reached 8.5GB. The database has not been used since...more >>

Hey, This is what I would like to do: =========== Declare @chvBOLNumber Set @chvBOLNumber='0001234' Select * from BOL where BOLNumber=@chvBOLNumber I want to return the row/rows when BOLNumber=1234 ============ The problem is the leading zeros. @chvBOLNumber can be 01234 or 001234 or ....more >>

Hi i have a problem i have the following Trans SQL statement in a function SET @desc= (Select description From table Where id = @id) Now i want to make table dynamic. Something like this SET @desc= (Select description From @table Where id = @id) Yeah yeah i know i can't do this: SET ...more >>

Is ther any new virus on sql server 2000 i have the latests ver with all patches and i get the same err at 6 pc's in mt department "SQLMANGR.EXE app error the inst... at '' ref memory at .....could not ... " i did a restart i reinstall sql in one of the pc's and i stil get it even if i run ...more >>

Hi, Has anyone ever had trouble using the query analyzer tool through a vpn client? I'm able to connect outside of work to a sqlserver db on my company lan with enterprise mgr, but the query analyzer times out every time I try to connect from outside of work. Both utilities work fine at w...more >>

Hello, Can anyone tell me how to monitor logins/logouts to Analysis Services databases? I use Profiler to do so in SQL, but cannot find a way to do it in AS. Thanks, Tim *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for...more >>

David: It is not a join as keys are not present in both the tables; between will just restrict the values for test_val. However, the following solution has been proposed by Joe Celko, which works. Thanks to everybody who contibuted. David and Joe, thanks so much. UPDATE T1 SET factor ...more >>

Hi, I am intetrested to know if there is a way to hide the information in a specific column in my table. SQLServer 200. Something like a password protection were you only see *****. I have a DBA but want to hide salary information from him that is stored in the database. Any suggestions Th...more >>

One of our developers is trying to register a database that is not local to his computer with his Windows domain account versus sql server. When registering the server he gets an error message that SQL Server does not exist and he is unable to register the database although his windows account ...more >>

Hi, everyone! I was just wondering if any of you knows of a linux/unix client (preferably on the command line) that would connect to ms sql server? Any hints would be appreciated! Roumen....more >>

It seems like there is no built in procedure for making a password hash in SQL2000. Am I wrong. Do I have to make it from scratch myself or is there samples out there? /Jens Ulrik ...more >>

Pls help if you can. I have two dates, date1 and date2. Basically I want to set date2 = date1 + 1 day for a range of dates that I can select out. Is there any way to do this?...more >>

Hello! I need to transpose some columns into rows and rows into columns. I know, tha i can do it by cursor, but i don't know how make it... I read a lot about it, but still don't understand... Can someone help me? *** Sent via Developersdex http://www.developersdex.com *** Don't just p...more >>

After creating database in SQL Server , it generates .mdf , .ndf datafiles . Generally if we get those datafiles then we can create the database with tables/data on another machine by using those datafiles. I want to know, if we can we protect this? I don't want another user to take those fi...more >>

Hi all. I have a dts package called package1 that I run once a week that does some imports/queries against about the same amount of data each run. I am wondering (hoping!) if I can run a query that will show its performance over the past 6 months in terms of how long the execution for each run t...more >>

Hi What is the sql commnad to view active user on sql sever databases Thanks...more >>

How-doody all. Is there an easy way to identify tables that are not properly indexed based on how often sessions are performing full table scans on it?...more >>

Is there anyone can tell me if the following statement is true? DBA are not experts on SQL. So they are not programmers and can't always answer programming questions. The example of programming questions that the DBA refers to is "What is the function in SQL that works like DECODE i...more >>

What is the function in SQL that works like DECODE in Oracle?" Thanks, N ...more >>

I keep seeing this return from running a DBCC OpenTran: Transaction information for database 'Live_App'. Oldest active transaction: SPID (server process ID) : 92 UID (user ID) : 1 Name : DTCXact LSN : (12837:1924:1) Start time : Oct 4 2004 8:5...more >>

Two tables: T1 (c1 int, TestVal numeric(18,2), ResultFactor numeric(18,2))--c1 is the primary key. T2 (x1 int, FromVal numeric(18,2), ToVal numeric(18,2), Factor numeric(18,2))--x1 is the primary key. T2 contains non-overlapping values. So for eg., a few rows in T2 may look like. 1, 51, ...more >>

Hi, I have a form which is submitting into a sql server db. i have one of the fields set to ntext which says length of 16. when i insert info, it only sumbits about a paragraph and when i pull this back using analyser it amounts to a byte count of 256 bytes. i thought you could submit page...more >>

I have a combo box where users select the customer name and can either go to the customer's info or open a list of the customer's orders. The RowSource for the combo box was a simple pass-through query: SELECT DISTINCT [Customer ID], [Company Name], [contact name],City, Region FROM Customers O...more >>

I'd like to remove any cruft that's built up in the system over the last couple of years. Obviously there's old information no longer needed, I'm just not sure what is there. I've been cleaning up backup history in MSDB with sp_delete_backuphistory (leaving the last year's data) - what else i...more >>

I plan to buy a new computer configured with an AMD processor. Are there are any known problems running SQL Server with an AMD processor?...more >>

I have 6 columns, all with dates within them, i.e. Proposed Start Date 1 Proposed Start Date 2 Proposed Start Date 3 Proposed Finish Date 1 Proposed Finish Date 2 Proposed Finish Date 3 What I need to do is narrow this down into two fields: Start Date Finish Date So I need to find th...more >>

i want to audit transactions done to table TOrig. I created table TAudit same as TOrig in addition to ActionID (1 for insert, 2 for update, 3 for delete), System Date and System User. I created triggers on TOrigto insert into TAudit in case of insert, update & delete. TOrig contains text colu...more >>

We have a requirement to secure the data in a new database so that no one can look at all of the data, including the SA / dbo. Should we give the VP the sa password and tell her how to change it so even the DBA can't access the data? Can we somehow stop SA/dbo from looking at say , a salary...more >>

I need to develop an internal messaging sub-system that is similar to a web mail application but without SMTP support (e.g message routes are confined to the webapp domain). The requirements are rather simple: Each user (e.g mailbox) can view incoming messages and his outgoing messages. Message ...more >>

Hi, I have table where i have the following fields in datasheetview: id | date | image | question1 | question2 | question..N I would like to have it in this way: id | date | image | questionnr | answer 1 01-01-2004 test.tif 1 1000 (this is the value of ...more >>

Hi I am kind of new to sql, so here goes my question I have this sql update query update [CACI-table] set premeses=[xaa].[Sub Building] where (premeses is null) Waht is wrong with this. I have tow table called xaa and CACI-table and all I want to do is take one column from one t...more >>

/*Given*/ CREATE TABLE [_T1sub] ( [PK] [int] IDENTITY (1, 1) NOT NULL , [FK] [int] NULL , [St] [char] (2) NULL , [Wt] [int] NULL , CONSTRAINT [PK__T1sub] PRIMARY KEY CLUSTERED ( [PK] ) ON [PRIMARY] ) ON [PRIMARY] GO INSERT INTO _T1sub (FK,St,Wt) VALUES (1,'id',10) I...more >>

Hi All, We're running SQL Server 2000, SP3. I have a stored procedure that consists of a single Select statement. It selects a bunch of columns one of which is a column of data type TEXT. SP takes 30 sec to run which causes timeouts on the Front End. When I comment out the Text column fro...more >>

My table is laid out as such: ID (int) What (varchar 20) TimeStamp (smalldatetime) ------- ------------- --------------- 73 Start <T1> 73 Misc <T2> 73 End <T3> 81 ...more >>

Is there a way to convert an image pointer to a page ID that could be used in DBCC page i.e. select TEXTPTR(document)FROM testdocs where id = 1 resturns 0xFEFF3601000000000800000003000000 select convert(int,TEXTPTR(document)) FROM testdocs where id =1 returns 50331648 dbcc page (9...more >>

I have tried many variations (after reviewing other posts) and can not resolve the following issue: RUNNING SQL MAINTENANCE ---------------------------- SET ARITHABORT ON SET CONCAT_NULL_YIELDS_NULL ON SET QUOTED_IDENTIFIER ON SET ANSI_NULLS ON SET ANSI_PADDING ON SET ANSI_WARNINGS ON SE...more >>

I've written a SP which does some complex calculations and in the end dumps data into 2 tables (master & detail) When I run this sp for smaller no of IDS (employees i.e for 13000 in Master and 60000 records in detail table) it takes around 3-4 hrs and if I run for all employees in the database (...more >>

Interested in creating a manual standby database.Will like to implement this with Sql Server standard Edition available Any ideas or recommendations Thanks TY...more >>

If installing SQL Server 2000 ( Standard Edition or EE) on any of the Windows 2000 Server with multiple processor, do we have to to do any additions configuration to utilize SMP ?...more >>

Hi is there a way to know if object (view, function, etc) are invalid ? let say a have a table t1 (field col1, col2) and a view v1 (field t1.col1, t1.col2) if I drop t1.col2, the view v1 is not working anymore. I want to know that information. In Oracle (8.1.7), i can query the all_object...more >>

I have a table like the following Field1 Field2 Field3 ------ ------- ------ x1 y1 z1 x1 y2 z2 x1 y3 z3 x1 y4 z4 x2 y1 z5 x2 y2 z6 x2 ...more >>

I'm seearching for information regarding database replication performance. We need to compare the performance of replication for SQL Server and Oracle and it is urgent! Anyone who can describe the performance bottlenecks for each database when performing replication, or can point me to a white p...more >>

I have an application that connects and edits a database's tables. My question is, what is the best way to save who did what changes to this database? I need to be able to display this in the application. The best way I have thought of so far is, to create a new table with 'user', 'date', 'tab...more >>

Can someone please recommend a good book for SQL Server 2000 for a programmer(Powerbuilder), not a DBA. ...more >>

I have 24 tables named tblData1 ... tblData24 and I have a scheduled job that runs successfully to delete all data older than 31 days. My problem is that I need to keep at least one record in each table for the aggregate function max() to work in one of my application's functions, as if there ...more >>