adjust my code

Hi,
I've created this code to determine the dates for an exam when a
certification must be met within a X number of months or years. The last
deadline date for an exam is the enddate. The marge where one can
schedule his time to study depends on how many exams he must take. So
based on the enddate i calculate backwards to the startdate to get
intervals of when the next examdate should be.
In my testscenario i used 12 months and the number of exams are 4. The
code gave me the results i needed, but when i tried e.g. 18 months the
results are not what i expected. The dates are somewhat correct but
there were too many dates.
Can someone see the error in my code?
declare @.exams int,
@.begindate datetime,
@.enddate datetime,
@.examdate datetime,
@.intervals int,
set @.begindate = '2005-11-23'
set @.enddate = dateadd(mm,12,@.begindate)
set @.exams = 4
set @.intervals = datediff(mm,@.begindate,@.enddate)/@.exams
create table #examdates(id int identity(1,1) , examdate datetime)
--i inserted the enddate as startingpoint, but this could be done
--better, i think
insert into #examdates(examdate)
values(@.enddate)
while (select count(*) from #examdates) <= @.intervals
begin
set @.examdate = (select examdate from #examdates where id in
(select max(id) from #examdates))
insert into #examdates(examdate)
select dateadd(mm,-@.exams,@.examdate) as examdate
end
select examdate from #examdates order by examdateJason
> results are not what i expected. The dates are somewhat correct but
> there were too many dates.
>
What do you want to return if you put 18 months in?
"Jason" <jasonlewis@.hotmail.com> wrote in message
news:eonG0vfEGHA.2040@.TK2MSFTNGP14.phx.gbl...
> Hi,
> I've created this code to determine the dates for an exam when a
> certification must be met within a X number of months or years. The last
> deadline date for an exam is the enddate. The marge where one can schedule
> his time to study depends on how many exams he must take. So based on the
> enddate i calculate backwards to the startdate to get intervals of when
> the next examdate should be.
> In my testscenario i used 12 months and the number of exams are 4. The
> code gave me the results i needed, but when i tried e.g. 18 months the
> results are not what i expected. The dates are somewhat correct but
> there were too many dates.
> Can someone see the error in my code?
> declare @.exams int,
> @.begindate datetime,
> @.enddate datetime,
> @.examdate datetime,
> @.intervals int,
> set @.begindate = '2005-11-23'
> set @.enddate = dateadd(mm,12,@.begindate)
> set @.exams = 4
> set @.intervals = datediff(mm,@.begindate,@.enddate)/@.exams
> create table #examdates(id int identity(1,1) , examdate datetime)
> --i inserted the enddate as startingpoint, but this could be
> done --better, i think
> insert into #examdates(examdate)
> values(@.enddate)
> while (select count(*) from #examdates) <= @.intervals
> begin
> set @.examdate = (select examdate from #examdates where id in
> (select max(id) from #examdates))
> insert into #examdates(examdate)
> select dateadd(mm,-@.exams,@.examdate) as examdate
> end
> select examdate from #examdates order by examdate|||You are creating a variable called intervals which is really the number of
months that you have to take each exam based on the way that you set it.
datediff(mm,@.begindate,@.enddate)/@.exams
So for 12 months this would be 3 but for 18 months it would be 4.5.
You then use this as the loop constraint for adding in your exam dates.
It seems to me like your look constraint should just be the number of exams
that you need to take since you are trying to find a date when to take each
of the exams.
This is the core of your problem. I didn't put any thought into the rest of
the algorithm and how you might be able to accomplish your goal easier.
HTH
Ryan Powers
Clarity Consulting
http://www.claritycon.com
"Jason" wrote:

> Hi,
> I've created this code to determine the dates for an exam when a
> certification must be met within a X number of months or years. The last
> deadline date for an exam is the enddate. The marge where one can
> schedule his time to study depends on how many exams he must take. So
> based on the enddate i calculate backwards to the startdate to get
> intervals of when the next examdate should be.
> In my testscenario i used 12 months and the number of exams are 4. The
> code gave me the results i needed, but when i tried e.g. 18 months the
> results are not what i expected. The dates are somewhat correct but
> there were too many dates.
> Can someone see the error in my code?
> declare @.exams int,
> @.begindate datetime,
> @.enddate datetime,
> @.examdate datetime,
> @.intervals int,
> set @.begindate = '2005-11-23'
> set @.enddate = dateadd(mm,12,@.begindate)
> set @.exams = 4
> set @.intervals = datediff(mm,@.begindate,@.enddate)/@.exams
> create table #examdates(id int identity(1,1) , examdate datetime)
> --i inserted the enddate as startingpoint, but this could be done
> --better, i think
> insert into #examdates(examdate)
> values(@.enddate)
> while (select count(*) from #examdates) <= @.intervals
> begin
> set @.examdate = (select examdate from #examdates where id in
> (select max(id) from #examdates))
> insert into #examdates(examdate)
> select dateadd(mm,-@.exams,@.examdate) as examdate
> end
> select examdate from #examdates order by examdate
>|||Ryan Powers wrote:
> You are creating a variable called intervals which is really the number of
> months that you have to take each exam based on the way that you set it.
> datediff(mm,@.begindate,@.enddate)/@.exams
> So for 12 months this would be 3 but for 18 months it would be 4.5.
> You then use this as the loop constraint for adding in your exam dates.
> It seems to me like your look constraint should just be the number of exam
s
> that you need to take since you are trying to find a date when to take eac
h
> of the exams.
>
> This is the core of your problem. I didn't put any thought into the rest
of
> the algorithm and how you might be able to accomplish your goal easier.
> HTH
>
Hi Ryan,
I want to calculate a date for taking an exam. The intervals are just
the number of months when the next exam must be taken.
Could you point out to me where i should better my code?|||Uri Dimant wrote:
> Jason
>
>
> What do you want to return if you put 18 months in?
>
> "Jason" <jasonlewis@.hotmail.com> wrote in message
> news:eonG0vfEGHA.2040@.TK2MSFTNGP14.phx.gbl...
>
>
>
Hi Uri,
I want to return 4 examdates because the number of exams to be taken are
4, the higher the months the longer someone may study until the next
date occurs.
In case of adding 18 months to the begindate, divide that with the
number of exams, you'll get 4.5 months. So knowing the enddate (is also
the last examdate deadline) i substract the 4.5 months from the enddate
which will give me the examdate before that and so on.
Can you find the mistake i have made in the code?|||Try the following script. I have given some hint about changes made in your
script. Inside the While loop, I have removed the select statement from the
#examdates table. This will enhance performance.
declare @.exams int,
@.begindate datetime,
@.enddate datetime,
@.examdate datetime,
@.intervals int
-- Added script
declare @.NextExamDate datetime, @.LastExamDate datetime
--
set @.begindate = '20051101'
set @.enddate = dateadd(mm,12,@.begindate)
-- Added script
set @.LastExamDate = @.enddate
--
set @.exams = 4
--[replaced with below line] set @.intervals =
datediff(mm,@.begindate,@.enddate)/@.exams
set @.intervals = datediff(dd,@.begindate,@.enddate)/@.exams
--create table #examdates(id int identity(1,1) , examdate datetime)
--i inserted the enddate as startingpoint, but this could be done
--better, i think
insert into #examdates(examdate)
values(@.enddate)
-- While loop script with lots of changes
while @.exams > 1
begin
set @.NextExamDate = dateadd(dd,-@.intervals,@.LastExamDate)
insert into #examdates(examdate) values(@.NextExamDate)
set @.LastExamDate = @.NextExamDate
set @.exams = @.exams -1
end
--
select examdate from #examdates order by examdate
"Jason" wrote:

> Ryan Powers wrote:
> Hi Ryan,
> I want to calculate a date for taking an exam. The intervals are just
> the number of months when the next exam must be taken.
> Could you point out to me where i should better my code?
>|||Sure. You don't need the intervals variable, since you are actually
recalcing it on the fly within the loop. I am going to just keep your base
logic, and show you how you can correct it so it works.
I changed it slightly to find days between exams because I'm thinking that
you can evenly divide the months by the number of exams is not correct. Wha
t
I did will not necessarily give you your last exam on your end date due to
integer math. But, it should be close. We could put in a condition that
checks if we are setting the last date and just set it as the enddate. Let
me know if you need help with that.-
declare @.exams int,
@.begindate datetime,
@.enddate datetime,
@.examdate datetime,
@.intervals int,
@.months int,
@.daysBetweenExams
set @.begindate = '2005-11-23'
set @.exams = 4
set @.months = 12
set @.enddate = dateadd(mm,@.months,@.begindate)
set @.daysBetweenExams = datediff(dd, @.begindate, @.enddate)/@.exams
create table #examdates(id int identity(1,1) , examdate datetime)
set @.examdate = @.begindate
while (select count(*) from #examdates) <= @.exams
begin
SELECT @.examdate = dateadd(dd, @.daysBetweenExams, @.examdate)
insert into #examdates(examdate)
values(@.examdate)
end
select examdate from #examdates order by examdate
--
Ryan Powers
Clarity Consulting
http://www.claritycon.com
"Jason" wrote:

> Ryan Powers wrote:
> Hi Ryan,
> I want to calculate a date for taking an exam. The intervals are just
> the number of months when the next exam must be taken.
> Could you point out to me where i should better my code?
>|||On Thu, 05 Jan 2006 14:28:42 +0100, Jason wrote:

>Hi,
>I've created this code to determine the dates for an exam when a
>certification must be met within a X number of months or years. The last
> deadline date for an exam is the enddate. The marge where one can
>schedule his time to study depends on how many exams he must take. So
>based on the enddate i calculate backwards to the startdate to get
>intervals of when the next examdate should be.
>In my testscenario i used 12 months and the number of exams are 4. The
>code gave me the results i needed, but when i tried e.g. 18 months the
>results are not what i expected. The dates are somewhat correct but
>there were too many dates.
>Can someone see the error in my code?
Hi Jason,
Why not use a set-based query instead of looping?
-- inputs:
DECLARE @.begindate datetime,
@.enddate datetime,
@.exams int
SET @.begindate = '2005-11-23'
SET @.enddate = '2007-05-23'
SET @.exams = 4
-- generate exam dates
--INSERT INTO @.examdate(examdate)
SELECT DATEADD(month,
Number * DATEDIFF(month, @.begindate, @.enddate) / @.exams,
@.begindate)
FROM dbo.Numbers
WHERE Number BETWEEN 1 AND @.exams
Note: this requires the use of a numbers table. See www.aspfaq.com/2516.
Hugo Kornelis, SQL Server MVP|||Hugo Kornelis wrote:
> On Thu, 05 Jan 2006 14:28:42 +0100, Jason wrote:
>
>
> Hi Jason,
> Why not use a set-based query instead of looping?
> -- inputs:
> DECLARE @.begindate datetime,
> @.enddate datetime,
> @.exams int
> SET @.begindate = '2005-11-23'
> SET @.enddate = '2007-05-23'
> SET @.exams = 4
> -- generate exam dates
> --INSERT INTO @.examdate(examdate)
> SELECT DATEADD(month,
> Number * DATEDIFF(month, @.begindate, @.enddate) / @.exams,
> @.begindate)
> FROM dbo.Numbers
> WHERE Number BETWEEN 1 AND @.exams
> Note: this requires the use of a numbers table. See www.aspfaq.com/2516.
>
Hi hugo,
your solution did the job. Thnx!

addning roles to a user

I created a "standard" role called "TestRole". Then I went to the list of
users and selected "dbo". I tried adding "TestRole" but I received a message
"Error 15405: Cannot use the reserved user or role name 'dbo'". I have
looked thru various sources to try to understand this but nothing really
straightforward explains what the problem is.
Hi Steven
Please ALWAYS state what version you are running.
The user dbo can do everything in a database. Why do you want to add that
user to a role?
What are you trying to accomplish?
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"Steven.Dahlin" <StevenDahlin@.discussions.microsoft.com> wrote in message
news:46113FB9-B978-4DC3-83BE-609BACE5F8F1@.microsoft.com...
>I created a "standard" role called "TestRole". Then I went to the list of
> users and selected "dbo". I tried adding "TestRole" but I received a
> message
> "Error 15405: Cannot use the reserved user or role name 'dbo'". I have
> looked thru various sources to try to understand this but nothing really
> straightforward explains what the problem is.
>
sql

addning roles to a user

I created a "standard" role called "TestRole". Then I went to the list of
users and selected "dbo". I tried adding "TestRole" but I received a message
"Error 15405: Cannot use the reserved user or role name 'dbo'". I have
looked thru various sources to try to understand this but nothing really
straightforward explains what the problem is.Hi Steven
Please ALWAYS state what version you are running.
The user dbo can do everything in a database. Why do you want to add that
user to a role?
What are you trying to accomplish?
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"Steven.Dahlin" <StevenDahlin@.discussions.microsoft.com> wrote in message
news:46113FB9-B978-4DC3-83BE-609BACE5F8F1@.microsoft.com...
>I created a "standard" role called "TestRole". Then I went to the list of
> users and selected "dbo". I tried adding "TestRole" but I received a
> message
> "Error 15405: Cannot use the reserved user or role name 'dbo'". I have
> looked thru various sources to try to understand this but nothing really
> straightforward explains what the problem is.
>

addning roles to a user

I created a "standard" role called "TestRole". Then I went to the list of
users and selected "dbo". I tried adding "TestRole" but I received a message
"Error 15405: Cannot use the reserved user or role name 'dbo'". I have
looked thru various sources to try to understand this but nothing really
straightforward explains what the problem is.Hi Steven
Please ALWAYS state what version you are running.
The user dbo can do everything in a database. Why do you want to add that
user to a role?
What are you trying to accomplish?
--
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"Steven.Dahlin" <StevenDahlin@.discussions.microsoft.com> wrote in message
news:46113FB9-B978-4DC3-83BE-609BACE5F8F1@.microsoft.com...
>I created a "standard" role called "TestRole". Then I went to the list of
> users and selected "dbo". I tried adding "TestRole" but I received a
> message
> "Error 15405: Cannot use the reserved user or role name 'dbo'". I have
> looked thru various sources to try to understand this but nothing really
> straightforward explains what the problem is.
>

addling linked server

Hi ,
how do i actually created a linked server in Enterprise
Manager ?
go to the Linked Server , do i choose impersonation ,
no security context or the remote user
usin impersonation means the other server needs to set
up as allowing impersonation
using the remote user means i'll need to create a login
over at the other side ?
have tried but still cannot connect
i have used the Query Analyzer though it like this
sp_addlinkedserver 'dbABC' and it says added
when i run query analyzer to get records using
dbABC.server123.dbo.xxx it says "General network
error ..."
appreciate any advise
thks & rdgs
Hate to say this, but..
You need to do some more reading on Linked Servers.
There are a lot of little items that need to be checked when you create a
linked server.
The server name, provider name (MSDAORA for example), the data source,
provider string.. catalog)
The second step in the process is to determine your security settings.. Use
sp_addlinkedsrvlgoin.
Once you gotten through the readings on those and linking server in general,
you should have a lot better idea of how it is done.
HTH
Rick Sawtell
MCT, MCSD, MCDBA
"maxzsim" <anonymous@.discussions.microsoft.com> wrote in message
news:05f501c4917d$3a0c4a40$a401280a@.phx.gbl...
> Hi ,
> how do i actually created a linked server in Enterprise
> Manager ?
>
> go to the Linked Server , do i choose impersonation ,
> no security context or the remote user
> usin impersonation means the other server needs to set
> up as allowing impersonation
> using the remote user means i'll need to create a login
> over at the other side ?
> have tried but still cannot connect
>
> i have used the Query Analyzer though it like this
> sp_addlinkedserver 'dbABC' and it says added
> when i run query analyzer to get records using
> dbABC.server123.dbo.xxx it says "General network
> error ..."
> appreciate any advise
> thks & rdgs
|||What is the target for the linked server? Is it SQL Server? Depending on
what security context you need to have the queries run on your linked
server target, you can select 1 of the 4 options on the security tab. Click
the Help button (in SQL Enterprise Manager -> Configure Linked Server's
Security tab) to see what each of these options means.
The easiest 1 of the 4 options you can use is the last one "Be made using
this security context". This option requires you to setup a login on the
linked server target and provide the login name and password in the linked
server configuration dialog in Enterprise Manager. (Keep in mind that if
you're using SQL Server as the targer for your linked server, choosing this
option will mean that you have to configure your target SQL Server to be
configured for SQL and Windows Authentication).
Refer to the following topic for additional information :
http://msdn.microsoft.com/library/de...us/adminsql/ad
_1_server_24tv.asp
Thank you for using Microsoft newsgroups.
Sincerely
Pankaj Agarwal
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.
sql

addling linked server

Hi ,
how do i actually created a linked server in Enterprise
Manager ?
go to the Linked Server , do i choose impersonation ,
no security context or the remote user
usin impersonation means the other server needs to set
up as allowing impersonation
using the remote user means i'll need to create a login
over at the other side ?
have tried but still cannot connect
i have used the Query Analyzer though it like this
sp_addlinkedserver 'dbABC' and it says added
when i run query analyzer to get records using
dbABC.server123.dbo.xxx it says "General network
error ..."
appreciate any advise
thks & rdgsHate to say this, but..
You need to do some more reading on Linked Servers.
There are a lot of little items that need to be checked when you create a
linked server.
The server name, provider name (MSDAORA for example), the data source,
provider string.. catalog)
The second step in the process is to determine your security settings.. Use
sp_addlinkedsrvlgoin.
Once you gotten through the readings on those and linking server in general,
you should have a lot better idea of how it is done.
HTH
Rick Sawtell
MCT, MCSD, MCDBA
"maxzsim" <anonymous@.discussions.microsoft.com> wrote in message
news:05f501c4917d$3a0c4a40$a401280a@.phx.gbl...
> Hi ,
> how do i actually created a linked server in Enterprise
> Manager ?
>
> go to the Linked Server , do i choose impersonation ,
> no security context or the remote user
> usin impersonation means the other server needs to set
> up as allowing impersonation
> using the remote user means i'll need to create a login
> over at the other side ?
> have tried but still cannot connect
>
> i have used the Query Analyzer though it like this
> sp_addlinkedserver 'dbABC' and it says added
> when i run query analyzer to get records using
> dbABC.server123.dbo.xxx it says "General network
> error ..."
> appreciate any advise
> thks & rdgs|||What is the target for the linked server? Is it SQL Server? Depending on
what security context you need to have the queries run on your linked
server target, you can select 1 of the 4 options on the security tab. Click
the Help button (in SQL Enterprise Manager -> Configure Linked Server's
Security tab) to see what each of these options means.
The easiest 1 of the 4 options you can use is the last one "Be made using
this security context". This option requires you to setup a login on the
linked server target and provide the login name and password in the linked
server configuration dialog in Enterprise Manager. (Keep in mind that if
you're using SQL Server as the targer for your linked server, choosing this
option will mean that you have to configure your target SQL Server to be
configured for SQL and Windows Authentication).
Refer to the following topic for additional information :
http://msdn.microsoft.com/library/d...-us/adminsql/ad
_1_server_24tv.asp
Thank you for using Microsoft newsgroups.
Sincerely
Pankaj Agarwal
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.

addling linked server

Hi ,
how do i actually created a linked server in Enterprise
Manager ?
go to the Linked Server , do i choose impersonation ,
no security context or the remote user
usin impersonation means the other server needs to set
up as allowing impersonation
using the remote user means i'll need to create a login
over at the other side ?
have tried but still cannot connect
i have used the Query Analyzer though it like this
sp_addlinkedserver 'dbABC' and it says added
when i run query analyzer to get records using
dbABC.server123.dbo.xxx it says "General network
error ..."
appreciate any advise
thks & rdgsHate to say this, but..
You need to do some more reading on Linked Servers.
There are a lot of little items that need to be checked when you create a
linked server.
The server name, provider name (MSDAORA for example), the data source,
provider string.. catalog)
The second step in the process is to determine your security settings.. Use
sp_addlinkedsrvlgoin.
Once you gotten through the readings on those and linking server in general,
you should have a lot better idea of how it is done.
HTH
Rick Sawtell
MCT, MCSD, MCDBA
"maxzsim" <anonymous@.discussions.microsoft.com> wrote in message
news:05f501c4917d$3a0c4a40$a401280a@.phx.gbl...
> Hi ,
> how do i actually created a linked server in Enterprise
> Manager ?
>
> go to the Linked Server , do i choose impersonation ,
> no security context or the remote user
> usin impersonation means the other server needs to set
> up as allowing impersonation
> using the remote user means i'll need to create a login
> over at the other side ?
> have tried but still cannot connect
>
> i have used the Query Analyzer though it like this
> sp_addlinkedserver 'dbABC' and it says added
> when i run query analyzer to get records using
> dbABC.server123.dbo.xxx it says "General network
> error ..."
> appreciate any advise
> thks & rdgs|||What is the target for the linked server? Is it SQL Server? Depending on
what security context you need to have the queries run on your linked
server target, you can select 1 of the 4 options on the security tab. Click
the Help button (in SQL Enterprise Manager -> Configure Linked Server's
Security tab) to see what each of these options means.
The easiest 1 of the 4 options you can use is the last one "Be made using
this security context". This option requires you to setup a login on the
linked server target and provide the login name and password in the linked
server configuration dialog in Enterprise Manager. (Keep in mind that if
you're using SQL Server as the targer for your linked server, choosing this
option will mean that you have to configure your target SQL Server to be
configured for SQL and Windows Authentication).
Refer to the following topic for additional information :
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad
_1_server_24tv.asp
Thank you for using Microsoft newsgroups.
Sincerely
Pankaj Agarwal
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.

Additional info

Hi,
I have one client and one server. In both hosts I created an OS-user named User1.
The user have been given various role under the prefix SQLServer2005. The default instance of SQL Server and instance (background-process) of Analysis Service are also run under User1.
I connected via Management Studio to Analysis Service directly in the server. I set the permission to access Analysis Service to Everyone. This means everyone should be able to use Analysis Service. I have set ODBC data source to the server. Testing was successful.
I created a project to create a cube. The tables are fairly small (total 300 MB, biggest one 290).
On project setting (created at client) I set the server host as deployment target. I managed to deploy project when all I have are Data Source and Data Source Views.
But on deployment I always failed. The errors remain the same, begins with
-
OLE DB error: OLE DB or ODBC error: [DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied.; 08001.

I have tried 4 combinations of Impersonation :
(A) Use a specific username and password
* in which I supply User1 and the password of User1
(B) Use the service account
(C) Use the credentials of the current user
(D) Default
Why is this? The server does certainly exist. I think the error message is wrong.
I have seen 3 posting that seems related. One of them say the solution is giving the right impersonation, but gave no detail. About the privilege, User1 are granted the role of
SQLServer2005MSOLAP. What is the solution?
Thank you,
Bernaridho
For additional info,

I allowed remote connection to the SQL Server database-engine. I always managed to connect via Management Studio in the client to access the server. For ODBC data source I have tried two methods of authentication : Windows and SQL Server. The error remain the same.
In essence, I have played with 8 combination : two methods of authentication in ODBC data source, and 4 combination of impersenation in data source view. The errors are consistent.

Thank you,

Bernaridho
|||I also encountered this error. I workaround by performing a remote connection to the server and create the Business Intelligence project directly on the server itself.|||Hi Sheah,

I'm not sure how often this error will occur. But trying other server, I don't have problem with one of previously failed try : impersonation using specific user. I supplied username User1 and its password as the credential for Data Source.

In my case, one of my server has the problem. Is it a bug or something I don't know but certainly the exact same setting on my two server machines give two different results : fail and success. Nevertheless, you may try setting the impersonation that uses specific username. I tried using OS-user (Windows-user), not SQL Server user. It worked.

Bernaridho
|||

Bernaridho wrote:

For additional info,

I allowed remote connection to the SQL Server database-engine. I always managed to connect via Management Studio in the client to access the server. For ODBC data source I have tried two methods of authentication : Windows and SQL Server. The error remain the same.
In essence, I have played with 8 combination : two methods of authentication in ODBC data source, and 4 combination of impersenation in data source view. The errors are consistent.

Thank you,

Bernaridho

sql

Additional info

Hi,

I have one client and one server. In both hosts I created an OS-user named User1.

The user have been given various role under the prefix SQLServer2005.

The default instance of SQL Server and instance (background-process) of

Analysis Service are also run under User1.

I connected via Management Studio to Analysis Service directly in the

server. I set the permission to access Analysis Service to Everyone.

This means everyone should be able to use Analysis Service. I have set

ODBC data source to the server. Testing was successful.

I created a project to create a cube. The tables are fairly small (total 300 MB, biggest one 290).

On project setting (created at client) I set the server host as

deployment target. I managed to deploy project when all I have are Data

Source and Data Source Views.

But on deployment I always failed. The errors remain the same, begins with

-

OLE DB error: OLE DB or ODBC error: [DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied.; 08001.
I have tried 4 combinations of Impersonation :

(A) Use a specific username and password

* in which I supply User1 and the password of User1

(B) Use the service account

(C) Use the credentials of the current user

(D) Default

Why is this? The server does certainly exist. I think the error message is wrong.
I have seen 3 posting that seems related. One of them say the solution is giving the right impersonation, but gave no detail. About the privilege, User1 are granted the role of
SQLServer2005MSOLAP. What is the solution?

Thank you,

BernaridhoFor additional info,

I allowed remote connection to the SQL Server database-engine. I always managed to connect via Management Studio in the client to access the server. For ODBC data source I have tried two methods of authentication : Windows and SQL Server. The error remain the same.
In essence, I have played with 8 combination : two methods of authentication in ODBC data source, and 4 combination of impersenation in data source view. The errors are consistent.

Thank you,

Bernaridho|||I also encountered this error. I workaround by performing a remote connection to the server and create the Business Intelligence project directly on the server itself.|||Hi Sheah,

I'm not sure how often this error will occur. But trying other server, I don't have problem with one of previously failed try : impersonation using specific user. I supplied username User1 and its password as the credential for Data Source.

In my case, one of my server has the problem. Is it a bug or something I don't know but certainly the exact same setting on my two server machines give two different results : fail and success. Nevertheless, you may try setting the impersonation that uses specific username. I tried using OS-user (Windows-user), not SQL Server user. It worked.

Bernaridho|||

Bernaridho wrote:

For additional info,

I allowed remote connection to the SQL Server database-engine. I always managed to connect via Management Studio in the client to access the server. For ODBC data source I have tried two methods of authentication : Windows and SQL Server. The error remain the same.
In essence, I have played with 8 combination : two methods of authentication in ODBC data source, and 4 combination of impersenation in data source view. The errors are consistent.

Thank you,

Bernaridho

adding two view columns together

I have created a new view where I joined two other views I want to use in
reporting services. I want to add two columns one from each view. The join
allows all records from both views. This creates a lot of null values in one
of the columns I want to add and therefore I get a null value as the answer
instead of the desired result of adding the two columns. Is there a way to
put zeros in place of the nulls within the select statement so that all
records will add ?
Ex: Col1 + Col2 = Col3
2 3 5
null 2 null (desired answer is 2)
I have been able to do this by using a temporary table but that makes things
more complicated.
Thank you for your help.Use the SQL statement IsNull. From books online:
USE AdventureWorks;
GO
SELECT Description, DiscountPct, MinQty, ISNULL(MaxQty, 0.00) AS 'Max
Quantity'
FROM Sales.SpecialOffer;
GO
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"JD" <jim@.microsoft.com> wrote in message
news:emMfrtvKIHA.4228@.TK2MSFTNGP02.phx.gbl...
>I have created a new view where I joined two other views I want to use in
>reporting services. I want to add two columns one from each view. The join
>allows all records from both views. This creates a lot of null values in
>one of the columns I want to add and therefore I get a null value as the
>answer instead of the desired result of adding the two columns. Is there a
>way to put zeros in place of the nulls within the select statement so that
>all records will add ?
> Ex: Col1 + Col2 = Col3
> 2 3 5
> null 2 null (desired answer is 2)
> I have been able to do this by using a temporary table but that makes
> things more complicated.
> Thank you for your help.
>|||You can also use in reporting services in your Col3 the following expression
=cint(Fields!Col1) + cint(Fields!Col2)
and of course, you can use cint function in your Col1 and Col2 columns.
Hope this helps,
Mónica
"JD" <jim@.microsoft.com> escribió en el mensaje
news:emMfrtvKIHA.4228@.TK2MSFTNGP02.phx.gbl...
>I have created a new view where I joined two other views I want to use in
>reporting services. I want to add two columns one from each view. The join
>allows all records from both views. This creates a lot of null values in
>one of the columns I want to add and therefore I get a null value as the
>answer instead of the desired result of adding the two columns. Is there a
>way to put zeros in place of the nulls within the select statement so that
>all records will add ?
> Ex: Col1 + Col2 = Col3
> 2 3 5
> null 2 null (desired answer is 2)
> I have been able to do this by using a temporary table but that makes
> things more complicated.
> Thank you for your help.
>

Adding transaction causes connection failure?

I have some tables that I need to wipeout and reload once per day. So I have created a T-SQL task that does a truncate table, then a data flow task, then a update statistics task. Running those works fine, but as soon as I put them all into a sequence container and set the container to require transactions, the first step gets a connection failure. "Failed to acquire connection "<name>". Connection may not be configured correctly or you may not have the right permissions on this connection". I am using a .Net SQLClient data provider for the T-SQL task and in the data flow task I have to use a OLEDB provider so that I can run the task locally in development.

Is there something I am doing wrong or is there some other way to handle truncate, load, update stats all in a transaction?

Thanks.

Tim

Unfortunately, the ADO.NET Connection Manager doesn't currently support DTC transaction enlistment, so the sequence container can't coordinate one transaction across your three tasks. I believe your best workaround (and one which will keep all the activity on one connection, which seems preferable in your case anyway), would be to use just the OLE DB connection.

To do this, you'll need to turn your Execute T-SQL task into a plain Execute SQL task (your TRUNCATE TABLE will work fine there). Next, create an Execute SQL Task to run your UPDATE STATISTICS statement (use the "View T-SQL" option in your Update Statistics task editor to see the SQL you'll want to run). You'll then be able to get rid of the ADO.NET connection manager, and your Sequence Container will be able to properly coordinate the transaction across the three operations.

Hope this helps!

|||That didn't work. I still get the failed to acquire connection error.|||

After further research I found that it was because the DTC was not set up to allow inbound transactions on the server. The config settings in question are described here:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=230390&SiteID=1

|||So fixing the DTC config helped me get through the first hurdle of connection failures, but now, it just gets stuck in the validation phase of my data flow.|||

Interesting. I just saw this myself, and what's happenning is that the execution of the TRUNCATE in a transaction appears to cause DTC to obtain a Schema Modification lock (LCK_M_SCH_M). This in turn blocks your destination's ability to fetch metadata for your destination, if ValidateExternalMetadata is True on your destination. Try disabling the destination's ValidateExternalMetadata.

-David

|||

That fixed it. I now have all the tasks running in a transaction. It seems odd that the truncate would cause a lock that doesn't allow reads of schema data. Thanks for your help.

Tim

|||I have the same problem.

I have a Sequence Container with 2 sql task and 2 Dataflow task inside.

Transaction option :
On the package : Supported On the sequence container : Required On the 4 task inside the container : Supported

Adding transaction causes connection failure?

I have some tables that I need to wipeout and reload once per day. So I have created a T-SQL task that does a truncate table, then a data flow task, then a update statistics task. Running those works fine, but as soon as I put them all into a sequence container and set the container to require transactions, the first step gets a connection failure. "Failed to acquire connection "<name>". Connection may not be configured correctly or you may not have the right permissions on this connection". I am using a .Net SQLClient data provider for the T-SQL task and in the data flow task I have to use a OLEDB provider so that I can run the task locally in development.

Is there something I am doing wrong or is there some other way to handle truncate, load, update stats all in a transaction?

Thanks.

Tim

Unfortunately, the ADO.NET Connection Manager doesn't currently support DTC transaction enlistment, so the sequence container can't coordinate one transaction across your three tasks. I believe your best workaround (and one which will keep all the activity on one connection, which seems preferable in your case anyway), would be to use just the OLE DB connection.

To do this, you'll need to turn your Execute T-SQL task into a plain Execute SQL task (your TRUNCATE TABLE will work fine there). Next, create an Execute SQL Task to run your UPDATE STATISTICS statement (use the "View T-SQL" option in your Update Statistics task editor to see the SQL you'll want to run). You'll then be able to get rid of the ADO.NET connection manager, and your Sequence Container will be able to properly coordinate the transaction across the three operations.

Hope this helps!

|||That didn't work. I still get the failed to acquire connection error.|||

After further research I found that it was because the DTC was not set up to allow inbound transactions on the server. The config settings in question are described here:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=230390&SiteID=1

|||So fixing the DTC config helped me get through the first hurdle of connection failures, but now, it just gets stuck in the validation phase of my data flow.|||

Interesting. I just saw this myself, and what's happenning is that the execution of the TRUNCATE in a transaction appears to cause DTC to obtain a Schema Modification lock (LCK_M_SCH_M). This in turn blocks your destination's ability to fetch metadata for your destination, if ValidateExternalMetadata is True on your destination. Try disabling the destination's ValidateExternalMetadata.

-David

|||

That fixed it. I now have all the tasks running in a transaction. It seems odd that the truncate would cause a lock that doesn't allow reads of schema data. Thanks for your help.

Tim

|||I have the same problem.

I have a Sequence Container with 2 sql task and 2 Dataflow task inside.

Transaction option :
On the package : Supported On the sequence container : Required On the 4 task inside the container : Supportedsql

Adding transaction causes connection failure?

I have some tables that I need to wipeout and reload once per day. So I have created a T-SQL task that does a truncate table, then a data flow task, then a update statistics task. Running those works fine, but as soon as I put them all into a sequence container and set the container to require transactions, the first step gets a connection failure. "Failed to acquire connection "<name>". Connection may not be configured correctly or you may not have the right permissions on this connection". I am using a .Net SQLClient data provider for the T-SQL task and in the data flow task I have to use a OLEDB provider so that I can run the task locally in development.

Is there something I am doing wrong or is there some other way to handle truncate, load, update stats all in a transaction?

Thanks.

Tim

Unfortunately, the ADO.NET Connection Manager doesn't currently support DTC transaction enlistment, so the sequence container can't coordinate one transaction across your three tasks. I believe your best workaround (and one which will keep all the activity on one connection, which seems preferable in your case anyway), would be to use just the OLE DB connection.

To do this, you'll need to turn your Execute T-SQL task into a plain Execute SQL task (your TRUNCATE TABLE will work fine there). Next, create an Execute SQL Task to run your UPDATE STATISTICS statement (use the "View T-SQL" option in your Update Statistics task editor to see the SQL you'll want to run). You'll then be able to get rid of the ADO.NET connection manager, and your Sequence Container will be able to properly coordinate the transaction across the three operations.

Hope this helps!

|||That didn't work. I still get the failed to acquire connection error.|||

After further research I found that it was because the DTC was not set up to allow inbound transactions on the server. The config settings in question are described here:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=230390&SiteID=1

|||So fixing the DTC config helped me get through the first hurdle of connection failures, but now, it just gets stuck in the validation phase of my data flow.|||

Interesting. I just saw this myself, and what's happenning is that the execution of the TRUNCATE in a transaction appears to cause DTC to obtain a Schema Modification lock (LCK_M_SCH_M). This in turn blocks your destination's ability to fetch metadata for your destination, if ValidateExternalMetadata is True on your destination. Try disabling the destination's ValidateExternalMetadata.

-David

|||

That fixed it. I now have all the tasks running in a transaction. It seems odd that the truncate would cause a lock that doesn't allow reads of schema data. Thanks for your help.

Tim

|||I have the same problem.

I have a Sequence Container with 2 sql task and 2 Dataflow task inside.

Transaction option :
On the package : Supported On the sequence container : Required On the 4 task inside the container : Supported

Adding the parameters dynamicaly to SP

In our website we have created the reportengine which passes the parameters dynamicaly means whatever parameters are provided in Stored procedure is automaticaly binded to grid from where values are passed to sp as follows
.Actualy this code is written by some other developer
If Not ParameterTable Is Nothing Then
RecordCount = ParameterTable.Rows.Count
If RecordCount >= 1 Then
For Index = 0 to (RecordCount - 1)
ParameterRow = ParameterTable.Rows(Index)
Select Case ParameterRow(1)
Case "231"
RecordType = "SQlDbType.NVarChar"
Case "60"
RecordType = "SQlDbType.Money"
Case "61"
RecordType = "SQlDbType.DateTime"
Case "62"
RecordType = "SQlDbType.Int"
Case "59"
RecordType = "SQlDbType.Float"
Case "104"
RecordType = "SQlDbType.Bit"
Case Else
RecordType = "SQlDbType.Int"

End Select
myCommand.Parameters.Add(ParameterRow(0), RecordType).Value = (ParameterRow(2))

[red]When Sp is executed with link[/red]<A href="http://links.10026.com/?link=Main.aspx?ReportID=1075443551&ReportName=RptAPandARComparative&ReportDescription=RptAPandARComparative"
target="main">

I get the error that nvarchar can not be converted int

Why is is So?
Is anybody having same type of experiance
In above link I got reportid as queries
select id from sysobjects where name='RptAPandARComparative'

Swati Jain:

I get the error that nvarchar can not be converted int

Is your stored procedure parameter declared as an int or varchar?

|||

I tried all datatypes still I got the same error with sp as well as code

|||

Swati Jain:

I tried all datatypes still I got the same error with sp as well as code

Can you show us your input parameters for the stored procedures?

You would also have to specify the parameter value size as well to make sure there are no data type mismatch errors.

|||

alterPROCEDURE [enterprise].[RptAPandARComparative]

@.Year1Int,--nvarchar(8), I tried various datatypes

@.Year2Int--nvarchar(8)

AS

No Code is written for specifying the size of the datatype

|||

Swati Jain:

alterPROCEDURE [enterprise].[RptAPandARComparative]

@.Year1Int,--nvarchar(8), I tried various datatypes

@.Year2Int--nvarchar(8)

your stored procedures are declared with INTEGER input paramters, so you have to set the type for your parameters toSqlDbType.Int for both input parameters.

You should also set the size as well, I believe int datatypes in SQL Server is 8 bytes.

Adding Subreport Total to Main Report Total

Hi, can anyone help?

I have created a Report using Visual studio-the report displays a subreport within it.

On the Subjective Report I have 12 values for each month of the year.

For the first month the value is =sum(Fields! Month_1.Value), and I

have named this text box ’SubRepM1’

The name of the subreport is ‘subreport1'.

On my Main Report, again I have 12 values for each month of the year.

For the first month the value is =sum(Fields! Month_1.Value)*-1, and I

have named this text box 'MainRepM1'

The name of the main report is 'GMSHA Budget Adjustment Differentials'

The report displays both of the subreport and main report values

but I now need to total these values together for each month in order to

produce a grand total.

I have tried using the following to add the totals for Month 1 together,

=subreport1.Report.SubRepM1 + MainRepM1

but this does not work and I get the following error message ‘The value expression for the text box 'textbox18'contains an error [BC30451] Name subreport1 is not declared'.

I feel that it should be a simple matter of adding the two sets of values together but I’m having major problems trying to get these totals to work.

Can anyone help, thanks

I have the same problem, did you find a solution yet?

Please help. thanks

|||

Hi Dalia

Sorry, no one has been able to help me with this query yet!

But if i find the answer i will let you know.

|||

Hello,

Actually, this is not doable in SQL reporting, you can use multiple datasets instead.

I hope that helps you.

|||

hiya Dailia

tried using an additional dataset and it works perfectly

many thanx

|||How did you use an additional dataset?

Adding Subreport Total to Main Report Total

Hi, can anyone help?

I have created a Report using Visual studio-the report displays a subreport within it.

On the Subjective Report I have 12 values for each month of the year.

For the first month the value is =sum(Fields! Month_1.Value), and I

have named this text box ’SubRepM1’

The name of the subreport is ‘subreport1'.

On my Main Report, again I have 12 values for each month of the year.

For the first month the value is =sum(Fields! Month_1.Value)*-1, and I

have named this text box 'MainRepM1'

The name of the main report is 'GMSHA Budget Adjustment Differentials'

The report displays both of the subreport and main report values

but I now need to total these values together for each month in order to

produce a grand total.

I have tried using the following to add the totals for Month 1 together,

=subreport1.Report.SubRepM1 + MainRepM1

but this does not work and I get the following error message ‘The value expression for the text box 'textbox18'contains an error [BC30451] Name subreport1 is not declared'.

I feel that it should be a simple matter of adding the two sets of values together but I’m having major problems trying to get these totals to work.

Can anyone help, thanks

I have the same problem, did you find a solution yet?

Please help. thanks

|||

Hi Dalia

Sorry, no one has been able to help me with this query yet!

But if i find the answer i will let you know.

|||

Hello,

Actually, this is not doable in SQL reporting, you can use multiple datasets instead.

I hope that helps you.

|||

hiya Dailia

tried using an additional dataset and it works perfectly

many thanx

|||How did you use an additional dataset?

Adding Subreport Total to Main Report Total

Hi, can anyone help?

I have created a Report using Visual studio-the report displays a subreport within it.

On the Subjective Report I have 12 values for each month of the year.

For the first month the value is =sum(Fields! Month_1.Value), and I

have named this text box ’SubRepM1’

The name of the subreport is ‘subreport1'.

On my Main Report, again I have 12 values for each month of the year.

For the first month the value is =sum(Fields! Month_1.Value)*-1, and I

have named this text box 'MainRepM1'

The name of the main report is 'GMSHA Budget Adjustment Differentials'

The report displays both of the subreport and main report values

but I now need to total these values together for each month in order to

produce a grand total.

I have tried using the following to add the totals for Month 1 together,

=subreport1.Report.SubRepM1 + MainRepM1

but this does not work and I get the following error message ‘The value expression for the text box 'textbox18'contains an error [BC30451] Name subreport1 is not declared'.

I feel that it should be a simple matter of adding the two sets of values together but I’m having major problems trying to get these totals to work.

Can anyone help, thanks

I have the same problem, did you find a solution yet?

Please help. thanks

|||

Hi Dalia

Sorry, no one has been able to help me with this query yet!

But if i find the answer i will let you know.

|||

Hello,

Actually, this is not doable in SQL reporting, you can use multiple datasets instead.

I hope that helps you.

|||

hiya Dailia

tried using an additional dataset and it works perfectly

many thanx

|||How did you use an additional dataset?

adding sqlexpress sp1/2 into setup project

Hi All,

How can I add sql server express sp1 or sp2 into a setup project I created for an application. Normally sqlserver express is a prerequisite for my app and in deployment vs only adds sqlexpress not the sqlexpress with sp1 or sp2. I know that I need those to make my application run on vista. Any suggestion?

Thanks

The prerequisites in VS are driven by a set of manifests installed with VS that only target SQL Express RTM. Microsoft has not released updates to these prerequisites, but you can build your own prerequisite for SQL Express SP2 by following the directions in the SQL Express blog. Check out the instructions here.

Mike

|||After a long wait I knew that you will reply. I haven't tried your instructions yet but I am sure this is the answer. Thanks for the reply.

adding sqlexpress sp1/2 into setup project

Hi All,

How can I add sql server express sp1 or sp2 into a setup project I created for an application. Normally sqlserver express is a prerequisite for my app and in deployment vs only adds sqlexpress not the sqlexpress with sp1 or sp2. I know that I need those to make my application run on vista. Any suggestion?

Thanks

The prerequisites in VS are driven by a set of manifests installed with VS that only target SQL Express RTM. Microsoft has not released updates to these prerequisites, but you can build your own prerequisite for SQL Express SP2 by following the directions in the SQL Express blog. Check out the instructions here.

Mike

|||After a long wait I knew that you will reply. I haven't tried your instructions yet but I am sure this is the answer. Thanks for the reply.

Adding SQL Server Database file to the website

Hi!!!

I have a database called "test_DB" i have one table and some data on it. I uploaded the website and created an SQL Server account to upload the database. Can you please help me to do that task. Is there any step by step directions to do it and what files i need to add ( like ".mdf" or others...) I appreciate your help!!!

Thanks...

You are using an ISP for the hosting? They should have some way of setting that up. You probably should send them an email for uploading a sql server database. If you own the server, do a database detach/attach, which is simple.