How can i separe only the first word of a nvarchar field?? Sample: Big barrel Small cat little dog i want this return : big small little only the first word of the field, i have try with LEFT, but this function only works with N letters, Thanks in advance Alejandro Carnero. ...more >>

Hi there.. I have an SP that takes a very long time to complete: <snip> CREATE PROCEDURE dbo.USP_RETURNDATA_NS_ORDERRETURN @RETURNDATA_ID AS INT AS SET NOCOUNT ON SELECT VW_CUSTOMER_ADDRESS.DISTRIBUTOR_CODE , VW_CUSTOMER_ADDRESS.DISTRIBUTOR_CUSTOMERID , VW_CUSTOMER_ADDRESS.SIG_REQD ,...more >>

Hi, Is is possible to mark an SP to run immediately before the SQL Server Service is stopped (like what we can do for startup) or any event available is DMO? Any help would be greatly appreciated, Leila ...more >>

hello is there any way to put select into function parameter ? lets say function(param1, param2, select id from table) ? i know that is possible using cursor but how to do it without cursor thanks Patrick ...more >>

hi , I am creating an Inventory database. Now the equipments come in different lots. then different equipments combine to form Assembly equipments. I know how many assembly equipments are made. But these Assembly equipments are made frequently. Now i want to design a database which will t...more >>

I need to run a script from one sqlserver db that will retreive data from another sqlserver db and fill a table in the first db. This needs to be in a script not by using enterpirse manager. Can any one point me in the right direction? Thanks...more >>

