adding a stored proc to a publication

Hi

I created a publication (merge anonymous via Web) - the subscriber already had the data, so there was no need to to initialize data and schema.

The problem is when it comes to adding a new stored proc to the publication. I create the proc, add it as an article to the publication and recreate the snapshot ok, but then the synchronisation fails. I presume this is because the stored proc does not exist at the subscriber. I bet it would work if I had initialized the schema and data, but due to the size of the data this is not practical.

If I manually create the proc at the subscriber then it works fine - but this defeats the purpose somewhat.....

Any ideas ?

thanks
BruceSince the subscription was created as no-sync, you will need to manually create the proc on the subscriber. You could create different publications for tables and stored procs and workaround that way. Now you can initialize the subscriber for the publication with the stored proc (and other smaller articles if you wish) and large data publication can stay intact (as no-sync)|||Thanks for the suggestion - a good workaround for now....

Adding a index automatically...

Hi, still new to all this I'm afraid! I've created a DTS routine that copys some table data from an Access database into another Access database. In the second mdb, I would like the routine to add an ID field and populate all the lines. I'm sure it must be kind of easy but I can't find how!

Any help appreciated.Ah sorted - just added the ID as an Identity when the table was being created in the routine.

adding a datestamp automatically

Hi,
As a convert from Access, I need a little help please.
I need to get a datestamp into a new record when it is created.
I can see the line for Default Value, but don't know what code to insert
here.
Many thanks,
NEIL
In news:csisi5$9ge$1$8300dec7@.news.demon.co.uk,
Neil Jarman <neil@.tNOiSPAMvPLEASEy.co.uk> said:
> Hi,
> As a convert from Access, I need a little help please.
> I need to get a datestamp into a new record when it is created.
> I can see the line for Default Value, but don't know what code to
> insert here.
Default should be:
getDate()
Steve
|||Hi Steve,
Many thanks!
NEIL
"Steve" <steve@.somewhere.invalid.com> wrote in message
news:FH6Hd.958160$2W1.74289@.news.easynews.com...
> In news:csisi5$9ge$1$8300dec7@.news.demon.co.uk,
> Neil Jarman <neil@.tNOiSPAMvPLEASEy.co.uk> said:
> Default should be:
> getDate()
> --
> Steve
>

adding a datestamp automatically

Hi,
As a convert from Access, I need a little help please.
I need to get a datestamp into a new record when it is created.
I can see the line for Default Value, but don't know what code to insert
here.
Many thanks,
NEILIn news:csisi5$9ge$1$8300dec7@.news.demon.co.uk,
Neil Jarman <neil@.tNOiSPAMvPLEASEy.co.uk> said:
> Hi,
> As a convert from Access, I need a little help please.
> I need to get a datestamp into a new record when it is created.
> I can see the line for Default Value, but don't know what code to
> insert here.
Default should be:
getDate()
Steve|||Hi Steve,
Many thanks!
NEIL
"Steve" <steve@.somewhere.invalid.com> wrote in message
news:FH6Hd.958160$2W1.74289@.news.easynews.com...
> In news:csisi5$9ge$1$8300dec7@.news.demon.co.uk,
> Neil Jarman <neil@.tNOiSPAMvPLEASEy.co.uk> said:
> Default should be:
> getDate()
> --
> Steve
>

adding a datestamp automatically

Hi,
As a convert from Access, I need a little help please.
I need to get a datestamp into a new record when it is created.
I can see the line for Default Value, but don't know what code to insert
here.
Many thanks,
NEILIn news:csisi5$9ge$1$8300dec7@.news.demon.co.uk,
Neil Jarman <neil@.tNOiSPAMvPLEASEy.co.uk> said:
> Hi,
> As a convert from Access, I need a little help please.
> I need to get a datestamp into a new record when it is created.
> I can see the line for Default Value, but don't know what code to
> insert here.
Default should be:
getDate()
--
Steve|||Hi Steve,
Many thanks!
NEIL
"Steve" <steve@.somewhere.invalid.com> wrote in message
news:FH6Hd.958160$2W1.74289@.news.easynews.com...
> In news:csisi5$9ge$1$8300dec7@.news.demon.co.uk,
> Neil Jarman <neil@.tNOiSPAMvPLEASEy.co.uk> said:
>> Hi,
>> As a convert from Access, I need a little help please.
>> I need to get a datestamp into a new record when it is created.
>> I can see the line for Default Value, but don't know what code to
>> insert here.
> Default should be:
> getDate()
> --
> Steve
>

Adding "From Query" parameters degrades performance

