Resetting DB

> Is there an easy way to drop all object of a db so it is as new? Just so I
> can run a script to recreate the objects.
Below is a script that will drop all objects. If the database is not large
or you are using SQL 2005, you might find it easy to simply drop and
recreate the database.
IF DB_NAME() IN ('master', 'msdb', 'model', 'distribution')
BEGIN
RAISERROR('Not for use on system databases', 16, 1)
GOTO Done
END
--Drop objects from current the database
SET NOCOUNT ON
DECLARE @.DropStatement nvarchar(4000)
DECLARE @.SequenceNumber int
DECLARE @.LastError int
DECLARE @.TablesDropped int
DECLARE DropStatements CURSOR
LOCAL FAST_FORWARD READ_ONLY FOR
--views
SELECT
1 AS SequenceNumber,
N'DROP VIEW ' +
QUOTENAME(TABLE_SCHEMA) +
N'.' +
QUOTENAME(TABLE_NAME) AS DropStatement
FROM
INFORMATION_SCHEMA.TABLES
WHERE
TABLE_TYPE = N'VIEW' AND
OBJECTPROPERTY(
OBJECT_ID(QUOTENAME(TABLE_SCHEMA) +
N'.' +
QUOTENAME(TABLE_NAME)),
'IsSchemaBound') = 1 AND
OBJECTPROPERTY(
OBJECT_ID(QUOTENAME(TABLE_SCHEMA) +
N'.' +
QUOTENAME(TABLE_NAME)),
'IsMSShipped') = 0
UNION ALL
--procedures and functions
SELECT
2 AS SequenceNumber,
N'DROP PROCEDURE ' +
QUOTENAME(ROUTINE_SCHEMA) +
N'.' +
QUOTENAME(ROUTINE_NAME) AS DropStatement
FROM
INFORMATION_SCHEMA.ROUTINES
WHERE
ROUTINE_TYPE = N'FUNCTION' AND
OBJECTPROPERTY(
OBJECT_ID(QUOTENAME(ROUTINE_SCHEMA) +
N'.' +
QUOTENAME(ROUTINE_NAME)),
'IsSchemaBound') = 1 AND
OBJECTPROPERTY(
OBJECT_ID(QUOTENAME(ROUTINE_SCHEMA) +
N'.' +
QUOTENAME(ROUTINE_NAME)),
'IsMSShipped') = 0
UNION ALL
--foreign keys
SELECT
3 AS SequenceNumber,
N'ALTER TABLE ' +
QUOTENAME(TABLE_SCHEMA) +
N'.' +
QUOTENAME(TABLE_NAME) +
N' DROP CONSTRAINT ' +
CONSTRAINT_NAME AS DropStatement
FROM
INFORMATION_SCHEMA.TABLE_CONSTRAINTS
WHERE
CONSTRAINT_TYPE = N'FOREIGN KEY'
UNION ALL
--tables
SELECT
4 AS SequenceNumber,
N'DROP TABLE ' +
QUOTENAME(TABLE_SCHEMA) +
N'.' +
QUOTENAME(TABLE_NAME) AS DropStatement
FROM
INFORMATION_SCHEMA.TABLES
WHERE
TABLE_TYPE = N'BASE TABLE' AND
OBJECTPROPERTY(
OBJECT_ID(QUOTENAME(TABLE_SCHEMA) +
N'.' +
QUOTENAME(TABLE_NAME)),
'IsMSShipped') = 0
ORDER BY SequenceNumber
OPEN DropStatements
WHILE 1 = 1
BEGIN
FETCH NEXT FROM DropStatements INTO @.SequenceNumber, @.DropStatement
IF @.@.FETCH_STATUS = -1 BREAK
BEGIN
RAISERROR('%s', 0, 1, @.DropStatement) WITH NOWAIT
--EXECUTE sp_ExecuteSQL @.DropStatement
SET @.LastError = @.@.ERROR
IF @.LastError > 0
BEGIN
RAISERROR('Script terminated due to unexpected error', 16, 1)
GOTO Done
END
END
END
CLOSE DropStatements
DEALLOCATE DropStatements
Done:
GO
Hope this helps.
Dan Guzman
SQL Server MVP
"John" <John@.nospam.infovis.co.uk> wrote in message
news:%23vTMcTheHHA.1312@.TK2MSFTNGP03.phx.gbl...
> Hi
> Is there an easy way to drop all object of a db so it is as new? Just so I
> can run a script to recreate the objects.
> Thanks
> Regards
>
Just a thought here, but another way, if you're doing this often, might be to
make a backup of the empty database and restore it as needed or to make a
backup of the database files while the database is empty and detach/attach as
needed - though that second option doesn't sound nearly as fast as just about
any of the others but it, like the first option here, would give you clean
log files and database files.
Dale
Dale Preston
MCAD C#
MCSE, MCDBA
"Dan Guzman" wrote:

