Deployment Issue

We have a requirement to display some 80,000 records in the report. The Stored procedure in the back end is displaying results in less than 2 mins..but the system hangs when we run the report.

Any work around for this strange behaviour?

When you run a report, not only do you have to collect the data, you also have to render the report, 80K records is 800 pages if you have 100 records per page.

What happens if you run the report as a scheduled report, and set the run time to the middle of the night when database and report server activity is low?

Deploying to Report Manager error

I have created a report using SSRS 2005. It uses a stored procedure. It executes beautifully when I'm in reporting services. But, when I deploy it to Report Manager, this is the error I am getting.

An error has occurred during report processing.
Query execution failed for data set 'DataSource1'.
An MDX Statement was expected. An MDX expression was specified.

I have 3 parameters in this report. Start Date, End Date & Region. Region is a drop down that contains 2 options. This is a string field being passed into the procedure. Start and end dates are just basic date fields.

I am not using a cube to get my data.

I have deployed to the Report Manager before and have been successful.

Thanks!

With a name like DataSource1 I'm assuming this is a test environment. It's probable that in a previous test a shared data source (connecting to MSAS) with the same name was deployed to the server.

In you report project, make sure that in the deployment properties you have the "Overwrite Data Sources" option set to true. I suspect that when you deploy, only the report is being uploaded and not the new data source that it's using

deploying extended stored proc

Hi
I have written an extended stored procedure DLL, thanks to the people who
helped me on this.
however i wonder if anyone could shed any light on my deployment issues that
I'm having with it.
Basically, I built it on my XP development PC with VC7.1. I can deploy it on
this PC's local default instance of MSDE without problems. It uses no managed
code whatsoever, and is compiled without /clr. Totally unmanaged.
I am trying to deploy it to a different PC with Win2K, and again - a local,
default, instance of MSDE. I copied it to C:\program files\microsoft sql
server\mssql\binn just as on my pc, added it using "sp_addextendedproc
'xp_myproc', 'xp_myproc.dll'", but it gave the error:
ODBC: Msg 0, Level 16, State 1
Cannot load the DLL xp_myproc.dll, or one of the DLLs it references. Reason:
126(error not found).
The only thing I can think to be different is that one's got Win2000,
whereas my dev. PC has got XP. This shouldn't matter though, should it?
I checked that the names are the correct case, correct names of the
procedure and the DLL filename and everything.
What could be wrong?
Thanks
Sorry for the obvious question, but perhaps you added the dll file for the wrong instance of SQL
server?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Bonj" <Bonj@.discussions.microsoft.com> wrote in message
news:A412072A-1157-4908-8D07-96CDBE6788AC@.microsoft.com...
> Hi
> I have written an extended stored procedure DLL, thanks to the people who
> helped me on this.
> however i wonder if anyone could shed any light on my deployment issues that
> I'm having with it.
> Basically, I built it on my XP development PC with VC7.1. I can deploy it on
> this PC's local default instance of MSDE without problems. It uses no managed
> code whatsoever, and is compiled without /clr. Totally unmanaged.
> I am trying to deploy it to a different PC with Win2K, and again - a local,
> default, instance of MSDE. I copied it to C:\program files\microsoft sql
> server\mssql\binn just as on my pc, added it using "sp_addextendedproc
> 'xp_myproc', 'xp_myproc.dll'", but it gave the error:
> ODBC: Msg 0, Level 16, State 1
> Cannot load the DLL xp_myproc.dll, or one of the DLLs it references. Reason:
> 126(error not found).
> The only thing I can think to be different is that one's got Win2000,
> whereas my dev. PC has got XP. This shouldn't matter though, should it?
> I checked that the names are the correct case, correct names of the
> procedure and the DLL filename and everything.
> What could be wrong?
> Thanks
>
|||Nope. Like I say, the following is true of both dev machine, and deployment
target:
There's only one, default, instance of SQL Server (MSDE) on the machine. The
path of the binaries is
c:\program files\microsoft sql server\mssql\binn
and this is the only location where sqlservr.exe is found on the machine.
"Tibor Karaszi" wrote:

> Sorry for the obvious question, but perhaps you added the dll file for the wrong instance of SQL
> server?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Bonj" <Bonj@.discussions.microsoft.com> wrote in message
> news:A412072A-1157-4908-8D07-96CDBE6788AC@.microsoft.com...
>
>
|||I see, it was only a wild guess...
Re-reading the error message, it can also be that the DLL you produced in return references some
other DLL file, and this is the file that cannot be found. (I'm sorry if I'm stating the obvious
here... :-\).
I'm sure that there are tools out there to check DLL dependencies etc to investigate what the
problem might be. Perhaps some of the VC persons can pick up on that. ;-)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Bonj" <Bonj@.discussions.microsoft.com> wrote in message
news:18370E98-D316-4A4C-B5D7-BCCD553AC4FD@.microsoft.com...[vbcol=seagreen]
> Nope. Like I say, the following is true of both dev machine, and deployment
> target:
> There's only one, default, instance of SQL Server (MSDE) on the machine. The
> path of the binaries is
> c:\program files\microsoft sql server\mssql\binn
> and this is the only location where sqlservr.exe is found on the machine.
>
> "Tibor Karaszi" wrote:
|||Like I say, it does it the same with a 'blank' extended stored procedure...
i.e. just let the wizard create one, don't add any code, and compile. That
doesn't work on it either.
"Tibor Karaszi" wrote:

> I see, it was only a wild guess...
> Re-reading the error message, it can also be that the DLL you produced in return references some
> other DLL file, and this is the file that cannot be found. (I'm sorry if I'm stating the obvious
> here... :-\).
> I'm sure that there are tools out there to check DLL dependencies etc to investigate what the
> problem might be. Perhaps some of the VC persons can pick up on that. ;-)
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Bonj" <Bonj@.discussions.microsoft.com> wrote in message
> news:18370E98-D316-4A4C-B5D7-BCCD553AC4FD@.microsoft.com...
>
>
|||Possibly some stuff is linked in by the compiling environment. I haven't used C or C++ for some 12
years now, so this is only a guess...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Bonj" <Bonj@.discussions.microsoft.com> wrote in message
news:67E3A9A6-3BC7-4110-9188-8FC3996555A8@.microsoft.com...[vbcol=seagreen]
> Like I say, it does it the same with a 'blank' extended stored procedure...
> i.e. just let the wizard create one, don't add any code, and compile. That
> doesn't work on it either.
>
> "Tibor Karaszi" wrote:
SQL[vbcol=seagreen]
|||As you suggested I created a default extended stored procedure using
VS.NET'03 on WinXP...I used the depends.exe to determine the dependencies.
Depends.exe reported and error trying to find the OPENDS60.DLL. Perhaps this
is part of your problem...
"Bonj" wrote:
[vbcol=seagreen]
> Like I say, it does it the same with a 'blank' extended stored procedure...
> i.e. just let the wizard create one, don't add any code, and compile. That
> doesn't work on it either.
>
> "Tibor Karaszi" wrote:
|||oh right... i'll check it out when I get back to work..
Cheers for the investigation, appreciate it!
"Mike M" <Mike M@.discussions.microsoft.com> wrote in message
news:D91F9749-AED1-44C0-9AA0-6251A39EE6B2@.microsoft.com...[vbcol=seagreen]
> As you suggested I created a default extended stored procedure using
> VS.NET'03 on WinXP...I used the depends.exe to determine the dependencies.
> Depends.exe reported and error trying to find the OPENDS60.DLL. Perhaps
> this
> is part of your problem...
> "Bonj" wrote:
|||I ran depends.exe on it and it said this at the bottom in red:
Warning: At least one module has an unresolved import due to a missing
export function in a delay-load dependent module.
How can I find which one, and resolve it?
|||It didn't seem to be the opends60.dll, because the target machine has got it
in its SQL server "binn" directory.
"Bonj" <benjtaylor at hotpop d0t com> wrote in message
news:uXSnkr5tEHA.2072@.tk2msftngp13.phx.gbl...
> oh right... i'll check it out when I get back to work..
> Cheers for the investigation, appreciate it!
> "Mike M" <Mike M@.discussions.microsoft.com> wrote in message
> news:D91F9749-AED1-44C0-9AA0-6251A39EE6B2@.microsoft.com...
>

