I have inserted a linked subreport into a table. I have it formatted so
that it lines up perfectly with my main report, so the user shouldn't even
be able to tell that it is a subreport. When I render the report in the
.Net viewer, it's columns line up perfectly. When I export to .PDF, it
lines up fine as well. But when I run the report from Report Manager,
viewing in the default HTML view or export to Web Archive, the columns shift
like 25 pts to the left. What's up with that? Is there a way to fix it?
Thanks,
LisaHave you apready tried playing with the Padding?
"Lisa" wrote:
> I have inserted a linked subreport into a table. I have it formatted so
> that it lines up perfectly with my main report, so the user shouldn't even
> be able to tell that it is a subreport. When I render the report in the
> ..Net viewer, it's columns line up perfectly. When I export to .PDF, it
> lines up fine as well. But when I run the report from Report Manager,
> viewing in the default HTML view or export to Web Archive, the columns shift
> like 25 pts to the left. What's up with that? Is there a way to fix it?
> Thanks,
> Lisa
>
>|||I have tried that. If I pad it enough where it lines up in the HTML default
view, then it ends up off alignment in the other direction in all the other
formats.
This default HTML view is killing me. It seems like there are so many
tweaky problems with it. I just wish the default looked like my view in
Visual Studio. That would make things so much easier!
Lisa
"Rob 'Spike' Stevens" <RobSpikeStevens@.discussions.microsoft.com> wrote in
message news:24866FD7-34E1-4188-A3B3-8E4465610A25@.microsoft.com...
> Have you apready tried playing with the Padding?
> "Lisa" wrote:
> > I have inserted a linked subreport into a table. I have it formatted so
> > that it lines up perfectly with my main report, so the user shouldn't
even
> > be able to tell that it is a subreport. When I render the report in the
> > ..Net viewer, it's columns line up perfectly. When I export to .PDF, it
> > lines up fine as well. But when I run the report from Report Manager,
> > viewing in the default HTML view or export to Web Archive, the columns
shift
> > like 25 pts to the left. What's up with that? Is there a way to fix
it?
> >
> > Thanks,
> >
> > Lisa
> >
> >
> >
Showing posts with label view. Show all posts
Showing posts with label view. Show all posts
Monday, March 19, 2012
Sunday, March 11, 2012
2col. with data in diff. sequence ref. same column in different table
I'm trying to learn SQL using SQLserver 2000.
I need some help Please!!
I'm having trouble creating a view that will give me the informaiton that i need correctly. I listed all the tables and the view that I tried but it's not working I dont think i have the view right.
Here is some info to help you understand what I'm trying to get:
Examples of the data that I'm having trouble with
only conscerns two of the tables
CREATE TABLE TDrivers
(
intDriverID INTEGER NOT NULL, <---
strFirstName VARCHAR(25) NOT NULL,
strMiddleName VARCHAR(25) NOT NULL,
strLastName VARCHAR(25) NOT NULL,
strAddress VARCHAR(25) NOT NULL,
strCity VARCHAR(25) NOT NULL,
strState VARCHAR(25) NOT NULL,
strZipCode VARCHAR(10) NOT NULL,
strPhoneNumber VARCHAR(14) NOT NULL,
CONSTRAINT TDriveres_PK PRIMARY KEY (intDriverID)
)
CREATE TABLE TScheduledRoutes
(
intRouteID INTEGER NOT NULL,
intScheduleTimeID INTEGER NOT NULL,
intBusID INTEGER NOT NULL,
intDriverID INTEGER NOT NULL, <Both ref above table
intAlternateDriverID INTEGER NOT NULL, <Both ref above table
CONSTRAINT TScheduleRoutes_PK PRIMARY KEY (intRouteID,intScheduleTimeID)
)
TDrivers Table has
intDriverID 1, 2, 3, 4, 5 and each id is associated with a name
1 = john
2 = mike
3 = sam
4 = jim
5 = tony
TScheduledRoutes Table
has column
intDriverID
and data is 1, 2, 3, 4, 5 that references TDrivers.intDriverID
and has column
intAlternateDriverID
and data is 5, 3, 1, 2, 4 that references TDrivers.intDriverID also
NOTICE the two have different sequence.
I need to get a select statement that would give me a list of
TScheduledRoutes.intDriverID full name
and their assciated alternate driver
TScheduledRoutes.intAlternateDriverID
output would give this as example
(intdriverID 1) john would have alt driverId 5 tony
I can't create a select statement that will give me both names at the same time.
Below is a list of the actual code and the view I cant get to do what I want it to and still keep the database in 3rd normal form.
Any suggestions would be greatly appreciated.
CREATE TABLE TRoutes
(
intRouteID INTEGER NOT NULL,
strRoute VARCHAR(30) NOT NULL,
strRouteDescription VARCHAR(50) NOT NULL,
CONSTRAINT TRoutes_PK PRIMARY KEY (intRouteID)
)
CREATE TABLE TBuses
(
intBusID INTEGER NOT NULL,
strBus VARCHAR(25) NOT NULL,
intCapacity INTEGER NOT NULL,
CONSTRAINT TBuses_PK PRIMARY KEY (intBusID)
)
CREATE TABLE TDrivers
(
intDriverID INTEGER NOT NULL,
strFirstName VARCHAR(25) NOT NULL,
strMiddleName VARCHAR(25) NOT NULL,
strLastName VARCHAR(25) NOT NULL,
strAddress VARCHAR(25) NOT NULL,
strCity VARCHAR(25) NOT NULL,
strState VARCHAR(25) NOT NULL,
strZipCode VARCHAR(10) NOT NULL,
strPhoneNumber VARCHAR(14) NOT NULL,
CONSTRAINT TDriveres_PK PRIMARY KEY (intDriverID)
)
CREATE TABLE TScheduleTimes
(
intScheduleTimeID INTEGER NOT NULL,
strScheduleTime DATETIME NOT NULL,
CONSTRAINT TScheduleTimes_PK PRIMARY KEY (intScheduleTimeID)
)
every column below is a foreign key to other tables
CREATE TABLE TScheduledRoutes
(
intRouteID INTEGER NOT NULL,
intScheduleTimeID INTEGER NOT NULL,
intBusID INTEGER NOT NULL,
intDriverID INTEGER NOT NULL,
intAlternateDriverID INTEGER NOT NULL,
CONSTRAINT TScheduleRoutes_PK PRIMARY KEY (intRouteID,intScheduleTimeID)
)
CREATE NONCLUSTERED INDEX TRoutes_NI ON TRoutes(strRoute)
CREATE NONCLUSTERED INDEX TBuses_NI ON TBuses(strBus)
CREATE NONCLUSTERED INDEX TDrivers_NI ON TDrivers (strLastName,strFirstName)
ALTER TABLE TScheduledRoutes
ADD CONSTRAINT TBuses_TScheduledRoutes_FK
FOREIGN KEY (intBusID)REFERENCES TBuses(intBusID)
ALTER TABLE TScheduledRoutes
ADD CONSTRAINT TRoutes_TScheduledRoutes_FK
FOREIGN KEY (intRouteID)REFERENCES TRoutes(intRouteID)
ALTER TABLE TScheduledRoutes
ADD CONSTRAINT TDrivers_TScheduledRoutes_FK
FOREIGN KEY (intDriverID)REFERENCES TDrivers(intDriverID)
ALTER TABLE TScheduledRoutes
ADD CONSTRAINT TADrivers_TScheduledRoutes_FK
FOREIGN KEY (intAlternateDriverID)REFERENCES TAltDrivers(intAltDriverID)
ALTER TABLE TScheduledRoutes
ADD CONSTRAINT TScheduleTimes_TScheduledRoutes_FK
FOREIGN KEY (intScheduleTimeID)REFERENCES TScheduleTimes(intScheduleTimeID)
Here is what I tried but its not working. Is there better way to get the information I need and
keep the database in 3rd Normal form
CREATE VIEW V_SchedualedRoutes AS
SELECT TRoutes.strRoute,
TBuses.strBus,
(TDrivers.strLastName + ', '+ TDrivers.strFirstName)
AS strDriverFullName,
(SELECT TDrivers.strLastName + ', '
+ TDrivers.strFirstName)
FROM TDrivers
INNER JOIN TScheduledRoutes
ON TDrivers.intDriverID =
TScheduledRoutes.intDriverID
WHERE TScheduledRoutes.intAlternateDriverID=
TDrivers.intDriverI)
AS strAltDriFullName, TScheduleTimes.strScheduleTime
FROM TBuses
INNER JOIN TScheduledRoutes
ON TBuses.intBusID = TScheduledRoutes.intBusID
INNER JOIN TScheduleTimes
ON TScheduledRoutes.intScheduleTimeID =
TScheduleTimes.intScheduleTimeID
INNER JOIN TDrivers
ON TScheduledRoutes.intDriverID = TDrivers.intDriverID
AND TScheduledRoutes.intAlternateDriverID =
TDrivers.intDriverID
INNER JOIN TRoutes
ON TScheduledRoutes.intRouteID = TRoutes.intRouteIDSELECT TRoutes.strRoute,
TBuses.strBus,
(TDrivers.strLastName + ', '+ TDrivers.strFirstName)
AS strDriverFullName,
(SELECT TDrivers.strLastName + ', '
+ TDrivers.strFirstName) -- remove )
FROM TDrivers
INNER JOIN TScheduledRoutes
ON TDrivers.intDriverID =
TScheduledRoutes.intDriverID
WHERE TScheduledRoutes.intAlternateDriverID=
TDrivers.intDriverI) --change TDrivers.intDriverI to TDrivers.intDriverID
AS strAltDriFullName, TScheduleTimes.strScheduleTime
FROM TBuses
INNER JOIN TScheduledRoutes
ON TBuses.intBusID = TScheduledRoutes.intBusID
INNER JOIN TScheduleTimes
ON TScheduledRoutes.intScheduleTimeID =
TScheduleTimes.intScheduleTimeID
INNER JOIN TDrivers
ON TScheduledRoutes.intDriverID = TDrivers.intDriverID
AND TScheduledRoutes.intAlternateDriverID =
TDrivers.intDriverID
INNER JOIN TRoutes
ON TScheduledRoutes.intRouteID = TRoutes.intRouteID
Try again :)|||Thankyou for your help
I need some help Please!!
I'm having trouble creating a view that will give me the informaiton that i need correctly. I listed all the tables and the view that I tried but it's not working I dont think i have the view right.
Here is some info to help you understand what I'm trying to get:
Examples of the data that I'm having trouble with
only conscerns two of the tables
CREATE TABLE TDrivers
(
intDriverID INTEGER NOT NULL, <---
strFirstName VARCHAR(25) NOT NULL,
strMiddleName VARCHAR(25) NOT NULL,
strLastName VARCHAR(25) NOT NULL,
strAddress VARCHAR(25) NOT NULL,
strCity VARCHAR(25) NOT NULL,
strState VARCHAR(25) NOT NULL,
strZipCode VARCHAR(10) NOT NULL,
strPhoneNumber VARCHAR(14) NOT NULL,
CONSTRAINT TDriveres_PK PRIMARY KEY (intDriverID)
)
CREATE TABLE TScheduledRoutes
(
intRouteID INTEGER NOT NULL,
intScheduleTimeID INTEGER NOT NULL,
intBusID INTEGER NOT NULL,
intDriverID INTEGER NOT NULL, <Both ref above table
intAlternateDriverID INTEGER NOT NULL, <Both ref above table
CONSTRAINT TScheduleRoutes_PK PRIMARY KEY (intRouteID,intScheduleTimeID)
)
TDrivers Table has
intDriverID 1, 2, 3, 4, 5 and each id is associated with a name
1 = john
2 = mike
3 = sam
4 = jim
5 = tony
TScheduledRoutes Table
has column
intDriverID
and data is 1, 2, 3, 4, 5 that references TDrivers.intDriverID
and has column
intAlternateDriverID
and data is 5, 3, 1, 2, 4 that references TDrivers.intDriverID also
NOTICE the two have different sequence.
I need to get a select statement that would give me a list of
TScheduledRoutes.intDriverID full name
and their assciated alternate driver
TScheduledRoutes.intAlternateDriverID
output would give this as example
(intdriverID 1) john would have alt driverId 5 tony
I can't create a select statement that will give me both names at the same time.
Below is a list of the actual code and the view I cant get to do what I want it to and still keep the database in 3rd normal form.
Any suggestions would be greatly appreciated.
CREATE TABLE TRoutes
(
intRouteID INTEGER NOT NULL,
strRoute VARCHAR(30) NOT NULL,
strRouteDescription VARCHAR(50) NOT NULL,
CONSTRAINT TRoutes_PK PRIMARY KEY (intRouteID)
)
CREATE TABLE TBuses
(
intBusID INTEGER NOT NULL,
strBus VARCHAR(25) NOT NULL,
intCapacity INTEGER NOT NULL,
CONSTRAINT TBuses_PK PRIMARY KEY (intBusID)
)
CREATE TABLE TDrivers
(
intDriverID INTEGER NOT NULL,
strFirstName VARCHAR(25) NOT NULL,
strMiddleName VARCHAR(25) NOT NULL,
strLastName VARCHAR(25) NOT NULL,
strAddress VARCHAR(25) NOT NULL,
strCity VARCHAR(25) NOT NULL,
strState VARCHAR(25) NOT NULL,
strZipCode VARCHAR(10) NOT NULL,
strPhoneNumber VARCHAR(14) NOT NULL,
CONSTRAINT TDriveres_PK PRIMARY KEY (intDriverID)
)
CREATE TABLE TScheduleTimes
(
intScheduleTimeID INTEGER NOT NULL,
strScheduleTime DATETIME NOT NULL,
CONSTRAINT TScheduleTimes_PK PRIMARY KEY (intScheduleTimeID)
)
every column below is a foreign key to other tables
CREATE TABLE TScheduledRoutes
(
intRouteID INTEGER NOT NULL,
intScheduleTimeID INTEGER NOT NULL,
intBusID INTEGER NOT NULL,
intDriverID INTEGER NOT NULL,
intAlternateDriverID INTEGER NOT NULL,
CONSTRAINT TScheduleRoutes_PK PRIMARY KEY (intRouteID,intScheduleTimeID)
)
CREATE NONCLUSTERED INDEX TRoutes_NI ON TRoutes(strRoute)
CREATE NONCLUSTERED INDEX TBuses_NI ON TBuses(strBus)
CREATE NONCLUSTERED INDEX TDrivers_NI ON TDrivers (strLastName,strFirstName)
ALTER TABLE TScheduledRoutes
ADD CONSTRAINT TBuses_TScheduledRoutes_FK
FOREIGN KEY (intBusID)REFERENCES TBuses(intBusID)
ALTER TABLE TScheduledRoutes
ADD CONSTRAINT TRoutes_TScheduledRoutes_FK
FOREIGN KEY (intRouteID)REFERENCES TRoutes(intRouteID)
ALTER TABLE TScheduledRoutes
ADD CONSTRAINT TDrivers_TScheduledRoutes_FK
FOREIGN KEY (intDriverID)REFERENCES TDrivers(intDriverID)
ALTER TABLE TScheduledRoutes
ADD CONSTRAINT TADrivers_TScheduledRoutes_FK
FOREIGN KEY (intAlternateDriverID)REFERENCES TAltDrivers(intAltDriverID)
ALTER TABLE TScheduledRoutes
ADD CONSTRAINT TScheduleTimes_TScheduledRoutes_FK
FOREIGN KEY (intScheduleTimeID)REFERENCES TScheduleTimes(intScheduleTimeID)
Here is what I tried but its not working. Is there better way to get the information I need and
keep the database in 3rd Normal form
CREATE VIEW V_SchedualedRoutes AS
SELECT TRoutes.strRoute,
TBuses.strBus,
(TDrivers.strLastName + ', '+ TDrivers.strFirstName)
AS strDriverFullName,
(SELECT TDrivers.strLastName + ', '
+ TDrivers.strFirstName)
FROM TDrivers
INNER JOIN TScheduledRoutes
ON TDrivers.intDriverID =
TScheduledRoutes.intDriverID
WHERE TScheduledRoutes.intAlternateDriverID=
TDrivers.intDriverI)
AS strAltDriFullName, TScheduleTimes.strScheduleTime
FROM TBuses
INNER JOIN TScheduledRoutes
ON TBuses.intBusID = TScheduledRoutes.intBusID
INNER JOIN TScheduleTimes
ON TScheduledRoutes.intScheduleTimeID =
TScheduleTimes.intScheduleTimeID
INNER JOIN TDrivers
ON TScheduledRoutes.intDriverID = TDrivers.intDriverID
AND TScheduledRoutes.intAlternateDriverID =
TDrivers.intDriverID
INNER JOIN TRoutes
ON TScheduledRoutes.intRouteID = TRoutes.intRouteIDSELECT TRoutes.strRoute,
TBuses.strBus,
(TDrivers.strLastName + ', '+ TDrivers.strFirstName)
AS strDriverFullName,
(SELECT TDrivers.strLastName + ', '
+ TDrivers.strFirstName) -- remove )
FROM TDrivers
INNER JOIN TScheduledRoutes
ON TDrivers.intDriverID =
TScheduledRoutes.intDriverID
WHERE TScheduledRoutes.intAlternateDriverID=
TDrivers.intDriverI) --change TDrivers.intDriverI to TDrivers.intDriverID
AS strAltDriFullName, TScheduleTimes.strScheduleTime
FROM TBuses
INNER JOIN TScheduledRoutes
ON TBuses.intBusID = TScheduledRoutes.intBusID
INNER JOIN TScheduleTimes
ON TScheduledRoutes.intScheduleTimeID =
TScheduleTimes.intScheduleTimeID
INNER JOIN TDrivers
ON TScheduledRoutes.intDriverID = TDrivers.intDriverID
AND TScheduledRoutes.intAlternateDriverID =
TDrivers.intDriverID
INNER JOIN TRoutes
ON TScheduledRoutes.intRouteID = TRoutes.intRouteID
Try again :)|||Thankyou for your help
2601 Error When Creating or Altering a SPROC SS2005
I get the following error(in profiler) when creating or altering a stored
procedure (or view) in SS2005: "Error: 2601, Severity: 14, State: 1"
To duplicate create a table (table1) start profiler, add errors and
warnings, start the trace, create the proc below
create procedure test
As
select * from table1
select * from table1
go
The number of exceptions you see in the profiler trace is the same as the
number of columns on table1. Is this some kind of database engine problem
where the engine is trying to insert/update sys.sql_dependencies?I could not reproduce this, whether or not the table exists. All I get is a
couple of error 208
(object doesn't exist).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<hpux9@.nospam.nospam> wrote in message news:D1E3C6C5-C624-41C1-A328-86357444DE45@.microsoft.c
om...
>I get the following error(in profiler) when creating or altering a stored
> procedure (or view) in SS2005: "Error: 2601, Severity: 14, State: 1"
> To duplicate create a table (table1) start profiler, add errors and
> warnings, start the trace, create the proc below
> create procedure test
> As
> select * from table1
> select * from table1
> go
> The number of exceptions you see in the profiler trace is the same as the
> number of columns on table1. Is this some kind of database engine problem
> where the engine is trying to insert/update sys.sql_dependencies?
>|||Tibor,
Thanks for the reply. I went back and tested this and it happens in my
production version:
9.00.1399.06 RTM Enterprise Edition (64-bit)
But it does not happen in my development version:
9.00.2047.00 SP1 Developer Edition
Fixed in SP1, or x86 vs x64 bug? I'll try to get the PRD upgraded to SP1
and see if it goes away.
Thanks
"Tibor Karaszi" wrote:
> I could not reproduce this, whether or not the table exists. All I get is
a couple of error 208
> (object doesn't exist).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> <hpux9@.nospam.nospam> wrote in message news:D1E3C6C5-C624-41C1-A328-863574
44DE45@.microsoft.com...
>
>|||Upgraded to:
9.00.2047.00 SP1 Enterprise Edition (64-bit)
Still has the same issue. Can anyone else duplicate this issue?
Thanks
"hpux9@.nospam.nospam" wrote:
[vbcol=seagreen]
> Tibor,
> Thanks for the reply. I went back and tested this and it happens in my
> production version:
> 9.00.1399.06 RTM Enterprise Edition (64-bit)
> But it does not happen in my development version:
> 9.00.2047.00 SP1 Developer Edition
> Fixed in SP1, or x86 vs x64 bug? I'll try to get the PRD upgraded to SP1
> and see if it goes away.
> Thanks
> "Tibor Karaszi" wrote:
>
procedure (or view) in SS2005: "Error: 2601, Severity: 14, State: 1"
To duplicate create a table (table1) start profiler, add errors and
warnings, start the trace, create the proc below
create procedure test
As
select * from table1
select * from table1
go
The number of exceptions you see in the profiler trace is the same as the
number of columns on table1. Is this some kind of database engine problem
where the engine is trying to insert/update sys.sql_dependencies?I could not reproduce this, whether or not the table exists. All I get is a
couple of error 208
(object doesn't exist).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<hpux9@.nospam.nospam> wrote in message news:D1E3C6C5-C624-41C1-A328-86357444DE45@.microsoft.c
om...
>I get the following error(in profiler) when creating or altering a stored
> procedure (or view) in SS2005: "Error: 2601, Severity: 14, State: 1"
> To duplicate create a table (table1) start profiler, add errors and
> warnings, start the trace, create the proc below
> create procedure test
> As
> select * from table1
> select * from table1
> go
> The number of exceptions you see in the profiler trace is the same as the
> number of columns on table1. Is this some kind of database engine problem
> where the engine is trying to insert/update sys.sql_dependencies?
>|||Tibor,
Thanks for the reply. I went back and tested this and it happens in my
production version:
9.00.1399.06 RTM Enterprise Edition (64-bit)
But it does not happen in my development version:
9.00.2047.00 SP1 Developer Edition
Fixed in SP1, or x86 vs x64 bug? I'll try to get the PRD upgraded to SP1
and see if it goes away.
Thanks
"Tibor Karaszi" wrote:
> I could not reproduce this, whether or not the table exists. All I get is
a couple of error 208
> (object doesn't exist).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> <hpux9@.nospam.nospam> wrote in message news:D1E3C6C5-C624-41C1-A328-863574
44DE45@.microsoft.com...
>
>|||Upgraded to:
9.00.2047.00 SP1 Enterprise Edition (64-bit)
Still has the same issue. Can anyone else duplicate this issue?
Thanks
"hpux9@.nospam.nospam" wrote:
[vbcol=seagreen]
> Tibor,
> Thanks for the reply. I went back and tested this and it happens in my
> production version:
> 9.00.1399.06 RTM Enterprise Edition (64-bit)
> But it does not happen in my development version:
> 9.00.2047.00 SP1 Developer Edition
> Fixed in SP1, or x86 vs x64 bug? I'll try to get the PRD upgraded to SP1
> and see if it goes away.
> Thanks
> "Tibor Karaszi" wrote:
>
2601 Error When Creating or Altering a SPROC SS2005
I get the following error(in profiler) when creating or altering a stored
procedure (or view) in SS2005: "Error: 2601, Severity: 14, State: 1"
To duplicate create a table (table1) start profiler, add errors and
warnings, start the trace, create the proc below
create procedure test
As
select * from table1
select * from table1
go
The number of exceptions you see in the profiler trace is the same as the
number of columns on table1. Is this some kind of database engine problem
where the engine is trying to insert/update sys.sql_dependencies?I could not reproduce this, whether or not the table exists. All I get is a couple of error 208
(object doesn't exist).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<hpux9@.nospam.nospam> wrote in message news:D1E3C6C5-C624-41C1-A328-86357444DE45@.microsoft.com...
>I get the following error(in profiler) when creating or altering a stored
> procedure (or view) in SS2005: "Error: 2601, Severity: 14, State: 1"
> To duplicate create a table (table1) start profiler, add errors and
> warnings, start the trace, create the proc below
> create procedure test
> As
> select * from table1
> select * from table1
> go
> The number of exceptions you see in the profiler trace is the same as the
> number of columns on table1. Is this some kind of database engine problem
> where the engine is trying to insert/update sys.sql_dependencies?
>|||Tibor,
Thanks for the reply. I went back and tested this and it happens in my
production version:
9.00.1399.06 RTM Enterprise Edition (64-bit)
But it does not happen in my development version:
9.00.2047.00 SP1 Developer Edition
Fixed in SP1, or x86 vs x64 bug? I'll try to get the PRD upgraded to SP1
and see if it goes away.
Thanks
"Tibor Karaszi" wrote:
> I could not reproduce this, whether or not the table exists. All I get is a couple of error 208
> (object doesn't exist).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> <hpux9@.nospam.nospam> wrote in message news:D1E3C6C5-C624-41C1-A328-86357444DE45@.microsoft.com...
> >I get the following error(in profiler) when creating or altering a stored
> > procedure (or view) in SS2005: "Error: 2601, Severity: 14, State: 1"
> >
> > To duplicate create a table (table1) start profiler, add errors and
> > warnings, start the trace, create the proc below
> >
> > create procedure test
> > As
> > select * from table1
> > select * from table1
> > go
> >
> > The number of exceptions you see in the profiler trace is the same as the
> > number of columns on table1. Is this some kind of database engine problem
> > where the engine is trying to insert/update sys.sql_dependencies?
> >
> >
>
>|||Upgraded to:
9.00.2047.00 SP1 Enterprise Edition (64-bit)
Still has the same issue. Can anyone else duplicate this issue?
Thanks
"hpux9@.nospam.nospam" wrote:
> Tibor,
> Thanks for the reply. I went back and tested this and it happens in my
> production version:
> 9.00.1399.06 RTM Enterprise Edition (64-bit)
> But it does not happen in my development version:
> 9.00.2047.00 SP1 Developer Edition
> Fixed in SP1, or x86 vs x64 bug? I'll try to get the PRD upgraded to SP1
> and see if it goes away.
> Thanks
> "Tibor Karaszi" wrote:
> > I could not reproduce this, whether or not the table exists. All I get is a couple of error 208
> > (object doesn't exist).
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > http://www.karaszi.com/sqlserver/default.asp
> > http://www.solidqualitylearning.com/
> >
> >
> > <hpux9@.nospam.nospam> wrote in message news:D1E3C6C5-C624-41C1-A328-86357444DE45@.microsoft.com...
> > >I get the following error(in profiler) when creating or altering a stored
> > > procedure (or view) in SS2005: "Error: 2601, Severity: 14, State: 1"
> > >
> > > To duplicate create a table (table1) start profiler, add errors and
> > > warnings, start the trace, create the proc below
> > >
> > > create procedure test
> > > As
> > > select * from table1
> > > select * from table1
> > > go
> > >
> > > The number of exceptions you see in the profiler trace is the same as the
> > > number of columns on table1. Is this some kind of database engine problem
> > > where the engine is trying to insert/update sys.sql_dependencies?
> > >
> > >
> >
> >
> >
procedure (or view) in SS2005: "Error: 2601, Severity: 14, State: 1"
To duplicate create a table (table1) start profiler, add errors and
warnings, start the trace, create the proc below
create procedure test
As
select * from table1
select * from table1
go
The number of exceptions you see in the profiler trace is the same as the
number of columns on table1. Is this some kind of database engine problem
where the engine is trying to insert/update sys.sql_dependencies?I could not reproduce this, whether or not the table exists. All I get is a couple of error 208
(object doesn't exist).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<hpux9@.nospam.nospam> wrote in message news:D1E3C6C5-C624-41C1-A328-86357444DE45@.microsoft.com...
>I get the following error(in profiler) when creating or altering a stored
> procedure (or view) in SS2005: "Error: 2601, Severity: 14, State: 1"
> To duplicate create a table (table1) start profiler, add errors and
> warnings, start the trace, create the proc below
> create procedure test
> As
> select * from table1
> select * from table1
> go
> The number of exceptions you see in the profiler trace is the same as the
> number of columns on table1. Is this some kind of database engine problem
> where the engine is trying to insert/update sys.sql_dependencies?
>|||Tibor,
Thanks for the reply. I went back and tested this and it happens in my
production version:
9.00.1399.06 RTM Enterprise Edition (64-bit)
But it does not happen in my development version:
9.00.2047.00 SP1 Developer Edition
Fixed in SP1, or x86 vs x64 bug? I'll try to get the PRD upgraded to SP1
and see if it goes away.
Thanks
"Tibor Karaszi" wrote:
> I could not reproduce this, whether or not the table exists. All I get is a couple of error 208
> (object doesn't exist).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> <hpux9@.nospam.nospam> wrote in message news:D1E3C6C5-C624-41C1-A328-86357444DE45@.microsoft.com...
> >I get the following error(in profiler) when creating or altering a stored
> > procedure (or view) in SS2005: "Error: 2601, Severity: 14, State: 1"
> >
> > To duplicate create a table (table1) start profiler, add errors and
> > warnings, start the trace, create the proc below
> >
> > create procedure test
> > As
> > select * from table1
> > select * from table1
> > go
> >
> > The number of exceptions you see in the profiler trace is the same as the
> > number of columns on table1. Is this some kind of database engine problem
> > where the engine is trying to insert/update sys.sql_dependencies?
> >
> >
>
>|||Upgraded to:
9.00.2047.00 SP1 Enterprise Edition (64-bit)
Still has the same issue. Can anyone else duplicate this issue?
Thanks
"hpux9@.nospam.nospam" wrote:
> Tibor,
> Thanks for the reply. I went back and tested this and it happens in my
> production version:
> 9.00.1399.06 RTM Enterprise Edition (64-bit)
> But it does not happen in my development version:
> 9.00.2047.00 SP1 Developer Edition
> Fixed in SP1, or x86 vs x64 bug? I'll try to get the PRD upgraded to SP1
> and see if it goes away.
> Thanks
> "Tibor Karaszi" wrote:
> > I could not reproduce this, whether or not the table exists. All I get is a couple of error 208
> > (object doesn't exist).
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > http://www.karaszi.com/sqlserver/default.asp
> > http://www.solidqualitylearning.com/
> >
> >
> > <hpux9@.nospam.nospam> wrote in message news:D1E3C6C5-C624-41C1-A328-86357444DE45@.microsoft.com...
> > >I get the following error(in profiler) when creating or altering a stored
> > > procedure (or view) in SS2005: "Error: 2601, Severity: 14, State: 1"
> > >
> > > To duplicate create a table (table1) start profiler, add errors and
> > > warnings, start the trace, create the proc below
> > >
> > > create procedure test
> > > As
> > > select * from table1
> > > select * from table1
> > > go
> > >
> > > The number of exceptions you see in the profiler trace is the same as the
> > > number of columns on table1. Is this some kind of database engine problem
> > > where the engine is trying to insert/update sys.sql_dependencies?
> > >
> > >
> >
> >
> >
256 table limit for partitioned views
I have a partitioned view sitting over several tables and I'm slowly
approaching the 256 number. Can anybody confirm if there is such a
limit for the maximum number of tables that a partitioned view can
hold?
approaching the 256 number. Can anybody confirm if there is such a
limit for the maximum number of tables that a partitioned view can
hold?
If this is true, does anybody have any suggestions or ideas to work
around this max limit?
TIA!karthik (karthiksmiles@.gmail.com) writes:
> I have a partitioned view sitting over several tables and I'm slowly
> approaching the 256 number. Can anybody confirm if there is such a
> limit for the maximum number of tables that a partitioned view can
> hold?
Yes, since the maximum number of tables per query is 256 I would
expect that there is such a limit.
> If this is true, does anybody have any suggestions or ideas to work
> around this max limit?
How big are your tables? Would it be possible to consolidate them?
In SQL 2005 there is partioned tables, which is taking this to another
level. I don't know how many partitions you can have in a table, but
it's a new ballpark.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||The limit is 256 tables "per SELECT statement", not per query.
Therefore, a UNION query can have more than 256 tables, but
unfortunately, such a query may not be used in a view. For example:
CREATE TABLE T (X INT)
INSERT INTO T VALUES (1)
DECLARE @.SQL varchar(8000)
SELECT @.SQL=ISNULL(@.SQL+' UNION ALL ','')+'SELECT X FROM T'
FROM (SELECT DISTINCT number FROM master..spt_values
WHERE number BETWEEN 0 AND 256) X
--PRINT LEN(@.SQL)
EXEC(@.SQL)
SET @.SQL='CREATE VIEW V AS '+@.SQL
EXEC (@.SQL)
For more informations, see:
http://groups-beta.google.com/group...885c192f511bd1a
Razvan|||Razvan Socol (rsocol@.gmail.com) writes:
> The limit is 256 tables "per SELECT statement", not per query.
> Therefore, a UNION query can have more than 256 tables, but
> unfortunately, such a query may not be used in a view. For example:
Thanks Razvan. I did notice "per SELECT statement", but I was too lazy
to get a practical interpretation of what that really meant.
> For more informations, see:
> http://groups-beta.google.com/group...885c192f511bd1a
That's a useful link!
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Thanks Razvan and Erland...I guess I'm just going to wait for the
Partitioned Tables feature in SQL Server 2005.
Thursday, March 8, 2012
21776 Error by EM when a view column name is changed.
I am wondering if there is a sp or some other method to fix an issue that
arises when you change the name of a column in a view.
The scenario is:
1. Create the view
2. Alter the view, changing the name of one of the fields
3. Within EM, select the view | Properties | permissions, and then check the
column permissions - you get an error "21776 [SQL-DMO] The name
'<newcolumnname>' was not found in the columns collection..."
The BOL item on ALTER VIEW warns that if you have permissions defined on a
view column, and then change the name of the column, the permissions do not
follow, BUT, in addition to this, you cannot even see the NEW column to
assign the permissions again in the above scenario.
Has anyone run into this?
Thanks
Best regards,
BlairCheck out sp_refreshview in BooksOnLine.
Andrew J. Kelly SQL MVP
"BlairH" <BlairH@.discussions.microsoft.com> wrote in message
news:8AB571A3-779F-497A-A707-703B66FEF13E@.microsoft.com...
>I am wondering if there is a sp or some other method to fix an issue that
> arises when you change the name of a column in a view.
> The scenario is:
> 1. Create the view
> 2. Alter the view, changing the name of one of the fields
> 3. Within EM, select the view | Properties | permissions, and then check
> the
> column permissions - you get an error "21776 [SQL-DMO] The name
> '<newcolumnname>' was not found in the columns collection..."
> The BOL item on ALTER VIEW warns that if you have permissions defined on a
> view column, and then change the name of the column, the permissions do
> not
> follow, BUT, in addition to this, you cannot even see the NEW column to
> assign the permissions again in the above scenario.
> Has anyone run into this?
> Thanks
> --
> Best regards,
> Blair|||Thanks very much Andrew. That's exactly what I was looking for. In
addition, it apears that you have to restart EM in order for the fix to take
effect. (but it does)
Thanks again.
--
Best regards,
Blair
"Andrew J. Kelly" wrote:
> Check out sp_refreshview in BooksOnLine.
>
> --
> Andrew J. Kelly SQL MVP
>
> "BlairH" <BlairH@.discussions.microsoft.com> wrote in message
> news:8AB571A3-779F-497A-A707-703B66FEF13E@.microsoft.com...
>
>|||All you should have needed to do after issuing sp_refreshview was to refresh
that node in EM. But restarting works too<g>.
Andrew J. Kelly SQL MVP
"BlairH" <BlairH@.discussions.microsoft.com> wrote in message
news:5EF6E77B-79A3-46AB-8070-3842A950407F@.microsoft.com...[vbcol=seagreen]
> Thanks very much Andrew. That's exactly what I was looking for. In
> addition, it apears that you have to restart EM in order for the fix to
> take
> effect. (but it does)
> Thanks again.
> --
> Best regards,
> Blair
>
> "Andrew J. Kelly" wrote:
>
arises when you change the name of a column in a view.
The scenario is:
1. Create the view
2. Alter the view, changing the name of one of the fields
3. Within EM, select the view | Properties | permissions, and then check the
column permissions - you get an error "21776 [SQL-DMO] The name
'<newcolumnname>' was not found in the columns collection..."
The BOL item on ALTER VIEW warns that if you have permissions defined on a
view column, and then change the name of the column, the permissions do not
follow, BUT, in addition to this, you cannot even see the NEW column to
assign the permissions again in the above scenario.
Has anyone run into this?
Thanks
Best regards,
BlairCheck out sp_refreshview in BooksOnLine.
Andrew J. Kelly SQL MVP
"BlairH" <BlairH@.discussions.microsoft.com> wrote in message
news:8AB571A3-779F-497A-A707-703B66FEF13E@.microsoft.com...
>I am wondering if there is a sp or some other method to fix an issue that
> arises when you change the name of a column in a view.
> The scenario is:
> 1. Create the view
> 2. Alter the view, changing the name of one of the fields
> 3. Within EM, select the view | Properties | permissions, and then check
> the
> column permissions - you get an error "21776 [SQL-DMO] The name
> '<newcolumnname>' was not found in the columns collection..."
> The BOL item on ALTER VIEW warns that if you have permissions defined on a
> view column, and then change the name of the column, the permissions do
> not
> follow, BUT, in addition to this, you cannot even see the NEW column to
> assign the permissions again in the above scenario.
> Has anyone run into this?
> Thanks
> --
> Best regards,
> Blair|||Thanks very much Andrew. That's exactly what I was looking for. In
addition, it apears that you have to restart EM in order for the fix to take
effect. (but it does)
Thanks again.
--
Best regards,
Blair
"Andrew J. Kelly" wrote:
> Check out sp_refreshview in BooksOnLine.
>
> --
> Andrew J. Kelly SQL MVP
>
> "BlairH" <BlairH@.discussions.microsoft.com> wrote in message
> news:8AB571A3-779F-497A-A707-703B66FEF13E@.microsoft.com...
>
>|||All you should have needed to do after issuing sp_refreshview was to refresh
that node in EM. But restarting works too<g>.
Andrew J. Kelly SQL MVP
"BlairH" <BlairH@.discussions.microsoft.com> wrote in message
news:5EF6E77B-79A3-46AB-8070-3842A950407F@.microsoft.com...[vbcol=seagreen]
> Thanks very much Andrew. That's exactly what I was looking for. In
> addition, it apears that you have to restart EM in order for the fix to
> take
> effect. (but it does)
> Thanks again.
> --
> Best regards,
> Blair
>
> "Andrew J. Kelly" wrote:
>
Friday, February 24, 2012
2005 Replication with SCHEMABINDING Subscriber Views
Hi Folks
Is there an easy way around this ?
One Way Transactional Rep Subscriber needs SCHEMABINDING on the majority of their Views (require View Indexes) which read from Replicated Tables.
Main table has 4 Million Rows
ReInitialize Subscription Errors with Cannot Drop Table because it is being referenced By Object .... [schemaBound View]
GWmmmm - not much interest in this thread :eek:
well for info I'm trying to backup any schemabound objects with the following code (Showing the OBJECT_DEFINITION Usage).
ALTER PROCEDURE [dbo].[usp_SchemaBoundObjects_Backup]
AS
SET NOCOUNT ON
IF(SELECT OBJECT_ID('SchemaBoundObjects')) IS NULL
BEGIN
CREATE TABLE SchemaBoundObjects
(ObjName SysName,ObjType VarChar(50),ObjText nVarChar(4000))
END
ELSE
BEGIN
TRUNCATE TABLE SchemaBoundObjects
END
INSERT INTO SchemaBoundObjects
SELECT DISTINCT OBJECT_NAME(o.object_id) ObjName
,o.type_desc ObjType
,OBJECT_DEFINITION (o.object_id) ObjText
FROM sys.sql_dependenciesAS d
JOIN sys.objectsAS o
ON d.object_id= o.object_id
AND o.type IN('FN','IF','TF','V','P')
WHERE d.class = 1
INSERT INTO SchemaBoundObjects
-- Does'nt work
SELECT si.name,'INDEX',OBJECT_DEFINITION (OBJECT_ID(si.name)) ObjText
FROM sys.indexes si
JOIN sys.views sv
ON sv.object_id= si.object_id
WHERE sv.name IN (SELECT ObjName FROM SchemaBoundObjects)
Programatically ReInitialize Subscription, spike the Distributor - Then Recreate the Objects (which I have the Code for).
Now if I can just sort out the sodding View Indexes which don't work Grrr :(
C'mon folks Join in anytime :p
GW
Is there an easy way around this ?
One Way Transactional Rep Subscriber needs SCHEMABINDING on the majority of their Views (require View Indexes) which read from Replicated Tables.
Main table has 4 Million Rows
ReInitialize Subscription Errors with Cannot Drop Table because it is being referenced By Object .... [schemaBound View]
GWmmmm - not much interest in this thread :eek:
well for info I'm trying to backup any schemabound objects with the following code (Showing the OBJECT_DEFINITION Usage).
ALTER PROCEDURE [dbo].[usp_SchemaBoundObjects_Backup]
AS
SET NOCOUNT ON
IF(SELECT OBJECT_ID('SchemaBoundObjects')) IS NULL
BEGIN
CREATE TABLE SchemaBoundObjects
(ObjName SysName,ObjType VarChar(50),ObjText nVarChar(4000))
END
ELSE
BEGIN
TRUNCATE TABLE SchemaBoundObjects
END
INSERT INTO SchemaBoundObjects
SELECT DISTINCT OBJECT_NAME(o.object_id) ObjName
,o.type_desc ObjType
,OBJECT_DEFINITION (o.object_id) ObjText
FROM sys.sql_dependenciesAS d
JOIN sys.objectsAS o
ON d.object_id= o.object_id
AND o.type IN('FN','IF','TF','V','P')
WHERE d.class = 1
INSERT INTO SchemaBoundObjects
-- Does'nt work
SELECT si.name,'INDEX',OBJECT_DEFINITION (OBJECT_ID(si.name)) ObjText
FROM sys.indexes si
JOIN sys.views sv
ON sv.object_id= si.object_id
WHERE sv.name IN (SELECT ObjName FROM SchemaBoundObjects)
Programatically ReInitialize Subscription, spike the Distributor - Then Recreate the Objects (which I have the Code for).
Now if I can just sort out the sodding View Indexes which don't work Grrr :(
C'mon folks Join in anytime :p
GW
Sunday, February 19, 2012
2005 Management Studio Reformats my source!
Minor issues that are driving me crazy about this tool:
1. I type in a new view and format it the way it makes sense to me. I hit
the Save button, and SqlMS decides it wants to format it a completely
different way. How can I turn this feature off, or tell the program how I
want it to format my code? Older versions of EM did not reformat the code.
2. When I create a new view, the Diagram, Results and Criteria panes
automatically display. I have to click the buttons to hide them each time.
I don't need them or want them taking up editing space. How can I make the
program remember that I don't want it to display these "helpful" windows
each time?
Please advise
no help or resolution here?
"ZippyThePinhead" wrote:
> Minor issues that are driving me crazy about this tool:
> 1. I type in a new view and format it the way it makes sense to me. I hit
> the Save button, and SqlMS decides it wants to format it a completely
> different way. How can I turn this feature off, or tell the program how I
> want it to format my code? Older versions of EM did not reformat the code.
> 2. When I create a new view, the Diagram, Results and Criteria panes
> automatically display. I have to click the buttons to hide them each time.
> I don't need them or want them taking up editing space. How can I make the
> program remember that I don't want it to display these "helpful" windows
> each time?
> Please advise
>
|||Why not create the view as a CREATE VIEW statement inside of a Query window? I
don't seem to have any issues when I do that. Of course, you will need to
choose ALTER - Script to New Window/Clipboard in order to modify the code
instead of choosing Modify as Modify defaults to that view.
-Pete Schott
ZippyThePinhead <ZippyThePinhead@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
> no help or resolution here?
> "ZippyThePinhead" wrote:
|||Thanks Peter -- yes that certainly does work, but it's a pain in the rear to
have to manage this myself..which is what I have been doing lately.
There simply needs to be some options available in the program to stop the
auto-formatting of our code. I've noticed that sometimes it even messes
things up by adding "as exp1" expressions on columns the parser thinks are
misnamed!
"Peter A. Schott" wrote:
> Why not create the view as a CREATE VIEW statement inside of a Query window? I
> don't seem to have any issues when I do that. Of course, you will need to
> choose ALTER - Script to New Window/Clipboard in order to modify the code
> instead of choosing Modify as Modify defaults to that view.
> -Pete Schott
> ZippyThePinhead <ZippyThePinhead@.discussions.microsoft.com> wrote:
>
1. I type in a new view and format it the way it makes sense to me. I hit
the Save button, and SqlMS decides it wants to format it a completely
different way. How can I turn this feature off, or tell the program how I
want it to format my code? Older versions of EM did not reformat the code.
2. When I create a new view, the Diagram, Results and Criteria panes
automatically display. I have to click the buttons to hide them each time.
I don't need them or want them taking up editing space. How can I make the
program remember that I don't want it to display these "helpful" windows
each time?
Please advise
no help or resolution here?
"ZippyThePinhead" wrote:
> Minor issues that are driving me crazy about this tool:
> 1. I type in a new view and format it the way it makes sense to me. I hit
> the Save button, and SqlMS decides it wants to format it a completely
> different way. How can I turn this feature off, or tell the program how I
> want it to format my code? Older versions of EM did not reformat the code.
> 2. When I create a new view, the Diagram, Results and Criteria panes
> automatically display. I have to click the buttons to hide them each time.
> I don't need them or want them taking up editing space. How can I make the
> program remember that I don't want it to display these "helpful" windows
> each time?
> Please advise
>
|||Why not create the view as a CREATE VIEW statement inside of a Query window? I
don't seem to have any issues when I do that. Of course, you will need to
choose ALTER - Script to New Window/Clipboard in order to modify the code
instead of choosing Modify as Modify defaults to that view.
-Pete Schott
ZippyThePinhead <ZippyThePinhead@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
> no help or resolution here?
> "ZippyThePinhead" wrote:
|||Thanks Peter -- yes that certainly does work, but it's a pain in the rear to
have to manage this myself..which is what I have been doing lately.
There simply needs to be some options available in the program to stop the
auto-formatting of our code. I've noticed that sometimes it even messes
things up by adding "as exp1" expressions on columns the parser thinks are
misnamed!
"Peter A. Schott" wrote:
> Why not create the view as a CREATE VIEW statement inside of a Query window? I
> don't seem to have any issues when I do that. Of course, you will need to
> choose ALTER - Script to New Window/Clipboard in order to modify the code
> instead of choosing Modify as Modify defaults to that view.
> -Pete Schott
> ZippyThePinhead <ZippyThePinhead@.discussions.microsoft.com> wrote:
>
Subscribe to:
Posts (Atom)