We have may reports that need to customize the where clause based on
parameters entered by the end user. Is there a way to modify the RDL on the
fly in order to achieve this? I am using VS2005 and SQLServer 2005.
Example:
if user selects run report by week ending date the following line needs to
be used
"and we_dt between ? and ?
if the user selects the run the report by export date then the line changes
to:
"and export_dt between ? and ?You can handle this via expressions in the command text of the dataset.
It is much easier than modifying the RDL on the fly.
For example, you can use VB expressions in the command text to
something like this:
="SELECT field1, field2 FROM tblName WHERE " &
iif(Parameter!Param1.Value = something, "do between stuff", "don't do
between stuff")
Andy Potter|||We'll look into this, but what if the entire WHERE cluase needs to be modified?
Are there any good examples of people doing this? Either through changing
the RDL or passing the entire where clause into the report?
Thanks
"Potter" wrote:
> You can handle this via expressions in the command text of the dataset.
> It is much easier than modifying the RDL on the fly.
> For example, you can use VB expressions in the command text to
> something like this:
> ="SELECT field1, field2 FROM tblName WHERE " &
> iif(Parameter!Param1.Value = something, "do between stuff", "don't do
> between stuff")
> Andy Potter
>|||Using expressions, your entire command text is available for
manipulation. Just do something like this:
="SELECT field1, field2 FROM tblName " & iif(Parameter!Param1.Value ="all", "", "WHERE field=" & Parameter!Param1.Value )
Personally, I prefer this kind of logic in a stored procedure. I find
large expressions to handle string manipulation to be a bit unwieldy.
The other option to handle large string manipulation is the custom code
section of the report, which give you a little more flexibility as far
writing your string manipulation code.
Andy Potter
Showing posts with label user. Show all posts
Showing posts with label user. Show all posts
Tuesday, March 27, 2012
Customize Prompt Message for Stored Procudure
Hello all,
I have a stored procedure that prompts the user for beginning date and
ending date to run a monthly report. The prompt says
Enter_Beginning_Date and Enter_Ending_Date. I want the prompt to say
Enter Beginning Date (Example:1-1-2003) or something like that. Is
there a way to do this?
I have a stored procedure that prompts the user for beginning date and
ending date to run a monthly report. The prompt says
Enter_Beginning_Date and Enter_Ending_Date. I want the prompt to say
Enter Beginning Date (Example:1-1-2003) or something like that. Is
there a way to do this?
CREATE PROCEDURE dbo.MonthlyReport(@.Enter_Beginning_Date datetime,
@.Enter_Ending_Date datetime)
AS SELECT incident, @.Enter_Beginning_Date AS BeginningDate,
@.Enter_Ending_Date AS EndingDate, COUNT(*) AS Occurances
FROM dbo.Incident
WHERE (DateOccured BETWEEN @.Enter_Beginning_Date AND
@.Enter_Ending_Date)
GROUP BY incident
GO"ndn_24_7" <ndn_24_7@.yahoo.com> wrote in message
news:1105983132.532065.27990@.c13g2000cwb.googlegro ups.com...
> Hello all,
> I have a stored procedure that prompts the user for beginning date and
> ending date to run a monthly report. The prompt says
> Enter_Beginning_Date and Enter_Ending_Date. I want the prompt to say
> Enter Beginning Date (Example:1-1-2003) or something like that. Is
> there a way to do this?
> CREATE PROCEDURE dbo.MonthlyReport(@.Enter_Beginning_Date datetime,
> @.Enter_Ending_Date datetime)
> AS SELECT incident, @.Enter_Beginning_Date AS BeginningDate,
> @.Enter_Ending_Date AS EndingDate, COUNT(*) AS Occurances
> FROM dbo.Incident
> WHERE (DateOccured BETWEEN @.Enter_Beginning_Date AND
> @.Enter_Ending_Date)
> GROUP BY incident
> GO
MSSQL is purely a server, so it doesn't have any idea about GUIs or
prompts - if you want to present a more user-friendly description of the two
parameters, then you would have to do that in the front-end application
where the users select the dates.
One possible approach would be to add an extended property to the two
parameters which has the description in it, then retrieve that from the
front end when you display the input screen. See "Using Extended Properties
on Database Objects" in Books Online for more details. But I don't know if
that would be a suitable solution for your toolset and design.
Simon|||I'm sorry
I should have been more discriptive. My program has a Access 2000 front
end and a SQL 2000 server backend. I have a button that the user clicks
that brings up the prompt window for the stored proc. So would I do
this on the Access front end, Where would Icustomize this message?|||Not a clue how you are prompting the user thru Stored Procedure. May be
you missed out some valuable info on the post........!|||ndn_24_7 (ndn_24_7@.yahoo.com) writes:
> I should have been more discriptive. My program has a Access 2000 front
> end and a SQL 2000 server backend. I have a button that the user clicks
> that brings up the prompt window for the stored proc. So would I do
> this on the Access front end, Where would Icustomize this message?
Sounds like you should try an Access newsgroup. It is possible that
you can use extended properties for this, but I have no knowledge
what Access makes use of. So try comp.databases.ms-access where the
expertise for this question might hang out.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||As far I understood,
You have a stored procedure that accepts parameters and you have an
Access front end that accepts input parameters thru prompts and pass
them to the backend stored procedure... Am I correct?
If so, what you are doing is correct so far. You need to find out how
to call a stored procedure thru access and once you know, all you need
to do is, thru access, get those inputs (you may display anyway you
need - that is irrelevent to the backend procedure...) and pass them as
parameters to the backend stored proc.. May be an access guru can show
some light on how to accomplish this..!|||"ndn_24_7" <ndn_24_7@.yahoo.com> wrote in message
news:1105990021.951317.205140@.c13g2000cwb.googlegr oups.com...
> I'm sorry
> I should have been more discriptive. My program has a Access 2000 front
> end and a SQL 2000 server backend. I have a button that the user clicks
> that brings up the prompt window for the stored proc. So would I do
> this on the Access front end, Where would Icustomize this message?
I suggest you want a form that the user enters the dates in and then clicks
the button to run the stored proc.
I'll assume you know a little about VB code.
Otherwise, you got a steep learning curve ahead mate.
You probably want to validate the fields using isdate()
Access now uses ADO, so the code involves using an ado connection and
command.
I found pretty much the below code by using google to search the access
newsgroup.
Not tested it and I had to add the execute line, so this is just to get you
started.
BTW You'll want to get used to doing such searches if you are new to this
lark.
I suggest also take a look at each of the bits in turn and read up using
msdn so you get a better understanding what you're up to.
I have deliberately not changed the parameter type to date and input because
you'd learn stuff all if I just gave you the code.
There's a couple gotchas with datetime. Inside access some bits want this
delimited by # but not with ado. Remember also the time bit of a date is
likely there. Not such a problem with > or < but throw that to the back of
your mind for later.
Anyhow.
You run the thing by using the execute method of the command.
There's a parameter collection associated with a command you add the values
to:
'start untested snippet
Dim cmd As ADODB.Command
Dim prm As ADODB.Parameter
Set cmd = New ADODB.Command
cmd.ActiveConnection = CurrentProject.Connection
cmd.CommandText = "stored procedurename"
cmd.CommandType = adCmdStoredProc
Set prm = cmd.CreateParameter("@.CompanyID", adInteger, adParamOutput _
, , forms!yourformname!text1.Value)
cmd.Parameters.Append prm
cmd.execute
set prm = Nothing
Set cmd = Nothing
' end snippet
HTH
--
Regards,
Andy O'Neill
Sunday, March 25, 2012
Customization at runtime
I have a report which i am using in vb6.
it has following
itemid, itemname,price
but i want to give user choice to change this order at runtime,
according to him like
itemname,itemid,price or it can be any combination.
Ideas will be appreciated.
Thanks in AdvanceHi,
yes you could but not Only using CR but VB + SQL manupulating to suit u r requirement
the SQL which brings u data has to be
select Itemid as Filed1, itemname as Filed2 , price as Filed3
ie u let the user decided the orer he wants (in VB he decides through a ordering using selecting from a Left Listbox to right Listbox)
the according to that u Generate the SQL
select Itemid as Filed1, itemname as Filed2 , price as Filed3
Or
select itemname as Filed1, Itemid as Filed2 , price as Filed3
Or
......(u can simply do this depending on the use selection)
but in crystal Report use a 'Field Definitions Only' in (Database Expert --> create new connection ) then create a .ttx file with 3 field of type String with name Filed1, Filed2, Filed3 then place them in report in order
in VB set the DS value got from SQL to this report the
Note : the Order is Truely dynamic in SQL but user feels like the rpt is, but u have created only one Rpt.
Hope u got it
FaFa|||Thanks it worked.
Do you have any idea about letting user to place fields at specified position.
Thanks in Advancesql
it has following
itemid, itemname,price
but i want to give user choice to change this order at runtime,
according to him like
itemname,itemid,price or it can be any combination.
Ideas will be appreciated.
Thanks in AdvanceHi,
yes you could but not Only using CR but VB + SQL manupulating to suit u r requirement
the SQL which brings u data has to be
select Itemid as Filed1, itemname as Filed2 , price as Filed3
ie u let the user decided the orer he wants (in VB he decides through a ordering using selecting from a Left Listbox to right Listbox)
the according to that u Generate the SQL
select Itemid as Filed1, itemname as Filed2 , price as Filed3
Or
select itemname as Filed1, Itemid as Filed2 , price as Filed3
Or
......(u can simply do this depending on the use selection)
but in crystal Report use a 'Field Definitions Only' in (Database Expert --> create new connection ) then create a .ttx file with 3 field of type String with name Filed1, Filed2, Filed3 then place them in report in order
in VB set the DS value got from SQL to this report the
Note : the Order is Truely dynamic in SQL but user feels like the rpt is, but u have created only one Rpt.
Hope u got it
FaFa|||Thanks it worked.
Do you have any idea about letting user to place fields at specified position.
Thanks in Advancesql
Customising report Manager
Hi guys
I just want user see only content tab in Report Manager and not the property
tab .
I create a new role and select 'VIEW ROLE' and 'VIEW REPORT' tasks,
descriptions says that user will be able to see the properties too. I dont
want let user see the property.
Niether should user see the subscription link.
How could I achieve that
Sincerely
AmeetI don't think it is possible to give a set of permissions where a user will
be able to view the report and not see the properties of the report. If you
don't 'Manage Individual Subscriptions' or 'Manage All Subscriptions'
permissions then they will not see the subscription tab.
--
-Daniel
This posting is provided "AS IS" with no warranties, and confers no rights.
"Ameet" <Ameet@.discussions.microsoft.com> wrote in message
news:2A20E4F6-1158-4845-8032-69DA31902816@.microsoft.com...
> Hi guys
> I just want user see only content tab in Report Manager and not the
property
> tab .
> I create a new role and select 'VIEW ROLE' and 'VIEW REPORT' tasks,
> descriptions says that user will be able to see the properties too. I dont
> want let user see the property.
> Niether should user see the subscription link.
> How could I achieve that
> Sincerely
> Ameetsql
I just want user see only content tab in Report Manager and not the property
tab .
I create a new role and select 'VIEW ROLE' and 'VIEW REPORT' tasks,
descriptions says that user will be able to see the properties too. I dont
want let user see the property.
Niether should user see the subscription link.
How could I achieve that
Sincerely
AmeetI don't think it is possible to give a set of permissions where a user will
be able to view the report and not see the properties of the report. If you
don't 'Manage Individual Subscriptions' or 'Manage All Subscriptions'
permissions then they will not see the subscription tab.
--
-Daniel
This posting is provided "AS IS" with no warranties, and confers no rights.
"Ameet" <Ameet@.discussions.microsoft.com> wrote in message
news:2A20E4F6-1158-4845-8032-69DA31902816@.microsoft.com...
> Hi guys
> I just want user see only content tab in Report Manager and not the
property
> tab .
> I create a new role and select 'VIEW ROLE' and 'VIEW REPORT' tasks,
> descriptions says that user will be able to see the properties too. I dont
> want let user see the property.
> Niether should user see the subscription link.
> How could I achieve that
> Sincerely
> Ameetsql
Customise Report Parameters in Report Manager
Is it possible to add custom controls in for parameters in report manager?
I have a parameter that requires the user to click a button and select a bunch of things in another form. How can i do this in reporting services?
I also would like to change the layout of the parameters toolbar, ie. make text boxes smaller widths, show/hide parameters based on other parameter values.
I know i could write my own web page that does this and hide the parameters toolbar when they run the report, but this would mean that we lose other functionality that report manager has, eg. scheduling.
Does anyone know if customising the report manager will become available in a later version?
You're correct that you can't customize Report Manager like this in RS 2005. You're also correct that if you wrote your own app using the Reporting controls in VS 2005, you'd have a lot of flexibility.
As for whether you'll be able to do this customization of RM in a later version, I doubt it, but there is "talk" of shipping add'l VS Reporting controls for things like scheduling and security, so you could build your own RM implementation.
Customise Report Parameters in Report Manager
Is it possible to add custom controls in for parameters in report manager?
I have a parameter that requires the user to click a button and select a bunch of things in another form. How can i do this in reporting services?
I also would like to change the layout of the parameters toolbar, ie. make text boxes smaller widths, show/hide parameters based on other parameter values.
I know i could write my own web page that does this and hide the parameters toolbar when they run the report, but this would mean that we lose other functionality that report manager has, eg. scheduling.
Does anyone know if customising the report manager will become available in a later version?
You're correct that you can't customize Report Manager like this in RS 2005. You're also correct that if you wrote your own app using the Reporting controls in VS 2005, you'd have a lot of flexibility.
As for whether you'll be able to do this customization of RM in a later version, I doubt it, but there is "talk" of shipping add'l VS Reporting controls for things like scheduling and security, so you could build your own RM implementation.
customer wants to remove power user (was "Need some advise")
Hi everyone,
I have a customer that wants to remove a power user
from her company.
She has given him the SA password (against my advise)
and he has his own user account setup in the database.
I am not sure if he has created any backdoor(s) into the
SQL box or not - I left him kinda on his own and didn't
pay too much attention to him.
I assume people have faced this problem before,
and i am looking for a best practices or "here is what we did"
post mortem of how they handled the issue.
thanks
tonyChange the sa password (you have no apps accessing via sa right)?
Check all the high permissions roles (sysadmins, securityadmins etc) and account for all the logins associated with them. Be very suspicious of any that are not domain accounts.
That would do for starters.
Future ref - always give permissions like that to domain accounts not SQL ones.|||The Flump speaks wisdom.
Also check for any new SQL Agent jobs.|||Thanks for the info -
Agent jobs was checked and double checked.
Also did a grep on the stored procs for any create users references
I am going to make a backup of the database and restore it
into a virtual machine, change the SA and the users password
without deleting the user.
I will leave the server running for a day or so looking for issues before the production server is touched.
Thanks again for ALL the hep!
take care
tony|||The Flump speaks wisdom.
Shouldn't this already be in his sig by now? :p
I have a customer that wants to remove a power user
from her company.
She has given him the SA password (against my advise)
and he has his own user account setup in the database.
I am not sure if he has created any backdoor(s) into the
SQL box or not - I left him kinda on his own and didn't
pay too much attention to him.
I assume people have faced this problem before,
and i am looking for a best practices or "here is what we did"
post mortem of how they handled the issue.
thanks
tonyChange the sa password (you have no apps accessing via sa right)?
Check all the high permissions roles (sysadmins, securityadmins etc) and account for all the logins associated with them. Be very suspicious of any that are not domain accounts.
That would do for starters.
Future ref - always give permissions like that to domain accounts not SQL ones.|||The Flump speaks wisdom.
Also check for any new SQL Agent jobs.|||Thanks for the info -
Agent jobs was checked and double checked.
Also did a grep on the stored procs for any create users references
I am going to make a backup of the database and restore it
into a virtual machine, change the SA and the users password
without deleting the user.
I will leave the server running for a day or so looking for issues before the production server is touched.
Thanks again for ALL the hep!
take care
tony|||The Flump speaks wisdom.
Shouldn't this already be in his sig by now? :p
Customer Passwords in SQL Server
I currently have a table with user passwords
Ideally I want to encrypt these passwords...
I have read in places to use pwdencrypt and pwdcompare as this is a SQL
function. But I have also read the microsoft may update this Hash function,
and may lead to me losing all of my passwords...
Can anybody assist in the correct way to store passwords in a SQL table...
Kind RegardsWhat is your application programmed in? The asp.net security
whitepaper has a section on how to encrypt, salt and store information
securely in a SQL Server database. It's available at
http://www.microsoft.com/downloads/...ReleaseID=44047
--Mary
On Wed, 1 Sep 2004 01:21:05 -0700, "Jay_Reborn"
<JayReborn@.discussions.microsoft.com> wrote:
>I currently have a table with user passwords
>Ideally I want to encrypt these passwords...
>I have read in places to use pwdencrypt and pwdcompare as this is a SQL
>function. But I have also read the microsoft may update this Hash function,
>and may lead to me losing all of my passwords...
>Can anybody assist in the correct way to store passwords in a SQL table...
>Kind Regards
>|||If you are using ASP.NET, this article might be helpful to you:
rl]
erik perez
[url]www.solien.com" target="_blank">http://www.dotnetjunkies.com/Tutori...]www.solien.com
"Jay_Reborn" <JayReborn@.discussions.microsoft.com> wrote in message
news:D673C5FE-DB92-4E8B-A598-F843AEE40176@.microsoft.com...
> I currently have a table with user passwords
> Ideally I want to encrypt these passwords...
> I have read in places to use pwdencrypt and pwdcompare as this is a SQL
> function. But I have also read the microsoft may update this Hash
function,
> and may lead to me losing all of my passwords...
> Can anybody assist in the correct way to store passwords in a SQL table...
> Kind Regards
>sql
Ideally I want to encrypt these passwords...
I have read in places to use pwdencrypt and pwdcompare as this is a SQL
function. But I have also read the microsoft may update this Hash function,
and may lead to me losing all of my passwords...
Can anybody assist in the correct way to store passwords in a SQL table...
Kind RegardsWhat is your application programmed in? The asp.net security
whitepaper has a section on how to encrypt, salt and store information
securely in a SQL Server database. It's available at
http://www.microsoft.com/downloads/...ReleaseID=44047
--Mary
On Wed, 1 Sep 2004 01:21:05 -0700, "Jay_Reborn"
<JayReborn@.discussions.microsoft.com> wrote:
>I currently have a table with user passwords
>Ideally I want to encrypt these passwords...
>I have read in places to use pwdencrypt and pwdcompare as this is a SQL
>function. But I have also read the microsoft may update this Hash function,
>and may lead to me losing all of my passwords...
>Can anybody assist in the correct way to store passwords in a SQL table...
>Kind Regards
>|||If you are using ASP.NET, this article might be helpful to you:
rl]
erik perez
[url]www.solien.com" target="_blank">http://www.dotnetjunkies.com/Tutori...]www.solien.com
"Jay_Reborn" <JayReborn@.discussions.microsoft.com> wrote in message
news:D673C5FE-DB92-4E8B-A598-F843AEE40176@.microsoft.com...
> I currently have a table with user passwords
> Ideally I want to encrypt these passwords...
> I have read in places to use pwdencrypt and pwdcompare as this is a SQL
> function. But I have also read the microsoft may update this Hash
function,
> and may lead to me losing all of my passwords...
> Can anybody assist in the correct way to store passwords in a SQL table...
> Kind Regards
>sql
Thursday, March 22, 2012
Custom user field in database
Is there a SQL 2005 table containing meta data about my database where I can set a column whose
purpose is to hold custom user data?
I have an application for which I need to change the table structure from version to version. Each
time I distribute an new build of the application, the setup program lookupthe DB revision level
then issue the proper DDL calls to perform the updates.
I could always create a small user table with only one column into which I would store the DB
revision level. However, I would prefer store this INT value somewhere else if SQL 2005 offers a
capability to do so.
Gaetan.
Look in Books Online about using Database Extended Properties. Relatively
easy to code to, and retrieve from; custom name value pairs.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"Gaetan" <me@.somewhere.com> wrote in message
news:q4d9m2t2o8suqoo678js6qndkk59721ji5@.4ax.com...
> Is there a SQL 2005 table containing meta data about my database where I
> can set a column whose
> purpose is to hold custom user data?
> I have an application for which I need to change the table structure from
> version to version. Each
> time I distribute an new build of the application, the setup program
> lookupthe DB revision level
> then issue the proper DDL calls to perform the updates.
> I could always create a small user table with only one column into which I
> would store the DB
> revision level. However, I would prefer store this INT value somewhere
> else if SQL 2005 offers a
> capability to do so.
> Gaetan.
|||sys.sp_addextendedproperty and sys.sp_updateextendedproperty are exactly what I needed.
Thank you.
purpose is to hold custom user data?
I have an application for which I need to change the table structure from version to version. Each
time I distribute an new build of the application, the setup program lookupthe DB revision level
then issue the proper DDL calls to perform the updates.
I could always create a small user table with only one column into which I would store the DB
revision level. However, I would prefer store this INT value somewhere else if SQL 2005 offers a
capability to do so.
Gaetan.
Look in Books Online about using Database Extended Properties. Relatively
easy to code to, and retrieve from; custom name value pairs.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"Gaetan" <me@.somewhere.com> wrote in message
news:q4d9m2t2o8suqoo678js6qndkk59721ji5@.4ax.com...
> Is there a SQL 2005 table containing meta data about my database where I
> can set a column whose
> purpose is to hold custom user data?
> I have an application for which I need to change the table structure from
> version to version. Each
> time I distribute an new build of the application, the setup program
> lookupthe DB revision level
> then issue the proper DDL calls to perform the updates.
> I could always create a small user table with only one column into which I
> would store the DB
> revision level. However, I would prefer store this INT value somewhere
> else if SQL 2005 offers a
> capability to do so.
> Gaetan.
|||sys.sp_addextendedproperty and sys.sp_updateextendedproperty are exactly what I needed.
Thank you.
Custom user field in database
Is there a SQL 2005 table containing meta data about my database where I can set a column whose
purpose is to hold custom user data?
I have an application for which I need to change the table structure from version to version. Each
time I distribute an new build of the application, the setup program lookupthe DB revision level
then issue the proper DDL calls to perform the updates.
I could always create a small user table with only one column into which I would store the DB
revision level. However, I would prefer store this INT value somewhere else if SQL 2005 offers a
capability to do so.
Gaetan.Look in Books Online about using Database Extended Properties. Relatively
easy to code to, and retrieve from; custom name value pairs.
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"Gaetan" <me@.somewhere.com> wrote in message
news:q4d9m2t2o8suqoo678js6qndkk59721ji5@.4ax.com...
> Is there a SQL 2005 table containing meta data about my database where I
> can set a column whose
> purpose is to hold custom user data?
> I have an application for which I need to change the table structure from
> version to version. Each
> time I distribute an new build of the application, the setup program
> lookupthe DB revision level
> then issue the proper DDL calls to perform the updates.
> I could always create a small user table with only one column into which I
> would store the DB
> revision level. However, I would prefer store this INT value somewhere
> else if SQL 2005 offers a
> capability to do so.
> Gaetan.|||sys.sp_addextendedproperty and sys.sp_updateextendedproperty are exactly what I needed.
Thank you.
purpose is to hold custom user data?
I have an application for which I need to change the table structure from version to version. Each
time I distribute an new build of the application, the setup program lookupthe DB revision level
then issue the proper DDL calls to perform the updates.
I could always create a small user table with only one column into which I would store the DB
revision level. However, I would prefer store this INT value somewhere else if SQL 2005 offers a
capability to do so.
Gaetan.Look in Books Online about using Database Extended Properties. Relatively
easy to code to, and retrieve from; custom name value pairs.
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"Gaetan" <me@.somewhere.com> wrote in message
news:q4d9m2t2o8suqoo678js6qndkk59721ji5@.4ax.com...
> Is there a SQL 2005 table containing meta data about my database where I
> can set a column whose
> purpose is to hold custom user data?
> I have an application for which I need to change the table structure from
> version to version. Each
> time I distribute an new build of the application, the setup program
> lookupthe DB revision level
> then issue the proper DDL calls to perform the updates.
> I could always create a small user table with only one column into which I
> would store the DB
> revision level. However, I would prefer store this INT value somewhere
> else if SQL 2005 offers a
> capability to do so.
> Gaetan.|||sys.sp_addextendedproperty and sys.sp_updateextendedproperty are exactly what I needed.
Thank you.
Custom user field in database
Is there a SQL 2005 table containing meta data about my database where I can
set a column whose
purpose is to hold custom user data?
I have an application for which I need to change the table structure from ve
rsion to version. Each
time I distribute an new build of the application, the setup program lookupt
he DB revision level
then issue the proper DDL calls to perform the updates.
I could always create a small user table with only one column into which I w
ould store the DB
revision level. However, I would prefer store this INT value somewhere else
if SQL 2005 offers a
capability to do so.
Gaetan.Look in Books Online about using Database Extended Properties. Relatively
easy to code to, and retrieve from; custom name value pairs.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"Gaetan" <me@.somewhere.com> wrote in message
news:q4d9m2t2o8suqoo678js6qndkk59721ji5@.
4ax.com...
> Is there a SQL 2005 table containing meta data about my database where I
> can set a column whose
> purpose is to hold custom user data?
> I have an application for which I need to change the table structure from
> version to version. Each
> time I distribute an new build of the application, the setup program
> lookupthe DB revision level
> then issue the proper DDL calls to perform the updates.
> I could always create a small user table with only one column into which I
> would store the DB
> revision level. However, I would prefer store this INT value somewhere
> else if SQL 2005 offers a
> capability to do so.
> Gaetan.|||sys.sp_addextendedproperty and sys.sp_updateextendedproperty are exactly w
hat I needed.
Thank you.sql
set a column whose
purpose is to hold custom user data?
I have an application for which I need to change the table structure from ve
rsion to version. Each
time I distribute an new build of the application, the setup program lookupt
he DB revision level
then issue the proper DDL calls to perform the updates.
I could always create a small user table with only one column into which I w
ould store the DB
revision level. However, I would prefer store this INT value somewhere else
if SQL 2005 offers a
capability to do so.
Gaetan.Look in Books Online about using Database Extended Properties. Relatively
easy to code to, and retrieve from; custom name value pairs.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"Gaetan" <me@.somewhere.com> wrote in message
news:q4d9m2t2o8suqoo678js6qndkk59721ji5@.
4ax.com...
> Is there a SQL 2005 table containing meta data about my database where I
> can set a column whose
> purpose is to hold custom user data?
> I have an application for which I need to change the table structure from
> version to version. Each
> time I distribute an new build of the application, the setup program
> lookupthe DB revision level
> then issue the proper DDL calls to perform the updates.
> I could always create a small user table with only one column into which I
> would store the DB
> revision level. However, I would prefer store this INT value somewhere
> else if SQL 2005 offers a
> capability to do so.
> Gaetan.|||sys.sp_addextendedproperty and sys.sp_updateextendedproperty are exactly w
hat I needed.
Thank you.sql
Custom unique ID.
Hello all,
I baddly need to create my own datatype, that will be able to generate
itself as unique id.
1. my user defined data type is based on char(14).
2. structure of this type is
YYYYMMDDSCIDCONT
YYYY = year of record creation
MM = month of record creation
DD = day of record creation
SCID = value of SCID column in the table
CONT = counter (like identity seed) next unique value in the table
I can to do it by my .NET application, or i can make unique keys with all
values in the table, but I would like to create my own SQL type, that will
be able to do what i described. Is it possible?
Thanks.Maybe I should better explain this or ask for some questions.
My task Im going to do:
1) create my own User Defined Data Type based on char(16) named for example
MyIDType
2) create rule that will check if the value is in right format.
3) create function, that will able to generate new id with parameters
datetime, scid. This fuction will parse the datetime, scid and newly created
counter into char(16) and into MyIDType.
Questions:
1) can I create rule that contain more complex check than one rule row ?
2) How to ask for source table in T-SQL function. I mean for the table which
initiated running of my function. Im going to put this function onto
defaultValue of the column that will be type of MyIDType. The function will
suppose, that in this table will exist columns CreationDateTime and SCID.
I know, how to do it in C#. The workflow of this function should be (it is
an example, dont check any syntax):
FUNCTION GetNewMyID (@.scid char(4))
DECLARE @.datetimenow char(8)
DECLARE @.lastid char(4)
DECLARE @.partOfMyID char(12)
@.datetimenow = FORMAT( GETDATE()) // in format YYYYMMDD
for(int i = 1; i < 10000; i++)
{
@.partOfMyID = @.datetimenow + @.scid + i.ToString("0000")
SELECT MyID FROM [executingTable?] WHERE MyID LIKE @.partOfMyID
if(there is no row)
return @.partOfMyID;
}
I hope, the good programer will understand this weird construction. :)))
Thanks.
"Mirek Endys" <MirekE@.community.nospam> wrote in message
news:%23$vH%23RZTGHA.196@.TK2MSFTNGP10.phx.gbl...
> Hello all,
> I baddly need to create my own datatype, that will be able to generate
> itself as unique id.
> 1. my user defined data type is based on char(16).
> 2. structure of this type is
> YYYYMMDDSCIDCONT
> YYYY = year of record creation
> MM = month of record creation
> DD = day of record creation
> SCID = value of SCID column in the table
> CONT = counter (like identity seed) next unique value in the table
> I can to do it by my .NET application, or i can make unique keys with all
> values in the table, but I would like to create my own SQL type, that will
> be able to do what i described. Is it possible?
> Thanks.
>|||hi Mirek,
Sure.
I understand that function but what does 'scid' mean? Let me know.
Current location: Alicante (ES)
"Mirek Endys" wrote:
> Maybe I should better explain this or ask for some questions.
> My task Im going to do:
> 1) create my own User Defined Data Type based on char(16) named for exampl
e
> MyIDType
> 2) create rule that will check if the value is in right format.
> 3) create function, that will able to generate new id with parameters
> datetime, scid. This fuction will parse the datetime, scid and newly creat
ed
> counter into char(16) and into MyIDType.
> Questions:
> 1) can I create rule that contain more complex check than one rule row ?
> 2) How to ask for source table in T-SQL function. I mean for the table whi
ch
> initiated running of my function. Im going to put this function onto
> defaultValue of the column that will be type of MyIDType. The function wil
l
> suppose, that in this table will exist columns CreationDateTime and SCID.
> I know, how to do it in C#. The workflow of this function should be (it is
> an example, dont check any syntax):
> FUNCTION GetNewMyID (@.scid char(4))
> DECLARE @.datetimenow char(8)
> DECLARE @.lastid char(4)
> DECLARE @.partOfMyID char(12)
> @.datetimenow = FORMAT( GETDATE()) // in format YYYYMMDD
>
> for(int i = 1; i < 10000; i++)
> {
> @.partOfMyID = @.datetimenow + @.scid + i.ToString("0000")
> SELECT MyID FROM [executingTable?] WHERE MyID LIKE @.partOfMyID
> if(there is no row)
> return @.partOfMyID;
> }
> I hope, the good programer will understand this weird construction. :)))
> Thanks.
>
> "Mirek Endys" <MirekE@.community.nospam> wrote in message
> news:%23$vH%23RZTGHA.196@.TK2MSFTNGP10.phx.gbl...
>
>|||Hi Enric
the SCID is variable - value that is contained in inserted record... or
passed by function parameter.
"Enric" <vtam13@.terra.es.(donotspam)> wrote in message
news:6DE5E6D0-9B7A-421C-A6BF-14E33D967B06@.microsoft.com...
> hi Mirek,
> Sure.
> I understand that function but what does 'scid' mean? Let me know.
> Current location: Alicante (ES)
>
> "Mirek Endys" wrote:
>|||Hi Mirek,
As for the SQL 2005 clr UDT, it is still quiet limited, e.g, if the data
type used in the UDT is not in the following basic types:
bool, byte, sbyte, short, ushort, int, uint, long, ulong, float, double,
SqlByte, SqlInt16, SqlInt32, SqlInt64, SqlDateTime, SqlSingle, SqlDouble,
SqlMoney, SqlBoolean
we have to implenent our userdefined serialization. For type validation, we
can define the validation rules in a certain method and mark it with the
"ValidationMethodName" attribute.
And for the constructing a new UDT instance based on some parameters or
other info in the current database, this is hard to be done through the UDT
itself since UDT just provide a "parse" function which help use create UDT
instance from a string representation(also a "ToString" method help return
string representation from a UDT instance). For your scenario, maybe we
have to define an additional user defined funciton(CLR based) to the work.
#User-Defined Type Requirements
http://msdn2.microsoft.com/en-us/library/f33b06h1.aspx
Regards,
Steven Cheng
Microsoft Online Community Support
========================================
==========
When responding to posts, please "Reply to Group" via your newsreader so
that others may
learn and benefit from your issue.
========================================
==========
This posting is provided "AS IS" with no warranties, and confers no rights.|||You can create static method(s) in your UDT to create. Kinda like a static
constructor. Something like:
public static MyID Create(SqlDateTime dt, SqlInt id, SqlInt counter)
{
// ...
return new MyID(dt, id, counter);
}
Use the sql types that make sense for your needs.
Then you can create an instance something like:
declare @.id MyID
set @.id = MyID.Create(getdate(), 1, 1)
William Stacey [MVP]
"Mirek Endys" <MirekE@.community.nospam> wrote in message
news:%23$vH%23RZTGHA.196@.TK2MSFTNGP10.phx.gbl...
| Hello all,
|
| I baddly need to create my own datatype, that will be able to generate
| itself as unique id.
|
| 1. my user defined data type is based on char(14).
| 2. structure of this type is
| YYYYMMDDSCIDCONT
|
| YYYY = year of record creation
| MM = month of record creation
| DD = day of record creation
| SCID = value of SCID column in the table
| CONT = counter (like identity seed) next unique value in the table
|
| I can to do it by my .NET application, or i can make unique keys with all
| values in the table, but I would like to create my own SQL type, that will
| be able to do what i described. Is it possible?
|
| Thanks.
|
||||Thank for Williams inputs.
This is a good suggestion that directly use a static method on the type.
Regards,
Steven Cheng
Microsoft Online Community Support
========================================
==========
When responding to posts, please "Reply to Group" via your newsreader so
that others may
learn and benefit from your issue.
========================================
==========
This posting is provided "AS IS" with no warranties, and confers no rights.|||Thanks guys,
i thought so, that this will be solution.
"Steven Cheng[MSFT]" <stcheng@.online.microsoft.com> wrote in message
news:wQfSEekTGHA.5536@.TK2MSFTNGXA03.phx.gbl...
> Thank for Williams inputs.
> This is a good suggestion that directly use a static method on the type.
> Regards,
> Steven Cheng
> Microsoft Online Community Support
>
> ========================================
==========
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may
> learn and benefit from your issue.
> ========================================
==========
>
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>|||Just a note after looking at it again. You need the double "::" to refer to
the static method on a type from tsql. Such as:
set @.id = MyID::Create(getdate(), 1, 1)
William Stacey [MVP]
"Mirek Endys" <MirekE@.community.nospam> wrote in message
news:uYBv3FmTGHA.4792@.TK2MSFTNGP14.phx.gbl...
| Thanks guys,
|
| i thought so, that this will be solution.
|
|
| "Steven Cheng[MSFT]" <stcheng@.online.microsoft.com> wrote in message
| news:wQfSEekTGHA.5536@.TK2MSFTNGXA03.phx.gbl...
| > Thank for Williams inputs.
| >
| > This is a good suggestion that directly use a static method on the type.
| >
| > Regards,
| >
| > Steven Cheng
| > Microsoft Online Community Support
| >
| >
| > ========================================
==========
| >
| > When responding to posts, please "Reply to Group" via your newsreader so
| > that others may
| >
| > learn and benefit from your issue.
| >
| > ========================================
==========
| >
| >
| > This posting is provided "AS IS" with no warranties, and confers no
| > rights.
| >
|
|
I baddly need to create my own datatype, that will be able to generate
itself as unique id.
1. my user defined data type is based on char(14).
2. structure of this type is
YYYYMMDDSCIDCONT
YYYY = year of record creation
MM = month of record creation
DD = day of record creation
SCID = value of SCID column in the table
CONT = counter (like identity seed) next unique value in the table
I can to do it by my .NET application, or i can make unique keys with all
values in the table, but I would like to create my own SQL type, that will
be able to do what i described. Is it possible?
Thanks.Maybe I should better explain this or ask for some questions.
My task Im going to do:
1) create my own User Defined Data Type based on char(16) named for example
MyIDType
2) create rule that will check if the value is in right format.
3) create function, that will able to generate new id with parameters
datetime, scid. This fuction will parse the datetime, scid and newly created
counter into char(16) and into MyIDType.
Questions:
1) can I create rule that contain more complex check than one rule row ?
2) How to ask for source table in T-SQL function. I mean for the table which
initiated running of my function. Im going to put this function onto
defaultValue of the column that will be type of MyIDType. The function will
suppose, that in this table will exist columns CreationDateTime and SCID.
I know, how to do it in C#. The workflow of this function should be (it is
an example, dont check any syntax):
FUNCTION GetNewMyID (@.scid char(4))
DECLARE @.datetimenow char(8)
DECLARE @.lastid char(4)
DECLARE @.partOfMyID char(12)
@.datetimenow = FORMAT( GETDATE()) // in format YYYYMMDD
for(int i = 1; i < 10000; i++)
{
@.partOfMyID = @.datetimenow + @.scid + i.ToString("0000")
SELECT MyID FROM [executingTable?] WHERE MyID LIKE @.partOfMyID
if(there is no row)
return @.partOfMyID;
}
I hope, the good programer will understand this weird construction. :)))
Thanks.
"Mirek Endys" <MirekE@.community.nospam> wrote in message
news:%23$vH%23RZTGHA.196@.TK2MSFTNGP10.phx.gbl...
> Hello all,
> I baddly need to create my own datatype, that will be able to generate
> itself as unique id.
> 1. my user defined data type is based on char(16).
> 2. structure of this type is
> YYYYMMDDSCIDCONT
> YYYY = year of record creation
> MM = month of record creation
> DD = day of record creation
> SCID = value of SCID column in the table
> CONT = counter (like identity seed) next unique value in the table
> I can to do it by my .NET application, or i can make unique keys with all
> values in the table, but I would like to create my own SQL type, that will
> be able to do what i described. Is it possible?
> Thanks.
>|||hi Mirek,
Sure.
I understand that function but what does 'scid' mean? Let me know.
Current location: Alicante (ES)
"Mirek Endys" wrote:
> Maybe I should better explain this or ask for some questions.
> My task Im going to do:
> 1) create my own User Defined Data Type based on char(16) named for exampl
e
> MyIDType
> 2) create rule that will check if the value is in right format.
> 3) create function, that will able to generate new id with parameters
> datetime, scid. This fuction will parse the datetime, scid and newly creat
ed
> counter into char(16) and into MyIDType.
> Questions:
> 1) can I create rule that contain more complex check than one rule row ?
> 2) How to ask for source table in T-SQL function. I mean for the table whi
ch
> initiated running of my function. Im going to put this function onto
> defaultValue of the column that will be type of MyIDType. The function wil
l
> suppose, that in this table will exist columns CreationDateTime and SCID.
> I know, how to do it in C#. The workflow of this function should be (it is
> an example, dont check any syntax):
> FUNCTION GetNewMyID (@.scid char(4))
> DECLARE @.datetimenow char(8)
> DECLARE @.lastid char(4)
> DECLARE @.partOfMyID char(12)
> @.datetimenow = FORMAT( GETDATE()) // in format YYYYMMDD
>
> for(int i = 1; i < 10000; i++)
> {
> @.partOfMyID = @.datetimenow + @.scid + i.ToString("0000")
> SELECT MyID FROM [executingTable?] WHERE MyID LIKE @.partOfMyID
> if(there is no row)
> return @.partOfMyID;
> }
> I hope, the good programer will understand this weird construction. :)))
> Thanks.
>
> "Mirek Endys" <MirekE@.community.nospam> wrote in message
> news:%23$vH%23RZTGHA.196@.TK2MSFTNGP10.phx.gbl...
>
>|||Hi Enric
the SCID is variable - value that is contained in inserted record... or
passed by function parameter.
"Enric" <vtam13@.terra.es.(donotspam)> wrote in message
news:6DE5E6D0-9B7A-421C-A6BF-14E33D967B06@.microsoft.com...
> hi Mirek,
> Sure.
> I understand that function but what does 'scid' mean? Let me know.
> Current location: Alicante (ES)
>
> "Mirek Endys" wrote:
>|||Hi Mirek,
As for the SQL 2005 clr UDT, it is still quiet limited, e.g, if the data
type used in the UDT is not in the following basic types:
bool, byte, sbyte, short, ushort, int, uint, long, ulong, float, double,
SqlByte, SqlInt16, SqlInt32, SqlInt64, SqlDateTime, SqlSingle, SqlDouble,
SqlMoney, SqlBoolean
we have to implenent our userdefined serialization. For type validation, we
can define the validation rules in a certain method and mark it with the
"ValidationMethodName" attribute.
And for the constructing a new UDT instance based on some parameters or
other info in the current database, this is hard to be done through the UDT
itself since UDT just provide a "parse" function which help use create UDT
instance from a string representation(also a "ToString" method help return
string representation from a UDT instance). For your scenario, maybe we
have to define an additional user defined funciton(CLR based) to the work.
#User-Defined Type Requirements
http://msdn2.microsoft.com/en-us/library/f33b06h1.aspx
Regards,
Steven Cheng
Microsoft Online Community Support
========================================
==========
When responding to posts, please "Reply to Group" via your newsreader so
that others may
learn and benefit from your issue.
========================================
==========
This posting is provided "AS IS" with no warranties, and confers no rights.|||You can create static method(s) in your UDT to create. Kinda like a static
constructor. Something like:
public static MyID Create(SqlDateTime dt, SqlInt id, SqlInt counter)
{
// ...
return new MyID(dt, id, counter);
}
Use the sql types that make sense for your needs.
Then you can create an instance something like:
declare @.id MyID
set @.id = MyID.Create(getdate(), 1, 1)
William Stacey [MVP]
"Mirek Endys" <MirekE@.community.nospam> wrote in message
news:%23$vH%23RZTGHA.196@.TK2MSFTNGP10.phx.gbl...
| Hello all,
|
| I baddly need to create my own datatype, that will be able to generate
| itself as unique id.
|
| 1. my user defined data type is based on char(14).
| 2. structure of this type is
| YYYYMMDDSCIDCONT
|
| YYYY = year of record creation
| MM = month of record creation
| DD = day of record creation
| SCID = value of SCID column in the table
| CONT = counter (like identity seed) next unique value in the table
|
| I can to do it by my .NET application, or i can make unique keys with all
| values in the table, but I would like to create my own SQL type, that will
| be able to do what i described. Is it possible?
|
| Thanks.
|
||||Thank for Williams inputs.
This is a good suggestion that directly use a static method on the type.
Regards,
Steven Cheng
Microsoft Online Community Support
========================================
==========
When responding to posts, please "Reply to Group" via your newsreader so
that others may
learn and benefit from your issue.
========================================
==========
This posting is provided "AS IS" with no warranties, and confers no rights.|||Thanks guys,
i thought so, that this will be solution.
"Steven Cheng[MSFT]" <stcheng@.online.microsoft.com> wrote in message
news:wQfSEekTGHA.5536@.TK2MSFTNGXA03.phx.gbl...
> Thank for Williams inputs.
> This is a good suggestion that directly use a static method on the type.
> Regards,
> Steven Cheng
> Microsoft Online Community Support
>
> ========================================
==========
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may
> learn and benefit from your issue.
> ========================================
==========
>
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>|||Just a note after looking at it again. You need the double "::" to refer to
the static method on a type from tsql. Such as:
set @.id = MyID::Create(getdate(), 1, 1)
William Stacey [MVP]
"Mirek Endys" <MirekE@.community.nospam> wrote in message
news:uYBv3FmTGHA.4792@.TK2MSFTNGP14.phx.gbl...
| Thanks guys,
|
| i thought so, that this will be solution.
|
|
| "Steven Cheng[MSFT]" <stcheng@.online.microsoft.com> wrote in message
| news:wQfSEekTGHA.5536@.TK2MSFTNGXA03.phx.gbl...
| > Thank for Williams inputs.
| >
| > This is a good suggestion that directly use a static method on the type.
| >
| > Regards,
| >
| > Steven Cheng
| > Microsoft Online Community Support
| >
| >
| > ========================================
==========
| >
| > When responding to posts, please "Reply to Group" via your newsreader so
| > that others may
| >
| > learn and benefit from your issue.
| >
| > ========================================
==========
| >
| >
| > This posting is provided "AS IS" with no warranties, and confers no
| > rights.
| >
|
|
Custom Task w/ runtime user interaction
I've been playing around with building custom components for SSIS. I've been doing workflow for years (using Java and Oracle). The company I worked for had a framework for publishing data that allowed for user interaction. That's something I'd love to be able to do in SSIS.
Is it possible to create a custom task that interacts with the user at runtime? So, the user starts the SSIS package. At some point, the process pops up a dialog (Windows Form) that asks the user to set a date using a calendar control.
Any thoughts?
Is it possible? Yes. Is it recommended? No. SSIS is really designed for batch, non-interactive applications. It would be much better to write a custom app that gathers all the user input in the beginning, and then launches the SSIS package, setting any necessary variables based on the user input at that point.|||That sounds like a great plan...only I cannot figure out a way to do that which does not require writing an entire program, figuring out a way to store the variable in a file, or somehow PASS it to the SQL SSIS Package, and I have NO IDEA how to do that.
I have the EXACT same need as the person who asked you the question, and I'd be happy with manually entering the date one character at a time. The SSIS Package I am writing pulls special information from a database and places it in a text file with fixed widths. I have everythign on this working perfectly. The problem is that Every three Months - I have to write it again from scratch because the starting date changes.
i just want the simplest way to enter a NEW Starting Date (even if that means storing it somewhere on a file server), and run the Agent using the SSIS Package to make it work.
My Perfect Solution would be a THREE STEP Agent, with Step One getting the variable and passing it to step 2, Step 2 being the query I have now importing the date it got from step one, and step three being the notify portion when it is done.
I can find NO USEDUL INFORMATION on how to do this...or maybe I just don't get it.
|||For that, I would define a variable in the Package to hold the start date. Then use DTEXEC with the /SET switch, which allows you to set a variable's value from the command line. Agent can run DTEXEC, passing it the /SET value.
Another option would be to store the date in a table on your database, and read it into the package using an Execute SQL task.
Since you are running this from Agent, you really don't want a UI, since no user will be there to respond to it.
Custom Task w/ runtime user interaction
I've been playing around with building custom components for SSIS. I've been doing workflow for years (using Java and Oracle). The company I worked for had a framework for publishing data that allowed for user interaction. That's something I'd love to be able to do in SSIS.
Is it possible to create a custom task that interacts with the user at runtime? So, the user starts the SSIS package. At some point, the process pops up a dialog (Windows Form) that asks the user to set a date using a calendar control.
Any thoughts?
Is it possible? Yes. Is it recommended? No. SSIS is really designed for batch, non-interactive applications. It would be much better to write a custom app that gathers all the user input in the beginning, and then launches the SSIS package, setting any necessary variables based on the user input at that point.|||That sounds like a great plan...only I cannot figure out a way to do that which does not require writing an entire program, figuring out a way to store the variable in a file, or somehow PASS it to the SQL SSIS Package, and I have NO IDEA how to do that.
I have the EXACT same need as the person who asked you the question, and I'd be happy with manually entering the date one character at a time. The SSIS Package I am writing pulls special information from a database and places it in a text file with fixed widths. I have everythign on this working perfectly. The problem is that Every three Months - I have to write it again from scratch because the starting date changes.
i just want the simplest way to enter a NEW Starting Date (even if that means storing it somewhere on a file server), and run the Agent using the SSIS Package to make it work.
My Perfect Solution would be a THREE STEP Agent, with Step One getting the variable and passing it to step 2, Step 2 being the query I have now importing the date it got from step one, and step three being the notify portion when it is done.
I can find NO USEDUL INFORMATION on how to do this...or maybe I just don't get it.
|||For that, I would define a variable in the Package to hold the start date. Then use DTEXEC with the /SET switch, which allows you to set a variable's value from the command line. Agent can run DTEXEC, passing it the /SET value.
Another option would be to store the date in a table on your database, and read it into the package using an Execute SQL task.
Since you are running this from Agent, you really don't want a UI, since no user will be there to respond to it.
Tuesday, March 20, 2012
Custom source adapter UI
I am currently writing a custom source adapter that extracts data from a JD Edwards OneWorld system. In the custom user interface of the source component I need to allow the user to set a query (a custom property), and then refresh a list of output columns that will be extracted based upon that query (similar to the list shown in the advanced editor).
My question is, can I apply the custom property change to the component and build my output column list without closing and restarting my custom UI form? I understand that the IDTSComponentUI interface being implemented allows for transactional editing of the component, in that the changes to the component are not applied until I have returned a result in the implemented Edit() method. However is there a way to apply changes without returning this result (and closing my UI)?
Essentially I am looking to have similar behaviour to that of the 'Refresh' button in the advanced component editor form.
Thanks
The transactional edit work by cloning the component - the UI works only with the component clone, and can use all the functionality of the component. So when the component property changes, your UI should apply the changes to the clone, the component (clone) refreshes it output columns and then UI can show refreshed list of output columns. After user closes the UI, the changes are applied to the original component.|||great, thanks for the response
Custom sort the dimension members
Hello users of SSAS2005!
I am in the funny position of developing a cube for (at least) three different user groups, and each need "their" special treatment. My problem is in sort order of one particular dimension. The dimension has 3 fields: ID (=person number), Firstname and Lastname - as simple as straightforward as it can be.
Now Finance wants it sorted by ID to relate it to their documents. Human resources needs it by last name, so it can be copy-pasted directly into their structures.
It gets really hilarious when IT needs it sorted by first name (not joking here) because an external application delivers correlating date sorted by first name (again, I am not making that up).
Since I cannot convience anyone onto one common sort I need to make all three possible sort orders available, and I might end up with having 3 identical dimensions or one dimension with 3 identical attributes (except for sorting)
We use Excel as our frontend, I know I can sort a cube there as well. This is, of course, if the field to base the sorting upon is dragged onto the same axis, but that is a bit clumsy too.
What is the "best practices" approach to such a funny situation? (Except for changing the company workflow, let's take that as ... sigh ... static.)
Hello Ralf! I am not sure that this is possible in SSAS/MDX.
One way to solve this is to make the SSAS 2005 reports in Reporting Services 2005.
Check Books On Line for dynamic sorting in reporting services. It is also possible to sort with parameters.
If you can only use Excel pivot tables you can make three different reports, each sorted according to each groups preference.
Publish them i Sharepoint(or any other portal).
HTH
Thomas Ivarsson
HTH
Thomas Ivarsson
Custom server permissions?
Hi,
I need to grant a user access to one or two specific system stored
procs without giving permission to everything else in the fixed server
role that allows them. Specifically, they need rights to
sp_addlinkedserver and sp_addlinkedsrvlogin, but they shouldn't have
all the rights associated with securityadmin. Is there a way to grant
specific rights to just these?
TIA,
BarryBoth of these system stored procedures have hard coded permission checks in
them. I'm not a big fan of these as it really limits flexibility. The only
way would be to alter them to remove these checks but this would not be a
supported scenario. sp_addlinkedserver has a hard coded check for membership
of the setupadmin server role and sp_addlinkedsrvlogin has a hard coded
check for membership of the securityadmin server role. The only alternative
would be one that I use a lot for scenarios where I want lower privilege
users to be able to do a very specific action and that is to write a queue
system whereby they basically have a table that they can insert rows into
via a stored procedure and this table is polled by a SQL Agent job that runs
once a minute and executes the specific commands they are allowed to run.
This way you can code so they can only do a very specific action (otherwise
you would lead yourself open a large security hole)
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
<barrygilbertusa_no_spam@.yahoo.com> wrote in message
news:1101316733.934708.165050@.f14g2000cwb.googlegroups.com...
> Hi,
> I need to grant a user access to one or two specific system stored
> procs without giving permission to everything else in the fixed server
> role that allows them. Specifically, they need rights to
> sp_addlinkedserver and sp_addlinkedsrvlogin, but they shouldn't have
> all the rights associated with securityadmin. Is there a way to grant
> specific rights to just these?
> TIA,
> Barry
>|||Jasper,
Thanks for your reply. I'll give this a try.
Barry
"Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message news:<#hwMr6n0EHA.1392@.TK2MSFTNG
P14.phx.gbl>...[vbcol=seagreen]
> Both of these system stored procedures have hard coded permission checks i
n
> them. I'm not a big fan of these as it really limits flexibility. The only
> way would be to alter them to remove these checks but this would not be a
> supported scenario. sp_addlinkedserver has a hard coded check for membersh
ip
> of the setupadmin server role and sp_addlinkedsrvlogin has a hard coded
> check for membership of the securityadmin server role. The only alternativ
e
> would be one that I use a lot for scenarios where I want lower privilege
> users to be able to do a very specific action and that is to write a queue
> system whereby they basically have a table that they can insert rows into
> via a stored procedure and this table is polled by a SQL Agent job that ru
ns
> once a minute and executes the specific commands they are allowed to run.
> This way you can code so they can only do a very specific action (otherwis
e
> you would lead yourself open a large security hole)
> --
> HTH
> Jasper Smith (SQL Server MVP)
> http://www.sqldbatips.com
> I support PASS - the definitive, global
> community for SQL Server professionals -
> http://www.sqlpass.org
> <barrygilbertusa_no_spam@.yahoo.com> wrote in message
> news:1101316733.934708.165050@.f14g2000cwb.googlegroups.com...
I need to grant a user access to one or two specific system stored
procs without giving permission to everything else in the fixed server
role that allows them. Specifically, they need rights to
sp_addlinkedserver and sp_addlinkedsrvlogin, but they shouldn't have
all the rights associated with securityadmin. Is there a way to grant
specific rights to just these?
TIA,
BarryBoth of these system stored procedures have hard coded permission checks in
them. I'm not a big fan of these as it really limits flexibility. The only
way would be to alter them to remove these checks but this would not be a
supported scenario. sp_addlinkedserver has a hard coded check for membership
of the setupadmin server role and sp_addlinkedsrvlogin has a hard coded
check for membership of the securityadmin server role. The only alternative
would be one that I use a lot for scenarios where I want lower privilege
users to be able to do a very specific action and that is to write a queue
system whereby they basically have a table that they can insert rows into
via a stored procedure and this table is polled by a SQL Agent job that runs
once a minute and executes the specific commands they are allowed to run.
This way you can code so they can only do a very specific action (otherwise
you would lead yourself open a large security hole)
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
<barrygilbertusa_no_spam@.yahoo.com> wrote in message
news:1101316733.934708.165050@.f14g2000cwb.googlegroups.com...
> Hi,
> I need to grant a user access to one or two specific system stored
> procs without giving permission to everything else in the fixed server
> role that allows them. Specifically, they need rights to
> sp_addlinkedserver and sp_addlinkedsrvlogin, but they shouldn't have
> all the rights associated with securityadmin. Is there a way to grant
> specific rights to just these?
> TIA,
> Barry
>|||Jasper,
Thanks for your reply. I'll give this a try.
Barry
"Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message news:<#hwMr6n0EHA.1392@.TK2MSFTNG
P14.phx.gbl>...[vbcol=seagreen]
> Both of these system stored procedures have hard coded permission checks i
n
> them. I'm not a big fan of these as it really limits flexibility. The only
> way would be to alter them to remove these checks but this would not be a
> supported scenario. sp_addlinkedserver has a hard coded check for membersh
ip
> of the setupadmin server role and sp_addlinkedsrvlogin has a hard coded
> check for membership of the securityadmin server role. The only alternativ
e
> would be one that I use a lot for scenarios where I want lower privilege
> users to be able to do a very specific action and that is to write a queue
> system whereby they basically have a table that they can insert rows into
> via a stored procedure and this table is polled by a SQL Agent job that ru
ns
> once a minute and executes the specific commands they are allowed to run.
> This way you can code so they can only do a very specific action (otherwis
e
> you would lead yourself open a large security hole)
> --
> HTH
> Jasper Smith (SQL Server MVP)
> http://www.sqldbatips.com
> I support PASS - the definitive, global
> community for SQL Server professionals -
> http://www.sqlpass.org
> <barrygilbertusa_no_spam@.yahoo.com> wrote in message
> news:1101316733.934708.165050@.f14g2000cwb.googlegroups.com...
Monday, March 19, 2012
Custom Security + Object Moved to here
I have the Custom Security model in place, and it all seems to be working. I
authenticate the user upon login.
when i try the code below, i get the follwing error:
object Move to here. The hyperlink being
http://somedomina/ReportServer/logon.aspx?ReturnUrl=Reportservice2005.asmx
Does anyone now how to get around this?
Dim rs As New ReportingService2005
rs.Credentials = System.Net.CredentialCache.DefaultCredentials
Dim item As CatalogItem
For Each item In rs.ListChildren("/Reports", False)
If item.Type = ItemTypeEnum.Report Then
Me.ddReports.Items.Add(New ListItem(item.Name, item.Path))
End If
NextYou have to call LogonUser from your report service proxy.
that gets you the cookie to request such operations.
"ghoff12" <ghoff12@.discussions.microsoft.com> wrote in message
news:68E2E101-9CAD-443A-8670-9EFE677D20CB@.microsoft.com...
>I have the Custom Security model in place, and it all seems to be working.
>I
> authenticate the user upon login.
> when i try the code below, i get the follwing error:
> object Move to here. The hyperlink being
> http://somedomina/ReportServer/logon.aspx?ReturnUrl=Reportservice2005.asmx
> Does anyone now how to get around this?
> Dim rs As New ReportingService2005
> rs.Credentials = System.Net.CredentialCache.DefaultCredentials
> Dim item As CatalogItem
> For Each item In rs.ListChildren("/Reports", False)
> If item.Type = ItemTypeEnum.Report Then
> Me.ddReports.Items.Add(New ListItem(item.Name,
> item.Path))
> End If
> Next|||Thanks for replying back. When i called LogonUser, i received an error. I
read that LogonUser only works with SSL enabled. Is there a way to call
LogonUser without SSL? Can you give me some example code, to make sure i did
not miss anything?
"Chris Taylor" wrote:
> You have to call LogonUser from your report service proxy.
> that gets you the cookie to request such operations.
>
> "ghoff12" <ghoff12@.discussions.microsoft.com> wrote in message
> news:68E2E101-9CAD-443A-8670-9EFE677D20CB@.microsoft.com...
> >I have the Custom Security model in place, and it all seems to be working.
> >I
> > authenticate the user upon login.
> >
> > when i try the code below, i get the follwing error:
> > object Move to here. The hyperlink being
> >
> > http://somedomina/ReportServer/logon.aspx?ReturnUrl=Reportservice2005.asmx
> >
> > Does anyone now how to get around this?
> >
> > Dim rs As New ReportingService2005
> > rs.Credentials = System.Net.CredentialCache.DefaultCredentials
> > Dim item As CatalogItem
> > For Each item In rs.ListChildren("/Reports", False)
> > If item.Type = ItemTypeEnum.Report Then
> > Me.ddReports.Items.Add(New ListItem(item.Name,
> > item.Path))
> > End If
> > Next
>
>|||No LogonUser works without SSL, it's not a "Best Practice" or anything like
that, but it works just fine.
Can you tell me about the error you received calling it?
Thanks,
Chris
"ghoff12" <ghoff12@.discussions.microsoft.com> wrote in message
news:1478ACC7-DF13-48E8-9D24-9080A3255D75@.microsoft.com...
> Thanks for replying back. When i called LogonUser, i received an error.
> I
> read that LogonUser only works with SSL enabled. Is there a way to call
> LogonUser without SSL? Can you give me some example code, to make sure i
> did
> not miss anything?
> "Chris Taylor" wrote:
>> You have to call LogonUser from your report service proxy.
>> that gets you the cookie to request such operations.
>>
>> "ghoff12" <ghoff12@.discussions.microsoft.com> wrote in message
>> news:68E2E101-9CAD-443A-8670-9EFE677D20CB@.microsoft.com...
>> >I have the Custom Security model in place, and it all seems to be
>> >working.
>> >I
>> > authenticate the user upon login.
>> >
>> > when i try the code below, i get the follwing error:
>> > object Move to here. The hyperlink being
>> >
>> > http://somedomina/ReportServer/logon.aspx?ReturnUrl=Reportservice2005.asmx
>> >
>> > Does anyone now how to get around this?
>> >
>> > Dim rs As New ReportingService2005
>> > rs.Credentials = System.Net.CredentialCache.DefaultCredentials
>> > Dim item As CatalogItem
>> > For Each item In rs.ListChildren("/Reports", False)
>> > If item.Type = ItemTypeEnum.Report Then
>> > Me.ddReports.Items.Add(New ListItem(item.Name,
>> > item.Path))
>> > End If
>> > Next
>>|||It just seems like it never does authenticate. Can you give me some sample
code to go off of?
"Chris Taylor" wrote:
> No LogonUser works without SSL, it's not a "Best Practice" or anything like
> that, but it works just fine.
> Can you tell me about the error you received calling it?
> Thanks,
> Chris
> "ghoff12" <ghoff12@.discussions.microsoft.com> wrote in message
> news:1478ACC7-DF13-48E8-9D24-9080A3255D75@.microsoft.com...
> > Thanks for replying back. When i called LogonUser, i received an error.
> > I
> > read that LogonUser only works with SSL enabled. Is there a way to call
> > LogonUser without SSL? Can you give me some example code, to make sure i
> > did
> > not miss anything?
> >
> > "Chris Taylor" wrote:
> >
> >> You have to call LogonUser from your report service proxy.
> >>
> >> that gets you the cookie to request such operations.
> >>
> >>
> >> "ghoff12" <ghoff12@.discussions.microsoft.com> wrote in message
> >> news:68E2E101-9CAD-443A-8670-9EFE677D20CB@.microsoft.com...
> >> >I have the Custom Security model in place, and it all seems to be
> >> >working.
> >> >I
> >> > authenticate the user upon login.
> >> >
> >> > when i try the code below, i get the follwing error:
> >> > object Move to here. The hyperlink being
> >> >
> >> > http://somedomina/ReportServer/logon.aspx?ReturnUrl=Reportservice2005.asmx
> >> >
> >> > Does anyone now how to get around this?
> >> >
> >> > Dim rs As New ReportingService2005
> >> > rs.Credentials = System.Net.CredentialCache.DefaultCredentials
> >> > Dim item As CatalogItem
> >> > For Each item In rs.ListChildren("/Reports", False)
> >> > If item.Type = ItemTypeEnum.Report Then
> >> > Me.ddReports.Items.Add(New ListItem(item.Name,
> >> > item.Path))
> >> > End If
> >> > Next
> >>
> >>
> >>
>
>|||I still get the Object Moved to here message. Can anyone help me?
my code looks like:
Dim rs As New wsEcho.ReportingService2005
rs.Url ="http://domainname/reportserver/reportexecution2005.asmx?wsdl"
rs.Credentials = System.Net.CredentialCache.DefaultCredentials
rs.LogonUser("admin", "somepassword", Nothing)
Dim item As wsEcho.CatalogItem
For Each item In rs.ListChildren("/Reports", False)
If item.Type = ItemTypeEnum.Report Then
Me.ddReports.Items.Add(New ListItem(item.Name, item.Path))
End If
Next|||Let me ask you this about wsEcho.ReportingService2005. Is this a direct
reference to the web service itself?
If so, it doesn't quite work that way. In the samples that come with 2005
in the UILogon.aspx.cs file there is *another* class embedded in there
called ReportProxy. This is very very important as it overrides two
important methods
GetWebRequest,
GetWebResponse
which does all the cookie work for you in this case.
It looks like you are not doing so. Which is why you are having it redirect
you.
also, setting rs.Credentials really isn't important since you have anonymous
access probably turned on.
"ghoff12" <ghoff12@.discussions.microsoft.com> wrote in message
news:48EA1A8C-2F49-413C-BD4C-7CEAB367698C@.microsoft.com...
>I still get the Object Moved to here message. Can anyone help me?
> my code looks like:
> Dim rs As New wsEcho.ReportingService2005
> rs.Url => "http://domainname/reportserver/reportexecution2005.asmx?wsdl"
> rs.Credentials = System.Net.CredentialCache.DefaultCredentials
> rs.LogonUser("admin", "somepassword", Nothing)
> Dim item As wsEcho.CatalogItem
> For Each item In rs.ListChildren("/Reports", False)
> If item.Type = ItemTypeEnum.Report Then
> Me.ddReports.Items.Add(New ListItem(item.Name,
> item.Path))
> End If
> Next
>|||Perfect! Makes total sense now. I wasn't even thiking. I took your advice,
and now it works the way i need it. Much thanks for all your help!!!
"Chris Taylor" wrote:
> Let me ask you this about wsEcho.ReportingService2005. Is this a direct
> reference to the web service itself?
> If so, it doesn't quite work that way. In the samples that come with 2005
> in the UILogon.aspx.cs file there is *another* class embedded in there
> called ReportProxy. This is very very important as it overrides two
> important methods
> GetWebRequest,
> GetWebResponse
> which does all the cookie work for you in this case.
> It looks like you are not doing so. Which is why you are having it redirect
> you.
> also, setting rs.Credentials really isn't important since you have anonymous
> access probably turned on.
>
> "ghoff12" <ghoff12@.discussions.microsoft.com> wrote in message
> news:48EA1A8C-2F49-413C-BD4C-7CEAB367698C@.microsoft.com...
> >I still get the Object Moved to here message. Can anyone help me?
> >
> > my code looks like:
> >
> > Dim rs As New wsEcho.ReportingService2005
> > rs.Url => > "http://domainname/reportserver/reportexecution2005.asmx?wsdl"
> > rs.Credentials = System.Net.CredentialCache.DefaultCredentials
> >
> > rs.LogonUser("admin", "somepassword", Nothing)
> >
> > Dim item As wsEcho.CatalogItem
> > For Each item In rs.ListChildren("/Reports", False)
> > If item.Type = ItemTypeEnum.Report Then
> > Me.ddReports.Items.Add(New ListItem(item.Name,
> > item.Path))
> > End If
> > Next
> >
>
>|||I tried this but I am still getting the 401 error. In the 2005 Security
Extension Sample (Readme_FormsAuthenication.htm), there is the following:
**************** FROM Readme_FormsAuthenication.htm
Using the Web Service with Custom Security
You can use the Web service API with Forms Authentication just as you would
with Windows Authentication. However, you must call LogonUser in your Web
service code and pass the credentials of the current user. In addition, your
Web service client will not have the benefit of automatic cookie management,
which is provided by Internet Explorer or other Web browsers. You will have
to extend the Microsoft.ReportingServices proxy class to include cookie
management. This can be done by overriding the GetWebRequest and
GetWebResponse methods of the Web service class.
****************
I think what is holding us up now is this, because the code in
UILogon.aspx.cs that does the above, in the routines WebRequest and
WebResponse runs, but has not been modififed. Then the IIS 401 error occurs.
LogonUser apparently never gets called. I am not sure how to do this, so I
am looking for a pointer to the correct direction.
It appears some modification of these overrides is called for, but I am not
sure how to do this. Can you provide some direction?
nickpup
nickpup@.yahoo.comNONSPAM
Please let me know...
"ghoff12" wrote:
> Perfect! Makes total sense now. I wasn't even thiking. I took your advice,
> and now it works the way i need it. Much thanks for all your help!!!
> "Chris Taylor" wrote:
> > Let me ask you this about wsEcho.ReportingService2005. Is this a direct
> > reference to the web service itself?
> >
> > If so, it doesn't quite work that way. In the samples that come with 2005
> > in the UILogon.aspx.cs file there is *another* class embedded in there
> > called ReportProxy. This is very very important as it overrides two
> > important methods
> >
> > GetWebRequest,
> > GetWebResponse
> >
> > which does all the cookie work for you in this case.
> >
> > It looks like you are not doing so. Which is why you are having it redirect
> > you.
> >
> > also, setting rs.Credentials really isn't important since you have anonymous
> > access probably turned on.
> >
> >
> >
> > "ghoff12" <ghoff12@.discussions.microsoft.com> wrote in message
> > news:48EA1A8C-2F49-413C-BD4C-7CEAB367698C@.microsoft.com...
> > >I still get the Object Moved to here message. Can anyone help me?
> > >
> > > my code looks like:
> > >
> > > Dim rs As New wsEcho.ReportingService2005
> > > rs.Url => > > "http://domainname/reportserver/reportexecution2005.asmx?wsdl"
> > > rs.Credentials = System.Net.CredentialCache.DefaultCredentials
> > >
> > > rs.LogonUser("admin", "somepassword", Nothing)
> > >
> > > Dim item As wsEcho.CatalogItem
> > > For Each item In rs.ListChildren("/Reports", False)
> > > If item.Type = ItemTypeEnum.Report Then
> > > Me.ddReports.Items.Add(New ListItem(item.Name,
> > > item.Path))
> > > End If
> > > Next
> > >
> >
> >
> >
authenticate the user upon login.
when i try the code below, i get the follwing error:
object Move to here. The hyperlink being
http://somedomina/ReportServer/logon.aspx?ReturnUrl=Reportservice2005.asmx
Does anyone now how to get around this?
Dim rs As New ReportingService2005
rs.Credentials = System.Net.CredentialCache.DefaultCredentials
Dim item As CatalogItem
For Each item In rs.ListChildren("/Reports", False)
If item.Type = ItemTypeEnum.Report Then
Me.ddReports.Items.Add(New ListItem(item.Name, item.Path))
End If
NextYou have to call LogonUser from your report service proxy.
that gets you the cookie to request such operations.
"ghoff12" <ghoff12@.discussions.microsoft.com> wrote in message
news:68E2E101-9CAD-443A-8670-9EFE677D20CB@.microsoft.com...
>I have the Custom Security model in place, and it all seems to be working.
>I
> authenticate the user upon login.
> when i try the code below, i get the follwing error:
> object Move to here. The hyperlink being
> http://somedomina/ReportServer/logon.aspx?ReturnUrl=Reportservice2005.asmx
> Does anyone now how to get around this?
> Dim rs As New ReportingService2005
> rs.Credentials = System.Net.CredentialCache.DefaultCredentials
> Dim item As CatalogItem
> For Each item In rs.ListChildren("/Reports", False)
> If item.Type = ItemTypeEnum.Report Then
> Me.ddReports.Items.Add(New ListItem(item.Name,
> item.Path))
> End If
> Next|||Thanks for replying back. When i called LogonUser, i received an error. I
read that LogonUser only works with SSL enabled. Is there a way to call
LogonUser without SSL? Can you give me some example code, to make sure i did
not miss anything?
"Chris Taylor" wrote:
> You have to call LogonUser from your report service proxy.
> that gets you the cookie to request such operations.
>
> "ghoff12" <ghoff12@.discussions.microsoft.com> wrote in message
> news:68E2E101-9CAD-443A-8670-9EFE677D20CB@.microsoft.com...
> >I have the Custom Security model in place, and it all seems to be working.
> >I
> > authenticate the user upon login.
> >
> > when i try the code below, i get the follwing error:
> > object Move to here. The hyperlink being
> >
> > http://somedomina/ReportServer/logon.aspx?ReturnUrl=Reportservice2005.asmx
> >
> > Does anyone now how to get around this?
> >
> > Dim rs As New ReportingService2005
> > rs.Credentials = System.Net.CredentialCache.DefaultCredentials
> > Dim item As CatalogItem
> > For Each item In rs.ListChildren("/Reports", False)
> > If item.Type = ItemTypeEnum.Report Then
> > Me.ddReports.Items.Add(New ListItem(item.Name,
> > item.Path))
> > End If
> > Next
>
>|||No LogonUser works without SSL, it's not a "Best Practice" or anything like
that, but it works just fine.
Can you tell me about the error you received calling it?
Thanks,
Chris
"ghoff12" <ghoff12@.discussions.microsoft.com> wrote in message
news:1478ACC7-DF13-48E8-9D24-9080A3255D75@.microsoft.com...
> Thanks for replying back. When i called LogonUser, i received an error.
> I
> read that LogonUser only works with SSL enabled. Is there a way to call
> LogonUser without SSL? Can you give me some example code, to make sure i
> did
> not miss anything?
> "Chris Taylor" wrote:
>> You have to call LogonUser from your report service proxy.
>> that gets you the cookie to request such operations.
>>
>> "ghoff12" <ghoff12@.discussions.microsoft.com> wrote in message
>> news:68E2E101-9CAD-443A-8670-9EFE677D20CB@.microsoft.com...
>> >I have the Custom Security model in place, and it all seems to be
>> >working.
>> >I
>> > authenticate the user upon login.
>> >
>> > when i try the code below, i get the follwing error:
>> > object Move to here. The hyperlink being
>> >
>> > http://somedomina/ReportServer/logon.aspx?ReturnUrl=Reportservice2005.asmx
>> >
>> > Does anyone now how to get around this?
>> >
>> > Dim rs As New ReportingService2005
>> > rs.Credentials = System.Net.CredentialCache.DefaultCredentials
>> > Dim item As CatalogItem
>> > For Each item In rs.ListChildren("/Reports", False)
>> > If item.Type = ItemTypeEnum.Report Then
>> > Me.ddReports.Items.Add(New ListItem(item.Name,
>> > item.Path))
>> > End If
>> > Next
>>|||It just seems like it never does authenticate. Can you give me some sample
code to go off of?
"Chris Taylor" wrote:
> No LogonUser works without SSL, it's not a "Best Practice" or anything like
> that, but it works just fine.
> Can you tell me about the error you received calling it?
> Thanks,
> Chris
> "ghoff12" <ghoff12@.discussions.microsoft.com> wrote in message
> news:1478ACC7-DF13-48E8-9D24-9080A3255D75@.microsoft.com...
> > Thanks for replying back. When i called LogonUser, i received an error.
> > I
> > read that LogonUser only works with SSL enabled. Is there a way to call
> > LogonUser without SSL? Can you give me some example code, to make sure i
> > did
> > not miss anything?
> >
> > "Chris Taylor" wrote:
> >
> >> You have to call LogonUser from your report service proxy.
> >>
> >> that gets you the cookie to request such operations.
> >>
> >>
> >> "ghoff12" <ghoff12@.discussions.microsoft.com> wrote in message
> >> news:68E2E101-9CAD-443A-8670-9EFE677D20CB@.microsoft.com...
> >> >I have the Custom Security model in place, and it all seems to be
> >> >working.
> >> >I
> >> > authenticate the user upon login.
> >> >
> >> > when i try the code below, i get the follwing error:
> >> > object Move to here. The hyperlink being
> >> >
> >> > http://somedomina/ReportServer/logon.aspx?ReturnUrl=Reportservice2005.asmx
> >> >
> >> > Does anyone now how to get around this?
> >> >
> >> > Dim rs As New ReportingService2005
> >> > rs.Credentials = System.Net.CredentialCache.DefaultCredentials
> >> > Dim item As CatalogItem
> >> > For Each item In rs.ListChildren("/Reports", False)
> >> > If item.Type = ItemTypeEnum.Report Then
> >> > Me.ddReports.Items.Add(New ListItem(item.Name,
> >> > item.Path))
> >> > End If
> >> > Next
> >>
> >>
> >>
>
>|||I still get the Object Moved to here message. Can anyone help me?
my code looks like:
Dim rs As New wsEcho.ReportingService2005
rs.Url ="http://domainname/reportserver/reportexecution2005.asmx?wsdl"
rs.Credentials = System.Net.CredentialCache.DefaultCredentials
rs.LogonUser("admin", "somepassword", Nothing)
Dim item As wsEcho.CatalogItem
For Each item In rs.ListChildren("/Reports", False)
If item.Type = ItemTypeEnum.Report Then
Me.ddReports.Items.Add(New ListItem(item.Name, item.Path))
End If
Next|||Let me ask you this about wsEcho.ReportingService2005. Is this a direct
reference to the web service itself?
If so, it doesn't quite work that way. In the samples that come with 2005
in the UILogon.aspx.cs file there is *another* class embedded in there
called ReportProxy. This is very very important as it overrides two
important methods
GetWebRequest,
GetWebResponse
which does all the cookie work for you in this case.
It looks like you are not doing so. Which is why you are having it redirect
you.
also, setting rs.Credentials really isn't important since you have anonymous
access probably turned on.
"ghoff12" <ghoff12@.discussions.microsoft.com> wrote in message
news:48EA1A8C-2F49-413C-BD4C-7CEAB367698C@.microsoft.com...
>I still get the Object Moved to here message. Can anyone help me?
> my code looks like:
> Dim rs As New wsEcho.ReportingService2005
> rs.Url => "http://domainname/reportserver/reportexecution2005.asmx?wsdl"
> rs.Credentials = System.Net.CredentialCache.DefaultCredentials
> rs.LogonUser("admin", "somepassword", Nothing)
> Dim item As wsEcho.CatalogItem
> For Each item In rs.ListChildren("/Reports", False)
> If item.Type = ItemTypeEnum.Report Then
> Me.ddReports.Items.Add(New ListItem(item.Name,
> item.Path))
> End If
> Next
>|||Perfect! Makes total sense now. I wasn't even thiking. I took your advice,
and now it works the way i need it. Much thanks for all your help!!!
"Chris Taylor" wrote:
> Let me ask you this about wsEcho.ReportingService2005. Is this a direct
> reference to the web service itself?
> If so, it doesn't quite work that way. In the samples that come with 2005
> in the UILogon.aspx.cs file there is *another* class embedded in there
> called ReportProxy. This is very very important as it overrides two
> important methods
> GetWebRequest,
> GetWebResponse
> which does all the cookie work for you in this case.
> It looks like you are not doing so. Which is why you are having it redirect
> you.
> also, setting rs.Credentials really isn't important since you have anonymous
> access probably turned on.
>
> "ghoff12" <ghoff12@.discussions.microsoft.com> wrote in message
> news:48EA1A8C-2F49-413C-BD4C-7CEAB367698C@.microsoft.com...
> >I still get the Object Moved to here message. Can anyone help me?
> >
> > my code looks like:
> >
> > Dim rs As New wsEcho.ReportingService2005
> > rs.Url => > "http://domainname/reportserver/reportexecution2005.asmx?wsdl"
> > rs.Credentials = System.Net.CredentialCache.DefaultCredentials
> >
> > rs.LogonUser("admin", "somepassword", Nothing)
> >
> > Dim item As wsEcho.CatalogItem
> > For Each item In rs.ListChildren("/Reports", False)
> > If item.Type = ItemTypeEnum.Report Then
> > Me.ddReports.Items.Add(New ListItem(item.Name,
> > item.Path))
> > End If
> > Next
> >
>
>|||I tried this but I am still getting the 401 error. In the 2005 Security
Extension Sample (Readme_FormsAuthenication.htm), there is the following:
**************** FROM Readme_FormsAuthenication.htm
Using the Web Service with Custom Security
You can use the Web service API with Forms Authentication just as you would
with Windows Authentication. However, you must call LogonUser in your Web
service code and pass the credentials of the current user. In addition, your
Web service client will not have the benefit of automatic cookie management,
which is provided by Internet Explorer or other Web browsers. You will have
to extend the Microsoft.ReportingServices proxy class to include cookie
management. This can be done by overriding the GetWebRequest and
GetWebResponse methods of the Web service class.
****************
I think what is holding us up now is this, because the code in
UILogon.aspx.cs that does the above, in the routines WebRequest and
WebResponse runs, but has not been modififed. Then the IIS 401 error occurs.
LogonUser apparently never gets called. I am not sure how to do this, so I
am looking for a pointer to the correct direction.
It appears some modification of these overrides is called for, but I am not
sure how to do this. Can you provide some direction?
nickpup
nickpup@.yahoo.comNONSPAM
Please let me know...
"ghoff12" wrote:
> Perfect! Makes total sense now. I wasn't even thiking. I took your advice,
> and now it works the way i need it. Much thanks for all your help!!!
> "Chris Taylor" wrote:
> > Let me ask you this about wsEcho.ReportingService2005. Is this a direct
> > reference to the web service itself?
> >
> > If so, it doesn't quite work that way. In the samples that come with 2005
> > in the UILogon.aspx.cs file there is *another* class embedded in there
> > called ReportProxy. This is very very important as it overrides two
> > important methods
> >
> > GetWebRequest,
> > GetWebResponse
> >
> > which does all the cookie work for you in this case.
> >
> > It looks like you are not doing so. Which is why you are having it redirect
> > you.
> >
> > also, setting rs.Credentials really isn't important since you have anonymous
> > access probably turned on.
> >
> >
> >
> > "ghoff12" <ghoff12@.discussions.microsoft.com> wrote in message
> > news:48EA1A8C-2F49-413C-BD4C-7CEAB367698C@.microsoft.com...
> > >I still get the Object Moved to here message. Can anyone help me?
> > >
> > > my code looks like:
> > >
> > > Dim rs As New wsEcho.ReportingService2005
> > > rs.Url => > > "http://domainname/reportserver/reportexecution2005.asmx?wsdl"
> > > rs.Credentials = System.Net.CredentialCache.DefaultCredentials
> > >
> > > rs.LogonUser("admin", "somepassword", Nothing)
> > >
> > > Dim item As wsEcho.CatalogItem
> > > For Each item In rs.ListChildren("/Reports", False)
> > > If item.Type = ItemTypeEnum.Report Then
> > > Me.ddReports.Items.Add(New ListItem(item.Name,
> > > item.Path))
> > > End If
> > > Next
> > >
> >
> >
> >
Custom Reports using Reporting Services
I have a couple of quetsions regarding the Reporting Services. Any response
will be greatly appreciated.
I am looking to see if the user has the capability to design thier own
reports using the reporting services..
Is it possible to provide the report design capability to the user so that
they can build custom reports using SQL Server Reporting Services ?
If yes, how can that be achived? If no, is there any third party tool that
can provide this capability to the user.
Thanks in advance,
--
snbatHi snbat:
Currently an end user needs Visual Studio installed to design reports.
Even the most basic (cheapest) version of the 2003 IDE can be used to
design reports.
There are also a slew of 3rd parties providing solutions, including
report designers (look at Cizer and SoftArtisans):
http://www.microsoft.com/sql/reporting/partners/softwareapps.asp
--
Scott
http://www.OdeToCode.com/
On Wed, 6 Oct 2004 08:05:03 -0700, "snbat"
<snbat@.discussions.microsoft.com> wrote:
>I have a couple of quetsions regarding the Reporting Services. Any response
>will be greatly appreciated.
>I am looking to see if the user has the capability to design thier own
>reports using the reporting services..
>Is it possible to provide the report design capability to the user so that
>they can build custom reports using SQL Server Reporting Services ?
>If yes, how can that be achived? If no, is there any third party tool that
>can provide this capability to the user.
>Thanks in advance,|||The next version of RS will include ad-hoc reporting functionality.
Currently, you can create your own ad-hoc report generator (it is not that
difficult) or use third-party tools as Scott mentioned.
--
Hope this helps.
----
Teo Lachev, MVP [SQL Server], MCSD, MCT
Author: "Microsoft Reporting Services in Action"
Publisher website: http://www.manning.com/lachev
Buy it from Amazon.com: http://shrinkster.com/eq
Home page and blog: http://www.prologika.com/
----
"snbat" <snbat@.discussions.microsoft.com> wrote in message
news:6C9CAFF3-E415-4E7A-8B25-246C5F64B762@.microsoft.com...
> I have a couple of quetsions regarding the Reporting Services. Any
response
> will be greatly appreciated.
> I am looking to see if the user has the capability to design thier own
> reports using the reporting services..
> Is it possible to provide the report design capability to the user so that
> they can build custom reports using SQL Server Reporting Services ?
> If yes, how can that be achived? If no, is there any third party tool that
> can provide this capability to the user.
> Thanks in advance,
>
> --
> snbat
will be greatly appreciated.
I am looking to see if the user has the capability to design thier own
reports using the reporting services..
Is it possible to provide the report design capability to the user so that
they can build custom reports using SQL Server Reporting Services ?
If yes, how can that be achived? If no, is there any third party tool that
can provide this capability to the user.
Thanks in advance,
--
snbatHi snbat:
Currently an end user needs Visual Studio installed to design reports.
Even the most basic (cheapest) version of the 2003 IDE can be used to
design reports.
There are also a slew of 3rd parties providing solutions, including
report designers (look at Cizer and SoftArtisans):
http://www.microsoft.com/sql/reporting/partners/softwareapps.asp
--
Scott
http://www.OdeToCode.com/
On Wed, 6 Oct 2004 08:05:03 -0700, "snbat"
<snbat@.discussions.microsoft.com> wrote:
>I have a couple of quetsions regarding the Reporting Services. Any response
>will be greatly appreciated.
>I am looking to see if the user has the capability to design thier own
>reports using the reporting services..
>Is it possible to provide the report design capability to the user so that
>they can build custom reports using SQL Server Reporting Services ?
>If yes, how can that be achived? If no, is there any third party tool that
>can provide this capability to the user.
>Thanks in advance,|||The next version of RS will include ad-hoc reporting functionality.
Currently, you can create your own ad-hoc report generator (it is not that
difficult) or use third-party tools as Scott mentioned.
--
Hope this helps.
----
Teo Lachev, MVP [SQL Server], MCSD, MCT
Author: "Microsoft Reporting Services in Action"
Publisher website: http://www.manning.com/lachev
Buy it from Amazon.com: http://shrinkster.com/eq
Home page and blog: http://www.prologika.com/
----
"snbat" <snbat@.discussions.microsoft.com> wrote in message
news:6C9CAFF3-E415-4E7A-8B25-246C5F64B762@.microsoft.com...
> I have a couple of quetsions regarding the Reporting Services. Any
response
> will be greatly appreciated.
> I am looking to see if the user has the capability to design thier own
> reports using the reporting services..
> Is it possible to provide the report design capability to the user so that
> they can build custom reports using SQL Server Reporting Services ?
> If yes, how can that be achived? If no, is there any third party tool that
> can provide this capability to the user.
> Thanks in advance,
>
> --
> snbat
Sunday, March 11, 2012
Custom protection of rows (problem with updatability of viwes with "WITH VIEW_METADATA" an
My question is connected with tip "Using Views to Control User Access to
Data":
http://www.microsoft.com/sql/techinfo/tips/administration/controlledaccess.asp
I need to update (using Access 2000/2002) table defended in such way,
but it cause some additional problems.
1. I can't update this view when user haven't SELECT permissions
on the base table (user have only permissions to the view, because I try to
defend base table).
As I understand ADO tries directly update base table and fails.
2. When I add "WITH VIEW_METADATA" to enforce ADO to update
view, not base tables I have another problem - I need to add
prymary key columns from "authtable" to make view updateble
for ADO. In such way I also had to add some additional procedures
to dataform in Access to fill this additional columns automatically (for
insert operation).
It helps but it cause additional procedures at client side.
3. The best way to resolve this problem only on server side as I thought
was to create view like this (using "WITH VIEW_METADATA" and "IN" clauses):
CREATE VIEW v_data
WITH VIEW_METADATA
AS
SELECT <column_list>
FROM dbo.mytable AS a
WHERE a.Pkey
IN
(
SELECT b.DataKey
FROM dbo.authtable AS b
WHERE b.userid = suser_sname()
)
But this view is not updatable in Access 2000/2002
because ADO DOES NOT SEE PRIMARY KEY INFORMATION
when there is combination "WITH VIEW_METADATA" and "IN" clauses.
I cann't use this view without "WITH VIEW_METADATA", in this case
view is "updatable" for ADO, but updates fails as described in p.1.
I send this bug year ago to MS when was mdac 2.7.
Now we have MDAC 2.8, SQL SP 3, Yukon Beta - ADO has the same problem with
updates
when there is combination "WITH VIEW_METADATA" and "IN" clauses
I think, it helps to make simple customised rowbased security system not
only for SELECT
but also for INSERT, UPDATE and DELETE in combination "SQL Server - MS
Access" or other ADO based clients.Hello Max:
You wrote on Wed, 28 Apr 2004 19:37:02 +0300:
ii> 1. I can't update this view when user haven't SELECT permissions
ii> on the base table (user have only permissions to the view, because I
ii> try to defend base table).
ii> As I understand ADO tries directly update base table and fails.
In Access 2002, open the view in design mode, open properties, select
"Update using view rules".
ÿê òàì Ëüâiâ? ÿ òàì â÷èâñÿ.
Vadim
---
Vadim Rapp Consulting
SQL, Access, VB Solutions
847-685-9073
www.vadimrapp.com|||"Vadim Rapp" <vrapp@.nospam.polyscience.com> ñîîáùèë/ñîîáùèëà â íîâîñòÿõ
ñëåäóþùåå: news:e1IXvQULEHA.2456@.TK2MSFTNGP12.phx.gbl...
> Hello Max:
> You wrote on Wed, 28 Apr 2004 19:37:02 +0300:
> ii> 1. I can't update this view when user haven't SELECT permissions
> ii> on the base table (user have only permissions to the view, because I
> ii> try to defend base table).
> ii> As I understand ADO tries directly update base table and fails.
> In Access 2002, open the view in design mode, open properties, select
> "Update using view rules".
> ÿê òàì Ëüâiâ? ÿ òàì â÷èâñÿ.
> Vadim
> ---
> Vadim Rapp Consulting
> SQL, Access, VB Solutions
> 847-685-9073
> www.vadimrapp.com
>
Hello Vadim,
Thanks for reply,
Chekbox "Update using view rules" exactly adds "WITH VIEW_METADATA" clause
to view definition and this situation adds aditional problems as described
in p.2. But when I want add more sophisticated rules for user selection
permissions and make it updatable in Access I get very processor time
cosuming solutions. Execution plans for view scheme discribed in p.3 for my
tasks and needed rules some times more than 10 time quick than the same that
Access (ADO) understands as updatable - this the main problem...
And root of problem is ADO uncorrect understanding of combination of clauses
"WITH VIEW_METADATA" and "IN" (also "EXISTS").
Ó Ëüâîâ³ êëàñíî, ïðàâäà ùå òðîõè çèìíî áóâàº, ³ ÷àñ â³ä ÷àñó äîùèòü. Â
íåä³ëþ 2 òðàâíÿ áóäåìî ñâÿòêóâàòè äåíü ì³ñòà.
MAX|||Hello Max:
You wrote in conference microsoft.public.sqlserver.server on Thu, 29 Apr
2004 14:05:18 +0300:
II> And root of problem is ADO uncorrect understanding of combination of
II> clauses "WITH VIEW_METADATA" and "IN" (also "EXISTS").
It looks like it's not ADO but Access. I see that Access indeed does not
allow to add records to such a view; however, I successfully executed the
following pure ado code in VB:
rs.CursorLocation = adUseClient
rs.Open "view1", conn, adOpenKeyset, adLockBatchOptimistic
rs.AddNew
rs!c1 = "a"
rs!c2 = "b"
rs!id = 14
rs.UpdateBatch
rs.Close
where view1 was
ALTER VIEW dbo.View1
WITH VIEW_METADATA
AS
SELECT dbo.t1.c1, dbo.t1.c2, dbo.t1.id, dbo.t1.auth
FROM dbo.t1 INNER JOIN
dbo.authtable ON dbo.t1.auth >= dbo.authtable.minvalue
WHERE (dbo.authtable.userid = USER_NAME())
Note that I did not include in the view anything from authtable.
Another interesting possibility is described in BOL in "Create View"
article, which says: "INSTEAD OF triggers can be created on a view in order
to make a view updatable".
As a side note: you might receive more advise if you asked the question in
more relevant newsgroups. This one is mainly read by server administrators;
you might try .access.adp.sqlserver and .data.ado (though, as I said, it
looks like ADO is innocent here).
Êðiì òîãî, êîëè òè ïèøåø â àíãëiéñüêó ãðóïïó, òî ïèøè ñâîº iì'ÿ
ïî-àíãë³éñüêè.
regards,
Vadim
---
Vadim Rapp Consulting
SQL, Access, VB Solutions
847-685-9073
www.vadimrapp.com
Data":
http://www.microsoft.com/sql/techinfo/tips/administration/controlledaccess.asp
I need to update (using Access 2000/2002) table defended in such way,
but it cause some additional problems.
1. I can't update this view when user haven't SELECT permissions
on the base table (user have only permissions to the view, because I try to
defend base table).
As I understand ADO tries directly update base table and fails.
2. When I add "WITH VIEW_METADATA" to enforce ADO to update
view, not base tables I have another problem - I need to add
prymary key columns from "authtable" to make view updateble
for ADO. In such way I also had to add some additional procedures
to dataform in Access to fill this additional columns automatically (for
insert operation).
It helps but it cause additional procedures at client side.
3. The best way to resolve this problem only on server side as I thought
was to create view like this (using "WITH VIEW_METADATA" and "IN" clauses):
CREATE VIEW v_data
WITH VIEW_METADATA
AS
SELECT <column_list>
FROM dbo.mytable AS a
WHERE a.Pkey
IN
(
SELECT b.DataKey
FROM dbo.authtable AS b
WHERE b.userid = suser_sname()
)
But this view is not updatable in Access 2000/2002
because ADO DOES NOT SEE PRIMARY KEY INFORMATION
when there is combination "WITH VIEW_METADATA" and "IN" clauses.
I cann't use this view without "WITH VIEW_METADATA", in this case
view is "updatable" for ADO, but updates fails as described in p.1.
I send this bug year ago to MS when was mdac 2.7.
Now we have MDAC 2.8, SQL SP 3, Yukon Beta - ADO has the same problem with
updates
when there is combination "WITH VIEW_METADATA" and "IN" clauses
I think, it helps to make simple customised rowbased security system not
only for SELECT
but also for INSERT, UPDATE and DELETE in combination "SQL Server - MS
Access" or other ADO based clients.Hello Max:
You wrote on Wed, 28 Apr 2004 19:37:02 +0300:
ii> 1. I can't update this view when user haven't SELECT permissions
ii> on the base table (user have only permissions to the view, because I
ii> try to defend base table).
ii> As I understand ADO tries directly update base table and fails.
In Access 2002, open the view in design mode, open properties, select
"Update using view rules".
ÿê òàì Ëüâiâ? ÿ òàì â÷èâñÿ.
Vadim
---
Vadim Rapp Consulting
SQL, Access, VB Solutions
847-685-9073
www.vadimrapp.com|||"Vadim Rapp" <vrapp@.nospam.polyscience.com> ñîîáùèë/ñîîáùèëà â íîâîñòÿõ
ñëåäóþùåå: news:e1IXvQULEHA.2456@.TK2MSFTNGP12.phx.gbl...
> Hello Max:
> You wrote on Wed, 28 Apr 2004 19:37:02 +0300:
> ii> 1. I can't update this view when user haven't SELECT permissions
> ii> on the base table (user have only permissions to the view, because I
> ii> try to defend base table).
> ii> As I understand ADO tries directly update base table and fails.
> In Access 2002, open the view in design mode, open properties, select
> "Update using view rules".
> ÿê òàì Ëüâiâ? ÿ òàì â÷èâñÿ.
> Vadim
> ---
> Vadim Rapp Consulting
> SQL, Access, VB Solutions
> 847-685-9073
> www.vadimrapp.com
>
Hello Vadim,
Thanks for reply,
Chekbox "Update using view rules" exactly adds "WITH VIEW_METADATA" clause
to view definition and this situation adds aditional problems as described
in p.2. But when I want add more sophisticated rules for user selection
permissions and make it updatable in Access I get very processor time
cosuming solutions. Execution plans for view scheme discribed in p.3 for my
tasks and needed rules some times more than 10 time quick than the same that
Access (ADO) understands as updatable - this the main problem...
And root of problem is ADO uncorrect understanding of combination of clauses
"WITH VIEW_METADATA" and "IN" (also "EXISTS").
Ó Ëüâîâ³ êëàñíî, ïðàâäà ùå òðîõè çèìíî áóâàº, ³ ÷àñ â³ä ÷àñó äîùèòü. Â
íåä³ëþ 2 òðàâíÿ áóäåìî ñâÿòêóâàòè äåíü ì³ñòà.
MAX|||Hello Max:
You wrote in conference microsoft.public.sqlserver.server on Thu, 29 Apr
2004 14:05:18 +0300:
II> And root of problem is ADO uncorrect understanding of combination of
II> clauses "WITH VIEW_METADATA" and "IN" (also "EXISTS").
It looks like it's not ADO but Access. I see that Access indeed does not
allow to add records to such a view; however, I successfully executed the
following pure ado code in VB:
rs.CursorLocation = adUseClient
rs.Open "view1", conn, adOpenKeyset, adLockBatchOptimistic
rs.AddNew
rs!c1 = "a"
rs!c2 = "b"
rs!id = 14
rs.UpdateBatch
rs.Close
where view1 was
ALTER VIEW dbo.View1
WITH VIEW_METADATA
AS
SELECT dbo.t1.c1, dbo.t1.c2, dbo.t1.id, dbo.t1.auth
FROM dbo.t1 INNER JOIN
dbo.authtable ON dbo.t1.auth >= dbo.authtable.minvalue
WHERE (dbo.authtable.userid = USER_NAME())
Note that I did not include in the view anything from authtable.
Another interesting possibility is described in BOL in "Create View"
article, which says: "INSTEAD OF triggers can be created on a view in order
to make a view updatable".
As a side note: you might receive more advise if you asked the question in
more relevant newsgroups. This one is mainly read by server administrators;
you might try .access.adp.sqlserver and .data.ado (though, as I said, it
looks like ADO is innocent here).
Êðiì òîãî, êîëè òè ïèøåø â àíãëiéñüêó ãðóïïó, òî ïèøè ñâîº iì'ÿ
ïî-àíãë³éñüêè.
regards,
Vadim
---
Vadim Rapp Consulting
SQL, Access, VB Solutions
847-685-9073
www.vadimrapp.com
Subscribe to:
Posts (Atom)