deploying extended stored proc

Hi
I have written an extended stored procedure DLL, thanks to the people who
helped me on this.
however i wonder if anyone could shed any light on my deployment issues that
I'm having with it.
Basically, I built it on my XP development PC with VC7.1. I can deploy it on
this PC's local default instance of MSDE without problems. It uses no manage
d
code whatsoever, and is compiled without /clr. Totally unmanaged.
I am trying to deploy it to a different PC with Win2K, and again - a local,
default, instance of MSDE. I copied it to C:\program files\microsoft sql
server\mssql\binn just as on my pc, added it using "sp_addextendedproc
'xp_myproc', 'xp_myproc.dll'", but it gave the error:
ODBC: Msg 0, Level 16, State 1
Cannot load the DLL xp_myproc.dll, or one of the DLLs it references. Reason:
126(error not found).
The only thing I can think to be different is that one's got Win2000,
whereas my dev. PC has got XP. This shouldn't matter though, should it?
I checked that the names are the correct case, correct names of the
procedure and the DLL filename and everything.
What could be wrong?
ThanksSorry for the obvious question, but perhaps you added the dll file for the w
rong instance of SQL
server?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Bonj" <Bonj@.discussions.microsoft.com> wrote in message
news:A412072A-1157-4908-8D07-96CDBE6788AC@.microsoft.com...
> Hi
> I have written an extended stored procedure DLL, thanks to the people who
> helped me on this.
> however i wonder if anyone could shed any light on my deployment issues th
at
> I'm having with it.
> Basically, I built it on my XP development PC with VC7.1. I can deploy it
on
> this PC's local default instance of MSDE without problems. It uses no mana
ged
> code whatsoever, and is compiled without /clr. Totally unmanaged.
> I am trying to deploy it to a different PC with Win2K, and again - a local
,
> default, instance of MSDE. I copied it to C:\program files\microsoft sql
> server\mssql\binn just as on my pc, added it using "sp_addextendedproc
> 'xp_myproc', 'xp_myproc.dll'", but it gave the error:
> ODBC: Msg 0, Level 16, State 1
> Cannot load the DLL xp_myproc.dll, or one of the DLLs it references. Reaso
n:
> 126(error not found).
> The only thing I can think to be different is that one's got Win2000,
> whereas my dev. PC has got XP. This shouldn't matter though, should it?
> I checked that the names are the correct case, correct names of the
> procedure and the DLL filename and everything.
> What could be wrong?
> Thanks
>|||Nope. Like I say, the following is true of both dev machine, and deployment
target:
There's only one, default, instance of SQL Server (MSDE) on the machine. The
path of the binaries is
c:\program files\microsoft sql server\mssql\binn
and this is the only location where sqlservr.exe is found on the machine.
"Tibor Karaszi" wrote:

> Sorry for the obvious question, but perhaps you added the dll file for the
wrong instance of SQL
> server?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Bonj" <Bonj@.discussions.microsoft.com> wrote in message
> news:A412072A-1157-4908-8D07-96CDBE6788AC@.microsoft.com...
>
>|||I see, it was only a wild guess...
Re-reading the error message, it can also be that the DLL you produced in re
turn references some
other DLL file, and this is the file that cannot be found. (I'm sorry if I'm
stating the obvious
here... :-\).
I'm sure that there are tools out there to check DLL dependencies etc to inv
estigate what the
problem might be. Perhaps some of the VC persons can pick up on that. ;-)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Bonj" <Bonj@.discussions.microsoft.com> wrote in message
news:18370E98-D316-4A4C-B5D7-BCCD553AC4FD@.microsoft.com...[vbcol=seagreen]
> Nope. Like I say, the following is true of both dev machine, and deploymen
t
> target:
> There's only one, default, instance of SQL Server (MSDE) on the machine. T
he
> path of the binaries is
> c:\program files\microsoft sql server\mssql\binn
> and this is the only location where sqlservr.exe is found on the machine.
>
> "Tibor Karaszi" wrote:
>|||Like I say, it does it the same with a 'blank' extended stored procedure...
i.e. just let the wizard create one, don't add any code, and compile. That
doesn't work on it either.
"Tibor Karaszi" wrote:

> I see, it was only a wild guess...
> Re-reading the error message, it can also be that the DLL you produced in
return references some
> other DLL file, and this is the file that cannot be found. (I'm sorry if I
'm stating the obvious
> here... :-\).
> I'm sure that there are tools out there to check DLL dependencies etc to i
nvestigate what the
> problem might be. Perhaps some of the VC persons can pick up on that. ;-)
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Bonj" <Bonj@.discussions.microsoft.com> wrote in message
> news:18370E98-D316-4A4C-B5D7-BCCD553AC4FD@.microsoft.com...
>
>|||Possibly some stuff is linked in by the compiling environment. I haven't use
d C or C++ for some 12
years now, so this is only a guess...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Bonj" <Bonj@.discussions.microsoft.com> wrote in message
news:67E3A9A6-3BC7-4110-9188-8FC3996555A8@.microsoft.com...[vbcol=seagreen]
> Like I say, it does it the same with a 'blank' extended stored procedure..
.
> i.e. just let the wizard create one, don't add any code, and compile. That
> doesn't work on it either.
>
> "Tibor Karaszi" wrote:
>
SQL[vbcol=seagreen]|||As you suggested I created a default extended stored procedure using
VS.NET'03 on WinXP...I used the depends.exe to determine the dependencies.
Depends.exe reported and error trying to find the OPENDS60.DLL. Perhaps thi
s
is part of your problem...
"Bonj" wrote:
[vbcol=seagreen]
> Like I say, it does it the same with a 'blank' extended stored procedure..
.
> i.e. just let the wizard create one, don't add any code, and compile. That
> doesn't work on it either.
>
> "Tibor Karaszi" wrote:
>|||oh right... i'll check it out when I get back to work..
Cheers for the investigation, appreciate it!
"Mike M" <Mike M@.discussions.microsoft.com> wrote in message
news:D91F9749-AED1-44C0-9AA0-6251A39EE6B2@.microsoft.com...[vbcol=seagreen]
> As you suggested I created a default extended stored procedure using
> VS.NET'03 on WinXP...I used the depends.exe to determine the dependencies.
> Depends.exe reported and error trying to find the OPENDS60.DLL. Perhaps
> this
> is part of your problem...
> "Bonj" wrote:
>|||I ran depends.exe on it and it said this at the bottom in red:
Warning: At least one module has an unresolved import due to a missing
export function in a delay-load dependent module.
How can I find which one, and resolve it?|||It didn't seem to be the opends60.dll, because the target machine has got it
in its SQL server "binn" directory.
"Bonj" <benjtaylor at hotpop d0t com> wrote in message
news:uXSnkr5tEHA.2072@.tk2msftngp13.phx.gbl...
> oh right... i'll check it out when I get back to work..
> Cheers for the investigation, appreciate it!
> "Mike M" <Mike M@.discussions.microsoft.com> wrote in message
> news:D91F9749-AED1-44C0-9AA0-6251A39EE6B2@.microsoft.com...
>

