Sql Update command: creating

Showing posts with label creating. Show all posts

Wednesday, March 28, 2012

Problems in exporting constraints and keys

When i try to replicate a production database as a new development database
by creating an empty DB called, say 'AppDev' in Enterprise Manager, and then
export the data using the Tasks->Export Data , the constraints, Primary Keys
and Foreign Keys are not exported , even though i have selected these
options . The data however is exported.
Is there a catch, or a better way to do this ?
Thanks,
Raj.
Why don't you script the database and all its objects and then run that
instead of the wizard? Right click on the db and choose All Tasks -
Generate SQL Scripts for the script wizard.
Andrew J. Kelly SQL MVP
"Chakravarthy" <r_chakravarthy@.hotmail.com> wrote in message
news:u4O6%23LnMHHA.448@.TK2MSFTNGP04.phx.gbl...
> When i try to replicate a production database as a new development
> database by creating an empty DB called, say 'AppDev' in Enterprise
> Manager, and then export the data using the Tasks->Export Data , the
> constraints, Primary Keys and Foreign Keys are not exported , even though
> i have selected these options . The data however is exported.
> Is there a catch, or a better way to do this ?
> Thanks,
> Raj.
>
sql

Problems in Creating Views in Sql

Hi everybody,
I have couple of Access databases needed to be
converted to access/Sql Client/Server database, which is new to me. I
am trying to convert my access quries to Sql Views or Stored
Procedures. I have run into a problem currently when creating view in
Sql:
I need to create some columns based on conditions of other columns,
for example:
1. Column example 1:
IIf([Column1]=-1,[ListPrice]*[Volume],IIf([Column2]=-1,0,[ListPrice]*Volume]))
2. Column example 2:
IIf(Len([Product].[P_No])<10,"",SUBSTRING([Product].[P_No],6,5))
3.Column example 3:
IIf([CustType]="D",([Field1]+[Field2])*[Volume],Field1*Volume)
The above are actually the columns that I need to create.
I have these functions built in Access Queries, but not sure how to
build them in Sql views. Have tried different ways, but failed.
Any body knows how to do it?
Any help will be greatly appreciated!
Thanks in advance!"Shelley" <schow@.hersheys.com> wrote in message
news:e085e628.0402020838.450efcdc@.posting.google.com...
> 3.Column example 3:
> IIf([CustType]="D",([Field1]+[Field2])*[Volume],Field1*Volume)
Take a look at the CASE statement in the online help.|||"Freddy" <noemail@.noemail> wrote in message news:<OT0rBBb6DHA.2524@.TK2MSFTNGP11.phx.gbl>...
> "Shelley" <schow@.hersheys.com> wrote in message
> news:e085e628.0402020838.450efcdc@.posting.google.com...
> > 3.Column example 3:
> > IIf([CustType]="D",([Field1]+[Field2])*[Volume],Field1*Volume)
> Take a look at the CASE statement in the online help.
Thanks Freddy.
This one works.

Problems in Creating Views in Sql

quote:

> 3.Column example 3:
> IIf([CustType]="D" ,([Field1]+[Field2])*[Volume],Field1*Vol
ume)

Take a look at the CASE statement in the online help.|||"Freddy" <noemail@.noemail> wrote in message news:<OT0rBBb6DHA.2524@.TK2MSFTNGP11.phx.gbl>...

quote:

> "Shelley" <schow@.hersheys.com> wrote in message
> news:e085e628.0402020838.450efcdc@.posting.google.com...
> Take a look at the CASE statement in the online help.

Thanks Freddy.
This one works.

problems in creating report (parent child hierarchy)

hello,
im working with as/rs 2005 and need help in creating a report.
my cube has a parent child hierarchy in "category".
structur is as follows:
Aa1
2b
B Cf
g10
1112
I like to have a function or something, that gives me the unique names of all children of maybe A (result should be: a,1,2,b,3,4,...)
there is a function called children, but i am not able to obtain my desired result. (in fact, i dont get any result with this fuction)
every little bit that shows me the right direction is highly appreciated ...
sincerely,
Rhapsody

If I understand you correctly, this should do the trick:

with

member [Measures].[UNMChildren]

as 'GENERATE(EXCEPT(descendants([Category].currentmember,10,SELF_BEFORE_AFTER),[Category].currentmember),

[Category].currentmember.uniquename, ","

select {[Measures].[UNMChildren]}

on columns,

{[Category].members} on rows

from CubeName

Hope this helps,

Santi

|||thank you santi, but that was not it ... or i didnt apply it the right way ...
i will provide you with some more details ... maybe you can help me than...
im using the adventure works dw.
measures: fact finance
dimension: dim organization (esp. parent child hierarchy: parent organization key)
i have a query parameter "DimOrganizationParentOrganizationKey", which is a multivalue parameter and user can choose IDs (from different levels) he/she wants to see.
the structure of the IDs looks as follows:
1|||

I'm sorry, but I can't help you with Reporting Services.

As far as I know, the mdx should be able to return the uniquenames of a dimension member's descendants separated by commas.

Maybe if you have a secondary datasource in Reporting Services which returns the Unique Names of the members passed by parameter, you could be able to use that to make visible/invisible the appropriate members. Not sure though...

sql

Friday, March 23, 2012

Problems Creating TVF function

I had succesfully created a CLR function which was fully operational on the development server. Today the powers that be decided to do a full restore from the Production server because the wanted the most recent data on the server. Since our code has not been deployed to production I went through the exercise of redeploying everything to the server and all goes well until I try to create the function:

Code Snippet

CREATE FUNCTION f_GetGroupMembership(@.providerKey nvarchar(200), @.connection nvarchar(1000))

RETURNS TABLE (groupID uniqueidentifier, groupName nvarchar(100), groupType nvarchar(25))

EXTERNAL NAME NavigatorSecurity.groups.GetGroupsForUser

This returns an error:

Msg 10305, Level 16, State 1, Procedure f_GetGroupMembership, Line 1

The Init method for a CLR table-valued function must be annotated with SqlFunctionAttribute.

Now remember this CLR was working correctly previous to the restore from backup. But just in case I got a case of dumb*** I checked my clr code anyhow here it is:

Code Snippet

using System;

using System.Data;

using System.Data.Sql;

using System.Data.SqlTypes;

using System.Data.SqlClient;

using Microsoft.SqlServer.Server;

using System.DirectoryServices;

using System.Collections;

public class GroupMembership

{

[SqlFunctionAttribute(FillRowMethodName = "FillGroupRow")]

public static IEnumerable GetGroupsForUser(String ProviderKey, String Connection)

{

return groups.GetGroupsForUser(ProviderKey, Connection);

}

public static void FillGroupRow(Object obj, out SqlGuid guid, out SqlString name, out SqlString type)

{

//cast the enumerator passed in

group grp = (group)obj;

//create output parameters

guid = new SqlGuid(grp.guid);

name = new SqlString(grp.Name);

type = new SqlString(grp.applicationData);

}

}

Can anyone tell me why this is happening? How can I overcome this problem?

Help will be highly appreciated.

Help Please! there is no reference to this error anywhere|||
In your CREATE FUNCTION statement, you are referencing the wrong class, groups.GetGroupsForUser instead of GroupMembership.GetGroupsForUser.|||

Steven,

Thank you I figured out I did not reply here because I did not want to look like the dumba** that I am. Thanks Anyway. I'll try to review my code a third time before I make a fool of myself in public.

Regards,

Problems creating subscription...

