When and when not to set SET NOCOUNT ON? I know what it does but not exactly where and when I want to use it. ...more >>

Hi Experts, I would like to seek your opinion on how to solve or handle this type of scenario. Store procedure - p_GetCustomerEmail can be accessed from ASP webpage and another Store procedure calls. CREATE PROCEDURE p_GetCustomerEmail @CustNo INT, @CustName CHAR(50), @CustEmail CH...more >>

I want to use the sp_OACreate to create an instace of MSXML, something like this... Exec master..sp_OACreate 'MSXML2.ServerXMLHTTP', @token OUTPUT And I want to get the XML from a URL EXEC master..sp_OAMethod @token, 'open', NULL, 'GET', @Url EXEC master..sp_OAMethod @token, 'se...more >>

Hi Freinds, What is the default SQL TCP/IP port? and if I chabge it to smt like 1471, dows it have effect on performance? Thanks in advance, Pat ...more >>

Hi Freinds, I know that SQLSERVER is RAM hungry. We are using a box with 4 CPU and 8 Gb RAM Bur still very slow and slower than my other 2CPU/4Mb machine ! What could be wrong? Thanks in advance, Pat ...more >>

Hi, I have an orders table (testing server) that contains an empty fields "sentBy" column until the order item is dispatched, how can I see what orders are waiting dispatch by using a query as I am unsure what the WHERE value should be to return a zero or empty value. SELECT orderID, orderD...more >>

Hi Freinds, My restore process take aery very long time to complete ! I ran Profiler but don't see any action going on . What could be the reasons ? Thanks in advance, Pat ...more >>

I'm in the process of constructing a query to create invoices and I just want to double check something before proceeding. I'd appreciate any comments and suggestions on the following scenario. I'm selecting transactions from a table and using the group by clause to group them by client and pr...more >>

I have a problem with my outer join : I only get 1 record as result, I want to get 7 records (see table dossierstructuur) This is the result I get : ROWID onderverd omschr bespreking ----------- ------------ --------- ------------- 2 1. ALGEMEEN petechieen ...more >>

