Hi all, Why I can't do the following: DECLARE @stringConnection as varchar(150) set @stringConnection= 'Data Source=vrpims\cpims'+';'+'User ID=sa'+';'+'Password=sa' select @maxAttendancesVC= max(AEATT_REFNO) from OPENDATASOURCE( 'SQLOLEDB', @stringConnection).materprod.dbo.ae_attendan...more >>

How would I go about capturing the output of a stored procedure that is being returned through a SqlPipe? I have to write a CLR sproc in c#, and I need get a string value out of it in the middle of a t-sql script. The only way that I can figure out how to get the string back out is using the Sql...more >>

Dear All, I got a SQL statement like that Insert into TableA (columnA,columnB,ColumnC) select VariableA, VariableB,VariableC Where columnA,columnB,ColumnC are Varchar. VariableA, VariableB,VariableC are variables return by VB Function. If the VariableA = A, Variab...more >>

I have to repost this, so I apologize if anyone responded. For some reason the copy I sent is not being refreshed in my newsreader Inbox. Is there a benefit to using one over the other? Both give me the same result. Should I go with the function or the stored proc? ALTER FUNCTION f_Phr...more >>

I am building a string variable that is my SQL. I am getting a "conversion failed when converting the varchar value". My parameter is defined as @MetroCode int = 0 This works: SELECT @output ='SELECT * FROM table1 WHERE col1=13' This doesn't: SELECT @output ='SELECT * FROM table1 WHERE...more >>

Hi all, My sql 2000 server is using 1434, I try to use the tool to fix the worm but it doesn't find anything. How can I fix this issue? Tks in advance JFB ================================================= Process Name : sqlservr.exe Process ID : 1432 Protocol : UDP ...more >>

Hi, We have an Oracle database with 3200 tables. We want to migrate this database tables (both structure and data) to SQL Server 2005. What would be the best way to do that? Is there any automated migration tool for doing that? Is there any white paper that explains Oracle t...more >>

Is there a performace difference in the following queries, assuming tblAgent contains 100,000 records? IF EXISTS(SELECT * FROM tblAgent WHERE AgentID = @SomeAgentID) BEGIN .....do operation A END ELSE BEGIN .....do operation B END ______________________________________...more >>

I have several different situations where I want to Update the record if it exists or add a new record if it does not exist. An example might be -------------------------------------------------------------------- -- Has this table already got a record for this entry.... select @ro...more >>