deploying extended stored proc

Hi
I have written an extended stored procedure DLL, thanks to the people who
helped me on this.
however i wonder if anyone could shed any light on my deployment issues that
I'm having with it.
Basically, I built it on my XP development PC with VC7.1. I can deploy it on
this PC's local default instance of MSDE without problems. It uses no managed
code whatsoever, and is compiled without /clr. Totally unmanaged.
I am trying to deploy it to a different PC with Win2K, and again - a local,
default, instance of MSDE. I copied it to C:\program files\microsoft sql
server\mssql\binn just as on my pc, added it using "sp_addextendedproc
'xp_myproc', 'xp_myproc.dll'", but it gave the error:
ODBC: Msg 0, Level 16, State 1
Cannot load the DLL xp_myproc.dll, or one of the DLLs it references. Reason:
126(error not found).
The only thing I can think to be different is that one's got Win2000,
whereas my dev. PC has got XP. This shouldn't matter though, should it?
I checked that the names are the correct case, correct names of the
procedure and the DLL filename and everything.
What could be wrong?
ThanksSorry for the obvious question, but perhaps you added the dll file for the wrong instance of SQL
server?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Bonj" <Bonj@.discussions.microsoft.com> wrote in message
news:A412072A-1157-4908-8D07-96CDBE6788AC@.microsoft.com...
> Hi
> I have written an extended stored procedure DLL, thanks to the people who
> helped me on this.
> however i wonder if anyone could shed any light on my deployment issues that
> I'm having with it.
> Basically, I built it on my XP development PC with VC7.1. I can deploy it on
> this PC's local default instance of MSDE without problems. It uses no managed
> code whatsoever, and is compiled without /clr. Totally unmanaged.
> I am trying to deploy it to a different PC with Win2K, and again - a local,
> default, instance of MSDE. I copied it to C:\program files\microsoft sql
> server\mssql\binn just as on my pc, added it using "sp_addextendedproc
> 'xp_myproc', 'xp_myproc.dll'", but it gave the error:
> ODBC: Msg 0, Level 16, State 1
> Cannot load the DLL xp_myproc.dll, or one of the DLLs it references. Reason:
> 126(error not found).
> The only thing I can think to be different is that one's got Win2000,
> whereas my dev. PC has got XP. This shouldn't matter though, should it?
> I checked that the names are the correct case, correct names of the
> procedure and the DLL filename and everything.
> What could be wrong?
> Thanks
>|||Nope. Like I say, the following is true of both dev machine, and deployment
target:
There's only one, default, instance of SQL Server (MSDE) on the machine. The
path of the binaries is
c:\program files\microsoft sql server\mssql\binn
and this is the only location where sqlservr.exe is found on the machine.
"Tibor Karaszi" wrote:
> Sorry for the obvious question, but perhaps you added the dll file for the wrong instance of SQL
> server?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Bonj" <Bonj@.discussions.microsoft.com> wrote in message
> news:A412072A-1157-4908-8D07-96CDBE6788AC@.microsoft.com...
> > Hi
> > I have written an extended stored procedure DLL, thanks to the people who
> > helped me on this.
> > however i wonder if anyone could shed any light on my deployment issues that
> > I'm having with it.
> > Basically, I built it on my XP development PC with VC7.1. I can deploy it on
> > this PC's local default instance of MSDE without problems. It uses no managed
> > code whatsoever, and is compiled without /clr. Totally unmanaged.
> > I am trying to deploy it to a different PC with Win2K, and again - a local,
> > default, instance of MSDE. I copied it to C:\program files\microsoft sql
> > server\mssql\binn just as on my pc, added it using "sp_addextendedproc
> > 'xp_myproc', 'xp_myproc.dll'", but it gave the error:
> > ODBC: Msg 0, Level 16, State 1
> > Cannot load the DLL xp_myproc.dll, or one of the DLLs it references. Reason:
> > 126(error not found).
> >
> > The only thing I can think to be different is that one's got Win2000,
> > whereas my dev. PC has got XP. This shouldn't matter though, should it?
> >
> > I checked that the names are the correct case, correct names of the
> > procedure and the DLL filename and everything.
> >
> > What could be wrong?
> >
> > Thanks
> >
>
>|||I see, it was only a wild guess...
Re-reading the error message, it can also be that the DLL you produced in return references some
other DLL file, and this is the file that cannot be found. (I'm sorry if I'm stating the obvious
here... :-\).
I'm sure that there are tools out there to check DLL dependencies etc to investigate what the
problem might be. Perhaps some of the VC persons can pick up on that. ;-)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Bonj" <Bonj@.discussions.microsoft.com> wrote in message
news:18370E98-D316-4A4C-B5D7-BCCD553AC4FD@.microsoft.com...
> Nope. Like I say, the following is true of both dev machine, and deployment
> target:
> There's only one, default, instance of SQL Server (MSDE) on the machine. The
> path of the binaries is
> c:\program files\microsoft sql server\mssql\binn
> and this is the only location where sqlservr.exe is found on the machine.
>
> "Tibor Karaszi" wrote:
> > Sorry for the obvious question, but perhaps you added the dll file for the wrong instance of SQL
> > server?
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > http://www.karaszi.com/sqlserver/default.asp
> > http://www.solidqualitylearning.com/
> >
> >
> > "Bonj" <Bonj@.discussions.microsoft.com> wrote in message
> > news:A412072A-1157-4908-8D07-96CDBE6788AC@.microsoft.com...
> > > Hi
> > > I have written an extended stored procedure DLL, thanks to the people who
> > > helped me on this.
> > > however i wonder if anyone could shed any light on my deployment issues that
> > > I'm having with it.
> > > Basically, I built it on my XP development PC with VC7.1. I can deploy it on
> > > this PC's local default instance of MSDE without problems. It uses no managed
> > > code whatsoever, and is compiled without /clr. Totally unmanaged.
> > > I am trying to deploy it to a different PC with Win2K, and again - a local,
> > > default, instance of MSDE. I copied it to C:\program files\microsoft sql
> > > server\mssql\binn just as on my pc, added it using "sp_addextendedproc
> > > 'xp_myproc', 'xp_myproc.dll'", but it gave the error:
> > > ODBC: Msg 0, Level 16, State 1
> > > Cannot load the DLL xp_myproc.dll, or one of the DLLs it references. Reason:
> > > 126(error not found).
> > >
> > > The only thing I can think to be different is that one's got Win2000,
> > > whereas my dev. PC has got XP. This shouldn't matter though, should it?
> > >
> > > I checked that the names are the correct case, correct names of the
> > > procedure and the DLL filename and everything.
> > >
> > > What could be wrong?
> > >
> > > Thanks
> > >
> >
> >
> >|||Like I say, it does it the same with a 'blank' extended stored procedure...
i.e. just let the wizard create one, don't add any code, and compile. That
doesn't work on it either.
"Tibor Karaszi" wrote:
> I see, it was only a wild guess...
> Re-reading the error message, it can also be that the DLL you produced in return references some
> other DLL file, and this is the file that cannot be found. (I'm sorry if I'm stating the obvious
> here... :-\).
> I'm sure that there are tools out there to check DLL dependencies etc to investigate what the
> problem might be. Perhaps some of the VC persons can pick up on that. ;-)
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Bonj" <Bonj@.discussions.microsoft.com> wrote in message
> news:18370E98-D316-4A4C-B5D7-BCCD553AC4FD@.microsoft.com...
> > Nope. Like I say, the following is true of both dev machine, and deployment
> > target:
> > There's only one, default, instance of SQL Server (MSDE) on the machine. The
> > path of the binaries is
> > c:\program files\microsoft sql server\mssql\binn
> > and this is the only location where sqlservr.exe is found on the machine.
> >
> >
> > "Tibor Karaszi" wrote:
> >
> > > Sorry for the obvious question, but perhaps you added the dll file for the wrong instance of SQL
> > > server?
> > >
> > > --
> > > Tibor Karaszi, SQL Server MVP
> > > http://www.karaszi.com/sqlserver/default.asp
> > > http://www.solidqualitylearning.com/
> > >
> > >
> > > "Bonj" <Bonj@.discussions.microsoft.com> wrote in message
> > > news:A412072A-1157-4908-8D07-96CDBE6788AC@.microsoft.com...
> > > > Hi
> > > > I have written an extended stored procedure DLL, thanks to the people who
> > > > helped me on this.
> > > > however i wonder if anyone could shed any light on my deployment issues that
> > > > I'm having with it.
> > > > Basically, I built it on my XP development PC with VC7.1. I can deploy it on
> > > > this PC's local default instance of MSDE without problems. It uses no managed
> > > > code whatsoever, and is compiled without /clr. Totally unmanaged.
> > > > I am trying to deploy it to a different PC with Win2K, and again - a local,
> > > > default, instance of MSDE. I copied it to C:\program files\microsoft sql
> > > > server\mssql\binn just as on my pc, added it using "sp_addextendedproc
> > > > 'xp_myproc', 'xp_myproc.dll'", but it gave the error:
> > > > ODBC: Msg 0, Level 16, State 1
> > > > Cannot load the DLL xp_myproc.dll, or one of the DLLs it references. Reason:
> > > > 126(error not found).
> > > >
> > > > The only thing I can think to be different is that one's got Win2000,
> > > > whereas my dev. PC has got XP. This shouldn't matter though, should it?
> > > >
> > > > I checked that the names are the correct case, correct names of the
> > > > procedure and the DLL filename and everything.
> > > >
> > > > What could be wrong?
> > > >
> > > > Thanks
> > > >
> > >
> > >
> > >
>
>|||Possibly some stuff is linked in by the compiling environment. I haven't used C or C++ for some 12
years now, so this is only a guess...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Bonj" <Bonj@.discussions.microsoft.com> wrote in message
news:67E3A9A6-3BC7-4110-9188-8FC3996555A8@.microsoft.com...
> Like I say, it does it the same with a 'blank' extended stored procedure...
> i.e. just let the wizard create one, don't add any code, and compile. That
> doesn't work on it either.
>
> "Tibor Karaszi" wrote:
> > I see, it was only a wild guess...
> >
> > Re-reading the error message, it can also be that the DLL you produced in return references some
> > other DLL file, and this is the file that cannot be found. (I'm sorry if I'm stating the obvious
> > here... :-\).
> >
> > I'm sure that there are tools out there to check DLL dependencies etc to investigate what the
> > problem might be. Perhaps some of the VC persons can pick up on that. ;-)
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > http://www.karaszi.com/sqlserver/default.asp
> > http://www.solidqualitylearning.com/
> >
> >
> > "Bonj" <Bonj@.discussions.microsoft.com> wrote in message
> > news:18370E98-D316-4A4C-B5D7-BCCD553AC4FD@.microsoft.com...
> > > Nope. Like I say, the following is true of both dev machine, and deployment
> > > target:
> > > There's only one, default, instance of SQL Server (MSDE) on the machine. The
> > > path of the binaries is
> > > c:\program files\microsoft sql server\mssql\binn
> > > and this is the only location where sqlservr.exe is found on the machine.
> > >
> > >
> > > "Tibor Karaszi" wrote:
> > >
> > > > Sorry for the obvious question, but perhaps you added the dll file for the wrong instance of
SQL
> > > > server?
> > > >
> > > > --
> > > > Tibor Karaszi, SQL Server MVP
> > > > http://www.karaszi.com/sqlserver/default.asp
> > > > http://www.solidqualitylearning.com/
> > > >
> > > >
> > > > "Bonj" <Bonj@.discussions.microsoft.com> wrote in message
> > > > news:A412072A-1157-4908-8D07-96CDBE6788AC@.microsoft.com...
> > > > > Hi
> > > > > I have written an extended stored procedure DLL, thanks to the people who
> > > > > helped me on this.
> > > > > however i wonder if anyone could shed any light on my deployment issues that
> > > > > I'm having with it.
> > > > > Basically, I built it on my XP development PC with VC7.1. I can deploy it on
> > > > > this PC's local default instance of MSDE without problems. It uses no managed
> > > > > code whatsoever, and is compiled without /clr. Totally unmanaged.
> > > > > I am trying to deploy it to a different PC with Win2K, and again - a local,
> > > > > default, instance of MSDE. I copied it to C:\program files\microsoft sql
> > > > > server\mssql\binn just as on my pc, added it using "sp_addextendedproc
> > > > > 'xp_myproc', 'xp_myproc.dll'", but it gave the error:
> > > > > ODBC: Msg 0, Level 16, State 1
> > > > > Cannot load the DLL xp_myproc.dll, or one of the DLLs it references. Reason:
> > > > > 126(error not found).
> > > > >
> > > > > The only thing I can think to be different is that one's got Win2000,
> > > > > whereas my dev. PC has got XP. This shouldn't matter though, should it?
> > > > >
> > > > > I checked that the names are the correct case, correct names of the
> > > > > procedure and the DLL filename and everything.
> > > > >
> > > > > What could be wrong?
> > > > >
> > > > > Thanks
> > > > >
> > > >
> > > >
> > > >
> >
> >
> >|||As you suggested I created a default extended stored procedure using
VS.NET'03 on WinXP...I used the depends.exe to determine the dependencies.
Depends.exe reported and error trying to find the OPENDS60.DLL. Perhaps this
is part of your problem...
"Bonj" wrote:
> Like I say, it does it the same with a 'blank' extended stored procedure...
> i.e. just let the wizard create one, don't add any code, and compile. That
> doesn't work on it either.
>
> "Tibor Karaszi" wrote:
> > I see, it was only a wild guess...
> >
> > Re-reading the error message, it can also be that the DLL you produced in return references some
> > other DLL file, and this is the file that cannot be found. (I'm sorry if I'm stating the obvious
> > here... :-\).
> >
> > I'm sure that there are tools out there to check DLL dependencies etc to investigate what the
> > problem might be. Perhaps some of the VC persons can pick up on that. ;-)
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > http://www.karaszi.com/sqlserver/default.asp
> > http://www.solidqualitylearning.com/
> >
> >
> > "Bonj" <Bonj@.discussions.microsoft.com> wrote in message
> > news:18370E98-D316-4A4C-B5D7-BCCD553AC4FD@.microsoft.com...
> > > Nope. Like I say, the following is true of both dev machine, and deployment
> > > target:
> > > There's only one, default, instance of SQL Server (MSDE) on the machine. The
> > > path of the binaries is
> > > c:\program files\microsoft sql server\mssql\binn
> > > and this is the only location where sqlservr.exe is found on the machine.
> > >
> > >
> > > "Tibor Karaszi" wrote:
> > >
> > > > Sorry for the obvious question, but perhaps you added the dll file for the wrong instance of SQL
> > > > server?
> > > >
> > > > --
> > > > Tibor Karaszi, SQL Server MVP
> > > > http://www.karaszi.com/sqlserver/default.asp
> > > > http://www.solidqualitylearning.com/
> > > >
> > > >
> > > > "Bonj" <Bonj@.discussions.microsoft.com> wrote in message
> > > > news:A412072A-1157-4908-8D07-96CDBE6788AC@.microsoft.com...
> > > > > Hi
> > > > > I have written an extended stored procedure DLL, thanks to the people who
> > > > > helped me on this.
> > > > > however i wonder if anyone could shed any light on my deployment issues that
> > > > > I'm having with it.
> > > > > Basically, I built it on my XP development PC with VC7.1. I can deploy it on
> > > > > this PC's local default instance of MSDE without problems. It uses no managed
> > > > > code whatsoever, and is compiled without /clr. Totally unmanaged.
> > > > > I am trying to deploy it to a different PC with Win2K, and again - a local,
> > > > > default, instance of MSDE. I copied it to C:\program files\microsoft sql
> > > > > server\mssql\binn just as on my pc, added it using "sp_addextendedproc
> > > > > 'xp_myproc', 'xp_myproc.dll'", but it gave the error:
> > > > > ODBC: Msg 0, Level 16, State 1
> > > > > Cannot load the DLL xp_myproc.dll, or one of the DLLs it references. Reason:
> > > > > 126(error not found).
> > > > >
> > > > > The only thing I can think to be different is that one's got Win2000,
> > > > > whereas my dev. PC has got XP. This shouldn't matter though, should it?
> > > > >
> > > > > I checked that the names are the correct case, correct names of the
> > > > > procedure and the DLL filename and everything.
> > > > >
> > > > > What could be wrong?
> > > > >
> > > > > Thanks
> > > > >
> > > >
> > > >
> > > >
> >
> >
> >|||oh right... i'll check it out when I get back to work..
Cheers for the investigation, appreciate it!
"Mike M" <Mike M@.discussions.microsoft.com> wrote in message
news:D91F9749-AED1-44C0-9AA0-6251A39EE6B2@.microsoft.com...
> As you suggested I created a default extended stored procedure using
> VS.NET'03 on WinXP...I used the depends.exe to determine the dependencies.
> Depends.exe reported and error trying to find the OPENDS60.DLL. Perhaps
> this
> is part of your problem...
> "Bonj" wrote:
>> Like I say, it does it the same with a 'blank' extended stored
>> procedure...
>> i.e. just let the wizard create one, don't add any code, and compile.
>> That
>> doesn't work on it either.
>>
>> "Tibor Karaszi" wrote:
>> > I see, it was only a wild guess...
>> >
>> > Re-reading the error message, it can also be that the DLL you produced
>> > in return references some
>> > other DLL file, and this is the file that cannot be found. (I'm sorry
>> > if I'm stating the obvious
>> > here... :-\).
>> >
>> > I'm sure that there are tools out there to check DLL dependencies etc
>> > to investigate what the
>> > problem might be. Perhaps some of the VC persons can pick up on that.
>> > ;-)
>> >
>> > --
>> > Tibor Karaszi, SQL Server MVP
>> > http://www.karaszi.com/sqlserver/default.asp
>> > http://www.solidqualitylearning.com/
>> >
>> >
>> > "Bonj" <Bonj@.discussions.microsoft.com> wrote in message
>> > news:18370E98-D316-4A4C-B5D7-BCCD553AC4FD@.microsoft.com...
>> > > Nope. Like I say, the following is true of both dev machine, and
>> > > deployment
>> > > target:
>> > > There's only one, default, instance of SQL Server (MSDE) on the
>> > > machine. The
>> > > path of the binaries is
>> > > c:\program files\microsoft sql server\mssql\binn
>> > > and this is the only location where sqlservr.exe is found on the
>> > > machine.
>> > >
>> > >
>> > > "Tibor Karaszi" wrote:
>> > >
>> > > > Sorry for the obvious question, but perhaps you added the dll file
>> > > > for the wrong instance of SQL
>> > > > server?
>> > > >
>> > > > --
>> > > > Tibor Karaszi, SQL Server MVP
>> > > > http://www.karaszi.com/sqlserver/default.asp
>> > > > http://www.solidqualitylearning.com/
>> > > >
>> > > >
>> > > > "Bonj" <Bonj@.discussions.microsoft.com> wrote in message
>> > > > news:A412072A-1157-4908-8D07-96CDBE6788AC@.microsoft.com...
>> > > > > Hi
>> > > > > I have written an extended stored procedure DLL, thanks to the
>> > > > > people who
>> > > > > helped me on this.
>> > > > > however i wonder if anyone could shed any light on my deployment
>> > > > > issues that
>> > > > > I'm having with it.
>> > > > > Basically, I built it on my XP development PC with VC7.1. I can
>> > > > > deploy it on
>> > > > > this PC's local default instance of MSDE without problems. It
>> > > > > uses no managed
>> > > > > code whatsoever, and is compiled without /clr. Totally unmanaged.
>> > > > > I am trying to deploy it to a different PC with Win2K, and
>> > > > > again - a local,
>> > > > > default, instance of MSDE. I copied it to C:\program
>> > > > > files\microsoft sql
>> > > > > server\mssql\binn just as on my pc, added it using
>> > > > > "sp_addextendedproc
>> > > > > 'xp_myproc', 'xp_myproc.dll'", but it gave the error:
>> > > > > ODBC: Msg 0, Level 16, State 1
>> > > > > Cannot load the DLL xp_myproc.dll, or one of the DLLs it
>> > > > > references. Reason:
>> > > > > 126(error not found).
>> > > > >
>> > > > > The only thing I can think to be different is that one's got
>> > > > > Win2000,
>> > > > > whereas my dev. PC has got XP. This shouldn't matter though,
>> > > > > should it?
>> > > > >
>> > > > > I checked that the names are the correct case, correct names of
>> > > > > the
>> > > > > procedure and the DLL filename and everything.
>> > > > >
>> > > > > What could be wrong?
>> > > > >
>> > > > > Thanks
>> > > > >
>> > > >
>> > > >
>> > > >
>> >
>> >
>> >|||I ran depends.exe on it and it said this at the bottom in red:
Warning: At least one module has an unresolved import due to a missing
export function in a delay-load dependent module.
How can I find which one, and resolve it?|||It didn't seem to be the opends60.dll, because the target machine has got it
in its SQL server "binn" directory.
"Bonj" <benjtaylor at hotpop d0t com> wrote in message
news:uXSnkr5tEHA.2072@.tk2msftngp13.phx.gbl...
> oh right... i'll check it out when I get back to work..
> Cheers for the investigation, appreciate it!
> "Mike M" <Mike M@.discussions.microsoft.com> wrote in message
> news:D91F9749-AED1-44C0-9AA0-6251A39EE6B2@.microsoft.com...
>> As you suggested I created a default extended stored procedure using
>> VS.NET'03 on WinXP...I used the depends.exe to determine the
>> dependencies.
>> Depends.exe reported and error trying to find the OPENDS60.DLL. Perhaps
>> this
>> is part of your problem...
>> "Bonj" wrote:
>> Like I say, it does it the same with a 'blank' extended stored
>> procedure...
>> i.e. just let the wizard create one, don't add any code, and compile.
>> That
>> doesn't work on it either.
>>
>> "Tibor Karaszi" wrote:
>> > I see, it was only a wild guess...
>> >
>> > Re-reading the error message, it can also be that the DLL you produced
>> > in return references some
>> > other DLL file, and this is the file that cannot be found. (I'm sorry
>> > if I'm stating the obvious
>> > here... :-\).
>> >
>> > I'm sure that there are tools out there to check DLL dependencies etc
>> > to investigate what the
>> > problem might be. Perhaps some of the VC persons can pick up on that.
>> > ;-)
>> >
>> > --
>> > Tibor Karaszi, SQL Server MVP
>> > http://www.karaszi.com/sqlserver/default.asp
>> > http://www.solidqualitylearning.com/
>> >
>> >
>> > "Bonj" <Bonj@.discussions.microsoft.com> wrote in message
>> > news:18370E98-D316-4A4C-B5D7-BCCD553AC4FD@.microsoft.com...
>> > > Nope. Like I say, the following is true of both dev machine, and
>> > > deployment
>> > > target:
>> > > There's only one, default, instance of SQL Server (MSDE) on the
>> > > machine. The
>> > > path of the binaries is
>> > > c:\program files\microsoft sql server\mssql\binn
>> > > and this is the only location where sqlservr.exe is found on the
>> > > machine.
>> > >
>> > >
>> > > "Tibor Karaszi" wrote:
>> > >
>> > > > Sorry for the obvious question, but perhaps you added the dll file
>> > > > for the wrong instance of SQL
>> > > > server?
>> > > >
>> > > > --
>> > > > Tibor Karaszi, SQL Server MVP
>> > > > http://www.karaszi.com/sqlserver/default.asp
>> > > > http://www.solidqualitylearning.com/
>> > > >
>> > > >
>> > > > "Bonj" <Bonj@.discussions.microsoft.com> wrote in message
>> > > > news:A412072A-1157-4908-8D07-96CDBE6788AC@.microsoft.com...
>> > > > > Hi
>> > > > > I have written an extended stored procedure DLL, thanks to the
>> > > > > people who
>> > > > > helped me on this.
>> > > > > however i wonder if anyone could shed any light on my deployment
>> > > > > issues that
>> > > > > I'm having with it.
>> > > > > Basically, I built it on my XP development PC with VC7.1. I can
>> > > > > deploy it on
>> > > > > this PC's local default instance of MSDE without problems. It
>> > > > > uses no managed
>> > > > > code whatsoever, and is compiled without /clr. Totally
>> > > > > unmanaged.
>> > > > > I am trying to deploy it to a different PC with Win2K, and
>> > > > > again - a local,
>> > > > > default, instance of MSDE. I copied it to C:\program
>> > > > > files\microsoft sql
>> > > > > server\mssql\binn just as on my pc, added it using
>> > > > > "sp_addextendedproc
>> > > > > 'xp_myproc', 'xp_myproc.dll'", but it gave the error:
>> > > > > ODBC: Msg 0, Level 16, State 1
>> > > > > Cannot load the DLL xp_myproc.dll, or one of the DLLs it
>> > > > > references. Reason:
>> > > > > 126(error not found).
>> > > > >
>> > > > > The only thing I can think to be different is that one's got
>> > > > > Win2000,
>> > > > > whereas my dev. PC has got XP. This shouldn't matter though,
>> > > > > should it?
>> > > > >
>> > > > > I checked that the names are the correct case, correct names of
>> > > > > the
>> > > > > procedure and the DLL filename and everything.
>> > > > >
>> > > > > What could be wrong?
>> > > > >
>> > > > > Thanks
>> > > > >
>> > > >
>> > > >
>> > > >
>> >
>> >
>> >
>