> Below is a script that will drop all objects. If the database is not large
> or you are using SQL 2005, you might find it easy to simply drop and
> recreate the database.
>
> IF DB_NAME() IN ('master', 'msdb', 'model', 'distribution')
> BEGIN
> RAISERROR('Not for use on system databases', 16, 1)
> GOTO Done
> END
> --Drop objects from current the database
> SET NOCOUNT ON
> DECLARE @.DropStatement nvarchar(4000)
> DECLARE @.SequenceNumber int
> DECLARE @.LastError int
> DECLARE @.TablesDropped int
> DECLARE DropStatements CURSOR
> LOCAL FAST_FORWARD READ_ONLY FOR
> --views
> SELECT
> 1 AS SequenceNumber,
> N'DROP VIEW ' +
> QUOTENAME(TABLE_SCHEMA) +
> N'.' +
> QUOTENAME(TABLE_NAME) AS DropStatement
> FROM
> INFORMATION_SCHEMA.TABLES
> WHERE
> TABLE_TYPE = N'VIEW' AND
> OBJECTPROPERTY(
> OBJECT_ID(QUOTENAME(TABLE_SCHEMA) +
> N'.' +
> QUOTENAME(TABLE_NAME)),
> 'IsSchemaBound') = 1 AND
> OBJECTPROPERTY(
> OBJECT_ID(QUOTENAME(TABLE_SCHEMA) +
> N'.' +
> QUOTENAME(TABLE_NAME)),
> 'IsMSShipped') = 0
> UNION ALL
> --procedures and functions
> SELECT
> 2 AS SequenceNumber,
> N'DROP PROCEDURE ' +
> QUOTENAME(ROUTINE_SCHEMA) +
> N'.' +
> QUOTENAME(ROUTINE_NAME) AS DropStatement
> FROM
> INFORMATION_SCHEMA.ROUTINES
> WHERE
> ROUTINE_TYPE = N'FUNCTION' AND
> OBJECTPROPERTY(
> OBJECT_ID(QUOTENAME(ROUTINE_SCHEMA) +
> N'.' +
> QUOTENAME(ROUTINE_NAME)),
> 'IsSchemaBound') = 1 AND
> OBJECTPROPERTY(
> OBJECT_ID(QUOTENAME(ROUTINE_SCHEMA) +
> N'.' +
> QUOTENAME(ROUTINE_NAME)),
> 'IsMSShipped') = 0
> UNION ALL
> --foreign keys
> SELECT
> 3 AS SequenceNumber,
> N'ALTER TABLE ' +
> QUOTENAME(TABLE_SCHEMA) +
> N'.' +
> QUOTENAME(TABLE_NAME) +
> N' DROP CONSTRAINT ' +
> CONSTRAINT_NAME AS DropStatement
> FROM
> INFORMATION_SCHEMA.TABLE_CONSTRAINTS
> WHERE
> CONSTRAINT_TYPE = N'FOREIGN KEY'
> UNION ALL
> --tables
> SELECT
> 4 AS SequenceNumber,
> N'DROP TABLE ' +
> QUOTENAME(TABLE_SCHEMA) +
> N'.' +
> QUOTENAME(TABLE_NAME) AS DropStatement
> FROM
> INFORMATION_SCHEMA.TABLES
> WHERE
> TABLE_TYPE = N'BASE TABLE' AND
> OBJECTPROPERTY(
> OBJECT_ID(QUOTENAME(TABLE_SCHEMA) +
> N'.' +
> QUOTENAME(TABLE_NAME)),
> 'IsMSShipped') = 0
> ORDER BY SequenceNumber
> OPEN DropStatements
> WHILE 1 = 1
> BEGIN
> FETCH NEXT FROM DropStatements INTO @.SequenceNumber, @.DropStatement
> IF @.@.FETCH_STATUS = -1 BREAK
> BEGIN
> RAISERROR('%s', 0, 1, @.DropStatement) WITH NOWAIT
> --EXECUTE sp_ExecuteSQL @.DropStatement
> SET @.LastError = @.@.ERROR
> IF @.LastError > 0
> BEGIN
> RAISERROR('Script terminated due to unexpected error', 16, 1)
> GOTO Done
> END
> END
> END
> CLOSE DropStatements
> DEALLOCATE DropStatements
> Done:
> GO
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "John" <John@.nospam.infovis.co.uk> wrote in message
> news:%23vTMcTheHHA.1312@.TK2MSFTNGP03.phx.gbl...
>
sql

Resetting DB

> Is there an easy way to drop all object of a db so it is as new? Just so I
> can run a script to recreate the objects.
Below is a script that will drop all objects. If the database is not large
or you are using SQL 2005, you might find it easy to simply drop and
recreate the database.
IF DB_NAME() IN ('master', 'msdb', 'model', 'distribution')
BEGIN
RAISERROR('Not for use on system databases', 16, 1)
GOTO Done
END
--Drop objects from current the database
SET NOCOUNT ON
DECLARE @.DropStatement nvarchar(4000)
DECLARE @.SequenceNumber int
DECLARE @.LastError int
DECLARE @.TablesDropped int
DECLARE DropStatements CURSOR
LOCAL FAST_FORWARD READ_ONLY FOR
--views
SELECT
1 AS SequenceNumber,
N'DROP VIEW ' +
QUOTENAME(TABLE_SCHEMA) +
N'.' +
QUOTENAME(TABLE_NAME) AS DropStatement
FROM
INFORMATION_SCHEMA.TABLES
WHERE
TABLE_TYPE = N'VIEW' AND
OBJECTPROPERTY(
OBJECT_ID(QUOTENAME(TABLE_SCHEMA) +
N'.' +
QUOTENAME(TABLE_NAME)),
'IsSchemaBound') = 1 AND
OBJECTPROPERTY(
OBJECT_ID(QUOTENAME(TABLE_SCHEMA) +
N'.' +
QUOTENAME(TABLE_NAME)),
'IsMSShipped') = 0
UNION ALL
--procedures and functions
SELECT
2 AS SequenceNumber,
N'DROP PROCEDURE ' +
QUOTENAME(ROUTINE_SCHEMA) +
N'.' +
QUOTENAME(ROUTINE_NAME) AS DropStatement
FROM
INFORMATION_SCHEMA.ROUTINES
WHERE
ROUTINE_TYPE = N'FUNCTION' AND
OBJECTPROPERTY(
OBJECT_ID(QUOTENAME(ROUTINE_SCHEMA) +
N'.' +
QUOTENAME(ROUTINE_NAME)),
'IsSchemaBound') = 1 AND
OBJECTPROPERTY(
OBJECT_ID(QUOTENAME(ROUTINE_SCHEMA) +
N'.' +
QUOTENAME(ROUTINE_NAME)),
'IsMSShipped') = 0
UNION ALL
--foreign keys
SELECT
3 AS SequenceNumber,
N'ALTER TABLE ' +
QUOTENAME(TABLE_SCHEMA) +
N'.' +
QUOTENAME(TABLE_NAME) +
N' DROP CONSTRAINT ' +
CONSTRAINT_NAME AS DropStatement
FROM
INFORMATION_SCHEMA.TABLE_CONSTRAINTS
WHERE
CONSTRAINT_TYPE = N'FOREIGN KEY'
UNION ALL
--tables
SELECT
4 AS SequenceNumber,
N'DROP TABLE ' +
QUOTENAME(TABLE_SCHEMA) +
N'.' +
QUOTENAME(TABLE_NAME) AS DropStatement
FROM
INFORMATION_SCHEMA.TABLES
WHERE
TABLE_TYPE = N'BASE TABLE' AND
OBJECTPROPERTY(
OBJECT_ID(QUOTENAME(TABLE_SCHEMA) +
N'.' +
QUOTENAME(TABLE_NAME)),
'IsMSShipped') = 0
ORDER BY SequenceNumber
OPEN DropStatements
WHILE 1 = 1
BEGIN
FETCH NEXT FROM DropStatements INTO @.SequenceNumber, @.DropStatement
IF @.@.FETCH_STATUS = -1 BREAK
BEGIN
RAISERROR('%s', 0, 1, @.DropStatement) WITH NOWAIT
--EXECUTE sp_ExecuteSQL @.DropStatement
SET @.LastError = @.@.ERROR
IF @.LastError > 0
BEGIN
RAISERROR('Script terminated due to unexpected error', 16, 1)
GOTO Done
END
END
END
CLOSE DropStatements
DEALLOCATE DropStatements
Done:
GO
Hope this helps.
Dan Guzman
SQL Server MVP
"John" <John@.nospam.infovis.co.uk> wrote in message
news:%23vTMcTheHHA.1312@.TK2MSFTNGP03.phx.gbl...
> Hi
> Is there an easy way to drop all object of a db so it is as new? Just so I
> can run a script to recreate the objects.
> Thanks
> Regards
>
Just a thought here, but another way, if you're doing this often, might be to
make a backup of the empty database and restore it as needed or to make a
backup of the database files while the database is empty and detach/attach as
needed - though that second option doesn't sound nearly as fast as just about
any of the others but it, like the first option here, would give you clean
log files and database files.
Dale
Dale Preston
MCAD C#
MCSE, MCDBA
"Dan Guzman" wrote:

