I am trying to do an outer join and can't seem to get it to work. I actually need to do a 3 table join, but I need to get this to work first. I have the following 2 tables: CREATE TABLE [dbo].[SkillsTest] ( [ClientID] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [Posi...more >>

Hello, Does it make sense to update statistics after all indexes on a table have been dropped and recreated? And another question... Maybe I should make another post about this one, but here it is anyway: What's the best way to keep queries performance optimized? What's happening wi...more >>

What is the best freeware SQL GUI Client for Oracle/SQLServer/Sybase under Win2000/WinXP? With SQL Squirrel and DBVisualizer (free version) I cannot: - unload all rows data (=generate INSERT statements with current content) - edit Views - Edit Table cells Has someone a recommendation for a ...more >>

I am trying to recreate my database from work to my home machines. But use Sql 2000. One error I get is that bigint is invalid type - but my tables have bigints in then Another is that Scope_Identity is not valid - but it works fine at work. Here is one Stored procedure (with errors at e...more >>

I need help with the syntax to use with the sp_OAMethod ... From VB, I use the command bfopen = objMspApp.FileOpen(<DB_Name>\Project_name, False, , , , , , "username", "password", "MSProject.ODBC") So translating that to a Stored Procedure, I tried: EXEC @hr = sp_OAMethod @object, 'FileO...more >>

I am looking to implement a program to determine SP's that have changed since a given date I see no way of doing this, ie) there is no 'lastupdated' column in the syscomments table I am from an Oracle background, so any info would be great thanks ...more >>

When I set @reorderfav = 1 or @reorderfav = 3, it works perfect. However, when i set @reorderfav = 2, it throws this error: Server: Msg 241, Level 16, State 1, Line 7 Syntax error converting datetime from character string. I have also put the ProjectMain.ProjectName in the ORDER BY and ran it ...more >>

hi, I have a table which stores some user details like email, name and Flag. Now currently we have a stored procedure which runs on the table and selects all the records from this table where flag=0 processes these records and sets the flag to 1. This process is working fine. But now we want ...more >>

A question of database owner chaining: I have a SQL 200 server. In master the database owner is sa. In a db called SPOA the database owner is sa. (sp_changedbowner 'sa') The dbo is linked to login sa. I create a stored procedure in SPOA: create proc test_OA as DECLARE @object in...more >>

If I want defragment the hard disk. I will stop the service of SQL server. Is it OK? ...more >>

I have been trying to run the following section to dump last 100 transactions in the trace file and I am getting an error that this procedure is not found in master databasse. How do I get this proc? Also, I dont see this proc in BOL (Upddated one). Any help? use [master] declare @P1 n...more >>

I am trying to find more elegant design solution for the following scenario: TABLE: RULE ------------------------------------- RULE_ID STATE PRODUCT MARKET ------------------------------------- R1 ALL ALL ALL R2 NY DOL NULL R2 CA ...more >>

I am trying to find more elegant design solution for the following scenario: TABLE: RULE ------------------------------------- RULE_ID STATE PRODUCT MARKET ------------------------------------- R1 ALL ALL ALL R2 NY DOL NULL R2 CA ...more >>

Is it possible to declare a variable based on an existing column? Instead of: DECLARE @myvariable VARCHAR(20) Use: DECLARE @myvariable mytable.mycolumn%type WHERE Table MYTABLE has column MYCOLUMN of data type VARCHAR(20) ...more >>

How do you call a stored procedure using the "+" sign in the parameter: exec test_sproc 'this is a ' + 'test' Do I have to use a variable, like this? declare @string varchar set @string = 'this is a ' + 'test' exec test_sproc @string ...more >>

When we run a stored procedure on one server we get the following results consistently: Num value 19 a 22 a 31 a 23 b 14 b 25 z The stored procedure orders by value. When we run the same stored procedure on the same table on a different server we can g...more >>

Hi, how can I get the object_name from a given dbid? I think the only solution is to use dynamic sql with cursors because I can not run dynamic sql inside a UDF... can you help me, please? create table dbo.objects ( dbid smallint , objectid int ) go insert into dbo.objects select 1...more >>

Is there a join limit to the number of joins that can occur within one query? Some one asked me that question and I didn't know how to answer. I do know that there are performance issues with "too" many joins but I do not believe that there is an imposed limit based in the DBMS software. ...more >>

I want to rewrite a stored procedure to user defined function because I want to insert into a table a subset of the returned columns from stored procedure. I want to insert UserName, GroupName and LoginName from sp_helpuser into a table. Prease help me how to rewrite this procedure ...more >>

How do I calculate the number of weekends that are between two dates? *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it!...more >>

Hi Guys, Need some help. I have a sql script generated in oracle consisting of insert into values and I'm trying to run that script in SQL Server 2k, but it has a TO_Date function and I'm unable to run it. I looked on the web for a work around and didn't come up with anything. below is a s...more >>

I have a script that calls other scripts using :r. I am using osql to run the script. I am getting an error that seems to indicate GO is not allowed in a :r script, yet it is required to create a table and trigger in the same script. How do I do this? I have greatly simplified the scripts, ...more >>

I'm trying to manipulate the date to return as 2/24/05 as opposed to 2/24/2005. So far I have this: select GoodDate=substring(convert(varchar(12),getdate(),101),1,6)+ substring(convert(varchar(12),getdate(),101),9,10) Curious, is there a better way to achieve this? The noob thanks you!...more >>

I have a script that calls other scripts using :r. I am using osql to run the script. I am getting an error that seems to indicate GO is not allowed in a :r script, yet it is required to create a table and trigger in the same script. How do I do this? I have greatly simplified the scripts, ...more >>

I want display the member's information such as his name, parentno1's name and parentno2's name. Can you show me the SQL statement? Thanks a lot. MemberNo Name ParentNo1 ParentNo2 Sex 1 Mike Johnson m 2 Jessica Johnson 1 4 f 4 Mary Johnson f 3 Peter ...more >>

What is the difference between sp_who and sp_who2 ? Thanks!...more >>

Hi, I'd like to sum the salary for the PayDate<'5/1/1985'. Can I use one SQL statement to get it? Please show me the SQL statement. Thank a lot. EmployeeNo PayDate Salary 1 1/31/1985 $1,000.00 1 2/28/1985 $1,000.00 1 3/31/1985 $1,000.00 1 4/30/1985 $1,000.00 ...more >>

Hi, I was wondering how i can create a proc, which will return a recordset, with multiple optional parameters. For example: select * from persons where language = @lang or country = @country or city = @city. If i use 2 parameters, i'm getting the result i want, but 3 it gives me more rec...more >>

Hi all, I'd like to ask if is there any deference between executing a stored procedure with 'exec' command in the Query analyzer and executing the code of the stored procedure in Query analyzer too. I mean that I copied the source of stored procedure in a window of the analyzer, I declare t...more >>

Guys, Have a question for you. I have a store procedure that returns reps for a certain account. Each account could have multiple reps, so naturally I have a separate table for them. So, when an account has two reps, the stored procedure returns something like AccountID RepID LastNam...more >>

Hi, Does the introduction of the "LIKE" operand in a query force a table scan? Thank you. -- -- Mark Holahan ...more >>

Hello, I need help to write a SELECT. I have the following select statements that I ran on Query Analyzer then go over the results one by one. This is not efficient since I have to review about 200 selects. My query is looking for any record counts that don't match. [3064 is the record c...more >>

Hi all, I have come across a strange performance difference using SQL Server 2000 SP3a on different hardware platforms (some Intel, some Compaq), or when switching hyperthreading on or off. I've seen this on XP and on Server 2003. The performance of certain operations is radically slower when...more >>

Hi, I want to set a variable to the rsult returned from a select statement. The following example is an oversimplification of what I need to do. declare @var as int; declare @strCommand as varchar(8000); set @strCommand = ' Select @var = select count(*) From MyTable '; exec (@strCo...more >>

I have the query below running against a table with no indexes. This is the only query that ever runs against it so I was planning on creating a clustered index for it to optimize performance. The table does rarely receives inserts or updates but it is queried 10 or so times per second. Righ...more >>

Friends Config: Windows Server Enterprise 2003 clustered running SQL Server 2000 in mixed mode. Background: Trying to get a VB6 program to execute properly after being called from SQL Server trigger using xp_cmdshell. The VB6 code uses a winHTTP Request object and a X509 Digital Certifica...more >>

Hi all! I have a problem with a Extended Stored Procedure that calls a WebService...I'm using SOAP to call the WebService and I'm using an TokenManager that I have written myself...I have WSE 2.0 installed... It works fine when I'm calling it from my PC, but when I call it from the SQL test...more >>

I am not sure if this is possible but here is what i need. Is it possible to query the database and concatenate the results into a string? for instance if i said SELECT ORDER_ID FORM CUSTOMER_ORDERS WHERE CUSTOMER = 'CUSTOMER A' and it returned order1,order2,order3,order4 - the returned value...more >>

All, I am using a trigger to keep a "transaction date" column up-to-date with the datetime the record was lasted inserted/updated. My question is which SQL statement would provide better performance: Update tablename set transdate = getdate() where primarykey in (select primarykey f...more >>

Is there any way in SQL Server 2000 to create a temp table in an SP where the column datatype is specified as that of a column in an existing table? create table #Vend ( Name AP.dbo.VENDOR.Name ) OR create table #Vend ( Name myFunction('AP.dbo.VENDOR.Name') ) Thanks Timo ...more >>

...more >>

I have about 7 years of experience with MS SQL server, but now need to become proficient with Oracle as quickly as possible. This group has been a great help, so I'm wondering if anyone recommend a specific Oracle related newsgroup? I'm sure if I do a search, I can find hundreds, but I was ho...more >>

I get following error Server: Msg 8144, Level 16, State 2, Procedure pPMEmployeeUpdate, Line 0 [Microsoft][ODBC SQL Server Driver][SQL Server]Procedure or function pPMEmployeeUpdate has too many arguments specified. Any Ideas Why can it appear? Following are NOT the reason: 1. C# code is corr...more >>

Hi I have this query below, which I'm trying to add and group data on the week number (ISO week, taken from Books online) fn_getISOWeek returns an integer and works independently of this query. This query also works as I want when there is no weekno invloved (the 3 places) The error I get is ...more >>

Hi, How do I change the identity properties of an existing column using trans-sql? Thanks -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- ...more >>

Pardon my newbieness. I have a field like this: Region Northwest Northwest NULL Southeast Southwest Midwest Atlantic NULL Southwest I thought if the WHERE part of my query said: WHERE Region <> 'Midwest' I would get 8 records. However, I only get 6 because it leaves out the...more >>

Any ideas on where I could get a list of US working days per month? A list that excludes Sat and Sun, and excludes standard bank holidays (or US Govt holidays)? Thx ...more >>

Dear All We have a table with around 100000 records, this table contains an Int value that can be >= 1 <= 999999. Whenever we use the command Select sum(MYINT) from tblTEST We get the error 'Arithmetic overflow error converting expression to data type int.' I know that the result of...more >>

I have several stored procs that return two columns of data - primary key values and another column, eg. Categories, Regions. Used to populate select lists in HTML. Is there a way to pass a parameter to the proc that represents a specific primary key value and have the corresponsding record a...more >>

Hi, I am working on a .NET application (VB.NET). We had to restore our SQL server database from yesterday's backup as we accidentally overwrote some tables. After restorign, we are able to log to the database alright from SQL Query analyzer and Enterprise manager but when we try to access i...more >>

need to get a value from the table and increment it by 1- how to do this ? something to the effect of : Create Procedure "IncrementCount" ( @CounterFieldName nVarChar(10), @EventID int ) As UPDATE Events SET @CounterFieldName = 1+ (SELECT @CounterFieldName WHERE EventID = ...more >>

I am working on a database that will be set up for multiple languages, the data will be set up as utf-8 and I want to store this in a sql server 2000 database. I was wondering how do you set this up in sqlserver and so on. Any help would be appreciated. Thanks. -- J. D ...more >>