Hello, I know the difference between the two but here my problem. If I run a script in sql server using xp_cmdshell with cscript.exe it does not work.....i use wscript.exe it work fine. my script takes in parameters. the one that lookx like it has an effect of which one you use (cscript.e...more >>

One of my pet peeves is stuffing all custom fields into one table. For example, Company table would get everything related to a company For 1-N data, it's simple because it necessitates a separate table. However, for 1-1 data, it's not so much clear cut. So here's a real life scenario: ...more >>

We are building a web application which can have and infinite number of menu items and levels for it's main menu. For example: TopLevelMenuItem1 SubLevel1MenuItem1 SubLevel1MenuItem2 SubLevel1_2_MenuItem1 SubLevel1_2_MenuItem2 SubLevel1MenuItem3 SubLev...more >>

I have query: SELECT name,company,sum(quantity) as quantity from table GROUP BY name,company WITH ROLLUP HAVING name is not null But in result set I get also resulting rows with name is null: name company quantity -------------------------------- NULL NULL 100...more >>

Hi, I need to import dbf file into sql server. This dbf file is about 300 MB with a 250,000 records. The import process needs to happen once a month. I can create a DTS package and schedule it but the problem is that the structure of DBF file (i.e. no. of columns) changes. So, my solution was...more >>

Can SQL Server 2K or 2K5 generate unique keys for a record? In Oracle we use a sequence. Thanks. ...more >>

I hope I can trouble someone to help me with this: In my SQL Server 2000 proc, I have a constant and a variable: DECLARE @c_cr char(2) SET @c_cr = char(13) /* carriage return */ DECLARE @sql nvarchar(4000) I am debugging this proc in CAST SQL-Builder. At this point in debugging...more >>

How can I check if there are any special chars in a certain field? How can I integrate this into my Select query? I need to check the field [Name] and see if the users have mistakenly typed special characters into the names. Thanks....more >>

The 1st 4 lines of code were copied verbatim from a VBS file that works perfectly. Why does it fail when running from a DTS ActiveX script? Function Main() set objShell = WScript.CreateObject("Wscript.Shell") wait = true strEXEC = "C:\FTPTest\FTPSNAP.BAT" objShell.run strEXEC, 1, wa...more >>

Hello, How do I convert UTC datetime to a local time on SQL Server 2000? I mean not just current time, but entries from months and years ago. In another words, I have a table with UTC datetime and I want my query to return rows with local time. I would appreciate any hints. Tomasz Sor...more >>

I'm kind of at a disadvantage here as I'm not a developer, but am putting out the questions here for a little advices. I have a contract developer that has requested a product called SQL Bundle Standard from www.red-gate.com and I'm wondering if it is necessary. Our environment consists of ...more >>

Hello, How do I convert UTC datetime to a local time? I mean not just current time, but entries from months and years ago. In another words, I have a table with UTC datetime and I want my query to return rows with local time. I would appreciate any hints. Tomasz ...more >>

Hi, Here is my sql result rows. Order Amount Company Size Total 1 109.8927 aaa 16-20 1 18.06736 aaa 21-25 1 68.10098 aaa 26-30 1 213.5475 aaa 31-40 sum of Amount 2 140.241 bbb 41-50 2 145.4496 bbb 51-60 2 61.67831 ...more >>

I need to convert a MS Access 2000 query into SQL Server 2000. Since the IIF statements are not allowed in Transact-SQL, I 'm trying to use the CASE Statement I have two tables, Results and Sections. I need to import the section records into the Results table. I tried to do a CASE statement...more >>

Hi, First of all, thanks for the answer about "Have you found any solution for this?" qustion... Here is another question, but related previous question. Environment : Win Svr 2003/SQL Svr 2000 As far as I know, to specify a UNC path for backup location, MSSQLServer service should be run...more >>

I SQL Server 2000 table that has varchar(50) format that has date time samples in this table. sample: 2006-01-21 12:00:22 I would like to write a SQL statement that will convert the varchar(50) to datetime output format. Thank You, ...more >>

I am using SQL Server 2000 that has a linked server to another SQL Server 2000. I only have view access to the linked server. I wrote a stored procedure that takes 4 parameters it runs fine but very slow. Can I use OPENQUERY in the stored procedure to retrieve the data from the linked ser...more >>

Hi all, I need do one thing, and I need help. I have 2 tables: CREATE TABLE terminais ( --... terminal_id uterminalid NOT NULL, --... )ON [ucs] CREATE TABLE estabelecimentos_terminais ( --... CONSTRAINT terminal_id__estabelecimentos_terminais__fk FOREIGN KEY(terminal_id) REFERE...more >>

How can I convert a float value to a string, without rounding issues or ending up with scientific notation ? Seems like a simple request. I have various types of numbers stored in a Float field that I need to convert to a string as is. I don't want rounding, nor do I want additional decimal ...more >>

I need to populate an existing table with the same data contained in/returned from an inline function table. An external app uses the data from the inline function table in a form. I need to be able to update that data also in a form, so I need to populate an existing table with the same dat...more >>

Dear all, Keep in mind the structure of the following table I would need alter ID field and add an IDENTITY property but when data are already loaded. The source table begin from 4200 as value in the first row and if before of that I enable IDENTITY when I load the data into VIA_DEBUGINFO ...more >>

I have a little batch query, and it putters along. It last completed in sixteen minutes, twenty-nine seconds. Now, I have check Tools/Options/Connection Properties/Set statistics time, so I get a little information on how much time each step is taking. In one step, I sort and filter a view, then...more >>

I thought I had read of a tool that would look at an existing database and them make recommendations for a better design. Perhaps I was dreaming ;-) If you know where it is, please post a link. Thanks, IanO ...more >>

How can I programatically change a table from ID Value 123 12 123 14 123 16 123 142 123 67 to ID Column1 Column2 Column3 Column4 Column5 123 12 14 16 142 67 tnx, ds ...more >>

Hello, I have data in a table as follows: number type percent 147823 MTOR 100 147823 CLOR 100 147964 CLOR 100 148078 MTOR 50 148126 CLOR 100 148126 MTOR 100 How do I write a sql to get it as follows: number CLOR MTOR 147823 100 100 147964 100 148078 50 148126 100 100 ...more >>

I want to execute the following SQL trigger: /* Trigger: t_CASE_VISITS_U2 Purpose: Sets the value to the previous value (DELETED.c_vis_date) if the incoming value (INSERTED.c_vis_date) is '1/1/1900'. Be sure to test the condition where the value already is '1/1/1900', to avoid a endl...more >>

Hi I have a stored procedure which gets executed from ASP code, the ASP in turn uses MTS . the problem is, when the stored procedure encounters a rollback statement, instead of just sending the error message and then rolling back the transaction, the application aborts with a SQL Server err...more >>

Hi, I've seen a post that he/she has exact same issue like me... However, I couldn't find any solution for this... This is the past post that somebody wrote the issue... ---------------------------------------------------------------- From: Wayne Antinore - view profile Date: Tues, Ap...more >>

Dear all, I would like have at the same time two available Enterprise Manager in my workstation. I mean, keep in mind that one should show a concrete settings and another one the same behaviour. For instance, every EM with different databases hanged. Is it possible to configure MMC for that...more >>

hi everyone, We currently have sql2k. We have got a database which contains almost 800 tables. Such tables are attending a lot of ASP solutions, some of them using 30 and others only 4.Spliting up these tables into specific databases will increase us nearly over 20 databases in our frame...more >>

Hi All, (I posted this in another group but didn't get any answer so posting here. ) I have recieved some 50 trace files (.trc) from the client and they want me to analyze them all in 1 hour and let them know certain things. I have heard there is some way to analyze all the files simu...more >>

We have the following select statement SELECT 1 FROM DAT_SAMPLES D , WRK_SELECT W WHERE W.TRANSACTIONID = 53364 AND D.ID = W.RECORDID AND D.APPROVAL IS NOT NULL AND NOT ( D.APPROVAL = 1 AND D.APPROVEDBY = 'SYSTEM' ) There are approx 350000 records in DAT_SAMPLES with a clustered index ...more >>

I am using SQL server 2005 Service Broker. Can I write a trigger on a Service Broker queue. Basically I want to mirror the queue. So whenever a message arrives into the queue, i want this trigger to get fired and copy the entire row in another Mirror Table. Is this possible? ...more >>

I am using SQL server 2005 Service Broker. Can I write a trigger on a Service Broker queue. Basically I want to mirror the queue. So whenever a message arrives into the queue, i want this trigger to get fired and copy the entire row in another Mirror Table. Is this possible? ...more >>

Hi, I understand there are no aggregate function for ORing multiple row columns together. We have a access control system that looks like CREATE TABLE AccessTable ( UserID int NOT NULL PRIMARY KEY, EntityID int NOT NULL PRIMARY KEY, AccessMask int NOT NULL ) where Acces...more >>

My proc: CREATE PROCEDURE ListOrderDetails DECLARE @idoc INT DECLARE @inn AS VARCHAR(1000) SET @inn = ' <ROOT> <OrderDetail num="261"/> <OrderDetail num="263"/> <OrderDetail num="264"/> </ROOT>' --Create an internal representation of the XML document. EXEC sp_xml_preparedocu...more >>

Hi Guys, does anyone know of any nice tools that will take the text output of a profiler trace (with show execution plan traces on) and turn it into the graphical version that you see through a query analyser? Cheers Will ...more >>

we r using xmlconcept , sp_xml_preparedocument taking 1/8 of the total memory but our front end tool is PB .often our application close cause of locking at a time more than 8 users executing sp_xml_preparedocument what will happen , i need urgent soultion ...more >>

We have an ASP.NET application with SQL server SP3A as back-end. We have a Stored procedure where we fetch the data from a table. When we execute the stored procedure on the SQL server having SP3a we get the result in primary key order. But when we execute the same query on a database which ...more >>

When an Object is scripted in From Enterprise Manager it Precedes Strings with an "N" however these scripts seem to still work if the N is removed so I would assume that the "N" has something to do with collation or the Encoding or something. I've tryed to find something about it on the net but ...more >>

what is the diff between cast and convert ...more >>

we r using sqlserver2000 , we r using VPN if i am running Quries Via VPN often Locking Occur Particular Db Give Me some solutions this is very urgent...more >>

I have the following schema Permission (Id int identity(1,1) primary key, UserId int, ItemId int, Bit1 tinyint, Bit2 tinyint, .... Bit31 tinyint) ItemId is the item being permissioned, while Bit1-Bit31 are permissions for that node (the actual permissions names are not given here as th...more >>

I have a table with a field that contains a currency quote as a float. In another table I have a rule field as a varchar. We have an application that uses both to gennerate a price. I need to do the same thing but in SQL. An example could be: Price = 1.05 rule = '*100' Another example: ...more >>

I'm working on SQL2000, SP3a. I know why my sysperfinfo table is empty - I had a performance tool working away whilst I made changes to the properties of the server via enterprise manager. I have seen a few other posts on how to resolve this problem, so I've tried everything from merely using th...more >>

I found in different scripts queries like this: SELECT s_name, level FROM staff START WITH s_sup_id IS NULL CONNECT BY PRIOR s_id = s_sup_id May be this is ORACLE-like, but I wonder that SQL-Server (2000) highlights several components as it would know them as key words. The query gives the...more >>

Here is what I'm running. The statement from @cmd_line variable does not get executed. There are no error messages either. Any help much appreciated. declare @hr int declare @object int declare @src varchar(255) declare @desc varchar(255) declare @cmd_line varchar(1000) exec @hr = sp...more >>

I have a database of 300 tables that needed to insert Primary Key: 1.Set ENO as PRIMARY KEY in all tables that contains the field ENO. 2. Set RPT_NAME as PRIMARY KEY in all tables that contains the field RPT_NAME. Is there a fast way to insert it once and for all rather than inserting i...more >>

Dear all, If I have a String str ='ABCDEFG' , If I want the first 4 character ABCD, the I use substring(str,1,4). But If I want to have EFG, that means I want to extract 3 character from the Back starting from G, is there any function like VB's Right() function in T-SQL. Thanks....more >>

Hi, I'd like the email be sent only when the query has results. When it doesn't (null or 0), the email should not be sent: EXEC master..xp_sendmail @recipients = 'mike', @message = 'Message text', @query = '---', @subject = 'SQL Mail test to attach query results', ...more >>

Dear all, Dear all, I got a table-A which store a lot_no as follows. lot_no: price JL-DO-1029n-A-0 100 JL-DO-1029n-A-0 100 JL-DO-1028n-B-0 ...more >>

I need to show the following numbers as such in a view (in a 15.3 format)... 12.3 as 000000000000012.300 10 as 000000000000010.000 12.3367 as 000000000000012.337 Is there an easy way to do this using a Cast ? Thanks ! ...more >>

Hi pals, In my win32 console application , I use to run the Stored procedure(SQL server 2000), verfy often. Stored procedure contains two "select count(*) from table" statements and three insert statements. Usually , the execution time for this SP is not more than 2 milliseconds. But some ...more >>

HI all, How can I find out the size of a particular table in bytes, as its proportionate space on disk Thanks RObert ...more >>

Hi We have a 3rd Party Database that's stucture is constantly being ammended by the Providers. We often make bulk ammendments to data but before we do we check that there are no extra fields that we should also be ammending. Usually this is a process of asking the providers (the accuracy o...more >>

Hello there I have some store procedure that run on many views by cursor. One of the views is failed on error level 16. And therefore the batch is being terminated. Is there a way not to terminate the procedure and continue? ...more >>

How can I execute an SQL command inside a read loop? My code looks something like this: With cmd1.ExecuteReader Do While .Read cmd2.CommandText = "..." Name = CStr(cmd2.ExecuteScalar) Loop .Close End With But it fails at the ExecuteScalar method, with an invalid operation ...more >>

Just curious, can you set up foreign key constraint from a table from different database? eg say CodeID of Table1 in Database1 referencing CodeID of Table1 in Database2 (of course Database1 and Database2 are in the same sql server) Thanks ...more >>

Hi I'm working with SQL 2000 and I want to know what data type is suitable for long texts and what's the limit for its length? Any help would be thankful....more >>

Hi, I have clients which connect via Internet and Intranet. How can I store the IP address and time in my dbo.Log table of each user? TIA Ana ...more >>

the sql cookbook is excellent. are there any other books like that? ...more >>