I am attempting to subscribe to a publication and I receive the
following message...
SQL 2005 & Win 2005 on both ends.
===================================
SQL Server could not create a subscription for Subscriber 'r99s-sqla'.
(New Subscription Wizard)
===================================
An exception occurred while executing a Transact-SQL statement or
batch. (Microsoft.SqlServer.ConnectionInfo)
Program Location:
at
Microsoft.SqlServer.Management.Common.ServerConnec tion.ExecuteNonQuery(String
sqlCommand, ExecutionTypes executionType)
at
Microsoft.SqlServer.Management.Common.ServerConnec tion.ExecuteNonQuery(String
sqlCommand)
at
Microsoft.SqlServer.Replication.ReplicationObject. ExecCommand(String
commandIn)
at Microsoft.SqlServer.Replication.ReplicationObject. CommonCreate()
at Microsoft.SqlServer.Replication.Subscription.Creat e()
at
Microsoft.SqlServer.Management.UI.CreateSubscripti onWizard.CreatePushSubscription(SubWizardSubscribe r
subscriber, SubWizardPublication pubInfo)
at
Microsoft.SqlServer.Management.UI.CreateSubscripti onWizard.CreateSubscriptions(Boolean
executeNow)
===================================
String or binary data would be truncated.
Changed database context to 'STOREMAIN'.
Job 'R00S-SQLA-STOREMAIN-Storemain from 00-R99S-SQLA-427' started
successfully.
Warning: The distribution agent job has been implicitly created and
will run under the SQL Server Agent Service Account.
'ROUSES.COM\LarryDSI' is a member of sysadmin server role and cannot be
granted to or revoked from the proxy. Members of sysadmin server role
are allowed to use any proxy. (.Net SqlClient Data Provider)
For help, click:
[url]http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00 .2047&EvtSrc=MSSQLServer&EvtID=8152&LinkId=20476[/url]
Server Name: r00s-sqla
Error Number: 8152
Severity: 16
State: 10
Procedure: sp_MSget_synctran_commands
Line Number: 198
Program Location:
at System.Data.SqlClient.SqlConnection.OnError(SqlExc eption
exception, Boolean breakConnection)
at System.Data.SqlClient.SqlInternalConnection.OnErro r(SqlException
exception, Boolean breakConnection)
at
System.Data.SqlClient.TdsParser.ThrowExceptionAndW arning(TdsParserStateObject
stateObj)
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior,
SqlCommand cmdHandler, SqlDataReader dataStream,
BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlCommand.RunExecuteNonQuer yTds(String
methodName, Boolean async)
at
System.Data.SqlClient.SqlCommand.InternalExecuteNo nQuery(DbAsyncResult
result, String methodName, Boolean sendToPipe)
at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
at
Microsoft.SqlServer.Management.Common.ServerConnec tion.ExecuteNonQuery(String
sqlCommand, ExecutionTypes executionType)
Hi Larry
Can you script out the subscription and run it from Query Analyzer/SSMS?
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"LPR-3rd" <lreames@.gmail.com> wrote in message
news:1168884119.848778.223260@.m58g2000cwm.googlegr oups.com...
> I am attempting to subscribe to a publication and I receive the
> following message...
> SQL 2005 & Win 2005 on both ends.
> ===================================
> SQL Server could not create a subscription for Subscriber 'r99s-sqla'.
> (New Subscription Wizard)
> ===================================
> An exception occurred while executing a Transact-SQL statement or
> batch. (Microsoft.SqlServer.ConnectionInfo)
> --
> Program Location:
> at
> Microsoft.SqlServer.Management.Common.ServerConnec tion.ExecuteNonQuery(String
> sqlCommand, ExecutionTypes executionType)
> at
> Microsoft.SqlServer.Management.Common.ServerConnec tion.ExecuteNonQuery(String
> sqlCommand)
> at
> Microsoft.SqlServer.Replication.ReplicationObject. ExecCommand(String
> commandIn)
> at Microsoft.SqlServer.Replication.ReplicationObject. CommonCreate()
> at Microsoft.SqlServer.Replication.Subscription.Creat e()
> at
> Microsoft.SqlServer.Management.UI.CreateSubscripti onWizard.CreatePushSubscription(SubWizardSubscribe r
> subscriber, SubWizardPublication pubInfo)
> at
> Microsoft.SqlServer.Management.UI.CreateSubscripti onWizard.CreateSubscriptions(Boolean
> executeNow)
> ===================================
> String or binary data would be truncated.
> Changed database context to 'STOREMAIN'.
> Job 'R00S-SQLA-STOREMAIN-Storemain from 00-R99S-SQLA-427' started
> successfully.
> Warning: The distribution agent job has been implicitly created and
> will run under the SQL Server Agent Service Account.
> 'ROUSES.COM\LarryDSI' is a member of sysadmin server role and cannot be
> granted to or revoked from the proxy. Members of sysadmin server role
> are allowed to use any proxy. (.Net SqlClient Data Provider)
> --
> For help, click:
> [url]http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00 .2047&EvtSrc=MSSQLServer&EvtID=8152&LinkId=20476[/url]
> --
> Server Name: r00s-sqla
> Error Number: 8152
> Severity: 16
> State: 10
> Procedure: sp_MSget_synctran_commands
> Line Number: 198
>
> --
> Program Location:
> at System.Data.SqlClient.SqlConnection.OnError(SqlExc eption
> exception, Boolean breakConnection)
> at System.Data.SqlClient.SqlInternalConnection.OnErro r(SqlException
> exception, Boolean breakConnection)
> at
> System.Data.SqlClient.TdsParser.ThrowExceptionAndW arning(TdsParserStateObject
> stateObj)
> at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior,
> SqlCommand cmdHandler, SqlDataReader dataStream,
> BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
> at System.Data.SqlClient.SqlCommand.RunExecuteNonQuer yTds(String
> methodName, Boolean async)
> at
> System.Data.SqlClient.SqlCommand.InternalExecuteNo nQuery(DbAsyncResult
> result, String methodName, Boolean sendToPipe)
> at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
> at
> Microsoft.SqlServer.Management.Common.ServerConnec tion.ExecuteNonQuery(String
> sqlCommand, ExecutionTypes executionType)
>
|||That is what I did to fix the problem.
Thanks,
Larry...
|||Sooy...different problem...
I scriped the creation of the publication, it took 23 min to create it.
I scripted the creation of the subscription & received the following
message......
Job 'R00S-SQLA-STOREMAIN-Storemain to 99-R99S-SQLA-449' started
successfully.
Warning: The distribution agent job has been implicitly created and
will run under the SQL Server Agent Service Account.
Msg 8152, Level 16, State 10, Procedure sp_MSget_synctran_commands,
Line 198
String or binary data would be truncated.
'ROUSES.COM\LarryDSI' is a member of sysadmin server role and cannot be
granted to or revoked from the proxy. Members of sysadmin server role
are allowed to use any proxy.
AHIA,
Larry...
|||I am looking at the code for sp_MSget_synctran_commands and this is what it
has around line 198
if @.command_only = 0
select * from #art_commands order by id
else
select commands from #art_commands order by id
Do you have any really long table names?
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"LPR-3rd" <lreames@.gmail.com> wrote in message
news:1168927021.980769.34300@.q2g2000cwa.googlegrou ps.com...
> Sooy...different problem...
> I scriped the creation of the publication, it took 23 min to create it.
> I scripted the creation of the subscription & received the following
> message......
> Job 'R00S-SQLA-STOREMAIN-Storemain to 99-R99S-SQLA-449' started
> successfully.
> Warning: The distribution agent job has been implicitly created and
> will run under the SQL Server Agent Service Account.
> Msg 8152, Level 16, State 10, Procedure sp_MSget_synctran_commands,
> Line 198
> String or binary data would be truncated.
> 'ROUSES.COM\LarryDSI' is a member of sysadmin server role and cannot be
> granted to or revoked from the proxy. Members of sysadmin server role
> are allowed to use any proxy.
>
> AHIA,
> Larry...
>
|||The longest would be 'tblReceivingHeader'
That is not long in my book!!!
Larry...
|||Nope, its not. Enable logging to find out where it is bombing.
If you narrow this down, please post the schema of the problem table, and
publication and subscription scripts here.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"LPR-3rd" <lreames@.gmail.com> wrote in message
news:1169050867.439445.84200@.11g2000cwr.googlegrou ps.com...
> The longest would be 'tblReceivingHeader'
> That is not long in my book!!!
> Larry...
>

Problems creating second relationship to the same table

I have two tables: ads and categories. I have an existing relationship: categories.id (PK) and ads.categoryid (FK). Now I want to create additional relationship with categories.id (PK) on ads.SecondCategoryID (FK). When I try to save it in SQL Manager I get the following error:

- Unable to create relationship 'FK_classifieds_Ads_classifieds_Categories2'.
The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "FK_classifieds_Ads_classifieds_Categories2". The conflict occurred in database "mydb", table "dbo.classifieds_Categories", column 'Id'.

Hi. Maybe the SecondCategoryId field has data that not exists in the categories table|||

mariop77 is probably right.

Try doing a SELECT DISTINCT SecondCategoryID FROM ADS

Make sure all FKs really do exist.

|||It has Nulls someties. How can I allow nulls? The reason is sometimes there will be no second category...|||

Hi,

Actually, null is allowed here. But you need to make sure that Allow Null has been checked in the design view of the table.

|||Yes, it is allowed on the column level of course, yet can not create relationship...|||

Seems to me like this is a limitation not allowing more than one join to the same table... Do I need to create alias for the existing table?

1 Category (alias 1) many Ads on Ads.CategoryID

1 Category (Alias 2) many Ads on Ads.SecondCategoryID

I used to be able to do this in MS Access, but maybe SQL does not allow...

sql

Problems Creating Report

Hi,

I have only recently started playing around with Reporting Services (2005), and I am having some problems creating a basic report.

What I want to display is a table which shows product names in the first column, sales for a specific period this year in the second column, and sales for the same period last year in the third column. The 'specific period' is determined by specifying a start week and an end week which I have set up as parameters.

I have managed to produce the first 2 column with no trouble. However, for the third column I need to subtract 52 weeks from the parameters and use them as new parameter in the third column.

Can anyone offer any suggestions?

Dave

Sounds like a T-SQL issue. Do you have a sample of the query?|||My apologies, I just realised I neglected to mention that the source data was in a cube. What I have in the cube is a product dimension, a time dimension (which goes down to week level), and a measure for sales amount.|||Anybody got any ideas?|||

In MDX I would suggest using ParallelPeriod. The following is an AdventureWorks query showing an example. You just need to replace the expicit time members with parameters:

WITH
MEMBER [Measures].[Selection] as '
Sum(
[Date].[Calendar].[Month].&[2003]&[1]
:
[Date].[Calendar].[Month].&[2003]&[5]
, [Measures].[Sales Amount]
)'
MEMBER [Measures].[SamePeriodLastYear] as '
Sum(
ParallelPeriod([Date].[Calendar].[Calendar Year], 1, [Date].[Calendar].[Month].&[2003]&[1])
:
ParallelPeriod([Date].[Calendar].[Calendar Year], 1, [Date].[Calendar].[Month].&[2003]&[5])
, [Measures].[Sales Amount]
)'

SELECT
{ [Measures].[Selection]
, [Measures].[SamePeriodLastYear]
} ON 0,

NON EMPTY [Product].[Product Categories].Members ON 1

FROM [Adventure Works]

|||Thanks very much, that works a treat.|||

Ok,

I managed to get the MDX query working in SQL Studio but when I try to parameterize it in Reporting Services, I don't get any rows returned.

I have created 2 parameters (StartWeek + EndWeek) which are both integers, and have used these in 2 calculated fields. The code for these is:

ThisYear

Sum(
[Dim Week No].[Week No].&[@.StartWeek]
:
[Dim Week No].[Week No].&[@.EndWeek]
, [Measures].[Rate]
)

LastYear

Sum(
ParallelPeriod([Dim Week No].[Week in Year].[Year Name], 1,[Dim Week No].[Week in Year].[Week No].&[@.StartWeek] )
:
ParallelPeriod([Dim Week No].[Week in Year].[Year Name], 1,[Dim Week No].[Week in Year].[Week No].&[@.EndWeek] )
, [Measures].[Rate]
)

I have then just added these 2 fields, and the product field to my report's dataset. Could somebody please tell me where I am going wrong as this is driving me crazy!!

Dave

|||

Ok,

I managed to get the MDX query working in SQL Studio but when I try to parameterize it in Reporting Services, I don't get any rows returned.

I have created 2 parameters (StartWeek + EndWeek) which are both integers, and have used these in 2 calculated fields. The code for these is:

ThisYear

Sum(
[Dim Week No].[Week No].&[@.StartWeek]
:
[Dim Week No].[Week No].&[@.EndWeek]
, [Measures].[Rate]
)

LastYear

I have then just added these 2 fields, and the product field to my report's dataset. Could somebody please tell me where I am going wrong as this is driving me crazy!!

Dave

|||

Ok,

I managed to get the MDX query working in SQL Studio but when I try to parameterize it in Reporting Services, I don't get any rows returned.

I have created 2 parameters (StartWeek + EndWeek) which are both integers, and have used these in 2 calculated fields. The code for these is:

ThisYear

Sum(
[Dim Week No].[Week No].&[@.StartWeek]
:
[Dim Week No].[Week No].&[@.EndWeek]
, [Measures].[Rate]
)

LastYear

I have then just added these 2 fields, and the product field to my report's dataset. Could somebody please tell me where I am going wrong as this is driving me crazy!!

Dave

|||

Ok,

I managed to get the MDX query working in SQL Studio but when I try to parameterize it in Reporting Services, I don't get any rows returned.

I have created 2 parameters (StartWeek + EndWeek) which are both integers, and have used these in 2 calculated fields. The code for these is:

ThisYear

