Mixing Types

Hello Group,
our company had a database on SQL Server 2000 Standard as the source for a
Report Server 2000 Standard. Well, the source databases had to be moved to a
brand new server. As it happened, the SQL Server 2000 on the new server is
Enterprise. Now, the report server will not work. We changed the connection
strings for the reports to point to the new server but no good. We just keep
getting "server not trusted".
Does the type of database server matter to the report server?
RichFirst, I assume that it is just the data that is an issue. Not the
reportserver database itself (where Report server keeps its metadata/object
caching).
My guess is that something else is going on with regards to the security. If
you are using integrated security there might be some issue with RS server
passing on credentials to
What I prefer to do is to have a read only SQL login. I use that for
credentials. I just use the windows domain login to determine who gets to
run what report.
It does not matter in the least where the data is coming from. I report off
of SQL Server standard, SQL Server Enterprise, Sybase, In-SQL (a real time
control historian).
Now, if you are not even having Report Manager come up (i.e. you never even
get where you can select the report to run) then it sounds like RS database
was moved. Again, you have a configuration/security problem going on.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Rich" <Rich@.discussions.microsoft.com> wrote in message
news:E44CB1AD-A386-40E2-BBE6-F1BB1872F817@.microsoft.com...
> Hello Group,
> our company had a database on SQL Server 2000 Standard as the source for a
> Report Server 2000 Standard. Well, the source databases had to be moved
> to a
> brand new server. As it happened, the SQL Server 2000 on the new server
> is
> Enterprise. Now, the report server will not work. We changed the
> connection
> strings for the reports to point to the new server but no good. We just
> keep
> getting "server not trusted".
> Does the type of database server matter to the report server?
> Rich|||Hello Bruce,
thanks...the Report Manger comes up just fine. It displays the folders and
reports and user just like always. I thought that if I changed the
Connection String in the Report Manager, this would be enough. I need to
come up with a stradigy as to how to narrow down the location of the problem.
Rich
"Bruce L-C [MVP]" wrote:
> First, I assume that it is just the data that is an issue. Not the
> reportserver database itself (where Report server keeps its metadata/object
> caching).
> My guess is that something else is going on with regards to the security. If
> you are using integrated security there might be some issue with RS server
> passing on credentials to
> What I prefer to do is to have a read only SQL login. I use that for
> credentials. I just use the windows domain login to determine who gets to
> run what report.
> It does not matter in the least where the data is coming from. I report off
> of SQL Server standard, SQL Server Enterprise, Sybase, In-SQL (a real time
> control historian).
> Now, if you are not even having Report Manager come up (i.e. you never even
> get where you can select the report to run) then it sounds like RS database
> was moved. Again, you have a configuration/security problem going on.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Rich" <Rich@.discussions.microsoft.com> wrote in message
> news:E44CB1AD-A386-40E2-BBE6-F1BB1872F817@.microsoft.com...
> > Hello Group,
> >
> > our company had a database on SQL Server 2000 Standard as the source for a
> > Report Server 2000 Standard. Well, the source databases had to be moved
> > to a
> > brand new server. As it happened, the SQL Server 2000 on the new server
> > is
> > Enterprise. Now, the report server will not work. We changed the
> > connection
> > strings for the reports to point to the new server but no good. We just
> > keep
> > getting "server not trusted".
> >
> > Does the type of database server matter to the report server?
> >
> > Rich
>
>sql

mixing SQL2K and SQL2K5

Has anyone had two instances with these two versions installed in Production
yet? Any gotchas? Any compelling reasons to not do this?
--
TIA,
ChrisRHi
Biggest issue would be that you can't restore the SQL Server 2005 database
in SQL Server 2000.
Otherwise, as long as you test your application that it works on SQL Server
2005, no issues.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
news:1ED99E08-D747-4E13-BB7B-005A742FD458@.microsoft.com...
> Has anyone had two instances with these two versions installed in
> Production
> yet? Any gotchas? Any compelling reasons to not do this?
> --
> TIA,
> ChrisR

mixing SQL2K and SQL2K5

Has anyone had two instances with these two versions installed in Production
yet? Any gotchas? Any compelling reasons to not do this?
TIA,
ChrisR
Hi
Biggest issue would be that you can't restore the SQL Server 2005 database
in SQL Server 2000.
Otherwise, as long as you test your application that it works on SQL Server
2005, no issues.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
news:1ED99E08-D747-4E13-BB7B-005A742FD458@.microsoft.com...
> Has anyone had two instances with these two versions installed in
> Production
> yet? Any gotchas? Any compelling reasons to not do this?
> --
> TIA,
> ChrisR

