Dear all, I will write a DLL by using Visual Basic. Then I will call the DLL in Excel VBA. Some variables will be passed to the DLL from VBA. After that, the DLL will get recordset from database will fill the cells in current active workbook. My question is how can I access the curr...more >>

i am trying to do what i would think is a very simple thing, define the default value for a column as the return value from a stored procedure within enterprise manager, when I enter the sp in the column Default Value , em forces this to be a quoted string. ie entering dbo.MySP for the default...more >>

I don't know where is appropriate to post this question, but anyway... Our company is going to develop a system (that includes finance, inventory, store, delivery and etc.) using MS SQL Server 2000 as the database. The program also requires the user to be able to access via the Internet using ...more >>

Hi Guru's, I'm trying to use SQL Server 2005's new Row_Number() function to make my life paging data easier, but have run into a little propblem trying to page a set of distinct values. Here is my query: Select distinct city, Row_Number() over (order by City) as RowNumber from MemberData...more >>

Hi gurus, If I restore a database DB_A into a standby mode (i.e. restore database DB_A from disk= .. with standby = ...), I can run sp_detatch_db to detach the DB_A, but I cannot attach it back because I got the following error Msg 1824, Level 16, State 1, Line 1 Cannot attach a database tha...more >>

When a stored procedure is created or when it compiles are the parameters saved in a table separate from the SProc text. I'd like to query them. Thanks...more >>

i need to query a date between two values, kindve like this :- mydatecolumn between '07/09/2006 00:00:00' and '07/09/2006 12:00:00' however i need those dates to be variable per day (SP) so :- mydatecolumn between getdate() 00:00:00 and getdate() + 12:00:00 is this possible ? ive...more >>

SQL 2K I got a table 'Sales' with columns CompanyID Deptid ChrgAmt Load I want to generate a report with Top 10 Deptid (in terms of ChrgAmt, Load) for each CompanyID. Thanks for your time. Mike ...more >>

I have a table which contains the file location for each vendor. For ex. tblVendor Vendor Location ABC C:\ABC\Results1.xml SBC D:\Results2.xml TNS C:\Results3.xml I want to loop through this table and select and retrieve the xml file path for each vendor. Then u...more >>

I have a table that has two types of results for each part: part, type, result ---- ---- ------ a 1 1 a 1 4 a 1 5 a 2 4 a 2 7 b 1 1 b 2 5 .... I need to produce a result set that takes the averages for each part's types (so I h...more >>

Inside my stored proc I have a SELECT statement which uses a table on a server whose name I only find out when the proc executes. I'd like to pass this servername as an argument. Is that possible (well, without using dynamic SQL!)? I tried: DECLARE @servername VARCHAR (25) SET @server...more >>

I use the following way to send out email message from my stored procedure: EXEC master.dbo.xp_smtp_sendmail @FROM = 'aaa@bbb.ca' , @TO = 'aaa@bbb.ca' , @subject = 'test message' , @type = N'text/plain' , @server = 'SQLEMAIL.bbb.ca' , @attachment = '...more >>

How do I update columns from one table with columns from another table? update employees set employees.address1=employees1.address1 , employees.city=employees1.city, employees.state=employees1.city, employees.homezip= employees1.homezip inner join employees1 on employees1.emplid = employees....more >>

I have been researching the best way to calculate median values using SQL. Most of the threads I come across include a semi-canned response from Joe Celko which I have some questions about. First, Celko says, > The statistical median of a column with duplicate values can be found > with a ...more >>

I have a feeling that I can't do this, but thought someone might have a way to work around this. I have a UDT type, called interval, that I want to be a parameter to a UDF. Let's say interval 1, i1 is [2:4] and interval 2, i2 is [3:5]. I would like to do something like i1 intersect i...more >>

Hey guys, Hopefully one of you has seen this before...... We have a new which we have seen reach the max value on four occasions in testing. When I look at the amount of data in the table, it contains about 15-20 rows. We are not doing any automated testing that inserts billions for ...more >>

I need to encrypt a field that contains confidential information, but I also need to decrypt that field later. This must be reasonably strong, not just a rotate or simple addition. Thanks, _harry ...more >>

I have a column ('ProblemResolution') in a table ('Incident') that holds plain text. I am doing a query against that column and converting the results to XML as follows: Select ProblemResolution From Incident Where RowID = 2 FOR XML AUTO, ELEMENTS The problem is the XML that is being gen...more >>

