Help on Tables

Is there a link that has good documentation on how to create tables?
Like I'm trying to put one value in a cell like:
=IIF( Fields!PERIOD.Value="Total", Fields!NET_SUBS.Value, Nothing)
but it will create additional rows, because there are multiple periods.If you do not use an aggregate you will get one report row per table row...
Will Sum, or AVG, etc do what you wish...
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Cindy Lee" <dangreece@.hotmail.com> wrote in message
news:%23rjJPmhXEHA.716@.TK2MSFTNGP11.phx.gbl...
> Is there a link that has good documentation on how to create tables?
> Like I'm trying to put one value in a cell like:
> =IIF( Fields!PERIOD.Value="Total", Fields!NET_SUBS.Value, Nothing)
>
> but it will create additional rows, because there are multiple periods.
>|||Yeah it still doesn't work:
=SUM(IIF( Fields!PERIOD.Value="Total", Fields!NET_SUBS.Value, Nothing))
=SUM(IIF( Fields!PERIOD.Value="Period1", Fields!NET_SUBS.Value, Nothing))
There are 3 periods: Period 1, Period 2 and Total that come back in the
query
The values are right but it repeats in my table
Period
Total Period 1
NETSUB 3000 1000
NETSUB 3000 1000
NETSUB 3000 1000
I just want it to appear 1 time.
"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
news:egp8gSqXEHA.3156@.TK2MSFTNGP12.phx.gbl...
> If you do not use an aggregate you will get one report row per table
row...
> Will Sum, or AVG, etc do what you wish...
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "Cindy Lee" <dangreece@.hotmail.com> wrote in message
> news:%23rjJPmhXEHA.716@.TK2MSFTNGP11.phx.gbl...
> > Is there a link that has good documentation on how to create tables?
> >
> > Like I'm trying to put one value in a cell like:
> > =IIF( Fields!PERIOD.Value="Total", Fields!NET_SUBS.Value, Nothing)
> >
> >
> >
> > but it will create additional rows, because there are multiple periods.
> >
> >
>|||From your description, it sounds like all of your fields are in the detail
row.
If this is correct, you can supress the duplicates by adding a details group
and set the group expression to use the period.
I have added a sample report to the end of this posting that demonstrates
how to use a table details group. To run it you will need access to the
Northwind sample database.
The table details group dialog can be reached from as follows: Table
Properties Dailog : Groups Tab : Details Grouping Button.
For a fuller discussion about table groups see the 'How to add a group to a
table' topic in Reporting Services Books Online.
--
Bruce Johnson [MSFT]
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Dave Clark" <daveclark@.hotmail.com> wrote in message
news:usF7VwsXEHA.3292@.TK2MSFTNGP09.phx.gbl...
> Yeah it still doesn't work:
> =SUM(IIF( Fields!PERIOD.Value="Total", Fields!NET_SUBS.Value, Nothing))
> =SUM(IIF( Fields!PERIOD.Value="Period1", Fields!NET_SUBS.Value, Nothing))
> There are 3 periods: Period 1, Period 2 and Total that come back in the
> query
> The values are right but it repeats in my table
> Period
> Total Period 1
> NETSUB 3000 1000
> NETSUB 3000 1000
> NETSUB 3000 1000
> I just want it to appear 1 time.
>
> "Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
> news:egp8gSqXEHA.3156@.TK2MSFTNGP12.phx.gbl...
> > If you do not use an aggregate you will get one report row per table
> row...
> > Will Sum, or AVG, etc do what you wish...
> >
> > --
> > Wayne Snyder, MCDBA, SQL Server MVP
> > Mariner, Charlotte, NC
> > www.mariner-usa.com
> > (Please respond only to the newsgroups.)
> >
> > I support the Professional Association of SQL Server (PASS) and it's
> > community of SQL Server professionals.
> > www.sqlpass.org
> >
> > "Cindy Lee" <dangreece@.hotmail.com> wrote in message
> > news:%23rjJPmhXEHA.716@.TK2MSFTNGP11.phx.gbl...
> > > Is there a link that has good documentation on how to create tables?
> > >
> > > Like I'm trying to put one value in a cell like:
> > > =IIF( Fields!PERIOD.Value="Total", Fields!NET_SUBS.Value, Nothing)
> > >
> > >
> > >
> > > but it will create additional rows, because there are multiple
periods.
> > >
Table Details Group Sample
========================================
<?xml version="1.0" encoding="utf-8"?>
<Report
xmlns="http://schemas.microsoft.com/sqlserver/reporting/2003/10/reportdefini
tion"
xmlns:rd="">http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
<RightMargin>1in</RightMargin>
<Body>
<ReportItems>
<Table Name="table1">
<Height>0.75in</Height>
<Style />
<Header>
<TableRows>
<TableRow>
<Height>0.25in</Height>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="textbox1">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<TextAlign>Right</TextAlign>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>8</ZIndex>
<rd:DefaultName>textbox1</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>Product ID</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox2">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<TextAlign>Right</TextAlign>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>7</ZIndex>
<rd:DefaultName>textbox2</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>Quantity</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox3">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>6</ZIndex>
<rd:DefaultName>textbox3</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
</TableRow>
</TableRows>
</Header>
<Details>
<TableRows>
<TableRow>
<Height>0.25in</Height>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="ProductID">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<TextAlign>Right</TextAlign>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>2</ZIndex>
<rd:DefaultName>ProductID</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=Fields!ProductID.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="Quantity">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<TextAlign>Right</TextAlign>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>1</ZIndex>
<rd:DefaultName>Quantity</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=Sum(Fields!Quantity.Value)</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox6">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<rd:DefaultName>textbox6</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
</TableRow>
</TableRows>
<Grouping Name="ProductID_DetailsGroup">
<GroupExpressions>
<GroupExpression>=Fields!ProductID.Value</GroupExpression>
</GroupExpressions>
</Grouping>
</Details>
<DataSetName>DataSet1</DataSetName>
<Footer>
<TableRows>
<TableRow>
<Height>0.25in</Height>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="textbox7">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>5</ZIndex>
<rd:DefaultName>textbox7</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox8">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>4</ZIndex>
<rd:DefaultName>textbox8</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox9">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>3</ZIndex>
<rd:DefaultName>textbox9</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
</TableRow>
</TableRows>
</Footer>
<TableColumns>
<TableColumn>
<Width>2.16667in</Width>
</TableColumn>
<TableColumn>
<Width>2.16667in</Width>
</TableColumn>
<TableColumn>
<Width>2.16667in</Width>
</TableColumn>
</TableColumns>
</Table>
</ReportItems>
<Style />
<Height>2in</Height>
</Body>
<TopMargin>1in</TopMargin>
<DataSources>
<DataSource Name="northwind">
<rd:DataSourceID>1a2ecefc-e377-49f4-a355-2c6133f21447</rd:DataSourceID>
<ConnectionProperties>
<DataProvider>SQL</DataProvider>
<ConnectString>data source=localhost;initial
catalog=northwind</ConnectString>
<IntegratedSecurity>true</IntegratedSecurity>
</ConnectionProperties>
</DataSource>
</DataSources>
<Width>6.5in</Width>
<DataSets>
<DataSet Name="Northwind">
<Fields>
<Field Name="OrderDate">
<DataField>OrderDate</DataField>
<rd:TypeName>System.DateTime</rd:TypeName>
</Field>
<Field Name="Quantity">
<DataField>Quantity</DataField>
<rd:TypeName>System.Int16</rd:TypeName>
</Field>
<Field Name="ProductID">
<DataField>ProductID</DataField>
<rd:TypeName>System.Int32</rd:TypeName>
</Field>
</Fields>
<Query>
<DataSourceName>northwind</DataSourceName>
<CommandText>SELECT Orders.OrderDate, [Order Details].Quantity,
[Order
Details].ProductID
FROM Orders INNER JOIN
[Order Details] ON Orders.OrderID = [Order
Details].OrderID
ORDER BY [Order Details].ProductID</CommandText>
</Query>
</DataSet>
</DataSets>
<LeftMargin>1in</LeftMargin>
<rd:SnapToGrid>true</rd:SnapToGrid>
<rd:DrawGrid>true</rd:DrawGrid>
<rd:ReportID>f28ffce0-dff1-4e06-ba21-c683aab528dc</rd:ReportID>
<BottomMargin>1in</BottomMargin>
<Language>en-US</Language>
</Report>

Help on Stored Procedure

CREATE PROCEDURE CreateMenuItems

@.in_cls_id int


AS
declare ....

DECLARE cursor_name CURSOR FOR
SELECT DISTINCT column1,column1,column3 ...
FROM viewUserPrivileges
WHERE ....
ORDER BY ....

OPEN cursor_name

FETCH NEXT FROM cursor_name
INTO declared local variables

while @.@.FETCH_STATUS <> -1
begin

if <condition>
begin
Print 'null'
insert into table...
Execute CreateMenuItems @.in_cls_id
end
else
insert into table...
Print 'Not null'
FETCH NEXT FROM cls_cursor
INTO declared local variables
End
close cursor_name
deallocate cursor_name

In the above procedure CreateMenuItems
calls itself. Is this possible? If not is there any other way to achieve the same?

Thanks in advance
P.C. VaidyanathanExecute CreateMenuItems @.in_cls_idIf it called itself, you would have an endless loop.

Not a good thing - I would not do it.

J.

Help on SQL syntax ??