Hi list,
This is the second time I've posted this question, the second time I've
created a report with "From Query" parameters and the second time I've had
problems with performance with the "From Query" parameters...
http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&threadm=eOTaQ1ufEHA.3024%40TK2MSFTNGP10.phx.gbl&rnum=1&prev=/groups%3Fq%3DFrom%2BQuery%2Bparameters%2Bslows%2Bquery%2Bmicrosoft.public.sqlserver.reportingsvcs%26ie%3DUTF-8%26hl%3Den%26btnG%3DGoogle%2BSearch
Here's the question I posed before, unfortunately I've recieved no
response.. Can someone help, has anyone else noticed the same problem...?
The problem I'm having is very easy to reproduce...
On the main menu in report designer... In the Report->Report Parameters
property page there are two types of
parameters Non-Queried and From Query. Non-Queried appears to prompt the
user for its value while the From Query parameter appears to be populated
from the query as it's name implies by assigning a column from the dataset.
I've created several of these type of parameters because I need a way to
have a page header with data from the query repeat on every page since I
cannot put Fields in the page header and using a table with the
repeatonnewpage doesn't seem to work this was my only viable solution. If
you look at my posts for the last couple days you'll get an idea of what I'm
trying to do. So my question remains why would creating From Query
parameters slow the execution of my report...? Am I using them incorrectly
is there another direction I can take to to achieve my goal...
Thanks,
DanI use this all the time without any performance problem whatsoever. A few
questions for you. How many parameters do you have in the report? How many
rows are being returned? How long does it take for each of them to run from
outside of RS (from Query Analyzer).
Am I right in assuming the time taken is for the report to come up and
prompt for the parameters? It is not when you try to then view the report is
it?
Try a report that does nothing except have the report parameters based on
the queries. This would help pinpoint if it is the parameters or something
else.
Bruce L-C
"alien2_51" <dan.billow"at"n.o.s.p.a.m.monacocoach.commercialversion> wrote
in message news:%2392SqZrjEHA.3608@.TK2MSFTNGP09.phx.gbl...
> Hi list,
> This is the second time I've posted this question, the second time I've
> created a report with "From Query" parameters and the second time I've had
> problems with performance with the "From Query" parameters...
>
http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&threadm=eOTaQ1ufEHA.3024%40TK2MSFTNGP10.phx.gbl&rnum=1&prev=/groups%3Fq%3DFrom%2BQuery%2Bparameters%2Bslows%2Bquery%2Bmicrosoft.public.sqlserver.reportingsvcs%26ie%3DUTF-8%26hl%3Den%26btnG%3DGoogle%2BSearch
> Here's the question I posed before, unfortunately I've recieved no
> response.. Can someone help, has anyone else noticed the same problem...?
> The problem I'm having is very easy to reproduce...
> On the main menu in report designer... In the Report->Report Parameters
> property page there are two types of
> parameters Non-Queried and From Query. Non-Queried appears to prompt the
> user for its value while the From Query parameter appears to be populated
> from the query as it's name implies by assigning a column from the
dataset.
> I've created several of these type of parameters because I need a way to
> have a page header with data from the query repeat on every page since I
> cannot put Fields in the page header and using a table with the
> repeatonnewpage doesn't seem to work this was my only viable solution. If
> you look at my posts for the last couple days you'll get an idea of what
I'm
> trying to do. So my question remains why would creating From Query
> parameters slow the execution of my report...? Am I using them incorrectly
> is there another direction I can take to to achieve my goal...
> Thanks,
> Dan
>
>|||Hi Bruce,
Thanks for your reply, in this particular report I have 47 "From Query"
parameters which is less than the previous report I built which seemed to
take longer. For the report I'm working on now I have a header and a
subreport, the header returns one row of data and contains the from query
parameters. Strangely enough the troubles I was having with the header
report have seemed to clear up, although the first couple of times I tried
to run the report the report previewer indicated that my report had been
canceled and then I would click the View Report button again and the report
would run. My first report returned several rows of data but the data was
repeated for every line of detail this is the one that I'm continually
having performance problems with I'm assigning the repeated data to the From
Query parameters.
Dan
"Bruce Loehle-Conger" <bruce_lcNOSPAM@.hotmail.com> wrote in message
news:OO4WMgrjEHA.3392@.TK2MSFTNGP15.phx.gbl...
> I use this all the time without any performance problem whatsoever. A few
> questions for you. How many parameters do you have in the report? How many
> rows are being returned? How long does it take for each of them to run
from
> outside of RS (from Query Analyzer).
> Am I right in assuming the time taken is for the report to come up and
> prompt for the parameters? It is not when you try to then view the report
is
> it?
> Try a report that does nothing except have the report parameters based on
> the queries. This would help pinpoint if it is the parameters or something
> else.
> Bruce L-C
>
> "alien2_51" <dan.billow"at"n.o.s.p.a.m.monacocoach.commercialversion>
wrote
> in message news:%2392SqZrjEHA.3608@.TK2MSFTNGP09.phx.gbl...
> > Hi list,
> >
> > This is the second time I've posted this question, the second time I've
> > created a report with "From Query" parameters and the second time I've
had
> > problems with performance with the "From Query" parameters...
> >
>
http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&threadm=eOTaQ1ufEHA.3024%40TK2MSFTNGP10.phx.gbl&rnum=1&prev=/groups%3Fq%3DFrom%2BQuery%2Bparameters%2Bslows%2Bquery%2Bmicrosoft.public.sqlserver.reportingsvcs%26ie%3DUTF-8%26hl%3Den%26btnG%3DGoogle%2BSearch
> >
> > Here's the question I posed before, unfortunately I've recieved no
> > response.. Can someone help, has anyone else noticed the same
problem...?
> > The problem I'm having is very easy to reproduce...
> >
> > On the main menu in report designer... In the Report->Report Parameters
> > property page there are two types of
> > parameters Non-Queried and From Query. Non-Queried appears to prompt the
> > user for its value while the From Query parameter appears to be
populated
> > from the query as it's name implies by assigning a column from the
> dataset.
> > I've created several of these type of parameters because I need a way to
> > have a page header with data from the query repeat on every page since I
> > cannot put Fields in the page header and using a table with the
> > repeatonnewpage doesn't seem to work this was my only viable solution.
If
> > you look at my posts for the last couple days you'll get an idea of what
> I'm
> > trying to do. So my question remains why would creating From Query
> > parameters slow the execution of my report...? Am I using them
incorrectly
> > is there another direction I can take to to achieve my goal...
> >
> > Thanks,
> >
> > Dan
> >
> >
> >
>|||One other issue to look at as well is whether the performance issue is seen
in only the development environment versus both development and after
deployment. That would be one other issue to consider.
Bruce L-C
"alien2_51" <dan.billow"at"n.o.s.p.a.m.monacocoach.commercialversion> wrote
in message news:uxjpJzrjEHA.712@.TK2MSFTNGP09.phx.gbl...
> Hi Bruce,
> Thanks for your reply, in this particular report I have 47 "From Query"
> parameters which is less than the previous report I built which seemed to
> take longer. For the report I'm working on now I have a header and a
> subreport, the header returns one row of data and contains the from query
> parameters. Strangely enough the troubles I was having with the header
> report have seemed to clear up, although the first couple of times I tried
> to run the report the report previewer indicated that my report had been
> canceled and then I would click the View Report button again and the
report
> would run. My first report returned several rows of data but the data was
> repeated for every line of detail this is the one that I'm continually
> having performance problems with I'm assigning the repeated data to the
From
> Query parameters.
> Dan
>
>
>
> "Bruce Loehle-Conger" <bruce_lcNOSPAM@.hotmail.com> wrote in message
> news:OO4WMgrjEHA.3392@.TK2MSFTNGP15.phx.gbl...
> > I use this all the time without any performance problem whatsoever. A
few
> > questions for you. How many parameters do you have in the report? How
many
> > rows are being returned? How long does it take for each of them to run
> from
> > outside of RS (from Query Analyzer).
> >
> > Am I right in assuming the time taken is for the report to come up and
> > prompt for the parameters? It is not when you try to then view the
report
> is
> > it?
> >
> > Try a report that does nothing except have the report parameters based
on
> > the queries. This would help pinpoint if it is the parameters or
something
> > else.
> >
> > Bruce L-C
> >
> >
> > "alien2_51" <dan.billow"at"n.o.s.p.a.m.monacocoach.commercialversion>
> wrote
> > in message news:%2392SqZrjEHA.3608@.TK2MSFTNGP09.phx.gbl...
> > > Hi list,
> > >
> > > This is the second time I've posted this question, the second time
I've
> > > created a report with "From Query" parameters and the second time I've
> had
> > > problems with performance with the "From Query" parameters...
> > >
> >
>
http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&threadm=eOTaQ1ufEHA.3024%40TK2MSFTNGP10.phx.gbl&rnum=1&prev=/groups%3Fq%3DFrom%2BQuery%2Bparameters%2Bslows%2Bquery%2Bmicrosoft.public.sqlserver.reportingsvcs%26ie%3DUTF-8%26hl%3Den%26btnG%3DGoogle%2BSearch
> > >
> > > Here's the question I posed before, unfortunately I've recieved no
> > > response.. Can someone help, has anyone else noticed the same
> problem...?
> > > The problem I'm having is very easy to reproduce...
> > >
> > > On the main menu in report designer... In the Report->Report
Parameters
> > > property page there are two types of
> > > parameters Non-Queried and From Query. Non-Queried appears to prompt
the
> > > user for its value while the From Query parameter appears to be
> populated
> > > from the query as it's name implies by assigning a column from the
> > dataset.
> > > I've created several of these type of parameters because I need a way
to
> > > have a page header with data from the query repeat on every page since
I
> > > cannot put Fields in the page header and using a table with the
> > > repeatonnewpage doesn't seem to work this was my only viable solution.
> If
> > > you look at my posts for the last couple days you'll get an idea of
what
> > I'm
> > > trying to do. So my question remains why would creating From Query
> > > parameters slow the execution of my report...? Am I using them
> incorrectly
> > > is there another direction I can take to to achieve my goal...
> > >
> > > Thanks,
> > >
> > > Dan
> > >
> > >
> > >
> >
> >
>|||I think the entire query is first run without parameters. Then from the
results of the query, the parameters are retrieved making them available for
selection. That would be why you would see a performance issue, especially
with larger sets of data.
"alien2_51" <dan.billow"at"n.o.s.p.a.m.monacocoach.commercialversion> wrote
in message news:%2392SqZrjEHA.3608@.TK2MSFTNGP09.phx.gbl...
> Hi list,
> This is the second time I've posted this question, the second time I've
> created a report with "From Query" parameters and the second time I've had
> problems with performance with the "From Query" parameters...
>
http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&threadm=eOTaQ1ufEHA.3024%40TK2MSFTNGP10.phx.gbl&rnum=1&prev=/groups%3Fq%3DFrom%2BQuery%2Bparameters%2Bslows%2Bquery%2Bmicrosoft.public.sqlserver.reportingsvcs%26ie%3DUTF-8%26hl%3Den%26btnG%3DGoogle%2BSearch
> Here's the question I posed before, unfortunately I've recieved no
> response.. Can someone help, has anyone else noticed the same problem...?
> The problem I'm having is very easy to reproduce...
> On the main menu in report designer... In the Report->Report Parameters
> property page there are two types of
> parameters Non-Queried and From Query. Non-Queried appears to prompt the
> user for its value while the From Query parameter appears to be populated
> from the query as it's name implies by assigning a column from the
dataset.
> I've created several of these type of parameters because I need a way to
> have a page header with data from the query repeat on every page since I
> cannot put Fields in the page header and using a table with the
> repeatonnewpage doesn't seem to work this was my only viable solution. If
> you look at my posts for the last couple days you'll get an idea of what
I'm
> trying to do. So my question remains why would creating From Query
> parameters slow the execution of my report...? Am I using them incorrectly
> is there another direction I can take to to achieve my goal...
> Thanks,
> Dan
>
>

added article to current transactional repl. How to replicate additional article?