I am looking for a SQL Server Differential Backup script that creates a weekly full backup and daily differential backups. Hopefully the script would be like the SQL Server maintenance job deletes files in the directory they are older than 3 days for differential backups and replaces (delet...more >>

I'd like to quickly figure out which tables in my database contain records. Can someone help me to SELECT all "user" tables that contain rows. -- Patrick Rouse Microsoft MVP - Terminal Server http://www.workthin.com ...more >>

Hi all. I need to select a random row from a table. I was thinking of getting a random_number < MAX(id) and > MIN(id) and select * where id = random_number. The problem is that the ids are not consistent. So i could get an empty query. Maybe i can make a view that creates new ids, and select f...more >>

I have a table that represents a queue of rows to be processed. I want to have multiple servers on the network that work on the queue. I am using ADO functionality to access the table. What architecture/functionality should be employed to allow only one server out of the group to process a gi...more >>

Hi, Can someone help me with the following I get a new set of data every week. Each week a partitioned table is created automatically. This data is inserted in the partiotned table. Using Northwind Example: Customers Table Suppose I have three weeks of data therefore I have 3 partitioned...more >>

When executing a top query, is there a way to return (in the same query) a rowcount for what the query would return without the top? For example SELECT TOP 100 FROM MyTable WHERE MyCondition Can I add something to that query to return what the COUNT (*) would be without the "top 100" at ...more >>

I have a 6-table, 1 MB MS Access database that generates a 149-page section of a Word document. I expect this to grow to 500+ pages. I'm considering upsizing to SQL Server 2000, hence the cross post. ;-) The tables are normalized; data is not duplicated. I would like to extend the schema t...more >>

Hi all, I have the following stored procedure: CREATE PROCEDURE gross_net_vat_calculation @id_no_vat int, @booking_date datetime, @amount_gross money as select DMBTR = case when percentage = 0 then @amount_gross else round((@amount_gross / (100+percentage) * 100),2) end, MWSTS =...more >>

Is There a better way I could have written the following stored procedure? Or is my way cool? Checking: Username, Password, Activation = True CREATE PROCEDURE GetAuthorization ( @Username Varchar( 20 ), @Password Varchar( 16 ) ) AS DECLARE @AccountID INT SELECT @AccountID = Accoun...more >>

I have this XML... <?xml version="1.0" encoding="utf-8" ?> <int xmlns="http://myurl">0</int> I want to read, assuming that the XML is loaded in the @doc variable DECLARE @idoc int --DECLARE @doc varchar(1000) EXEC sp_xml_preparedocument @idoc OUTPUT, @doc SELECT * FROM OPENXM...more >>

Hi, Is it possible to restart SQLServer and the Agent from within a job? Calling a batch file with NET STOP SQLSERVERAGENT NET STOP SQLSERVER NET START SQLSERVER NET START SQLSERVERAGENT doesn't work because if you're in a job and the agent stops then the job gets canceled. Any other id...more >>

Hello, I am having a query that is performing poorly inside an SP. The query was supported by a set of indexes to boost its performance. The same query is running good on query analyzer, however when i run from inside a SP that query is running far slowly than query analyzer although i am g...more >>

can you update date a column with an if then statement. This is a simple example and I want to do something more complex, but I can't figure out how to do this simple example. can someone help? update car set field1 = (if (select passengers from car where id=1)='3' begin '2' end) ...more >>

Hi! I have a table defining the meta data for a database. (TableName, FieldName, FieldType) There are 49 unique tables and I started to create them manually. I would like to create a T-SQL script that will generate all tables and fields. Any ideas where to start? Thanks. Dee ...more >>

I installed sql server 2000. But, I was playing with names, and now i can't access to sql database. Then i uninstall it, and install it again, but he keep showing message "Some files pending for information. Restart before running setup". I restarted computer several times, but this message ke...more >>

I am just curious on how SQL Server stores index internally. What I mean is if there is a way to dig into the Index page to see what is actually stored there. Some sort of undocumented commands, procedures etc. Anyone has any insights on this?? -M ...more >>

Hi, I have an Employee Table. I am looking for a query that would return me a row of data of that table which has Maximum salary Ponnurangam ...more >>

Hi guys! I'm trying to get a recordset from access into T-SQL and openrowset returns this error: "OLE DB error trace [Non-interface error: OLE DB provider unable to process object, since the object has no columns" my sql looks like this: "select a, b, c,... from tbl", it has about thirty field...more >>

Hi, Can someone help me with the following when I try the following select count(*) from dept d, dept_status dd where d.dept_number = dd.dept_number I get the error message Server: Msg 8115, Level 16, State 2, Line 1 Arithmetic overflow error converting expression to data type in...more >>

Is there a sample Northwind application written in VB or VFP that uses the Northwind SQL database? Thanks. ...more >>

I tried to run the statement below on my laptop where I have a developmental copy of the database. HorSql01 is a linked SQL Server. Both machines are using SQL Server 2000 -- Fill in the 16 erroneous null VendorKey values UPDATE SoftwareProduct SET SoftwareProduct.VendorKey = av.Vendor...more >>

Hi, I would like to know how often I need to Reindex all the tables and under what situation I need to do that... How often I need to recompile the stored procedure, too?? Also, when I reindex the table, should I set the Fill Factor to be 0??? Can someone explain to me more details p...more >>

I am having a Set Statement inside a cursor and I am getting the Error on this line as A SELECT statement that assigns a value to a variable must not be combined with data-retrieval operations. SET @LastPaymentReceivedDate = 0.0 Set @LastPaymentRcvdDateInCurrentQtr = -1 Please advice ...more >>

Is there a way to raise an error in a SP, and if that error occurs, the SP is rolled back? If so, how? I'm using Begin Transaction, and Raiseerror event, however this does not seem to do the job? thanks ...more >>

Is it possible in SQL (not t-sql) unfortunately it's an Access DB i've got to work with to run an update statement that would assign random nubers to a field. (they don't have to be Unique either). I need to be able to run it once a week through an update statement. So I can't use the Access a...more >>

I've written simple SQL 2000 queries to our ActiveDirectory via a linked server (using LDAP dialect). I'm now trying to write a query that returns a recordset with rows grouped by the AD container they occur in: Our Active Directory has a container 'Departments'. Under that container are sev...more >>

Hi, below are the code DDL , Supose I have this tables DETAIL, HEADER and ITEMS and I want to add in the table DETAIL a Foreing Key ITEMS.CUSTOMER , ITEMS.PRODUCT but I can't do this. Because in the DETAIL table I don't have the field CUSTOMER Should I ADD a field CUSTOMER in the table DETA...more >>

I'm using a statement along the lines of: SELECT * FROM TABLE WHERE FIELDX IN ('abc','rd9','blah','etc',so on') Is there a way to assign the array to a variable, for example: DECLARE @Array AS ARRAY SET @Array = ('abc','rd9','blah','etc','so on') SELECT * FROM TABLE WHERE FI...more >>

I've got a SQL Server 2000 environment with production data on a remote server, and an MSDE instance on my local box for development. I'm running into problems with my DTS packages that replicate data to my MSDE box. I can create a DTS just fine, and it runs without a hitch; however when I ...more >>

Hello, all. I'm building a database for a Web immunization-record storage system. One input screen has me puzzled as how to best design one of the tables. The part that has me puzzled is this: For each immunization that is required, there are anywhere from 1 to 5 rules, and each rule has diffe...more >>

Hi, Can I join tables from two databases via OPENQUERY? Where can I find advance info on OPENQUERY? Thanks...more >>

I have question about setting up DBs on Clustered environment. I have to set up 2 nodes on SQL 2k. (Active/Passive). I am going to have one central server for the data. Now how does setting up clustering work on SQL End?? Do master, model, msdb, tempdb go to the same location where rest of th...more >>

I have question about setting up DBs on Clustered environment. I have to set up 2 nodes on SQL 2k. (Active/Passive). I am going to have one central server for the data. Now how does setting up clustering work on SQL End?? Do master, model, msdb, tempdb go to the same location where rest of th...more >>

Hi! I have an issue with migrating Access database into SQL Server. Is there any SQL Server substitution for the Last() sql function from Access? I couldn't find abything in msdn. I can try to do everything with cursors to get the last record, but is there any easier way to do it??? Thanks fo...more >>

Hi, IS there anyway to find to the last updated dates for stored procedures? Not the dates stored procedures were created. Thanks in Adanvce...more >>

This syntax worked in dbase, but now need it to work in SQL. Thanks in advance to all who may reply. John UPDATE COLLDESC.DBF SET CMS416 = IIF(LEFT(CMS436,2)='08','YES','NO')...more >>

Hi, I need to manipulate a history table via linkserver. This table is hugh (VERY) and is currently on a production server. Shou;d I make a copy of the table on another server and manipulate it from there? If so, how do I update the copied table on a daily basis? Or can I just create a view o...more >>

Hi, I have a table with some data in it, I would like to generate one Insert script command for each record. Is there a SQL Server tools for doing it? I mean, this is my table: NamesTbl Name Surname ------------------------------------- Filippo Bettinaglio John Gil...more >>

Hi I have a mixed managed/unmanaged project which thanks to you guys I've managed to get rid of the linker errors of, so cheers for that. But now I'm experiencing an unknown access violation. Firstly this is an extended stored procedure DLL, I've been able to debug it successfully by starting...more >>

Hello, I am passing 2 parameters in the URL top page "results.aspx": ....?name="john"&book="SQL server" In page "results.aspx" I have a dataset. "names" and "books" are 2 fields. I want to filter all records acoording to the following rules: "names" files contains "name" value AND ...more >>

Anybody please help me with this query. my table is ddl is: CREATE TABLE [dbo].[testTBL] ( [txtPO] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [dtDateStart] [smalldatetime] NOT NULL , [dtDateEnd] [smalldatetime] NOT NULL , [intQTY] [int] NOT NULL ) ON [PRIMARY] samp...more >>

Someone can help me in finding some programs that create documentation for my sql server DB? HTML or PDF (better) documentation....more >>

Hi I have a batch of stored procedures that execute in SQL as scheduled tasks. I want to be able to write the results of the stored procedure calls to an audit table. I can trap @@ERROR after all of my operations to check the result, and store this value away, but I cannot work out how to...more >>

I want an extra validation if the first two characters matches a string upon insert. Can this be implemented using a constraint or do I need a trigger? something like ALTER TABLE orders.dbo.PRODUCTS WITH NOCHECK ADD CONSTRAINT CH_ID_PRODUCT CHECK (CASE LEFT(IDPRODUCT,2) WHEN 'US' CHEC...more >>

I'm using MS DAAB V2.0 to retrieve a long set of data from a stored procedure, and filled into a dataset, then displayed on the webform. It's no problem in displaying data. However, for update back the modified data, I couldn't find a simple and smart way to do it. I understand that SQL ser...more >>

i want to find the hexadecimal value of a decimal number.For example 12->C,11->B.Is there any existing function which do this in Transact-SQL. ...more >>

Can anyone tell me how to get the toolbar to show in the Design View of a table. It contains the Manage Triggers, Manage Keys and Relationships, etc., buttons. I recently installed SQL Server 2000 on a new XP machine, and I don't see that toolbar. Thanks, Gerald *** Sent via Developersde...more >>

I need to know the number of distinct couple of two fields in my table. The query "SELECT DISTINCT myField1, myField2 FROM myTable" returns me the rowset correct, but i need to know only the number of row with distinct myField1, myField2. Can i use COUNT function and how? Or there is another w...more >>