Dear all,
I am using SQL 2000 with vs2003.
I need to create a store procedure which get the LAST record which has been
written in the database.
What is teh way to get that record ?
thnaks for your help
regards
sergeHi
How many table does the database have?
What is at the same time users insert data in many tables?
One option is create a new columns as datetime (DEFAULT GETDATE()) for
inserting .For updating you will have to create a trigger for update to
track the changes.
"serge calderara" <sergecalderara@.discussions.microsoft.com> wrote in
message news:78D429C3-3519-45FA-ADD8-C952D199D47D@.microsoft.com...
> Dear all,
> I am using SQL 2000 with vs2003.
> I need to create a store procedure which get the LAST record which has
> been
> written in the database.
> What is teh way to get that record ?
> thnaks for your help
> regards
> serge|||I only need to return a single row from a database table which already
contains a DateTime field.
I have tried to use the TOP keyword as follow in a strore procedure:
SELECT TOP ID, LEN, START_TIME FROM REELHIST ORDER BY START_TIME DESC
But it return a syntax error message when checking the syntax
Any idea ?
regards
serge
"Uri Dimant" wrote:

> Hi
> How many table does the database have?
> What is at the same time users insert data in many tables?
> One option is create a new columns as datetime (DEFAULT GETDATE()) for
> inserting .For updating you will have to create a trigger for update to
> track the changes.
>
> "serge calderara" <sergecalderara@.discussions.microsoft.com> wrote in
> message news:78D429C3-3519-45FA-ADD8-C952D199D47D@.microsoft.com...
>
>|||SELECT TOP 1 ID, LEN, START_TIME FROM REELHIST ORDER BY START_TIME DESC
"serge calderara" <sergecalderara@.discussions.microsoft.com> wrote in
message news:486F2059-0EE0-4CF6-8687-3C5F707EE77F@.microsoft.com...
>I only need to return a single row from a database table which already
> contains a DateTime field.
> I have tried to use the TOP keyword as follow in a strore procedure:
> SELECT TOP ID, LEN, START_TIME FROM REELHIST ORDER BY START_TIME DESC
> But it return a syntax error message when checking the syntax
> Any idea ?
> regards
> serge
> "Uri Dimant" wrote:
>|||thnaks,
so simple sometimes :-)
"Uri Dimant" wrote:

> SELECT TOP 1 ID, LEN, START_TIME FROM REELHIST ORDER BY START_TIME DESC
> "serge calderara" <sergecalderara@.discussions.microsoft.com> wrote in
> message news:486F2059-0EE0-4CF6-8687-3C5F707EE77F@.microsoft.com...
>
>

Help on replication from SQL 2005 to DB2/AS400

I was trying to set up a replication process to move data from SQL 2005 to DB2/AS400.

To get it started, I have create a simplest table (say, PERSON) on SQL 2005 server, and the same table (PERSON) on DB2/AS400. I have only one column SSN (of type int). I have journaled the table on DB2 end. I was able to have successfully configured publisher (on SQL 2005) and subscriber (i.e. a pushed subscription). I used transactional replication. And I also installed HIS 2004 to have DB2OLEDB ready. I was able to use query/insert/delete against table on DB2 based the linked server (that I created for testing purpose).

I did a lot of reading on this and my impression is that replicating to DB2/AS400 is supported from SQL 2005. But I always have this error from replication monitor. Can anyone enlighten me what was wrong.

Command attempted:

CREATE TABLE MY_DB.PERSON(
SSN
INT NOT NULL)

(Transaction sequence number: 0x00000020000000F9000100000004, Command ID: 4)

Error messages:

A SQL error has occurred. Please consult the documentation for your specific DB2 version for a description of the associated Native Error and SQL State. SQLSTATE: 42710, SQLCODE: -601 (Source: MSSQL_REPL_DB2, Error number: -601)
Get help: http://help/-601

A SQL error has occurred. Please consult the documentation for your specific DB2 version for a description of the associated Native Error and SQL State. SQLSTATE: 42710, SQLCODE: -601 (Source: MSSQL_REPL_DB2, Error number: -601)
Get help: http://help/-601

I assume you've read Books Online topic IBM DB2 Subscribers already? If so, try this KB article: http://support.microsoft.com/default.aspx?scid=kb;%5BLN%5D;Q239458

|||

Hi Steve,

Couple questions?

Did you setup the replication through user interface or replication stored procedures?

It seems like that you are trying to publisher data from SQL server to an "existing" DB2 table, is this a safe assumption to make? If this is what you are attempting, UI does not support this scenario, you will have to set it up through the stored procedures.

Let me know if you need more help.

Gary

|||

Hi Gary and Greg:

1). To answer Gary's questions -

Yes. i setup replication through user interface (i.e. SQL 2005 management studio). Your assumption is correct. I was trying to replicate data from SQL 2005 to DB2/AS400.

I have always used UI to set up the replication process (like SQL to SQL). Unfortunately I have rarely used stored procedure to setup replication although i know it is doable. In another word, i do not have any hands-on experience yet to set up replication from stored procedures. Can you post some samples here or can you enlighten me in a different way please?

2) Greg - i have installed SQL 2005 SP1 on my machine. I wonder if i need to download the special fix that the article refers to.

I have read all of Books Online topics of IBM DB2 Subscribers. It is a kind of generic guidance but not specific enough to help solve my issue. Plus, some of description is a bit vague.

Thank you very much for your attention anyway. Please help me more if you can. I do appreciate it.

Steve

Thanks in advance.

|||

Hi Steve,

Here ia a sample script for setuping replication for DB2 subscriber without dropping and re-creating the table on DB2 subscriber side.

Please note:

For sp_addpublication, the following have to be set
@.sync_method=N'character',
@.enabled_for_het_sub='true'

For sp_addarticle, the following have to be set
@.creation_script=null,
@.pre_creation_cmd=N'none',
@.schema_option=0x00,
@.ins_cmd=N'SQL',
@.del_cmd=N'SQL',
@.upd_cmd=N'SQL',

For sp_addsubscription , the following have to be set
@.update_mode=N'read only',
@.subscriber_type=3

The above list is the ones I think are the more critical ones to get them right (hopefully I didn't miss anything). The sample will require some modification to your environment such as server name, user account, password and DB2 connection info.

After running the following script, if you chose to run the snapshot, logread, and distribution agents manually, I have included the command line statement at the bottom of the script.

And don't forget to create a table schema on the DB2 side first.

Let me know if this solve your problem.

Cheers,

Gary

/* This posting is provided as is. */

Sample Script

USE master
EXEC sp_adddistributor 'myservername'
go

EXEC sp_adddistributiondb 'distribution', @.data_folder='C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA', @.data_file='ddata.dat', @.data_file_size=32, @.log_folder='c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA', @.log_file='dlog.dat', @.log_file_size=16, @.security_mode=0, @.login='sa', @.password='******'
go

EXEC sp_adddistpublisher @.publisher='myservername', @.distribution_db='distribution', @.security_mode=0, @.login='sa', @.password='******', @.working_directory='\\myservername\MSSQL\REPLDATA'
go

create database [pubdb]
go

pubdb..sp_replicationdboption 'pubdb', 'publish', 'true'
go

-- This table is used to store the DB2 connection string, it can save you to time to copy/paste the string.
create table [_provider] ([string] varchar(1000))
go
-- Please replace with your DB2 connection string
insert into [_provider] ([string]) values ('Provider=DB2OLEDB;........')
go

use pubdb
go
-- Please replace with a valid user account
exec sp_addlogreader_agent @.job_login='MYDOMAIN\USERID' , @.job_password='******' , @.publisher_security_mode=1
go
-- Create a sample table on SQL server, assuming a same table called [MYTEST] already exist on DB2
create table [MYTEST] ([PKCOL] tinyint primary key)
insert into [MYTEST] ([PKCOL]) values (77)
go

exec sp_addpublication
@.publication=N'DTYPETEST',
@.sync_method=N'character',
@.retention=0,
@.allow_push=N'true',
@.allow_anonymous=N'false',
@.autogen_sync_procs='false',
@.enabled_for_internet=N'false',
@.snapshot_in_defaultfolder=N'true',
@.compress_snapshot=N'false',
@.allow_subscription_copy=N'false',
@.add_to_active_directory=N'false',
@.repl_freq=N'continuous',
@.status=N'active',
@.independent_agent=N'true',
@.immediate_sync=N'true',
@.allow_sync_tran=N'false',
@.allow_queued_tran=N'false',
@.allow_dts=N'false',
@.replicate_ddl=0,
@.allow_initialize_from_backup=N'false',
@.enabled_for_het_sub='true'
go

exec sp_addarticle
@.publication=N'DTYPETEST',
@.article=N'MYTEST',
@.source_owner=N'dbo',
@.source_object=N'MYTEST',
@.type=N'logbased',
@.description=null,
@.creation_script=null,
@.pre_creation_cmd=N'none',
@.schema_option=0x00,
@.identityrangemanagementoption=N'none',
@.destination_table=N'MYTEST',
@.destination_owner=null,
@.vertical_partition=N'false',
@.ins_cmd=N'SQL',
@.del_cmd=N'SQL',
@.upd_cmd=N'SQL',
@.force_invalidate_snapshot=1,
@.fire_triggers_on_snapshot='false'
go

exec sp_addsubscription
@.publication=N'DTYPETEST',
@.subscriber=N'DB2NTOLEDB',
@.destination_db=N'(default destination)',
@.subscription_type=N'Push',
@.frequency_type=1,
@.frequency_interval=0,
@.frequency_relative_interval=0,
@.frequency_recurrence_factor=0,
@.frequency_subday=0,
@.frequency_subday_interval=0,
@.active_start_time_of_day=0,
@.active_end_time_of_day=0,
@.active_start_date=0,
@.active_end_date=20070101,
@.enabled_for_syncmgr=N'False',
@.offloadagent=0,
@.sync_type=N'Automatic',
@.article=N'all',
@.update_mode=N'read only',
@.dts_package_location=N'Distributor',
@.subscriber_type=3
go

declare @.provstr varchar(1000)
select @.provstr=[string] from [master]..[_provider]
exec sp_addpushsubscription_agent
@.publication='DTYPETEST',
@.subscriber='DB2NTOLEDB',
@.subscriber_db='(default destination)',
@.subscriber_security_mode=0,
@.subscriber_login='Your DB2 User ID',
@.subscriber_password='Your DB2 Password',
@.job_login='Windows account or SQL server account',
@.job_password='******' ,
@.subscriber_provider_string=@.provstr,
@.subscriber_provider='DB2OLEDB',
@.subscriber_catalog='DB2 catalog'

--"c:\Program Files\Microsoft SQL Server\90\COM\SNAPSHOT.EXE" -Publisher MYSERVER -PublisherDB pubdb -Publication DTYPETEST -Distributor MYSERVER -OutputVerboseLevel 1 -DistributorLogin sa -DistributorPassword password

--"c:\Program Files\Microsoft SQL Server\90\COM\LOGREAD.EXE" -Publisher MYSERVER -PublisherDB pubdb -Distributor MYSERVER -OutputVerboseLevel 1 -DistributorLogin sa -DistributorPassword password

--"c:\Program Files\Microsoft SQL Server\90\COM\DISTRIB.EXE" -Subscriber DB2NTOLEDB -Publisher MYSERVER -PublisherDB pubdb -Distributor MYSERVER -Publication DTYPETEST -SubscriberType 3 -OutputVerboseLevel 2 -DistributorLogin sa -DistributorPassword password
THE END -

|||

Wow, a super nice reply and posting. Thanks much Gary. Seems you are quite knowledgeable in using replication to DB2.

I will give it a shot today to see how it works.

- Steve

Incidently I wonder why Microsoft does not elaborate this on Books Online in detailed.

|||

Hi Gary -

Thank you for the very detailed info and sample scripts. That is very helpful in getting me started to use script to create replication/subscription.

I have tried over and over, and unfortunately i was not able to get it to work. Shawn told me that he talked to you and you two are going to help me out of this trouble.

Thanks a lot.

Steve

|||

What is the problem you are encountering? Can you provide more detail? At what point does it fail? Maybe the schema of your table may help.

Thanks,

Gary

|||

Hi Gary -

I worked on this with a couple of your Microsoft engineers last Friday. Finally I got it to work using scripts. But there are many limitations for replication from SQL server 2005 to DB2 as compared with the replication from SQL 2005 to SQL 2005. The following are the main limitations i found:

1) The replication can NOT replicate schema change from SQL server to DB2. It makes replication to fail.

