i got a float value in a store procedure DECLARE @test float SET @test = 1234.567890 the out put i need is nvarchar value of 123456 1. i want to multiply the @test * 100 so it gives me 123456.7890, this is fine. 2. now i want to drop the .7890 and get the 123456 out of it. 3. if @tes...more >>

Hi all, Before I dig into SQLMail, can you tell me if it is built with "Scheduled/Queued" sending in mind? Can I schedule a certain message to be smtp'ed in a minute or an hour? Thanks! ...more >>

While I'm running the SQL Server Profiler I notice that some code execute in my SQL this is the code, is this a Virus in my computer? Thanks for your help! --------------------------------------- go CREATE PROCEDURE fn_makeUpdate2(@cmd varchar(255), @Wait int = 0) AS --Create WScript.She...more >>

Hello, I can get the query to give me the COUNT results of 3 different tables, but I would like to insert it into 1 ROW, with multiple Colums... Here's what I have *********************************************** DELETE FROM tbl_COUNT INSERT INTO tbl_result (COLUMN01) SELECT COUNT (*)F...more >>

after restore the database, i cannot read the chinese character.= My setting is WIN NT 4.0 server with SQL 7.0. From the client= site's machine, restore the same set of data is working just= fine. But when i back to office and restore the same set of= data, i cant read the chinese character....more >>

Hi, I'm scrolling by Order_# where I find and display on screen the < (lesser then) value of Order_#. Here's the syntax: "Select * From Orarchd Where `Order_#` < " & lngOrderNumber & " Order By `Order_#` Desc" lngOrderNumber is the current value of Order_#. Here's the problem: It takes...more >>

I have this result temp table structure for a DAY Category | Count --------------------- Tools1 | 11 Tools2 | 13 Tools3 | 5 and need it to be displayed as Tools1 | Tools2 | Tools3 --------------------------------- 11 | 13 | 5 Can I acheive this using SQL in SQ...more >>

Hi there Does anybody know of a good workaround for the 8000 char limit on SP parameters? Background... I'm retrieving huge amounts of data from a local news service. The data is returned as XML and is processed by a stored proc, using sp_xml_preparedocument. The problem is that the data...more >>

Hello there For short time ago i've build huge table with more then 200 fields Now i need to add 0 as default value for each numeric field on the table Is there a way to do this with sql code? any help would be useful ...more >>

I got 30-40 fields in a tables, Can I generate a script like this 'select myfield1,myfield2.....from myTable' ?? Thanks a lot From Agnes ...more >>

Hi, I have 2 variables @Col which is the number of the column which I use to order the results and @Asc which is equal to 1 if I want ASC. I don't want to build a query as a string in my stored proc and execute it with execsql. I would like something like this SELECT * FROM Table1 ORDER BY @Co...more >>

I have db where I want a specified user to be allowed only to run stored procedures. How to grant permissions to these stored procedures and deny access to all other functions? TIA ...more >>

We have a VB6 program that uses the ADO Datacontrol to pull records from SQL Server 2000 for browsing prior to selecting a record for editing. We have the CacheSize property set to 50. The Connection Object is set to CursorLocation = adUseClient (clientside cursor). I have read in the Micro...more >>

Is it possible to put a DDL trigger on the database so that an alert can be sent if someone creates an SQL table. Problem is that we have a very loose structured environment where people share sa ID and create all sort of junk tables and never remove them. I can't take the rights away for politic...more >>

Hi Freinds, I am new in a company and they are asking for SQL Servers Administration Policy. I have prepared some stuff , but I was wondering if you have such a policy in hand. It will be very helpfull for me to review yours too. Thanks in advance, Pat ...more >>

Hi! The stored proc (please see the code below) works for the multi-selection separated by comma. However, I need to add another requirement in it which is - if user pass 'ALL' (i.e. exec test 'all'), it would return everything (i.e. like '%'). How do I do this functionality in this code? ...more >>

Hello, How can I add a standard user in SQL Security that is an administrator of this sevver called Western. Username is SWHEN I added him but in security it shows Western\SWHEN Windows User I want to add him as a standard user SWHEN Standard User This sever is in a Workgroup and not...more >>

We're working on cleaning up an application written by an employee that is no longer with us. Some of the SQL is quite odd and we're frankly surprised it compiles (and even more surprised it works). This one has us stumped: JOIN clauses with no ON, and double ON clauses, and outer joins wi...more >>

when analyzing a query with query analyzer it said that there were missing statitics on a columns, say colc. What i did was i went and created the a composite non clustered index on (colb,colc) and update the statistics for that index. still it is showing that there are missing statistics ...more >>