mixing SQL2K and SQL2K5

Has anyone had two instances with these two versions installed in Production
yet? Any gotchas? Any compelling reasons to not do this?
TIA,
ChrisRHi
Biggest issue would be that you can't restore the SQL Server 2005 database
in SQL Server 2000.
Otherwise, as long as you test your application that it works on SQL Server
2005, no issues.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
news:1ED99E08-D747-4E13-BB7B-005A742FD458@.microsoft.com...
> Has anyone had two instances with these two versions installed in
> Production
> yet? Any gotchas? Any compelling reasons to not do this?
> --
> TIA,
> ChrisR

Mixing SQL Server and MSDE and authentication

We install MSDE2000 under a named instance and with SQL Server
authentication, and specify a password for 'sa'.
I recently had someone install it on a system that already had SQL Server
installed.
On that system, we could not connect to the named instance using the "sa"
user and password specified in the installer. Using ODBC, we kept getting
the error 18452 - Login failed for user 'sa'.
How does the presence of SQL Server affect this? Does the 'sa' password for
the SQL Server default instance take precedence?
hi,
"JJ" <jjjj@.nospam.com> ha scritto nel messaggio
news:uAPJ05LGEHA.2732@.tk2msftngp13.phx.gbl...
> We install MSDE2000 under a named instance and with SQL Server
> authentication, and specify a password for 'sa'.
> I recently had someone install it on a system that already had SQL Server
> installed.
> On that system, we could not connect to the named instance using the "sa"
> user and password specified in the installer. Using ODBC, we kept getting
> the error 18452 - Login failed for user 'sa'.
> How does the presence of SQL Server affect this? Does the 'sa' password
for
> the SQL Server default instance take precedence?
>
it shoul'd really not...
all specified settings are instance specific, the only difference is that
the 1st instance (default) will listen to default TCP/IP port 1433, while
all other ubsequent instances will pass throught UDP port 1434 if they have
been set to dinamically retrieve the port number... but this another
story...
if you can reproduce the problem and are "sure" [ =;-) ] that the MSDE
instance has been installed specifying the SECURITYMODE=SQL parameter, you
are wellcome to open a case with Microsoft PSS...
thank you
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.7.0 - DbaMgr ver 0.53.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||hi again,
anyway, try checking the HKLM\Software\Microsoft\Microsoft SQL
Server\Instance Name\MSSQLServer\LoginMode registry key... it shoul'd be 2
or 0 for mixed mode authentication...
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.7.0 - DbaMgr ver 0.53.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||That's the strange part - I did check that in the registry and it was set to
2, so I tried setting it to 0 (then stopped and restarted the MSDE instance)
and got the same error.
Unfortunately, I can't check it again because we ended up just using SQL
Server and uninstalling MSDE for the user with the problem. (It was
actually a mistake for the user to have done the install with MSDE because
usually if they have SQL Server already, we want them to just use that. I
can't think of a good reason for an end user to have both SQL Server and
MSDE installed on the same system. But I was curious to find out whether or
not it should have worked.)
It may indeed have been the dynamic port issue - I did not check the client
configuration to see if it was trying to go to a specific port (i.e. 1433).
But I would have thought it just wouldn't have connected since the server
name contained the instance name.
"Andrea Montanari" <andrea.sqlDMO@.virgilio.it> wrote in message
news:c4ju2l$2h7scp$1@.ID-207518.news.uni-berlin.de...
> hi again,
> anyway, try checking the HKLM\Software\Microsoft\Microsoft SQL
> Server\Instance Name\MSSQLServer\LoginMode registry key... it shoul'd be 2
> or 0 for mixed mode authentication...
> --
> Andrea Montanari (Microsoft MVP - SQL Server)
> http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
> DbaMgr2k ver 0.7.0 - DbaMgr ver 0.53.0
> (my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
> interface)
> -- remove DMO to reply
>

Mixing SQL 2000 and SQL 2005 replication

Does anyone know if a 2005 server can replicate data with a 2000 server or
do they all have to be 2005.
Thanks in advance
Martin
Never mind yes it can.
Martin
"Martin McNally" <martinmcnally@.gmail.com> wrote in message
news:%23sZvOazdFHA.3048@.TK2MSFTNGP12.phx.gbl...
> Does anyone know if a 2005 server can replicate data with a 2000 server or
> do they all have to be 2005.
> Thanks in advance
> Martin
>
|||Martin,
there are loads of caveats for this scenario eg logical records, partitions
etc - have a look at
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/instsql9/html/45154b65-b46f-4aad-82e4-261376a7e9d4.htm
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
sql