2) type problems – when replicating data from a type of either nchar(10) or nvarchar(10) to BD2's GRAPHIC(10). The replication ALWAYS FAILED.

QUESTIONS:

Currently I tested DB2OLEDB using connection string like “……Units of Work=RUW; … “, I wonder how replication supports data consistence and reliability. In case of replication failure, how does the rollback work? Without using distributed transcation enabled (like above), how the replication support guaranteed data delivery ?

- Steve

|||

My SQL side table scema:

CREATE TABLE [dbo].[MYTEST](

[PKCOL] [tinyint] NOT NULL,

[NEW_COL1] [nchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL CONSTRAINT [DF_MYTEST_NEW_COL] DEFAULT (N'nchar'),

[NEW_COL2] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL CONSTRAINT [DF_MYTEST_NEW_COL2] DEFAULT (N'nvarchar'),

CONSTRAINT [PK__MYTEST__7C8480AE] PRIMARY KEY CLUSTERED

(

[PKCOL] ASC

)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]

) ON [PRIMARY]

The DB2's table scema is:

CREATE TABLE PUBDB.MYTEST (

PKCOL INTEGER DEFAULT NULL ,

NEW_COL1 GRAPHIC(10) CCSID 835 DEFAULT NULL ,

NEW_COL2 GRAPHIC(10) CCSID 835 DEFAULT NULL ,

CONSTRAINT PUBDB.Q_PUBDB_MYTEST_PKCOL_00001 PRIMARY KEY( PKCOL ) ) ;

|||

1) SQL server does not support DDL (schema change) for DB2 subscriber.

2) Replication failed from nchar(10) or nvarchar(10) to DB2's GRAPHIC(10) is because of the default datatype mapping from SQL to DB2. By default, both nchar(10) and nvarchar(10) are mapped to VARCHAR on DB2 side. I believe this can be changed. Please check out the following article,

ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/repref9/html/bfd2e111-cada-4304-80a8-c1e1061573b5.htm

When you said rollback, do you mean rollback on SQL publisher? Or DB2 subscriber?

Cheers,

Gary

The posting is provided as is

|||

1) SQL server does not support DDL (schema change) for DB2 subscriber.

IT IS REALLY TRUE. I HAVE TRIED THIS AND IT DID EXACTLY THE SAME AS WHAT YOU SAID HERE. ONE OF YOUR MICROSOFT ENGINEER ALSO PROVED THIS AS WELL.

2) As of datatype mapping, i have tried to modify the following file:

C:\Program Files\Microsoft SQL Server\90\DTS\MappingFiles\SqlClientToDB2.xml

and

C:\Program Files\Microsoft SQL Server\90\DTS\MappingFiles\SqlClientToIBMDB2.xml

Can you confirm if these files are the ones that need to customize for datatype mapping?

3) Regarding rollback-- while in the process of replication of a record or a batch of several records, network fails (i.,e. broken), does publisher roll back those records that have been propergated to the subscriber? How does publisher know which record fails so that it knows where to start next time when network is restored.

Thanks.

Help on query

I have two tables Income(Date, IncomeType, Amount) and Expense(Date, ExpenseType, Amount). I want to create a balance sheet report for which need a query or view which creates a virtual table of form BalanceSheet(IncomeType, IncomeAmount, ExpenseType, ExpenseAmount). The table should look like:

Salary 100000 Rent a Car 5000
(null) (null) Tution 1000
...................................... ...
...................................... ...

How can it be done?

Appreciate for the help.

Rajuselect income, amount, '','' from incomes
union
select '','',expense, amount from expenses

Help on Migrating from 2000 to 2005- SSIS package

Hi,

I have to create a migration package ..means package should migrate the sql server 2000 tables to 2005 tables (Not dealing with data at this point of time and ignoring SPs,DTS packages).But there are lot of normalisation ans schema changes in 2005 compared to 2000.Like,

- One 2000 table devided into 3-4 tables in 2005
- Lot of changes in the filed names
- Handling integrity relationship between the newversion tables

Being new to SSIS ,iam in confusion like how to start and where to start.can you pls tell me the steps(Structured way) i have to fallow

-- I have around 8-9 tables in 2000 ,I have to migrate them into 18-19 tables (with some new fileds )

-- For each table i have to create one package(bcoz lot of transformations are there) or I can create one package for all of those ? but the finally i have to handover one package to the client

pls ask me if u need any further info to come up with the explanation..bcoz iam not sure whether i provided enough info or not

Thanks for ur help
Niru

You should be able to build just one package. You might need as many data-flows as there are destination tables. it depends on the situation.

Use precedence constraints to ensure that the data-flows are executed in the correct order according to RI declared on the destination tables.

If you ask more specific questions then more specific answers can be provided.

-Jamie

|||

Thanks Jamie for the clarifying ..yes i think i should create different dataflows instead of different packages .

Here is the scenario which iam working now

I have a table...i have to map some of those fields to the existing 5 tables.

fileds should map like this

source table Destination fileds with tables
(contract)

contract_code --> contract_code (Incident)

Date --> DateId (Dates)
TimeId (Times)

Duration --> Duration (Session)

Ended --> Completion_state(Session) will have options like 'yes','no','unknown'
Problem(session) -- need to set 'true' if completion_state is 'No'

From --> Number(Audit) -- Adding source if it is not existed
Id (Audit) -- giving id if you added target in the above column

To --> Number(Audit) -- Adding Traget if it is not existed
Id (Audit) -- giving id if you added target in the above column

[I will differentiate the difference betweeen 'from' and 'TO' columns in session tables seperatley by refernce]

I think you got some idea what iam trying to do ..like this i have 9-10 tables .

I apperciate if can help me out in this .

Thanks

Niru

Help on Dates

I'm new to SQL server...
I'm trying to create a date (only) with no time.
I'm importing the data from navchar to datetime and getting the time stamp. How do I configure the table to show only the date part?
I did not see a date datatype...
ThanksThat creature does not exist. You will have to use the convert function using a style - look at sql server books online under 'Cast and Convert'.
The time value stored when no time is provided is midnight - so you would have a date followed by 00:00:00.000. You might also consider smalldatetime.

For example, using northwind's orders table:

select convert(varchar(10), orderdate,101) from orders|||If my date field is indexed (2-3 million rows), then will the request read all records before converting and selecting?|||/*
--replace "origDateTime" with our datetime value/column

_ You can use datetime column with check constraint
( "origDateTime"=convert(datetime,convert(int,"origDateTime")) )
to ensure that there is no time saved, in the future you could need
time, so you only remove this constraint. Linking to date analysis
table will be done by indexed computed column on table level
( "compOnlyDate" AS convert(int,"origDateTime") )
to get additional precomputed information about date
(year,month,day in week,season,...) faster.
_ If you NEVER use time, consider using only int
or smallint ( convert(int,getdate())-30000 ),
but be prepared on problems with user reports and applications.
*/

Help on creating letter in RS

Hi,
I am trying to create report using Reporting Services 2000. My dataset returns 2 records. I want to generate two reports (one for each record) in a letter format. Can anybody tell me how do I design the report that to generate seperate letter for each record? Thanks.

Am I correct in assuming that you want a seperate page for each record?

Help on Calculated Members

Hi Gurus,

I am new to Microsoft Analysis Services. I wanted to create a new Average Calculation.

The Syntax is

Avg(?Set[, ?Numeric Expression]). I am using the Foodmart cube.

I have tried using many things but have not achieved any success.

These are the following things that I have tried.