Hello All, I wanted to get the TOP 2 orders of every employee. I wrote this query expecting it to not to work. but it worked and I dont understand how :( select * from Orders O1 Where O1.OrderID IN (select TOP 2 O2.OrderID from Orders O2 WHERE O2.EmployeeID = O1.EmployeeID ...more >>

Could someone give me the syntax for printing the list of stored procedures in a DB? Thanks a lot!! -- sam...more >>

Please excuse my ignorance. I know this ought to be simple, but I do not find a way to apply explicit locks in BOL. What I'm trying to do is to write a function that takes two parameters: create function GetNextKey (@keyval varchar(20), @inc int) I want to find the one record in a table...more >>

I am dynamically building SQL strings within my code and using EXEC () to excute. My quetions is as follows: Can I assign the ouput of Exec (@sqlstring) to a variable i.e @myvar=exec (@sqlstring) note: @sqlstring return a single number, count of records. ...more >>

Please could someone help? I have an ADO recordset that gets closed, and intermittenly I get the = error error no: 3219 "Operation is not allowed in this context" I even changed the code to look like this: 282 If rsProposal.State =3D adStateOpen Then rsProposal.Close but I stil...more >>

Dear all, Im having several Select statements to Get Counts of records for certain conditions from several large tables. Im using SQL server as the BE and VFP DLL as the middle tier. I have two options to retrive the data. 1. Issue seperate select statements to the SQL server 2. comb...more >>

Hi, I have a SP in a database which needs to be run every night, it basically deleted all records in one table. How can it be setup ? ... Thanks ! Aleks ...more >>

The query below returns three different counts: Outbound, Inbound, and Exception for all the users. I need help to modify the query to return the top 10 percent for each of the count (Outbound, Inbound, Exception). I have learned that the syntax "Top 10 Percent" will make the query return...more >>

Has finally finished! 9 months of hell are *over*! I'm actually not going to have anything to complain about tomorrow! But all the migration work was done in a database called TNS_IBM_prod So, in Enterprise Manager, we have this yellow TNS_IBM_prod icon. Its logical Filenames are TNS_IB...more >>

I'm implementing a simple "keyword" search capability such that if the user enters "mouse trap", all records will be returned that have all those words in one (or more) records of a number of tables. In other words, "mouse" must appear in one of the tables and "trap" must appear in one of the tab...more >>

Does anyone knows wich are the available string options for CommandTerminator? ...more >>

I am using vb.net to process a SP The sp are insert XXX select () statment, there are two parameters , Start Company code and End Company Code, The user input the range From A to Z or From A to B. I found that If the range is A to C , the SP runs fine , if the range is A to Z , I will got Timeo...more >>

Hi, I have a VB program which connects to a database located in a SQL Server. The program deletes an index column from a table and re-creates the same column. The problem is when i run the application for the first time it is performing the task correctly (i.e. dropping the index column and ...more >>

Hi, I create a unique constraint on a column in a table. When I use select columnname from tablename and I look at the execution plan, it uses non cluster index to look for the data. I am confused... I created a unique constraint not unique index... so... create a unique constraint als...more >>

I need to get information from a table in a join but need to join on a record where the join information is in the middle of the record data. Does anyone know how to get the middle of the record data Thanks ...more >>

Hello, I have a search form in an ASP.NET/VB page. The form has the input text box and the button "search". The keywords are passed in the URL to results.aspx. Here is an example: results.aspx?search=asp%20book%20london (%20 means for space) I used the words "asp", "book", "london"....more >>

Greetings ! We need to find if MS SQL has an API for connecting C++ components to it. We don't know if "extended stored procedure (xp)" is the way to go. Why do we need that ? We must implement a synchronization software layer to make sure an ODBMS has the same values as tho...more >>

I have a field called ShiftSequence that consists of YEAR, MONTH, DAY, SHIFT all in one long string. Example: 200407132 is July 13, 2004 Shift 2 Within a SELECT statement, how can I parse this field in a date field (07/13/2004) and a shift field? I also want tomake sure that the date is rec...more >>

When I execute SQL procedure, I get an error: Server: Msg 18456, Level 14, State 1, Procedure c_prenosIzdelki, Line 6 Login failed for user 'brane'. So, I try to capture the error and if it happends, send email. But nothing happends. When error is raised(within first procedure) the execu...more >>

I have multiple database servers, with multiple databases. Some queries require data from 2 servers. For this reason I have linked the servers. This has however caused quite a bit of degradation when running queries. I' ve run a trace, and it seems that when you join across multiple servers, th...more >>

From time to time I make a mistake and need to send a customer a script to modify something in their sqlserver database. Just now I had to send out a change to 2 stored procedures. I've got a menu in my app where users can paste and run the script. The problem is VB won't accept anything with ...more >>

I have two table like below Table1 ProdId GroupId p1 G1 p2 G1 p3 G1 p4 G2 p5 G2 Table2 ProdId p1 p2 p4 p5 I want the list of GroupId s which are fully matched with Table2.i.e in above data p3 is not there in Tabl...more >>

Looked around (SQL 2000) BOL and did not see where it says that a TSQL Having clause is always optimized and uses an index for filtering, eg Select Col1 , Col2 From MyTable Where Col1='This' Having Col2 Like 'That%' Order by Col1 , Col2 Where can I find docs that prove the above query wou...more >>

Hi ! I need input on what is the best way to solve a special Select from my SQL2000 database before I make a temporary solution I need to change later. In my example I have a table with 5 fields called Field1 ~ Field5. In these fields a random value may exist, lets say field3 = "WinXP". How c...more >>

Hi all, I made my database backup the wrong way (accordingly to a friend) and now I desparatly need to restore because of hd crash. I started in Enterprice Manager where I chose All Activities and then Backup Database and from there I continued. The result was a database file with the fi...more >>

Hi I have created a backupdevice. It shows up in Enterprise Manager on the Server under the management\backup node. Is there a function to get the name(s) or determine if a particular umpbackupdevice exists? Does a record exist in the master or msdb database using T-SQL? thanks Chri...more >>

Hello, I read that to download the backups of a MS SQL 2000 from my server I should use DTS. Is this a software? Can someone give me some tips about it? Thanks, Miguel ...more >>

What are views? Can it be used within a stored procedure?...more >>

Hi, Running SQL Server 2000 SP3 I have a table with a int column. In this table there are both NULL values and numeric values such as 0 or any other number. The problem i have is with the len function. It seams to return len() = 1 when i expect len() = 0 when running a query such as t...more >>

Hi, I am creating a stored procedure. I want to dynamically create the name of my umpdevice hence I have the following code: declare @SQLString NVARCHAR (50) declare @createstring NVARCHAR(200) declare @yr char(4) declare @mnth char(3) set @yr = (select fiscalyear from tblmasters) se...more >>

Is there a way to rename a table in TSQL? I have a proc that creates a backup of a table after a report is run, to freeze the info for the report. I need to keep the backup table in my database for 3 months. I created an SP to select * into tbl1 from tablename. next month I want to rename tb...more >>

In my sp, i create several cursor, Now the sP go some errors, and I need to drop the cursor, Any command can let drop all the cursor ?? thx -- .. ...more >>

I hope somebody can help provide references to documents explaining how to shred XML data into an MS-SQL 2000 database. Tools available? Methodologies? Thanks for any comments... -- <%= Clinton Gallagher ...more >>

I am trying to create a function to run in a query that will check the time difference between two datetime values if the difference is over 48 hours - if it's over 48 hours I have to take out the minutes for non-workdays. I have a table called calendar that has all the dates in it and a bit colu...more >>

Hi all, I have a result of a select statement, just like SELECT Value1, Value2, Value3, Value4 FROM MyTables How can I get the Maximum of these 4 Values? I tried with SELECT MAX({Value1, Value2, Value3, Value4}) FROM MyTables but this does not work. I thought there might be an eas...more >>

Hello, I have a slight problem. I have a table where each entry is given an id (uniqueidentifier) and a number. The number itself must be unique and can be altered by the user, this can be solved by setting a UNIQUE constraint. However, an entry can be set as inactive and if that is the ca...more >>

I have a table with the following structure item loc primvendor recordtype 111 1 111 ME FRIAME 2 111 NH FRIAME 2 112 1 112 ME MADINT 2 112 VT FRIAME ...more >>

Hi All, I have a VB6 app to extract & process data from a SQL 2000 database. Very soon the database structure will be changed & as result I need to change the app too. At this stage the app will assign data from one table to a recordset, then process the reocrds, but the new DB will be ...more >>

I know how to encrypt a Store Procedure (by adding the string WITH ENCRYPTION on the SP) but my question is how can i encrypt a table? ...more >>

I'm creating new records in my aspx file, but after its created, I need = the ID of the new record. The ID field is the primary key and increments = automatically. Should this be completed in a proc? Sorry about the cross = group post, but I'm not sure where and who would know this. Thanks! ...more >>

Don't want to open the big can of worms called "surrogate vs. natural keys". Please, no flames :) Question is: Suppose I have master/detail tables with the master tables having 3-to-5 column natural primary keys. Suppose those columns were fairly short, i.e. 10-20 bytes total. Would it make...more >>

I'm having problems using the Group By function in a SQL Server view. I'm trying to total up yearly sales by for each employee. The employees sales are split by six month periods. I want to get their yearly total by adding together each six month period. The problem is that some employees wor...more >>

What is the difference between nvarchar and char? What about the rest of the nvar types? Is there a website that explains and lists the different data types along with relavant examples for each? I looked at a few SQL help screens and was more confused than when I started. Thanks Joe ...more >>

In Enterprise Manager, I know how to set default values for a field. For one of my tables, I have an identity field, and a text field. I'd like to concat some text on to the generated identity and place it in another field. Should I do this with a trigger or can this be done with the formula fo...more >>

I'm trying to squeeze a little more speed out of a stored procedure and have arrived at the point of needing outside help. Following are the table definitions for the two tables I'm using along with the snippet of code that takes the longest time (between 15 and 20 seconds). I also included so...more >>

I have two tables and and I want to count each table and add the two values together. The following works, but I was wondering if anyone knows if there is a better way to write this query then the way I did it. select sum(cn) from (select count(*) as cn from tb1 union select count(*) as cn f...more >>

Hello, we linked the Oracle server as Linked Server using Microsoft OLE DB OLE Provider for Oracle We are using this syntax to make heterogeneous query: INSERT INTO OracleServer..schema.remote_table select * from local_table We found out that INSERT is slow because OLE DB Provider is doing i...more >>

hello i have a database in MS SQL from my friend and i cant modify structure of this database, and there is a little problem : i have one table ARTICLES where are columns : id_article, name and others second table is CONTRACTOR with id_contractor, name, and contractor may have many prices...more >>

Can someone from the Microsoft team explain to me why indexed views do not allow for the union or union all operator? Further, given that indexed views have a unique indexed, why can't I create relationships to indexed views?...more >>

Hi, Can you please give me the steps to backup a database by using the backup database option? I tried backing up the database, but iam having some difficulties in it.Iam unable to open the backup file.Please help. ...more >>

Hello, im trying to extract and merge data from several tables into one table, to indetify which table each row of data has come from I would like to insert a column which references the table name. Below is an example of the code:- I would like to create a column which will show for example ...more >>

I have a User-defined SQL function that takes a date and then returns a string containing the the WHERE portion for all versions of that date. Example: set @ConvertedDate=.dbo.DateWHEREClause(@DOB,'Attribute') The Result if date were (1/1/2004) '01/01/2004' OR Attribute = '01/1/2004' ...more >>

if i select the backup database option,will the data be backed up? And will the stored procedures and triggers get backed up too? ...more >>

Suppose i have a table called x,i alter the structure of the table.Now i have to send the updated table to my client.But if i generate a script it's going to drop the table and create it again and the data that was previously there in the table is going to get lost.What do i do in order that...more >>

Hello (sql server 2000 ,sp3) I'd like to order by three columns (deptid,pri, d_pri) CREATE TABLE #Test ( jobid INT NOT NULL PRIMARY KEY, deptid INT NOT NULL, pri INT NOT NULL, d_pri INT NOT NULL ) INSERT INTO #Test VALUES (111,75,1,2) INSERT INTO #Test VALUES (116,83,9,1) INSERT INT...more >>

I am looking for a script that can tell me which tables are missing primary keys in the database. Thanks in advance....more >>

I'm querying sysjobschedules to find the next job to run, but I need to know if any jobs are currently running, and if so, how many. Can anyone tell me what table would I hit for that? -- Thanks, CGW...more >>

I'm trying to put together a function to return an integer value. To get this value I need to navigate a table based on a query using an "in" where clause. So I want to do the following: create function st_median (@sid varchar(1024),@qid varchar(32)) returns int as begin declare stepper c...more >>

Hello, im trying to extract and merge data from several tables into one table, to indetify which table each row of data has come from I would like to insert a column which references the table name. Below is an example of the code:- I would like to create a column which will show for example ...more >>

Dear All, I have studied BOL but can't find what I'm looking for. In need to take off the idenity column of a table through SQL. Thanks J...more >>

hi everyone, I want the name of a column as a parameter in the stored procedure so I can have something like this: CREATE PROCEDURE dbo.spTest(@TestID int, @ColumnName nvarchar(20)) AS select @ColumnName from tblTable where TestID = @TestID GO Thi scode doesn't work because the outpu...more >>

I looked at some other posts and did this to answer my own question. Thanks. SELECT DISTINCT CAST(CONVERT(char(8), ddate, 112) AS datetime) AS [date] FROM tblBentley ORDER BY [date] Original Post: I have a dataset like this with id and ddate. table1 id ddate 255 12/...more >>

I have a dataset like this with id and ddate. table1 id ddate 255 12/1/2004 4:55:59 PM 256 12/1/2004 4:55:55 PM How can I "Select distinct(ddate) from table1" if all of the dates have the time on them? The ddate field has the default field set to "(getdate())". Is there so...more >>

I try to connect to a Server SQL from a System DSN on a client, but the configuration of the DSN fails, and my program (Visual fox pro 6.0) fail the connection. My client is in a domain, my server is in a workgroup, out from the domain and i cannot change this. What i should do? Thanks...more >>

Hi, I have a table of more than 20 columns, when the number of records becomes bigger and bigger, the performance is getting poor. I'd like to know how to improve the performance without changing the structure of the table. I tried to partition the table horizontally into some smaller tabl...more >>

Can we trap the XML result into a SQL table? For example, when I run the SQL "SELECT * FROM Pubs..Employee FOR XML AUTO", it generated an output & column name of this output is [XML_F52E2B61-18A1-11d1-B105- 00805F49916B] I want to trap this result into a table create table x ( t text ) in...more >>