Performance Degrading Placing Join In WHERE Instead Of FROM Block (using =, =*, *=)

Jul 20, 2005

Hello folks,
first of all I really don't know how you gurus call this way of
writing joins:

SELECT
A.FIELD,
B.FIELD
FROM
TABLE_A A,
TABLE_B B
WHERE
A.ID_FIELD = B.ID_FIELD

I find this way very useful and readable. It works also with left and
right Joins (using *= or =* instead of = )

A friend of mine found that the inner join way (using = ) in Access is
much more slower than using the classic INNER JOIN TABLE ON FIELD
sintax. My question is: was MSSQL Server studied for using the short
way, or it is just a workaround found by someone? Is there a
performance degrade folllowing this way?

TIA,
tK

View 1 Replies


ADVERTISEMENT

Database Performance Degrading (again)

Jul 23, 2005

We have an application with a SQL Server 2000 back end that is fairlydatabase intensive -- lots of fairly frequent queries, inserts, updates-- the gamut. The application does not make use of performance hogslike cursors, but I know there are lots of ways the application couldbe made more efficient database-wise. The server code is running VB6of all things, using COM+ database interfaces. There are someclustered and non-clustered indexes defined, but I'm pretty surethere's room for improvement there as well.Some tables have grown into the millions of records in recent months,and performance of the application slowed to a crawl. Optimizing thedatabase helped a little, but not much. We know that several millionrecords in a table is a lot, but one would think that SQL Server shouldbe able to still handle that pretty well. We do have plans to archivea lot of old data, but in the meantime, we were hurting for a quickfix.So we threw hardware at the problem, and transferred the database to anew, more powerful server. The performance improvement was dramatic.Transactions were many many times faster than before. Withoutimplementing any of the other more difficult performance improvementswe have planned, we suddenly became minor heros. :-)Well, the honeymoon seems to be somewhat over. While performance isstill much better than when the database resided on our old server,performance appears to have degraded rather significantly again.Performance is also not significantly better with fewer users on oursystem. What the heck?Yes, the database continues to grow unchecked as we haven't quite gotan archive utility in place yet, but the growth is relatively gradual,so you wouldn't think that would be the issue. The database isoptimized on a weekly basis, and our web and database servers are bothrebooted monthly. Our database administrators don't seem to haveanswers, so I appeal to the experts reading this forum to maybe offersome clues.Prior to posting I did do a fair amount of research to see what peoplehave suggested in similar situations, and ran this by our databaseadmin. Here's what I can tell you from this research:- Statistics are updated weekly along with whatever else the databaseoptimization does- We do not use the "autoshrink" option for automatically shrinking logfiles- Regarding preallocating space and setting growth factors for log anddata files to minimize time spent allocating disk space, our adminsays, "We do allow database files to grow unchecked, but we do monitorgrowth and manually expand as needed. Autogrow is typically set in50MB increments or less as the amount of time it takes to expand thisamount is negligible."- Transaction logging is turned on, and data and log devices are onseparate physical disks- The database server is monitored to ensure no process is hogging allof the CPU, I/O or memory

View 6 Replies View Related

Stored Procedure Performance Degrading Dramatically When Accessed Through ADO.NET

Oct 2, 2007

Hi

I am having a problem with a particular stored procedure in a database application and I have ran out of ideas as to what is the cause. When calling this stored procedure from a .Net application it typically returns results in about 0.2 seconds. 24 hours after it's creation, the procedure takes over 40 seconds to return the same results to the application. However if I call the procedure via Management Studio or Query Analyzer, the performance remains consistently fast.

It's a fairly complicated query making use of the following features:


FOR XML EXPLICIT

The ROW_NUMBER function

Input Parameters

The procedure is replicated, along with the tables that it references
The calling application is using ExecuteXMLReader to retrieve the results.

To fix the problem, I can simply run an ALTER PROCEDURE statement (without changing any of the contents of the stored procedure). However, by the next morning, the problem will have reoccurred.

Can anyone shed any light on why this is happening?

Phil

View 9 Replies View Related

I Want To Save And Work With .sdf File On Premanent Storage Without Degrading Performance...

May 5, 2008



In my pocket pc inventory application i keep database .sdf file in My Documents. This gives me good performance. But the problem is that sometime my pocket pc hangs and some users cold boot the device and everything is lost.