1. Avg([Product].[Product Family].&[Drink].&[Dairy].&[Dairy].&[Milk] , 'Avergare of Milk')

In this 'Avergare of Milk' is some sort of Calculation name I suppose.

2. Avg([Product Family].&[Drink].&[Dairy].&[Dairy].&[Milk])

In this ‘6’ I have mentioned assuming there are only 6 members in the hierarchy.

Please tell me What shall I write in place of Set and Numeric Expression.

I also came across an article and found this

“If you want to create an average, what you need to do is create one
measure
called SUM, and another measure based on COUNT and then create a
calculated
member called AVG which would just be SUM/COUNT. I believe there are
examples of this in the Foodmart 2000 cubes.”

But to my Surprise I also found the Syntax of SUM and Count to be similar.

Please help me out in this.

I also have a request Could anyone give me some examples and any link from where I could learn all the available calculation with examples.

Thanks in advance

Here is an example that should help you:

with member [Measures].[Av1] as
'Avg({descendants([Product].[All Products].[Drink],[Product Name])}, [Measures].[Profit])'
member [Measures].[Av2] as 'Avg({descendants([Product].[All Products].[Drink],[Product Name])}, coalesceempty([Measures].[Profit],0))'
member [Measures].[Num] as 'count({descendants([Product].[All Products].[Drink],[Product Name])})'
member [Measures].[Sum] as 'sum({descendants([Product].[All Products].[Drink],[Product Name])}, [Measures].[Profit])'
member [Measures].[Av3] as '[Measures].[Sum]/[Measures].[Num]'

select

{[Measures].[Av1],[Measures].[Av2],[Measures].[Av3],[Measures].[Num],[Measures].[Sum]} on columns,

{[Store].[Store Name].members} on rows
from sales

As you can see, you Avg1 is different from Av2 because it doesn't consider empty cells, so the average is slightly bigger.

Hope this help,

Santiago

|||

Hi,

Thanks for the help. But I think I was not clear what I wanted. I am using Analysis manager (Microsoft SQL Server -> Analysis Services -> Analysis Manger).

In that I am drilling down the Analysis Servers to list the Available servers and then drilling down the database present to get the list of the available cubes.Now I am right clicking on a cube and choosing edit to open the Cube Editor.In Cube editor I am trying to create a new Calculated Members. (In the Calculated member Builder I am unable to write the query for the Average.) There I am not able to make the calculation. And am unable to sort out the problem.

I hope this information help in giving my problem the perfect picture.

Please let me know if u need anything else

Thanks in advance

|||

In Calculated Member builder, select Measures as the Parent Dimension, write the name of the measure (eg. MyAverage) and type the appropriate mdx expression. For example:

Avg({descendants([Product].[All Products].[Drink],[Product Name])}, [Measures].[Profit])

You don′t need then to reprocess the cube. Just browse it and you should be able to see the new Calculated Member.

|||

HI,

Many many thanks for the help. It worked.

Can I get some link from where I can get some examples of the calculation.

Thanks

Help on a query

Hi,
Iam trying to figure out the query to achieve the output depicted below

create table master (
iss_dtl_seq_nbr int
)

create table child1(
iss_dtl_seq_nbr int,
line_no int
)

create table child2(
iss_dtl_seq_nbr int,
line_no int
)

insert into master
select 1

insert into child1
select 1, 1
insert into child1
select 1, 2

insert into child2
select 1, 1

insert into child2
select 1, 2

insert into child2
select 1, 3

SELECT MASTER.ISS_DTL_SEQ_NBR,CHILD1.LINE_NO, CHILD2.LINE_NO
FROM CHILD1, CHILD2, MASTER
WHERE MASTER.ISS_DTL_SEQ_NBR = CHILD1.ISS_DTL_SEQ_NBR
AND MASTER.ISS_DTL_SEQ_NBR = CHILD2.ISS_DTL_SEQ_NBR
AND CHILD1.LINE_NO = CHILD2.LINE_NO
ORDER BY CHILD1.LINE_NO, CHILD2.LINE_NO

Expected Output:

ISS_DTL_SEQ_NBR LINE_NO LINE_NO
----- ---- ----
1 1 1
1 2 2
1 NULL 3

Can anybody help with the query to achieve this?

Thanks.On 9 Sep 2004 09:33:21 -0700, Sudhir wrote:

>Hi,
> Iam trying to figure out the query to achieve the output depicted below
>create table master (
>iss_dtl_seq_nbr int
>)
>create table child1(
>iss_dtl_seq_nbr int,
>line_no int
>)
>create table child2(
>iss_dtl_seq_nbr int,
>line_no int
>)
>insert into master
>select 1
>insert into child1
>select 1, 1
>insert into child1
>select 1, 2
>insert into child2
>select 1, 1
>insert into child2
>select 1, 2
>insert into child2
>select 1, 3
>
>SELECT MASTER.ISS_DTL_SEQ_NBR,CHILD1.LINE_NO, CHILD2.LINE_NO
>FROM CHILD1, CHILD2, MASTER
>WHERE MASTER.ISS_DTL_SEQ_NBR = CHILD1.ISS_DTL_SEQ_NBR
>AND MASTER.ISS_DTL_SEQ_NBR = CHILD2.ISS_DTL_SEQ_NBR
>AND CHILD1.LINE_NO = CHILD2.LINE_NO
>ORDER BY CHILD1.LINE_NO, CHILD2.LINE_NO
>Expected Output:
>ISS_DTL_SEQ_NBR LINE_NO LINE_NO
>----- ---- ----
>1 1 1
>1 2 2
>1 NULL 3
>Can anybody help with the query to achieve this?
>Thanks.

Hi Sudhir,

Thanks for providing the statements to recreate your table structure and
data. The following query will produce the expected output:

select coalesce(master.iss_dtl_seq_nbr, child2.iss_dtl_seq_nbr) AS
iss_dtl_seq_nbr,
child1.line_no, child2.line_no
from master
inner join child1
on master.iss_dtl_seq_nbr = child1.iss_dtl_seq_nbr
right outer join child2
on child1.iss_dtl_seq_nbr = child2.iss_dtl_seq_nbr
and child1.line_no = child2.line_no
order by child2.line_no, child1.line_no

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||Try this:

select master.iss_dtl_seq_nbr, child1.line_no, child2.line_no
from child1 full outer join child2 on child1.iss_dtl_seq_nbr =
child2.iss_dtl_seq_nbr and child1.line_no = child2.line_no
join master on master.iss_dtl_seq_nbr =
isnull(child1.iss_dtl_seq_nbr, child2.iss_dtl_seq_nbr)

Shervin

s.sudhir@.gmail.com (Sudhir) wrote in message news:<80a5355b.0409090833.4ccd6d2b@.posting.google.com>...
> Hi,
> Iam trying to figure out the query to achieve the output depicted below
> create table master (
> iss_dtl_seq_nbr int
> )
> create table child1(
> iss_dtl_seq_nbr int,
> line_no int
> )
> create table child2(
> iss_dtl_seq_nbr int,
> line_no int
> )
> insert into master
> select 1
> insert into child1
> select 1, 1
> insert into child1
> select 1, 2
> insert into child2
> select 1, 1
> insert into child2
> select 1, 2
> insert into child2
> select 1, 3
>
> SELECT MASTER.ISS_DTL_SEQ_NBR,CHILD1.LINE_NO, CHILD2.LINE_NO
> FROM CHILD1, CHILD2, MASTER
> WHERE MASTER.ISS_DTL_SEQ_NBR = CHILD1.ISS_DTL_SEQ_NBR
> AND MASTER.ISS_DTL_SEQ_NBR = CHILD2.ISS_DTL_SEQ_NBR
> AND CHILD1.LINE_NO = CHILD2.LINE_NO
> ORDER BY CHILD1.LINE_NO, CHILD2.LINE_NO
> Expected Output:
> ISS_DTL_SEQ_NBR LINE_NO LINE_NO
> ----- ---- ----
> 1 1 1
> 1 2 2
> 1 NULL 3
> Can anybody help with the query to achieve this?
> Thanks.

Help needed: Granting Create table permisions on specific Schema Options

Having some trouble getting my head around setting access to specific
schemas- here's my problem:

I've created a specific schema that I only want certain users to
control

Problem: Even though I give them full access...the cannot create
tables under that schema...my code is below (flyer is the schema,
eflyerAdmin is the role, and eflyer is the user):

GRANT
ALTER,
CONTROL,
DELETE,
EXECUTE,
INSERT,
REFERENCES,
SELECT,
TAKE OWNERSHIP,
UPDATE,
VIEW DEFINITION
ON SCHEMA::flyer
TO eflyerAdmin
GO
-- Add an existing user to the role
EXEC sp_addrolemember N'eflyerAdmin', N'eflyer'gdev (paul.afamdi.okeke@.gmail.com) writes:

Quote:

Originally Posted by

Having some trouble getting my head around setting access to specific
schemas- here's my problem:
>
I've created a specific schema that I only want certain users to
control
>
>
Problem: Even though I give them full access...the cannot create
tables under that schema...my code is below (flyer is the schema,
eflyerAdmin is the role, and eflyer is the user):
>
GRANT
ALTER,
CONTROL,
DELETE,
EXECUTE,
INSERT,
REFERENCES,
SELECT,
TAKE OWNERSHIP,
UPDATE,
VIEW DEFINITION
ON SCHEMA::flyer
TO eflyerAdmin
GO
-- Add an existing user to the role
EXEC sp_addrolemember N'eflyerAdmin', N'eflyer'

You also need:

GRANT CREATE TABLE TO eflyerAdmin

And it's sufficient to grant CONTROL on the schema, since CONTROL implies
the rest.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Help Needed: DataSet

Should I create a new dataset in order to populate a multi select parameter
drop down box for users?
I am trying to capture all Companies stored in our backend database as a
drop down selection to users.
Thanks!Hi Terry,
You are correct. Thats how the guy explained it on the Reporting Services
webcasts.
The webcasts are quite informative. I would advise everyone to watch them.
R
S
--
http://dotnet.org.za/stanley
"Terry" wrote:
> Should I create a new dataset in order to populate a multi select parameter
> drop down box for users?
> I am trying to capture all Companies stored in our backend database as a
> drop down selection to users.
> Thanks!|||Which webcast are you referring to?
Provide URL or link, if available?
Again, thanks!
"Stan" wrote:
> Hi Terry,
> You are correct. Thats how the guy explained it on the Reporting Services
> webcasts.
> The webcasts are quite informative. I would advise everyone to watch them.
> R
> S
> --
> http://dotnet.org.za/stanley
>
> "Terry" wrote:
> > Should I create a new dataset in order to populate a multi select parameter
> > drop down box for users?
> >
> > I am trying to capture all Companies stored in our backend database as a
> > drop down selection to users.
> >
> > Thanks!|||I am getting an error when I create a new dataset and use it as a parameter.
Error:
[rsMissingDataSetName] The data set name is missing in the data region
â'DataSetNameâ'
CompanyLookup Dataset:
select DISTINCT ADDR_Org_Name_1, ADDR_Org_Name_2
from addresses
where len(rtrim(ADDR_Org_Name_1)) > 0
order by ADDR_Org_Name_1
"Stan" wrote:
> Hi Terry,
> You are correct. Thats how the guy explained it on the Reporting Services
> webcasts.
> The webcasts are quite informative. I would advise everyone to watch them.
> R
> S
> --
> http://dotnet.org.za/stanley
>
> "Terry" wrote:
> > Should I create a new dataset in order to populate a multi select parameter
> > drop down box for users?
> >
> > I am trying to capture all Companies stored in our backend database as a
> > drop down selection to users.
> >
> > Thanks!|||The best thing to do is to go through the webcasts and see what you like.
But the ones I'm reffering to is a 5 day series. They touch on everything.
Follow the URL and scroll down till you see the webcast that starts on 07
March part 1
Very long URL :-)
http://www.microsoft.com/events/AdvSearch.mspx?EventsAndWebcastsControlName=As1%3AAdvSrc&As1%3AAdvSrc%3AAudienceID=0&As1%3AAdvSrc%3AProductID=261ba873-f3ab-420e-96d6-e3004596a551&As1%3AAdvSrc%3AEventType=OnDemandWebcast&As1%3AAdvSrc%3ACountryRegionID=en%7CUS%7CUnited+States&StateProvinceID=0&As1%3AAdvSrc%3ATimeframeID=-1&As1%3AAdvSrc%3ASearchFilter=%C2%A0+Go+%C2%A0&PageNumber=3
Hope this helps man...
R
S
--
http://dotnet.org.za/stanley
"Terry" wrote:
> Which webcast are you referring to?
> Provide URL or link, if available?
> Again, thanks!
> "Stan" wrote:
> > Hi Terry,
> > You are correct. Thats how the guy explained it on the Reporting Services
> > webcasts.
> > The webcasts are quite informative. I would advise everyone to watch them.
> >
> > R
> > S
> > --
> > http://dotnet.org.za/stanley
> >
> >
> > "Terry" wrote:
> >
> > > Should I create a new dataset in order to populate a multi select parameter
> > > drop down box for users?
> > >
> > > I am trying to capture all Companies stored in our backend database as a
> > > drop down selection to users.
> > >
> > > Thanks!|||Thank you for your assistance.
"Stan" wrote:
> The best thing to do is to go through the webcasts and see what you like.
> But the ones I'm reffering to is a 5 day series. They touch on everything.
> Follow the URL and scroll down till you see the webcast that starts on 07
> March part 1
> Very long URL :-)
> http://www.microsoft.com/events/AdvSearch.mspx?EventsAndWebcastsControlName=As1%3AAdvSrc&As1%3AAdvSrc%3AAudienceID=0&As1%3AAdvSrc%3AProductID=261ba873-f3ab-420e-96d6-e3004596a551&As1%3AAdvSrc%3AEventType=OnDemandWebcast&As1%3AAdvSrc%3ACountryRegionID=en%7CUS%7CUnited+States&StateProvinceID=0&As1%3AAdvSrc%3ATimeframeID=-1&As1%3AAdvSrc%3ASearchFilter=%C2%A0+Go+%C2%A0&PageNumber=3
> Hope this helps man...
> R
> S
> --
> http://dotnet.org.za/stanley
>
> "Terry" wrote:
> > Which webcast are you referring to?
> >
> > Provide URL or link, if available?
> >
> > Again, thanks!
> >
> > "Stan" wrote:
> >
> > > Hi Terry,
> > > You are correct. Thats how the guy explained it on the Reporting Services
> > > webcasts.
> > > The webcasts are quite informative. I would advise everyone to watch them.
> > >
> > > R
> > > S
> > > --
> > > http://dotnet.org.za/stanley
> > >
> > >
> > > "Terry" wrote:
> > >
> > > > Should I create a new dataset in order to populate a multi select parameter
> > > > drop down box for users?
> > > >
> > > > I am trying to capture all Companies stored in our backend database as a
> > > > drop down selection to users.
> > > >
> > > > Thanks!|||I hope I understand you correctly.
Just to clear things out.
You have a report that takes parameters, but you want the user to select the
parameter from a drop down box. Is this correct?
If that is the case, go to you Data Tab and click on new dataset. Write
your query that will bring back the company names. Now go to your layout
view and click on the report menu. Go to report parameters. Now select on
your company parameter and then click the radio button that says from query.
Select your new dataset in there. When you preview your report the dropdown
box should be populated.
Hope this helps man...
--
http://dotnet.org.za/stanley
"Terry" wrote:
> I am getting an error when I create a new dataset and use it as a parameter.
> Error:
> [rsMissingDataSetName] The data set name is missing in the data region
> â'DataSetNameâ'
> CompanyLookup Dataset:
> select DISTINCT ADDR_Org_Name_1, ADDR_Org_Name_2
> from addresses
> where len(rtrim(ADDR_Org_Name_1)) > 0
> order by ADDR_Org_Name_1
>
> "Stan" wrote:
> > Hi Terry,
> > You are correct. Thats how the guy explained it on the Reporting Services
> > webcasts.
> > The webcasts are quite informative. I would advise everyone to watch them.
> >
> > R
> > S
> > --
> > http://dotnet.org.za/stanley
> >
> >
> > "Terry" wrote:
> >
> > > Should I create a new dataset in order to populate a multi select parameter
> > > drop down box for users?
> > >
> > > I am trying to capture all Companies stored in our backend database as a
> > > drop down selection to users.
> > >
> > > Thanks!

Help needed on reprting services

Hi, Can anyone help me to create a report with all drill down features
to be expanded at the time of display itself( without clicking the
expand button)
Thank youMake all the texbox visible ofcourse with toggle items and check in the
texbox->properties->visibility last option
ie "initial Appearence" select "expanded (-)"
Amarnath
"georgerijo@.gmail.com" wrote:
> Hi, Can anyone help me to create a report with all drill down features
> to be expanded at the time of display itself( without clicking the
> expand button)
> Thank you
>

Help needed on DDL Triggers in SQL server 2005

Hello,
I am trying to create DDL trigger as below
ALTER TRIGGER DDLTRIGGER ON DATABASE
FOR DDL_DATABASE_LEVEL_EVENTS
AS
DECLARE @.EventData XML
SET @.EventData = EVENTDATA()
INSERT DDLEVENTLOG
(EVENTTYPE
/*POSTTIME,
SPID,
SERVERNAME,
LOGINNAME,
DATABASENAME,
SCHEMANAME,
OBJECTNAME,
OBJECTTYPE,
TARGETOBJNAME,
TARGETOBJTYPE,
TSQLSTATEMENT,
FULLDATA*/
)
VALUES
(
CONVERT (NVARCHAR(100),@.EventData.Query('DATA(//EventType)'))
/*CONVERT (NVARCHAR(100),@.EVENTDATA.QUERY('DATA(//PostTime)')),
CONVERT (NVARCHAR(100),@.EVENTDATA.QUERY('DATA(//SPID)')),
CONVERT (NVARCHAR(100),@.EVENTDATA.QUERY('DATA(//SERVERNAME)')),
CONVERT (NVARCHAR(100),@.EVENTDATA.QUERY('DATA(//LOGINNAME)')),
CONVERT (NVARCHAR(100),@.EVENTDATA.QUERY('DATA(//DATABASENAME)')),
CONVERT (NVARCHAR(100),@.EVENTDATA.QUERY('DATA(//SCHEMANAME)')),
CONVERT (NVARCHAR(100),@.EVENTDATA.QUERY('DATA(//OBJECTNAME)')),
CONVERT (NVARCHAR(100),@.EVENTDATA.QUERY('DATA(//OBJECTTYPE)')),
CONVERT (NVARCHAR(100),@.EVENTDATA.QUERY('DATA(//TARGETOBJNAME)')),
CONVERT (NVARCHAR(100),@.EVENTDATA.QUERY('DATA(//TARGETOBJTYPE)')),
CONVERT (NVARCHAR(100),@.EVENTDATA.QUERY('DATA(//COMMANDTEXT)')),
@.EventData*/
)
But when i execute this its throwing an error...
Msg 227, Level 15, State 1, Procedure DDLTRIGGER, Line 6
"Query" is not a valid function, property, or field.
Am i doing some thing here?
Please help
Thanks
-LPerhaps you need
CAST(@.eventdata.query('data(//EventType)') AS SYSNAME)
"Learner" <pradev@.gmail.com> wrote in message
news:1139846190.438357.222750@.g43g2000cwa.googlegroups.com...
> Hello,
> I am trying to create DDL trigger as below
>
> ALTER TRIGGER DDLTRIGGER ON DATABASE
> FOR DDL_DATABASE_LEVEL_EVENTS
> AS
> DECLARE @.EventData XML
> SET @.EventData = EVENTDATA()
> INSERT DDLEVENTLOG
> (EVENTTYPE
> /*POSTTIME,
> SPID,
> SERVERNAME,
> LOGINNAME,
> DATABASENAME,
> SCHEMANAME,
> OBJECTNAME,
> OBJECTTYPE,
> TARGETOBJNAME,
> TARGETOBJTYPE,
> TSQLSTATEMENT,
> FULLDATA*/
> )
> VALUES
> (
> CONVERT (NVARCHAR(100),@.EventData.Query('DATA(//EventType)'))
> /*CONVERT (NVARCHAR(100),@.EVENTDATA.QUERY('DATA(//PostTime)')),
> CONVERT (NVARCHAR(100),@.EVENTDATA.QUERY('DATA(//SPID)')),
> CONVERT (NVARCHAR(100),@.EVENTDATA.QUERY('DATA(//SERVERNAME)')),
> CONVERT (NVARCHAR(100),@.EVENTDATA.QUERY('DATA(//LOGINNAME)')),
> CONVERT (NVARCHAR(100),@.EVENTDATA.QUERY('DATA(//DATABASENAME)')),
> CONVERT (NVARCHAR(100),@.EVENTDATA.QUERY('DATA(//SCHEMANAME)')),
> CONVERT (NVARCHAR(100),@.EVENTDATA.QUERY('DATA(//OBJECTNAME)')),
> CONVERT (NVARCHAR(100),@.EVENTDATA.QUERY('DATA(//OBJECTTYPE)')),
> CONVERT (NVARCHAR(100),@.EVENTDATA.QUERY('DATA(//TARGETOBJNAME)')),
> CONVERT (NVARCHAR(100),@.EVENTDATA.QUERY('DATA(//TARGETOBJTYPE)')),
> CONVERT (NVARCHAR(100),@.EVENTDATA.QUERY('DATA(//COMMANDTEXT)')),
> @.EventData*/
> )
> But when i execute this its throwing an error...
>
> Msg 227, Level 15, State 1, Procedure DDLTRIGGER, Line 6
> "Query" is not a valid function, property, or field.
> Am i doing some thing here?
> Please help
> Thanks
> -L
>|||Please post the DDL for DDLEVENTLOG.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
"Learner" <pradev@.gmail.com> wrote in message
news:1139846190.438357.222750@.g43g2000cwa.googlegroups.com...
Hello,
I am trying to create DDL trigger as below
ALTER TRIGGER DDLTRIGGER ON DATABASE
FOR DDL_DATABASE_LEVEL_EVENTS
AS
DECLARE @.EventData XML
SET @.EventData = EVENTDATA()
INSERT DDLEVENTLOG
(EVENTTYPE
/*POSTTIME,
SPID,
SERVERNAME,
LOGINNAME,
DATABASENAME,
SCHEMANAME,
OBJECTNAME,
OBJECTTYPE,
TARGETOBJNAME,
TARGETOBJTYPE,
TSQLSTATEMENT,
FULLDATA*/
)
VALUES
(
CONVERT (NVARCHAR(100),@.EventData.Query('DATA(//EventType)'))
/*CONVERT (NVARCHAR(100),@.EVENTDATA.QUERY('DATA(//PostTime)')),
CONVERT (NVARCHAR(100),@.EVENTDATA.QUERY('DATA(//SPID)')),
CONVERT (NVARCHAR(100),@.EVENTDATA.QUERY('DATA(//SERVERNAME)')),
CONVERT (NVARCHAR(100),@.EVENTDATA.QUERY('DATA(//LOGINNAME)')),
CONVERT (NVARCHAR(100),@.EVENTDATA.QUERY('DATA(//DATABASENAME)')),
CONVERT (NVARCHAR(100),@.EVENTDATA.QUERY('DATA(//SCHEMANAME)')),
CONVERT (NVARCHAR(100),@.EVENTDATA.QUERY('DATA(//OBJECTNAME)')),
CONVERT (NVARCHAR(100),@.EVENTDATA.QUERY('DATA(//OBJECTTYPE)')),
CONVERT (NVARCHAR(100),@.EVENTDATA.QUERY('DATA(//TARGETOBJNAME)')),
CONVERT (NVARCHAR(100),@.EVENTDATA.QUERY('DATA(//TARGETOBJTYPE)')),
CONVERT (NVARCHAR(100),@.EVENTDATA.QUERY('DATA(//COMMANDTEXT)')),
@.EventData*/
)
But when i execute this its throwing an error...
Msg 227, Level 15, State 1, Procedure DDLTRIGGER, Line 6
"Query" is not a valid function, property, or field.
Am i doing some thing here?
Please help
Thanks
-L|||Seems some of the XML and Xquery stuff is case sensitive. Below executed wit
h no errors on my
machine:
CREATE TRIGGER DDLTRIGGER ON DATABASE
FOR DDL_DATABASE_LEVEL_EVENTS
AS
DECLARE @.EventData XML
SET @.EventData = EVENTDATA()
INSERT DDLEVENTLOG
(EVENTTYPE
)
VALUES
(
@.EventData.query('data(//EventType)')
--CONVERT (NVARCHAR(100),@.EventData.Query('DATA(//EventType)'))
)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Learner" <pradev@.gmail.com> wrote in message
news:1139846190.438357.222750@.g43g2000cwa.googlegroups.com...
> Hello,
> I am trying to create DDL trigger as below
>
> ALTER TRIGGER DDLTRIGGER ON DATABASE
> FOR DDL_DATABASE_LEVEL_EVENTS
> AS
> DECLARE @.EventData XML
> SET @.EventData = EVENTDATA()
> INSERT DDLEVENTLOG
> (EVENTTYPE
> /*POSTTIME,
> SPID,
> SERVERNAME,
> LOGINNAME,
> DATABASENAME,
> SCHEMANAME,
> OBJECTNAME,
> OBJECTTYPE,
> TARGETOBJNAME,
> TARGETOBJTYPE,
> TSQLSTATEMENT,
> FULLDATA*/
> )
> VALUES
> (
> CONVERT (NVARCHAR(100),@.EventData.Query('DATA(//EventType)'))
> /*CONVERT (NVARCHAR(100),@.EVENTDATA.QUERY('DATA(//PostTime)')),
> CONVERT (NVARCHAR(100),@.EVENTDATA.QUERY('DATA(//SPID)')),
> CONVERT (NVARCHAR(100),@.EVENTDATA.QUERY('DATA(//SERVERNAME)')),
> CONVERT (NVARCHAR(100),@.EVENTDATA.QUERY('DATA(//LOGINNAME)')),
> CONVERT (NVARCHAR(100),@.EVENTDATA.QUERY('DATA(//DATABASENAME)')),
> CONVERT (NVARCHAR(100),@.EVENTDATA.QUERY('DATA(//SCHEMANAME)')),
> CONVERT (NVARCHAR(100),@.EVENTDATA.QUERY('DATA(//OBJECTNAME)')),
> CONVERT (NVARCHAR(100),@.EVENTDATA.QUERY('DATA(//OBJECTTYPE)')),
> CONVERT (NVARCHAR(100),@.EVENTDATA.QUERY('DATA(//TARGETOBJNAME)')),
> CONVERT (NVARCHAR(100),@.EVENTDATA.QUERY('DATA(//TARGETOBJTYPE)')),
> CONVERT (NVARCHAR(100),@.EVENTDATA.QUERY('DATA(//COMMANDTEXT)')),
> @.EventData*/
> )
> But when i execute this its throwing an error...
>
> Msg 227, Level 15, State 1, Procedure DDLTRIGGER, Line 6
> "Query" is not a valid function, property, or field.
> Am i doing some thing here?
> Please help
> Thanks
> -L
>|||Uri,
Thanks for the quick reply . But :( no luck yet.
After changing the whole line looks like this
CONVERT (NVARCHAR(100), cast(@.EventData.Query('Data(//EventType)')) as
sysname)
but it said
Msg 1035, Level 15, State 10, Procedure DDLTRIGGER, Line 23
Incorrect syntax near 'cast', expected 'AS'.
Am i doing it right here?
Thanks
-L|||Thank you for the quick reply. But i still get the same error on my
machine.
if i run your sql as is i got this
Msg 257, Level 16, State 3, Procedure DDLTRIGGER, Line 6
Implicit conversion from data type xml to nvarchar is not allowed. Use
the CONVERT function to run this query.
and later i uncommented the Convert line and commented out the first
line then i got the same Query error
Msg 227, Level 15, State 1, Procedure DDLTRIGGER, Line 6
"Query" is not a valid function, property, or field.
Do i need to setup any kind of option on my machine?
Thanks
-L|||You've misplaced the closing parentheses:
CONVERT (NVARCHAR(100), cast(@.EventData.Query('Data(//EventType)') as
sysname))
ML
http://milambda.blogspot.com/|||Looks like all of you have no problem running the above sql... But i
still get the same thig.
Here is the entire sql that i am trying to run
CREATE TRIGGER DDLTRIGGER ON DATABASE
FOR DDL_DATABASE_LEVEL_EVENTS
AS
DECLARE @.eventData XML
SET @.eventData = EventData()
INSERT DDLEVENTLOG
(EVENTTYPE
)
VALUES
(
CONVERT (NVARCHAR(100), cast(@.EventData.Query('Data(//EventType)') as
sysname))
)
Some thing wrong with it?
I still get the Query error
Msg 227, Level 15, State 1, Procedure DDLTRIGGER, Line 6
"Query" is not a valid function, property, or field.
Thanks
-L|||FWIW, I use @.EventData.Value, not @.EventData.Query. E.g. these work great
for me in a DDL auditing trigger:
@.eventdata.value('(/EVENT_INSTANCE/SPID)[1]','int')
@.eventdata.value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(100)')
@.eventdata.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'nvarchar(MAX)')|||I got it solved the line, CONVERT
(NVARCHAR(100),@.EventData.query('data(//EventType)')) works!
It doesn't recognise @.EventData.Query but it does @.EventData.query..
I thank all of you for looking into my problem.
How ever i need one more help with dropping a trigger
when i run this sql, drop trigger ddltrigger
i got this message. Is it some kind of security issue? what property do
i need to set inorder to drop it?
Msg 3701, Level 11, State 5, Line 1
Cannot drop the trigger 'ddltrigger', because it does not exist or you
do not have permission.
Thanks
-L