Deploying AS to Production

Hi,

Is this the right deployment procedure: I copied the whole project to production server and re-processes the cube. Does the OLAP database resides in \bin or is it the one in C:\Program Files\Microsoft SQL Server\MSSQL.2\OLAP\Data? What's the extension name?

Thanks alot!

cherriesh

Copy the whole project is not really required. The database in the \bin folder is the one that was last built from the BI Development Studio (BIDS), but you do not deploy this file by copying it. In your program files folder where you have installed BIDS you should have an Analysis Services folder with an Analysis Services deployment wizard. This wizard allows you to do a number of things that are useful when deploying to production such as changing or retaining data source connection strings and retaining roles and partitions which may be different between dev and prod.

Also from BIDS, you can setup different profiles and change the target server for the deploy operation from BIDS, but if you needed to change data source connections you would need to remember to do that yourself.

|||

Hi,

After deployment, does the AS database reside in the drive where Analysis Services resides? I'm afraid in the server it is installed in C:\ with only 10GB instead of in the D:\ which has much greater space capacity..

cherriesh

|||

Hi,

If you open up the management studio and right click on the server instance and look at the properties. The Property DataDir will tell you where it's storing the databases. You can change it if it is wrong from C: to D: and you might want to change the backup and log directories property too (BackupDir, LogDir).