Sum(
[Dim Week No].[Week No].&[@.StartWeek]
:
[Dim Week No].[Week No].&[@.EndWeek]
, [Measures].[Rate]
)

LastYear

I have then just added these 2 fields, and the product field to my report's dataset. Could somebody please tell me where I am going wrong as this is driving me crazy!!

Dave

|||

Ok,

I managed to get the MDX query working in SQL Studio but when I try to parameterize it in Reporting Services, I don't get any rows returned.

I have created 2 parameters (StartWeek + EndWeek) which are both integers, and have used these in 2 calculated fields. The code for these is:

ThisYear

Sum(
[Dim Week No].[Week No].&[@.StartWeek]
:
[Dim Week No].[Week No].&[@.EndWeek]
, [Measures].[Rate]
)

LastYear

I have then just added these 2 fields, and the product field to my report's dataset. Could somebody please tell me where I am going wrong as this is driving me crazy!!

Dave

|||

Ok,

I managed to get the MDX query working in SQL Studio but when I try to parameterize it in Reporting Services, I don't get any rows returned.

I have created 2 parameters (StartWeek + EndWeek) which are both integers, and have used these in 2 calculated fields. The code for these is:

ThisYear

Sum(
[Dim Week No].[Week No].&[@.StartWeek]
:
[Dim Week No].[Week No].&[@.EndWeek]
, [Measures].[Rate]
)

LastYear

I have then just added these 2 fields, and the product field to my report's dataset. Could somebody please tell me where I am going wrong as this is driving me crazy!!

Dave

|||

Try constructing a MemberUniqueName string and passing that to the StrToMember functiuon

Sum(
StrToMember("[Dim Week No].[Week No].&[" + CStr(@.StartWeek) + "] ", CONSTRAINED)
:
StrToMember("[Dim Week No].[Week No].&[" + CStr(@.EndWeek) + "] ", CONSTRAINED)
, [Measures].[Rate]
)

|||

Hi Adam,

Again, that works fine in SQL Studio with integer values instead of the parameters. However, when I try it in Reporting Services with my parameters I get the following error:

"The 'ThisYear' calculated member cannot be created because of the following error: Parser: The StartWeek parameter could not be resolved because it was referenced in ann inner subexpression"

My calculated member field is now:

"Sum(
StrToMember("[Dim Week No].[Week in Year].[Week No].&[" + CStr(@.StartWeek) + "] ", CONSTRAINED)
:
StrToMember("[Dim Week No].[Week in Year].[Week No].&[" + CStr(@.EndWeek) + "] ", CONSTRAINED)
, [Measures].[Rate]
)"

|||

Some options:

try removing the CStr() call and see if that works
OR

Problems Creating Report

Hi,

I have only recently started playing around with Reporting Services (2005), and I am having some problems creating a basic report.

I have managed to produce the first 2 column with no trouble. However, for the third column I need to subtract 52 weeks from the parameters and use them as new parameter in the third column.

Can anyone offer any suggestions?

Dave

In MDX I would suggest using ParallelPeriod. The following is an AdventureWorks query showing an example. You just need to replace the expicit time members with parameters:

SELECT
{ [Measures].[Selection]
, [Measures].[SamePeriodLastYear]
} ON 0,

NON EMPTY [Product].[Product Categories].Members ON 1

FROM [Adventure Works]

|||Thanks very much, that works a treat.|||

Ok,

I managed to get the MDX query working in SQL Studio but when I try to parameterize it in Reporting Services, I don't get any rows returned.

I have created 2 parameters (StartWeek + EndWeek) which are both integers, and have used these in 2 calculated fields. The code for these is:

ThisYear

Sum(
[Dim Week No].[Week No].&[@.StartWeek]
:
[Dim Week No].[Week No].&[@.EndWeek]
, [Measures].[Rate]
)

LastYear

I have then just added these 2 fields, and the product field to my report's dataset. Could somebody please tell me where I am going wrong as this is driving me crazy!!

Dave

|||

Ok,

I managed to get the MDX query working in SQL Studio but when I try to parameterize it in Reporting Services, I don't get any rows returned.

I have created 2 parameters (StartWeek + EndWeek) which are both integers, and have used these in 2 calculated fields. The code for these is:

ThisYear

Sum(
[Dim Week No].[Week No].&[@.StartWeek]
:
[Dim Week No].[Week No].&[@.EndWeek]
, [Measures].[Rate]
)

LastYear

I have then just added these 2 fields, and the product field to my report's dataset. Could somebody please tell me where I am going wrong as this is driving me crazy!!

Dave

|||

Ok,

I managed to get the MDX query working in SQL Studio but when I try to parameterize it in Reporting Services, I don't get any rows returned.

I have created 2 parameters (StartWeek + EndWeek) which are both integers, and have used these in 2 calculated fields. The code for these is:

ThisYear

Sum(
[Dim Week No].[Week No].&[@.StartWeek]
:
[Dim Week No].[Week No].&[@.EndWeek]
, [Measures].[Rate]
)

LastYear

I have then just added these 2 fields, and the product field to my report's dataset. Could somebody please tell me where I am going wrong as this is driving me crazy!!

Dave

|||

Ok,

I managed to get the MDX query working in SQL Studio but when I try to parameterize it in Reporting Services, I don't get any rows returned.

I have created 2 parameters (StartWeek + EndWeek) which are both integers, and have used these in 2 calculated fields. The code for these is:

ThisYear

Sum(
[Dim Week No].[Week No].&[@.StartWeek]
:
[Dim Week No].[Week No].&[@.EndWeek]
, [Measures].[Rate]
)

LastYear

I have then just added these 2 fields, and the product field to my report's dataset. Could somebody please tell me where I am going wrong as this is driving me crazy!!

Dave

|||

Ok,

I managed to get the MDX query working in SQL Studio but when I try to parameterize it in Reporting Services, I don't get any rows returned.

I have created 2 parameters (StartWeek + EndWeek) which are both integers, and have used these in 2 calculated fields. The code for these is:

ThisYear

Sum(
[Dim Week No].[Week No].&[@.StartWeek]
:
[Dim Week No].[Week No].&[@.EndWeek]
, [Measures].[Rate]
)

LastYear

I have then just added these 2 fields, and the product field to my report's dataset. Could somebody please tell me where I am going wrong as this is driving me crazy!!

Dave

|||

Ok,

I managed to get the MDX query working in SQL Studio but when I try to parameterize it in Reporting Services, I don't get any rows returned.

I have created 2 parameters (StartWeek + EndWeek) which are both integers, and have used these in 2 calculated fields. The code for these is:

ThisYear

Sum(
[Dim Week No].[Week No].&[@.StartWeek]
:
[Dim Week No].[Week No].&[@.EndWeek]
, [Measures].[Rate]
)

LastYear

I have then just added these 2 fields, and the product field to my report's dataset. Could somebody please tell me where I am going wrong as this is driving me crazy!!

Dave

|||

Try constructing a MemberUniqueName string and passing that to the StrToMember functiuon

Sum(
StrToMember("[Dim Week No].[Week No].&[" + CStr(@.StartWeek) + "] ", CONSTRAINED)
:
StrToMember("[Dim Week No].[Week No].&[" + CStr(@.EndWeek) + "] ", CONSTRAINED)
, [Measures].[Rate]
)

|||

Hi Adam,

Again, that works fine in SQL Studio with integer values instead of the parameters. However, when I try it in Reporting Services with my parameters I get the following error:

"The 'ThisYear' calculated member cannot be created because of the following error: Parser: The StartWeek parameter could not be resolved because it was referenced in ann inner subexpression"

My calculated member field is now:

|||

Some options:

try removing the CStr() call and see if that works
OR

Problems Creating Report

Hi,

I have only recently started playing around with Reporting Services (2005), and I am having some problems creating a basic report.

I have managed to produce the first 2 column with no trouble. However, for the third column I need to subtract 52 weeks from the parameters and use them as new parameter in the third column.

Can anyone offer any suggestions?

Dave

In MDX I would suggest using ParallelPeriod. The following is an AdventureWorks query showing an example. You just need to replace the expicit time members with parameters:

SELECT
{ [Measures].[Selection]
, [Measures].[SamePeriodLastYear]
} ON 0,

NONEMPTY [Product].[Product Categories].MembersON 1

FROM [Adventure Works]

|||Thanks very much, that works a treat.|||

Ok,

I managed to get the MDX query working in SQL Studio but when I try to parameterize it in Reporting Services, I don't get any rows returned.

I have created 2 parameters (StartWeek + EndWeek) which are both integers, and have used these in 2 calculated fields. The code for these is:

ThisYear

Sum(
[Dim Week No].[Week No].&[@.StartWeek]
:
[Dim Week No].[Week No].&[@.EndWeek]
, [Measures].[Rate]
)

LastYear

I have then just added these 2 fields, and the product field to my report's dataset. Could somebody please tell me where I am going wrong as this is driving me crazy!!

Dave

|||

Ok,

I managed to get the MDX query working in SQL Studio but when I try to parameterize it in Reporting Services, I don't get any rows returned.

I have created 2 parameters (StartWeek + EndWeek) which are both integers, and have used these in 2 calculated fields. The code for these is:

ThisYear

Sum(
[Dim Week No].[Week No].&[@.StartWeek]
:
[Dim Week No].[Week No].&[@.EndWeek]
, [Measures].[Rate]
)

LastYear

I have then just added these 2 fields, and the product field to my report's dataset. Could somebody please tell me where I am going wrong as this is driving me crazy!!

Dave

|||

Ok,

I managed to get the MDX query working in SQL Studio but when I try to parameterize it in Reporting Services, I don't get any rows returned.

I have created 2 parameters (StartWeek + EndWeek) which are both integers, and have used these in 2 calculated fields. The code for these is:

ThisYear

Sum(
[Dim Week No].[Week No].&[@.StartWeek]
:
[Dim Week No].[Week No].&[@.EndWeek]
, [Measures].[Rate]
)

LastYear

I have then just added these 2 fields, and the product field to my report's dataset. Could somebody please tell me where I am going wrong as this is driving me crazy!!

Dave

|||

Ok,

I managed to get the MDX query working in SQL Studio but when I try to parameterize it in Reporting Services, I don't get any rows returned.

I have created 2 parameters (StartWeek + EndWeek) which are both integers, and have used these in 2 calculated fields. The code for these is:

ThisYear

Sum(
[Dim Week No].[Week No].&[@.StartWeek]
:
[Dim Week No].[Week No].&[@.EndWeek]
, [Measures].[Rate]
)

LastYear

I have then just added these 2 fields, and the product field to my report's dataset. Could somebody please tell me where I am going wrong as this is driving me crazy!!

Dave