help needed on configure reporting service

report server: sql2005
remote database server: clustered server, sql2005
while configure on report server failed on the step:
create a grant scripts
with the error message:
System.Runtime.InteropServices.COMException (0x800706BA): The RPC server is
unavailable. (Exception from HRESULT: 0x800706BA)
at
System.Runtime.InteropServices.Marshal.ThrowExceptionForHRInternal(Int32
errorCode, IntPtr errorInfo)
at System.Management.ManagementScope.InitializeGuts (Object o)
at System.Management.ManagementScope.Initialize()
at System.Management.ManagementObjectSearcher.Initialize()
at System.Management.ManagementObjectSearcher.Get()
at Microsoft.ReportingServices.Diagnostics.WebUtil.GetIPAddresses (String
machine)
at
ReportServicesConfigUI.Panels.DatabaseSetupPanel.IsLocalDbServer(String
dbServer)
at
ReportServicesConfigUI.Panels.DatabaseSetupPanel.SetDatabaseConnectionTask(String
connectionString, String server, String database,
ConfigurationCredentialsType credsType, String account, String password,
Boolean upgrade, String dbVersion)
TCT/IP and name pipes is enabled, service RPC is started. 'remote access' is
set to 1 on the database srever. Also no firewall on the database server.
Any one have some hint on this one, any help is welcome.
ThanksWhat user are you using to run the services?
daw
"green hand" wrote:
> report server: sql2005
> remote database server: clustered server, sql2005
> while configure on report server failed on the step:
> create a grant scripts
> with the error message:
> System.Runtime.InteropServices.COMException (0x800706BA): The RPC server is
> unavailable. (Exception from HRESULT: 0x800706BA)
> at
> System.Runtime.InteropServices.Marshal.ThrowExceptionForHRInternal(Int32
> errorCode, IntPtr errorInfo)
> at System.Management.ManagementScope.InitializeGuts (Object o)
> at System.Management.ManagementScope.Initialize()
> at System.Management.ManagementObjectSearcher.Initialize()
> at System.Management.ManagementObjectSearcher.Get()
> at Microsoft.ReportingServices.Diagnostics.WebUtil.GetIPAddresses (String
> machine)
> at
> ReportServicesConfigUI.Panels.DatabaseSetupPanel.IsLocalDbServer(String
> dbServer)
> at
> ReportServicesConfigUI.Panels.DatabaseSetupPanel.SetDatabaseConnectionTask(String
> connectionString, String server, String database,
> ConfigurationCredentialsType credsType, String account, String password,
> Boolean upgrade, String dbVersion)
> TCT/IP and name pipes is enabled, service RPC is started. 'remote access' is
> set to 1 on the database srever. Also no firewall on the database server.
> Any one have some hint on this one, any help is welcome.
> Thanks|||the user to run the RPC and to configure the report ingservice has the System
admin for the systme and database.
"daw" wrote:
> What user are you using to run the services?
> daw
> "green hand" wrote:
> > report server: sql2005
> > remote database server: clustered server, sql2005
> >
> > while configure on report server failed on the step:
> > create a grant scripts
> >
> > with the error message:
> > System.Runtime.InteropServices.COMException (0x800706BA): The RPC server is
> > unavailable. (Exception from HRESULT: 0x800706BA)
> > at
> > System.Runtime.InteropServices.Marshal.ThrowExceptionForHRInternal(Int32
> > errorCode, IntPtr errorInfo)
> > at System.Management.ManagementScope.InitializeGuts (Object o)
> > at System.Management.ManagementScope.Initialize()
> > at System.Management.ManagementObjectSearcher.Initialize()
> > at System.Management.ManagementObjectSearcher.Get()
> > at Microsoft.ReportingServices.Diagnostics.WebUtil.GetIPAddresses (String
> > machine)
> > at
> > ReportServicesConfigUI.Panels.DatabaseSetupPanel.IsLocalDbServer(String
> > dbServer)
> > at
> > ReportServicesConfigUI.Panels.DatabaseSetupPanel.SetDatabaseConnectionTask(String
> > connectionString, String server, String database,
> > ConfigurationCredentialsType credsType, String account, String password,
> > Boolean upgrade, String dbVersion)
> >
> > TCT/IP and name pipes is enabled, service RPC is started. 'remote access' is
> > set to 1 on the database srever. Also no firewall on the database server.
> >
> > Any one have some hint on this one, any help is welcome.
> >
> > Thanks|||I'm not familiar with the problem, but hopefully one of these links will help:
http://support.microsoft.com/kb/224370
http://msdn2.microsoft.com/en-us/library/ms365170.aspx
http://blogs.msdn.com/sql_protocols/archive/2005/09/22/473012.aspx
daw
"green hand" wrote:
> the user to run the RPC and to configure the report ingservice has the System
> admin for the systme and database.
> "daw" wrote:
> > What user are you using to run the services?
> >
> > daw
> >
> > "green hand" wrote:
> >
> > > report server: sql2005
> > > remote database server: clustered server, sql2005
> > >
> > > while configure on report server failed on the step:
> > > create a grant scripts
> > >
> > > with the error message:
> > > System.Runtime.InteropServices.COMException (0x800706BA): The RPC server is
> > > unavailable. (Exception from HRESULT: 0x800706BA)
> > > at
> > > System.Runtime.InteropServices.Marshal.ThrowExceptionForHRInternal(Int32
> > > errorCode, IntPtr errorInfo)
> > > at System.Management.ManagementScope.InitializeGuts (Object o)
> > > at System.Management.ManagementScope.Initialize()
> > > at System.Management.ManagementObjectSearcher.Initialize()
> > > at System.Management.ManagementObjectSearcher.Get()
> > > at Microsoft.ReportingServices.Diagnostics.WebUtil.GetIPAddresses (String
> > > machine)
> > > at
> > > ReportServicesConfigUI.Panels.DatabaseSetupPanel.IsLocalDbServer(String
> > > dbServer)
> > > at
> > > ReportServicesConfigUI.Panels.DatabaseSetupPanel.SetDatabaseConnectionTask(String
> > > connectionString, String server, String database,
> > > ConfigurationCredentialsType credsType, String account, String password,
> > > Boolean upgrade, String dbVersion)
> > >
> > > TCT/IP and name pipes is enabled, service RPC is started. 'remote access' is
> > > set to 1 on the database srever. Also no firewall on the database server.
> > >
> > > Any one have some hint on this one, any help is welcome.
> > >
> > > Thanks

help needed in replication..

Hi All,

I am new to Sql Server.I have to create snapshot replication between SQL 2000 and 2005.The Publisher should be sql 2000 and the subscriber is sql 2005.I need ur valuable inputs as how to start.

Hi,

I would start with the replication section of SQL BOL (http://msdn2.microsoft.com/en-us/library/ms151198.aspx) for general information on snapshot replication. To use multiple version of SQL in a replication topology, you can refer to this page (http://msdn2.microsoft.com/en-us/library/ms143241.aspx) for more information.

Thanks,

Peng

|||

Hi Peng,

Thanks for you reply..

I have a few more queries on snapshot replication

Steps followed:

1)created distributor and publisher at the SQL Server 2000 end.

3)Create a Publication Database at SQL Server 2000 end.

2)Created subscribtion for the Publication at the SQL Server 2005 end.

Will these steps do for a Snap-shot replication?

Do i need to configure a distributor at the SQL Server 2005 end also?

How do i check whether the replication is success?

If i try to synchronize from the SQL server 2000 side,Its asking for a username,password...and its not accepting what ever login,passwd i give..

The Error Message i get is :

A Connection could not be established to <server 2005>

Reason:Login failed for the user “<user name>”.The user is not associated with a trusted SQL Server connection.

…………….

pls help me with this.

thankx

Help needed creating select statement

Hi,

I have a need to create a table detailing the ID of all contacts and the
last time they were contacted. This information is stored in 2 tables,
'contact' and 'activity' (ID in the 'contact' table links to 'main_contact'
in the 'activity' table).

I guess I need some sort if iteration to go through each contact and find
find the last activity that took place against each of them (there many be
more than 1 activity against each contact) and then place the output values
into the new table.

Can anyone show me how to go about this?

Thanks!This sounds like something that can be handled by a view, rather than
creating a table that has to be maintained. Either way the general
approach is something like that below. Note that it is all based on
assumptions, but hopefully it will be enough to give you the idea.

SELECT *
FROM Contact as C
JOIN Activity as A
ON C.ID = A.main_contact
WHERE A.ActivityDate =
(SELECT MAX(X.ActivityDate) FROM Activity as X
WHERE A.main_contact = X.mainContact)

Roy Harvey
Beacon Falls, CT

On Tue, 20 Mar 2007 15:02:06 -0000, "Mintyman" <mintyman@.ntlworld.com>
wrote:

Quote:

Originally Posted by