Hope that helps

Matt

|||

Hi,

Done with LogDir, BackUpDir and DataDir. I also saw the AllowedBrowsingFolder that is set to :

C:\Program Files\Microsoft SQL Server\MSSQL.3\OLAP\Log|C:\Program Files\Microsoft SQL Server\MSSQL.3\OLAP\Backup

Do I have to change this also?

I've done the Deployment wizard and I have generated the .xmla script. How to proceed from there?

cherriesh

|||

Hi,

Probably worth doing the AllowedBrowsingFolder to, it populates the drop down when you are backup and restore, various things like that.

On the assumption your script looks something like:

<Alter AllowCreate="true" ObjectExpansion="ObjectProperties" xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">

<Object />

<ObjectDefinition>

<Server xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlnsdl2="http://schemas.microsoft.com/analysisservices/2003/engine/2" xmlnsdl2_2="http://schemas.microsoft.com/analysisservices/2003/engine/2/2">

<ID>SERVER</ID>

<Name>SERVER</Name>

<ServerProperties>

<ServerProperty>

<Name>BackupDir</Name>

<Value>D:\Microsoft SQL Server\MSSQL.4\OLAP\Backup</Value>

</ServerProperty>

</ServerProperties>

</Server>

</ObjectDefinition>

</Alter>