Mixing parameter syntax in Execute SQL task

Hi all,

As part of the logging process for data input, I want to update two fields in a logging table. The first is a datetime, derived from looking up the maximum value in another table (the table I've just imported), and the second is an integer - the number of rows captured in a variable during the task.

I can do this in two separate Execute SQL tasks as follows:

Task 1 syntax

DECLARE @.maxDate datetime
SELECT @.maxDate = max(dtLastChangedDate)
FROM dbo.tblCancel_RAW

UPDATE dbo.tblLogging
SET PreviousFilterValue = CurrentFilterValue,
CurrentFilterValue = ISNULL(CAST ( @.maxdate as varchar(25)),CurrentFilterValue),
DateSourceTableLastRead = GetDate(),
RowsReturned= -1
WHERE SourceTableName = 'cancel'

Task 2 Syntax, with the variable user::rowsimported mapped to parameter 0

UPDATE dbo.tblLogging
SET
RowsReturned= ?
WHERE SourceTableName = 'cancel'

However I cannot make this work with a single SQL statement such as

DECLARE @.maxDate datetime
SELECT @.maxDate = max(dtLastChangedDate)
FROM dbo.tblCancel_RAW

UPDATE dbo.tblLogging
SET PreviousFilterValue = CurrentFilterValue,
CurrentFilterValue = ISNULL(CAST ( @.maxdate as varchar(25)),CurrentFilterValue),
DateSourceTableLastRead = GetDate(),
RowsReturned= ?
WHERE SourceTableName = 'cancel'

because no matter how I try to map the parameter (0,1,2,3,4 etc) the task fails.

Is this behaviour by design, is it a bug, or is there something I've missed?

Thanks as ever,

Richard

Richard,

What error message do you get?

-Jamie

Mixing INNER and OUTER joins

I've encountered this problem multiple times in the past and I have a solution but wonder if there might be a more elegant method of achieving the same result...

Take the following example:

SELECT * FROM [User]
LEFT OUTER JOIN [Profile] ON [Profile].[UserId] = [User].[UserId]
INNER JOIN [Department] ON [Department].[DepartmentId] = [Profile].[DepartmentId]

Users may or may not have a profile (but never more than one). A profile may or may not have a department (but never more than one).

Now, this will return only users that have a profile even though an outer join has been used. What I really want is to return all users and include their profile and department details but only when the profile has a department.

The solution I have used in the past is:

SELECT * FROM [User]
LEFT OUTER JOIN
(
SELECT *
FROM [Profile]
INNER JOIN [Department] ON [Department].[DepartmentId] = [Profile].[DepartmentId]
) [ProfileDepartment] ON [ProfileDepartment].[UserId] = [User].[UserId]

The trouble here is that I've lost the ability to reference department and profile independantly in the outer query. Also, more complex scenarios can also become horribly complex if this needs to be done multiple times in the same query.

I could do this:

SELECT * FROM [User]
LEFT OUTER JOIN [Profile] ON [Profile].[UserId] = [User].[UserId] AND [Profile].[DepartmentId] IS NOT NULL
LEFT OUTER JOIN [Department] ON [Department].[DepartmentId] = [Profile].[DepartmentId]

But again I feel that the intention is not at all clear. I want to inner join department to profile because I'm only interested in profiles with a department and departments referenced by a profile.

I would like to be able to specify that the departments should be inner joined to profiles and whichever profiles remain get outer joined to users whilst retaining department and profile as seperate entities within the query.

Is there any way to use brackets to indicate an order of precedance to the logical joins within the from clause?

Daniel

I think that this is pretty much the answer, though there is an easier way to write it

SELECT * FROM [User]
LEFT OUTER JOIN
(
SELECT *
FROM [Profile]
INNER JOIN [Department] ON [Department].[DepartmentId] = [Profile].[DepartmentId]
) [ProfileDepartment] ON [ProfileDepartment].[UserId] = [User].[UserId]

You can nest the joins to get the effect you want:

SELECT *
FROM [User]
LEFT OUTER JOIN [Profile]
INNER JOIN [Department] --this limits the department to the context of a Profile only!
ON [Department].[DepartmentId] = [Profile].[DepartmentId]
ON [Profile].[UserId] = [User].[UserId]

However, you have the same limitation that you could not in another join reference Department. You could nest in other tables to to get to the right combination, perhaps (it can get really messy fast if you have lots of tables to join :) So if you actually need to refer to the department in that context in a later join, then the two left outer joins might be the correct thing to do

SELECT *
FROM [User]
LEFT OUTER JOIN [Profile]
ON [Profile].[UserId] = [User].[UserId]
AND [Profile].[DepartmentId] IS NOT NULL
LEFT OUTER JOIN [Department]
ON [Department].[DepartmentId] = [Profile].[DepartmentId]

I don't see a problem with that syntax at all. You are correct that it is not as clear what your intention is, but, if you are joining department to multiple tables, that might be the case.

I highlighted in that context because if you need department in another context, it is valid to reference it again, as some alias:

SELECT *
FROM [User]
LEFT OUTER JOIN [Profile]
INNER JOIN [Department] --this limits the department to the context of a Profile only!
ON [Department].[DepartmentId] = [Profile].[DepartmentId]
ON [Profile].[UserId] = [User].[UserId]
INNER JOIN Department as otherDepartment
ON User.department = otherDepartment.DepartmentId

Or whatever. It is all in how you need to use the data.

|||

Hi Louis,

Thanks for the info; the nesting of joins is new syntax to me... Looks like I'm going to have to revisit my T-SQL code structure standards now!

I've tried the nexted join approach for the specific scenario I'm working on and it seems to work perfectly and also reads more clearly to me than the other two options.

I've also tried to extend it more than I need right now and it looks like you can reference the nested joined table in a different context, e.g.:

SELECT *

FROM [User]

LEFT OUTER JOIN [Profile]

INNER JOIN [Department] ON [Department].[DepartmentId] = [Profile].[DepartmentId]

ON [Profile].[UserId] = [User].[UserId]

LEFT OUTER JOIN [BusinessUnit] ON [BusinessUnit].[BusinessUnitId] = [Department].[BusinessUnitId]

AND [BusinessUnit].[BusinessUnitId] = [User].[BusinessUnitId]

I think you were saying that the above example would not work because the department table is only visible inside the scope of the "profile" join but the above example does work OK. Have I misunderstood what you were saying or could this be a change in SQL 2005?

Daniel

mixing case sensitive/insensitive instances

Does anyone know of any issues with mixing a case sensitive instance with case INsensitive instances on a cluster? Is there anything I should do or consider before adding a case-sensitive instance?
Instances are as independent from each other regarding case sensitivity and
other SQL Server settings as if they were actually running on different
machines. You can have two instances on the same machine with different
service packs for example, or a SQL Server 7 and a SQL Server 2000 instance.
Jacco Schalkwijk
SQL Server MVP
"J Jetson" <JJetson@.discussions.microsoft.com> wrote in message
news:252CAC4C-D3C9-4DFC-BCA5-84A77AE0BC3F@.microsoft.com...
> Does anyone know of any issues with mixing a case sensitive instance with
> case INsensitive instances on a cluster? Is there anything I should do or
> consider before adding a case-sensitive instance?

mixing case sensitive/insensitive instances

Does anyone know of any issues with mixing a case sensitive instance with ca
se INsensitive instances on a cluster? Is there anything I should do or cons
ider before adding a case-sensitive instance?Instances are as independent from each other regarding case sensitivity and
other SQL Server settings as if they were actually running on different
machines. You can have two instances on the same machine with different
service packs for example, or a SQL Server 7 and a SQL Server 2000 instance.
Jacco Schalkwijk
SQL Server MVP
"J Jetson" <JJetson@.discussions.microsoft.com> wrote in message
news:252CAC4C-D3C9-4DFC-BCA5-84A77AE0BC3F@.microsoft.com...
> Does anyone know of any issues with mixing a case sensitive instance with
> case INsensitive instances on a cluster? Is there anything I should do or
> consider before adding a case-sensitive instance?

Mixing Aggregated data with non-aggregated data in a cube.

At this stage in the project I have a simple fact table which currently holds the low level data. I have designed and built a cube around this table and everything works fine with the current data.

The next stage of the project is to migrate some data into the cube from another business area. The problem is that this data is already grouped. I would like to display this data in the same cube so the users can at least get an overview of the different business areas. Is this practical and doable or are there caveats to doing this?

Does anyone have any suggestions as to what the best approach would be?

The classic approach to this problem is to create another measure group for the new set of data, and set the granularity of that measure group to match the granularity of data. I.e., if in the original measure group, the granularity by Time was Day, in the new one it might be set to Month. Some dimensions may be missing from the new measure group altogether etc.

Mosha.

|||

Interesting.... so is this what I need to do..(an educated guess):

Add the data to the same table|||

Since data is at different granularities - I suggest you keep it in different tables, not in the same table.

Once everything is done - you will be able to use your cube from Excel pivot tables or any other client tool. You will have multiple measures, some of them at one granularity and some at another.

Mixing 32-bit and 64-bit in MSSQL Cluster

Hi,
We currently have 2 32-bit machines in an MS Cluster with MSSQL as a
Resource.
And these machines can only hold 6 Gb of memory which is not enough
anymore.
I was wondering if I could add 1 new 64-bit machine in this cluster or
that need to create a new cluster with 2 new 64-bit machines.
Regards,
Sven Peeters
Hi,
You can not mix 32-bit and 64-bit nodes on the same Windows Cluster. So, you
will need to create a new 64-bit Windows Cluster.
Hope this helps,
Ben Nevarez
"Icemokka" wrote:

> Hi,
> We currently have 2 32-bit machines in an MS Cluster with MSSQL as a
> Resource.
> And these machines can only hold 6 Gb of memory which is not enough
> anymore.
> I was wondering if I could add 1 new 64-bit machine in this cluster or
> that need to create a new cluster with 2 new 64-bit machines.
> Regards,
> Sven Peeters
>
|||Hi,
You can not mix 32-bit and 64-bit nodes on the same Windows Cluster. So, you
will need to create a new 64-bit Windows Cluster.
Hope this helps,
Ben Nevarez
"Icemokka" wrote:

> Hi,
> We currently have 2 32-bit machines in an MS Cluster with MSSQL as a
> Resource.
> And these machines can only hold 6 Gb of memory which is not enough
> anymore.
> I was wondering if I could add 1 new 64-bit machine in this cluster or
> that need to create a new cluster with 2 new 64-bit machines.
> Regards,
> Sven Peeters
>
|||On 10 feb, 11:26, Ben Nevarez <bneva...@.no.spam.please.sunamerica.com>
wrote:
> Hi,
> You can not mix 32-bit and 64-bit nodes on the same Windows Cluster. So, you
> will need to create a new 64-bit Windows Cluster.
> Hope this helps,
> Ben Nevarez
>
> "Icemokka" wrote:
>
> - Tekst uit oorspronkelijk bericht weergeven -
Thank you ...

Mixing 32-bit and 64-bit in MSSQL Cluster

Hi,
We currently have 2 32-bit machines in an MS Cluster with MSSQL as a
Resource.
And these machines can only hold 6 Gb of memory which is not enough
anymore.
I was wondering if I could add 1 new 64-bit machine in this cluster or
that need to create a new cluster with 2 new 64-bit machines.
Regards,
Sven PeetersHi,
You can not mix 32-bit and 64-bit nodes on the same Windows Cluster. So, you
will need to create a new 64-bit Windows Cluster.
Hope this helps,
Ben Nevarez
"Icemokka" wrote:
> Hi,
> We currently have 2 32-bit machines in an MS Cluster with MSSQL as a
> Resource.
> And these machines can only hold 6 Gb of memory which is not enough
> anymore.
> I was wondering if I could add 1 new 64-bit machine in this cluster or
> that need to create a new cluster with 2 new 64-bit machines.
> Regards,
> Sven Peeters
>|||Hi,
You can not mix 32-bit and 64-bit nodes on the same Windows Cluster. So, you
will need to create a new 64-bit Windows Cluster.
Hope this helps,
Ben Nevarez
"Icemokka" wrote:
> Hi,
> We currently have 2 32-bit machines in an MS Cluster with MSSQL as a
> Resource.
> And these machines can only hold 6 Gb of memory which is not enough
> anymore.
> I was wondering if I could add 1 new 64-bit machine in this cluster or
> that need to create a new cluster with 2 new 64-bit machines.
> Regards,
> Sven Peeters
>|||On 10 feb, 11:26, Ben Nevarez <bneva...@.no.spam.please.sunamerica.com>
wrote:
> Hi,
> You can not mix 32-bit and 64-bit nodes on the same Windows Cluster. So, you
> will need to create a new 64-bit Windows Cluster.
> Hope this helps,
> Ben Nevarez
>
> "Icemokka" wrote:
> > Hi,
> > We currently have 2 32-bit machines in an MS Cluster with MSSQL as a
> > Resource.
> > And these machines can only hold 6 Gb of memory which is not enough
> > anymore.
> > I was wondering if I could add 1 new 64-bit machine in this cluster or
> > that need to create a new cluster with 2 new 64-bit machines.
> > Regards,
> > Sven Peeters- Tekst uit oorspronkelijk bericht niet weergeven -
> - Tekst uit oorspronkelijk bericht weergeven -
Thank you ...sql