|||

Ok,

I managed to get the MDX query working in SQL Studio but when I try to parameterize it in Reporting Services, I don't get any rows returned.

I have created 2 parameters (StartWeek + EndWeek) which are both integers, and have used these in 2 calculated fields. The code for these is:

ThisYear

Sum(
[Dim Week No].[Week No].&[@.StartWeek]
:
[Dim Week No].[Week No].&[@.EndWeek]
, [Measures].[Rate]
)

LastYear

I have then just added these 2 fields, and the product field to my report's dataset. Could somebody please tell me where I am going wrong as this is driving me crazy!!

Dave

|||

Ok,

I managed to get the MDX query working in SQL Studio but when I try to parameterize it in Reporting Services, I don't get any rows returned.

I have created 2 parameters (StartWeek + EndWeek) which are both integers, and have used these in 2 calculated fields. The code for these is:

ThisYear

Sum(
[Dim Week No].[Week No].&[@.StartWeek]
:
[Dim Week No].[Week No].&[@.EndWeek]
, [Measures].[Rate]
)

LastYear

I have then just added these 2 fields, and the product field to my report's dataset. Could somebody please tell me where I am going wrong as this is driving me crazy!!

Dave

|||

Try constructing a MemberUniqueName string and passing that to the StrToMember functiuon

Sum(
StrToMember("[Dim Week No].[Week No].&[" + CStr(@.StartWeek) + "] ", CONSTRAINED)
:
StrToMember("[Dim Week No].[Week No].&[" + CStr(@.EndWeek) + "] ", CONSTRAINED)
, [Measures].[Rate]
)

|||

Hi Adam,

Again, that works fine in SQL Studio with integer values instead of the parameters. However, when I try it in Reporting Services with my parameters I get the following error:

"The 'ThisYear' calculated member cannot be created because of the following error: Parser: The StartWeek parameter could not be resolved because it was referenced in ann inner subexpression"

My calculated member field is now:

|||

Some options:

try removing the CStr() call and see if that works
OR

Problems Creating Report

Hi,

I have only recently started playing around with Reporting Services (2005), and I am having some problems creating a basic report.

I have managed to produce the first 2 column with no trouble. However, for the third column I need to subtract 52 weeks from the parameters and use them as new parameter in the third column.

Can anyone offer any suggestions?

Dave

In MDX I would suggest using ParallelPeriod. The following is an AdventureWorks query showing an example. You just need to replace the expicit time members with parameters:

SELECT
{ [Measures].[Selection]
, [Measures].[SamePeriodLastYear]
} ON 0,

NON EMPTY [Product].[Product Categories].Members ON 1

FROM [Adventure Works]

|||Thanks very much, that works a treat.|||

Ok,

I managed to get the MDX query working in SQL Studio but when I try to parameterize it in Reporting Services, I don't get any rows returned.

I have created 2 parameters (StartWeek + EndWeek) which are both integers, and have used these in 2 calculated fields. The code for these is:

ThisYear

Sum(
[Dim Week No].[Week No].&[@.StartWeek]
:
[Dim Week No].[Week No].&[@.EndWeek]
, [Measures].[Rate]
)

LastYear

I have then just added these 2 fields, and the product field to my report's dataset. Could somebody please tell me where I am going wrong as this is driving me crazy!!

Dave

|||

Ok,

I managed to get the MDX query working in SQL Studio but when I try to parameterize it in Reporting Services, I don't get any rows returned.

I have created 2 parameters (StartWeek + EndWeek) which are both integers, and have used these in 2 calculated fields. The code for these is:

ThisYear

Sum(
[Dim Week No].[Week No].&[@.StartWeek]
:
[Dim Week No].[Week No].&[@.EndWeek]
, [Measures].[Rate]
)

LastYear

I have then just added these 2 fields, and the product field to my report's dataset. Could somebody please tell me where I am going wrong as this is driving me crazy!!

Dave

|||

Ok,

I managed to get the MDX query working in SQL Studio but when I try to parameterize it in Reporting Services, I don't get any rows returned.

I have created 2 parameters (StartWeek + EndWeek) which are both integers, and have used these in 2 calculated fields. The code for these is:

ThisYear

Sum(
[Dim Week No].[Week No].&[@.StartWeek]
:
[Dim Week No].[Week No].&[@.EndWeek]
, [Measures].[Rate]
)

LastYear

I have then just added these 2 fields, and the product field to my report's dataset. Could somebody please tell me where I am going wrong as this is driving me crazy!!

Dave

|||

Ok,

I managed to get the MDX query working in SQL Studio but when I try to parameterize it in Reporting Services, I don't get any rows returned.

I have created 2 parameters (StartWeek + EndWeek) which are both integers, and have used these in 2 calculated fields. The code for these is:

ThisYear

Sum(
[Dim Week No].[Week No].&[@.StartWeek]
:
[Dim Week No].[Week No].&[@.EndWeek]
, [Measures].[Rate]
)

LastYear

I have then just added these 2 fields, and the product field to my report's dataset. Could somebody please tell me where I am going wrong as this is driving me crazy!!

Dave

|||

Ok,

I managed to get the MDX query working in SQL Studio but when I try to parameterize it in Reporting Services, I don't get any rows returned.

I have created 2 parameters (StartWeek + EndWeek) which are both integers, and have used these in 2 calculated fields. The code for these is:

ThisYear

Sum(
[Dim Week No].[Week No].&[@.StartWeek]
:
[Dim Week No].[Week No].&[@.EndWeek]
, [Measures].[Rate]
)

LastYear

I have then just added these 2 fields, and the product field to my report's dataset. Could somebody please tell me where I am going wrong as this is driving me crazy!!

Dave

|||

Ok,

I managed to get the MDX query working in SQL Studio but when I try to parameterize it in Reporting Services, I don't get any rows returned.

I have created 2 parameters (StartWeek + EndWeek) which are both integers, and have used these in 2 calculated fields. The code for these is:

ThisYear

Sum(
[Dim Week No].[Week No].&[@.StartWeek]
:
[Dim Week No].[Week No].&[@.EndWeek]
, [Measures].[Rate]
)

LastYear

I have then just added these 2 fields, and the product field to my report's dataset. Could somebody please tell me where I am going wrong as this is driving me crazy!!

Dave

|||

Try constructing a MemberUniqueName string and passing that to the StrToMember functiuon

Sum(
StrToMember("[Dim Week No].[Week No].&[" + CStr(@.StartWeek) + "] ", CONSTRAINED)
:
StrToMember("[Dim Week No].[Week No].&[" + CStr(@.EndWeek) + "] ", CONSTRAINED)
, [Measures].[Rate]
)

|||

Hi Adam,

Again, that works fine in SQL Studio with integer values instead of the parameters. However, when I try it in Reporting Services with my parameters I get the following error:

"The 'ThisYear' calculated member cannot be created because of the following error: Parser: The StartWeek parameter could not be resolved because it was referenced in ann inner subexpression"

My calculated member field is now:

|||

Some options:

try removing the CStr() call and see if that works
ORsql

Problems creating mirroring on 2005.

What about the security configuration? Have you granted connect permissions
to the other servers' accounts to the database mirroring endpoints?
You should use
grant connect on endpoint::endpoint_mirroring to [domain\account]
Ben Nevarez, MCDBA, OCP
Database Administrator
"ChrisR" wrote:

> SQL2K5 sp1
> Instance names:
> Development\MySQL2K5
> Development\Subscriber
> Development\Witness
> Im trying to set up mirroring on 3 instances on my desktop and having some
> issues (not ideal I know, but it's what I've got to work with for now). He
re
> is what I've done:
> --Create endpoint on principal instance and set recovery model
> CREATE ENDPOINT endpoint_mirroring
> STATE = STARTED
> AS TCP ( LISTENER_PORT = 5022 )
> FOR DATABASE_MIRRORING (ROLE=PARTNER);
> GO
> ALTER DATABASE AdventureWorks
> SET RECOVERY FULL;
> --Create endpoint on mirror instance
> CREATE ENDPOINT endpoint_mirroring
> STATE = STARTED
> AS TCP ( LISTENER_PORT = 5023 )
> FOR DATABASE_MIRRORING (ROLE=PARTNER);
> GO
> --Create endpoint on witness instance
> CREATE ENDPOINT endpoint_mirroring
> STATE = STARTED
> AS TCP ( LISTENER_PORT = 5024 )
> FOR DATABASE_MIRRORING (ROLE=WITNESS);
> GO
> --Backup principal database
> BACKUP DATABASE AdventureWorks
> TO DISK='C:\MirrorBackup\AWBackup.BAK'
> with init;
> GO
> --Restore mirror database
> RESTORE DATABASE AdventureWorks
> FROM DISK='C:\MirrorBackup\AWBackup.BAK'
> WITH NORECOVERY,
> MOVE 'AdventureWorks_Data' TO 'C:\Program Files\Microsoft SQL
> Server\MSSQL.2\MSSQL\DATA\AdventureWorks_Data.mdf',
> MOVE 'AdventureWorks_Log' TO 'C:\Program Files\Microsoft SQL
> Server\MSSQL.2\MSSQL\DATA\AdventureWorks_Log.ldf';
> GO
> --On the mirror server set the principal server
> ALTER DATABASE AdventureWorks
> SET PARTNER =
> 'TCP://MySQL2K5:5022'
> GO
> --On the principal server set the mirror server
> ALTER DATABASE AdventureWorks
> SET PARTNER = 'TCP://MySQL2K5:5023'
> GO
> That last step blows up with:
> Msg 1418, Level 16, State 1, Line 1
> The server network address "TCP://MYSQL2K5:5023" can not be reached or doe
s
> not exist. Check the network address name and that the ports for the local
> and remote endpoints are operational.
> Any ideas?
> TIA, ChrisR
>
>That didn't fix it. As an FYI all 3 instances are running under the same
domain account.
"Ben Nevarez" <bnevarez@.sjm.com> wrote in message
news:D8493E83-C7A3-4A76-9302-7FA2C77EE3EF@.microsoft.com...
> What about the security configuration? Have you granted connect
permissions[vbcol=seagreen]
> to the other servers' accounts to the database mirroring endpoints?
> You should use
> grant connect on endpoint::endpoint_mirroring to [domain\account]
> Ben Nevarez, MCDBA, OCP
> Database Administrator
>
> "ChrisR" wrote:
>
some[vbcol=seagreen]
Here[vbcol=seagreen]
does[vbcol=seagreen]
local[vbcol=seagreen]|||SQL2K5 sp1
Instance names:
Development\MySQL2K5
Development\Subscriber
Development\Witness
Im trying to set up mirroring on 3 instances on my desktop and having some
issues (not ideal I know, but it's what I've got to work with for now). Here
is what I've done:
--Create endpoint on principal instance and set recovery model
CREATE ENDPOINT endpoint_mirroring
STATE = STARTED
AS TCP ( LISTENER_PORT = 5022 )
FOR DATABASE_MIRRORING (ROLE=PARTNER);
GO
ALTER DATABASE AdventureWorks
SET RECOVERY FULL;
--Create endpoint on mirror instance
CREATE ENDPOINT endpoint_mirroring
STATE = STARTED
AS TCP ( LISTENER_PORT = 5023 )
FOR DATABASE_MIRRORING (ROLE=PARTNER);
GO
--Create endpoint on witness instance
CREATE ENDPOINT endpoint_mirroring
STATE = STARTED
AS TCP ( LISTENER_PORT = 5024 )
FOR DATABASE_MIRRORING (ROLE=WITNESS);
GO
--Backup principal database
BACKUP DATABASE AdventureWorks
TO DISK='C:\MirrorBackup\AWBackup.BAK'
with init;
GO
--Restore mirror database
RESTORE DATABASE AdventureWorks
FROM DISK='C:\MirrorBackup\AWBackup.BAK'
WITH NORECOVERY,
MOVE 'AdventureWorks_Data' TO 'C:\Program Files\Microsoft SQL
Server\MSSQL.2\MSSQL\DATA\AdventureWorks_Data.mdf',
MOVE 'AdventureWorks_Log' TO 'C:\Program Files\Microsoft SQL
Server\MSSQL.2\MSSQL\DATA\AdventureWorks_Log.ldf';
GO
--On the mirror server set the principal server
ALTER DATABASE AdventureWorks
SET PARTNER =
'TCP://MySQL2K5:5022'
GO
--On the principal server set the mirror server
ALTER DATABASE AdventureWorks
SET PARTNER = 'TCP://MySQL2K5:5023'
GO
That last step blows up with:
Msg 1418, Level 16, State 1, Line 1
The server network address "TCP://MYSQL2K5:5023" can not be reached or does
not exist. Check the network address name and that the ports for the local
and remote endpoints are operational.
Any ideas?
TIA, ChrisR|||What about the security configuration? Have you granted connect permissions
to the other servers' accounts to the database mirroring endpoints?
You should use
grant connect on endpoint::endpoint_mirroring to [domain\account]
Ben Nevarez, MCDBA, OCP
Database Administrator
"ChrisR" wrote:

> SQL2K5 sp1
> Instance names:
> Development\MySQL2K5
> Development\Subscriber
> Development\Witness
> Im trying to set up mirroring on 3 instances on my desktop and having some
> issues (not ideal I know, but it's what I've got to work with for now). He
re
> is what I've done:
> --Create endpoint on principal instance and set recovery model
> CREATE ENDPOINT endpoint_mirroring
> STATE = STARTED
> AS TCP ( LISTENER_PORT = 5022 )
> FOR DATABASE_MIRRORING (ROLE=PARTNER);
> GO
> ALTER DATABASE AdventureWorks
> SET RECOVERY FULL;
> --Create endpoint on mirror instance
> CREATE ENDPOINT endpoint_mirroring
> STATE = STARTED
> AS TCP ( LISTENER_PORT = 5023 )
> FOR DATABASE_MIRRORING (ROLE=PARTNER);
> GO
> --Create endpoint on witness instance
> CREATE ENDPOINT endpoint_mirroring
> STATE = STARTED
> AS TCP ( LISTENER_PORT = 5024 )
> FOR DATABASE_MIRRORING (ROLE=WITNESS);
> GO
> --Backup principal database
> BACKUP DATABASE AdventureWorks
> TO DISK='C:\MirrorBackup\AWBackup.BAK'
> with init;
> GO
> --Restore mirror database
> RESTORE DATABASE AdventureWorks
> FROM DISK='C:\MirrorBackup\AWBackup.BAK'
> WITH NORECOVERY,
> MOVE 'AdventureWorks_Data' TO 'C:\Program Files\Microsoft SQL
> Server\MSSQL.2\MSSQL\DATA\AdventureWorks_Data.mdf',
> MOVE 'AdventureWorks_Log' TO 'C:\Program Files\Microsoft SQL
> Server\MSSQL.2\MSSQL\DATA\AdventureWorks_Log.ldf';
> GO
> --On the mirror server set the principal server
> ALTER DATABASE AdventureWorks
> SET PARTNER =
> 'TCP://MySQL2K5:5022'
> GO
> --On the principal server set the mirror server
> ALTER DATABASE AdventureWorks
> SET PARTNER = 'TCP://MySQL2K5:5023'
> GO
> That last step blows up with:
> Msg 1418, Level 16, State 1, Line 1
> The server network address "TCP://MYSQL2K5:5023" can not be reached or doe
s
> not exist. Check the network address name and that the ports for the local
> and remote endpoints are operational.
> Any ideas?
> TIA, ChrisR
>
>|||That didn't fix it. As an FYI all 3 instances are running under the same
domain account.
"Ben Nevarez" <bnevarez@.sjm.com> wrote in message
news:D8493E83-C7A3-4A76-9302-7FA2C77EE3EF@.microsoft.com...
> What about the security configuration? Have you granted connect
permissions[vbcol=seagreen]
> to the other servers' accounts to the database mirroring endpoints?
> You should use
> grant connect on endpoint::endpoint_mirroring to [domain\account]
> Ben Nevarez, MCDBA, OCP
> Database Administrator
>
> "ChrisR" wrote:
>
some[vbcol=seagreen]
Here[vbcol=seagreen]
does[vbcol=seagreen]
local[vbcol=seagreen]|||Let us try something else then.
Is MYSQL2K5 the name of your computer?
BOL recommends to use the fully qualified domain name. Try ipconfig /all and
look for host name and primary dns suffix.
Ben Nevarez, MCDBA, OCP
Database Administrator
"ChrisR" wrote:

> That didn't fix it. As an FYI all 3 instances are running under the same
> domain account.
> "Ben Nevarez" <bnevarez@.sjm.com> wrote in message
> news:D8493E83-C7A3-4A76-9302-7FA2C77EE3EF@.microsoft.com...
> permissions
> some
> Here
> does
> local
>
>|||The box name is Development. The instance name is MySQL2K5. I have tried
development\mysql2k5 and many variations of it and get incorrect syntax
errors. I also tried the FQDN and still got the original error.
Thanks for all the help.
"Ben Nevarez" <bnevarez@.sjm.com> wrote in message
news:2F186919-F424-4707-9320-68474A3A59C1@.microsoft.com...
> Let us try something else then.
> Is MYSQL2K5 the name of your computer?
> BOL recommends to use the fully qualified domain name. Try ipconfig /all
and[vbcol=seagreen]
> look for host name and primary dns suffix.
> Ben Nevarez, MCDBA, OCP
> Database Administrator
>
> "ChrisR" wrote:
>
having[vbcol=seagreen]
now).[vbcol=seagreen]
or[vbcol=seagreen]|||On
ALTER DATABASE AdventureWorks
you need to specify your host name and domain name NOT the SQL Server
instance name. For example, development.yourdomain.com.
Run ipconfig /all and look into host name and primary dns suffix.
Ben Nevarez, MCDBA, OCP
Database Administrator
"ChrisR" wrote:
[vbcol=seagreen]
> The box name is Development. The instance name is MySQL2K5. I have tried
> development\mysql2k5 and many variations of it and get incorrect syntax
> errors. I also tried the FQDN and still got the original error.
> Thanks for all the help.
> "Ben Nevarez" <bnevarez@.sjm.com> wrote in message
> news:2F186919-F424-4707-9320-68474A3A59C1@.microsoft.com...
> and
> having
> now).
> or
>
>|||The box name is Development. The instance name is MySQL2K5. I have tried
development\mysql2k5 and many variations of it and get incorrect syntax
errors. I also tried the FQDN and still got the original error.
Thanks for all the help.
"Ben Nevarez" <bnevarez@.sjm.com> wrote in message
news:2F186919-F424-4707-9320-68474A3A59C1@.microsoft.com...
> Let us try something else then.
> Is MYSQL2K5 the name of your computer?
> BOL recommends to use the fully qualified domain name. Try ipconfig /all
and[vbcol=seagreen]
> look for host name and primary dns suffix.
> Ben Nevarez, MCDBA, OCP
> Database Administrator
>
> "ChrisR" wrote:
>
having[vbcol=seagreen]
now).[vbcol=seagreen]
or[vbcol=seagreen]|||On
ALTER DATABASE AdventureWorks
you need to specify your host name and domain name NOT the SQL Server
instance name. For example, development.yourdomain.com.
Run ipconfig /all and look into host name and primary dns suffix.
Ben Nevarez, MCDBA, OCP
Database Administrator
"ChrisR" wrote:
[vbcol=seagreen]
> The box name is Development. The instance name is MySQL2K5. I have tried
> development\mysql2k5 and many variations of it and get incorrect syntax
> errors. I also tried the FQDN and still got the original error.
> Thanks for all the help.
> "Ben Nevarez" <bnevarez@.sjm.com> wrote in message
> news:2F186919-F424-4707-9320-68474A3A59C1@.microsoft.com...
> and
> having
> now).
> or
>
>

Problems creating mirroring on 2005.