> Below is a script that will drop all objects. If the database is not large
> or you are using SQL 2005, you might find it easy to simply drop and
> recreate the database.
>
> IF DB_NAME() IN ('master', 'msdb', 'model', 'distribution')
> BEGIN
> RAISERROR('Not for use on system databases', 16, 1)
> GOTO Done
> END
> --Drop objects from current the database
> SET NOCOUNT ON
> DECLARE @.DropStatement nvarchar(4000)
> DECLARE @.SequenceNumber int
> DECLARE @.LastError int
> DECLARE @.TablesDropped int
> DECLARE DropStatements CURSOR
> LOCAL FAST_FORWARD READ_ONLY FOR
> --views
> SELECT
> 1 AS SequenceNumber,
> N'DROP VIEW ' +
> QUOTENAME(TABLE_SCHEMA) +
> N'.' +
> QUOTENAME(TABLE_NAME) AS DropStatement
> FROM
> INFORMATION_SCHEMA.TABLES
> WHERE
> TABLE_TYPE = N'VIEW' AND
> OBJECTPROPERTY(
> OBJECT_ID(QUOTENAME(TABLE_SCHEMA) +
> N'.' +
> QUOTENAME(TABLE_NAME)),
> 'IsSchemaBound') = 1 AND
> OBJECTPROPERTY(
> OBJECT_ID(QUOTENAME(TABLE_SCHEMA) +
> N'.' +
> QUOTENAME(TABLE_NAME)),
> 'IsMSShipped') = 0
> UNION ALL
> --procedures and functions
> SELECT
> 2 AS SequenceNumber,
> N'DROP PROCEDURE ' +
> QUOTENAME(ROUTINE_SCHEMA) +
> N'.' +
> QUOTENAME(ROUTINE_NAME) AS DropStatement
> FROM
> INFORMATION_SCHEMA.ROUTINES
> WHERE
> ROUTINE_TYPE = N'FUNCTION' AND
> OBJECTPROPERTY(
> OBJECT_ID(QUOTENAME(ROUTINE_SCHEMA) +
> N'.' +
> QUOTENAME(ROUTINE_NAME)),
> 'IsSchemaBound') = 1 AND
> OBJECTPROPERTY(
> OBJECT_ID(QUOTENAME(ROUTINE_SCHEMA) +
> N'.' +
> QUOTENAME(ROUTINE_NAME)),
> 'IsMSShipped') = 0
> UNION ALL
> --foreign keys
> SELECT
> 3 AS SequenceNumber,
> N'ALTER TABLE ' +
> QUOTENAME(TABLE_SCHEMA) +
> N'.' +
> QUOTENAME(TABLE_NAME) +
> N' DROP CONSTRAINT ' +
> CONSTRAINT_NAME AS DropStatement
> FROM
> INFORMATION_SCHEMA.TABLE_CONSTRAINTS
> WHERE
> CONSTRAINT_TYPE = N'FOREIGN KEY'
> UNION ALL
> --tables
> SELECT
> 4 AS SequenceNumber,
> N'DROP TABLE ' +
> QUOTENAME(TABLE_SCHEMA) +
> N'.' +
> QUOTENAME(TABLE_NAME) AS DropStatement
> FROM
> INFORMATION_SCHEMA.TABLES
> WHERE
> TABLE_TYPE = N'BASE TABLE' AND
> OBJECTPROPERTY(
> OBJECT_ID(QUOTENAME(TABLE_SCHEMA) +
> N'.' +
> QUOTENAME(TABLE_NAME)),
> 'IsMSShipped') = 0
> ORDER BY SequenceNumber
> OPEN DropStatements
> WHILE 1 = 1
> BEGIN
> FETCH NEXT FROM DropStatements INTO @.SequenceNumber, @.DropStatement
> IF @.@.FETCH_STATUS = -1 BREAK
> BEGIN
> RAISERROR('%s', 0, 1, @.DropStatement) WITH NOWAIT
> --EXECUTE sp_ExecuteSQL @.DropStatement
> SET @.LastError = @.@.ERROR
> IF @.LastError > 0
> BEGIN
> RAISERROR('Script terminated due to unexpected error', 16, 1)
> GOTO Done
> END
> END
> END
> CLOSE DropStatements
> DEALLOCATE DropStatements
> Done:
> GO
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "John" <John@.nospam.infovis.co.uk> wrote in message
> news:%23vTMcTheHHA.1312@.TK2MSFTNGP03.phx.gbl...
>

Reset Identity