Just open an xmla query, run it on the server and restart the service. It should in theory create the directories, pretty sure it did when i have done this before. If there is anything already on the AS Server, best to back that up first then restore it in the new location.

Any problems, just post again.

Matt

|||

hi,

sorry im really new to this. how do i run this .xmla in the server? what command?

thanks a lot!

cherriesh

|||

Hi,

Not to worry. If you open up management studio, click on File, then New, Analysis Services XMLA Query. Type in the server you are changing into "Server Name"

Or there is an icon on the query tool bar, has XMLA in green on it, if you hover over it it will say "Analysis Services XMLA Query".

An Alternative to not running XMLA:

If you are in management studio, connect to the server and right click the server in the object explorer. You can manually change the settings in the properties there.

If I didn't make any sense:

http://www.microsoft.com/technet/prodtechnol/sql/2005/ssasproperties.mspx

(Figures 1 and 2 are the wrong way around though)

remember to restart it the service

Hope that helps

Matt

|||

Thanks a lot! this really helps. is it the AS service that i need to restart? because, when i ran the .xmla, i could already see the AS database in the Management Studio. Does it really important to restart?

thanks!

cherriesh

|||

The changes you make won't actually take effect until you do, just the AS service.

No problem

Matt

Deploying AS to Production

Hi,

