Check if temporary table exists

Hello.

How can I check if a temporary table exists in the current context?

With normal tables I'd do a

EXISTS ( SELECT name FROM sysobjects

WHERE name='myTableName' AND type='U')

However, I can't do that with a temporary table. I'd have to go look at the sysobjects table in the tempdb database.

The problem is that for temporary tables, a suffix is added to the name to make it unique for each scope. I can't change the WHERE clause to name LIKE 'myTableName%' because this would return true if a temporary table with the same name exists in a different scope.

Any ideas?

Carlos

You can try the following

IF object_id('tempdb..#MyTempTable') IS NOT NULL
BEGIN
DROP TABLE #MyTempTable
END

CREATE TABLE #MyTempTable
(
ID int IDENTITY(1,1),
SomeValue varchar(100)
)
GO

|||IF OBJECT_ID('tempdb..#MyTempTable', 'U') IS NOT NULL
Print 'Yes'
Else
Print 'No'sql

Check if substring exists within string

I am building a string of the form:

FirstName LastName, FirstName LastName, FirstName LastName,...

and I would like to only add the current name if it isn't already in the string.

The reason I am doing this is because the table I'm working on has multiple entries for the same key but with different values for one of the columns.

The problem is I can't figure out how to check if current name already exists in the name list string that was previously built.

I appreciate your replies. Thank you.

The direct answer is,

