Evening all, I'm having a problem find out from the tutorial and books I have at my disposal how to do the following (probably because its getiting late). I have for example a user table with the following columns UserTable ----------- USERID Display Name Address Notes Table ------...more >>

Hi, I would like to launch a Local Package in the Data transformation Service of SQL Server from a VB .Net application, but I didn't find any documentation about it, I don't have any problem to connect to the DB itself and do queryes but I don't have any idea about the lauch of the DTS. can ...more >>

How Can I retrieve all hostnames/ip adresses, that is connected to my sqlserver through transaction sql ? Jonas ...more >>

Hello world I have just started programming SQL Server 2000 but have hit a problem regards triggers. As a test, I have created the following trigger to audit any changes to data: CREATE TRIGGER Audit ON tblMain FOR INSERT, UPDATE, DELETE AS IF (SELECT COUNT(*) FROM inserted) > 0 and (SELE...more >>

SELECT count(distinct *) FROM REPORT1 ...more >>

Hi everyone, I have the following store procedure, CREATE PROCEDURE Get_Person_By_Page @SelectSQL varchar(2000) AS INSERT INTO TempPerson (FirstName, Initial, LastName) SELECT FirstName,Initial,LastName FROM Person However, I like to replace my SELECT with @SelectSQL which doesn't work....more >>

SQL Server 200 In one of my database table column, there is a field that looks similar to the following example, [OPTICAL79A]\0215896.TIF I want to replace the text, OPTICAL79A with OPTICAL79B, and leave the rest just as it is How do I write this MS...more >>

Hi, I am trying to concoct a query that will join rows on the basis of a whitespace insensitive comparison. For instance if one row has the value 'a<space>b' and another has the value 'a<space><space>b' I want them to be considered equal (but not the same as 'ab') I am happy to do some T-SQ...more >>

Hello All, I have written a stored procedure which takes a parameter of type datetime. If this value is not passed, then I want to use system date time. I wrote the procedure in the following way create procedure myproc @my_date datetime = GETDATE as insert into mytable values(@my_da...more >>

dynamically building a t-sql string inside a stored-procedure , and then executing it ... what technique could be used to populate the (assumed scalar) result into a local variable of the stored procedure? example: declare @target varchar(10) declare @sql varchar(1000) set @sql = '' se...more >>

Hi, We installed SQL 2000 on a new server and restored all backups from the old one. The new server is much faster, but when we select a database in Enterprise Manager with the right mouse key and ask properties it takes 25 seconds before it appears. CPU is 0-2%. The same happens when we ar...more >>

I'm trying to copy a table from one SQL Server to another using linked servers. The table contains some datetime fields. When trying to do this copy over a network, I get the following error: Error converting data type DBTYPE_DBTIMESTAMP to datetime. From what I've found searching the web, t...more >>

Well I try to execute this query strsql= "UPDATE descrizioni SET descrizione = REPLACE (descrizione, '"& testo_find &"', '"& testo_new &"') WHERE id IN (" & stringa & ")" but it return this error: Microsoft OLE DB Provider for ODBC Drivers (0x80040E14) [Microsoft][ODBC SQL Server Driver][...more >>

Hi all. Is there a way I can save the results of a stored procedure to a table? I mean, create the table in the moment I execute the procedure. (Something similar to "Select Into") Thanks. Have a nice day, Frank ...more >>

Hi, I need to restart the actual physical server. I can only get there thu sql. I have admin priveldges. what can i do? ...more >>

I have a sql 2000 sp3a box that is giving me some trouble. when I attempt to modify and save a table through enterprise manager I get an error like this: 'some' table - Unable to modify table. ODBC error: [Microsoft][ODBC SQL Server Driver]Invalid cursor state This seems to be server wid...more >>

I want to move a small client from their Access DB to a SQL server version, but they are on Windows 98 so I will need SQL 7.0 . Microsoft doesn't sell this on their Web site anymore, so other than E-Bay, does anyone know where I can get a copy. Will I need a license for their few clients, or would a...more >>

Hi All, I have a table and I would like to filter the results based on the date field. What I want to filter is any record between 12 months ago and now. I know how to do it in Cognos - (Issue_Date between add-months (now(),-12) and now()). But I am stumped on how to do it in SQL. T...more >>

Ok, these strings contain data I want to INSERT into a record: Dim cNumber As String Dim fName As String Dim lName As String Dim cPhone As String Dim Address1 As String Dim Address2 As String Dim City As String Dim State As Stri...more >>

Hello all. We need to parse a variable-length string and extract 70 characters from it each time, insert those 70 characters into a table, and quit when none is left. This sounds like a simple problem, but haven't been able to think of a good algorithm. Thanks. ...more >>

I can't get my head around this one: I want to end up with a results table with 2 columns and one row e.g. [5,512] doing it this way: select * from ( select min(inv#) from table1 , select max(inv#) from table2 ) how do I do it that way ?? ...more >>

Can anyone tell me if there is anywhere to get a list of "ALL" undocumented stored procedures? Thanks all!!...more >>

Someone told me that this could not be done. I have hundreds of tables that are of the same format and would just like to create a SP and change the query with a simple input parameter specifying the table name, here's a snippet: CREATE PROCEDURE sp_GetData @tablename nvarchar(40) A...more >>

I have two tables that I want to define triggers on. There is probably a better way to do this (besides triggers), but I don't know it as of yet. If FieldA in Table1 is populated with "X", I want FieldB in Table 2 to be populated with "1". If FieldB in Table2 is populated with "1", I ...more >>

There is a little used TSQL command that I once knew that in one TSQL command created a table that was an exact copy of an existing table. This command also inserted the data from the exisiting table into the new table as part of its feature Does anyone know how this TSQL is structured? It is a ...more >>

Hi, I have created a stored procedure. The function is to select records by giving the input. I want the results to be displayed on a datagrid. When the SP is executed in either Access or T-sql the results are displayed correctly. This is the SP. ALTER PROCEDURE dbo.PMComplete (@EnggName ...more >>

CREATE TABLE dbo.t ( a int null, b int null) GO INSERT INTO t VALUES(1,1) INSERT INTO t VALUES(1,1) INSERT INTO t VALUES(2,2) INSERT INTO t VALUES(2,3) INSERT INTO t VALUES(2,3) Hello, Please can someone help me with the following problem. I'm trying to extract input errors in...more >>

Here's the situation: My production environment has two SQL Servers 2000, call them S1 and S2. S1 also hosts IIS and my ASP.NET application. On S1, I have created a linked server for S2. Thus, on S1, I can write: SELECT * FROM S2.myDB.dbo.myTable and everything is happy. My development en...more >>

Re: http://aspfaq.com/show.asp?id=2126 The artcile uses "Provider=SQLOLEDB.1;....." I have been using "Provider=SQLOLEDB;...." Is there any difference between the two? ...more >>

When I run a "SELECT USER" in QA on my database, it returns that I am 'dbo'. I created another login/user in the database and did a sp_changedbowner to this new user but I'm stilled called 'dbo'. My login has access to the db but my username is not a db_owner role. I am an 'Administrator' on th...more >>

I'm just getting to the point in my vb.net program where I use sql strings in the code and I need to know this: dbase name is tribidz tbl name is TBL_Seller columns are: cnumber | fname | lname | baddress | bcity | bstate | bzip | bstateprov | bcountry | userid | password | email what ...more >>

How can I be able to programmatically convert a text (tab delimited) data file into SQL Server? Thank you very much. Joey ...more >>

I have a sql select query that joins two tables together by a location number. Is there a way to join two tables together so that if the first one does not find a match in the second table it still returns with no information in the fields from the second table. Some of the the locations...more >>

Hi all! I'm testing the following on my local machine under the Northwind database. DECLARE @cmd varchar(200) set @cmd = 'bcp "Select FirstName from Northwind..Employees" queryout "c:\test.txt" -C -Usa -P' exec master ..xp_cmdshell @cmd But... I get this error: ...more >>

Hi, I use bulk copy functions to extract data from the database. Everything works fine, except that the output file begins with garbage characters. Here is some code: bcp_init(m_hDbc, NULL, _T("test.xml"), NULL, DB_OUT) bcp_control(m_hDbc, BCPHINTS, _T("select * from aTable for xml raw")...more >>

I have a table with 2 columns: xorder char (10) NOT NULL xship int NOT NULL DEFAULT(0) It has the following in it: ABCDEFGHIJ 14 I want a select statement that the return is one column: ABCDEFGHIJ-0014 How can I do that? Darin *** Sent via Developersdex http://www...more >>

Hi, Can I set the "return result sets" to a cursor when running "sp_executesql "sql_select_statement" ? The "sql_select_statement" is dynamic generated at run time. e.g. Declare @my_cursor CURSOR sp_executesql "sql_select_statement", @my_cursor OUTPUT How can I make this works? Thanks....more >>

Hi, I have a question about CASCADE-ing with a database. Let's say you have two tables (ie. MAIN and SUB), with referential integrity using DRI. When deleting a record from MAIN, all records in SUB related to MAIN will also be deleted. Now here's my question. In what order are the rec...more >>

Hi A newbie in TSQL Programming .. I have got 2 tables with the following structure Table 1 ------- CUCODE (PRIMARY CUNAM CUADDRES TABLE --------- AD_COD AD_ADDRES Table 1 stores the main address while Table 2 stores other addresses (like delivery) of the...more >>

Hello, I have a problem with a stored procedure, it worked OK until few days ago, now executing endlessly. At the same time other stored procedure and all SQL Server objects working OK. How to debug and see why it cannot return resultset? Vladimir ...more >>

Hai , My problem I have problem with table insertion and selection When I try to bulk insert data in a table, a few rows are not being inserted (out of around 2000 insertions, around 15 will fail) When I issue a SELECT statement on the table from client side (VC++, CDatabase and CReco...more >>

Is it possible to CONVERT or CAST a text column to money? I have a table T_Items with the columns ID(integer) and COSTPRICE(text). The SQL server is MS SQL server 2000 running on an MS 2000 STD server. And before i continue i would like to add that i know its silly to store a product price as ...more >>

Hi, Is there a way to add an extra column showing numbers from 1 to (maximum number) for each selected record? I need to mark numbers for each randomly displayed IDs. Cyont ...more >>

I'd like to search through all stored procedures in a database for a certain string. How can I do this? Does Query Analyzer have this capability? Some other utility?...more >>

Hi I see this code everywhere for dropping a table before you create it (again) and it always has this part of the WHERE clause "sysstat & 0xf = 4" or something like it. I can't find sysstat clearly defined anywhere. It's obviously a bit flag field but what are the bits? SQL Server docs say "In...more >>

Hi, Is there any way to convert timestamp(binary) datatype to readabler datetime format in tsql? Thanks...more >>

I have productional SQL Database on computer, which is located in other country. Than I have the copy of this database on my computer. Every night I should update my database with changes made on productional database on the other country. What is the best way to do that? Does sql has some...more >>

Hi does anyone know how to transform the result data automatically into a graph format Thanks for any help Mikey...more >>

Hi there, i want to create a statement that basically says this If Year(DateinDB) >= 2004 The If Month(DateinDB) > 02 The Add Row to result End I End I At the moment im using SELECT * FROM DB WHERE Year(DateinDB)>= 2004 And Month(DateinDB)> 02. Obvioulsy however this doesnt ...more >>

Hi Is there a way to create a process that creates a hierarchy structure using one table ! for example I have 3 columns ! Goup Family, Family and product in one specific table ! I want the select to build the hierarchy ! For example - 1 2 4 And so on ! Please help ! thank' ...more >>

Is there SQL code to find the date of the next friday to a given datetime input ie SELECT NextFridayTo(fldMyDate) as NFriday FROM tbl_Mytable WHERE etc etc many thanks in advance Charlie...more >>

Hi, If I created a trigger that handles all three types of actions (INSERT, UPDATE and DELETE), how, in the trigger code, would you be able to identify what trigger was invoked. For example: ================================= CREATE TRIGGER [TRIGGER_NAME] ON [OWNER].[TABLE_NAME] FOR I...more >>

I am looking for help in writing a stored procedure that will grab the current log size ad file size of a DB and insert the records into another table for keeping track of the history. I have never written a SP so please bare with me. If possible I would like the SP to loop through each...more >>

Hi there, ive been having a few problems with a Where statement and hoped someone here could maybe help me out. Basically i want to do this. If Year(DateinDB) => 2004 The If Month(DateinDB) > 02 The Add to Result End I End I Obviously this isnt in SQL but gives ...more >>

I am facing the following problem: Two servers, each running sql server 2000. db1 on server1 has several "instead of"-triggers (for insert, update, delete) on a view. This view refers to a table on db2 on server2. The insert trigger works fine, but of course only when applying SET XACT_AB...more >>

Hi to all.. I have three table Cards Card_Edition Editio CardID CardID EditionI Name_Card ...more >>

Hello Can someone help me with passing a value from MS Access into an SQL stored procedure The SP I have so far is How do I pass a value from Access to the @nApptID parameter THanks so much CREATE PROCEDURE dbo.procApptOVDetailUpdate (@nApptID int FOR UPDATE A SELECT ovd.ApptReasonC...more >>

I have a DTS package name VIEWS that I would like to append it to an exist TSQL script that I can use for replication. Please help with the TSQL statement to complete this task. Thank You, Dan ...more >>

Hi, I'm having trouble running an INSERT statement with the EXEC function. Here's the situation: I have an INSERT statement I create and store in a VARCHAR variable. Now when I run the string on it's own (using the string as the actual statement), the statement works fine. But when I ...more >>

Hi Is there a sentence I can use in order to clone an existing record (for more than 1 or 2) Daniel...more >>

Is there a way of finding what schedule jobs are actually running at the current time ...more >>

Hello MVPs, Is there any stored procedure/function or a any solution=20 to get the actual character of the Accent character? example: I got =E1 and I want a PS: I got a collation with CS_AS. ...more >>

Looking to see if there is a way in MSSQL 2000 to have the same functionality of Oracle's connect by function?...more >>

If i have a table with the followin InvDate SmallDateTim InvNo Varchar(10 how can i get the highest and lowest invoice number for any given month ?...more >>

Hello, I recently started experimenting with the SQL Server Table type because I have read on the internet that Cursors can be slow do to their use of the tempdb and any contention/io issues that may arise because of that. So to familiarize my self with using the table type I decided to convert a...more >>

Hi, I have a stored procedure which when being run through VB, times out after 30 seconds. It takes more than 30 seconds to run when run on its own. But when I drop and re-create the same procedure, it all works well. Why would this be? This is without updating any table statistics, and it seems...more >>

I use the following statement to get all names of foreign key about @Table_name and @Col_Name select name from sysobjects where xtype='F ' and parent_obj = Object_ID(@table_name) and col_name(parent_obj ,info)=@col_name Sometime, It works fine. However, It get something wrong t...more >>

Dear Friends I want to change the WINDOWS AUTHENTICATION MODE OF MY SERVER to MIX MODE but i want to do the same with OSQL please suggest how i can do the same. Best regards Sharad...more >>

When we want to drop a Index, the only way I know is using the following statement: Drop Index TableName.IndexName Sometime, I want to drop a index, I dont know it's really name(s), I just want to drop all of the indexes reference to Col1, Col2, Col3.... Is there a way to get all na...more >>