I tried with keeping my .sdf file on SD card. This way data is there even after cold reboot, but the preformance is very bad. it takes some seconds just to insert only one record. Also if connection is not closed properly due to any reason, the database file is corrupted.

Can anyone tell me if there is a way to persist my .sdf file even after cold reboot without degrading the performance?

Regards.

View 12 Replies View Related

Having A Mental Block On An Exclusive Join

Feb 9, 2008

Hi, I've got three tables that I'm trying to pull data from. The first is a family of rings, the second is the individual rings and the third relates one ring to another:

RingFamilies
------------------------------------
FamilyID pk | FamName
------------------------------------

Rings
-------------------------------------------
RingID | FamilyID fk | RingName
-------------------------------------------

RingAssociations
-----------------------------------------
RingID1 pk/fk | RingID2 pk/fk
-----------------------------------------

I'm trying to pull a list of RingIDs and names for a given FamilyID. To complicate it, I want to exclude rings that are already associated to a given RingID, i.e. I only want the unassociated rings in a given family. To complicate it even a little more then name needs to be the FamName+RingName...is this possible? Thanks!

Given @FamID and @RingID
Result Table
------------------------------------------------
RingID | FamName + RingName
------------------------------------------------

View 4 Replies View Related

T-SQL (SS2K8) :: Why Block Scope Variables Exist Outside Of Block

Dec 3, 2014

I have the following stored procedure to test scope of variables

alter proc updatePrereq
@pcntr int,
@pmax int
as
begin

[Code] ....

In the above script @i is declare in the if block only when the @pcntr value is 1. Assume the above stored procedure is called 5 times from this script

declare @z int
set @z = 1
declare @max int
set @max = 5
while @z <= @max
begin
exec dbo.updatePrereq @z, @max
set @z = @z + 1
end
go

As i said earlier `@i` variable exists only when `@pcntr` is `1`. Therefore when i call the stored procedure for the second time and so forth the control cannot enter the if block therefore @i variable wouldn't even exist. But the script prints the value in `@i` in each iteration, How comes this is possible should it throw an error saying `@i` variable does not exist when `@pcntr` values is greater than `1`?

View 1 Replies View Related

Performance Between Standard Join And Inner Join

Jun 28, 2007

Hello, everyoneI have one question about the standard join and inner join, which oneis faster and more reliable? Can you recommend me to use? Please,explain me...ThanksChamnap

View 12 Replies View Related

Inner Join Performance

Mar 8, 2004

May sound stupid, but I need some advise. Will there be any performance difference between
select colx from tab1 inner join tab2 on tab2.col = tab1.col
and
select colx from tab1 inner join tab2 on tab1.col = tab2.col

I couldn't find any reference on this, but I see the second one used everywhere.
Thanks

View 1 Replies View Related

Performance On Inner Join

Apr 17, 2008

Guys,

what is the best possible way to optimize a query when u are inner joining two tables....

let's say ..We have one table with 100 rows and another with million rows...

Thanks

View 2 Replies View Related

INNER JOIN Query - Performance

Mar 13, 2006

Hi,

Please let me know the way to increase the performance of the below query :

SELECT DISTINCT a.* FROM a INNER JOIN #temp1 b on (a.col1 = b.col1 OR a.col1 IS NULL) INNER JOIN #temp2 c on (a.col2 = c.col1 OR a.col2 IS NULL)

Here, there are no indexes/pk on the columns in any table. But I am sure that the table #temp1 and #temp2 has distinct/unique values in columns col1 used here. The table 'a' has redandant values in its column used here.

Should I create pk on the columns for #temp1 and #temp2 used here. Is that enough ? Or should I also create index on the columns of the table 'a' used here.

Also please let me know is there anyother way to increase the performance of the query.

Please advice,
MiraJ

View 6 Replies View Related

Performance Issue In Join

May 10, 2006

Hi to all,

I am having two tables contains 6 lakhs record and 30,000 record respectively. i make a innner join between these two tables it take min 15 sec. i dint know wats the problem can any 1 help me.

Thanx in Advance.
Amjath

View 9 Replies View Related

How To Improve Performance If Inner Join Has More Than 2 Or 3 Tables

Aug 15, 2006