Hi, there is a way to reset identity field of many tables via storeprocedure
?
i try with the scripts below, but they don't work !
BACKUP LOG test_dbWITH TRUNCATE_ONLY
DBCC shrinkdatabase (test_db)
and also with
create table #table(
idTabella int,
nome varchar(4000)
)
insert into #table
SELECT dbo.sysobjects.id, dbo.sysobjects.name
FROM dbo.sysobjects INNER JOIN
dbo.syscolumns ON dbo.sysobjects.id =
dbo.syscolumns.id INNER JOIN
dbo.systypes ON dbo.syscolumns.xtype =
dbo.systypes.xtype
WHERE (dbo.syscolumns.status = 128)
declare @.NomeTabella as varchar(4000)
declare @.TabellaID int
select @.TabellaID =idTabella,@.NomeTabella =nome from #table
while exists(select idTabella from #table)
begin
DBCC CHECKIDENT(@.NomeTabella, RESEED)
delete from #table where idTabella = @.TabellaID
select @.TabellaID =idTabella,@.NomeTabella =nome from #table
end"Alessandro" schrieb:
> Hi, there is a way to reset identity field of many tables via storeprocedu
re
> ?
> i try with the scripts below, but they don't work !
> BACKUP LOG test_dbWITH TRUNCATE_ONLY
> DBCC shrinkdatabase (test_db)
> and also with
> create table #table(
> idTabella int,
> nome varchar(4000)
> )
> insert into #table
> SELECT dbo.sysobjects.id, dbo.sysobjects.name
> FROM dbo.sysobjects INNER JOIN
> dbo.syscolumns ON dbo.sysobjects.id =
> dbo.syscolumns.id INNER JOIN
> dbo.systypes ON dbo.syscolumns.xtype =
> dbo.systypes.xtype
> WHERE (dbo.syscolumns.status = 128)
> declare @.NomeTabella as varchar(4000)
> declare @.TabellaID int
> select @.TabellaID =idTabella,@.NomeTabella =nome from #table
> while exists(select idTabella from #table)
> begin
> DBCC CHECKIDENT(@.NomeTabella, RESEED)
> delete from #table where idTabella = @.TabellaID
> select @.TabellaID =idTabella,@.NomeTabella =nome from #table
> end
The follwing procedure reseeds all ID-cols in the db. Tables without an
ID-col return an error that you can ignore ...
declare @.table varchar(256)
declare cu cursor for select [name] from sysobjects where xtype = 'U'
open cu
fetch next from cu into @.table
while @.@.fetch_status = 0
begin
dbcc checkident (@.table, RESEED)
fetch next from cu into @.table
end
close cu deallocate cu

Required Security for Report Viewer on Windows 2003 SP1 Web Server

When trying to run a local report using the RS Report Viewer for VS
2005 we get the below exception when the User Account for the
Application Pool we are using is not in the Administrators Group of the
web server.
Does anybody have any suggestions as to what specific security is
needed to use the Report Viewer?
Could this be because we built the application as a "Web Project"
instead of a "Web Site"?
Microsoft.Reporting.WebForms.LocalProcessingException: An error
occurred during local report processing. -->
Microsoft.Reporting.DefinitionInvalidException: The definition of the
report 'Main Report' is invalid. -->
Microsoft.ReportingServices.ReportProcessing.ReportProcessingException:
An unexpected error occurred while compiling expressions. Native
compiler return value: '[BC2001] file 'C:\WINDOWS\TEMP\n_rkco4n.0.vb'
could not be found'. -- End of inner exception stack trace -- at
AMR.AdvertiserClientList.RefreshReport() in C:\Documents and
Settings\god\My Documents\Visual Studio
2005\Projects\AMR\AdvertiserClientList.aspx.vb:line 192 at
AMR.AdvertiserClientList.RUNREPORT_Click(Object sender, EventArgs e) in
C:\Documents and Settings\God\My Documents\Visual Studio
2005\Projects\AMR\AdvertiserClientList.aspx.vb:line 50 ::
InnerException: Microsoft.Reporting.DefinitionInvalidException: The
definition of the report 'Main Report' is invalid. -->
Microsoft.ReportingServices.ReportProcessing.ReportProcessingException:
An unexpected error occurred while compiling expressions. Native
compiler return value: '[BC2001] file 'C:\WINDOWS\TEMP\n_rkco4n.0.vb'
could not be found'.This error is also logged in the system event viewer, however granting
access NETMAN in the DCOM manager only keeps the errors from showing
and does not fix the problem. The CLSID refers to tthe Network
Connection Manager Class (under the NETMAN application).
The application-specific permission settings do not grant Local
Activation permission for the COM Server application with CLSID
{BA126AD1-2166-11D1-B1D0-00805FC1270E}
to the user AMI\srvAcctAppAccessDEV SID
(S-1-5-21-2975352776-793034493-3225607600-10632). This security
permission can be modified using the Component Services administrative
tool.
tfelber@.gmail.com wrote:
> When trying to run a local report using the RS Report Viewer for VS
> 2005 we get the below exception when the User Account for the
> Application Pool we are using is not in the Administrators Group of the
> web server.
> Does anybody have any suggestions as to what specific security is
> needed to use the Report Viewer?
> Could this be because we built the application as a "Web Project"
> instead of a "Web Site"?
> Microsoft.Reporting.WebForms.LocalProcessingException: An error
> occurred during local report processing. -->
> Microsoft.Reporting.DefinitionInvalidException: The definition of the
> report 'Main Report' is invalid. -->
> Microsoft.ReportingServices.ReportProcessing.ReportProcessingException:
> An unexpected error occurred while compiling expressions. Native
> compiler return value: '[BC2001] file 'C:\WINDOWS\TEMP\n_rkco4n.0.vb'
> could not be found'. -- End of inner exception stack trace -- at
> AMR.AdvertiserClientList.RefreshReport() in C:\Documents and
> Settings\god\My Documents\Visual Studio
> 2005\Projects\AMR\AdvertiserClientList.aspx.vb:line 192 at
> AMR.AdvertiserClientList.RUNREPORT_Click(Object sender, EventArgs e) in
> C:\Documents and Settings\God\My Documents\Visual Studio
> 2005\Projects\AMR\AdvertiserClientList.aspx.vb:line 50 ::
> InnerException: Microsoft.Reporting.DefinitionInvalidException: The
> definition of the report 'Main Report' is invalid. -->
> Microsoft.ReportingServices.ReportProcessing.ReportProcessingException:
> An unexpected error occurred while compiling expressions. Native
> compiler return value: '[BC2001] file 'C:\WINDOWS\TEMP\n_rkco4n.0.vb'
> could not be found'.|||Finally figured it out.
The configurable identity that our application pool was using under
IIS 6.0 needed read/write access to the c:\Windows\Temp directory.
(Explains why we could get this to work when the account was part of
the Administrators or Power Users Group). I suppose this is to give it
temp space when rendering large reports.
Hopefully this helps somebody else out one day.
tfelber@.gmail.com wrote:
> This error is also logged in the system event viewer, however granting
> access NETMAN in the DCOM manager only keeps the errors from showing
> and does not fix the problem. The CLSID refers to tthe Network
> Connection Manager Class (under the NETMAN application).
> The application-specific permission settings do not grant Local
> Activation permission for the COM Server application with CLSID
> {BA126AD1-2166-11D1-B1D0-00805FC1270E}
> to the user AMI\srvAcctAppAccessDEV SID
> (S-1-5-21-2975352776-793034493-3225607600-10632). This security
> permission can be modified using the Component Services administrative
> tool.
>
> tfelber@.gmail.com wrote:
> > When trying to run a local report using the RS Report Viewer for VS
> > 2005 we get the below exception when the User Account for the
> > Application Pool we are using is not in the Administrators Group of the
> > web server.
> >
> > Does anybody have any suggestions as to what specific security is
> > needed to use the Report Viewer?
> >
> > Could this be because we built the application as a "Web Project"
> > instead of a "Web Site"?
> >
> > Microsoft.Reporting.WebForms.LocalProcessingException: An error
> > occurred during local report processing. -->
> > Microsoft.Reporting.DefinitionInvalidException: The definition of the
> > report 'Main Report' is invalid. -->
> > Microsoft.ReportingServices.ReportProcessing.ReportProcessingException:
> > An unexpected error occurred while compiling expressions. Native
> > compiler return value: '[BC2001] file 'C:\WINDOWS\TEMP\n_rkco4n.0.vb'
> > could not be found'. -- End of inner exception stack trace -- at
> > AMR.AdvertiserClientList.RefreshReport() in C:\Documents and
> > Settings\god\My Documents\Visual Studio
> > 2005\Projects\AMR\AdvertiserClientList.aspx.vb:line 192 at
> > AMR.AdvertiserClientList.RUNREPORT_Click(Object sender, EventArgs e) in
> > C:\Documents and Settings\God\My Documents\Visual Studio
> > 2005\Projects\AMR\AdvertiserClientList.aspx.vb:line 50 ::
> > InnerException: Microsoft.Reporting.DefinitionInvalidException: The
> > definition of the report 'Main Report' is invalid. -->
> > Microsoft.ReportingServices.ReportProcessing.ReportProcessingException:
> > An unexpected error occurred while compiling expressions. Native
> > compiler return value: '[BC2001] file 'C:\WINDOWS\TEMP\n_rkco4n.0.vb'
> > could not be found'.

Require an SQL

Hi all
From one of the table having the values like below, I need
to take only top 3 records for each EquipmentID.
(I don't prefer to use a simple stored procedure using
temp table) Expecting an SQL Statement if possible.
EquipmentID AreaID AlarmID Duration
-- -- -- --
L4-BELine 1 8241 17484
L4-BELine 1 6038 62
L4-BELine 1 2042 52
L4-BELine 1 8005 32
L4-BELine 1 1013 28
L4-BELine 1 3054 24
L4-BELine 1 5005 24
L4-BUF1 1 1 17340
L4-BUF1 1 2 2056
L4-BUF1 1 8 856
L4-DA01 1 18 6196
L4-DA01 1 1 4924
L4-DA01 1 200 4390
L4-DA01 1 33 24
L4-DA01 1 74 18
L4-DA02 1 80 3920
L4-DA02 1 73 2858
L4-DA02 1 18 2214
L4-DA02 1 203 458
L4-DA02 1 74 346
So the Result expected is as follows:
EquipmentID AreaID AlarmID Duration
-- -- -- --
L4-BELine 1 8241 17484
L4-BELine 1 6038 62
L4-BELine 1 2042 52
L4-BUF1 1 1 17340
L4-BUF1 1 2 2056
L4-BUF1 1 8 856
L4-DA01 1 18 6196
L4-DA01 1 1 4924
L4-DA01 1 200 4390
L4-DA02 1 80 3920
L4-DA02 1 73 2858
L4-DA02 1 18 2214
To try I am here with giving the Schema and data
CREATE TABLE [dbo].[tempRTM_EQM_Top10Alarms] (
[EquipmentID] [nvarchar] (12) COLLATE
SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[AreaID] [int] NOT NULL ,
[AlarmID] [int] NULL ,
[Duration] [int] NULL
) ON [PRIMARY]
GO
Data:
INSERT INTO [temprtm_eqm_Top10Alarms] ([EquipmentID],
[AreaID],[AlarmID],[Duration])VALUES('L4-
BELine',1,8241,17484)
INSERT INTO [temprtm_eqm_Top10Alarms] ([EquipmentID],
[AreaID],[AlarmID],[Duration])VALUES('L4-BELine',1,6038,62)
INSERT INTO [temprtm_eqm_Top10Alarms] ([EquipmentID],
[AreaID],[AlarmID],[Duration])VALUES('L4-BELine',1,2042,52)
INSERT INTO [temprtm_eqm_Top10Alarms] ([EquipmentID],
[AreaID],[AlarmID],[Duration])VALUES('L4-BELine',1,8005,32)
INSERT INTO [temprtm_eqm_Top10Alarms] ([EquipmentID],
[AreaID],[AlarmID],[Duration])VALUES('L4-BELine',1,1013,28)
INSERT INTO [temprtm_eqm_Top10Alarms] ([EquipmentID],
[AreaID],[AlarmID],[Duration])VALUES('L4-BELine',1,3054,24)
INSERT INTO [temprtm_eqm_Top10Alarms] ([EquipmentID],
[AreaID],[AlarmID],[Duration])VALUES('L4-BELine',1,5005,24)
INSERT INTO [temprtm_eqm_Top10Alarms] ([EquipmentID],
[AreaID],[AlarmID],[Duration])VALUES('L4-BUF1',1,1,17340)
INSERT INTO [temprtm_eqm_Top10Alarms] ([EquipmentID],
[AreaID],[AlarmID],[Duration])VALUES('L4-BUF1',1,2,2056)
INSERT INTO [temprtm_eqm_Top10Alarms] ([EquipmentID],
[AreaID],[AlarmID],[Duration])VALUES('L4-BUF1',1,8,856)
INSERT INTO [temprtm_eqm_Top10Alarms] ([EquipmentID],
[AreaID],[AlarmID],[Duration])VALUES('L4-DA01',1,18,6196)
INSERT INTO [temprtm_eqm_Top10Alarms] ([EquipmentID],
[AreaID],[AlarmID],[Duration])VALUES('L4-DA01',1,1,4924)
INSERT INTO [temprtm_eqm_Top10Alarms] ([EquipmentID],
[AreaID],[AlarmID],[Duration])VALUES('L4-DA01',1,200,4390)
INSERT INTO [temprtm_eqm_Top10Alarms] ([EquipmentID],
[AreaID],[AlarmID],[Duration])VALUES('L4-DA01',1,33,24)
INSERT INTO [temprtm_eqm_Top10Alarms] ([EquipmentID],
[AreaID],[AlarmID],[Duration])VALUES('L4-DA01',1,74,18)
INSERT INTO [temprtm_eqm_Top10Alarms] ([EquipmentID],
[AreaID],[AlarmID],[Duration])VALUES('L4-DA02',1,80,3920)
INSERT INTO [temprtm_eqm_Top10Alarms] ([EquipmentID],
[AreaID],[AlarmID],[Duration])VALUES('L4-DA02',1,73,2858)
INSERT INTO [temprtm_eqm_Top10Alarms] ([EquipmentID],
[AreaID],[AlarmID],[Duration])VALUES('L4-DA02',1,18,2214)
INSERT INTO [temprtm_eqm_Top10Alarms] ([EquipmentID],
[AreaID],[AlarmID],[Duration])VALUES('L4-DA02',1,203,458)
INSERT INTO [temprtm_eqm_Top10Alarms] ([EquipmentID],
[AreaID],[AlarmID],[Duration])VALUES('L4-DA02',1,74,346)
Thanks in advance.
AnandYou probably want something like this
SELECT EquipmentID, AreaID, AlarmID, Duration
FROM tempRTM_EQM_Top10Alarms A
WHERE AlarmID IN (SELECT TOP 3 AlarmID FROM tempRTM_EQM_Top10Alarms B WHERE
B.EquipmentID = A.EquipmentID ORDER BY AlarmID DESC)
ORDER BY EquipmentID, AlarmID
--
--
Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org
"Anand" <gurusanand1@.sifymail.com> wrote in message
news:09be01c366f5$ffb8fd00$a501280a@.phx.gbl...
> Hi all
> From one of the table having the values like below, I need
> to take only top 3 records for each EquipmentID.
> (I don't prefer to use a simple stored procedure using
> temp table) Expecting an SQL Statement if possible.
> EquipmentID AreaID AlarmID Duration
> -- -- -- --
> L4-BELine 1 8241 17484
> L4-BELine 1 6038 62
> L4-BELine 1 2042 52
> L4-BELine 1 8005 32
> L4-BELine 1 1013 28
> L4-BELine 1 3054 24
> L4-BELine 1 5005 24
> L4-BUF1 1 1 17340
> L4-BUF1 1 2 2056
> L4-BUF1 1 8 856
> L4-DA01 1 18 6196
> L4-DA01 1 1 4924
> L4-DA01 1 200 4390
> L4-DA01 1 33 24
> L4-DA01 1 74 18
> L4-DA02 1 80 3920
> L4-DA02 1 73 2858
> L4-DA02 1 18 2214
> L4-DA02 1 203 458
> L4-DA02 1 74 346
> So the Result expected is as follows:
> EquipmentID AreaID AlarmID Duration
> -- -- -- --
> L4-BELine 1 8241 17484
> L4-BELine 1 6038 62
> L4-BELine 1 2042 52
> L4-BUF1 1 1 17340
> L4-BUF1 1 2 2056
> L4-BUF1 1 8 856
> L4-DA01 1 18 6196
> L4-DA01 1 1 4924
> L4-DA01 1 200 4390
> L4-DA02 1 80 3920
> L4-DA02 1 73 2858
> L4-DA02 1 18 2214
>
> To try I am here with giving the Schema and data
> CREATE TABLE [dbo].[tempRTM_EQM_Top10Alarms] (
> [EquipmentID] [nvarchar] (12) COLLATE
> SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [AreaID] [int] NOT NULL ,
> [AlarmID] [int] NULL ,
> [Duration] [int] NULL
> ) ON [PRIMARY]
> GO
> Data:
> INSERT INTO [temprtm_eqm_Top10Alarms] ([EquipmentID],
> [AreaID],[AlarmID],[Duration])VALUES('L4-
> BELine',1,8241,17484)
> INSERT INTO [temprtm_eqm_Top10Alarms] ([EquipmentID],
> [AreaID],[AlarmID],[Duration])VALUES('L4-BELine',1,6038,62)
> INSERT INTO [temprtm_eqm_Top10Alarms] ([EquipmentID],
> [AreaID],[AlarmID],[Duration])VALUES('L4-BELine',1,2042,52)
> INSERT INTO [temprtm_eqm_Top10Alarms] ([EquipmentID],
> [AreaID],[AlarmID],[Duration])VALUES('L4-BELine',1,8005,32)
> INSERT INTO [temprtm_eqm_Top10Alarms] ([EquipmentID],
> [AreaID],[AlarmID],[Duration])VALUES('L4-BELine',1,1013,28)
> INSERT INTO [temprtm_eqm_Top10Alarms] ([EquipmentID],
> [AreaID],[AlarmID],[Duration])VALUES('L4-BELine',1,3054,24)
> INSERT INTO [temprtm_eqm_Top10Alarms] ([EquipmentID],
> [AreaID],[AlarmID],[Duration])VALUES('L4-BELine',1,5005,24)
> INSERT INTO [temprtm_eqm_Top10Alarms] ([EquipmentID],
> [AreaID],[AlarmID],[Duration])VALUES('L4-BUF1',1,1,17340)
> INSERT INTO [temprtm_eqm_Top10Alarms] ([EquipmentID],
> [AreaID],[AlarmID],[Duration])VALUES('L4-BUF1',1,2,2056)
> INSERT INTO [temprtm_eqm_Top10Alarms] ([EquipmentID],
> [AreaID],[AlarmID],[Duration])VALUES('L4-BUF1',1,8,856)
> INSERT INTO [temprtm_eqm_Top10Alarms] ([EquipmentID],
> [AreaID],[AlarmID],[Duration])VALUES('L4-DA01',1,18,6196)
> INSERT INTO [temprtm_eqm_Top10Alarms] ([EquipmentID],
> [AreaID],[AlarmID],[Duration])VALUES('L4-DA01',1,1,4924)
> INSERT INTO [temprtm_eqm_Top10Alarms] ([EquipmentID],
> [AreaID],[AlarmID],[Duration])VALUES('L4-DA01',1,200,4390)
> INSERT INTO [temprtm_eqm_Top10Alarms] ([EquipmentID],
> [AreaID],[AlarmID],[Duration])VALUES('L4-DA01',1,33,24)
> INSERT INTO [temprtm_eqm_Top10Alarms] ([EquipmentID],
> [AreaID],[AlarmID],[Duration])VALUES('L4-DA01',1,74,18)
> INSERT INTO [temprtm_eqm_Top10Alarms] ([EquipmentID],
> [AreaID],[AlarmID],[Duration])VALUES('L4-DA02',1,80,3920)
> INSERT INTO [temprtm_eqm_Top10Alarms] ([EquipmentID],
> [AreaID],[AlarmID],[Duration])VALUES('L4-DA02',1,73,2858)
> INSERT INTO [temprtm_eqm_Top10Alarms] ([EquipmentID],
> [AreaID],[AlarmID],[Duration])VALUES('L4-DA02',1,18,2214)
> INSERT INTO [temprtm_eqm_Top10Alarms] ([EquipmentID],
> [AreaID],[AlarmID],[Duration])VALUES('L4-DA02',1,203,458)
> INSERT INTO [temprtm_eqm_Top10Alarms] ([EquipmentID],
> [AreaID],[AlarmID],[Duration])VALUES('L4-DA02',1,74,346)
>
> Thanks in advance.
> Anand|||Hi Allan
This is what I want. Thanks a lot.
Anand.
>--Original Message--
>You probably want something like this
>
>SELECT EquipmentID, AreaID, AlarmID, Duration
>FROM tempRTM_EQM_Top10Alarms A
>WHERE AlarmID IN (SELECT TOP 3 AlarmID FROM
tempRTM_EQM_Top10Alarms B WHERE
>B.EquipmentID = A.EquipmentID ORDER BY AlarmID DESC)
>ORDER BY EquipmentID, AlarmID
>--
>--
>Allan Mitchell (Microsoft SQL Server MVP)
>MCSE,MCDBA
>www.SQLDTS.com
>I support PASS - the definitive, global community
>for SQL Server professionals - http://www.sqlpass.org
>
>"Anand" <gurusanand1@.sifymail.com> wrote in message
>news:09be01c366f5$ffb8fd00$a501280a@.phx.gbl...
>> Hi all
>> From one of the table having the values like below, I
need
>> to take only top 3 records for each EquipmentID.
>> (I don't prefer to use a simple stored procedure using
>> temp table) Expecting an SQL Statement if possible.
>> EquipmentID AreaID AlarmID Duration
>> -- -- -- --
>> L4-BELine 1 8241 17484
>> L4-BELine 1 6038 62
>> L4-BELine 1 2042 52
>> L4-BELine 1 8005 32
>> L4-BELine 1 1013 28
>> L4-BELine 1 3054 24
>> L4-BELine 1 5005 24
>> L4-BUF1 1 1 17340
>> L4-BUF1 1 2 2056
>> L4-BUF1 1 8 856
>> L4-DA01 1 18 6196
>> L4-DA01 1 1 4924
>> L4-DA01 1 200 4390
>> L4-DA01 1 33 24
>> L4-DA01 1 74 18
>> L4-DA02 1 80 3920
>> L4-DA02 1 73 2858
>> L4-DA02 1 18 2214
>> L4-DA02 1 203 458
>> L4-DA02 1 74 346
>> So the Result expected is as follows:
>> EquipmentID AreaID AlarmID Duration
>> -- -- -- --
>> L4-BELine 1 8241 17484
>> L4-BELine 1 6038 62
>> L4-BELine 1 2042 52
>> L4-BUF1 1 1 17340
>> L4-BUF1 1 2 2056
>> L4-BUF1 1 8 856
>> L4-DA01 1 18 6196
>> L4-DA01 1 1 4924
>> L4-DA01 1 200 4390
>> L4-DA02 1 80 3920
>> L4-DA02 1 73 2858
>> L4-DA02 1 18 2214
>>
>> To try I am here with giving the Schema and data
>> CREATE TABLE [dbo].[tempRTM_EQM_Top10Alarms] (
>> [EquipmentID] [nvarchar] (12) COLLATE
>> SQL_Latin1_General_CP1_CI_AS NOT NULL ,
>> [AreaID] [int] NOT NULL ,
>> [AlarmID] [int] NULL ,
>> [Duration] [int] NULL
>> ) ON [PRIMARY]
>> GO
>> Data:
>> INSERT INTO [temprtm_eqm_Top10Alarms] ([EquipmentID],
>> [AreaID],[AlarmID],[Duration])VALUES('L4-
>> BELine',1,8241,17484)
>> INSERT INTO [temprtm_eqm_Top10Alarms] ([EquipmentID],
>> [AreaID],[AlarmID],[Duration])VALUES('L4-
BELine',1,6038,62)
>> INSERT INTO [temprtm_eqm_Top10Alarms] ([EquipmentID],
>> [AreaID],[AlarmID],[Duration])VALUES('L4-
BELine',1,2042,52)
>> INSERT INTO [temprtm_eqm_Top10Alarms] ([EquipmentID],
>> [AreaID],[AlarmID],[Duration])VALUES('L4-
BELine',1,8005,32)
>> INSERT INTO [temprtm_eqm_Top10Alarms] ([EquipmentID],
>> [AreaID],[AlarmID],[Duration])VALUES('L4-
BELine',1,1013,28)
>> INSERT INTO [temprtm_eqm_Top10Alarms] ([EquipmentID],
>> [AreaID],[AlarmID],[Duration])VALUES('L4-
BELine',1,3054,24)
>> INSERT INTO [temprtm_eqm_Top10Alarms] ([EquipmentID],
>> [AreaID],[AlarmID],[Duration])VALUES('L4-
BELine',1,5005,24)
>> INSERT INTO [temprtm_eqm_Top10Alarms] ([EquipmentID],
>> [AreaID],[AlarmID],[Duration])VALUES('L4-
BUF1',1,1,17340)
>> INSERT INTO [temprtm_eqm_Top10Alarms] ([EquipmentID],
>> [AreaID],[AlarmID],[Duration])VALUES('L4-BUF1',1,2,2056)
>> INSERT INTO [temprtm_eqm_Top10Alarms] ([EquipmentID],
>> [AreaID],[AlarmID],[Duration])VALUES('L4-BUF1',1,8,856)
>> INSERT INTO [temprtm_eqm_Top10Alarms] ([EquipmentID],
>> [AreaID],[AlarmID],[Duration])VALUES('L4-
DA01',1,18,6196)
>> INSERT INTO [temprtm_eqm_Top10Alarms] ([EquipmentID],
>> [AreaID],[AlarmID],[Duration])VALUES('L4-DA01',1,1,4924)
>> INSERT INTO [temprtm_eqm_Top10Alarms] ([EquipmentID],
>> [AreaID],[AlarmID],[Duration])VALUES('L4-
DA01',1,200,4390)
>> INSERT INTO [temprtm_eqm_Top10Alarms] ([EquipmentID],
>> [AreaID],[AlarmID],[Duration])VALUES('L4-DA01',1,33,24)
>> INSERT INTO [temprtm_eqm_Top10Alarms] ([EquipmentID],
>> [AreaID],[AlarmID],[Duration])VALUES('L4-DA01',1,74,18)
>> INSERT INTO [temprtm_eqm_Top10Alarms] ([EquipmentID],
>> [AreaID],[AlarmID],[Duration])VALUES('L4-
DA02',1,80,3920)
>> INSERT INTO [temprtm_eqm_Top10Alarms] ([EquipmentID],
>> [AreaID],[AlarmID],[Duration])VALUES('L4-
DA02',1,73,2858)
>> INSERT INTO [temprtm_eqm_Top10Alarms] ([EquipmentID],
>> [AreaID],[AlarmID],[Duration])VALUES('L4-
DA02',1,18,2214)
>> INSERT INTO [temprtm_eqm_Top10Alarms] ([EquipmentID],
>> [AreaID],[AlarmID],[Duration])VALUES('L4-
DA02',1,203,458)
>> INSERT INTO [temprtm_eqm_Top10Alarms] ([EquipmentID],
>> [AreaID],[AlarmID],[Duration])VALUES('L4-DA02',1,74,346)
>>
>> Thanks in advance.
>> Anand
>
>.
>

repoting services charts rendering

Hi Guys
Can any body help me for this problem.
I am having a table and two charts. These charts are places one below
the other. When ever i view the report the first chart shows up fine at
the side of the table. But the other chart which was placed below the
first chart shows up after the table. Dont know why. Is there any way
so that i can fix the position of this second chart so that it can show
immediately after the first chart instead of showing after the table .
The table is a drill down table and and when ever i drill down the data
the second chart also moves down accordingly again ending up showing
after the first table.
Can anybody help me with this problem.....Add a rectangle to whichever side of the table you want the charts to
appear, and place both of your charts within the rectangle. This will
group the two charts together, and as long as the page layout is set so
the charts and table both fit horizontally, should solve the problem
you are having.
Passx wrote:
> Hi Guys
> Can any body help me for this problem.
> I am having a table and two charts. These charts are places one below
> the other. When ever i view the report the first chart shows up fine at
> the side of the table. But the other chart which was placed below the
> first chart shows up after the table. Dont know why. Is there any way
> so that i can fix the position of this second chart so that it can show
> immediately after the first chart instead of showing after the table .
> The table is a drill down table and and when ever i drill down the data
> the second chart also moves down accordingly again ending up showing
> after the first table.
> Can anybody help me with this problem.....|||Topher wrote:
> Add a rectangle to whichever side of the table you want the charts to
> appear, and place both of your charts within the rectangle. This will
> group the two charts together, and as long as the page layout is set so
> the charts and table both fit horizontally, should solve the problem
> you are having.
>
> Passx wrote:
> > Hi Guys
> >
> > Can any body help me for this problem.
> >
> > I am having a table and two charts. These charts are places one below
> > the other. When ever i view the report the first chart shows up fine at
> > the side of the table. But the other chart which was placed below the
> > first chart shows up after the table. Dont know why. Is there any way
> > so that i can fix the position of this second chart so that it can show
> > immediately after the first chart instead of showing after the table .
> > The table is a drill down table and and when ever i drill down the data
> > the second chart also moves down accordingly again ending up showing
> > after the first table.
> >
> > Can anybody help me with this problem.....

REPOST: Merge Replication Conditional Filter with UDF Problem - Help !

Hi all,
I'm still stuck on this issue. Please find a re-cap below. I'd be really grateful if anyone
could shed some light on this.Thanks a million :o)
I am performing merge replication between SQL CE/Server 2000.
I would like to select the stores specific to each rep
unless the repid = 6, in which case I would like to select
all stores. Is this possible in merge replication?
I have been attempting to set the host_name() to my repID and
passing this Host_Name() into a table-valued UDF. The aim
being that the UDF will return a table of stores. My problem
is that I'm receiving an error and I would like to know if
UDF's accept Host_Name() as a parameter ?
My Dynamic Filter ...
SELECT <published_columns> FROM [dbo].[Stores] WHERE
Stores.RepID IN (SELECT * FROM SELECT_Stores_RepID(Host_Name()))
The Error I'm receiving ...
Error 170: Line 1: Incorrect Syntax near '('.
Cube,this is my previous reply:"I have only been able to use UDFs before
when they are scalar, eg SELECT <published_columns> FROM [dbo].[Region]
WHERE region.regionid = dbo.fn_TaxRate(host_name())works fine. In your case
can you restructure your query to return a scalar value and then use this
value combined with another table in a join + where clause perhaps?"You
mentioned that you would give it a try and post back your results - how did
it go?Regards,Paul Ibison
|||Hi Paul,
I gave this a go but I didn't have much joy, maybe I'm just confused as to how to
go about things. As you mentioned I changed my Dynamic Filter so that I returned
the RepID ...
SELECT <published_columns> FROM [dbo].[tbl_Usr] WHERE
CAST(FK_Rep_ID AS char(4)) = Host_Name()
Next I attempted to use this in an Extended Filter (which is where I ran into problems) ...
SELECT <published_columns> FROM [dbo].[tbl_Usr] INNER JOIN
[dbo].[Stores] ON Stores.RepID IN (SELECT * FROM
SELECT_Stores_RepID(tbl_Usr.TKT_RepID))
This product the syntax error ...
Error 170: Line 1: Incorrect Syntax near '.'.
This will work if I pass in a constant instead of tbl_Usr.TKT_RepID, which of
course I can't do. (At this stage you're probably saying to yourself...this guy just
tried the same thing he did in the Dynamic Filter...and is chasing his tail ;o)
I'm very open to trying alternative queries/approaches. At this stage I've looked at this
so many times I need some devine inspiration ;o)
Thanks in advance,
|||OK - I'll give it a try and post up an example later this afternoon (5pm UK
time).
Regards,
Paul
|||OK, have tested it and it works. Here is how I did it:
I had 2 tables: MainTable and LookupTable
MainTable
ID,Description
1,aaa
2,bbb
3,ccc
LookupTable
ID,Description,Host
1,aaa,Host1
2,bbb,Host1
3,ccc,Host999
My filter is LookupTable.Host = Host_Name()
The tables are joined on LookupTbale.ID = MainTable.ID
I set the -HOSTNAME parameter on the merge agent to be Host1 and only 2
records from each table are replicated. This successfully mimics what you
are doing with the subquery.
Any questions, please post back.
Paul Ibison
|||Hi Paul,
This is what I've been doing to date. But what I'd like is to select ALL records
if Host_Name() = some Rep ID set by the Merge Agent. Pseudocode...
IF (Host_Name() = 6)
SELECT * FROM Stores
ELSE
SELECT * FROM Stores WHERE RepID = Host_Name()
Maybe your filters do this, and I'm just missing the point ?
Thanks for the time you're giving me :o)
-- Paul Ibison wrote: --
OK, have tested it and it works. Here is how I did it:
I had 2 tables: MainTable and LookupTable
MainTable
ID,Description
1,aaa
2,bbb
3,ccc
LookupTable
ID,Description,Host
1,aaa,Host1
2,bbb,Host1
3,ccc,Host999
My filter is LookupTable.Host = Host_Name()
The tables are joined on LookupTbale.ID = MainTable.ID
I set the -HOSTNAME parameter on the merge agent to be Host1 and only 2
records from each table are replicated. This successfully mimics what you
are doing with the subquery.
Any questions, please post back.
Paul Ibison
|||Cube,
such logic is part of the linking table. If you had 3 stores, then the
linking table looks like this:
LookupTable
StoreID,Host
1,6
2,6
3,6
For the other hostnames there are records representing the corresponding
links:
LookupTable
StoreID,Host
1,6
2,6
3,6
2,999
HTH,
Paul Ibison