Hi, I already have transactional replication setup between two servers
(production server to reporting server).
I have created a new table witha primary key and have added this table as
an article to the publication.
How can i get my setup to take an initial snapshot of the table and apply it
to the reporting server?
Any help most appreciated!
thanks
John
John,
you will need to run the snapshot agent then synchronize. The whole process
from start to finish is:
exec sp_addarticle @.publication = 'tTestFNames'
, @.article = 'tEmployees'
, @.source_table = 'tEmployees'
exec sp_addsubscription @.publication = 'tTestFNames'
, @.article = 'tEmployees'
, @.subscriber = 'RSCOMPUTER'
, @.destination_db = 'testrep'
Start snapshot agent, start distribuition agent.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Thanks Paul,
Are you saying that I have to take a snapshot of the whole database all over
again?
I thought I could take a snapshot of just the article that has been newely
added to the publication?
In actual fact, the 'Last Action' column on my agent (subsicriber' is saying
"The initial snapshot for article 'myTable' is not yet available"
Many thanks!
John
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:OQ2XvSVEGHA.4000@.TK2MSFTNGP10.phx.gbl...
> John,
> you will need to run the snapshot agent then synchronize. The whole
> process from start to finish is:
> exec sp_addarticle @.publication = 'tTestFNames'
> , @.article = 'tEmployees'
> , @.source_table = 'tEmployees'
> exec sp_addsubscription @.publication = 'tTestFNames'
> , @.article = 'tEmployees'
> , @.subscriber = 'RSCOMPUTER'
> , @.destination_db = 'testrep'
> Start snapshot agent, start distribuition agent.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||John,
if you add a new table and subscription to that table, running the snapshot
agent won't create a complete snapshot - just the new article.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

Add unique id column - newbie

I have a table (inherited) that gets data from a DTS package. The problem i
s
that the table that the data is being read in to wasn't created with a uniqu
e
id field. Is there a way that I can add a unique id field and have it
populated?
This table has 86000 records in it already.
TIAThe following article describes how to choose and implement a primary
(unique) key
http://www.aspfaq.com/show.asp?id=2504
"ks" <ks@.discussions.microsoft.com> wrote in message
news:B4F3293A-D2C2-4C67-9772-164E5693D467@.microsoft.com...
>I have a table (inherited) that gets data from a DTS package. The problem
>is
> that the table that the data is being read in to wasn't created with a
> unique
> id field. Is there a way that I can add a unique id field and have it
> populated?
> This table has 86000 records in it already.
> TIA

Add table to view: only showing (All Columns)

Hi all,
I have a database created a few years ago by another team.It has gone
through upgrades and is currently SQL Server 2000 SP3 on Windows 2000 SP4.
It is performing normally, with one annoying exception:
When I use Enterprise Manager's view designer to create a new view or look
at an existing view, the tables always show only 1 line: * (All Columns).
The individual column names are not showing.This is true for all tables and
views, including a new test table I just created.
The problem only occurs with this database. It also occurs on other servers.
DBCC CHECKDB did not report anything unusual.
What can I do to make the column names appear again? Please let me know if
I should report any other information to diagnose this issue.
Thanks,
-Tom.
P.S.:
I *can* force columns to show with a syntax like this:
SELECT *
FROM (SELECT Col1, Col2
FROM tblTest) DERIVEDTBL
but that's hardly what I should have to do.Is it only the view/query designer which has this problem? Perhaps there's
some strange database setting in play here... What is the compatibility
level of the database?
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Tom van Stiphout" <no.spam.tvanstiphout@.kinetik-it.com> wrote in message
news:1027hviq64k8e35@.corp.supernews.com...
> Hi all,
> I have a database created a few years ago by another team.It has gone
> through upgrades and is currently SQL Server 2000 SP3 on Windows 2000 SP4.
> It is performing normally, with one annoying exception:
> When I use Enterprise Manager's view designer to create a new view or look
> at an existing view, the tables always show only 1 line: * (All Columns).
> The individual column names are not showing.This is true for all tables
and
> views, including a new test table I just created.
> The problem only occurs with this database. It also occurs on other
servers.
> DBCC CHECKDB did not report anything unusual.
> What can I do to make the column names appear again? Please let me know
if
> I should report any other information to diagnose this issue.
> Thanks,
> -Tom.
> P.S.:
> I *can* force columns to show with a syntax like this:
> SELECT *
> FROM (SELECT Col1, Col2
> FROM tblTest) DERIVEDTBL
> but that's hardly what I should have to do.
>|||Hi Tibor,
"only"? What other viewer are you referring to? The Diagrams are normal.
Compatibility level is 80 for all databases, as reported by sp_helpdb.
-Tom.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:ODekOPN7DHA.2392@.TK2MSFTNGP11.phx.gbl...
> Is it only the view/query designer which has this problem? Perhaps there's
> some strange database setting in play here... What is the compatibility
> level of the database?
> --
> Tibor Karaszi, SQL Server MVP
> Archive at:
>
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
>
> "Tom van Stiphout" <no.spam.tvanstiphout@.kinetik-it.com> wrote in message
> news:1027hviq64k8e35@.corp.supernews.com...
> > Hi all,
> > I have a database created a few years ago by another team.It has gone
> > through upgrades and is currently SQL Server 2000 SP3 on Windows 2000
SP4.
> > It is performing normally, with one annoying exception:
> > When I use Enterprise Manager's view designer to create a new view or
look
> > at an existing view, the tables always show only 1 line: * (All
Columns).
> > The individual column names are not showing.This is true for all tables
> and
> > views, including a new test table I just created.
> > The problem only occurs with this database. It also occurs on other
> servers.
> > DBCC CHECKDB did not report anything unusual.
> > What can I do to make the column names appear again? Please let me know
> if
> > I should report any other information to diagnose this issue.
> > Thanks,
> > -Tom.
> >
> > P.S.:
> > I *can* force columns to show with a syntax like this:
> > SELECT *
> > FROM (SELECT Col1, Col2
> > FROM tblTest) DERIVEDTBL
> > but that's hardly what I should have to do.
> >
> >
>|||No viewer in particular, just other GUI tools in general. I'm trying to
pinpoint what might be the problem. So, you are saying that you run EM on a
machine. From that machine, the query builder doesn't show the column names
for the tables in one of your databases, even if you create new tables. But
of you create another database and create tables in that database, you will
see the column in that. Is this a correct description?
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Tom van Stiphout" <no.spam.tvanstiphout@.kinetik-it.com> wrote in message
news:1027j8k4od20t23@.corp.supernews.com...
> Hi Tibor,
> "only"? What other viewer are you referring to? The Diagrams are normal.
> Compatibility level is 80 for all databases, as reported by sp_helpdb.
> -Tom.
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
in
> message news:ODekOPN7DHA.2392@.TK2MSFTNGP11.phx.gbl...
> > Is it only the view/query designer which has this problem? Perhaps
there's
> > some strange database setting in play here... What is the compatibility
> > level of the database?
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > Archive at:
> >
>
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
> >
> >
> > "Tom van Stiphout" <no.spam.tvanstiphout@.kinetik-it.com> wrote in
message
> > news:1027hviq64k8e35@.corp.supernews.com...
> > > Hi all,
> > > I have a database created a few years ago by another team.It has gone
> > > through upgrades and is currently SQL Server 2000 SP3 on Windows 2000
> SP4.
> > > It is performing normally, with one annoying exception:
> > > When I use Enterprise Manager's view designer to create a new view or
> look
> > > at an existing view, the tables always show only 1 line: * (All
> Columns).
> > > The individual column names are not showing.This is true for all
tables
> > and
> > > views, including a new test table I just created.
> > > The problem only occurs with this database. It also occurs on other
> > servers.
> > > DBCC CHECKDB did not report anything unusual.
> > > What can I do to make the column names appear again? Please let me
know
> > if
> > > I should report any other information to diagnose this issue.
> > > Thanks,
> > > -Tom.
> > >
> > > P.S.:
> > > I *can* force columns to show with a syntax like this:
> > > SELECT *
> > > FROM (SELECT Col1, Col2
> > > FROM tblTest) DERIVEDTBL
> > > but that's hardly what I should have to do.
> > >
> > >
> >
> >
>|||Yes, that's correct. But note that I created this db from a backup from my
ISP's SQL Server. I'm assuming that the imperfections came with the backup
and are not native to my Server. At the ISP they see the same behavior.
Additionally, if I use an Access 2000 ADP to create a view or inspect an
existing one, I get the same behavior.
-Tom.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23eqDG$N7DHA.712@.tk2msftngp13.phx.gbl...
> No viewer in particular, just other GUI tools in general. I'm trying to
> pinpoint what might be the problem. So, you are saying that you run EM on
a
> machine. From that machine, the query builder doesn't show the column
names
> for the tables in one of your databases, even if you create new tables.
But
> of you create another database and create tables in that database, you
will
> see the column in that. Is this a correct description?
> --
> Tibor Karaszi, SQL Server MVP
> Archive at:
>
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
>
> "Tom van Stiphout" <no.spam.tvanstiphout@.kinetik-it.com> wrote in message
> news:1027j8k4od20t23@.corp.supernews.com...
> > Hi Tibor,
> > "only"? What other viewer are you referring to? The Diagrams are
normal.
> > Compatibility level is 80 for all databases, as reported by sp_helpdb.
> > -Tom.
> >
> >
> > "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
> in
> > message news:ODekOPN7DHA.2392@.TK2MSFTNGP11.phx.gbl...
> > > Is it only the view/query designer which has this problem? Perhaps
> there's
> > > some strange database setting in play here... What is the
compatibility
> > > level of the database?
> > >
> > > --
> > > Tibor Karaszi, SQL Server MVP
> > > Archive at:
> > >
> >
>
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
> > >
> > >
> > > "Tom van Stiphout" <no.spam.tvanstiphout@.kinetik-it.com> wrote in
> message
> > > news:1027hviq64k8e35@.corp.supernews.com...
> > > > Hi all,
> > > > I have a database created a few years ago by another team.It has
gone
> > > > through upgrades and is currently SQL Server 2000 SP3 on Windows
2000
> > SP4.
> > > > It is performing normally, with one annoying exception:
> > > > When I use Enterprise Manager's view designer to create a new view
or
> > look
> > > > at an existing view, the tables always show only 1 line: * (All
> > Columns).
> > > > The individual column names are not showing.This is true for all
> tables
> > > and
> > > > views, including a new test table I just created.
> > > > The problem only occurs with this database. It also occurs on other
> > > servers.
> > > > DBCC CHECKDB did not report anything unusual.
> > > > What can I do to make the column names appear again? Please let me
> know
> > > if
> > > > I should report any other information to diagnose this issue.
> > > > Thanks,
> > > > -Tom.
> > > >
> > > > P.S.:
> > > > I *can* force columns to show with a syntax like this:
> > > > SELECT *
> > > > FROM (SELECT Col1, Col2
> > > > FROM tblTest) DERIVEDTBL
> > > > but that's hardly what I should have to do.
> > > >
> > > >
> > >
> > >
> >
> >
>|||OK, the next thing I'd do is to check all the database options and see if
there are any differences between a "working" database and this one.
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Tom van Stiphout" <no.spam.tvanstiphout@.kinetik-it.com> wrote in message
news:1027pld4sts7g4e@.corp.supernews.com...
> Yes, that's correct. But note that I created this db from a backup from my
> ISP's SQL Server. I'm assuming that the imperfections came with the backup
> and are not native to my Server. At the ISP they see the same behavior.
> Additionally, if I use an Access 2000 ADP to create a view or inspect an
> existing one, I get the same behavior.
> -Tom.
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
in
> message news:%23eqDG$N7DHA.712@.tk2msftngp13.phx.gbl...
> > No viewer in particular, just other GUI tools in general. I'm trying to
> > pinpoint what might be the problem. So, you are saying that you run EM
on
> a
> > machine. From that machine, the query builder doesn't show the column
> names
> > for the tables in one of your databases, even if you create new tables.
> But
> > of you create another database and create tables in that database, you
> will
> > see the column in that. Is this a correct description?
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > Archive at:
> >
>
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
> >
> >
> > "Tom van Stiphout" <no.spam.tvanstiphout@.kinetik-it.com> wrote in
message
> > news:1027j8k4od20t23@.corp.supernews.com...
> > > Hi Tibor,
> > > "only"? What other viewer are you referring to? The Diagrams are
> normal.
> > > Compatibility level is 80 for all databases, as reported by sp_helpdb.
> > > -Tom.
> > >
> > >
> > > "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com>
wrote
> > in
> > > message news:ODekOPN7DHA.2392@.TK2MSFTNGP11.phx.gbl...
> > > > Is it only the view/query designer which has this problem? Perhaps
> > there's
> > > > some strange database setting in play here... What is the
> compatibility
> > > > level of the database?
> > > >
> > > > --
> > > > Tibor Karaszi, SQL Server MVP
> > > > Archive at:
> > > >
> > >
> >
>
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
> > > >
> > > >
> > > > "Tom van Stiphout" <no.spam.tvanstiphout@.kinetik-it.com> wrote in
> > message
> > > > news:1027hviq64k8e35@.corp.supernews.com...
> > > > > Hi all,
> > > > > I have a database created a few years ago by another team.It has
> gone
> > > > > through upgrades and is currently SQL Server 2000 SP3 on Windows
> 2000
> > > SP4.
> > > > > It is performing normally, with one annoying exception:
> > > > > When I use Enterprise Manager's view designer to create a new view
> or
> > > look
> > > > > at an existing view, the tables always show only 1 line: * (All
> > > Columns).
> > > > > The individual column names are not showing.This is true for all
> > tables
> > > > and
> > > > > views, including a new test table I just created.
> > > > > The problem only occurs with this database. It also occurs on
other
> > > > servers.
> > > > > DBCC CHECKDB did not report anything unusual.
> > > > > What can I do to make the column names appear again? Please let
me
> > know
> > > > if
> > > > > I should report any other information to diagnose this issue.
> > > > > Thanks,
> > > > > -Tom.
> > > > >
> > > > > P.S.:
> > > > > I *can* force columns to show with a syntax like this:
> > > > > SELECT *
> > > > > FROM (SELECT Col1, Col2
> > > > > FROM tblTest) DERIVEDTBL
> > > > > but that's hardly what I should have to do.
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>|||Hi Tibor,
Thanks for hanging in there with me. You are making some good suggestions.
Not much difference in database options. I ran this:
sp_dboption 'arizonavacationvalues.com'
go
sp_dboption 'pubs'
results:
torn page detection
auto create statistics
auto update statistics
trunc. log on chkpt.
torn page detection
auto create statistics
auto update statistics
-Tom.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:O6A2oYO7DHA.1040@.TK2MSFTNGP10.phx.gbl...
> OK, the next thing I'd do is to check all the database options and see if
> there are any differences between a "working" database and this one.
> --
> Tibor Karaszi, SQL Server MVP
> Archive at:
>
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
>
> "Tom van Stiphout" <no.spam.tvanstiphout@.kinetik-it.com> wrote in message
> news:1027pld4sts7g4e@.corp.supernews.com...
> > Yes, that's correct. But note that I created this db from a backup from
my
> > ISP's SQL Server. I'm assuming that the imperfections came with the
backup
> > and are not native to my Server. At the ISP they see the same behavior.
> >
> > Additionally, if I use an Access 2000 ADP to create a view or inspect an
> > existing one, I get the same behavior.
> >
> > -Tom.
> >
> >
> >
> > "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
> in
> > message news:%23eqDG$N7DHA.712@.tk2msftngp13.phx.gbl...
> > > No viewer in particular, just other GUI tools in general. I'm trying
to
> > > pinpoint what might be the problem. So, you are saying that you run EM
> on
> > a
> > > machine. From that machine, the query builder doesn't show the column
> > names
> > > for the tables in one of your databases, even if you create new
tables.
> > But
> > > of you create another database and create tables in that database, you
> > will
> > > see the column in that. Is this a correct description?
> > >
> > > --
> > > Tibor Karaszi, SQL Server MVP
> > > Archive at:
> > >
> >
>
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
> > >
> > >
> > > "Tom van Stiphout" <no.spam.tvanstiphout@.kinetik-it.com> wrote in
> message
> > > news:1027j8k4od20t23@.corp.supernews.com...
> > > > Hi Tibor,
> > > > "only"? What other viewer are you referring to? The Diagrams are
> > normal.
> > > > Compatibility level is 80 for all databases, as reported by
sp_helpdb.
> > > > -Tom.
> > > >
> > > >
> > > > "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com>
> wrote
> > > in
> > > > message news:ODekOPN7DHA.2392@.TK2MSFTNGP11.phx.gbl...
> > > > > Is it only the view/query designer which has this problem? Perhaps
> > > there's
> > > > > some strange database setting in play here... What is the
> > compatibility
> > > > > level of the database?
> > > > >
> > > > > --
> > > > > Tibor Karaszi, SQL Server MVP
> > > > > Archive at:
> > > > >
> > > >
> > >
> >
>
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
> > > > >
> > > > >
> > > > > "Tom van Stiphout" <no.spam.tvanstiphout@.kinetik-it.com> wrote in
> > > message
> > > > > news:1027hviq64k8e35@.corp.supernews.com...
> > > > > > Hi all,
> > > > > > I have a database created a few years ago by another team.It has
> > gone
> > > > > > through upgrades and is currently SQL Server 2000 SP3 on Windows
> > 2000
> > > > SP4.
> > > > > > It is performing normally, with one annoying exception:
> > > > > > When I use Enterprise Manager's view designer to create a new
view
> > or
> > > > look
> > > > > > at an existing view, the tables always show only 1 line: * (All
> > > > Columns).
> > > > > > The individual column names are not showing.This is true for all
> > > tables
> > > > > and
> > > > > > views, including a new test table I just created.
> > > > > > The problem only occurs with this database. It also occurs on
> other
> > > > > servers.
> > > > > > DBCC CHECKDB did not report anything unusual.
> > > > > > What can I do to make the column names appear again? Please let
> me
> > > know
> > > > > if
> > > > > > I should report any other information to diagnose this issue.
> > > > > > Thanks,
> > > > > > -Tom.
> > > > > >
> > > > > > P.S.:
> > > > > > I *can* force columns to show with a syntax like this:
> > > > > > SELECT *
> > > > > > FROM (SELECT Col1, Col2
> > > > > > FROM tblTest) DERIVEDTBL
> > > > > > but that's hardly what I should have to do.
> > > > > >
> > > > > >
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>|||I had this happen several years ago. It turned out that someone had put a
stored procedure in the database that was the same name as the stored
procedure that Enterprise Manager uses to get the Field Names. Since SQL
will pull the stored proc from the current database before it tries the
master, it was messing up Enterprise Manager. I'm sorry, I don't recall the
name of the stored proc, but maybe this will give you a starting place.
"Tom van Stiphout" <no.spam.tvanstiphout@.kinetik-it.com> wrote in message
news:1027hviq64k8e35@.corp.supernews.com...
> Hi all,
> I have a database created a few years ago by another team.It has gone
> through upgrades and is currently SQL Server 2000 SP3 on Windows 2000 SP4.
> It is performing normally, with one annoying exception:
> When I use Enterprise Manager's view designer to create a new view or look
> at an existing view, the tables always show only 1 line: * (All Columns).
> The individual column names are not showing.This is true for all tables
and
> views, including a new test table I just created.
> The problem only occurs with this database. It also occurs on other
servers.
> DBCC CHECKDB did not report anything unusual.
> What can I do to make the column names appear again? Please let me know
if
> I should report any other information to diagnose this issue.
> Thanks,
> -Tom.
> P.S.:
> I *can* force columns to show with a syntax like this:
> SELECT *
> FROM (SELECT Col1, Col2
> FROM tblTest) DERIVEDTBL
> but that's hardly what I should have to do.
>|||Hmm, I think I'm out of ideas, then (or I need to get some sleep). However,
Joe has a very intriguing theory!
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Tom van Stiphout" <no.spam.tvanstiphout@.kinetik-it.com> wrote in message
news:1027u48lpb6iac4@.corp.supernews.com...
> Hi Tibor,
> Thanks for hanging in there with me. You are making some good suggestions.
> Not much difference in database options. I ran this:
> sp_dboption 'arizonavacationvalues.com'
> go
> sp_dboption 'pubs'
> results:
> torn page detection
> auto create statistics
> auto update statistics
> trunc. log on chkpt.
> torn page detection
> auto create statistics
> auto update statistics
> -Tom.
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
in
> message news:O6A2oYO7DHA.1040@.TK2MSFTNGP10.phx.gbl...
> > OK, the next thing I'd do is to check all the database options and see
if
> > there are any differences between a "working" database and this one.
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > Archive at:
> >
>
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
> >
> >
> > "Tom van Stiphout" <no.spam.tvanstiphout@.kinetik-it.com> wrote in
message
> > news:1027pld4sts7g4e@.corp.supernews.com...
> > > Yes, that's correct. But note that I created this db from a backup
from
> my
> > > ISP's SQL Server. I'm assuming that the imperfections came with the
> backup
> > > and are not native to my Server. At the ISP they see the same
behavior.
> > >
> > > Additionally, if I use an Access 2000 ADP to create a view or inspect
an
> > > existing one, I get the same behavior.
> > >
> > > -Tom.
> > >
> > >
> > >
> > > "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com>
wrote
> > in
> > > message news:%23eqDG$N7DHA.712@.tk2msftngp13.phx.gbl...
> > > > No viewer in particular, just other GUI tools in general. I'm trying
> to
> > > > pinpoint what might be the problem. So, you are saying that you run
EM
> > on
> > > a
> > > > machine. From that machine, the query builder doesn't show the
column
> > > names
> > > > for the tables in one of your databases, even if you create new
> tables.
> > > But
> > > > of you create another database and create tables in that database,
you
> > > will
> > > > see the column in that. Is this a correct description?
> > > >
> > > > --
> > > > Tibor Karaszi, SQL Server MVP
> > > > Archive at:
> > > >
> > >
> >
>
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
> > > >
> > > >
> > > > "Tom van Stiphout" <no.spam.tvanstiphout@.kinetik-it.com> wrote in
> > message
> > > > news:1027j8k4od20t23@.corp.supernews.com...
> > > > > Hi Tibor,
> > > > > "only"? What other viewer are you referring to? The Diagrams are
> > > normal.
> > > > > Compatibility level is 80 for all databases, as reported by
> sp_helpdb.
> > > > > -Tom.
> > > > >
> > > > >
> > > > > "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com>
> > wrote
> > > > in
> > > > > message news:ODekOPN7DHA.2392@.TK2MSFTNGP11.phx.gbl...
> > > > > > Is it only the view/query designer which has this problem?
Perhaps
> > > > there's
> > > > > > some strange database setting in play here... What is the
> > > compatibility
> > > > > > level of the database?
> > > > > >
> > > > > > --
> > > > > > Tibor Karaszi, SQL Server MVP
> > > > > > Archive at:
> > > > > >
> > > > >
> > > >
> > >
> >
>
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
> > > > > >
> > > > > >
> > > > > > "Tom van Stiphout" <no.spam.tvanstiphout@.kinetik-it.com> wrote
in
> > > > message
> > > > > > news:1027hviq64k8e35@.corp.supernews.com...
> > > > > > > Hi all,
> > > > > > > I have a database created a few years ago by another team.It
has
> > > gone
> > > > > > > through upgrades and is currently SQL Server 2000 SP3 on
Windows
> > > 2000
> > > > > SP4.
> > > > > > > It is performing normally, with one annoying exception:
> > > > > > > When I use Enterprise Manager's view designer to create a new
> view
> > > or
> > > > > look
> > > > > > > at an existing view, the tables always show only 1 line: *
(All
> > > > > Columns).
> > > > > > > The individual column names are not showing.This is true for
all
> > > > tables
> > > > > > and
> > > > > > > views, including a new test table I just created.
> > > > > > > The problem only occurs with this database. It also occurs on
> > other
> > > > > > servers.
> > > > > > > DBCC CHECKDB did not report anything unusual.
> > > > > > > What can I do to make the column names appear again? Please
let
> > me
> > > > know
> > > > > > if
> > > > > > > I should report any other information to diagnose this issue.
> > > > > > > Thanks,
> > > > > > > -Tom.
> > > > > > >
> > > > > > > P.S.:
> > > > > > > I *can* force columns to show with a syntax like this:
> > > > > > > SELECT *
> > > > > > > FROM (SELECT Col1, Col2
> > > > > > > FROM tblTest) DERIVEDTBL
> > > > > > > but that's hardly what I should have to do.
> > > > > > >
> > > > > > >
> > > > > >
> > > > > >
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>|||Hi Joe,
Thanks for this neat idea.
Alas:
select * from sysobjects
where [name] in (select [name] from master..sysobjects)
only results in 21 sysxxxx tables.
-Tom.
"Joe Jackson" <jj@.microsoft.com> wrote in message
news:uNoPTyP7DHA.1636@.TK2MSFTNGP12.phx.gbl...
> I had this happen several years ago. It turned out that someone had put a
> stored procedure in the database that was the same name as the stored
> procedure that Enterprise Manager uses to get the Field Names. Since SQL
> will pull the stored proc from the current database before it tries the
> master, it was messing up Enterprise Manager. I'm sorry, I don't recall
the
> name of the stored proc, but maybe this will give you a starting place.
>
> "Tom van Stiphout" <no.spam.tvanstiphout@.kinetik-it.com> wrote in message
> news:1027hviq64k8e35@.corp.supernews.com...
> > Hi all,
> > I have a database created a few years ago by another team.It has gone
> > through upgrades and is currently SQL Server 2000 SP3 on Windows 2000
SP4.
> > It is performing normally, with one annoying exception:
> > When I use Enterprise Manager's view designer to create a new view or
look
> > at an existing view, the tables always show only 1 line: * (All
Columns).
> > The individual column names are not showing.This is true for all tables
> and
> > views, including a new test table I just created.
> > The problem only occurs with this database. It also occurs on other
> servers.
> > DBCC CHECKDB did not report anything unusual.
> > What can I do to make the column names appear again? Please let me know
> if
> > I should report any other information to diagnose this issue.
> > Thanks,
> > -Tom.
> >
> > P.S.:
> > I *can* force columns to show with a syntax like this:
> > SELECT *
> > FROM (SELECT Col1, Col2
> > FROM tblTest) DERIVEDTBL
> > but that's hardly what I should have to do.
> >
> >
>

Add table to view: only showing (All Columns)

Hi all,
I have a database created a few years ago by another team.It has gone
through upgrades and is currently SQL Server 2000 SP3 on Windows 2000 SP4.
It is performing normally, with one annoying exception:
When I use Enterprise Manager's view designer to create a new view or look
at an existing view, the tables always show only 1 line: * (All Columns).
The individual column names are not showing.This is true for all tables and
views, including a new test table I just created.
The problem only occurs with this database. It also occurs on other servers.
DBCC CHECKDB did not report anything unusual.
What can I do to make the column names appear again? Please let me know if
I should report any other information to diagnose this issue.
Thanks,
-Tom.
P.S.:
I *can* force columns to show with a syntax like this:
SELECT *
FROM (SELECT Col1, Col2
FROM tblTest) DERIVEDTBL
but that's hardly what I should have to do.Is it only the view/query designer which has this problem? Perhaps there's
some strange database setting in play here... What is the compatibility
level of the database?
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=...ublic.sqlserver
"Tom van Stiphout" <no.spam.tvanstiphout@.kinetik-it.com> wrote in message
news:1027hviq64k8e35@.corp.supernews.com...
> Hi all,
> I have a database created a few years ago by another team.It has gone
> through upgrades and is currently SQL Server 2000 SP3 on Windows 2000 SP4.
> It is performing normally, with one annoying exception:
> When I use Enterprise Manager's view designer to create a new view or look
> at an existing view, the tables always show only 1 line: * (All Columns).
> The individual column names are not showing.This is true for all tables
and
> views, including a new test table I just created.
> The problem only occurs with this database. It also occurs on other
servers.
> DBCC CHECKDB did not report anything unusual.
> What can I do to make the column names appear again? Please let me know
if
> I should report any other information to diagnose this issue.
> Thanks,
> -Tom.
> P.S.:
> I *can* force columns to show with a syntax like this:
> SELECT *
> FROM (SELECT Col1, Col2
> FROM tblTest) DERIVEDTBL
> but that's hardly what I should have to do.
>|||Hi Tibor,
"only"? What other viewer are you referring to? The Diagrams are normal.
Compatibility level is 80 for all databases, as reported by sp_helpdb.
-Tom.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:ODekOPN7DHA.2392@.TK2MSFTNGP11.phx.gbl...
> Is it only the view/query designer which has this problem? Perhaps there's
> some strange database setting in play here... What is the compatibility
> level of the database?
> --
> Tibor Karaszi, SQL Server MVP
> Archive at:
>
http://groups.google.com/groups?oi=...ublic.sqlserver
>
> "Tom van Stiphout" <no.spam.tvanstiphout@.kinetik-it.com> wrote in message
> news:1027hviq64k8e35@.corp.supernews.com...
SP4.
look
Columns).
> and
> servers.
> if
>|||No viewer in particular, just other GUI tools in general. I'm trying to
pinpoint what might be the problem. So, you are saying that you run EM on a
machine. From that machine, the query builder doesn't show the column names
for the tables in one of your databases, even if you create new tables. But
of you create another database and create tables in that database, you will
see the column in that. Is this a correct description?
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=...ublic.sqlserver
"Tom van Stiphout" <no.spam.tvanstiphout@.kinetik-it.com> wrote in message
news:1027j8k4od20t23@.corp.supernews.com...
> Hi Tibor,
> "only"? What other viewer are you referring to? The Diagrams are normal.
> Compatibility level is 80 for all databases, as reported by sp_helpdb.
> -Tom.
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
in
> message news:ODekOPN7DHA.2392@.TK2MSFTNGP11.phx.gbl...
there's
>
http://groups.google.com/groups?oi=...ublic.sqlserver
message
> SP4.
> look
> Columns).
tables
know
>|||Yes, that's correct. But note that I created this db from a backup from my
ISP's SQL Server. I'm assuming that the imperfections came with the backup
and are not native to my Server. At the ISP they see the same behavior.
Additionally, if I use an Access 2000 ADP to create a view or inspect an
existing one, I get the same behavior.
-Tom.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23eqDG$N7DHA.712@.tk2msftngp13.phx.gbl...
> No viewer in particular, just other GUI tools in general. I'm trying to
> pinpoint what might be the problem. So, you are saying that you run EM on
a
> machine. From that machine, the query builder doesn't show the column
names
> for the tables in one of your databases, even if you create new tables.
But
> of you create another database and create tables in that database, you
will
> see the column in that. Is this a correct description?
> --
> Tibor Karaszi, SQL Server MVP
> Archive at:
>
http://groups.google.com/groups?oi=...ublic.sqlserver
>
> "Tom van Stiphout" <no.spam.tvanstiphout@.kinetik-it.com> wrote in message
> news:1027j8k4od20t23@.corp.supernews.com...
normal.
> in
> there's
compatibility
>
http://groups.google.com/groups?oi=...ublic.sqlserver
> message
gone
2000
or
> tables
> know
>|||OK, the next thing I'd do is to check all the database options and see if
there are any differences between a "working" database and this one.
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=...ublic.sqlserver
"Tom van Stiphout" <no.spam.tvanstiphout@.kinetik-it.com> wrote in message
news:1027pld4sts7g4e@.corp.supernews.com...
> Yes, that's correct. But note that I created this db from a backup from my
> ISP's SQL Server. I'm assuming that the imperfections came with the backup
> and are not native to my Server. At the ISP they see the same behavior.
> Additionally, if I use an Access 2000 ADP to create a view or inspect an
> existing one, I get the same behavior.
> -Tom.
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
in
> message news:%23eqDG$N7DHA.712@.tk2msftngp13.phx.gbl...
on
> a
> names
> But
> will
>
http://groups.google.com/groups?oi=...ublic.sqlserver
message
> normal.
wrote
> compatibility
>
http://groups.google.com/groups?oi=...ublic.sqlserver
> gone
> 2000
> or
other
me
>|||Hi Tibor,
Thanks for hanging in there with me. You are making some good suggestions.
Not much difference in database options. I ran this:
sp_dboption 'arizonavacationvalues.com'
go
sp_dboption 'pubs'
results:
torn page detection
auto create statistics
auto update statistics
trunc. log on chkpt.
torn page detection
auto create statistics
auto update statistics
-Tom.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:O6A2oYO7DHA.1040@.TK2MSFTNGP10.phx.gbl...
> OK, the next thing I'd do is to check all the database options and see if
> there are any differences between a "working" database and this one.
> --
> Tibor Karaszi, SQL Server MVP
> Archive at:
>
http://groups.google.com/groups?oi=...ublic.sqlserver
>
> "Tom van Stiphout" <no.spam.tvanstiphout@.kinetik-it.com> wrote in message
> news:1027pld4sts7g4e@.corp.supernews.com...
my
backup
> in
to
> on
tables.
>
http://groups.google.com/groups?oi=...ublic.sqlserver
> message
sp_helpdb.
> wrote
>
http://groups.google.com/groups?oi=...ublic.sqlserver
view
> other
> me
>|||I had this happen several years ago. It turned out that someone had put a
stored procedure in the database that was the same name as the stored
procedure that Enterprise Manager uses to get the Field Names. Since SQL
will pull the stored proc from the current database before it tries the
master, it was messing up Enterprise Manager. I'm sorry, I don't recall the
name of the stored proc, but maybe this will give you a starting place.
"Tom van Stiphout" <no.spam.tvanstiphout@.kinetik-it.com> wrote in message
news:1027hviq64k8e35@.corp.supernews.com...
> Hi all,
> I have a database created a few years ago by another team.It has gone
> through upgrades and is currently SQL Server 2000 SP3 on Windows 2000 SP4.
> It is performing normally, with one annoying exception:
> When I use Enterprise Manager's view designer to create a new view or look
> at an existing view, the tables always show only 1 line: * (All Columns).
> The individual column names are not showing.This is true for all tables
and
> views, including a new test table I just created.
> The problem only occurs with this database. It also occurs on other
servers.
> DBCC CHECKDB did not report anything unusual.
> What can I do to make the column names appear again? Please let me know
if
> I should report any other information to diagnose this issue.
> Thanks,
> -Tom.
> P.S.:
> I *can* force columns to show with a syntax like this:
> SELECT *
> FROM (SELECT Col1, Col2
> FROM tblTest) DERIVEDTBL
> but that's hardly what I should have to do.
>|||Hmm, I think I'm out of ideas, then (or I need to get some sleep). However,
Joe has a very intriguing theory!
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=...ublic.sqlserver
"Tom van Stiphout" <no.spam.tvanstiphout@.kinetik-it.com> wrote in message
news:1027u48lpb6iac4@.corp.supernews.com...
> Hi Tibor,
> Thanks for hanging in there with me. You are making some good suggestions.
> Not much difference in database options. I ran this:
> sp_dboption 'arizonavacationvalues.com'
> go
> sp_dboption 'pubs'
> results:
> torn page detection
> auto create statistics
> auto update statistics
> trunc. log on chkpt.
> torn page detection
> auto create statistics
> auto update statistics
> -Tom.
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
in
> message news:O6A2oYO7DHA.1040@.TK2MSFTNGP10.phx.gbl...
if
>
http://groups.google.com/groups?oi=...ublic.sqlserver
message
from
> my
> backup
behavior.
an
wrote
> to
EM
column
> tables.
you
>
http://groups.google.com/groups?oi=...ublic.sqlserver
> sp_helpdb.
Perhaps
>
http://groups.google.com/groups?oi=...ublic.sqlserver
in
has
Windows
> view
(All
all
let
>|||Hi Joe,
Thanks for this neat idea.
Alas:
select * from sysobjects
where [name] in (select [name] from master..sysobjects)
only results in 21 sysxxxx tables.
-Tom.
"Joe Jackson" <jj@.microsoft.com> wrote in message
news:uNoPTyP7DHA.1636@.TK2MSFTNGP12.phx.gbl...
> I had this happen several years ago. It turned out that someone had put a
> stored procedure in the database that was the same name as the stored
> procedure that Enterprise Manager uses to get the Field Names. Since SQL
> will pull the stored proc from the current database before it tries the
> master, it was messing up Enterprise Manager. I'm sorry, I don't recall
the
> name of the stored proc, but maybe this will give you a starting place.
>
> "Tom van Stiphout" <no.spam.tvanstiphout@.kinetik-it.com> wrote in message
> news:1027hviq64k8e35@.corp.supernews.com...
SP4.
look
Columns).
> and
> servers.
> if
>