Hi everyone
     I need a solution for this query. It is working fine for 2 tables but when there are 1000's of records in each table and query has more than 2 tables. The process never ends.
Here is the query
(select siqPid= 1007, t1.Gmt909Time as GmtTime,(t1.engValue+t2.engValue+t3.engValue+t4.engValue) as EngValue,
 t1.Loc1Time as locTime,t1.msgId
 into #temp5
 from #temp1 as t1,#temp2 as t2,#temp3 as t3,#temp4 as t4
 where t1.Loc1Time = t2.Loc1Time and t2.Loc1Time = t3.Loc1Time and t3.Loc1Time = t4.Loc1Time)
 I was trying to do something with this query.
 
But the engValues cant be summed up. and if I add that in the query, the query isnt compiling.
(select siqPid= 1007, t1.Gmt909Time as GmtTime,
 t1.Loc1Time as locTime,t1.msgId,(t1.engValue+t2.engValue+t3.engValue+t4.engValue) as engValue
 --into #temp5
 from #temp1 as t1
 where exists
(Select 1
  from #temp2 as t2
 where t1.Loc1Time = t2.Loc1Time and
  exists
(Select 1
  from #temp3 as t3
 where t2.Loc1Time = t3.Loc1Time and
   exists
(Select 1
 from #temp4 as t4
 where t3.Loc1Time = t4.Loc1Time))))
 
 
I need immediate help on that, I would appreciate an input on it.
 
Thanks
-Sarah

View 15 Replies View Related

How To Improve Performance Of 'LEFT JOIN'

May 18, 2006

I am developing reporting service and using lots of 'LEFT OUTER JOIN',I am worried about the performance and want to use some subquery toimprovethe performance.Could I do that like below,[the origin source]SELECT *FROM TableALEFT OUTER JOIN TableBON TableA.item1 = TableB.item1WHERE TableA.item2 = 'xxxx'TableB.item2 > yyyy AND TableB.item2 < zzzzI add the subquery to query every table before 'LEFT JOIN'--------------------------------------------------------------------------SELECT *FROM(SELECT *FROM TableAWHERE TableA.item2 = 'xxxx') TableCLEFT OUTER JOIN(SELECT *FROM TableBWHERE TableB.item2 > yyyy AND TableB.item2 < zzzz) TableDON TableC.item1 = TableD.item1WHERE TableC.item2 = 'xxxx'TableD.item2 > yyyy AND TableD.item2 < zzzz--------------------------------------------------------------------------Can anyone give me some suggestion?Thanks a lot.Leland Huang

View 2 Replies View Related

Performance Problem With Merge Join

May 9, 2008

We are using SSIS to transfer data from OLTP database to DataWarehouse database on a daily basis. Our solution is modelled on / a copy of the (excellent) Project Real solution.

first stage of process, we extract the daily "records" into 4 stage files (raw file source)
Then we process these 4 files to populate our Fact and Dimension tables.

We are having a problem with the population of a couple of our dimension tables.
e.g, Comment dimension table - DimComment
Our aim is to only add records to the table that do not already exist

So
on "left side" of dataflow

1) read contents of stage file - just get commenttext column
2) use derived column component to add three columns (updatedby, lastupdated, ETLLoadID) and to Trim(commenttext)
3) use sort component to sort output - sort on commenttext and remove duplicates
on "right side" of dataflow

1) read the contents of the DimComments table


select ltrim(rtrim(CommentText)) as CommentText,
CommentKey
from DimComment
order by ltrim(rtrim(CommentText))
- remembering to set both the IsSorted and SortOrder properties for the component.


then we use a Merge Join component to merge the two dataflows. Within the Merge Join, we use a Left Outer Join so that we get all of the commenttext records from the daily stage file - which will have a CommentKey from the dimcomment table if there is a match (matching on comment text)
then we use a Conditional Split component to remove records from the data flow where the CommentKey is not null - i.e. we only want records that dont already exist onthe comment table.
finally, update DimComment table

Problem
Database table not being updated correctly - has duplicates
Problem appears to be with the Merge Join component.

From the existing records on the DimComment table, we get 1,943,309 records
From the daily stage file, we get 2,578 records - that after the sort (and duplicate removal) is reduced to 776 records
After the Merge Join, this is reduced to 771 records - only the first 5 records are matched - but it should be reduced by more....