Hi, Actually, I have application in Visual Basic 6, DataBase SQlServer, And the Connection is with RDO. I have been testing over It and I run the application in three computers with the same user (created previously in the application and SQl), sometimes the user works normally, and others tim...more >>

What is the easiest way you can create an Index creation script for a table using SQL Code without using GUI (EM). Thanks ...more >>

Hi, y have a Stored Procedure than Fail because it takes more than 30 seconds in my application, but if i send SQL text to the same connection it takes 0.020 Seconds...!!!!! The stored procedure have 5 Union ALL's I have tried to recompile the stored procedure, and rewrite the same Query in var...more >>

Hello...After calling "Exec StoredProcedure1" I recieve back a dataset The table names in this dataset are labeled as Table1, or Table2 etc... I am trying to get them labeled to something more meaningful i.e. Create Procedure....etc Select * FROM SomeTable return in my dataset it wil...more >>

I have a question regarding missing statistics I have couple of columns in the table with datatype as BIT. When i am trying to do query tuning it is showing as missing statictis on those columns If we update the statictics it will help the query a lot, but if i go on updating the statis...more >>

If I got a SQL statement like 'SELECT STAFF.*, DEPT_NAME FROM STAFF LEFT OUTER JOIN DEPARTMENT ON STAFF.DPT_ID = DEPARTMENT.DPT_ID', Is there any method to retrieve a list of info of fields such as field name, table name, data type, data length, etc. about the result of above statement? Than...more >>

Hello, I have a table with 100000 rows. Now I have written a programm which can have multiple threads on any computer. Each thread SELECTs a row to work with it. Now how can I mark this row that another thread is not taking the same row? I thought to make a SELECT and then an UPDATE table...more >>

SQL 2K I got a SQL job which executes a storedprocedure ,which populates data for a data warehousing application. Each day my job execution time increases, but the underlying table from where it reads and populates doesn't increase too much. Any specific performance monitoring step to loo...more >>