Add new tables to replication

I have setup transactional replication with no snapshot. I created a new
table on publisher side and I would want the table also to be replicated.
Here is what i did-
- drop the subscription
- create/dts table to subscriber
- create insert update stored procedures at subscriber(i have created them
during testing)
- add article to publication
-recreate subscription
Is there any other way to do it as I donot want my subscription to be
dropped each time as it has some other tables that are replicated
continuously...
Regards,
Ravi
Ravi,
to avoid dropping the subscription: as you did before...
> - create/dts table to subscriber
> - create insert update stored procedures at subscriber(i have created them
> during testing)
> - add article to publication
Then
exec sp_addsubscription @.publication = 'tTestFNames'
, @.article = 'tEmployees'
, @.subscriber = 'RSCOMPUTER'
, @.destination_db = 'testrep'
, @.sync_type = 'none'
ie add the subscription to the particular article.
HTH,
Paul Ibison, SQL Server MVP (www.replicationanswers.com)

Add Linked Server Failed

hi,
i have added a link server but it fails when i try to retrieve data.

SQL server in on the same machine
i have created the another SQL server instance

Main Server: SERVER1
Instance: SERVER1/NewServer

EXEC sp_addlinkedserver @.server = SERVER1/NewServer, @.srvproduct = 'SQL Server'
EXEC sp_addlinkedsrvlogin 'SERVER1/NewServer', false, NULL, 'sa', 'test'