if i amend the reading of the DimComment table to have the following clause

where (commenttext like '%automation comm%')
then it returns 8,347 rows - and 20 rows are matched - correct
if i amend the reading of the DimComment table to have the following clause

where (commenttext like '%automation com%')
then it returns 603,286 rows - and 358 rows are matched - correct
if i amend the reading of the DimComment table to have the following clause

where (commenttext like '%automation%')
then it returns 899,462 rows - and 0 rows are matched - incorrect
if i amend the reading of the DimComment table to have the following clause

where (commenttext like 'a%' or commenttext like 'b%'

or commenttext like 'c%' or commenttext like 'd%' or commenttext like 'e%')
then it returns 899,462 rows - and 29 rows are matched - incorrect - did most of them - but not all ???

in theory - if i run the process twice, i should get NO updates second time through - this is NOT the case !!

so
it would appear that in some cases, the Merge Join component is doing its merge join before it has got all of its records from the DimComment dimension table - matching appear to work on diffferent sets of records - as long as there are not too many records to process

Question 1
is it possible to correct this ?
Question 2
is there a limitation on the processing of the Merge Join component in number of records it can handle ? - if so, what is it ? - we have two other SSIS packages doing similar processing to this (but not as many records - YET)
(tablerow structure on dimensions tables is short - only 3-4 columns per row)
Question 3
might it be better to process the daily comments by after doing the sort, doing a lookup on the DimComment table - and then inserting into the DimComment table if there is no match found

FYI
above details got from running app on my PC on local database (SQL2005) - database is a copy of production (couple of weeks old)


tia

View 3 Replies View Related

Merge Join's Poor Performance

Sep 4, 2006

Hello All,

I'm experiencing performance problems with the merge join task.
Every time I'm building a nice package using this task, I'm ending up deleting it and using SQL statement in the OLE DB source to accomplish the join since it takes forever to run and crushing my computer at the process.
It makes me feel I don't use the abilities SSIS has to offer compared to DTS.
Of course for the use of several thousands of records it works fine, but in a production surrounding with hundred of thousands of rows, it seems to be futile.

Maybe someone had a little more luck with it?



View 7 Replies View Related

How To Improve Performance With A Join Between 2 Table From 2 SQL Servers

Aug 18, 2006

I am making a ASP.NET web application that involves 2 SQL Server(A & B).
I created a view in SQL server A pointing to the table in SQL Server B. I found out my application will run REALLY slow when accessing such a view. so I try to avoid using them. But in the case of 2 table joining from 2 different SQL Servers, I have no choice.
Can anyone help me with this?
Thanks!

View 4 Replies View Related

Left Outer JOIN Proceducing Very Slow Performance...

Oct 28, 2007

Hi,

I'm executing a nested queries consisting of LEFT OUTER JOIN for:
Duration = 27 sec;
Reads = 1690;
Number of users = 1.

This is extremely slow for a small set of data. I'm afraid that when we have a larger dataset and more concurrent users that query will take forward.

What am I doing wrong for my query to be taking too long?

Here's the query:


SELECT B.Business_Id as Business1_0_,

B.Place_Of_Business_Id as Place2_30_0_,

B.Business_Type_Id as Business3_30_0_,

B.Business_Name as Business4_30_0_,

B.Business_Description as Business5_30_0_,

B.Last_Update_Timestamp as Last6_30_0_

FROM Busines as B

LEFT OUTER JOIN Business_Service as BS

ON B.Business_Id = BS.Business_Id

LEFT OUTER JOIN Business_Service_Category as BSC

ON B.Business_Id = BSC.Business_Id

LEFT OUTER JOIN Business_Sub_Category as BSSC

ON B.Business_Id = BSSC.Business_Id

where B.Business_Id IN (

Select B.Business_Id from Busines as B

JOIN Business_Address as BA ON B.Business_Id = BA.Business_Id

JOIN Address as A ON A.Address_Id = BA.Address_Id

WHERE A.City_Name like '%New York%'

and A.state = 'NY') AND ( FREETEXT(B.Business_Name, 'Designer')

or BSC.Service_Category_Id IN

( Select SC.Service_Category_Id from Service_Category as SC

where FREETEXT(SC.Service_Category_Name, 'Designer') )

or BSSC.Sub_Category_Id IN (

Select SSC.Service_Sub_Category_Id from Service_Sub_Category as SSC

where FREETEXT(SSC.Service_Sub_Category_Name, 'Designer') )

or BS.Service_Id IN (

Select S.Service_Id from Service as S

where FREETEXT(S.Service_Name, 'Designer') ) )