I am currently doing performance tuning, in particular, query tuning. Before I used a user defined function to get the result set. The new query now uses "set-based" approach to get the result set. I am pretty sure the newer version is a lot more efficient. Since the new query has more joi...more >>

/************************************************/ Example Table /************************************************/ create table UserInfoTest ( FirstName varchar(100), LastName varchar(100) ) /************************************************/ Example Data /*******************************...more >>

I have a .CMD file with an OSQL command that does a simple -Q "DELETE FROM MYTABLE". Under some circumstances this table does not exist, and I'd like to know about in the batch file. I'm trying to trap for this with: if errorlevel 1 goto :errorlabel But OSQL doesn't seem to set the DOS...more >>

Hi, I've got the following question. I always used this technique with temporary tables to cope with paging in sql server 2000 but now that we got sql 2005 running in our company i've got to rewrite the paging stored procedures to a version that uses ROW_NUMBER. Basically, this is the sql s...more >>

hi, i met one problem in sql server my table name is : student Field1 : name Field2 : rank ...more >>

I would like to be able to update customer and insurance dollar values in one pass using the UPDATE. Below is my code so far for the update but it only updates the Insurance columns. For example, what I want to accomplish is to make InsuranceAmount = CustomerAmount and make CustomerAmount = ...more >>

We are thinking of moving to SqlServer 2005 and I have come across the following problem and I am looking for a "best practices" solution to it. Currently we access all of our data through stored procedures. Any auditing we do happens on the client side. We are now looking to add a lot more audi...more >>

Is this the proper way of doing a join in a delete or update? Is the a better way? delete useraccount where useraccountid in ( select useraccountid from UserAccount with (nolock ) inner join employee with (nolock) on employee.personId = UserAccount.PersonId where EmployerId =66 ) -- Arne Ga...more >>

I'm using SQL Server 2000. Is it somehow possible to use the Index Tuning Wizard or Display Estimated Execution Plan with stored procedures using temporary tables? For instance, running the statements below give the following error: "Server: Msg 208, Level 16, State 1, Procedure test, Line 1 ...more >>

Hi, I have a very basic query question. I have two tables tblabc and tblxyz. tblabc has three col abcid, abc1, abc2 tblxyz has three col xyzid xyz1 xyz2 I want to insert value in tblabc from tblxyz where xyz1 like '%OPEN' (do not want xyzid and xyz2) with this I want to insert abc2 as '...more >>

I've just written this UDF as a compliment to GetWorkingDays, which I see around the net a lot. I'm just looking for general comments and to leave it here for anyone who wants to use it. /*==============================================================================/ -- -- SCRIPT : ...more >>

Hi I'm trying to register the JDBC driver, I found the articles on how to do it but there are no instruction on which tool to use or the command. If any one has any idea that would greatful. thanks...more >>

Hi, In SQL server 2000 . Can I get list of tables and the number of page per table that currently exist in the cache ? Event table list without number of page will be good. Thanks, Eyal ...more >>

Hi I have a Stored Procedure in Database DB_A, where i'm getting data from Database DB_B, both on the same SQL 2000 server S. Like. Select * from DB_B.dbo.Table_A A, DB_B.dbo.Table_B B Where A.A_Id = B.A_Id And it works fine, but it is very slow, compared to running th...more >>

Hi I'm trying to execute a stored procedure which contains the below line. exec master..xp_cmdshell 'echo abcd' I granted permissions to the user by using the follwing lines sp_grantdbaccess 'LimitedUser' go grant exec on xp_cmdshell to LimitedUser go but when I execute the store...more >>

Sorry I included this code but I needed to demonstrate how clear and simple I am pursuing an answer to my question and how dumb I feel. This is an HTML text box and a submit button. I want to enter data into the textbox and insert that data into a SQL Server 2005 Enterprise Edition table. ...more >>

Hello I have a problem, I was given a table full of addresses and I found that zipcode and city were in same column, so I need to run an update that separates those. Problem is that there are 2 spaces so I cant just split at a space. Format: 123 45 City How I do that? /Lasse ...more >>

Hi, Is there any way to get the error message within a stored procedure. I have to store the SQL Server error message in a table. TIA Thanks P ...more >>

Hey MS Sql Server 2005 I have a scheduled task which failed. Where can I see the reason why it failed? By the way, in the Job Steps Properties window I listed (in the "command" field) up the stored procedures I want the task to execute: Hello_Publish Hello_SendEmail Any suggestions...more >>