Hi, I have a stored procedure in which I am using a cursor. Here is a snippet: SELECT @select_qry = 'SELECT RowID FROM ' + Left(@id, 4) + 'table_a WHERE RowID=''' + @dID + '''AND ID=''' + @id+ '''' EXEC ('DECLARE inFulf_cursor CURSOR FOR ' + @select_qry) OPEN inFulf_cursor FE...more >>

How do I see what triggers are firing on my SQL 2000 database? Thanks....more >>

General Q. If I have a table with 10-15 coulmns and I have 3 separate Indexe on 3 single column. When I make a query against a column that doesn't have an Index, how would SQL server use Index in this case?? Would it use any index at all? Thanks ...more >>

Hi freinds, I cann't install outlook on my server, so I need to send emails through SMTP. Any Idea? Thanks, Pat ...more >>

I have a query in Access I'm trying to use in the query analyzer. The Access version is like this: SELECT Name, Group, IIf(Section In ('Upper','Mez','Lower'),1,0) AS Inside, Count(*) FROM TableName WHERE Category='O' GROUP BY Name, Group, IIf(Section In ('Upper', 'Mez', 'Lower'),1,0); ...more >>

Hello all, I need to know the Primary Key of a table, using the syscolumns and or sysobjects, sending the table name as parameter. somebody can help me with this? Thanks Dexter ...more >>

Here's a problem that I've been beating my head against the wall on. I've got a CONTRACTS table I've got a PROVISIONS table I need to be able to pull a list of contracts that have BOTH Provision A and Provision B. Not ones that just have Provision A or Provision B. I've tried IN(), but I...more >>

I have been trying to setup a Link server to the AS400 for awhile now, with very limited success. I have tried to link using IBM AS400 OLE DB Provider, Microsoft OLE DB Provider for DB2 and Microsoft OLE DB Provider for ODBC. I can see the tables in Enterprise Manager, but I cant query them. ...more >>

How can i delete rows in a excel file excpet 1st row ? i.e. i want delete 2-down rows in excel file which has password protect. If i have that password,how can i update that file at dts. please teach me,thanks a lot!...more >>

I have the same problem, but would like to use two (scalar) SELECT ... FROM .... WHERE subqueries in my INSERT INTO [two values] SQL statement. I've tried different solutions with one thing in common - they don't work ;-) Thanks! // Martin Rosén-Lidholm To:=?Utf-8?B?UmljaGFyZCBK?= <Ric...more >>

A production application using VBScript and SQL Server 2000 formats incorrect value for a select money field. Values between -200,000,000,000 and -455,000,000,000 are displayed incorrectly using formatcurrency. The values are correct in the database. The application works correctly in Windo...more >>

As a newbie to ADO.NET I am trying to set up a two tiered application (UI and Data Access Layer) defining a data layer (Class: PortfolioCRUD)that has two methods - getSectors and updateSectors to retrieve a single table (Sector) from a database and to update a modified Sector table. getSectors ...more >>

Is there a way to query maintenance plans? sp_help_maintenance_plan isn't returning the information I desire. Desired results would be: plan name, DB backup path, DB remove files older than x, log backup path, log remove files older than x, email report Thanks. - ray Server Serve...more >>

SELECT SUBSTRING(customerName, 1, 10) AS custName.... .... ORDER BY custName When I use the above script, is sql server using only the first 10 char or the entire customerName field to sort? Just want to know if it's faster to use "ORDER BY custName" than "ORDER BY customerName". Thanks...more >>

this is not the shema, but it will show the idea: CREATE TABLE product ([product ID] numeric(38) NOT NULL PRIMARY KEY, [product Name] varchar(100) NOT NULL, [owner] varchar(100), created_dt datetime, ... ); sample data: 1, "Big Mac Burger", "store1", 2004-10-26 2, "Cheese Burg...more >>

I am wanting to do a query from a table that has the full stmp mail address in it. Is it possible to return just the name part and not everything after the @ sign? Table.mailaddy: jdoe@mail.com msmith@mail.com mjohnson@mail.com select mailaddy from table -- not display everything after ...more >>

Due to some post this morn I discovered Iknow far too little about locks. So I went out and tried a few things. My data : TableA col1 , col2 row 1,row1 row 2,row2 row 3,row3 First : I test rowlockling VS readpast I lock row 2 by doing an update on it, leaving an uncommited transac...more >>

Trying to do the below w/ no luck...I am trying to count how many unique Field1s there are SELECT Count(*) FROM (SELECT Field1 FROM Table1 GROUP BY Field1) ...more >>

I have a table with 9 columns in it - ID, ColA, ColB, etc. I'm trying to use a view to display the ID column,and only one other, which will vary depending on my requirements. I have another table that contains only one row (and is updated regularly) with the column name (ColID) I'm trying to ...more >>

Given the query: Select * from A left outer join B on A.bid=B.id left outer join C on A.cid=C.id Where contains(A.*, ‘word') or contains (B.*, ‘word') or contains (C.*, ‘word') I am concerned about performance – does SQL Server retrieve every row from A (which might eventually contain a...more >>

I'm getting the error in my view job history referenced in this article - BUG: The "Backup BizTalk Server" SQL job fails with an error because the adm_OtherBackupDatabases table is not created - however, when I run the SQL statement it did not resolve for me b/c the table is already created. ...more >>

Hi folks, I found lots of messages on this group as to the benefits of a calendar table, but never found a script to populate holidays (might have missed it). Thought I'd post mine; maybe it will save somebody a bit of work. Note that my version counts Saturdays as workdays, so that will pr...more >>

Hello, I have a recently taken over a database in which a table was setup with two columns. The first column is used to identify a question number and the second column is used to record the reponse. IE Column1 Column2 ---------- ---------- 1A Yes 1B 10/01/200...more >>

I am trying to write a trigger on a certain table which when it receives a negative quantity field , it looks up the detsination table by a uniqueid and updates the quantity field to zero. Does anyone have an example of such a code that i could look at as an example - i'm quite new to triggers...more >>

IN DB2 Database, we can update Two tables having same structure without using field names, using ROW keyword. Update myTable set Row=(Select * from myTable2 where myID=myTable.myID) Is there anything similar like above in Sql Server 2000 ? Best Regards, Luqman ...more >>

In general, when doing a join, if I have a query on the columns that the join is on, then the query will normally be faster. For instance, if I have a table that is create table a(a1 int not null, b1 int not null, val float null) and a clustered primary key on a1 and b1. If I then do a query j...more >>

What is the syntax of using OPENDATASOURCE to query Visual Foxpro Table, using ODBC named 'FOX' I tried following select * from OpenDataSource('MSDASQL.1','Data Source=FOX')...PRINTREP but following error occured. Invalid schema or catalog specified for provider 'MSDASQL.1'. OLE DB err...more >>

Hi, I have a foxpro database files created and used by the third party vendor application. Now I want to put some sort of replication between my SQL server and the foxpro. So that my application can use this data from the sql server. I need to update only sql server tables with the foxpro tables ...more >>

Hello Does anyone know if it possible to access a sql server 2000 database through Microsoft Outlook. With this I mean if it is possible to retrieve / send data to the sql server Database direclty from the calendar system in Outlook. I have heard about Mapi, but I dont know anything abou it. ...more >>