Does the logon details you're using from the client have rights to change
the job details?
--
--
Jack Vamvas
___________________________________
Receive free SQL tips - www.ciquery.com/sqlserver.htm
___________________________________
"Sorin Popescu" <spopescu@.nospam.com> wrote in message
news:OZyCCE4kGHA.4224@.TK2MSFTNGP05.phx.gbl...
> I can't edit a step (or create a new one) form an Agent job.
> I get General acces denied error.
> On details I see at program location
>
Microsoft.SqlServer.Dts.Runtime.Wrapper.ApplicationClass.get_LogProviderInfo
s()
> Microsoft.SqlServer.Dts.Runtime.Application.get_LogProviderInfos()
> I use a client station to acces the server
> On server works fine
> Any ideas ?
>How can I verify that please ?
"Jack Vamvas" <DEL_TO_REPLYtechsupport@.ciquery.com> wrote in message
news:2-ednVMta7jY5AvZRVnyvQ@.bt.com...
> Does the logon details you're using from the client have rights to change
> the job details?
> --
> --
> Jack Vamvas
> ___________________________________
> Receive free SQL tips - www.ciquery.com/sqlserver.htm
> ___________________________________
>
> "Sorin Popescu" <spopescu@.nospam.com> wrote in message
> news:OZyCCE4kGHA.4224@.TK2MSFTNGP05.phx.gbl...
> Microsoft.SqlServer.Dts.Runtime.Wrapper.ApplicationClass.get_LogProviderIn
fo
> s()
>|||It's the owner of job
"Jack Vamvas" <DEL_TO_REPLYtechsupport@.ciquery.com> wrote in message
news:2-ednVMta7jY5AvZRVnyvQ@.bt.com...
> Does the logon details you're using from the client have rights to change
> the job details?
> --
> --
> Jack Vamvas
> ___________________________________
> Receive free SQL tips - www.ciquery.com/sqlserver.htm
> ___________________________________
>
> "Sorin Popescu" <spopescu@.nospam.com> wrote in message
> news:OZyCCE4kGHA.4224@.TK2MSFTNGP05.phx.gbl...
> Microsoft.SqlServer.Dts.Runtime.Wrapper.ApplicationClass.get_LogProviderIn
fo
> s()
>|||I can't edit a step (or create a new one) form an Agent job.
I get General acces denied error.
On details I see at program location
Microsoft.SqlServer.Dts.Runtime.Wrapper.ApplicationClass.get_LogProviderInfo
s()
Microsoft.SqlServer.Dts.Runtime.Application.get_LogProviderInfos()
I use a client station to acces the server
On server works fine
Any ideas ?|||Does the logon details you're using from the client have rights to change
the job details?
--
--
Jack Vamvas
___________________________________
Receive free SQL tips - www.ciquery.com/sqlserver.htm
___________________________________
"Sorin Popescu" <spopescu@.nospam.com> wrote in message
news:OZyCCE4kGHA.4224@.TK2MSFTNGP05.phx.gbl...
> I can't edit a step (or create a new one) form an Agent job.
> I get General acces denied error.
> On details I see at program location
>
Microsoft.SqlServer.Dts.Runtime.Wrapper.ApplicationClass.get_LogProviderInfo
s()
> Microsoft.SqlServer.Dts.Runtime.Application.get_LogProviderInfos()
> I use a client station to acces the server
> On server works fine
> Any ideas ?
>|||How can I verify that please ?
"Jack Vamvas" <DEL_TO_REPLYtechsupport@.ciquery.com> wrote in message
news:2-ednVMta7jY5AvZRVnyvQ@.bt.com...
> Does the logon details you're using from the client have rights to change
> the job details?
> --
> --
> Jack Vamvas
> ___________________________________
> Receive free SQL tips - www.ciquery.com/sqlserver.htm
> ___________________________________
>
> "Sorin Popescu" <spopescu@.nospam.com> wrote in message
> news:OZyCCE4kGHA.4224@.TK2MSFTNGP05.phx.gbl...
> Microsoft.SqlServer.Dts.Runtime.Wrapper.ApplicationClass.get_LogProviderIn
fo
> s()
>|||It's the owner of job
"Jack Vamvas" <DEL_TO_REPLYtechsupport@.ciquery.com> wrote in message
news:2-ednVMta7jY5AvZRVnyvQ@.bt.com...
> Does the logon details you're using from the client have rights to change
> the job details?
> --
> --
> Jack Vamvas
> ___________________________________
> Receive free SQL tips - www.ciquery.com/sqlserver.htm
> ___________________________________
>
> "Sorin Popescu" <spopescu@.nospam.com> wrote in message
> news:OZyCCE4kGHA.4224@.TK2MSFTNGP05.phx.gbl...
> Microsoft.SqlServer.Dts.Runtime.Wrapper.ApplicationClass.get_LogProviderIn
fo
> s()
>
Showing posts with label client. Show all posts
Showing posts with label client. Show all posts
Thursday, March 29, 2012
Easy way to update multiple databases
SQL 2K, Win 2K
My company is in the process of developing a new app that will have a lot of
client specific databases (30 to 50) with identical schemas and stored
procedures. My job as the DBA will be to make the schema and SP changes to
all of these when we have updates.
I have a similar app now that I have a script with several Use Database
commands and I just paste the changes in between the Use Database commands,
but I only have to do this for a few databases.
Does anybody know of a way to automate this with scripts or a tool to apply
the schema and SO change to all user databases on a server.
Thanks.
MikePerhaps DB Ghost would fill your needs.
www.dbghost.com
or
http://www.innovartis.co.uk/Home.aspx
-- Keith
"Mike" <Mike@.Comcast.net> wrote in message =news:OUpLTC1uDHA.2180@.TK2MSFTNGP09.phx.gbl...
> SQL 2K, Win 2K
> > My company is in the process of developing a new app that will have a =lot of
> client specific databases (30 to 50) with identical schemas and stored
> procedures. My job as the DBA will be to make the schema and SP =changes to
> all of these when we have updates.
> > I have a similar app now that I have a script with several Use =Database
> commands and I just paste the changes in between the Use Database =commands,
> but I only have to do this for a few databases.
> > Does anybody know of a way to automate this with scripts or a tool to =apply
> the schema and SO change to all user databases on a server.
> > Thanks.
> > Mike
> >|||Hi
If you are using a version control system to maintain your database code.
The most obvious way to upgrade would be use scripts called from osql/isql
or possibly DMO.
John
"Mike" <Mike@.Comcast.net> wrote in message
news:OUpLTC1uDHA.2180@.TK2MSFTNGP09.phx.gbl...
> SQL 2K, Win 2K
> My company is in the process of developing a new app that will have a lot
of
> client specific databases (30 to 50) with identical schemas and stored
> procedures. My job as the DBA will be to make the schema and SP changes to
> all of these when we have updates.
> I have a similar app now that I have a script with several Use Database
> commands and I just paste the changes in between the Use Database
commands,
> but I only have to do this for a few databases.
> Does anybody know of a way to automate this with scripts or a tool to
apply
> the schema and SO change to all user databases on a server.
> Thanks.
> Mike
>|||Check out if www.red-gate.com or www.dbghost.com does what you want.
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Mike" <Mike@.Comcast.net> wrote in message
news:OUpLTC1uDHA.2180@.TK2MSFTNGP09.phx.gbl...
> SQL 2K, Win 2K
> My company is in the process of developing a new app that will have a lot
of
> client specific databases (30 to 50) with identical schemas and stored
> procedures. My job as the DBA will be to make the schema and SP changes to
> all of these when we have updates.
> I have a similar app now that I have a script with several Use Database
> commands and I just paste the changes in between the Use Database
commands,
> but I only have to do this for a few databases.
> Does anybody know of a way to automate this with scripts or a tool to
apply
> the schema and SO change to all user databases on a server.
> Thanks.
> Mike
>|||I wrote a little vb app that gets a list of db's to apply
changes to. you just paste the SQL syntax (ie "Alter
Table x add constraint y...") and click go, and it runs
the command on all dbs. I populate a simple table w/ the
names of db's to run the command against.
Also, I put together an app that uses a template database
(with the tables, sp's, triggers, that you want), and
pushes the schema out to a list of databases (in my case,
that list is in a table).
If you want the VB code for either, just let me know.
>--Original Message--
>SQL 2K, Win 2K
>My company is in the process of developing a new app that
will have a lot of
>client specific databases (30 to 50) with identical
schemas and stored
>procedures. My job as the DBA will be to make the schema
and SP changes to
>all of these when we have updates.
> I have a similar app now that I have a script with
several Use Database
>commands and I just paste the changes in between the Use
Database commands,
>but I only have to do this for a few databases.
>Does anybody know of a way to automate this with scripts
or a tool to apply
>the schema and SO change to all user databases on a
server.
>Thanks.
>Mike
>
>.
>|||I would appreciate it if you could share the app with me. It doesn't look
like DBGhost or SQL Compare will do it.
Thanks.
Mike
"Gene Daigle" <anonymous@.discussions.microsoft.com> wrote in message
news:024101c3bb73$7b7e3e10$a401280a@.phx.gbl...
> I wrote a little vb app that gets a list of db's to apply
> changes to. you just paste the SQL syntax (ie "Alter
> Table x add constraint y...") and click go, and it runs
> the command on all dbs. I populate a simple table w/ the
> names of db's to run the command against.
> Also, I put together an app that uses a template database
> (with the tables, sp's, triggers, that you want), and
> pushes the schema out to a list of databases (in my case,
> that list is in a table).
> If you want the VB code for either, just let me know.
>
> >--Original Message--
> >SQL 2K, Win 2K
> >
> >My company is in the process of developing a new app that
> will have a lot of
> >client specific databases (30 to 50) with identical
> schemas and stored
> >procedures. My job as the DBA will be to make the schema
> and SP changes to
> >all of these when we have updates.
> >
> > I have a similar app now that I have a script with
> several Use Database
> >commands and I just paste the changes in between the Use
> Database commands,
> >but I only have to do this for a few databases.
> >
> >Does anybody know of a way to automate this with scripts
> or a tool to apply
> >the schema and SO change to all user databases on a
> server.
> >
> >Thanks.
> >
> >Mike
> >
> >
> >.
> >
My company is in the process of developing a new app that will have a lot of
client specific databases (30 to 50) with identical schemas and stored
procedures. My job as the DBA will be to make the schema and SP changes to
all of these when we have updates.
I have a similar app now that I have a script with several Use Database
commands and I just paste the changes in between the Use Database commands,
but I only have to do this for a few databases.
Does anybody know of a way to automate this with scripts or a tool to apply
the schema and SO change to all user databases on a server.
Thanks.
MikePerhaps DB Ghost would fill your needs.
www.dbghost.com
or
http://www.innovartis.co.uk/Home.aspx
-- Keith
"Mike" <Mike@.Comcast.net> wrote in message =news:OUpLTC1uDHA.2180@.TK2MSFTNGP09.phx.gbl...
> SQL 2K, Win 2K
> > My company is in the process of developing a new app that will have a =lot of
> client specific databases (30 to 50) with identical schemas and stored
> procedures. My job as the DBA will be to make the schema and SP =changes to
> all of these when we have updates.
> > I have a similar app now that I have a script with several Use =Database
> commands and I just paste the changes in between the Use Database =commands,
> but I only have to do this for a few databases.
> > Does anybody know of a way to automate this with scripts or a tool to =apply
> the schema and SO change to all user databases on a server.
> > Thanks.
> > Mike
> >|||Hi
If you are using a version control system to maintain your database code.
The most obvious way to upgrade would be use scripts called from osql/isql
or possibly DMO.
John
"Mike" <Mike@.Comcast.net> wrote in message
news:OUpLTC1uDHA.2180@.TK2MSFTNGP09.phx.gbl...
> SQL 2K, Win 2K
> My company is in the process of developing a new app that will have a lot
of
> client specific databases (30 to 50) with identical schemas and stored
> procedures. My job as the DBA will be to make the schema and SP changes to
> all of these when we have updates.
> I have a similar app now that I have a script with several Use Database
> commands and I just paste the changes in between the Use Database
commands,
> but I only have to do this for a few databases.
> Does anybody know of a way to automate this with scripts or a tool to
apply
> the schema and SO change to all user databases on a server.
> Thanks.
> Mike
>|||Check out if www.red-gate.com or www.dbghost.com does what you want.
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Mike" <Mike@.Comcast.net> wrote in message
news:OUpLTC1uDHA.2180@.TK2MSFTNGP09.phx.gbl...
> SQL 2K, Win 2K
> My company is in the process of developing a new app that will have a lot
of
> client specific databases (30 to 50) with identical schemas and stored
> procedures. My job as the DBA will be to make the schema and SP changes to
> all of these when we have updates.
> I have a similar app now that I have a script with several Use Database
> commands and I just paste the changes in between the Use Database
commands,
> but I only have to do this for a few databases.
> Does anybody know of a way to automate this with scripts or a tool to
apply
> the schema and SO change to all user databases on a server.
> Thanks.
> Mike
>|||I wrote a little vb app that gets a list of db's to apply
changes to. you just paste the SQL syntax (ie "Alter
Table x add constraint y...") and click go, and it runs
the command on all dbs. I populate a simple table w/ the
names of db's to run the command against.
Also, I put together an app that uses a template database
(with the tables, sp's, triggers, that you want), and
pushes the schema out to a list of databases (in my case,
that list is in a table).
If you want the VB code for either, just let me know.
>--Original Message--
>SQL 2K, Win 2K
>My company is in the process of developing a new app that
will have a lot of
>client specific databases (30 to 50) with identical
schemas and stored
>procedures. My job as the DBA will be to make the schema
and SP changes to
>all of these when we have updates.
> I have a similar app now that I have a script with
several Use Database
>commands and I just paste the changes in between the Use
Database commands,
>but I only have to do this for a few databases.
>Does anybody know of a way to automate this with scripts
or a tool to apply
>the schema and SO change to all user databases on a
server.
>Thanks.
>Mike
>
>.
>|||I would appreciate it if you could share the app with me. It doesn't look
like DBGhost or SQL Compare will do it.
Thanks.
Mike
"Gene Daigle" <anonymous@.discussions.microsoft.com> wrote in message
news:024101c3bb73$7b7e3e10$a401280a@.phx.gbl...
> I wrote a little vb app that gets a list of db's to apply
> changes to. you just paste the SQL syntax (ie "Alter
> Table x add constraint y...") and click go, and it runs
> the command on all dbs. I populate a simple table w/ the
> names of db's to run the command against.
> Also, I put together an app that uses a template database
> (with the tables, sp's, triggers, that you want), and
> pushes the schema out to a list of databases (in my case,
> that list is in a table).
> If you want the VB code for either, just let me know.
>
> >--Original Message--
> >SQL 2K, Win 2K
> >
> >My company is in the process of developing a new app that
> will have a lot of
> >client specific databases (30 to 50) with identical
> schemas and stored
> >procedures. My job as the DBA will be to make the schema
> and SP changes to
> >all of these when we have updates.
> >
> > I have a similar app now that I have a script with
> several Use Database
> >commands and I just paste the changes in between the Use
> Database commands,
> >but I only have to do this for a few databases.
> >
> >Does anybody know of a way to automate this with scripts
> or a tool to apply
> >the schema and SO change to all user databases on a
> server.
> >
> >Thanks.
> >
> >Mike
> >
> >
> >.
> >
Monday, March 26, 2012
easy query for you...
hi can you help me? i have a table like this:
agent client year import
-- ---- -- -------
2 DF 2004 3,00000000000000E+001
2 DF 2004 7,02166900000000E+004
1 DF 2003 6,47549000000000E+003
2 DIEFFE 2004 7,14400000000000E+001
1 ISC 2004 1,50000000000000E+001
2 ISC 2003 4,00000000000000E+001
but i needed a table like:
agent client import2003 import2004
have you any idea? thanksThis is the never ending question.
Look up 'pivot table' in http://asktom.oracle.com
:rolleyes:
agent client year import
-- ---- -- -------
2 DF 2004 3,00000000000000E+001
2 DF 2004 7,02166900000000E+004
1 DF 2003 6,47549000000000E+003
2 DIEFFE 2004 7,14400000000000E+001
1 ISC 2004 1,50000000000000E+001
2 ISC 2003 4,00000000000000E+001
but i needed a table like:
agent client import2003 import2004
have you any idea? thanksThis is the never ending question.
Look up 'pivot table' in http://asktom.oracle.com
:rolleyes:
Easy method of Forms Authentication
Does anybody know if it is possible to create an ASP.NET webform (Form1)
that simply recieves a request from a client browser, and then sends that
request (changing a few bits as necessary) on to another page (Form2). The
response from Form2 will then be digested by Form1 and returned to client
browser.
I know that Server.Transfer has limitations, in that it only works between
ASPX forms, but this would be a great way of getting around all of the
Authentication problems in Report Services and sounds really easy.
You would just have to add some credentials, parameters if required and hey
presto, your in.
There may be a bit of a complicated conversation on the way through - but
this cant be as difficult as all the stuff that is going on here for
integeration.
Please somebody either add there support for this issue, or answer it as it
would solve loads of peoples issues.
Thanks in advance.
--
Message posted via http://www.sqlmonster.comThe Forms Authentication sample code provided by Microsoft is about as easy
as it can get for integrating with Reporting Services. We have used this as
a starting point to integrate with our own ASP.NET applications and
single-sign-on solution. If you have questions about this, please feel free
to post back.
--
Cheers,
'(' Jeff A. Stucker
\
Business Intelligence
www.criadvantage.com
---
"Tom Robson via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:47c0624d6e254541b1877708761a9ea9@.SQLMonster.com...
> Does anybody know if it is possible to create an ASP.NET webform (Form1)
> that simply recieves a request from a client browser, and then sends that
> request (changing a few bits as necessary) on to another page (Form2). The
> response from Form2 will then be digested by Form1 and returned to client
> browser.
> I know that Server.Transfer has limitations, in that it only works between
> ASPX forms, but this would be a great way of getting around all of the
> Authentication problems in Report Services and sounds really easy.
> You would just have to add some credentials, parameters if required and
> hey
> presto, your in.
> There may be a bit of a complicated conversation on the way through - but
> this cant be as difficult as all the stuff that is going on here for
> integeration.
> Please somebody either add there support for this issue, or answer it as
> it
> would solve loads of peoples issues.
> Thanks in advance.
> --
> Message posted via http://www.sqlmonster.com
that simply recieves a request from a client browser, and then sends that
request (changing a few bits as necessary) on to another page (Form2). The
response from Form2 will then be digested by Form1 and returned to client
browser.
I know that Server.Transfer has limitations, in that it only works between
ASPX forms, but this would be a great way of getting around all of the
Authentication problems in Report Services and sounds really easy.
You would just have to add some credentials, parameters if required and hey
presto, your in.
There may be a bit of a complicated conversation on the way through - but
this cant be as difficult as all the stuff that is going on here for
integeration.
Please somebody either add there support for this issue, or answer it as it
would solve loads of peoples issues.
Thanks in advance.
--
Message posted via http://www.sqlmonster.comThe Forms Authentication sample code provided by Microsoft is about as easy
as it can get for integrating with Reporting Services. We have used this as
a starting point to integrate with our own ASP.NET applications and
single-sign-on solution. If you have questions about this, please feel free
to post back.
--
Cheers,
'(' Jeff A. Stucker
\
Business Intelligence
www.criadvantage.com
---
"Tom Robson via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:47c0624d6e254541b1877708761a9ea9@.SQLMonster.com...
> Does anybody know if it is possible to create an ASP.NET webform (Form1)
> that simply recieves a request from a client browser, and then sends that
> request (changing a few bits as necessary) on to another page (Form2). The
> response from Form2 will then be digested by Form1 and returned to client
> browser.
> I know that Server.Transfer has limitations, in that it only works between
> ASPX forms, but this would be a great way of getting around all of the
> Authentication problems in Report Services and sounds really easy.
> You would just have to add some credentials, parameters if required and
> hey
> presto, your in.
> There may be a bit of a complicated conversation on the way through - but
> this cant be as difficult as all the stuff that is going on here for
> integeration.
> Please somebody either add there support for this issue, or answer it as
> it
> would solve loads of peoples issues.
> Thanks in advance.
> --
> Message posted via http://www.sqlmonster.com
Thursday, March 22, 2012
Easy database dump to a file
Hi everyone,
Using SQL Server 2000, what would be the best way to get a dump of a
database that is on a server at a client? I need to be able to say "Here,
run this script/app/command" and it has to be simple enough so that the
person there is able to complete the task. Then, the person will ship me
the dump file so I can have a look at it.
Thanks in advance for any suggestions.
Ericbackup database and the copy the file (.bak) to your workstation
"Eric Caron" <ecaron.nospam@.quebecaffaires.com> wrote in message
news:eEPoz9CbFHA.2520@.TK2MSFTNGP09.phx.gbl...
> Hi everyone,
> Using SQL Server 2000, what would be the best way to get a dump of a
> database that is on a server at a client? I need to be able to say "Here,
> run this script/app/command" and it has to be simple enough so that the
> person there is able to complete the task. Then, the person will ship me
> the dump file so I can have a look at it.
> Thanks in advance for any suggestions.
> Eric
>|||Hi
T-SQL command BACKUP DATABASE and give it to them as a file to run against
OSQL.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Uri Dimant" wrote:
> backup database and the copy the file (.bak) to your workstation
>
> "Eric Caron" <ecaron.nospam@.quebecaffaires.com> wrote in message
> news:eEPoz9CbFHA.2520@.TK2MSFTNGP09.phx.gbl...
>
>
Using SQL Server 2000, what would be the best way to get a dump of a
database that is on a server at a client? I need to be able to say "Here,
run this script/app/command" and it has to be simple enough so that the
person there is able to complete the task. Then, the person will ship me
the dump file so I can have a look at it.
Thanks in advance for any suggestions.
Ericbackup database and the copy the file (.bak) to your workstation
"Eric Caron" <ecaron.nospam@.quebecaffaires.com> wrote in message
news:eEPoz9CbFHA.2520@.TK2MSFTNGP09.phx.gbl...
> Hi everyone,
> Using SQL Server 2000, what would be the best way to get a dump of a
> database that is on a server at a client? I need to be able to say "Here,
> run this script/app/command" and it has to be simple enough so that the
> person there is able to complete the task. Then, the person will ship me
> the dump file so I can have a look at it.
> Thanks in advance for any suggestions.
> Eric
>|||Hi
T-SQL command BACKUP DATABASE and give it to them as a file to run against
OSQL.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Uri Dimant" wrote:
> backup database and the copy the file (.bak) to your workstation
>
> "Eric Caron" <ecaron.nospam@.quebecaffaires.com> wrote in message
> news:eEPoz9CbFHA.2520@.TK2MSFTNGP09.phx.gbl...
>
>
Easiest way to determine if only client tools are installed?
Hi all,
I've been trying to register my local sql server in enterprise manager
without any success - I can see my remote sql server, but it just wont
connect to the local one on the laptop - I think I may have only install
"client tools" in the past because I thought I'd only want to connect to the
remote server - is there an easy to way to determine whether this is the
case? I've just checked under Add/Remove Programs and there's a SQL Server
entry for about 70mb...
Any info appreciated..
Regards
RobRob Meade (ten.bewdoowsgnik@.edaem.bbor) writes:
> I've been trying to register my local sql server in enterprise manager
> without any success - I can see my remote sql server, but it just wont
> connect to the local one on the laptop - I think I may have only install
> "client tools" in the past because I thought I'd only want to connect to
> the remote server - is there an easy to way to determine whether this is
> the case? I've just checked under Add/Remove Programs and there's a SQL
> Server entry for about 70mb...
Start->Programs->SQL Server. Do you see a Server Network Utility? If you
don't, you don't have the server installed. If you do, you have it
installed. If you also have a Service Manager, start it, and then find
the SQL Server service, and make sure it's running.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspxsql
I've been trying to register my local sql server in enterprise manager
without any success - I can see my remote sql server, but it just wont
connect to the local one on the laptop - I think I may have only install
"client tools" in the past because I thought I'd only want to connect to the
remote server - is there an easy to way to determine whether this is the
case? I've just checked under Add/Remove Programs and there's a SQL Server
entry for about 70mb...
Any info appreciated..
Regards
RobRob Meade (ten.bewdoowsgnik@.edaem.bbor) writes:
> I've been trying to register my local sql server in enterprise manager
> without any success - I can see my remote sql server, but it just wont
> connect to the local one on the laptop - I think I may have only install
> "client tools" in the past because I thought I'd only want to connect to
> the remote server - is there an easy to way to determine whether this is
> the case? I've just checked under Add/Remove Programs and there's a SQL
> Server entry for about 70mb...
Start->Programs->SQL Server. Do you see a Server Network Utility? If you
don't, you don't have the server installed. If you do, you have it
installed. If you also have a Service Manager, start it, and then find
the SQL Server service, and make sure it's running.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspxsql
Sunday, March 11, 2012
Dynamically determining the SQL statement?
Hello all,
I am working with a client who will be creating "template" report for users to be able to customize. The customization settings will be stored in meta data on a SQL Server somehere.
Part of the customization features will allow users to filter the data that they view. There may be 20 plus filters on any given report and they may want to filter in many different ways (i.e. sometimes to an Equals, other times an IN (), other times a LIKE, etc...), but only one way per field, per customization.
With this many possible filters, I want to avoid having the report authors be responsbile for coding these as parameters on the CommandText node (it is just using a SQL SELECT statement).
Well, let me get to the question...What is the best way to dynamically set the Report's CommandText or "main" dataset (and I guess while I am at it, the main datasource/connection information).
BTW...currently using SSRS 2005 + ASP.NET 2.0.
Thanks for your help!!
-BrianYou can create a function (sketch) like this:
public static string PrepareStatement(string templateSql, string userID)
{
string userFilters = GoGetUserPrefsFromDB(userID);
return templateSql + " WHERE " + userFilters;
}
Put it into a custom assembly, ref the assembly from your reports and have
<CommandText>=MyClass.PrepareStatement("select * from qqq", User!UserID)</CommandText>
Make sure you grant/assert CAS permissions.|||That is a good idea, the only problem with that I see is that it might be difficult to debug since the rendering engine is going to be responsible for creating the parameters...no to mention the client would have to maintain and deploy this assembly to each reporting server in the SSRS farm...
I guess the *best* way to set the command text and datasource from the front end is the use parameters?
Are there any other possible solutions?
Thanks!
-Brian
Friday, February 17, 2012
Dynamic SQL Issues with Functions/SP
I am working on a reporting application that uses dynamic SQL to generate
result sets. My client initially used a non-dynamic user-defined function
that returned a table, but when I made the SQL dynamic, it turns out that
EXEC statements cannot be used within a function. Is there a way to return a
table variable from a stored procedure if I convert the function into a
stored procedure? Are there any workarounds for this?
--
Larry Menzin
American Techsystems Corp.>> Is there a way to return a table variable from a stored procedure if I
Table variables cannot be used as parameters or return values, however you
can use regular resultsets for similar functionality. Also see some other
alternatives at:
http://www.sommarskog.se/share_data.html
Anith|||Why are you trying to make udf dynamic? Why not, in your dynamic SQL, simply
include a call to the function?
Thomas
"Larry Menzin" <LarryMenzin@.discussions.microsoft.com> wrote in message
news:A7A7359A-B77C-486A-BDF9-2D86141A0B16@.microsoft.com...
>I am working on a reporting application that uses dynamic SQL to generate
> result sets. My client initially used a non-dynamic user-defined function
> that returned a table, but when I made the SQL dynamic, it turns out that
> EXEC statements cannot be used within a function. Is there a way to return
a
> table variable from a stored procedure if I convert the function into a
> stored procedure? Are there any workarounds for this?
> --
> Larry Menzin
> American Techsystems Corp.|||You can return a result set from a Stored Procedure with a SELECT statement.
Here's a really simplified example that uses dynamic SQL:
CREATE PROCEDURE dbo.usp_test
AS
DECLARE @.MySQL AS NVARCHAR(4000)
SET @.MySQL = N'SELECT * FROM master.dbo.sysmessages'
EXEC dbo.sp_executesql @.MySQL
GO
EXEC dbo.usp_test
GO
"Larry Menzin" <LarryMenzin@.discussions.microsoft.com> wrote in message
news:A7A7359A-B77C-486A-BDF9-2D86141A0B16@.microsoft.com...
>I am working on a reporting application that uses dynamic SQL to generate
> result sets. My client initially used a non-dynamic user-defined function
> that returned a table, but when I made the SQL dynamic, it turns out that
> EXEC statements cannot be used within a function. Is there a way to return
> a
> table variable from a stored procedure if I convert the function into a
> stored procedure? Are there any workarounds for this?
> --
> Larry Menzin
> American Techsystems Corp.|||> table variable from a stored procedure if I convert the function into a
> stored procedure? Are there any workarounds for this?
No
Can you explain what are you trying to accomplish?
AMB
"Larry Menzin" wrote:
> I am working on a reporting application that uses dynamic SQL to generate
> result sets. My client initially used a non-dynamic user-defined function
> that returned a table, but when I made the SQL dynamic, it turns out that
> EXEC statements cannot be used within a function. Is there a way to return
a
> table variable from a stored procedure if I convert the function into a
> stored procedure? Are there any workarounds for this?
> --
> Larry Menzin
> American Techsystems Corp.|||I am trying to join 4 result sets together, where each set is currently
generated as a table output variable from a UDF. UDF output is used directly
in JOIN statements in a stored procedure. If I convert UDFs to stored
procedures can I use stored procedure result sets in joins like UDF table
variables are used in joins, i.e.,
Select * From ExecSP1(parameters1) JOIN ExecSP1(Parameters2) JOIN
ExceSP1(Parameters3) ON ...
Larry Menzin
American Techsystems Corp.
"Alejandro Mesa" wrote:
> No
> Can you explain what are you trying to accomplish?
>
> AMB
> "Larry Menzin" wrote:
>|||You can use VIEWs in the manner you're talking about. Another option is to
create Temporary Tables with SELECT INTO or INSERT INTO in your SP and JOIN
them.
"Larry Menzin" <LarryMenzin@.discussions.microsoft.com> wrote in message
news:0E155215-A119-4980-B1B9-7DFC110E22AF@.microsoft.com...
>I am trying to join 4 result sets together, where each set is currently
> generated as a table output variable from a UDF. UDF output is used
> directly
> in JOIN statements in a stored procedure. If I convert UDFs to stored
> procedures can I use stored procedure result sets in joins like UDF table
> variables are used in joins, i.e.,
> Select * From ExecSP1(parameters1) JOIN ExecSP1(Parameters2) JOIN
> ExceSP1(Parameters3) ON ...
> --
> Larry Menzin
> American Techsystems Corp.
>
> "Alejandro Mesa" wrote:
>|||Larry,
No, you can not reference stored procedure from the "from" clause, but as
Anith pointed, there are other alternatives, like capturing the sp resultset
into a table (temporary or permanent) and use that table in subsequent
statements. Click the link posted by Anith Sen.
AMB
"Larry Menzin" wrote:
> I am trying to join 4 result sets together, where each set is currently
> generated as a table output variable from a UDF. UDF output is used direct
ly
> in JOIN statements in a stored procedure. If I convert UDFs to stored
> procedures can I use stored procedure result sets in joins like UDF table
> variables are used in joins, i.e.,
> Select * From ExecSP1(parameters1) JOIN ExecSP1(Parameters2) JOIN
> ExceSP1(Parameters3) ON ...
> --
> Larry Menzin
> American Techsystems Corp.
>
> "Alejandro Mesa" wrote:
>|||> If I convert UDFs to stored
> procedures can I use stored procedure result sets in joins like UDF table
> variables are used in joins, i.e.,
No and I don't see a reason why you would need to do that. You should be abl
e to
do the following:
Select *
From dbo.UDF1(parameters) As UDF1
Join dbo.UDF2(parameters) As UDF2
On UDF1.Key = UDF2.Key
...
This assumes that all of the functions are table-valued functions. If there
is
commonality amongst the parameters in the functions, you could create a sing
le
stored procedure that returns the data like so:
Create Procedure ReportXYZ(parameter1 as ...)
As
Select *
From dbo.UDF1(parameters) As UDF1
Join dbo.UDF2(parameters) As UDF2
On UDF1.Key = UDF2.Key
Return
Notice that no dynamic SQL is needed for any of this. You would simply call
the
stored procedure which would return the raw data for you.
Thomas|||Here's one of I'm sure many articles on this subject:
http://www.databasejournal.com/feat...cle.php/3386661
jp
Larry Menzin wrote:
> Alejandro,
> Can I capture the result set from the SP into a table variable, i.e.,
> DECLARE @.CurrentPeriodDataPrelim TABLE(
> Product VarChar(40)
> ,Region varchar(40)
> ,Market varchar(40)
> ,Channel varchar(40)
> ,MRC float
> ,Net float
> ,NumerLabel nVarChar(150)
> )
>
> Declare @.strSQL nvarchar(2000)
> Set @.strSQL = N'
> INSERT INTO @.CurrentPeriodDataPrelim (Product, Region, Market, Channel, MR
C,
> NET, NumerLabel)
> SELECT cat as Product, ...
> Or do I have to use temp tables with the '#' designation if I don't want t
o
> use permanent tables. Also, don't I worry about temp tables going out of
> scope?
>
>
result sets. My client initially used a non-dynamic user-defined function
that returned a table, but when I made the SQL dynamic, it turns out that
EXEC statements cannot be used within a function. Is there a way to return a
table variable from a stored procedure if I convert the function into a
stored procedure? Are there any workarounds for this?
--
Larry Menzin
American Techsystems Corp.>> Is there a way to return a table variable from a stored procedure if I
Table variables cannot be used as parameters or return values, however you
can use regular resultsets for similar functionality. Also see some other
alternatives at:
http://www.sommarskog.se/share_data.html
Anith|||Why are you trying to make udf dynamic? Why not, in your dynamic SQL, simply
include a call to the function?
Thomas
"Larry Menzin" <LarryMenzin@.discussions.microsoft.com> wrote in message
news:A7A7359A-B77C-486A-BDF9-2D86141A0B16@.microsoft.com...
>I am working on a reporting application that uses dynamic SQL to generate
> result sets. My client initially used a non-dynamic user-defined function
> that returned a table, but when I made the SQL dynamic, it turns out that
> EXEC statements cannot be used within a function. Is there a way to return
a
> table variable from a stored procedure if I convert the function into a
> stored procedure? Are there any workarounds for this?
> --
> Larry Menzin
> American Techsystems Corp.|||You can return a result set from a Stored Procedure with a SELECT statement.
Here's a really simplified example that uses dynamic SQL:
CREATE PROCEDURE dbo.usp_test
AS
DECLARE @.MySQL AS NVARCHAR(4000)
SET @.MySQL = N'SELECT * FROM master.dbo.sysmessages'
EXEC dbo.sp_executesql @.MySQL
GO
EXEC dbo.usp_test
GO
"Larry Menzin" <LarryMenzin@.discussions.microsoft.com> wrote in message
news:A7A7359A-B77C-486A-BDF9-2D86141A0B16@.microsoft.com...
>I am working on a reporting application that uses dynamic SQL to generate
> result sets. My client initially used a non-dynamic user-defined function
> that returned a table, but when I made the SQL dynamic, it turns out that
> EXEC statements cannot be used within a function. Is there a way to return
> a
> table variable from a stored procedure if I convert the function into a
> stored procedure? Are there any workarounds for this?
> --
> Larry Menzin
> American Techsystems Corp.|||> table variable from a stored procedure if I convert the function into a
> stored procedure? Are there any workarounds for this?
No
Can you explain what are you trying to accomplish?
AMB
"Larry Menzin" wrote:
> I am working on a reporting application that uses dynamic SQL to generate
> result sets. My client initially used a non-dynamic user-defined function
> that returned a table, but when I made the SQL dynamic, it turns out that
> EXEC statements cannot be used within a function. Is there a way to return
a
> table variable from a stored procedure if I convert the function into a
> stored procedure? Are there any workarounds for this?
> --
> Larry Menzin
> American Techsystems Corp.|||I am trying to join 4 result sets together, where each set is currently
generated as a table output variable from a UDF. UDF output is used directly
in JOIN statements in a stored procedure. If I convert UDFs to stored
procedures can I use stored procedure result sets in joins like UDF table
variables are used in joins, i.e.,
Select * From ExecSP1(parameters1) JOIN ExecSP1(Parameters2) JOIN
ExceSP1(Parameters3) ON ...
Larry Menzin
American Techsystems Corp.
"Alejandro Mesa" wrote:
> No
> Can you explain what are you trying to accomplish?
>
> AMB
> "Larry Menzin" wrote:
>|||You can use VIEWs in the manner you're talking about. Another option is to
create Temporary Tables with SELECT INTO or INSERT INTO in your SP and JOIN
them.
"Larry Menzin" <LarryMenzin@.discussions.microsoft.com> wrote in message
news:0E155215-A119-4980-B1B9-7DFC110E22AF@.microsoft.com...
>I am trying to join 4 result sets together, where each set is currently
> generated as a table output variable from a UDF. UDF output is used
> directly
> in JOIN statements in a stored procedure. If I convert UDFs to stored
> procedures can I use stored procedure result sets in joins like UDF table
> variables are used in joins, i.e.,
> Select * From ExecSP1(parameters1) JOIN ExecSP1(Parameters2) JOIN
> ExceSP1(Parameters3) ON ...
> --
> Larry Menzin
> American Techsystems Corp.
>
> "Alejandro Mesa" wrote:
>|||Larry,
No, you can not reference stored procedure from the "from" clause, but as
Anith pointed, there are other alternatives, like capturing the sp resultset
into a table (temporary or permanent) and use that table in subsequent
statements. Click the link posted by Anith Sen.
AMB
"Larry Menzin" wrote:
> I am trying to join 4 result sets together, where each set is currently
> generated as a table output variable from a UDF. UDF output is used direct
ly
> in JOIN statements in a stored procedure. If I convert UDFs to stored
> procedures can I use stored procedure result sets in joins like UDF table
> variables are used in joins, i.e.,
> Select * From ExecSP1(parameters1) JOIN ExecSP1(Parameters2) JOIN
> ExceSP1(Parameters3) ON ...
> --
> Larry Menzin
> American Techsystems Corp.
>
> "Alejandro Mesa" wrote:
>|||> If I convert UDFs to stored
> procedures can I use stored procedure result sets in joins like UDF table
> variables are used in joins, i.e.,
No and I don't see a reason why you would need to do that. You should be abl
e to
do the following:
Select *
From dbo.UDF1(parameters) As UDF1
Join dbo.UDF2(parameters) As UDF2
On UDF1.Key = UDF2.Key
...
This assumes that all of the functions are table-valued functions. If there
is
commonality amongst the parameters in the functions, you could create a sing
le
stored procedure that returns the data like so:
Create Procedure ReportXYZ(parameter1 as ...)
As
Select *
From dbo.UDF1(parameters) As UDF1
Join dbo.UDF2(parameters) As UDF2
On UDF1.Key = UDF2.Key
Return
Notice that no dynamic SQL is needed for any of this. You would simply call
the
stored procedure which would return the raw data for you.
Thomas|||Here's one of I'm sure many articles on this subject:
http://www.databasejournal.com/feat...cle.php/3386661
jp
Larry Menzin wrote:
> Alejandro,
> Can I capture the result set from the SP into a table variable, i.e.,
> DECLARE @.CurrentPeriodDataPrelim TABLE(
> Product VarChar(40)
> ,Region varchar(40)
> ,Market varchar(40)
> ,Channel varchar(40)
> ,MRC float
> ,Net float
> ,NumerLabel nVarChar(150)
> )
>
> Declare @.strSQL nvarchar(2000)
> Set @.strSQL = N'
> INSERT INTO @.CurrentPeriodDataPrelim (Product, Region, Market, Channel, MR
C,
> NET, NumerLabel)
> SELECT cat as Product, ...
> Or do I have to use temp tables with the '#' designation if I don't want t
o
> use permanent tables. Also, don't I worry about temp tables going out of
> scope?
>
>
Wednesday, February 15, 2012
Dynamic SQL in Stored Procedures
A small percentage of my client's stored procedures use dynamic SQL
statements that are executed using sp_executesql. I am planning on
removing access to everything except stored procedure execution.
However, I have heard, that to be able to execute dynamic sql, a user
must have access to more than just stored procedure execution (i.e.,
SELECT, UPDATE, etc.). Can anyone clarify this for me, or lead me to a
site that explains it in better detail? Thank you in advance, Jeremy.On SQL Server 2000, permissions would be needed on the
underlying tables. There are other options with execute as
and certificates in SQL Server 2005.
You can find a lot of details on dynamic sql in the
following article on Erland's site:
The Curse and Blessings of Dynamic SQL
http://www.sommarskog.se/dynamic_sql.html
-Sue
On 5 Jun 2006 06:51:31 -0700, "jbiros" <jbiros@.sppinc.net>
wrote:
>A small percentage of my client's stored procedures use dynamic SQL
>statements that are executed using sp_executesql. I am planning on
>removing access to everything except stored procedure execution.
>However, I have heard, that to be able to execute dynamic sql, a user
>must have access to more than just stored procedure execution (i.e.,
>SELECT, UPDATE, etc.). Can anyone clarify this for me, or lead me to a
>site that explains it in better detail? Thank you in advance, Jeremy.|||Sue,
Thank you for the site reference. That is exactly the type of
information I was looking for.
Jeremy
statements that are executed using sp_executesql. I am planning on
removing access to everything except stored procedure execution.
However, I have heard, that to be able to execute dynamic sql, a user
must have access to more than just stored procedure execution (i.e.,
SELECT, UPDATE, etc.). Can anyone clarify this for me, or lead me to a
site that explains it in better detail? Thank you in advance, Jeremy.On SQL Server 2000, permissions would be needed on the
underlying tables. There are other options with execute as
and certificates in SQL Server 2005.
You can find a lot of details on dynamic sql in the
following article on Erland's site:
The Curse and Blessings of Dynamic SQL
http://www.sommarskog.se/dynamic_sql.html
-Sue
On 5 Jun 2006 06:51:31 -0700, "jbiros" <jbiros@.sppinc.net>
wrote:
>A small percentage of my client's stored procedures use dynamic SQL
>statements that are executed using sp_executesql. I am planning on
>removing access to everything except stored procedure execution.
>However, I have heard, that to be able to execute dynamic sql, a user
>must have access to more than just stored procedure execution (i.e.,
>SELECT, UPDATE, etc.). Can anyone clarify this for me, or lead me to a
>site that explains it in better detail? Thank you in advance, Jeremy.|||Sue,
Thank you for the site reference. That is exactly the type of
information I was looking for.
Jeremy
Subscribe to:
Posts (Atom)