No info in diagramed tables

I ran the diagram tool in EM SQL 7.0 and the table appears but nothing else is visible. If you check properties everything is grayed out. Select tables with Joins and all the tables show but again no column names, key or joins visible. I opened the diagram in Northwind and it appears correctly. I must have missed something, hope someone knows what.

Thanks in advance.
KayteeHOriginally posted by Kayteeh
I ran the diagram tool in EM SQL 7.0 and the table appears but nothing else is visible. If you check properties everything is grayed out. Select tables with Joins and all the tables show but again no column names, key or joins visible. I opened the diagram in Northwind and it appears correctly. I must have missed something, hope someone knows what.

Thanks in advance.
KayteeH

Can you right click on the table and get a menu that has an option called table view?|||could it be that you're missing permissions?|||Originally posted by Brett Kaiser
Can you right click on the table and get a menu that has an option called table view?

No - I don't see a menu that says table view.|||Originally posted by ms_sql_dba
could it be that you're missing permissions?

That's what I thought, but I am dbo. I can use the diagram feature in other databases I own. Just not this particular database.

No Indexes - Linking to MS Access

Hello,

I am using MS Access 2000 as a front end to a SQL Server 2000 db. When I link to the SQL Server db using a DSN, the SQL Server table pk's and indexes are not coming over with the link. The pk's and indexes do exist in SQL Server. I haven't had to link to these tables in over a year, but they used to link just fine.

Do you have a suggestion as to where to look at this problem?

Thanks you - Darryle

Please define the problem. Are you trying to “design” SQL Server tables form Access? If there are indices in SQL Server, it will take advantage of them when you send queries. You can link to the sysindexes table in your database, and you can query what indices you have in SQL Server for that database.

Zlatko

|||

It has been a while since I have done this type of linking. But it seemed that when I used to link to SQL Server from Access, the linking process would automatically figure out what the primary key was and also see the other indexes on the SQL table. I have an old Acccess front end, when I look at the link tables in design mode (I know I can't change it, just looking) I see the pk's and the indexes.

It seems that now when I link a table from Access, it asks me to pick the pk column, which I do. Once I have linked and I look at the linked table in design mode in Access, I see the pk but none of the indexes show. These are the same tables that had linked to before. Is this a problem? Will the indexes set up in SQL Server be used in Access even though I don't see them?

Thanks for you help and I'll be glad to give you any further information - Darryle

|||

Your question is entirely related to Access and not to SQL Server. Unfortunately I don’t see a thread dedicated to Access programming. You may want to try http://asp.net.

Consider “pass through” queries. They send a native SQL statement to the linked store, and receive the final result – all the processing is done at the server. In that case you don’t care about indices being replicated. Normal Access queries perform the processing in Access, and they need indices but the approach of using SQL Server only for storage and not for processing is near useless.

sql

No Indexes - Linking to MS Access

Hello,

I am using MS Access 2000 as a front end to a SQL Server 2000 db. When I link to the SQL Server db using a DSN, the SQL Server table pk's and indexes are not coming over with the link. The pk's and indexes do exist in SQL Server. I haven't had to link to these tables in over a year, but they used to link just fine.

Do you have a suggestion as to where to look at this problem?

Thanks you - Darryle

Please define the problem. Are you trying to “design” SQL Server tables form Access? If there are indices in SQL Server, it will take advantage of them when you send queries. You can link to the sysindexes table in your database, and you can query what indices you have in SQL Server for that database.

Zlatko

|||

It has been a while since I have done this type of linking. But it seemed that when I used to link to SQL Server from Access, the linking process would automatically figure out what the primary key was and also see the other indexes on the SQL table. I have an old Acccess front end, when I look at the link tables in design mode (I know I can't change it, just looking) I see the pk's and the indexes.

It seems that now when I link a table from Access, it asks me to pick the pk column, which I do. Once I have linked and I look at the linked table in design mode in Access, I see the pk but none of the indexes show. These are the same tables that had linked to before. Is this a problem? Will the indexes set up in SQL Server be used in Access even though I don't see them?

Thanks for you help and I'll be glad to give you any further information - Darryle

|||

Your question is entirely related to Access and not to SQL Server. Unfortunately I don’t see a thread dedicated to Access programming. You may want to try http://asp.net.

Consider “pass through” queries. They send a native SQL statement to the linked store, and receive the final result – all the processing is done at the server. In that case you don’t care about indices being replicated. Normal Access queries perform the processing in Access, and they need indices but the approach of using SQL Server only for storage and not for processing is near useless.

No idea if this is the right place to post... Need stored proc help

Hello,
I am trying to make a stored proc that will tell me if there are any rows in a table. The table name is a variable that I get at the start of the proc it is not sent in. What I am looking for in the table is sent into the stored proc.

here is where the problem is, i can not save it. It does not seem to allow me to use the if exists with variables. I have tried it also without the + and with the + ' to create a string I cant seem to get it anyway I try.
if exists (SELECT * FROM + @.TableName + WHERE + @.ColumnName + = + @.SearchStr2)

print 'got here ' +@.TableName + ' '+ @.ColumnName

END

Thanks for any help

You need to build the entire query string in a variable, and then use the dbo.sp_executesql stored procedure to execute that query string. As you've discovered, you can't use 'if exists' with variables.

For example:

declare @.sql nvarchar(max)

set @.sql = N'

if exists (select * from ' + @.TableName + N' where ' + @.ColumnName + ' = ''' + @.SearchStr2 + ''')

print ''Got here ' + @.TableName + ', ' + @.ColumnName + ''''

exec dbo.sp_executesql @.sql

|||

All of this of course is true; however, you also need to know that this is exactly the style of code that a hacker is looking to exploit for an "SQL Injection" attack. Please do some additional research before you implement this. This kind of code CREATES a security hole.

Dave

|||

Thanks Iain I will give it a shot and let you know. Also thanks Mugambo but this is a one time thing i need to run to do the following if anyone has a better idea how to do it I would love ideas.

I need to update our SQL database for an individual user. The problem is somehow this user got two ID's with different numbers and it has been about 4 months now that it has gone unnoticed and. So I now need to change the two id's into one. So what I was planning to do was to do was search ever column of every table and it would let me know if there is an exact match to the ID I put in. If so it will display the column and table so I can decide if it is data I need to change or not. We have about 200 tables so to do it one table at a time wouldnt be impossible to do but this way I learn a little more about SQL and stored procs which is what I really want anyway.

Thanks again

|||And thanks again Iain works great.|||Hi,

Can you mark a correct answer for us please.

Thanks!

No HTML page breaks and slow expanding rows

I have a report with a single table, single grouping level, single data set and no sub-reports. It has 3 rows for a grouping header and 3 rows per dataset row of detail. The detail rows are initially hidden and can be expanded by clicking on the header +. Its a fairly standard master-detail report.

Regardless of data size, I get NO page breaks in HTML. I have the Interactive size set to 8.5x11, KeepTogether is set to False, and PageBreakAtEnd is set to False. I would like it to break based on the visible grouping rows.

As it is now, everytime you expand any section, it takes forever to reload for a larger recordset.

I know that "HTML renderer and Preview (which are soft page break renderers) will ignore page breaks of conditionally hidden items and their children.", but how do I get this report to page break? I've seen a lot of posts on this, but none that seem to have an answer.

Anyone? Can I programmatically add the soft page breaks? The report is useless as it is now.|||

Thanks to others, I found a solution. Use the query to calculate the count of headers using the dense_rank() function:

select ...

dense_rank() over ( order by cl.last_name, cl.first_name, cl.client_id ) AS ClientRank

Then add a top level grouping on the table set to "Page Break at End" with the formula:

=Ceiling(Fields!ClientRank.Value/15)

replace 15 with how many group headers you want per page.

-Dave

sql

No help from the error messages

I am writing a tracking system. There is a table in the Sql Server 2000 database that contains a column for the user's ntid, the page they visited, the date of the last visit, a column each to track hits for the current year and a previous year column (basically for archiveing and reporting purposes), and 12 columns for hits per month (obviously, one per column). To record a hit, my unit determined we would only track one hit per day, so basically, there are 3 possible outcomes I needed to account for :

1) A user had never hit the page before, so I need to record the user's ID, the page they hit for the first time (since it won't exist yet), increment the year counter for that user on that page, and then determine what month column counter should be incremented as well.

2) A user had hit the page before, but not on this same day, so I need to update the row for that user on that page, changing the last visit field to reflect the current date, and icnrementing the appropriate counters.

3) A user had hit the page already on the same day, so basically, nothing should be changed whatsoever. No action should be taken.

I wrote a stored procedure to attempt to accomplish that logic, and though it's probably not very pretty, I was surprised at how few errors I got on my first Syntax check. Here's the stored procedure :

CREATE PROCEDURE sp_hitMe
@.ntid varchar(10),
@.page varchar(50),
@.thisHit datetime
AS
SET NOCOUNT ON

DECLARE @.tempDate datetime
DECLARE @.yearCount int
DECLARE @.monthCount int
DECLARE @.inMonth varchar(20)
DECLARE @.monthColumn varchar(10)
SET @.inMonth = DATENAME(mm, @.thisHit)
SET @.monthColumn =
CASE
WHEN @.inMonth = 'January' THEN 'hitsInJan'
WHEN @.inMonth = 'February' THEN 'hitsInFeb'
WHEN @.inMonth = 'March' THEN 'hitsInMar'
WHEN @.inMonth = 'April' THEN 'hitsInApr'
WHEN @.inMonth = 'May' THEN 'hitsInMay'
WHEN @.inMonth = 'June' THEN 'hitsInJun'
WHEN @.inMonth = 'July' THEN 'hitsInJul'
WHEN @.inMonth = 'August' THEN 'hitsInAug'
WHEN @.inMonth = 'September' THEN 'hitsInSep'
WHEN @.inMonth = 'October' THEN 'hitsInOct'
WHEN @.inMonth = 'November' THEN 'hitsInNov'
WHEN @.inMonth = 'December' THEN 'hitsInDec'
END

DECLARE @.insString varchar(500)
DECLARE @.updString varchar(500)
SET @.insString = 'INSERT INTO tblTracking (ntid, page, lastVisit, hitsThisYear, ' + @.monthColumn + ') VALUES (' + @.ntid + ', ' + @.page + ', ' + @.thisHit + ', 1, 1)'

if exists(select * from tblTracking where ntid = @.ntid and @.page = page)
begin
if exists(select * from tblTracking where lastVisit = @.thisHit)
begin
-- DO NOTHING!
end
else
begin
DECLARE @.theColumn varchar (100)
SET @.theColumn = 'SELECT ' + @.monthColumn + ' FROM tblTracking WHERE ntid = @.ntid AND @.page = page'
SET @.yearCount = (SELECT hitsThisYear FROM tblTracking WHERE ntid = @.ntid AND @.page = page) + 1
SET @.monthCount = (Exec @.theColumn)
SET @.monthCount = @.monthCount + 1
SET @.updString = 'UPDATE tblTracking SET lastVisit = ' + @.thisHit + ', hitsThisYear = ' + @.yearCount + ', ' + @.monthColumn + ' = ' + @.monthCount + ' WHERE ntid = @.ntid AND @.page = page'
Exec @.updString
end
end
else
begin
Exec @.insString
end
GO

And to my surprise, the only 3 errors I got were :

Server: Msg 156, Level 15, State 1, Procedure sp_hitMe, Line 39
Incorrect syntax near the keyword 'end'.
Server: Msg 156, Level 15, State 1, Procedure sp_hitMe, Line 45
Incorrect syntax near the keyword 'Exec'.
Server: Msg 156, Level 15, State 1, Procedure sp_hitMe, Line 50
Incorrect syntax near the keyword 'end'.

However, these are of course so vague as to be useless to me. What's wrong with the procedure? What have I missed?

At around line 33 I changed the --Do Nothing so that it actually declares something that is never used...

begin
if exists(select * from tblTracking where lastVisit = @.thisHit)
begin
-- DO NOTHING!
DECLARE @.theColumn1 varchar (100)
end
else

An alternative is finding out how to do something like an if not exists()

As far as the Exec, why don't you make @.theColumn a temporary table instead, and work with it that way? I don't know about using variables as tables, but you can do this easily with a temporary table.

|||

LD50:

At around line 33 I changed the --Do Nothing so that it actually declares something that is never used...

begin
if exists(select * from tblTracking where lastVisit = @.thisHit)
begin
-- DO NOTHING!
DECLARE @.theColumn1 varchar (100)
end
else

An alternative is finding out how to do something like an if not exists()

As far as the Exec, why don't you make @.theColumn a temporary table instead, and work with it that way? I don't know about using variables as tables, but you can do this easily with a temporary table.

That do nothing must actually have been a slightly larger error, because when I changed it to declare a small variable (basically a bit, just to avoid too much memory usage), 2 of the errors went away. I'm still having ti gripe at me about the Exec (@.theColumn) line. I don't think in this case a temporary table will do the trick only because I'm basically only trying to get one value, which is the current value of the month counter. Any other ideas?

|||

I'm not quite sure what you're trying to do, or what the column names in tbltracking are, so you'll have to modify this a bit. But it will compile.

if exists(select * from tblTracking where ntid = @.ntid and @.page = page)
begin
if exists(select * from tblTracking where lastVisit = @.thisHit)
begin
-- DO NOTHING!

DECLARE @.theColumn1 varchar (100)
end
else
begin
Create Table #theColumn (theColumn varchar (100))
Insert into #theColumn
Select monthname from tblTracking Where ntid = @.ntid and page = @.page
--SET @.theColumn = 'SELECT ' + @.monthColumn + ' FROM tblTracking WHERE ntid = @.ntid AND @.page = page'
SET @.yearCount = (SELECT hitsThisYear FROM tblTracking WHERE ntid = @.ntid AND @.page = page) + 1
SET @.monthCount = (Select Count(*) from #theColumn)
SET @.monthCount = @.monthCount + 1
SET @.updString = 'UPDATE tblTracking SET lastVisit = ' + @.thisHit + ', hitsThisYear = ' + @.yearCount + ', ' + @.monthColumn + ' = ' + @.monthCount + ' WHERE ntid = @.ntid AND @.page = page'
Exec @.updString
end
end
else

|||

LD50:

An alternative is finding out how to do something like an if not exists()

That's exactly how you do it. IF NOT EXISTS(SELECT ...)

|||

Oh, THANK you! I did have to slightly modify what you put in there, but you had the gist of it, so that's awesome. I would go ahead and mark an answer here, and I probably will since you answered that concern, but I was testing the logic, and trying to input the same record twice, which should have simply done nothing, but instead it inserted a duplicate record. Any idea why the logic failed me there?

|||

well why don't you paste up your new code and we'll let you know...

A good troubleshooting tip is to add some select statements in each part of the if. That way you can run it and see which if statement is firing and/or see if the columns in the table contain the data you expect.

Example:

if exists(select * from tblTracking where ntid = @.ntid and @.page = page)
begin
if exists(select * from tblTracking where lastVisit = @.thisHit)
begin
-- DO NOTHING!
select * from tblTracking where lastVisit = @.thisHit
DECLARE @.theColumn1 varchar (100)
end
else
begin

select * from tblTracking

Create Table #theColumn (theColumn varchar (100))
Insert into #theColumn
Select monthname from tblTracking Where ntid = @.ntid and page = @.page
--SET @.theColumn = 'SELECT ' + @.monthColumn + ' FROM tblTracking WHERE ntid = @.ntid AND @.page = page'
SET @.yearCount = (SELECT hitsThisYear FROM tblTracking WHERE ntid = @.ntid AND @.page = page) + 1
SET @.monthCount = (Select Count(*) from #theColumn)
SET @.monthCount = @.monthCount + 1
SET @.updString = 'UPDATE tblTracking SET lastVisit = ' + @.thisHit + ', hitsThisYear = ' + @.yearCount + ', ' + @.monthColumn + ' = ' + @.monthCount + ' WHERE ntid = @.ntid AND @.page = page'
Exec @.updString
end
end
else
begin
select * from tblTracking
Exec @.insString
end

|||

I also noticed that your if is illogical

if exists(select * from tblTracking where ntid = @.ntid and @.page = page)

should be:

if exists(select * from tblTracking where ntid = @.ntid and page = @.page)

|||

LD50:

I also noticed that your if is illogical

if exists(select * from tblTracking where ntid = @.ntid and @.page = page)

should be:

if exists(select * from tblTracking where ntid = @.ntid and page = @.page)

Actually, I noticed that myself and had already corrected it... *sheepish shrug*

OK, well I've added in a SELECT statement for the 'do-nothing' branch of logic and changed the two Exec statements to Print statements so I don't get a ton of duplicate records. But the logic is still somehow faulty, as I'm using the Query Analyzer to test it, and trying to input the same record that already exists in the table still prints out the INSERT string, instead of the do-nothing SELECT statement. Here's what I have now, see if you can help me figure out what the problem is...

CREATE PROCEDURE sp_hitMe
@.ntid varchar(10),
@.page varchar(50),
@.thisHit smalldatetime
AS
SET NOCOUNT ON

DECLARE @.yearCount int
DECLARE @.monthCount int
DECLARE @.inMonth varchar(20)
DECLARE @.monthColumn varchar(10)
SET @.inMonth = DATENAME(mm, @.thisHit)
SET @.monthColumn =
CASE
WHEN @.inMonth = 'January' THEN 'hitsInJan'
WHEN @.inMonth = 'February' THEN 'hitsInFeb'
WHEN @.inMonth = 'March' THEN 'hitsInMar'
WHEN @.inMonth = 'April' THEN 'hitsInApr'
WHEN @.inMonth = 'May' THEN 'hitsInMay'
WHEN @.inMonth = 'June' THEN 'hitsInJun'
WHEN @.inMonth = 'July' THEN 'hitsInJul'
WHEN @.inMonth = 'August' THEN 'hitsInAug'
WHEN @.inMonth = 'September' THEN 'hitsInSep'
WHEN @.inMonth = 'October' THEN 'hitsInOct'
WHEN @.inMonth = 'November' THEN 'hitsInNov'
WHEN @.inMonth = 'December' THEN 'hitsInDec'
END

DECLARE @.insString varchar(500)
DECLARE @.updString varchar(500)
SET @.insString = 'INSERT INTO tblTracking(ntid, page, lastVisit, hitsThisYear, ' + @.monthColumn + ') VALUES (''' + @.ntid + ''','' ' + @.page + ''', ''' + Convert(varchar(15), @.thisHit, 101) + ''', ''1'', ''1'')'

if exists(select * from tblTracking where ntid = @.ntid and page = @.page)
begin
if exists(select * from tblTracking where lastVisit = @.thisHit)
begin
DECLARE @.bs bit
SELECT * FROM tblTracking where lastVisit = @.thisHit
end
else
begin
DECLARE @.theString varchar (100)
SET @.theString = 'SELECT ' + @.monthColumn + ' FROM tblTracking WHERE ntid = @.ntid AND page = @.page'
CREATE TABLE #theColumn (theColumn varchar (100))
INSERT INTO #theColumn
Exec @.theString
SET @.yearCount = (SELECT hitsThisYear FROM tblTracking WHERE ntid = @.ntid AND page = @.page) + 1
SET @.monthCount = (SELECT theColumn FROM #theColumn)
SET @.monthCount = @.monthCount + 1
SET @.updString = 'UPDATE tblTracking SET lastVisit = ' + Convert(varchar(15), @.thisHit, 101) + ', hitsThisYear = ' + @.yearCount + ', ' + @.monthColumn + ' = ' + @.monthCount + ' WHERE ntid = @.ntid AND page = @.page'
Print @.updString
end
end
else
begin
Print (@.insString)
end
GO

|||

Woo! Nevermind, I have finally figured it out. Turns out I needed to use LIKEs instead of =s when I was comparing the varchar values, and I needed to throw in some CASTs to turn some of the ints into varchars for the UPDATE string. If anyone cares to see the corrected, working code, I will post it, but it is working now.

Thanks for the help!

No Excel 2007 Table Support in SSIS?

When I connect to an Excel 2007 workbook using the new Microsoft.ACE.Oledb.12.0 provider in SSIS 2005, I notice that any tables that I've created in the worksheet are not recognized in SSIS.

In the OLE DB data source component (using the "Table or View" data access mode) the any table(s) I've created are nowhere to be found in the drop down list. Similarly, when I constuct a simple SQL query on the workbook, such as "SELECT * From MyTable" it returns the error:

Microsoft Office Access Database Engine

Hresult: 0x80004005

Description: "The Microsoft Access database engine could not find the object 'MyTable'. Make sure the object exists and that you spell its name and the path name correctly."

I know I have the name right -- I can use structured references to my named table in the worksheet without any problems.

Does the new ACE provider not support Excel 2007 tables? Am I stuck with using "overlapping" cell references to capture data from tables in my worksheet?

I see that you are using Named Ranges. What if you use the worksheet name?

The Office 12 versions of the Jet Provider and Excel Driver are rewritten, so it's possible that behavior has changed, whether intentionally or unintentionally. Previously the driver displayed both sheets and named ranges as "tables".

-Doug

|||

Apologies for not making myself clearer. I'm referring to the improved table objects in Excel 2007, discussed here:

http://blogs.msdn.com/excel/archive/2005/10/25/484915.aspx

When extracting data from an Excel 2007 source, I'm able to access all of the information on the sheet or from my named ranges without any problem -- both appear as what SSIS called "tables" in the OLE DB Source dialog box. This behavior is the exact same as it was using the JET provider.

My problem is that the Excel 2007 table objects that I've created in various sheets simply aren't accessible -- they don't show up at all. Given their structured, unifiorm nature, one would expect an Excel table to be recognized by the new ACE provider just like, say, a named range.

Why named tables instead of simple named ranges?

-- Tables can grow dynamically as you add rows, and named ranges cannot. My thinking was that if you could import an Excel 2007 table, you wouldn't have to worry if the named range was large enough.

-- Excel 2007 tables can be filtered and sorted, giving the user in the workbook a richer experience.

I think it would be very powerful and useful to access data as it is represented in an Excel 2007 table object through SSIS. I haven't been able to determine if this is even possible, or if I need to take some special steps to make it work.

|||

The driver has no knowledge of any new features in Excel 2007. This is merely the legacy driver, updated to connect successfully to Excel 2007 files, which as you know use a different file format.

-Doug

|||

Thanks for your quick reply -- looks like I'll have to go back to named references.

Also, for anyone wanting to do a similar thing with "Lists" in Excel 2003 (the precursor to Excel 2007) a quick test seems to indicate that the Excel driver (for pre-2007 versions) does not support them, either.

No duplicate record from joined table

Hi,
I have 2 tables on DB2 version 6

Tab_A
----
id name
1 shop1
2 shop2

Tab_B
--------
id_tab_a keyword
1 book
1 bookstore
1 handbook
2 restaurant

I need to write a SQL query that find who have 'book' as keyword on Tab_B, but with no duplicated record. If i write:

Select distinct id, name left join Tab_B on id=id_tab_a where
keyword like '%book%'

obtain 3 "duplicated" records.

Any ideas?
Thanks,
JhonOriginally posted by fartman
Select distinct id, name left join Tab_B on id=id_tab_a where
keyword like '%book%'


You need to group this puppy

select id,name from tab_a
left join tab_b on id=id_tab_a
group by id,name

Best of luck
-Chris
Come read my SQL tutorial at http://www.bitesizeinc.net/index.php/sql.html|||HI, the following query will help you

select id,name from tab_a
left join tab_b on id=id_tab_a where keyword like '%book%'
group by id,name;

Originally posted by christodd
You need to group this puppy

select id,name from tab_a
left join tab_b on id=id_tab_a
group by id,name

Best of luck
-Chris
Come read my SQL tutorial at http://www.bitesizeinc.net/index.php/sql.html|||problem solved !!!
Thank you.

Jhonny

Originally posted by neelamchalam
HI, the following query will help you

select id,name from tab_a
left join tab_b on id=id_tab_a where keyword like '%book%'
group by id,name;

No dependencies when inserting data into a #temp table

Hi
I have a problem. I have a database with way to many stored procedures. In
order delete some i made a script that finds stored procedures and their
dependencies. Those without any dependencies should be deleted because they
typically have been emptied for logic.
But unfortunately if there is a 'INSERT INTO #TEMP (FIELD1) SELECT Field1
FROM TABLE_1' there is no dependency for table TABLE_1. If the INSERT INTO
uses a normal table or a table variable there is a dependency.
My example below turns out to have no dependecies as all even though the
table exists up front and the procedure has been executed succesfully.
Example: --
CREATE PROCEDURE Test3 AS
Create Table #no (
i int
)
Insert into #no (i) Select top 10 ID from table_1
Select * from #no
Drop table #no
--
This is a problem for me because a lot of my reports collect data from
several tables and insert them into temp tables. But effectively they will
show up with no dependencies and therefor be subject for deletion.
Remodelling all stored procedures to use table variables is no option.
This occurs in both SQL 2000 and SQL 2005 so I wonder if this is an error in
the product or whether there is a good reason for doing as it does.> This occurs in both SQL 2000 and SQL 2005 so I wonder if this is an error
> in
> the product or whether there is a good reason for doing as it does.
One of the reasons dependency information may be missing is due to deferred
name resolution. Since the temp table does not exist when the proc is
created, no dependency info is recorded for any objects referenced by the
INSERT statement.

> Remodelling all stored procedures to use table variables is no option.
If you create the temp table before creating the proc, you should get the
dependency info.

> This occurs in both SQL 2000 and SQL 2005 so I wonder if this is an error
> in
> the product or whether there is a good reason for doing as it does.
Although one could make the argument that this is a bug, it's unclear to me
what the correct behavior should be when a statement contains a mix of
existing and non-existing objects. You might consider submitting product
enhancement feedback via Connect (http://connect.microsoft.com/SQLServer) to
suggest that known dependency information be recorded when a statement
contains a mix of existing and non-existing objects. Perhaps this will make
it into the next SQL Server version or future service pack.
Hope this helps.
Dan Guzman
SQL Server MVP
"Karsten Feddersen, Denmark" <Karsten Feddersen,
Denmark@.discussions.microsoft.com> wrote in message
news:48D61C0B-5635-4137-A3E8-C7A7CC16796F@.microsoft.com...
> Hi
> I have a problem. I have a database with way to many stored procedures. In
> order delete some i made a script that finds stored procedures and their
> dependencies. Those without any dependencies should be deleted because
> they
> typically have been emptied for logic.
> But unfortunately if there is a 'INSERT INTO #TEMP (FIELD1) SELECT Field1
> FROM TABLE_1' there is no dependency for table TABLE_1. If the INSERT INTO
> uses a normal table or a table variable there is a dependency.
> My example below turns out to have no dependecies as all even though the
> table exists up front and the procedure has been executed succesfully.
> Example: --
> CREATE PROCEDURE Test3 AS
> Create Table #no (
> i int
> )
> Insert into #no (i) Select top 10 ID from table_1
> Select * from #no
> Drop table #no
> --
> This is a problem for me because a lot of my reports collect data from
> several tables and insert them into temp tables. But effectively they will
> show up with no dependencies and therefor be subject for deletion.
> Remodelling all stored procedures to use table variables is no option.
> This occurs in both SQL 2000 and SQL 2005 so I wonder if this is an error
> in
> the product or whether there is a good reason for doing as it does.sql

No dependencies when inserting data into a #temp table

Hi
I have a problem. I have a database with way to many stored procedures. In
order delete some i made a script that finds stored procedures and their
dependencies. Those without any dependencies should be deleted because they
typically have been emptied for logic.
But unfortunately if there is a 'INSERT INTO #TEMP (FIELD1) SELECT Field1
FROM TABLE_1' there is no dependency for table TABLE_1. If the INSERT INTO
uses a normal table or a table variable there is a dependency.
My example below turns out to have no dependecies as all even though the
table exists up front and the procedure has been executed succesfully.
Example: --
CREATE PROCEDURE Test3 AS
Create Table #no (
i int
)
Insert into #no (i) Select top 10 ID from table_1
Select * from #no
Drop table #no
--
This is a problem for me because a lot of my reports collect data from
several tables and insert them into temp tables. But effectively they will
show up with no dependencies and therefor be subject for deletion.
Remodelling all stored procedures to use table variables is no option.
This occurs in both SQL 2000 and SQL 2005 so I wonder if this is an error in
the product or whether there is a good reason for doing as it does.> This occurs in both SQL 2000 and SQL 2005 so I wonder if this is an error
> in
> the product or whether there is a good reason for doing as it does.
One of the reasons dependency information may be missing is due to deferred
name resolution. Since the temp table does not exist when the proc is
created, no dependency info is recorded for any objects referenced by the
INSERT statement.
> Remodelling all stored procedures to use table variables is no option.
If you create the temp table before creating the proc, you should get the
dependency info.
> This occurs in both SQL 2000 and SQL 2005 so I wonder if this is an error
> in
> the product or whether there is a good reason for doing as it does.
Although one could make the argument that this is a bug, it's unclear to me
what the correct behavior should be when a statement contains a mix of
existing and non-existing objects. You might consider submitting product
enhancement feedback via Connect (http://connect.microsoft.com/SQLServer) to
suggest that known dependency information be recorded when a statement
contains a mix of existing and non-existing objects. Perhaps this will make
it into the next SQL Server version or future service pack.
Hope this helps.
Dan Guzman
SQL Server MVP
"Karsten Feddersen, Denmark" <Karsten Feddersen,
Denmark@.discussions.microsoft.com> wrote in message
news:48D61C0B-5635-4137-A3E8-C7A7CC16796F@.microsoft.com...
> Hi
> I have a problem. I have a database with way to many stored procedures. In
> order delete some i made a script that finds stored procedures and their
> dependencies. Those without any dependencies should be deleted because
> they
> typically have been emptied for logic.
> But unfortunately if there is a 'INSERT INTO #TEMP (FIELD1) SELECT Field1
> FROM TABLE_1' there is no dependency for table TABLE_1. If the INSERT INTO
> uses a normal table or a table variable there is a dependency.
> My example below turns out to have no dependecies as all even though the
> table exists up front and the procedure has been executed succesfully.
> Example: --
> CREATE PROCEDURE Test3 AS
> Create Table #no (
> i int
> )
> Insert into #no (i) Select top 10 ID from table_1
> Select * from #no
> Drop table #no
> --
> This is a problem for me because a lot of my reports collect data from
> several tables and insert them into temp tables. But effectively they will
> show up with no dependencies and therefor be subject for deletion.
> Remodelling all stored procedures to use table variables is no option.
> This occurs in both SQL 2000 and SQL 2005 so I wonder if this is an error
> in
> the product or whether there is a good reason for doing as it does.

No dependencies when inserting data into a #temp table

Hi
I have a problem. I have a database with way to many stored procedures. In
order delete some i made a script that finds stored procedures and their
dependencies. Those without any dependencies should be deleted because they
typically have been emptied for logic.
But unfortunately if there is a 'INSERT INTO #TEMP (FIELD1) SELECT Field1
FROM TABLE_1' there is no dependency for table TABLE_1. If the INSERT INTO
uses a normal table or a table variable there is a dependency.
My example below turns out to have no dependecies as all even though the
table exists up front and the procedure has been executed succesfully.
Example: --
CREATE PROCEDURE Test3 AS
Create Table #no (
i int
)
Insert into #no (i)Select top 10 ID from table_1
Select * from #no
Drop table #no
This is a problem for me because a lot of my reports collect data from
several tables and insert them into temp tables. But effectively they will
show up with no dependencies and therefor be subject for deletion.
Remodelling all stored procedures to use table variables is no option.
This occurs in both SQL 2000 and SQL 2005 so I wonder if this is an error in
the product or whether there is a good reason for doing as it does.
> This occurs in both SQL 2000 and SQL 2005 so I wonder if this is an error
> in
> the product or whether there is a good reason for doing as it does.
One of the reasons dependency information may be missing is due to deferred
name resolution. Since the temp table does not exist when the proc is
created, no dependency info is recorded for any objects referenced by the
INSERT statement.

> Remodelling all stored procedures to use table variables is no option.
If you create the temp table before creating the proc, you should get the
dependency info.

> This occurs in both SQL 2000 and SQL 2005 so I wonder if this is an error
> in
> the product or whether there is a good reason for doing as it does.
Although one could make the argument that this is a bug, it's unclear to me
what the correct behavior should be when a statement contains a mix of
existing and non-existing objects. You might consider submitting product
enhancement feedback via Connect (http://connect.microsoft.com/SQLServer) to
suggest that known dependency information be recorded when a statement
contains a mix of existing and non-existing objects. Perhaps this will make
it into the next SQL Server version or future service pack.
Hope this helps.
Dan Guzman
SQL Server MVP
"Karsten Feddersen, Denmark" <Karsten Feddersen,
Denmark@.discussions.microsoft.com> wrote in message
news:48D61C0B-5635-4137-A3E8-C7A7CC16796F@.microsoft.com...
> Hi
> I have a problem. I have a database with way to many stored procedures. In
> order delete some i made a script that finds stored procedures and their
> dependencies. Those without any dependencies should be deleted because
> they
> typically have been emptied for logic.
> But unfortunately if there is a 'INSERT INTO #TEMP (FIELD1) SELECT Field1
> FROM TABLE_1' there is no dependency for table TABLE_1. If the INSERT INTO
> uses a normal table or a table variable there is a dependency.
> My example below turns out to have no dependecies as all even though the
> table exists up front and the procedure has been executed succesfully.
> Example: --
> CREATE PROCEDURE Test3 AS
> Create Table #no (
> i int
> )
> Insert into #no (i) Select top 10 ID from table_1
> Select * from #no
> Drop table #no
> --
> This is a problem for me because a lot of my reports collect data from
> several tables and insert them into temp tables. But effectively they will
> show up with no dependencies and therefor be subject for deletion.
> Remodelling all stored procedures to use table variables is no option.
> This occurs in both SQL 2000 and SQL 2005 so I wonder if this is an error
> in
> the product or whether there is a good reason for doing as it does.