select * from SERVER1/NewServer.db1.dbo.tbl_Prouduct

when i try to run the above query it gives me the error
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near '/'.

anyone have suggestion??
Aditry this
select * from [SERVER1/NewServer].db1.dbo.tbl_Prouduct|||i try this too but its still not working

Add Linked Server Failed

hi,
i have added a link server but it fails when i try to retrieve data.
SQL server in on the same machine
i have created the another SQL server instance
Main Server: SERVER1
Instance: SERVER1/NewServer
EXEC sp_addlinkedserver @.server = SERVER1/NewServer, @.srvproduct = 'SQL
Server'
EXEC sp_addlinkedsrvlogin 'SERVER1/NewServer', false, NULL, 'sa', 'test'
select * from SERVER1/NewServer.db1.dbo.tbl_Prouduct
when i try to run the above query it gives me the error
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near '/'.
anyone have suggestion?
Adi
This was just dicussed this week. The "/" is a special character not
normally allowed in an identifier. Either give the linked server a
"regular" name or delimit the name with brackets - [SERVER1/NewServer]. BOL
uses this same situation as an example.
"Adeel Ahmed" <adeel.ahmed@.pk.softechww.com> wrote in message
news:O0qoJPu0EHA.2876@.TK2MSFTNGP12.phx.gbl...
> hi,
> i have added a link server but it fails when i try to retrieve data.
> SQL server in on the same machine
> i have created the another SQL server instance
> Main Server: SERVER1
> Instance: SERVER1/NewServer
> EXEC sp_addlinkedserver @.server = SERVER1/NewServer, @.srvproduct = 'SQL
> Server'
> EXEC sp_addlinkedsrvlogin 'SERVER1/NewServer', false, NULL, 'sa', 'test'
> select * from SERVER1/NewServer.db1.dbo.tbl_Prouduct
> when i try to run the above query it gives me the error
> Server: Msg 170, Level 15, State 1, Line 1
> Line 1: Incorrect syntax near '/'.
>
> anyone have suggestion?
> Adi
>
|||Thanks,
i had resolve the problem.
actually i try this too
select * from [Server1/Newserver].dbo.db1.tbl_product
but its still not work
but when i used back slash "\" to register new linked server and
retrieve records like that
select * from [Server1\Newserver].dbo.db1.tbl_product
its work fine
thanks
Adi
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
|||Yeah, smart. Look it up. And do us a favor and don't multipost. I just answered this question in another thread just to come to find out the it was "discovered" or answered in another.
Don't waste people's time like that.
Sincerely,
Anthony Thomas