Is this the right deployment procedure: I copied the whole project to production server and re-processes the cube. Does the OLAP database resides in \bin or is it the one in C:\Program Files\Microsoft SQL Server\MSSQL.2\OLAP\Data? What's the extension name?

Thanks alot!

cherriesh

Copy the whole project is not really required. The database in the \bin folder is the one that was last built from the BI Development Studio (BIDS), but you do not deploy this file by copying it. In your program files folder where you have installed BIDS you should have an Analysis Services folder with an Analysis Services deployment wizard. This wizard allows you to do a number of things that are useful when deploying to production such as changing or retaining data source connection strings and retaining roles and partitions which may be different between dev and prod.

Also from BIDS, you can setup different profiles and change the target server for the deploy operation from BIDS, but if you needed to change data source connections you would need to remember to do that yourself.

|||

Hi,

After deployment, does the AS database reside in the drive where Analysis Services resides? I'm afraid in the server it is installed in C:\ with only 10GB instead of in the D:\ which has much greater space capacity..

cherriesh

|||

Hi,

If you open up the management studio and right click on the server instance and look at the properties. The Property DataDir will tell you where it's storing the databases. You can change it if it is wrong from C: to D: and you might want to change the backup and log directories property too (BackupDir, LogDir).

Hope that helps

Matt

|||

Hi,

Done with LogDir, BackUpDir and DataDir. I also saw the AllowedBrowsingFolder that is set to :

C:\Program Files\Microsoft SQL Server\MSSQL.3\OLAP\Log|C:\Program Files\Microsoft SQL Server\MSSQL.3\OLAP\Backup

Do I have to change this also?

I've done the Deployment wizard and I have generated the .xmla script. How to proceed from there?

cherriesh

|||

Hi,

Probably worth doing the AllowedBrowsingFolder to, it populates the drop down when you are backup and restore, various things like that.

On the assumption your script looks something like:

<Alter AllowCreate="true" ObjectExpansion="ObjectProperties" xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">

<Object />

<ObjectDefinition>

<Server xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlnsdl2="http://schemas.microsoft.com/analysisservices/2003/engine/2" xmlnsdl2_2="http://schemas.microsoft.com/analysisservices/2003/engine/2/2">

<ID>SERVER</ID>

<Name>SERVER</Name>

<ServerProperties>

<ServerProperty>

<Name>BackupDir</Name>

<Value>D:\Microsoft SQL Server\MSSQL.4\OLAP\Backup</Value>

</ServerProperty>

</ServerProperties>

</Server>

</ObjectDefinition>

</Alter>

Just open an xmla query, run it on the server and restart the service. It should in theory create the directories, pretty sure it did when i have done this before. If there is anything already on the AS Server, best to back that up first then restore it in the new location.

Any problems, just post again.

Matt

|||

hi,

sorry im really new to this. how do i run this .xmla in the server? what command?

thanks a lot!

cherriesh

|||

Hi,

Not to worry. If you open up management studio, click on File, then New, Analysis Services XMLA Query. Type in the server you are changing into "Server Name"

Or there is an icon on the query tool bar, has XMLA in green on it, if you hover over it it will say "Analysis Services XMLA Query".

An Alternative to not running XMLA:

If you are in management studio, connect to the server and right click the server in the object explorer. You can manually change the settings in the properties there.

If I didn't make any sense:

http://www.microsoft.com/technet/prodtechnol/sql/2005/ssasproperties.mspx

(Figures 1 and 2 are the wrong way around though)

remember to restart it the service

Hope that helps

Matt

|||