----------------------------------------------------------------------------------------------------------------------------------------
Here's the SQL Query Plan:

|--Nested Loops(Left Semi Join, OUTER REFERENCES.[Business_Id], [BS].[Service_Id], [BSC].[Service_Category_Id], [BSSC].[Sub_Category_Id]))
|--Nested Loops(Left Outer Join, WHERE[PB].[dbo].[Busines].[Business_Id] as .[Business_Id]=[PB].[dbo].[Business_Sub_Category].[Business_Id] as [BSSC].[Business_Id]))
| |--Nested Loops(Left Outer Join, WHERE[PB].[dbo].[Busines].[Business_Id] as .[Business_Id]=[PB].[dbo].[Business_Service_Category].[Business_Id] as [BSC].[Business_Id]))
| | |--Nested Loops(Left Outer Join, WHERE[PB].[dbo].[Busines].[Business_Id] as .[Business_Id]=[PB].[dbo].[Business_Service].[Business_Id] as [BS].[Business_Id]))
| | | |--Nested Loops(Inner Join, OUTER REFERENCES.[Business_Id]))
| | | | |--Sort(DISTINCT ORDER BY.[Business_Id] ASC))
| | | | | |--Nested Loops(Inner Join, OUTER REFERENCES[BA].[Business_Id]))
| | | | | |--Hash Match(Inner Join, HASH.[Address_Id])=([BA].[Address_Id]), RESIDUAL[PB].[dbo].[Address].[Address_Id] as .[Address_Id]=[PB].[dbo].[Business_Address].[Address_Id] as [BA].[Address_Id]))
| | | | | | |--Clustered Index Scan(OBJECT[PB].[dbo].[Address].[PK__Address__0519C6AF] AS ), WHERE[PB].[dbo].[Address].[State] as .[State]='NY' AND [PB].[dbo].[Address].[City_Name] as .[City_Name] like '%JayVille%'))
| | | | | | |--Index Scan(OBJECT[PB].[dbo].[Business_Address].[ClusteredIDX_Business_Address] AS [BA]))
| | | | | |--Index Seek(OBJECT[PB].[dbo].[Busines].[UI_ukBusiness] AS ), SEEK.[Business_Id]=[PB].[dbo].[Business_Address].[Business_Id] as [BA].[Business_Id]) ORDERED FORWARD)
| | | | |--Clustered Index Seek(OBJECT[PB].[dbo].[Busines].[PK_Busines] AS ), SEEK.[Business_Id]=[PB].[dbo].[Busines].[Business_Id] as .[Business_Id]) ORDERED FORWARD)
| | | |--Clustered Index Scan(OBJECT[PB].[dbo].[Business_Service].[ClusterIDX_Business_Service] AS [BS]))
| | |--Clustered Index Scan(OBJECT[PB].[dbo].[Business_Service_Category].[ClusterIDX_Business_Service_Category] AS [BSC]))
| |--Clustered Index Scan(OBJECT[PB].[dbo].[Business_Sub_Category].[ClusterIDX_Business_Sub_Category] AS [BSSC]))
|--Concatenation
|--Filter(WHERE[PB].[dbo].[Busines].[Business_Id] as .[Business_Id] = [Full-text Search Engine].[KEY]))
| |--Remote Scan(OBJECTFREETEXT))
|--Nested Loops(Left Semi Join)
| |--Clustered Index Seek(OBJECT[PB].[dbo].[Service_Category].[PK__Service_Category__15502E78] AS [SC]), SEEK[SC].[Service_Category_Id]=[PB].[dbo].[Business_Service_Category].[Service_Category_Id] as [BSC].[Service_Category_Id]) ORDERED FORWARD)
| |--Filter(WHERE[Full-text Search Engine].[KEY] = [PB].[dbo].[Business_Service_Category].[Service_Category_Id] as [BSC].[Service_Category_Id]))
| |--Remote Scan(OBJECTFREETEXT))
|--Nested Loops(Left Semi Join)
| |--Clustered Index Seek(OBJECT[PB].[dbo].[Service_Sub_Category].[PK_Service_Sub_Category] AS [SSC]), SEEK[SSC].[Service_Sub_Category_Id]=[PB].[dbo].[Business_Sub_Category].[Sub_Category_Id] as [BSSC].[Sub_Category_Id]) ORDERED FORWARD)
| |--Filter(WHERE[Full-text Search Engine].[KEY] = [PB].[dbo].[Business_Sub_Category].[Sub_Category_Id] as [BSSC].[Sub_Category_Id]))
| |--Remote Scan(OBJECTFREETEXT))
|--Nested Loops(Left Semi Join)
|--Clustered Index Seek(OBJECT[PB].[dbo].[Service].[PK__Service__117F9D94] AS ), SEEK.[Service_Id]=[PB].[dbo].[Business_Service].[Service_Id] as [BS].[Service_Id]) ORDERED FORWARD)
|--Filter(WHERE[Full-text Search Engine].[KEY] = [PB].[dbo].[Business_Service].[Service_Id] as [BS].[Service_Id]))
|--Remote Scan(OBJECTFREETEXT))