"Adeel Ahmed" <itsmeadeel@.hotmail.com> wrote in message news:ekkVr1G1EHA.1452@.TK2MSFTNGP11.phx.gbl...
Thanks,
i had resolve the problem.
actually i try this too
select * from [Server1/Newserver].dbo.db1.tbl_product
but its still not work
but when i used back slash "\" to register new linked server and
retrieve records like that
select * from [Server1\Newserver].dbo.db1.tbl_product
its work fine
thanks
Adi
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!

Add Linked Server Failed

hi,
i have added a link server but it fails when i try to retrieve data.
SQL server in on the same machine
i have created the another SQL server instance
Main Server: SERVER1
Instance: SERVER1/NewServer
EXEC sp_addlinkedserver @.server = SERVER1/NewServer, @.srvproduct = 'SQL
Server'
EXEC sp_addlinkedsrvlogin 'SERVER1/NewServer', false, NULL, 'sa', 'test'
select * from SERVER1/NewServer.db1.dbo.tbl_Prouduct
when i try to run the above query it gives me the error
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near '/'.
anyone have suggestion'
AdiThis was just dicussed this week. The "/" is a special character not
normally allowed in an identifier. Either give the linked server a
"regular" name or delimit the name with brackets - [SERVER1/NewServer]. BOL
uses this same situation as an example.
"Adeel Ahmed" <adeel.ahmed@.pk.softechww.com> wrote in message
news:O0qoJPu0EHA.2876@.TK2MSFTNGP12.phx.gbl...
> hi,
> i have added a link server but it fails when i try to retrieve data.
> SQL server in on the same machine
> i have created the another SQL server instance
> Main Server: SERVER1
> Instance: SERVER1/NewServer
> EXEC sp_addlinkedserver @.server = SERVER1/NewServer, @.srvproduct = 'SQL
> Server'
> EXEC sp_addlinkedsrvlogin 'SERVER1/NewServer', false, NULL, 'sa', 'test'
> select * from SERVER1/NewServer.db1.dbo.tbl_Prouduct
> when i try to run the above query it gives me the error
> Server: Msg 170, Level 15, State 1, Line 1
> Line 1: Incorrect syntax near '/'.
>
> anyone have suggestion'
> Adi
>

Add Linked Server Failed

hi,
i have added a link server but it fails when i try to retrieve data.
SQL server in on the same machine
i have created the another SQL server instance
Main Server: SERVER1
Instance: SERVER1/NewServer
EXEC sp_addlinkedserver @.server = SERVER1/NewServer, @.srvproduct = 'SQL
Server'
EXEC sp_addlinkedsrvlogin 'SERVER1/NewServer', false, NULL, 'sa', 'test'
select * from SERVER1/NewServer.db1.dbo.tbl_Prouduct
when i try to run the above query it gives me the error
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near '/'.
anyone have suggestion'
AdiThis was just dicussed this week. The "/" is a special character not
normally allowed in an identifier. Either give the linked server a
"regular" name or delimit the name with brackets - [SERVER1/NewServer].
BOL
uses this same situation as an example.
"Adeel Ahmed" <adeel.ahmed@.pk.softechww.com> wrote in message
news:O0qoJPu0EHA.2876@.TK2MSFTNGP12.phx.gbl...
> hi,
> i have added a link server but it fails when i try to retrieve data.
> SQL server in on the same machine
> i have created the another SQL server instance
> Main Server: SERVER1
> Instance: SERVER1/NewServer
> EXEC sp_addlinkedserver @.server = SERVER1/NewServer, @.srvproduct = 'SQL
> Server'
> EXEC sp_addlinkedsrvlogin 'SERVER1/NewServer', false, NULL, 'sa', 'test'
> select * from SERVER1/NewServer.db1.dbo.tbl_Prouduct
> when i try to run the above query it gives me the error
> Server: Msg 170, Level 15, State 1, Line 1
> Line 1: Incorrect syntax near '/'.
>
> anyone have suggestion'
> Adi
>|||Thanks,
i had resolve the problem.
actually i try this too
select * from [Server1/Newserver].dbo.db1.tbl_product
but its still not work
but when i used back slash "\" to register new linked server and
retrieve records like that
select * from [Server1\Newserver].dbo.db1.tbl_product
its work fine
thanks
Adi
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!|||Yeah, smart. Look it up. And do us a favor and don't multipost. I just an
swered this question in another thread just to come to find out the it was "
discovered" or answered in another.
Don't waste people's time like that.
Sincerely,
Anthony Thomas
--
"Adeel Ahmed" <itsmeadeel@.hotmail.com> wrote in message news:ekkVr1G1EHA.1
452@.TK2MSFTNGP11.phx.gbl...
Thanks,
i had resolve the problem.
actually i try this too
select * from [Server1/Newserver].dbo.db1.tbl_product
but its still not work
but when i used back slash "\" to register new linked server and
retrieve records like that
select * from [Server1\Newserver].dbo.db1.tbl_product
its work fine
thanks
Adi
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!