No Data Returned for report

Hi,

In RS 2005, if a specific query does not return any rows, the table also is not displayed in the report.

Is there any way to get around this and show an empty table?

Manish

Hi,

select all the cells and in the properties make "borderstyle" - "Solid"

and select the table and make same as above in the properties.

you can see the empty table.

Amarnath

|||

One option is to set the "NoRows" property of the table. You can also apply styles to that message.

-- Robert

|||

This is correct but only if the table has a Header or Footer row.

If the dataset associated with a table returns no data, and there is no "NoRows" message specified for the table, AND if there is a header or footer, the table will be shown.

-chris

No Data Needed To be Merged - But Only for INSERT ??

Hi All,
I'm receiving some strange behaviour from replication between SQL Server 2K/SQL CE.
Replicating UPDATE's to a table on my server works fine as viewed in the Replication
Monitor and the updated rows in the table.
However, if I replicate INSERTS, then Replication Monitor reports No Data Needed
To be Merged and the rows are not inserted in the table.
Has anyone come across this issue?
Cheers,
ciaran
Does your Table use Identity Values ?
If so, then you will need to set it to (Not For Replication).
|||Hi Gerry,
My table does use Identity Values, and it does have NOT FOR Replication set.
I also have Identity Ranges set.
Some more info. that has just come to light. Some rows DO insert, while other
rows do not. At one stage the SQL CE database that was on the devices was
physically deleted and re-initialised. Could it be that the old rows were left
lying around and that somehow this is preventing me from doing my inserts on
these rows? In which case what can I do? Delete rows from MSmerge_contents?
Thanks for the help !
|||By re-initialised do you mean you re-initialised the Subscription (i.e. under Replication Monitor / Publishers / right click on the publication and selected Reinitialise All Subscriptions ?)
Try running the option (again right click on the Publisher) and selet Validate all Subscriptions to see if
the subscriber is in sync with the publisher.
Check out BOL "Validating Replicatated Data" for more info on this.
sql