>Hi,
>
>I have a need to create a table detailing the ID of all contacts and the
>last time they were contacted. This information is stored in 2 tables,
>'contact' and 'activity' (ID in the 'contact' table links to 'main_contact'
>in the 'activity' table).
>
>I guess I need some sort if iteration to go through each contact and find
>find the last activity that took place against each of them (there many be
>more than 1 activity against each contact) and then place the output values
>into the new table.
>
>Can anyone show me how to go about this?
>
>Thanks!
>

|||Hi Roy,

Many thanks. I've managed to use your example to get exactly what I need.
Cheers!

"Roy Harvey" <roy_harvey@.snet.netwrote in message
news:7240031r1ken2gmb5a3qe8gfost4nvma25@.4ax.com...

Quote:

Originally Posted by

This sounds like something that can be handled by a view, rather than
creating a table that has to be maintained. Either way the general
approach is something like that below. Note that it is all based on
assumptions, but hopefully it will be enough to give you the idea.
>
SELECT *
FROM Contact as C
JOIN Activity as A
ON C.ID = A.main_contact
WHERE A.ActivityDate =
(SELECT MAX(X.ActivityDate) FROM Activity as X
WHERE A.main_contact = X.mainContact)
>
Roy Harvey
Beacon Falls, CT
>
On Tue, 20 Mar 2007 15:02:06 -0000, "Mintyman" <mintyman@.ntlworld.com>
wrote:
>

Quote:

Originally Posted by

>>Hi,
>>
>>I have a need to create a table detailing the ID of all contacts and the
>>last time they were contacted. This information is stored in 2 tables,
>>'contact' and 'activity' (ID in the 'contact' table links to
>>'main_contact'
>>in the 'activity' table).
>>
>>I guess I need some sort if iteration to go through each contact and find
>>find the last activity that took place against each of them (there many be
>>more than 1 activity against each contact) and then place the output
>>values
>>into the new table.
>>
>>Can anyone show me how to go about this?
>>
>>Thanks!
>>

help me!beginner create database password

I've learn DotNetNuke.I don't know how to create passID and userID for database sqlserver2005 express.someone help me.Thanks!thanks!You can do that by using the appropiate TSQL command, like sp_AddLogin and so forth, but as a beginner I would suggest you to use the SQL Server Management Express which has a graphical interface to administer the server instance. You will have to create a user and map the user to the appropiate database. There you will have to either grant him a role or dedicated permissions on the securables (objects) within the database.

http://msdn.microsoft.com/vstudio/express/sql/download/

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||

can you help me how step by step I have to do:

right click database(example I've created database name"dotnetnuke")?

then choose properties->choose permissions?

Or can you tell me TSQL command...?

Thank you very much!

|||

I've created new users name"dnnusers" and set some permissions but I've recieved some instructions:

"Grant failed for Database 'DotNetNuke'. (Microsoft.SqlServer.Smo)"why?

Help me write sql script

Hi I need to create query which would calculate weekly change of some values. There is table with values for every day. At the end of week I need to calculate % of change. It is something like this:

SELECT ((LastFridayValue - PreviousFridayValue) / PreviousFridayValue) * 100 from myTable.

or it could be something like this:

(LastValue - FirstValue) / FirstValue * 100 from top 5 values from my table order by ID DESC.

Please help me translate this into real sql query :)

Could you provide your table structure with some data example like last two weeks?

Thanks

|||There are two related tables. First table tblValues has structure like this:

ItemID

DayID

Value

1

261

1086,8986

2

262

1110,3700

3

263

1110,3700

4

264

1167,9900

5

265

1121,2900

6

266

1121,2900

7

267

1100,9600

8

268

1100,9600

9

269

1061,1000

10

270

1061,1000

11

271

985,6700

12

272

918,1300

13

273

908,5200

14

274

908,5200

and tblDate has structure like this

DayID

Date

261

28.1.2007

262

29.1.2007

263

30.1.2007

264

31.1.2007

265

1.2.2007

266

2.2.2007

267

3.2.2007

268

4.2.2007

269

5.2.2007

270

6.2.2007

271

7.2.2007

272

8.2.2007

273

9.2.2007

274

10.2.2007

|||

try this I hope it will point you in good direction:

createtable #test1(ItemIDint,

DayID

int,Valuenumeric(12,4))

insert

into #test1

Values

(1, 261, 1086.8986)

insert

into #test1

Values

(2, 262, 1110.3700)

insert

into #test1

Values

(3, 263, 1110.3700)

insert

into #test1

Values

(4, 264, 1167.9900)

insert

into #test1

Values

(5, 265, 1121.2900)

insert

into #test1

Values

(6, 266, 1121.2900)

insert

into #test1

Values

(7, 267, 1100.9600)

insert

into #test1

Values

(8, 268, 1100.9600)

insert

into #test1

Values

(9, 269, 1061.1000)

insert

into #test1

Values

(10, 270, 1061.1000)

insert

into #test1

Values

(11, 271, 985.6700)

insert

into #test1

Values

(12, 272, 918.1300)

insert

into #test1

Values

(13, 273, 908.5200)

insert

into #test1

Values

(14, 274, 908.5200)

create

table #days(DayIDint,

Date

datetime)

insert

into #days

Values

(261,convert(datetime,'28.1.2007',104))

insert

into #days

Values

(262,convert(datetime,'29.1.2007',104))

insert

into #days

Values

(263,convert(datetime,'30.1.2007',104))

insert

into #days

Values

(264,convert(datetime,'31.1.2007',104))

insert

into #days

Values

(265,convert(datetime,'1.2.2007',104))

insert

into #days

Values

(266,convert(datetime,'2.2.2007',104))

insert

into #days

Values

(267,convert(datetime,'3.2.2007',104))

insert

into #days

Values

(268,convert(datetime,'4.2.2007',104))

insert

into #days

Values

(269,convert(datetime,'5.2.2007',104))

insert

into #days

Values

(270,convert(datetime,'6.2.2007',104))

insert

into #days

Values

(271,convert(datetime,'7.2.2007',104))

insert

into #days

Values

(272,convert(datetime,'8.2.2007',104))

insert

into #days

Values

(273,convert(datetime,'9.2.2007',104))

insert

into #days

Values

(274,convert(datetime,'10.2.2007',104))

select

day1 [WeekDay],(cc.value-dd.value) [difference],*from(select day1,max(dayID) cur,MIN(dayID) prevfrom(selectdatepart(weekday,date) day1, dayIDfrom #days

where

date>dateadd(day,-13,getdate()))aa

group

by day1)aa

left

join #test1 ccON cc.dayid=aa.cur

left

join #test1 ddON dd.dayid=aa.prev

select

*from #test1

drop

table #days

drop

table #test1

Help me to create a order column

Hi all,

In Visual Foxpro i create a Order column that:
replace all myOrderColumn with recno()

[myOrderColumn]
1
2
3
4
5
6
...
EOF()

Help me to create myOrderColumn - SQL Server 2000 (by code, not use indetity column)

Thank alot!
I am wondering why this is necessary.

Bear with me here. . .

Fundamental relational database theory states that 'order' should be based on some quality of a relation. Remember, each tuple (record) in a table is a relation (Some people erroneously think that relational means referential integrity, i.e. foreign keys.) If there is some determinant that implies order, that determinant must be derivable by an examination of one or more fields of the tuple, e. g. Alphabetical Last Name, First Name or Date Of Transaction, etc. . .

You do realize that a real RDBMS makes no guarantee that a record last inserted will be the last physically in the table.

What? evertime a record is deleted/inserted you are going to change the order value? recalculate the index?

Do you need it for a surrogate key? why does it need to be ordered?

I am willing to wager my yearly salary that if you need an order column, your data model is underqualified.

I am also willing to wager that FoxPro is not a Relational Database System.|||Well, the first person that replied to your question should probably dismount his high horse a bit, as it is sometimes handy to retrieve an ordinal number corresponding to the row being returned (especially when generating output for reporting purposes or whatnot). I won't question why you are doing this, but I trust you have. a. very. good. reason.

In SQL Server 2005 :-) you can use the ROW_NUMBER() function in your SELECT list, and that will do the job. In SQL Server 2000, you have no such function. You will have to either use a cursor, number your rows in code, or insert your results into a similarly formed temp-table with an identity column on it and then return your values.

-Dave|||

Dave Markle wrote:

Well, the first person that replied to your question should probably dismount his high horse a bit, as it is sometimes handy to retrieve an ordinal number corresponding to the row being returned (especially when generating output for reporting purposes or whatnot). I won't question why you are doing this, but I trust you have. a. very. good. reason.

I wasn't trying to be arrogant I am just telling the truth.

What is he trying to model?
What is the purpose of the ordinal?
How would you determine the order?
Why would one not just index on that determinant and forget about the ordinal?

In a relational system, there is never a good reason for doing this. An arbitrary sequence number has NO intrinsic value.
Oh it isn't arbitrary?
Then how is it determined?
Index on the determinant and forget the order by column!
Oh! it is a multi-field key and you don't want to carry all the fields over to a child table?
Use an identity as a surrogate key. Primary Key on the multiple values and unique on the identity. Use the identity in the child as a foreign key to the parent.
To get the parent keys in proper order:

select identityfield from my table order by primarykeyfield1, primarykeyfield2

It matters not that the identityfield might not be in numeric sequence. . It will be in the order that is intrinsic to the data domain.

I am just making guesses as to what need there is for the order column. Again, I am sure that there is no need for it.

In SQL Server 2005 :-) you can use the ROW_NUMBER() function in your SELECT list, and that will do the job. In SQL Server 2000, you have no such function. You will have to either use a cursor, number your rows in code, or insert your results into a similarly formed temp-table with an identity column on it and then return your values.

This isn't what he is asking for. ROW_NUMBER and a temp identity have no persistence from run to run. That is, the rows returned could have a different row number or identity value if the underlying data changes via inserts or deletes - no intrinsic value. Therefore it has no use as part of a foreign key or any other use that has any real meaning.