SQL2K5 sp1
Instance names:
Development\MySQL2K5
Development\Subscriber
Development\Witness
Im trying to set up mirroring on 3 instances on my desktop and having some
issues (not ideal I know, but it's what I've got to work with for now). Here
is what I've done:
--Create endpoint on principal instance and set recovery model
CREATE ENDPOINT endpoint_mirroring
STATE = STARTED
AS TCP ( LISTENER_PORT = 5022 )
FOR DATABASE_MIRRORING (ROLE=PARTNER);
GO
ALTER DATABASE AdventureWorks
SET RECOVERY FULL;
--Create endpoint on mirror instance
CREATE ENDPOINT endpoint_mirroring
STATE = STARTED
AS TCP ( LISTENER_PORT = 5023 )
FOR DATABASE_MIRRORING (ROLE=PARTNER);
GO
--Create endpoint on witness instance
CREATE ENDPOINT endpoint_mirroring
STATE = STARTED
AS TCP ( LISTENER_PORT = 5024 )
FOR DATABASE_MIRRORING (ROLE=WITNESS);
GO
--Backup principal database
BACKUP DATABASE AdventureWorks
TO DISK='C:\MirrorBackup\AWBackup.BAK'
with init;
GO
--Restore mirror database
RESTORE DATABASE AdventureWorks
FROM DISK='C:\MirrorBackup\AWBackup.BAK'
WITH NORECOVERY,
MOVE 'AdventureWorks_Data' TO 'C:\Program Files\Microsoft SQL
Server\MSSQL.2\MSSQL\DATA\AdventureWorks_Data.mdf',
MOVE 'AdventureWorks_Log' TO 'C:\Program Files\Microsoft SQL
Server\MSSQL.2\MSSQL\DATA\AdventureWorks_Log.ldf';
GO
--On the mirror server set the principal server
ALTER DATABASE AdventureWorks
SET PARTNER = 'TCP://MySQL2K5:5022'
GO
--On the principal server set the mirror server
ALTER DATABASE AdventureWorks
SET PARTNER = 'TCP://MySQL2K5:5023'
GO
That last step blows up with:
Msg 1418, Level 16, State 1, Line 1
The server network address "TCP://MYSQL2K5:5023" can not be reached or does
not exist. Check the network address name and that the ports for the local
and remote endpoints are operational.
Any ideas?
TIA, ChrisRWhat about the security configuration? Have you granted connect permissions
to the other servers' accounts to the database mirroring endpoints?
You should use
grant connect on endpoint::endpoint_mirroring to [domain\account]
Ben Nevarez, MCDBA, OCP
Database Administrator
"ChrisR" wrote:
> SQL2K5 sp1
> Instance names:
> Development\MySQL2K5
> Development\Subscriber
> Development\Witness
> Im trying to set up mirroring on 3 instances on my desktop and having some
> issues (not ideal I know, but it's what I've got to work with for now). Here
> is what I've done:
> --Create endpoint on principal instance and set recovery model
> CREATE ENDPOINT endpoint_mirroring
> STATE = STARTED
> AS TCP ( LISTENER_PORT = 5022 )
> FOR DATABASE_MIRRORING (ROLE=PARTNER);
> GO
> ALTER DATABASE AdventureWorks
> SET RECOVERY FULL;
> --Create endpoint on mirror instance
> CREATE ENDPOINT endpoint_mirroring
> STATE = STARTED
> AS TCP ( LISTENER_PORT = 5023 )
> FOR DATABASE_MIRRORING (ROLE=PARTNER);
> GO
> --Create endpoint on witness instance
> CREATE ENDPOINT endpoint_mirroring
> STATE = STARTED
> AS TCP ( LISTENER_PORT = 5024 )
> FOR DATABASE_MIRRORING (ROLE=WITNESS);
> GO
> --Backup principal database
> BACKUP DATABASE AdventureWorks
> TO DISK='C:\MirrorBackup\AWBackup.BAK'
> with init;
> GO
> --Restore mirror database
> RESTORE DATABASE AdventureWorks
> FROM DISK='C:\MirrorBackup\AWBackup.BAK'
> WITH NORECOVERY,
> MOVE 'AdventureWorks_Data' TO 'C:\Program Files\Microsoft SQL
> Server\MSSQL.2\MSSQL\DATA\AdventureWorks_Data.mdf',
> MOVE 'AdventureWorks_Log' TO 'C:\Program Files\Microsoft SQL
> Server\MSSQL.2\MSSQL\DATA\AdventureWorks_Log.ldf';
> GO
> --On the mirror server set the principal server
> ALTER DATABASE AdventureWorks
> SET PARTNER => 'TCP://MySQL2K5:5022'
> GO
> --On the principal server set the mirror server
> ALTER DATABASE AdventureWorks
> SET PARTNER = 'TCP://MySQL2K5:5023'
> GO
> That last step blows up with:
> Msg 1418, Level 16, State 1, Line 1
> The server network address "TCP://MYSQL2K5:5023" can not be reached or does
> not exist. Check the network address name and that the ports for the local
> and remote endpoints are operational.
> Any ideas?
> TIA, ChrisR
>
>|||That didn't fix it. As an FYI all 3 instances are running under the same
domain account.
"Ben Nevarez" <bnevarez@.sjm.com> wrote in message
news:D8493E83-C7A3-4A76-9302-7FA2C77EE3EF@.microsoft.com...
> What about the security configuration? Have you granted connect
permissions
> to the other servers' accounts to the database mirroring endpoints?
> You should use
> grant connect on endpoint::endpoint_mirroring to [domain\account]
> Ben Nevarez, MCDBA, OCP
> Database Administrator
>
> "ChrisR" wrote:
> > SQL2K5 sp1
> >
> > Instance names:
> >
> > Development\MySQL2K5
> > Development\Subscriber
> > Development\Witness
> >
> > Im trying to set up mirroring on 3 instances on my desktop and having
some
> > issues (not ideal I know, but it's what I've got to work with for now).
Here
> > is what I've done:
> >
> > --Create endpoint on principal instance and set recovery model
> > CREATE ENDPOINT endpoint_mirroring
> > STATE = STARTED
> > AS TCP ( LISTENER_PORT = 5022 )
> > FOR DATABASE_MIRRORING (ROLE=PARTNER);
> > GO
> > ALTER DATABASE AdventureWorks
> > SET RECOVERY FULL;
> > --Create endpoint on mirror instance
> > CREATE ENDPOINT endpoint_mirroring
> > STATE = STARTED
> > AS TCP ( LISTENER_PORT = 5023 )
> > FOR DATABASE_MIRRORING (ROLE=PARTNER);
> > GO
> > --Create endpoint on witness instance
> > CREATE ENDPOINT endpoint_mirroring
> > STATE = STARTED
> > AS TCP ( LISTENER_PORT = 5024 )
> > FOR DATABASE_MIRRORING (ROLE=WITNESS);
> > GO
> > --Backup principal database
> > BACKUP DATABASE AdventureWorks
> > TO DISK='C:\MirrorBackup\AWBackup.BAK'
> > with init;
> > GO
> > --Restore mirror database
> > RESTORE DATABASE AdventureWorks
> > FROM DISK='C:\MirrorBackup\AWBackup.BAK'
> > WITH NORECOVERY,
> > MOVE 'AdventureWorks_Data' TO 'C:\Program Files\Microsoft SQL
> > Server\MSSQL.2\MSSQL\DATA\AdventureWorks_Data.mdf',
> > MOVE 'AdventureWorks_Log' TO 'C:\Program Files\Microsoft SQL
> > Server\MSSQL.2\MSSQL\DATA\AdventureWorks_Log.ldf';
> > GO
> > --On the mirror server set the principal server
> > ALTER DATABASE AdventureWorks
> > SET PARTNER => > 'TCP://MySQL2K5:5022'
> > GO
> > --On the principal server set the mirror server
> > ALTER DATABASE AdventureWorks
> > SET PARTNER = 'TCP://MySQL2K5:5023'
> > GO
> >
> > That last step blows up with:
> > Msg 1418, Level 16, State 1, Line 1
> > The server network address "TCP://MYSQL2K5:5023" can not be reached or
does
> > not exist. Check the network address name and that the ports for the
local
> > and remote endpoints are operational.
> >
> > Any ideas?
> > TIA, ChrisR
> >
> >
> >|||Let us try something else then.
Is MYSQL2K5 the name of your computer?
BOL recommends to use the fully qualified domain name. Try ipconfig /all and
look for host name and primary dns suffix.
Ben Nevarez, MCDBA, OCP
Database Administrator
"ChrisR" wrote:
> That didn't fix it. As an FYI all 3 instances are running under the same
> domain account.
> "Ben Nevarez" <bnevarez@.sjm.com> wrote in message
> news:D8493E83-C7A3-4A76-9302-7FA2C77EE3EF@.microsoft.com...
> >
> > What about the security configuration? Have you granted connect
> permissions
> > to the other servers' accounts to the database mirroring endpoints?
> >
> > You should use
> >
> > grant connect on endpoint::endpoint_mirroring to [domain\account]
> >
> > Ben Nevarez, MCDBA, OCP
> > Database Administrator
> >
> >
> > "ChrisR" wrote:
> >
> > > SQL2K5 sp1
> > >
> > > Instance names:
> > >
> > > Development\MySQL2K5
> > > Development\Subscriber
> > > Development\Witness
> > >
> > > Im trying to set up mirroring on 3 instances on my desktop and having
> some
> > > issues (not ideal I know, but it's what I've got to work with for now).
> Here
> > > is what I've done:
> > >
> > > --Create endpoint on principal instance and set recovery model
> > > CREATE ENDPOINT endpoint_mirroring
> > > STATE = STARTED
> > > AS TCP ( LISTENER_PORT = 5022 )
> > > FOR DATABASE_MIRRORING (ROLE=PARTNER);
> > > GO
> > > ALTER DATABASE AdventureWorks
> > > SET RECOVERY FULL;
> > > --Create endpoint on mirror instance
> > > CREATE ENDPOINT endpoint_mirroring
> > > STATE = STARTED
> > > AS TCP ( LISTENER_PORT = 5023 )
> > > FOR DATABASE_MIRRORING (ROLE=PARTNER);
> > > GO
> > > --Create endpoint on witness instance
> > > CREATE ENDPOINT endpoint_mirroring
> > > STATE = STARTED
> > > AS TCP ( LISTENER_PORT = 5024 )
> > > FOR DATABASE_MIRRORING (ROLE=WITNESS);
> > > GO
> > > --Backup principal database
> > > BACKUP DATABASE AdventureWorks
> > > TO DISK='C:\MirrorBackup\AWBackup.BAK'
> > > with init;
> > > GO
> > > --Restore mirror database
> > > RESTORE DATABASE AdventureWorks
> > > FROM DISK='C:\MirrorBackup\AWBackup.BAK'
> > > WITH NORECOVERY,
> > > MOVE 'AdventureWorks_Data' TO 'C:\Program Files\Microsoft SQL
> > > Server\MSSQL.2\MSSQL\DATA\AdventureWorks_Data.mdf',
> > > MOVE 'AdventureWorks_Log' TO 'C:\Program Files\Microsoft SQL
> > > Server\MSSQL.2\MSSQL\DATA\AdventureWorks_Log.ldf';
> > > GO
> > > --On the mirror server set the principal server
> > > ALTER DATABASE AdventureWorks
> > > SET PARTNER => > > 'TCP://MySQL2K5:5022'
> > > GO
> > > --On the principal server set the mirror server
> > > ALTER DATABASE AdventureWorks
> > > SET PARTNER = 'TCP://MySQL2K5:5023'
> > > GO
> > >
> > > That last step blows up with:
> > > Msg 1418, Level 16, State 1, Line 1
> > > The server network address "TCP://MYSQL2K5:5023" can not be reached or
> does
> > > not exist. Check the network address name and that the ports for the
> local
> > > and remote endpoints are operational.
> > >
> > > Any ideas?
> > > TIA, ChrisR
> > >
> > >
> > >
>
>|||The box name is Development. The instance name is MySQL2K5. I have tried
development\mysql2k5 and many variations of it and get incorrect syntax
errors. I also tried the FQDN and still got the original error.
Thanks for all the help.
"Ben Nevarez" <bnevarez@.sjm.com> wrote in message
news:2F186919-F424-4707-9320-68474A3A59C1@.microsoft.com...
> Let us try something else then.
> Is MYSQL2K5 the name of your computer?
> BOL recommends to use the fully qualified domain name. Try ipconfig /all
and
> look for host name and primary dns suffix.
> Ben Nevarez, MCDBA, OCP
> Database Administrator
>
> "ChrisR" wrote:
> > That didn't fix it. As an FYI all 3 instances are running under the same
> > domain account.
> >
> > "Ben Nevarez" <bnevarez@.sjm.com> wrote in message
> > news:D8493E83-C7A3-4A76-9302-7FA2C77EE3EF@.microsoft.com...
> > >
> > > What about the security configuration? Have you granted connect
> > permissions
> > > to the other servers' accounts to the database mirroring endpoints?
> > >
> > > You should use
> > >
> > > grant connect on endpoint::endpoint_mirroring to [domain\account]
> > >
> > > Ben Nevarez, MCDBA, OCP
> > > Database Administrator
> > >
> > >
> > > "ChrisR" wrote:
> > >
> > > > SQL2K5 sp1
> > > >
> > > > Instance names:
> > > >
> > > > Development\MySQL2K5
> > > > Development\Subscriber
> > > > Development\Witness
> > > >
> > > > Im trying to set up mirroring on 3 instances on my desktop and
having
> > some
> > > > issues (not ideal I know, but it's what I've got to work with for
now).
> > Here
> > > > is what I've done:
> > > >
> > > > --Create endpoint on principal instance and set recovery model
> > > > CREATE ENDPOINT endpoint_mirroring
> > > > STATE = STARTED
> > > > AS TCP ( LISTENER_PORT = 5022 )
> > > > FOR DATABASE_MIRRORING (ROLE=PARTNER);
> > > > GO
> > > > ALTER DATABASE AdventureWorks
> > > > SET RECOVERY FULL;
> > > > --Create endpoint on mirror instance
> > > > CREATE ENDPOINT endpoint_mirroring
> > > > STATE = STARTED
> > > > AS TCP ( LISTENER_PORT = 5023 )
> > > > FOR DATABASE_MIRRORING (ROLE=PARTNER);
> > > > GO
> > > > --Create endpoint on witness instance
> > > > CREATE ENDPOINT endpoint_mirroring
> > > > STATE = STARTED
> > > > AS TCP ( LISTENER_PORT = 5024 )
> > > > FOR DATABASE_MIRRORING (ROLE=WITNESS);
> > > > GO
> > > > --Backup principal database
> > > > BACKUP DATABASE AdventureWorks
> > > > TO DISK='C:\MirrorBackup\AWBackup.BAK'
> > > > with init;
> > > > GO
> > > > --Restore mirror database
> > > > RESTORE DATABASE AdventureWorks
> > > > FROM DISK='C:\MirrorBackup\AWBackup.BAK'
> > > > WITH NORECOVERY,
> > > > MOVE 'AdventureWorks_Data' TO 'C:\Program Files\Microsoft SQL
> > > > Server\MSSQL.2\MSSQL\DATA\AdventureWorks_Data.mdf',
> > > > MOVE 'AdventureWorks_Log' TO 'C:\Program Files\Microsoft SQL
> > > > Server\MSSQL.2\MSSQL\DATA\AdventureWorks_Log.ldf';
> > > > GO
> > > > --On the mirror server set the principal server
> > > > ALTER DATABASE AdventureWorks
> > > > SET PARTNER => > > > 'TCP://MySQL2K5:5022'
> > > > GO
> > > > --On the principal server set the mirror server
> > > > ALTER DATABASE AdventureWorks
> > > > SET PARTNER = 'TCP://MySQL2K5:5023'
> > > > GO
> > > >
> > > > That last step blows up with:
> > > > Msg 1418, Level 16, State 1, Line 1
> > > > The server network address "TCP://MYSQL2K5:5023" can not be reached
or
> > does
> > > > not exist. Check the network address name and that the ports for the
> > local
> > > > and remote endpoints are operational.
> > > >
> > > > Any ideas?
> > > > TIA, ChrisR
> > > >
> > > >
> > > >
> >
> >
> >|||On
ALTER DATABASE AdventureWorks
> > > > > SET PARTNER => > > > > 'TCP://MySQL2K5:5022'
you need to specify your host name and domain name NOT the SQL Server
instance name. For example, development.yourdomain.com.
Run ipconfig /all and look into host name and primary dns suffix.
Ben Nevarez, MCDBA, OCP
Database Administrator
"ChrisR" wrote:
> The box name is Development. The instance name is MySQL2K5. I have tried
> development\mysql2k5 and many variations of it and get incorrect syntax
> errors. I also tried the FQDN and still got the original error.
> Thanks for all the help.
> "Ben Nevarez" <bnevarez@.sjm.com> wrote in message
> news:2F186919-F424-4707-9320-68474A3A59C1@.microsoft.com...
> >
> > Let us try something else then.
> >
> > Is MYSQL2K5 the name of your computer?
> >
> > BOL recommends to use the fully qualified domain name. Try ipconfig /all
> and
> > look for host name and primary dns suffix.
> >
> > Ben Nevarez, MCDBA, OCP
> > Database Administrator
> >
> >
> > "ChrisR" wrote:
> >
> > > That didn't fix it. As an FYI all 3 instances are running under the same
> > > domain account.
> > >
> > > "Ben Nevarez" <bnevarez@.sjm.com> wrote in message
> > > news:D8493E83-C7A3-4A76-9302-7FA2C77EE3EF@.microsoft.com...
> > > >
> > > > What about the security configuration? Have you granted connect
> > > permissions
> > > > to the other servers' accounts to the database mirroring endpoints?
> > > >
> > > > You should use
> > > >
> > > > grant connect on endpoint::endpoint_mirroring to [domain\account]
> > > >
> > > > Ben Nevarez, MCDBA, OCP
> > > > Database Administrator
> > > >
> > > >
> > > > "ChrisR" wrote:
> > > >
> > > > > SQL2K5 sp1
> > > > >
> > > > > Instance names:
> > > > >
> > > > > Development\MySQL2K5
> > > > > Development\Subscriber
> > > > > Development\Witness
> > > > >
> > > > > Im trying to set up mirroring on 3 instances on my desktop and
> having
> > > some
> > > > > issues (not ideal I know, but it's what I've got to work with for
> now).
> > > Here
> > > > > is what I've done:
> > > > >
> > > > > --Create endpoint on principal instance and set recovery model
> > > > > CREATE ENDPOINT endpoint_mirroring
> > > > > STATE = STARTED
> > > > > AS TCP ( LISTENER_PORT = 5022 )
> > > > > FOR DATABASE_MIRRORING (ROLE=PARTNER);
> > > > > GO
> > > > > ALTER DATABASE AdventureWorks
> > > > > SET RECOVERY FULL;
> > > > > --Create endpoint on mirror instance
> > > > > CREATE ENDPOINT endpoint_mirroring
> > > > > STATE = STARTED
> > > > > AS TCP ( LISTENER_PORT = 5023 )
> > > > > FOR DATABASE_MIRRORING (ROLE=PARTNER);
> > > > > GO
> > > > > --Create endpoint on witness instance
> > > > > CREATE ENDPOINT endpoint_mirroring
> > > > > STATE = STARTED
> > > > > AS TCP ( LISTENER_PORT = 5024 )
> > > > > FOR DATABASE_MIRRORING (ROLE=WITNESS);
> > > > > GO
> > > > > --Backup principal database
> > > > > BACKUP DATABASE AdventureWorks
> > > > > TO DISK='C:\MirrorBackup\AWBackup.BAK'
> > > > > with init;
> > > > > GO
> > > > > --Restore mirror database
> > > > > RESTORE DATABASE AdventureWorks
> > > > > FROM DISK='C:\MirrorBackup\AWBackup.BAK'
> > > > > WITH NORECOVERY,
> > > > > MOVE 'AdventureWorks_Data' TO 'C:\Program Files\Microsoft SQL
> > > > > Server\MSSQL.2\MSSQL\DATA\AdventureWorks_Data.mdf',
> > > > > MOVE 'AdventureWorks_Log' TO 'C:\Program Files\Microsoft SQL
> > > > > Server\MSSQL.2\MSSQL\DATA\AdventureWorks_Log.ldf';
> > > > > GO
> > > > > --On the mirror server set the principal server
> > > > > ALTER DATABASE AdventureWorks
> > > > > SET PARTNER => > > > > 'TCP://MySQL2K5:5022'
> > > > > GO
> > > > > --On the principal server set the mirror server
> > > > > ALTER DATABASE AdventureWorks
> > > > > SET PARTNER = 'TCP://MySQL2K5:5023'
> > > > > GO
> > > > >
> > > > > That last step blows up with:
> > > > > Msg 1418, Level 16, State 1, Line 1
> > > > > The server network address "TCP://MYSQL2K5:5023" can not be reached
> or
> > > does
> > > > > not exist. Check the network address name and that the ports for the
> > > local
> > > > > and remote endpoints are operational.
> > > > >
> > > > > Any ideas?
> > > > > TIA, ChrisR
> > > > >
> > > > >
> > > > >
> > >
> > >
> > >
>
>|||Doh! I thought it was instance name.
"Ben Nevarez" <bnevarez@.sjm.com> wrote in message
news:5B6FD5FC-E687-4B1F-A01B-57C0C265D5DE@.microsoft.com...
> On
> ALTER DATABASE AdventureWorks
> > > > > > SET PARTNER => > > > > > 'TCP://MySQL2K5:5022'
> you need to specify your host name and domain name NOT the SQL Server
> instance name. For example, development.yourdomain.com.
> Run ipconfig /all and look into host name and primary dns suffix.
> Ben Nevarez, MCDBA, OCP
> Database Administrator
>
> "ChrisR" wrote:
> > The box name is Development. The instance name is MySQL2K5. I have tried
> > development\mysql2k5 and many variations of it and get incorrect syntax
> > errors. I also tried the FQDN and still got the original error.
> >
> > Thanks for all the help.
> >
> > "Ben Nevarez" <bnevarez@.sjm.com> wrote in message
> > news:2F186919-F424-4707-9320-68474A3A59C1@.microsoft.com...
> > >
> > > Let us try something else then.
> > >
> > > Is MYSQL2K5 the name of your computer?
> > >
> > > BOL recommends to use the fully qualified domain name. Try ipconfig
/all
> > and
> > > look for host name and primary dns suffix.
> > >
> > > Ben Nevarez, MCDBA, OCP
> > > Database Administrator
> > >
> > >
> > > "ChrisR" wrote:
> > >
> > > > That didn't fix it. As an FYI all 3 instances are running under the
same
> > > > domain account.
> > > >
> > > > "Ben Nevarez" <bnevarez@.sjm.com> wrote in message
> > > > news:D8493E83-C7A3-4A76-9302-7FA2C77EE3EF@.microsoft.com...
> > > > >
> > > > > What about the security configuration? Have you granted connect
> > > > permissions
> > > > > to the other servers' accounts to the database mirroring
endpoints?
> > > > >
> > > > > You should use
> > > > >
> > > > > grant connect on endpoint::endpoint_mirroring to [domain\account]
> > > > >
> > > > > Ben Nevarez, MCDBA, OCP
> > > > > Database Administrator
> > > > >
> > > > >
> > > > > "ChrisR" wrote:
> > > > >
> > > > > > SQL2K5 sp1
> > > > > >
> > > > > > Instance names:
> > > > > >
> > > > > > Development\MySQL2K5
> > > > > > Development\Subscriber
> > > > > > Development\Witness
> > > > > >
> > > > > > Im trying to set up mirroring on 3 instances on my desktop and
> > having
> > > > some
> > > > > > issues (not ideal I know, but it's what I've got to work with
for
> > now).
> > > > Here
> > > > > > is what I've done:
> > > > > >
> > > > > > --Create endpoint on principal instance and set recovery model
> > > > > > CREATE ENDPOINT endpoint_mirroring
> > > > > > STATE = STARTED
> > > > > > AS TCP ( LISTENER_PORT = 5022 )
> > > > > > FOR DATABASE_MIRRORING (ROLE=PARTNER);
> > > > > > GO
> > > > > > ALTER DATABASE AdventureWorks
> > > > > > SET RECOVERY FULL;
> > > > > > --Create endpoint on mirror instance
> > > > > > CREATE ENDPOINT endpoint_mirroring
> > > > > > STATE = STARTED
> > > > > > AS TCP ( LISTENER_PORT = 5023 )
> > > > > > FOR DATABASE_MIRRORING (ROLE=PARTNER);
> > > > > > GO
> > > > > > --Create endpoint on witness instance
> > > > > > CREATE ENDPOINT endpoint_mirroring
> > > > > > STATE = STARTED
> > > > > > AS TCP ( LISTENER_PORT = 5024 )
> > > > > > FOR DATABASE_MIRRORING (ROLE=WITNESS);
> > > > > > GO
> > > > > > --Backup principal database
> > > > > > BACKUP DATABASE AdventureWorks
> > > > > > TO DISK='C:\MirrorBackup\AWBackup.BAK'
> > > > > > with init;
> > > > > > GO
> > > > > > --Restore mirror database
> > > > > > RESTORE DATABASE AdventureWorks
> > > > > > FROM DISK='C:\MirrorBackup\AWBackup.BAK'
> > > > > > WITH NORECOVERY,
> > > > > > MOVE 'AdventureWorks_Data' TO 'C:\Program Files\Microsoft SQL
> > > > > > Server\MSSQL.2\MSSQL\DATA\AdventureWorks_Data.mdf',
> > > > > > MOVE 'AdventureWorks_Log' TO 'C:\Program Files\Microsoft SQL
> > > > > > Server\MSSQL.2\MSSQL\DATA\AdventureWorks_Log.ldf';
> > > > > > GO
> > > > > > --On the mirror server set the principal server
> > > > > > ALTER DATABASE AdventureWorks
> > > > > > SET PARTNER => > > > > > 'TCP://MySQL2K5:5022'
> > > > > > GO
> > > > > > --On the principal server set the mirror server
> > > > > > ALTER DATABASE AdventureWorks
> > > > > > SET PARTNER = 'TCP://MySQL2K5:5023'
> > > > > > GO
> > > > > >
> > > > > > That last step blows up with:
> > > > > > Msg 1418, Level 16, State 1, Line 1
> > > > > > The server network address "TCP://MYSQL2K5:5023" can not be
reached
> > or
> > > > does
> > > > > > not exist. Check the network address name and that the ports for
the
> > > > local
> > > > > > and remote endpoints are operational.
> > > > > >
> > > > > > Any ideas?
> > > > > > TIA, ChrisR
> > > > > >
> > > > > >
> > > > > >
> > > >
> > > >
> > > >
> >
> >
> >|||Thanks a lot!
"Ben Nevarez" <bnevarez@.sjm.com> wrote in message
news:5B6FD5FC-E687-4B1F-A01B-57C0C265D5DE@.microsoft.com...
> On
> ALTER DATABASE AdventureWorks
> > > > > > SET PARTNER => > > > > > 'TCP://MySQL2K5:5022'
> you need to specify your host name and domain name NOT the SQL Server
> instance name. For example, development.yourdomain.com.
> Run ipconfig /all and look into host name and primary dns suffix.
> Ben Nevarez, MCDBA, OCP
> Database Administrator
>
> "ChrisR" wrote:
> > The box name is Development. The instance name is MySQL2K5. I have tried
> > development\mysql2k5 and many variations of it and get incorrect syntax
> > errors. I also tried the FQDN and still got the original error.
> >
> > Thanks for all the help.
> >
> > "Ben Nevarez" <bnevarez@.sjm.com> wrote in message
> > news:2F186919-F424-4707-9320-68474A3A59C1@.microsoft.com...
> > >
> > > Let us try something else then.
> > >
> > > Is MYSQL2K5 the name of your computer?
> > >
> > > BOL recommends to use the fully qualified domain name. Try ipconfig
/all
> > and
> > > look for host name and primary dns suffix.
> > >
> > > Ben Nevarez, MCDBA, OCP
> > > Database Administrator
> > >
> > >
> > > "ChrisR" wrote:
> > >
> > > > That didn't fix it. As an FYI all 3 instances are running under the
same
> > > > domain account.
> > > >
> > > > "Ben Nevarez" <bnevarez@.sjm.com> wrote in message
> > > > news:D8493E83-C7A3-4A76-9302-7FA2C77EE3EF@.microsoft.com...
> > > > >
> > > > > What about the security configuration? Have you granted connect
> > > > permissions
> > > > > to the other servers' accounts to the database mirroring
endpoints?
> > > > >
> > > > > You should use
> > > > >
> > > > > grant connect on endpoint::endpoint_mirroring to [domain\account]
> > > > >
> > > > > Ben Nevarez, MCDBA, OCP
> > > > > Database Administrator
> > > > >
> > > > >
> > > > > "ChrisR" wrote:
> > > > >
> > > > > > SQL2K5 sp1
> > > > > >
> > > > > > Instance names:
> > > > > >
> > > > > > Development\MySQL2K5
> > > > > > Development\Subscriber
> > > > > > Development\Witness
> > > > > >
> > > > > > Im trying to set up mirroring on 3 instances on my desktop and
> > having
> > > > some
> > > > > > issues (not ideal I know, but it's what I've got to work with
for
> > now).
> > > > Here
> > > > > > is what I've done:
> > > > > >
> > > > > > --Create endpoint on principal instance and set recovery model
> > > > > > CREATE ENDPOINT endpoint_mirroring
> > > > > > STATE = STARTED
> > > > > > AS TCP ( LISTENER_PORT = 5022 )
> > > > > > FOR DATABASE_MIRRORING (ROLE=PARTNER);
> > > > > > GO
> > > > > > ALTER DATABASE AdventureWorks
> > > > > > SET RECOVERY FULL;
> > > > > > --Create endpoint on mirror instance
> > > > > > CREATE ENDPOINT endpoint_mirroring
> > > > > > STATE = STARTED
> > > > > > AS TCP ( LISTENER_PORT = 5023 )
> > > > > > FOR DATABASE_MIRRORING (ROLE=PARTNER);
> > > > > > GO
> > > > > > --Create endpoint on witness instance
> > > > > > CREATE ENDPOINT endpoint_mirroring
> > > > > > STATE = STARTED
> > > > > > AS TCP ( LISTENER_PORT = 5024 )
> > > > > > FOR DATABASE_MIRRORING (ROLE=WITNESS);
> > > > > > GO
> > > > > > --Backup principal database
> > > > > > BACKUP DATABASE AdventureWorks
> > > > > > TO DISK='C:\MirrorBackup\AWBackup.BAK'
> > > > > > with init;
> > > > > > GO
> > > > > > --Restore mirror database
> > > > > > RESTORE DATABASE AdventureWorks
> > > > > > FROM DISK='C:\MirrorBackup\AWBackup.BAK'
> > > > > > WITH NORECOVERY,
> > > > > > MOVE 'AdventureWorks_Data' TO 'C:\Program Files\Microsoft SQL
> > > > > > Server\MSSQL.2\MSSQL\DATA\AdventureWorks_Data.mdf',
> > > > > > MOVE 'AdventureWorks_Log' TO 'C:\Program Files\Microsoft SQL
> > > > > > Server\MSSQL.2\MSSQL\DATA\AdventureWorks_Log.ldf';
> > > > > > GO
> > > > > > --On the mirror server set the principal server
> > > > > > ALTER DATABASE AdventureWorks
> > > > > > SET PARTNER => > > > > > 'TCP://MySQL2K5:5022'
> > > > > > GO
> > > > > > --On the principal server set the mirror server
> > > > > > ALTER DATABASE AdventureWorks
> > > > > > SET PARTNER = 'TCP://MySQL2K5:5023'
> > > > > > GO
> > > > > >
> > > > > > That last step blows up with:
> > > > > > Msg 1418, Level 16, State 1, Line 1
> > > > > > The server network address "TCP://MYSQL2K5:5023" can not be
reached
> > or
> > > > does
> > > > > > not exist. Check the network address name and that the ports for
the
> > > > local
> > > > > > and remote endpoints are operational.
> > > > > >
> > > > > > Any ideas?
> > > > > > TIA, ChrisR
> > > > > >
> > > > > >
> > > > > >
> > > >
> > > >
> > > >
> >
> >
> >

Wednesday, March 28, 2012

Problems in exporting constraints and keys

Problems in Creating Views in Sql

Problems in Creating Views in Sql

problems in creating report (parent child hierarchy)

Friday, March 23, 2012

Problems Creating TVF function

Problems creating subscription...

Problems creating second relationship to the same table

Problems Creating Report

Problems Creating Report

Problems Creating Report

Problems Creating Report

Problems creating mirroring on 2005.

Problems creating mirroring on 2005.

Sql Update command

Blog Archive

About Me