No data in SYSPERFINFO table of Master DB

Has anybody run into the problem on not being able to access SQL performance
monitors. I have tracked it down to the SYSPERFINFO table not collecting any
data. I have worked with Microsoft and they have no idea why this is
happening. I reboot and everything is fine for about a week and then it just
stops...no warning...no nothing.
I have SQL2000 w/SP3a Enterprise running in a cluster. I have reregistered
and rebuilt the counters and ensured that they have been selected using the
exctrlst app.
Just curious.
RickOne thing I would check -- run sysmon with logging turned
on, gathering data every minute or so. Perhaps there's so
much activity that IT isn't collecting the data in a
timely fashion. I've seen that happen on busy systems,
but I've never corelated that with the SYSPERFINFO table.
>--Original Message--
>Has anybody run into the problem on not being able to
access SQL performance
>monitors. I have tracked it down to the SYSPERFINFO table
not collecting any
>data. I have worked with Microsoft and they have no idea
why this is
>happening. I reboot and everything is fine for about a
week and then it just
>stops...no warning...no nothing.
>I have SQL2000 w/SP3a Enterprise running in a cluster. I
have reregistered
>and rebuilt the counters and ensured that they have been
selected using the
>exctrlst app.
>Just curious.
>Rick
>
>.
>

No Create Table as Select from ?