CharIndex('Your Searching String', 'Your Main string)

If the result is NON ZERO Then the value is already exists.

Example,

Select Case When CharIndex('Three Four','One Two,Two Three,Three Four') <> 0 Then 'Yes' Else 'No' End --Yes

Select Case When CharIndex('Three Five','One Two,Two Three,Three Four') <> 0 Then 'Yes' Else 'No' End --No

NOTE:

But it is really bad idea to keep the multiple entry values as columns.

Better you can have a one more table where you can make a new row for each names.

The design which you are using is not recommended.

|||

I am pulling data from several tables and in one of them for some reason ( it might be a bug in how that table is generated or in how inserts are being made) I have multiple values for the same column for the same key. Which is generating multiple rows in my query. I cannot change that design. I can only work with it.

Thanks for your answer.

|||hi you can try this options..

SELECT *
INTO #Names
FROM (
SELECT 'John' AS FirstName, 'Doe' AS LastName, 1 AS OtherColumn UNION ALL
SELECT 'John' AS FirstName, 'Doe' AS LastName, 2 AS OtherColumn UNION ALL
SELECT 'Rhamille' AS FirstName, 'Golimlim' AS LastName, 3 AS OtherColumn UNION ALL
SELECT 'Rhamille' AS FirstName, 'Golimlim' AS LastName, 4 AS OtherColumn UNION ALL
SELECT 'Princess Quamille' AS FirstName, 'Golimlim' AS LastName, 4 AS OtherColumn
) Names

SELECT *
FROM #Names

-- option 1
DECLARE @.Names varchar(100)

SET @.Names = ''

SELECT @.Names = @.Names + FirstName + ' ' + LastName + ', '
FROM (
SELECT DISTINCT
FirstName
, LastName
FROM #Names
) Names
ORDER BY
FirstName
, LastName

SET @.Names = LEFT(@.Names,LEN(@.Names) - 1)

-- option 2
DECLARE @.Names2 varchar(100)

SET @.Names2 = ''

SELECT @.Names2 = @.Names2 + (CASE WHEN CHARINDEX(FirstName + ' ' + LastName, @.Names2) > 0 THEN '' ELSE FirstName + ' ' + LastName + ', ' END)
FROM #Names
ORDER BY
FirstName
, LastName

SET @.Names2 = LEFT(@.Names2,LEN(@.Names2) - 1)

SELECT @.Names as FromOption1, @.Names2 AS FromOption2

DROP TABLE #Names

check how many connections

Hi, how to check how many connections current be used?
Thanks,
sp_who2 will give you a rough idea. Any unique SPID above 50 is essentially
a different connection. There is a SQL Server perfmon counter for
connections as well.
Andrew J. Kelly SQL MVP
<js@.someone.com> wrote in message
news:e3$kLxzQHHA.1016@.TK2MSFTNGP04.phx.gbl...
> Hi, how to check how many connections current be used?
> Thanks,
>
|||Hello,
For SQL 2005, use the below dynamic management views to query connections
and sessions.
sys.dm_exec_connections
sys.dm_exec_sessions
Read the below article to know the usage and details. Please rate the
article if found useful.
http://www.codeproject.com/useritems/Dynamic_Management_Views.asp
Thanks
Hari
<js@.someone.com> wrote in message
news:e3$kLxzQHHA.1016@.TK2MSFTNGP04.phx.gbl...
> Hi, how to check how many connections current be used?
> Thanks,
>

check how many connections

Hi, how to check how many connections current be used?
Thanks,sp_who2 will give you a rough idea. Any unique SPID above 50 is essentially
a different connection. There is a SQL Server perfmon counter for
connections as well.
Andrew J. Kelly SQL MVP
<js@.someone.com> wrote in message
news:e3$kLxzQHHA.1016@.TK2MSFTNGP04.phx.gbl...
> Hi, how to check how many connections current be used?
> Thanks,
>|||Hello,
For SQL 2005, use the below dynamic management views to query connections
and sessions.
sys.dm_exec_connections
sys.dm_exec_sessions
Read the below article to know the usage and details. Please rate the
article if found useful.
http://www.codeproject.com/useritem...ement_Views.asp
Thanks
Hari
<js@.someone.com> wrote in message
news:e3$kLxzQHHA.1016@.TK2MSFTNGP04.phx.gbl...
> Hi, how to check how many connections current be used?
> Thanks,
>sql

Check current database

I was wondering if it was possible to check what server database you are currently connected to, using T-SQL, when executing commands in Query Analyzer. Even though you choose the server and database when connection, sometimes by habit you may connect to the wrong server & DB, and execute an .SQL file. Is it possible to put a line of T-SQL at the beginning that performs this pseudo-code, as a safety feature:

-- While in Query Analyzer with a certain .SQL file open:
If current server <> 'TheCorrectServer' and current DB <> 'TheCorrectDB' then cancel this .SQL file execution.Hi there

In Query analyzer type the following to get server name

select @.@.servername

Unsure how to get database name, you could just always put;

use <databasename>

in your code, that way you would always be on the right database|||SELECT db_name()

returns the currently active database|||Thanks a lot! That will help.

Chart with 3 year series

Hello
I have a dataset with a date field and a value field. I wish to display the
value fields for current year as well as the previous 2 years together with
different colours on the same month interval on the X-axis. Can someone lend
a hand on how to do this?
thanks in advance
Chris PulfordExample is attached below. The basic idea is to group by months on the
categories and group by year on the series.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
<?xml version="1.0" encoding="utf-8"?>
<Report
xmlns="http://schemas.microsoft.com/sqlserver/reporting/2003/10/reportdefini
tion"
xmlns:rd="">http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
<RightMargin>1in</RightMargin>
<Body>
<ReportItems>
<Chart Name="Sales">
<ThreeDProperties>
<Rotation>30</Rotation>
<Inclination>30</Inclination>
<Shading>Simple</Shading>
<WallThickness>50</WallThickness>
</ThreeDProperties>
<Style />
<Legend>
<Visible>true</Visible>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<Color>Brown</Color>
</Style>
<Position>RightCenter</Position>
</Legend>
<Palette>Pastel</Palette>
<ChartData>
<ChartSeries>
<DataPoints>
<DataPoint>
<DataValues>
<DataValue>
<Value>=Sum(Fields!UnitPrice.Value *
Fields!Quantity.Value)</Value>
</DataValue>
</DataValues>
<DataLabel />
<Style>
<BorderWidth>
<Default>6pt</Default>
</BorderWidth>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
</Style>
<Marker />
</DataPoint>
</DataPoints>
</ChartSeries>
</ChartData>
<CategoryAxis>
<Axis>
<Title>
<Style />
</Title>
<Style />
<MajorGridLines>
<ShowGridLines>true</ShowGridLines>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
</Style>
</MajorGridLines>
<MinorGridLines>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
</Style>
</MinorGridLines>
<MajorTickMarks>Outside</MajorTickMarks>
<Visible>true</Visible>
</Axis>
</CategoryAxis>
<DataSetName>Northwind</DataSetName>
<PointWidth>100</PointWidth>
<Type>Line</Type>
<Title>
<Caption>Sales</Caption>
<Style>
<FontSize>14pt</FontSize>
<FontWeight>700</FontWeight>
</Style>
</Title>
<CategoryGroupings>
<CategoryGrouping>
<DynamicCategories>
<Grouping Name="Sales_CategoryGroup1">
<GroupExpressions>
<GroupExpression>=Month(Fields!OrderDate.Value)</GroupExpression>
</GroupExpressions>
</Grouping>
<Sorting>
<SortBy>
<SortExpression>=Month(Fields!OrderDate.Value)</SortExpression>
<Direction>Ascending</Direction>
</SortBy>
</Sorting>
<Label>=MonthName(Month(Fields!OrderDate.Value))</Label>
</DynamicCategories>
</CategoryGrouping>
</CategoryGroupings>
<Height>6.125in</Height>
<SeriesGroupings>
<SeriesGrouping>
<DynamicSeries>
<Grouping Name="Sales_SeriesGroup1">
<GroupExpressions>
<GroupExpression>=Year(Fields!OrderDate.Value)</GroupExpression>
</GroupExpressions>
</Grouping>
<Label />
</DynamicSeries>
</SeriesGrouping>
<SeriesGrouping>
<StaticSeries>
<StaticMember>
<Label>Sales</Label>
</StaticMember>
</StaticSeries>
</SeriesGrouping>
</SeriesGroupings>
<Subtype>Plain</Subtype>
<PlotArea>
<Style>
<BackgroundGradientEndColor>White</BackgroundGradientEndColor>
<BackgroundGradientType>TopBottom</BackgroundGradientType>
<BackgroundColor>LightGrey</BackgroundColor>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
</Style>
</PlotArea>
<ValueAxis>
<Axis>
<Title>
<Style />
</Title>
<Style />
<MajorGridLines>
<ShowGridLines>true</ShowGridLines>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
</Style>
</MajorGridLines>
<MinorGridLines>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
</Style>
</MinorGridLines>
<MajorTickMarks>Outside</MajorTickMarks>
<MinorTickMarks>Outside</MinorTickMarks>
<Min>0</Min>
<Margin>true</Margin>
<Visible>true</Visible>
<Scalar>true</Scalar>
</Axis>
</ValueAxis>
</Chart>
</ReportItems>
<Style />
<Height>6.5in</Height>
</Body>
<TopMargin>1in</TopMargin>
<DataSources>
<DataSource Name="Northwind">
<rd:DataSourceID>da5964d0-11a7-4e51-9b22-cc4fa55fdd7a</rd:DataSourceID>
<ConnectionProperties>
<DataProvider>SQL</DataProvider>
<ConnectString>data source=(local);initial
catalog=Northwind</ConnectString>
<IntegratedSecurity>true</IntegratedSecurity>
</ConnectionProperties>
</DataSource>
</DataSources>
<Width>6.5in</Width>
<DataSets>
<DataSet Name="Northwind">
<Fields>
<Field Name="UnitPrice">
<DataField>UnitPrice</DataField>
<rd:TypeName>System.Decimal</rd:TypeName>
</Field>
<Field Name="Quantity">
<DataField>Quantity</DataField>
<rd:TypeName>System.Int16</rd:TypeName>
</Field>
<Field Name="OrderDate">
<DataField>OrderDate</DataField>
<rd:TypeName>System.DateTime</rd:TypeName>
</Field>
</Fields>
<Query>
<DataSourceName>Northwind</DataSourceName>
<CommandText>SELECT [Order Details].UnitPrice, [Order
Details].Quantity, Orders.OrderDate
FROM Orders INNER JOIN
[Order Details] ON Orders.OrderID = [Order
Details].OrderID</CommandText>
<Timeout>30</Timeout>
</Query>
</DataSet>
</DataSets>
<LeftMargin>1in</LeftMargin>
<rd:SnapToGrid>true</rd:SnapToGrid>
<rd:DrawGrid>true</rd:DrawGrid>
<rd:ReportID>bc811835-2302-4f9e-9c89-a99d4d3f5fd2</rd:ReportID>
<BottomMargin>1in</BottomMargin>
</Report>