Thanks a lot! this really helps. is it the AS service that i need to restart? because, when i ran the .xmla, i could already see the AS database in the Management Studio. Does it really important to restart?

thanks!

cherriesh

|||

The changes you make won't actually take effect until you do, just the AS service.

No problem

Matt

Depending on the selection of the user of the asp.net web-form how can i do changes in the

Hi frdz,

  I have created the following stored procedure in sql server 2005.

 In my database i have one option for the payment mode which can be done thru cash or credit(cheque).  

 I have created my web-application in asp.net with C# 2005.

 There i have a dropdownlist box for the user to select the option whether wants to do the payment thru cash or cheque.

Depending on that selection if user selects cheque then all the reqt for cheque like it's no,dt,bankname etc...are visible.

but if user selects the option as cash then the cheque details become invisible.

Depending on the selection of the user of the asp.net web-form how can i do changes in the stored procedure...  

i can write the condition like

if paymentmode=cash

then ........

else

..........

but where and how can it be written ...pls tell me

thanxs in adv...u can go thru my below SP  

ALTER PROCEDURE MiscellaneousStoredProcedure @.miscidint output, @.storenamevarchar(20),--store name to storeid @.accountnamevarchar(20), @.groupnamevarchar(20), @.paymentdtdatetime,@.payeenamevarchar(30),@.paymodevarchar(20),@.banknamevarchar(50), @.chqdtdatetime, @.chqnovarchar(20), @.amtnumeric(10, 2),@.balnumeric(10, 2), @.remarksvarchar(50)asdeclare@.storeidint,@.accountidint,@.groupidintbeginset nocount onselect @.miscid =isnull(max(@.miscid),0) + 1from miscellaneourpayifexists (select *from storemasterwhere storename = @.storename)select @.storeid = storeidfrom storemasterwhere storename = @.storenameifexists (select *from accountmasterwhere accountname =@.accountname)select @.accountid = accountidfrom accountmasterwhere accountname =@.accountnameifexists (select *from accountgroupmasterwhere groupname=@.groupname)select @.groupid=groupidfrom accountgroupmasterwhere groupname=@.groupnamebegin transactioninsert into miscellaneourpay(miscid,storeid,accountid,groupid,paymentdt,paymode,payeename,bankname,chqdt,chqno,amt,bal, remarks)values( @.miscid,@.storeid,@.accountid,@.groupid,@.paymentdt,@.paymode,@.payeename,@.bankname,@.chqdt,@.chqno,@.amt,@.bal,@.remarks)commit transaction end

What about creating two different stored Proceedures for both cases?|||

Hi,

Try to use two different stroed procedure for cash and credit. The value from your DropDownList decides which one to use. In this way,you can make your stored procedure easier.

Thanks.

Dependencies not correct with temporary tables --> replication is failing

Hello all,

here is a stored procedure I have:

CREATE PROCEDURE spU_GUI_AppliqueConditionFinancementPourGuichet
(
@.GuichetId int,
@.Validateur nvarchar(40)
)
AS
CREATE TABLE #tReservations (ReservationId int)

IF (dbo.GetSiGuichetEnRegle(@.GuichetId) = 0)
INSERT #tReservations
EXECUTE spU_GUI_AppliquePerteFinancement @.GuichetID, @.Validateur
ELSE
INSERT #tReservations
EXECUTE spU_GUI_AppliquePerteAgrement @.GuichetID, @.Validateur

SELECT GR.Id,
dbo.FormateNoms(GR.Name) AS Names
FROM #tReservations
LEFT JOIN AnotherTable GR ON GR.Id = AnotherTable.id
DROP TABLE #tReservations
GO

The creation is ok but when I look to the dependencies, I see that it depends on GetSiGuichetEnRegle only.

For me, it shall also depend on

AnotherTable

spU_GUI_AppliquePerteFinancement

spU_GUI_AppliquePerteAgrement

FormateNoms

Apparently the dependencies are not calculated correctly because I'm using a temporary table.

My problem is that I have updated this stored procedures (and the two other that I call) to add a new parameter. As a consequence, when I do a replication, this is failing saying that I have an extra parameter. I imagine that because my dependencies are not correct, the replication is not occuring in the correct order and so it's still using the old definition of the stored procedure.

Do you have any idea on how I can force the dependencies to be calculated correctly ?

Thanks

Did the error happen when you are applying a snapshot or when the distribution agent is applying a DDL change for the stored procedure update? If this happened while the snapshot was being delivered to the subscriber, I have the following questions for you:

1) Are you using 'drop' as the pre-creation command for the stored procedure articles? If so, you should theoretically not be seeing the error that you saw when the snapshot was delivered to the subscriber since the referenced procedures would either have been drop (or recreated with the new definition).

2) Are the referenced stored procedures (spU_GUI_AppliquePerteFinancement and spU_GUI_AppliquePerteAgrement) included in the publication as well? (It never hurts to ask the obvious.)

3) It would be great if you can post history messages of the distribution agent when the snapshot was applied, I just want to see the relative order of how the objects that you mentioned are applied. I will also be interested to see the history messages from the snapshot agent.

There is an undocumented -EnableStoredProcedureDependenciesReevaluation 1 switch in the snapshot agent which forces the snapshot agent to compute dependency ordering for stored procedure articles in a more accurate manner but I have a feeling that the real problem is something else.

-Raymond

|||

Hello Raymond,

Indeed there seems to be problems with the dependencies between stored procedures and tables, but the main issue here seems to be to be related to the latest problem we spoke together

After isolating the problem, I've found that the stored procedure that could not be replicated contains a "INSERT INTO" statement.

I will try a work-around by playing with post-replication scripts, while waiting for SP2 ;-)

Anyway, I will investigate the solutions you give for the dependencies

Pierre-Emmanuel

Deny user roles from a Stored Procedure.

Hello,

I am trying to deny a user from processing a query, however the user still seems to be able to see the procedure.

I have executed the following command on both the 'Public' and specific user name.

DENY EXECUTE ON [OBJECT_NAME] TO [PUBLIC]

However when i run the command 'sp_helprotect OBJECT_NAME' afterwards it shows that deny is set on 'Public', yet the user can still run the procedure from a web report... I have tried restarting IIS and this also does nothing, any ideas?

Thanks,

RichAre you using windows authentication? If so, make sure that a Windows group does not have execute authority on the procedure. I would also check to make sure the user acount that is the problem does not have an elevated role like db_owner or a system role like sysadmin. I'm assuming that you verified that the application is actually using the login you have been denying permissions on.|||

Quote:

Originally Posted by rob313

Are you using windows authentication? If so, make sure that a Windows group does not have execute authority on the procedure. I would also check to make sure the user acount that is the problem does not have an elevated role like db_owner or a system role like sysadmin. I'm assuming that you verified that the application is actually using the login you have been denying permissions on.

the user might be a "dbo".|||If the user is dbo then SQL Server does not check any other permissions (grant or deny), so that would be why you are seeing this behavior. You will need to remove the user from dbo and grant that account the needed permissions or role.