SQLServer2k doesn't let me do a:
CREATE TABLE myNewTbl
AS
select fld1 from myOldTbl
In oracle it works.
Thanks,
Carlbelow code will do the same job in sql2000

select fld1 into myNewTbl from myOldTbl|||Great, I guess that's not as good as the ahem, the oracle way. Suppose you had a union query. How would that work?|||what union query? u meant something like,

select fld1 into myNewTbl from (select fld1 from table1 union select fld1 from table2) as tm
post ur union query.|||Ya sorry, I should've been more specific.

In oracle I would:

CREATE TABLE myNewTable AS
SELECT fld1 FROM myOldTable1
UNION
SELECT fld2 FROM myOldTable2

Mostly i use this to get a roughed out table structure, then set up my append queries, so your 1st reply did the job, I just used the 1st SELECT.

Thanks,
Carl|||SELECT *
INTO myNewTable
FROM (
SELECT fld1 FROM myOldTable1
UNION
SELECT fld2 FROM myOldTable2) AS XXX|||SELECT *
INTO myNewTable
FROM (
SELECT fld1 FROM myOldTable1
UNION
SELECT fld2 FROM myOldTable2) AS XXX

Why not just:

SELECT fld1
INTO myNewTable
FROM myOldTable1
UNION
select fld2
FROM myOldTable2sql

No Client Protocols Enabled yet I can still get a DSN connection to SQL Server 2005

Perplexed...Why am I able to get a connection to my SQL Server from an
Access 2003 database via DSN linked SQL server db table without a
common protocol between the client and server? Could it be that the
client does have TCP/IP or Named Pipes enabled but they are just not
showing up in the cliconfg.exe utility as such? Help!Clients have always TCP/IP and/or Named Pipes enabled. This setting is for
the server, not for the client tools.
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)
"lacdn76" <dakarpiak@.yahoo.com> wrote in message
news:1175526801.931586.126790@.y66g2000hsf.googlegroups.com...
> Perplexed...Why am I able to get a connection to my SQL Server from an
> Access 2003 database via DSN linked SQL server db table without a
> common protocol between the client and server? Could it be that the
> client does have TCP/IP or Named Pipes enabled but they are just not
> showing up in the cliconfg.exe utility as such? Help!
>

No Client Protocols Enabled yet I can still get a DSN connection to SQL Server 2005

Perplexed...Why am I able to get a connection to my SQL Server from an
Access 2003 database via DSN linked SQL server db table without a
common protocol between the client and server? Could it be that the
client does have TCP/IP or Named Pipes enabled but they are just not
showing up in the cliconfg.exe utility as such? Help!
Clients have always TCP/IP and/or Named Pipes enabled. This setting is for
the server, not for the client tools.
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)
"lacdn76" <dakarpiak@.yahoo.com> wrote in message
news:1175526801.931586.126790@.y66g2000hsf.googlegr oups.com...
> Perplexed...Why am I able to get a connection to my SQL Server from an
> Access 2003 database via DSN linked SQL server db table without a
> common protocol between the client and server? Could it be that the
> client does have TCP/IP or Named Pipes enabled but they are just not
> showing up in the cliconfg.exe utility as such? Help!
>

No "Open Table" In SQL Server 2005?

In Enterprise Manager for SQL Server 2000, I used to choose "Open Table..."
and then "Return All rows" to be able to update data directly into a grid.
This tool was rapid and useful, but i'm not able to find a such tool in SQL
Server 2005 environment... Is there a tool like this? If not, somebody know
why Microsoft has removed a awesome tool like it'
David.
Thanks(a) you must be using an old beta:
http://www.aspfaq.com/sql2005/show.asp?id=5
(b) please post SQL Server 2005 questions to the SQL Server 2005 newsgroups:
http://www.aspfaq.com/sql2005/show.asp?id=1
"David Parenteau" <DavidParenteau@.discussions.microsoft.com> wrote in
message news:2344C393-7600-4041-BE01-5E0A10AE33D4@.microsoft.com...
> In Enterprise Manager for SQL Server 2000, I used to choose "Open
> Table..."
> and then "Return All rows" to be able to update data directly into a grid.
> This tool was rapid and useful, but i'm not able to find a such tool in
> SQL
> Server 2005 environment... Is there a tool like this? If not, somebody
> know
> why Microsoft has removed a awesome tool like it'
> David.
> Thanks

Nice problem: How to combine different columns into a table

It's rather easy to combine resultset from the same table structure...we can either insert the entries or union the results.

But let's say you select different columns from different tables and want to combine them to form a new table, how would you do it (assuming you can't join those tables since they are not related), assuming they all return the same number of rows.

select col1 from table1
go
select col2 from table2
go

Now I want to combine them so table3 is made of col1 and col2.Go into more detail as to what you are trying to do - if I understand what you are attempting to do, how do you guarantee the relationship between col1 and col2, so that when an insert into table3 occurs you have the correct col1 and col2 together ?|||Originally posted by rnealejr
Go into more detail as to what you are trying to do - if I understand what you are attempting to do, how do you guarantee the relationship between col1 and col2, so that when an insert into table3 occurs you have the correct col1 and col2 together ?

I basically go through gathering a list of statistical info for all users.
So say I have 10 users. I do one pass of some rather complex queries to get the data for first column. Then I do another pass for another column. Now I want to combine them all into one table.

There is no need to guarantee the relationship between columns, this is merely for reporting purposes so once combined there will never be row insertion to the table ever.

e.g.

User [dataset 1] [dataset 2] [dataset 3]
1 10 20 2
2 23 30 4

Thanks!|||Originally posted by waspfish
I basically go through gathering a list of statistical info for all users.
So say I have 10 users. I do one pass of some rather complex queries to get the data for first column. Then I do another pass for another column. Now I want to combine them all into one table.

There is no need to guarantee the relationship between columns, this is merely for reporting purposes so once combined there will never be row insertion to the table ever.

e.g.

User [dataset 1] [dataset 2] [dataset 3]
1 10 20 2
2 23 30 4

Thanks!

1. Create an @.table with an identity. Fill that table.
2. Create another @.table with an identity. Fill that with the second table.
3. Query both tables and relate them using their identities.|||you mean something like this >>>

select id, (select count(*) from sysobjects), (select max(id) from sysindexes) from syscolumns

Next_run_time and next_run_date of sysjobschedules

I have written some code to query the next_run_time and next_run_date of the sysjobschedules table. It works well with the exception that when a new schedule is created or an existing one is changed, these two columns are set to zero. They will eventually be initialized to the appropriate values but that may take a while. That means that the code will not work until then.

Is there a way to FORCE the initialization of the two columns ?How does your code looks like?
Tried initializing those 2 values in the code itself?|||The code is simply selecting the two columns into variables and then do further processing.

The problem is the two columns (next_run_date and next_run_time) of sysjobschedules table are set to zero whenever a new schedule is created or changed. You can try it yourselef.

Create a job (xxxxx) with a schedule.

select a.name, b.name,next_run_date, next_run_time from msdb..sysjobs a inner join msdb..sysjobschedules b
on a.job_id=b.job_id
where a.name='xxxxx'

and you will see those two columns are zeros.

However, after a while they will updated to the appropriate values by SQL.

My question, how do we force SQL to update these two columns immediately? It seems SQL keeps them in cache somewhere and periodically updates the sysjobschedules table.|||True, what you said is right.

I believe as soon as the job finishes the execution these values will be updated internally and only with manual updation you can set other values if required.

May refer to book 'Inside SQL Server 2000' by Kalen Daleney.