Add leading zero to field value via SP

Hi All,

I want to add a leading zero to a field based on a param that I create on the fly in my stored proc. I have a @.month which is created from my datetime param @.date.

@.Month needs to be char(2) but if the month is inputted as '04' I get '4 ' in the table (note the space after 4)

How can I add a leading zero to this field?
Set @.Year = right('0',1)year(@.Date) is spitting it's toys out.

Thanks,
Brett

prepend a '0' char to the front of it and take RIGHT('0'+yourString,2)

Add Identity Property to existing table

How can I add an Identity property using t-sql when the
table has already been created and the column already
exist? The column name is InvoiceItemID.
TIA,
Vic
You can't. Nor can Enterprise Manager so it creates a new table, copy over the data etc etc etc. I
suggest you do the operation in EM, and press the "save change script" button to see what SQL code
EM does for this operation.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Vic" <vduran@.specpro-inc.com> wrote in message news:0e2701c4f27f$30055240$a501280a@.phx.gbl...
> How can I add an Identity property using t-sql when the
> table has already been created and the column already
> exist? The column name is InvoiceItemID.
> TIA,
> Vic
|||> How can I add an Identity property using t-sql when the
> table has already been created and the column already
> exist? The column name is InvoiceItemID.
You can't add Identity property to an existing column. Yo can do it through
EM, but it will actually create a new table, copy all of the rows, drop the
old table and rename the new table to the old name in a single big
transaction.
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com

Add Identity Property to existing table

How can I add an Identity property using t-sql when the
table has already been created and the column already
exist? The column name is InvoiceItemID.
TIA,
VicYou can't. Nor can Enterprise Manager so it creates a new table, copy over the data etc etc etc. I
suggest you do the operation in EM, and press the "save change script" button to see what SQL code
EM does for this operation.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Vic" <vduran@.specpro-inc.com> wrote in message news:0e2701c4f27f$30055240$a501280a@.phx.gbl...
> How can I add an Identity property using t-sql when the
> table has already been created and the column already
> exist? The column name is InvoiceItemID.
> TIA,
> Vic|||> How can I add an Identity property using t-sql when the
> table has already been created and the column already
> exist? The column name is InvoiceItemID.
You can't add Identity property to an existing column. Yo can do it through
EM, but it will actually create a new table, copy all of the rows, drop the
old table and rename the new table to the old name in a single big
transaction.
--
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com

Add Identity Property to existing table

How can I add an Identity property using t-sql when the
table has already been created and the column already
exist? The column name is InvoiceItemID.
TIA,
VicYou can't. Nor can Enterprise Manager so it creates a new table, copy over t
he data etc etc etc. I
suggest you do the operation in EM, and press the "save change script" butto
n to see what SQL code
EM does for this operation.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Vic" <vduran@.specpro-inc.com> wrote in message news:0e2701c4f27f$30055240$a501280a@.phx.gbl.
.
> How can I add an Identity property using t-sql when the
> table has already been created and the column already
> exist? The column name is InvoiceItemID.
> TIA,
> Vic|||> How can I add an Identity property using t-sql when the
> table has already been created and the column already
> exist? The column name is InvoiceItemID.
You can't add Identity property to an existing column. Yo can do it through
EM, but it will actually create a new table, copy all of the rows, drop the
old table and rename the new table to the old name in a single big
transaction.
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com

Add ID at Insert

The below code works fine, but for one reason, I need to get the ReferenceID inserted into a seperate table along with the newly created unique ID in the table Box. Any ideas how I can accomplish this...

CREATE PROCEDURE AddBox
(
@.BoxNumber nvarchar(40),
@.LocId int,
@.BoxNotes Text,
--This gets added to a different table that I'll call References, but can't go until I can get the uniqueID of what is inserted.
@.ReferenceID int
)
AS
INSERT INTO box
(
box_num,
loc_id,
box_notes,
status_id
)
VALUES
(
@.BoxNumber,
@.Locid,
@.BoxNotes,
2
)

didnt quite understand your question but check out BOL for SCOPE_IDENTITY().

hth|||Just the point in the right direction I needed, Thanks.

Once I inserted a new box file into the Box table I then was able to get the Unique ID number and then insert that into a reference table. The end code (after the first insert) for my previous post ends up looking as such:

Set @.Identity = SCOPE_IDENTITY()
Set @.Reference = @.ReferenceNumber
Insert into reference
(
reference_name,
box_id
)
VALUES
(
 @.Reference,
 @.Identity
)
GO