Please Help....



View 2 Replies View Related

Query Performance Problems With Join On UDF-based Computed Column

Aug 25, 2007

We have a table with a couple of computed columns. The value of the computed column represents a foreign key reference into another table. We're seeing a major performance problem doing a query joining between the two tables with one of the columns, but not the other. In other words, this kind of query is very fast:

select * from TheTable A, FKeyTable B
where A.ComputedColumn1 = B.KeyColumn

but this one sends the CPU usage of SQL Server to 99% for a very long time:

select * from TheTable A, FKeyTable B
where A.ComputedColumn2 = B.KeyColumn

The main difference we can see that the computed column that causes problems is based on a UDF, and the other one isn't (but again, both are computed). When I look at the execution plan, the slow query shows a Nested Loop (Inner Join) with a "No Join Predicate" warning, with the estimated # of rows being 70 million (which correponds to the product of 1016 rows in TheTable and 69K rows in FKeyTable). The fast query doesn't have that warning, and shows 1016 rows (the # of rows in TheTable).

Does anyone know why the usage of a UDF would induce this horribly inefficient join behavior? Anything we can do to fix it?

This is SQL Server 2005 SP2, btw.

View 15 Replies View Related

Performance Related Doubt On The Following Items --&&> LEFT/INNER JOIN Vs IN And Cost Of CONVERT

May 28, 2008

Hi,

1.
Right now in my queries I am using lots of LEFT Joins and INNER JOINs... and I was suggested to look at 'IN'... But with IN I did face some performance issues previously and stopped using it... but I have got new doubts on which query will give me better performance...

A query using LEFTJoin or a query using IN/NOT-IN


2.
This question is about CONVERT...

I have a stored proc which is used for updating a table... and multiple columns [of the same table] and corresponding values are sent to the proc [only a subset of the columns might be sent for updates everytime and the columns to update is not fixed for each run of the SP]...

I have to construct a UPDATE String out of it using string concatenation to finally be able to use "sys.sp_executesql" on that update statement...

This results in me having to use CONVERT() lots of times... and one of the columns among them on which I am doing a CONVERT is of the type XML...

So the question is as follows...
a. Is it preferrable to construct a single UPDATE statement string and execute it using "sys.sp_executesql"
b. Or Is it preferrable to give multiple UPDATE statments... i.e. one update statement for each column [Depending on whether that column has to be updated for that run or not]


i.e. The question essentially is:

Does a single update query constructed using lots of CONVERTS [Basically on INT and XML types]
give more performance over using multiple UPDATE statments on the table
Or is it the other way round..

Thanks,
Pratap.

View 5 Replies View Related

How Do I Go About Placing Photographs In SQL?

May 30, 2007

I have SQL 2005 Server and Visual Studio 2005 and I am working in vb. I want to create a database which has photographs.

View 3 Replies View Related

Placing A Formula

May 19, 2008



Hi everyone, I have a small dilema here. I am running SSRS 2000, and I have a report that is grouped by Part Number. I have =Fields!Formula.Value < .1 so I can view only what is greater than .1 Well, my problem is that when I put it under the group Visibility properties, it does not pull all the data. and if i put it under detail Visibility. It does show me everything but any Part Number that is less than .1 shows just the part number but no detail. How can I make my report show all my data but without leaving data out????

Thanks ahead of time,

Abner

View 1 Replies View Related

Placing Sp_executesql Data Into A Var

Jan 27, 2004

I have a stored procedure that takes various parameters and performs simple selects using these. A quick summary of the db structure this is working on would be :

lookup_table1
table1_id
description

lookup_table2
table2_id
description

This stored procedure attempts to select the given description from the given table and return the id, if the id isnt present it then inserts the new value and returns the new id.

Now I have this working, ish, but the problem that I have is that in the case of a new insert the procedure returns two datasets, an empty one (as the initial select returned no results) and another with the id of the newly inserted value.

This procedure as it stands right now


CREATE PROCEDURE aida_lookup
@lookup_table varchar(255),
@lookup_id_name varchar(255),
@lookup_description_col varchar(255),
@lookup_value varchar(255)
AS

declare @sql nvarchar(2048)

--
-- Check if the given lookup value exists in the give table / column
--
set @sql ='SELECT ' + @lookup_id_name + ' FROM ' + @lookup_table + ' WHERE ' + @lookup_description_col + ' = ''' + @lookup_value + ''''
EXECUTE sp_executesql @sql
if (@@rowcount = 0) goto new_value
if (@@error <> 0) goto on_error
return(1)

--
-- Insert new lookup value into the given table / column
--
new_value:
-- NEED TO CLEAR PREVIOUS NULL SELECT
begin transaction
set @sql = 'INSERT INTO ' + @lookup_table + ' (' + @lookup_description_col + ') VALUES (''' + @lookup_value + '''); SELECT SCOPE_IDENTITY()'
execute sp_executesql @sql
if (@@error <> 0) goto on_error
commit transaction
return(1)

--
-- Error handler
--
on_error:
rollback transaction
return (0)
GO



The problem being is that if the value is inserted as a new value I need to remove the empty dataset so that regardless of how the procedure is run, it will always return the id at position row 0 column 0.

My attempted solution was to declare a @temp var and place the return value of sp_executesql into this, and if it wasnt null then return it, and if it was then proceed to insert, e.g.


declare @temp int
...
SET @temp = EXECUTE sp_executesql @SQL
if (@@temp = null) goto new_value
SELECT @temp
return(1)


Obviously this doesnt work, so I am open to suggestions. To be completly honest I have run out of hair to rip out and I am sure this can probably be done in a much more elegant fashion, so any help will be greatly appreciated

View 11 Replies View Related

Placing A Value In NULL Column

Mar 7, 2008

Do anyone know how I can automatically place a value eg. €śNOTHING€? in a database column when there is no or has a NULL value. Thanks.

View 2 Replies View Related

Placing A New Line Character

May 29, 2007

hi all

i have a filed in my database with name address which contains addresses.

i want to split address in multiple line .

LIke



addressline1

addressline2

addressline3



i.e

Robart Peter

4th Banglow road

MC city



can any one help me.

View 4 Replies View Related

Placing A LOCk On The Table In SqlServer2005

Apr 17, 2008

Hi,
 I am using Sql Server 2005.
I want to place a LOCK, insert something in the table then return back to the code (with the ID of the inserted item) and then call FUNCTION_A and then based on the outcome of the function I either want to

I want to keep the inserted item and release the lock or
I want to delete the inserted item and release the lock
Any suggestion is greatly appreciated.
Regards
naimulah

View 3 Replies View Related

Placing Tempdb BACK On Disk

Jul 23, 1998

Our development server is limited on RAM and I have been asked to increase Tempdb. I made the decision to put it back on disk; something I have done dozens of times before. I have used the Enterprise Manager`s GUI interface and I have manually typed in the SQL using ISQL/w, both ways have resulted in the same error message.

Microsoft SQL-DMO
Error 5016: [SQL Server] Incorrect database name or device name(s).

I have dropped the old and created new database devices (with different names) and I still receive the error message. I have even tried rebooting with Tempdb back in RAM and on disk 1t 2MB (which works). And yes, I HAVE checked my typing.

I would GREATLY appreciate any ideas on what may be wrong. The system is 6.5 with SP4 on Win NT with SP3. I inherited the system, so I am not certain what the hisory is.

Thanks,
Mike Gaudet
Visages, Inc.

View 3 Replies View Related

Placing Data And Log Files Seperately

Mar 4, 2004

Hi, Gurus, I am running SQL-Server 2000 on SCSIIs with Raid-5. Three disks. I see only one Logical Disk with 3 partitions. Since it's recommended to have Log files seperate to Data files physicially, currently both exist in the D-Drive. How can it be accomplished?

Another question for ur kind suggestion. I have a merge-repl setup. Publisher and distributor at the same machine. One subscriber over the internet. Replication works through VPN rather that FTP. Whenever i apply SNAPSHOT it takes more that 12 hrs to replicate the 2 GB DB. Although VPN is more secured but the tunneling makes the replication rather slow i guess. Will there be a major difference if i open the default port of ftp on the ISA and change the subscriber to get the SNAPSHOT as Anonymous Subsciption. I mean What's better, Security VS Performance. During the SNAPSHOT the Web-users running on the Subscriber wait for the latest data which is very much annoying.

Another question please, I wanna replace the current publisher-DB machine with a new Hardware-Machine. I don't wanna lose Replication and re-running the SNAPSHOT. Is it possible to retain the publication by restoring the Master, Publication and Distributor respectively on the new machine. The new machine will have the same Operating system name and configuration.

Regards!!

View 3 Replies View Related

Placing A Web Link In An Email Body

Nov 28, 2007

I'm generating emails using sp_send_dbmail. Everything works perfectly except for one thing. In the body of the email I need to show a link to a web page (eg http://myweb/login.aspx).

The problem is that the received email shows the "link" as plain text, ie it is not a clickable link. I've tried adding char(13) (and char(10) and both) after the link text but that doesn't help.

Is there a way to make the link text a real link when received by Outlook? (All recipients will be using Outlook if that helps).

Thanks,

John

View 6 Replies View Related

Placing Ssis Packages On Server

Apr 3, 2007

I have created my packages and i want them to place them on the server.Do i need to place the entire project of dts packages on the server or is there any option to place executables...if so please explain....



And to run these packages on the server do i need to set them as new job at sql server agent or is there any other way i need to run on the server.



I want then to run whenever the text file gets updated is it possible to set anything for my packages to run as and when the text file gets updated..



Please help me with all my questions

Thanks in advance..



View 5 Replies View Related

Placing A Sum In A Table Header Column

Jan 30, 2007

I have been requested to add the sum of an interger field to the table header. I have the sum in the footer (which is very easy to do), but I cannot get the sum to appear in the table header.

I then set-up the stored procedure to run the sum, and place it into a dummy field. I still cannot add this field to the table header. Instead of printing the data for the dummy field (the correct total), it instead prints the actual field name on the report.

Is there anyway to place a sum in a table header on a SQL Server Report?

Let me know.

Thank you,

T.J.

View 7 Replies View Related

Placing Textboxes In Headers Of Reports

Jan 4, 2008

Hi,

After exporting the report the column headers in the rreport should not scroll, it has to fix with report headers. If i move the column headers to report header the columns are merging in excel, some columns are hiding.

For ex: I have 10 columns, it has to take from A to K but it is taking A to P. one column is occupying two columns. how to avoid this merging and how to place the column headers in the report header.

Thanks
Dinesh

View 4 Replies View Related

Placing A Zero In A Reporting Services Report

Jan 29, 2008



I have a define formula that is SUM(Loan Loan Amount[2]) and this formula has a addition filter off of it.


The report is pulling off of calander months months so if in some of the fields it has a dollar amount but in others it is blank. Trying to write formula that if there is no amount then place zero otherwise put the $ amount.


thanks

View 1 Replies View Related

Selecting And Placing Values From Sql Query Into Textboxes

Jan 9, 2008

Hi. I have an sql query that i use to insert articles to a sql databse table and for that i use addWithValue to select witch textboxes etc goes where in the database.
Now i need to retrive these values and place them back into some other textboxes (some of them multiline) ,and i wonder if there are any similar ways to retrive values like AddWithparameter so i can easily do textBox.text = //whatever goes here ?

View 4 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved