Calling Webservice: contain

Showing posts with label contain. Show all posts

Tuesday, March 27, 2012

Can I have an SQL view like in Access

If you open the Northwind.mdb in access and look at tables Orders &
Employees you'll see that both contain "Employee ID", but when you view the
Orders table you don't see the Employee ID you see the associated Employee's
name. Can this be done in SQL 2005? When I view the Orders table I see a
whole list of Employee ID which makes it hard to know which employee is
associated to the Employee ID. I have a real world application but I'm only
use Northwind as an example as everyone has this database.I don't use SQL 2005 but I am sure you can by creating a View combining
tables Orders and Employees with the EmployeeID as the linking Field.
--
HTH
Van T. Dinh
MVP (Access)
"M.Siler" <John.Doe@.NoSpam.com> wrote in message
news:eDQUjhWbGHA.3364@.TK2MSFTNGP05.phx.gbl...
> If you open the Northwind.mdb in access and look at tables Orders &
> Employees you'll see that both contain "Employee ID", but when you view
> the Orders table you don't see the Employee ID you see the associated
> Employee's name. Can this be done in SQL 2005? When I view the Orders
> table I see a whole list of Employee ID which makes it hard to know which
> employee is associated to the Employee ID. I have a real world application
> but I'm only use Northwind as an example as everyone has this database.
>|||Hi.
> Can this be done in SQL 2005?
Not on the table itself. And you don't want to, either. It causes huge
problems. For more information, please see the article, "The Evils of
Lookup Fields in Tables," on the following Web page:
http://www.mvps.org/access/lookupfields.htm
> When I view the Orders table I see a whole list of Employee ID which makes
> it hard to know which employee is associated to the Employee ID.
You shouldn't be viewing the raw data in the tables. That's where the data
is stored, but to glean meaningful information from the data, you need to
use queries, forms or reports. In a query, you can join the two tables and
select the employee's first name and last name to display for each record,
not the EmployeeID. Create a similar view in SQL Server.
HTH.
Gunny
See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
info.
"M.Siler" <John.Doe@.NoSpam.com> wrote in message
news:eDQUjhWbGHA.3364@.TK2MSFTNGP05.phx.gbl...
> If you open the Northwind.mdb in access and look at tables Orders &
> Employees you'll see that both contain "Employee ID", but when you view
> the Orders table you don't see the Employee ID you see the associated
> Employee's name. Can this be done in SQL 2005? When I view the Orders
> table I see a whole list of Employee ID which makes it hard to know which
> employee is associated to the Employee ID. I have a real world application
> but I'm only use Northwind as an example as everyone has this database.
>|||I can see the "evils" of a lookup field in a table. I've never created a
view in SQL 2005 before... do you have a site that I could reference that
would give me a 101 on creating SQL 2005 views?
"'69 Camaro" <ForwardZERO_SPAM.To.69Camaro@.Spameater.orgZERO_SPAM> wrote in
message news:uC3ybuWbGHA.3956@.TK2MSFTNGP04.phx.gbl...
> Hi.
>> Can this be done in SQL 2005?
> Not on the table itself. And you don't want to, either. It causes huge
> problems. For more information, please see the article, "The Evils of
> Lookup Fields in Tables," on the following Web page:
> http://www.mvps.org/access/lookupfields.htm
>> When I view the Orders table I see a whole list of Employee ID which
>> makes it hard to know which employee is associated to the Employee ID.
> You shouldn't be viewing the raw data in the tables. That's where the
> data is stored, but to glean meaningful information from the data, you
> need to use queries, forms or reports. In a query, you can join the two
> tables and select the employee's first name and last name to display for
> each record, not the EmployeeID. Create a similar view in SQL Server.
> HTH.
> Gunny
> See http://www.QBuilt.com for all your database needs.
> See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
> http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
> info.
>
> "M.Siler" <John.Doe@.NoSpam.com> wrote in message
> news:eDQUjhWbGHA.3364@.TK2MSFTNGP05.phx.gbl...
>> If you open the Northwind.mdb in access and look at tables Orders &
>> Employees you'll see that both contain "Employee ID", but when you view
>> the Orders table you don't see the Employee ID you see the associated
>> Employee's name. Can this be done in SQL 2005? When I view the Orders
>> table I see a whole list of Employee ID which makes it hard to know which
>> employee is associated to the Employee ID. I have a real world
>> application but I'm only use Northwind as an example as everyone has this
>> database.
>|||A view is very simple to create. The basic syntax is
CREATE VIEW <viewname>
AS <select statement>
See these Books Online topics for the 101 information you need.
http://msdn2.microsoft.com/en-us/library/ms190174(SQL.90).aspx
http://msdn2.microsoft.com/en-us/library/ms188250(SQL.90).aspx
http://msdn2.microsoft.com/en-us/library/ms187956(SQL.90).aspx
--
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
"M.Siler" <John.Doe@.NoSpam.com> wrote in message
news:%23tWe5tfbGHA.5104@.TK2MSFTNGP03.phx.gbl...
>I can see the "evils" of a lookup field in a table. I've never created a
>view in SQL 2005 before... do you have a site that I could reference that
>would give me a 101 on creating SQL 2005 views?
> "'69 Camaro" <ForwardZERO_SPAM.To.69Camaro@.Spameater.orgZERO_SPAM> wrote
> in message news:uC3ybuWbGHA.3956@.TK2MSFTNGP04.phx.gbl...
>> Hi.
>> Can this be done in SQL 2005?
>> Not on the table itself. And you don't want to, either. It causes huge
>> problems. For more information, please see the article, "The Evils of
>> Lookup Fields in Tables," on the following Web page:
>> http://www.mvps.org/access/lookupfields.htm
>> When I view the Orders table I see a whole list of Employee ID which
>> makes it hard to know which employee is associated to the Employee ID.
>> You shouldn't be viewing the raw data in the tables. That's where the
>> data is stored, but to glean meaningful information from the data, you
>> need to use queries, forms or reports. In a query, you can join the two
>> tables and select the employee's first name and last name to display for
>> each record, not the EmployeeID. Create a similar view in SQL Server.
>> HTH.
>> Gunny
>> See http://www.QBuilt.com for all your database needs.
>> See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
>> http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
>> info.
>>
>> "M.Siler" <John.Doe@.NoSpam.com> wrote in message
>> news:eDQUjhWbGHA.3364@.TK2MSFTNGP05.phx.gbl...
>> If you open the Northwind.mdb in access and look at tables Orders &
>> Employees you'll see that both contain "Employee ID", but when you view
>> the Orders table you don't see the Employee ID you see the associated
>> Employee's name. Can this be done in SQL 2005? When I view the Orders
>> table I see a whole list of Employee ID which makes it hard to know
>> which employee is associated to the Employee ID. I have a real world
>> application but I'm only use Northwind as an example as everyone has
>> this database.
>>
>|||One may also download the latest edition of SQL Server 2005 Books Online
(BOL), which also replaces previous editions of the SQL Server 2005 Express
BOL. Please see the following Web page for the download:
http://www.microsoft.com/downloads/details.aspx?FamilyId=BE6A2C5D-00DF-4220-B133-29C1E0B6585F&displaylang=en
HTH.
Gunny
See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
info.
"Gail Erickson [MS]" <gaile@.online.microsoft.com> wrote in message
news:ezDtd8fbGHA.4892@.TK2MSFTNGP02.phx.gbl...
>A view is very simple to create. The basic syntax is
> CREATE VIEW <viewname>
> AS <select statement>
> See these Books Online topics for the 101 information you need.
> http://msdn2.microsoft.com/en-us/library/ms190174(SQL.90).aspx
> http://msdn2.microsoft.com/en-us/library/ms188250(SQL.90).aspx
> http://msdn2.microsoft.com/en-us/library/ms187956(SQL.90).aspx
> --
> Gail Erickson [MS]
> SQL Server Documentation Team
> This posting is provided "AS IS" with no warranties, and confers no rights
> "M.Siler" <John.Doe@.NoSpam.com> wrote in message
> news:%23tWe5tfbGHA.5104@.TK2MSFTNGP03.phx.gbl...
>>I can see the "evils" of a lookup field in a table. I've never created a
>>view in SQL 2005 before... do you have a site that I could reference that
>>would give me a 101 on creating SQL 2005 views?
>> "'69 Camaro" <ForwardZERO_SPAM.To.69Camaro@.Spameater.orgZERO_SPAM> wrote
>> in message news:uC3ybuWbGHA.3956@.TK2MSFTNGP04.phx.gbl...
>> Hi.
>> Can this be done in SQL 2005?
>> Not on the table itself. And you don't want to, either. It causes huge
>> problems. For more information, please see the article, "The Evils of
>> Lookup Fields in Tables," on the following Web page:
>> http://www.mvps.org/access/lookupfields.htm
>> When I view the Orders table I see a whole list of Employee ID which
>> makes it hard to know which employee is associated to the Employee ID.
>> You shouldn't be viewing the raw data in the tables. That's where the
>> data is stored, but to glean meaningful information from the data, you
>> need to use queries, forms or reports. In a query, you can join the two
>> tables and select the employee's first name and last name to display for
>> each record, not the EmployeeID. Create a similar view in SQL Server.
>> HTH.
>> Gunny
>> See http://www.QBuilt.com for all your database needs.
>> See http://www.Access.QBuilt.com for Microsoft Access tips and
>> tutorials.
>> http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
>> info.
>>
>> "M.Siler" <John.Doe@.NoSpam.com> wrote in message
>> news:eDQUjhWbGHA.3364@.TK2MSFTNGP05.phx.gbl...
>> If you open the Northwind.mdb in access and look at tables Orders &
>> Employees you'll see that both contain "Employee ID", but when you view
>> the Orders table you don't see the Employee ID you see the associated
>> Employee's name. Can this be done in SQL 2005? When I view the Orders
>> table I see a whole list of Employee ID which makes it hard to know
>> which employee is associated to the Employee ID. I have a real world
>> application but I'm only use Northwind as an example as everyone has
>> this database.
>>
>>
>|||Since users shouldn't be viewing tables or even queries, you can simply base
your forms on queries that join the necessary tables. Forms should ALWAYS
be based on queries with selection criteria when the back end is not Jet.
"M.Siler" <John.Doe@.NoSpam.com> wrote in message
news:eDQUjhWbGHA.3364@.TK2MSFTNGP05.phx.gbl...
> If you open the Northwind.mdb in access and look at tables Orders &
> Employees you'll see that both contain "Employee ID", but when you view
> the Orders table you don't see the Employee ID you see the associated
> Employee's name. Can this be done in SQL 2005? When I view the Orders
> table I see a whole list of Employee ID which makes it hard to know which
> employee is associated to the Employee ID. I have a real world application
> but I'm only use Northwind as an example as everyone has this database.
>|||sure, you can easily write a select statement to do this.
in the select statemetn are the columns you want to "see". in the
where section are the data rules that tell the engine how to get to
that data.
Absolutely, for sure, you do NOT have to have the "where" stuff in the
upper "select" stuff.|||I got the view working nicely, but I can't update any of the data from the
view. Is there a way around this or this is how it should be working?
"'69 Camaro" <ForwardZERO_SPAM.To.69Camaro@.Spameater.orgZERO_SPAM> wrote in
message news:OGwfqlgbGHA.4892@.TK2MSFTNGP02.phx.gbl...
> One may also download the latest edition of SQL Server 2005 Books Online
> (BOL), which also replaces previous editions of the SQL Server 2005
> Express BOL. Please see the following Web page for the download:
> http://www.microsoft.com/downloads/details.aspx?FamilyId=BE6A2C5D-00DF-4220-B133-29C1E0B6585F&displaylang=en
> HTH.
> Gunny
> See http://www.QBuilt.com for all your database needs.
> See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
> http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
> info.
>
> "Gail Erickson [MS]" <gaile@.online.microsoft.com> wrote in message
> news:ezDtd8fbGHA.4892@.TK2MSFTNGP02.phx.gbl...
>>A view is very simple to create. The basic syntax is
>> CREATE VIEW <viewname>
>> AS <select statement>
>> See these Books Online topics for the 101 information you need.
>> http://msdn2.microsoft.com/en-us/library/ms190174(SQL.90).aspx
>> http://msdn2.microsoft.com/en-us/library/ms188250(SQL.90).aspx
>> http://msdn2.microsoft.com/en-us/library/ms187956(SQL.90).aspx
>> --
>> Gail Erickson [MS]
>> SQL Server Documentation Team
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights
>> "M.Siler" <John.Doe@.NoSpam.com> wrote in message
>> news:%23tWe5tfbGHA.5104@.TK2MSFTNGP03.phx.gbl...
>>I can see the "evils" of a lookup field in a table. I've never created a
>>view in SQL 2005 before... do you have a site that I could reference that
>>would give me a 101 on creating SQL 2005 views?
>> "'69 Camaro" <ForwardZERO_SPAM.To.69Camaro@.Spameater.orgZERO_SPAM> wrote
>> in message news:uC3ybuWbGHA.3956@.TK2MSFTNGP04.phx.gbl...
>> Hi.
>> Can this be done in SQL 2005?
>> Not on the table itself. And you don't want to, either. It causes
>> huge problems. For more information, please see the article, "The
>> Evils of Lookup Fields in Tables," on the following Web page:
>> http://www.mvps.org/access/lookupfields.htm
>> When I view the Orders table I see a whole list of Employee ID which
>> makes it hard to know which employee is associated to the Employee ID.
>> You shouldn't be viewing the raw data in the tables. That's where the
>> data is stored, but to glean meaningful information from the data, you
>> need to use queries, forms or reports. In a query, you can join the
>> two tables and select the employee's first name and last name to
>> display for each record, not the EmployeeID. Create a similar view in
>> SQL Server.
>> HTH.
>> Gunny
>> See http://www.QBuilt.com for all your database needs.
>> See http://www.Access.QBuilt.com for Microsoft Access tips and
>> tutorials.
>> http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
>> info.
>>
>> "M.Siler" <John.Doe@.NoSpam.com> wrote in message
>> news:eDQUjhWbGHA.3364@.TK2MSFTNGP05.phx.gbl...
>> If you open the Northwind.mdb in access and look at tables Orders &
>> Employees you'll see that both contain "Employee ID", but when you
>> view the Orders table you don't see the Employee ID you see the
>> associated Employee's name. Can this be done in SQL 2005? When I view
>> the Orders table I see a whole list of Employee ID which makes it hard
>> to know which employee is associated to the Employee ID. I have a real
>> world application but I'm only use Northwind as an example as everyone
>> has this database.
>>
>>
>>
>|||It depends on if it's an updatable view or not. The
conditions on when it's updatable or not are listed in books
online in the Create View topic. There is a section titled
Updatable Views.
-Sue
On Fri, 12 May 2006 13:15:39 -0400, "M.Siler"
<John.Doe@.NoSpam.com> wrote:
>I got the view working nicely, but I can't update any of the data from the
>view. Is there a way around this or this is how it should be working?
>
>"'69 Camaro" <ForwardZERO_SPAM.To.69Camaro@.Spameater.orgZERO_SPAM> wrote in
>message news:OGwfqlgbGHA.4892@.TK2MSFTNGP02.phx.gbl...
>> One may also download the latest edition of SQL Server 2005 Books Online
>> (BOL), which also replaces previous editions of the SQL Server 2005
>> Express BOL. Please see the following Web page for the download:
>> http://www.microsoft.com/downloads/details.aspx?FamilyId=BE6A2C5D-00DF-4220-B133-29C1E0B6585F&displaylang=en
>> HTH.
>> Gunny
>> See http://www.QBuilt.com for all your database needs.
>> See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
>> http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
>> info.
>>
>> "Gail Erickson [MS]" <gaile@.online.microsoft.com> wrote in message
>> news:ezDtd8fbGHA.4892@.TK2MSFTNGP02.phx.gbl...
>>A view is very simple to create. The basic syntax is
>> CREATE VIEW <viewname>
>> AS <select statement>
>> See these Books Online topics for the 101 information you need.
>> http://msdn2.microsoft.com/en-us/library/ms190174(SQL.90).aspx
>> http://msdn2.microsoft.com/en-us/library/ms188250(SQL.90).aspx
>> http://msdn2.microsoft.com/en-us/library/ms187956(SQL.90).aspx
>> --
>> Gail Erickson [MS]
>> SQL Server Documentation Team
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights
>> "M.Siler" <John.Doe@.NoSpam.com> wrote in message
>> news:%23tWe5tfbGHA.5104@.TK2MSFTNGP03.phx.gbl...
>>I can see the "evils" of a lookup field in a table. I've never created a
>>view in SQL 2005 before... do you have a site that I could reference that
>>would give me a 101 on creating SQL 2005 views?
>> "'69 Camaro" <ForwardZERO_SPAM.To.69Camaro@.Spameater.orgZERO_SPAM> wrote
>> in message news:uC3ybuWbGHA.3956@.TK2MSFTNGP04.phx.gbl...
>> Hi.
>> Can this be done in SQL 2005?
>> Not on the table itself. And you don't want to, either. It causes
>> huge problems. For more information, please see the article, "The
>> Evils of Lookup Fields in Tables," on the following Web page:
>> http://www.mvps.org/access/lookupfields.htm
>> When I view the Orders table I see a whole list of Employee ID which
>> makes it hard to know which employee is associated to the Employee ID.
>> You shouldn't be viewing the raw data in the tables. That's where the
>> data is stored, but to glean meaningful information from the data, you
>> need to use queries, forms or reports. In a query, you can join the
>> two tables and select the employee's first name and last name to
>> display for each record, not the EmployeeID. Create a similar view in
>> SQL Server.
>> HTH.
>> Gunny
>> See http://www.QBuilt.com for all your database needs.
>> See http://www.Access.QBuilt.com for Microsoft Access tips and
>> tutorials.
>> http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
>> info.
>>
>> "M.Siler" <John.Doe@.NoSpam.com> wrote in message
>> news:eDQUjhWbGHA.3364@.TK2MSFTNGP05.phx.gbl...
>> If you open the Northwind.mdb in access and look at tables Orders &
>> Employees you'll see that both contain "Employee ID", but when you
>> view the Orders table you don't see the Employee ID you see the
>> associated Employee's name. Can this be done in SQL 2005? When I view
>> the Orders table I see a whole list of Employee ID which makes it hard
>> to know which employee is associated to the Employee ID. I have a real
>> world application but I'm only use Northwind as an example as everyone
>> has this database.
>>
>>
>>
>>
>

Sunday, March 25, 2012

can i Display Database report from SQL 2005 using .Net 2005 ?

Can I generate database report from SQL 2005 using .Net 2005 ?

I want report contain the following:

1. How many schemas I have in database.

2. Display schemas list with its information.

I need the SQL Statement or query that return the above result.

Best regards,

Z. A. A.

Hi,

this should be something like:

Select * FROM sys.schemas

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

Sunday, February 19, 2012

can a textbox hold the value of more than 1 field? maybe use funct

Hello,
My question is if a textbox in a report can contain values from multiple
fields from the data source:
txt1.value
=Fields!txt1.Value & "-" & Fields!txt2.Value & "-" & Fields!txt3.Value
If this is doable, what is the method/correct method?
I can add multiple fields to one textbox in an MS Access Report. Can this
be done in a Reporting Services Report? I am thinking I could use a function
which would return the concatenated values of these fields as a string. What
would the code for that function look like?
Thanks,
RichI figured out my problem. I added some new fields to my dataset, but not to
the report. Gotta do that for them to compile without complaining.
"Rich" wrote:
> Hello,
> My question is if a textbox in a report can contain values from multiple
> fields from the data source:
> txt1.value
> =Fields!txt1.Value & "-" & Fields!txt2.Value & "-" & Fields!txt3.Value
> If this is doable, what is the method/correct method?
> I can add multiple fields to one textbox in an MS Access Report. Can this
> be done in a Reporting Services Report? I am thinking I could use a function
> which would return the concatenated values of these fields as a string. What
> would the code for that function look like?
> Thanks,
> Rich|||You are correct, your format looks correct to me.
Steve MunLeeuw
"Rich" <Rich@.discussions.microsoft.com> wrote in message
news:2D73C97A-9840-4D71-91CA-42B27A1D0B40@.microsoft.com...
> Hello,
> My question is if a textbox in a report can contain values from multiple
> fields from the data source:
> txt1.value
> =Fields!txt1.Value & "-" & Fields!txt2.Value & "-" & Fields!txt3.Value
> If this is doable, what is the method/correct method?
> I can add multiple fields to one textbox in an MS Access Report. Can this
> be done in a Reporting Services Report? I am thinking I could use a
> function
> which would return the concatenated values of these fields as a string.
> What
> would the code for that function look like?
> Thanks,
> Rich|||Thank you. I am still learning. Learn by doing. BTW, if I notice a bug,
who can I report that too?
My actual project is using the reportviewer control that comes with VS2005
(it is almost the same as RS except doesn't require a server - and a few
other things). It works pretty good, but when I select a tractor feeding
printer (one of those older wide paper - dotmatrix like printers) if I tell
the layout to print landscape when using US STD Fanfold paper , the little
icon in the dialog display portrait and it prints portrait. Then if I tell
it Portrait when using the US STD Fanfold papter with tractor feed printer -
the icon displays landscapte and prints landscape. It is pretty obvious that
someone mixed up the options.
So I am not trying to be mr. picky, but when the end user uses my product,
it needs to work according to the standards. Who can I report this too?
Thanks,
Rich
"Steve MunLeeuw" wrote:
> You are correct, your format looks correct to me.
> Steve MunLeeuw
> "Rich" <Rich@.discussions.microsoft.com> wrote in message
> news:2D73C97A-9840-4D71-91CA-42B27A1D0B40@.microsoft.com...
> > Hello,
> >
> > My question is if a textbox in a report can contain values from multiple
> > fields from the data source:
> >
> > txt1.value
> >
> > =Fields!txt1.Value & "-" & Fields!txt2.Value & "-" & Fields!txt3.Value
> >
> > If this is doable, what is the method/correct method?
> >
> > I can add multiple fields to one textbox in an MS Access Report. Can this
> > be done in a Reporting Services Report? I am thinking I could use a
> > function
> > which would return the concatenated values of these fields as a string.
> > What
> > would the code for that function look like?
> >
> > Thanks,
> > Rich
>
>|||http://connect.microsoft.com/SQLServer/Feedback
Yeah, dealing with different page sizes can be tricky from what I gather.
Luckily I haven't had to deal with that much. Adobe allows you to have
pages in both landscape and portrait in the same document I was asked if I
could do that the other day. I don't think I could.
Steve MunLeeuw
"Rich" <Rich@.discussions.microsoft.com> wrote in message
news:D2173E9D-E9AF-410D-AFEE-E919AF360951@.microsoft.com...
> Thank you. I am still learning. Learn by doing. BTW, if I notice a bug,
> who can I report that too?
> My actual project is using the reportviewer control that comes with VS2005
> (it is almost the same as RS except doesn't require a server - and a few
> other things). It works pretty good, but when I select a tractor feeding
> printer (one of those older wide paper - dotmatrix like printers) if I
> tell
> the layout to print landscape when using US STD Fanfold paper , the little
> icon in the dialog display portrait and it prints portrait. Then if I
> tell
> it Portrait when using the US STD Fanfold papter with tractor feed
> printer -
> the icon displays landscapte and prints landscape. It is pretty obvious
> that
> someone mixed up the options.
> So I am not trying to be mr. picky, but when the end user uses my product,
> it needs to work according to the standards. Who can I report this too?
> Thanks,
> Rich
> "Steve MunLeeuw" wrote:
>> You are correct, your format looks correct to me.
>> Steve MunLeeuw
>> "Rich" <Rich@.discussions.microsoft.com> wrote in message
>> news:2D73C97A-9840-4D71-91CA-42B27A1D0B40@.microsoft.com...
>> > Hello,
>> >
>> > My question is if a textbox in a report can contain values from
>> > multiple
>> > fields from the data source:
>> >
>> > txt1.value
>> >
>> > =Fields!txt1.Value & "-" & Fields!txt2.Value & "-" & Fields!txt3.Value
>> >
>> > If this is doable, what is the method/correct method?
>> >
>> > I can add multiple fields to one textbox in an MS Access Report. Can
>> > this
>> > be done in a Reporting Services Report? I am thinking I could use a
>> > function
>> > which would return the concatenated values of these fields as a string.
>> > What
>> > would the code for that function look like?
>> >
>> > Thanks,
>> > Rich
>>

Thursday, February 16, 2012

Can a managed udf contain a static dictionary<>? Is this a wise idea?

I currently have a udf written in T-SQL that's getting way too logically complicated!

It’s typically accessed like this:

SELECT PartNumber,dbo.PartPrice(Manufacturer, Model, AssemblageInfo, Version, CustomerDiscountLevel) FROM WorkOrders where OrderNumber=123456

The udf does some complicated manipulations on the parameters and eventually does a SELECT on a lookup table and returns the result.

If I make this a managed code udf, the logic gets much simpler to write (great!).

But, my question is:

Can I take the lookup table and embed it in the udf--so the udf doesn't have to go to the database to do the lookup?

Would I do that in a STATIC dictionary<>?

Is it wise to keep the info statically?

The lookup table consists of 3600(+/-) elements and changes exactly once a month.

The SELECT statement using the udf typically returns several thousand rows.

The SELECT is done often.

--Mark

If the table changes over the time, I would not implement this as a static dictionary as you would have to recreate the function everytime the tables changes and you would hae more trouble changing the data of the table than it would be in a normal static data table in SQL Server.

Jens K. Suessmeyer.

http://www.sqlserver2005.de
|||

Hi Jens!

Thanks for the response!

I thought of the refresh function thing.

I 'm willing to make it a monthly chore to refresh the function in exchange for a faster static dictionary lookups--but only if it really will speed up the SELECTs.

I guess this brings the question of when the static dictionary goes out of scope on a function call.

For example, if the dictionary is reloaded for each row of a SELECT, then it's worse than worthless!

If the dictionary is retained for all rows in a SELECT statement, it **might** be useful.

If the dictionary is retained over many SELECT's then it's worthwhile.

If the dictionary is retained all month, then it's priceless (as the mastercard commercial says!).

When does a static dictionary within a function go out of scope?

Is a static dictionary retained in memory for all rows in a SELECT statement?

Is a static dictionary retained in memory between function calls?

Does the SQL engine cache the function and then release the dictionary from memory after a time-out period?

Thanks, Mark

|||

I finally got to a machine with SQL2005 to try the static dictionary<>

Turns out you can't even use static variables in a managed udf--at least not without declaring the code as unsafe.

--Mark

Tuesday, March 27, 2012

Can I have an SQL view like in Access

Can I have an SQL view like in Access

Sunday, March 25, 2012

can i Display Database report from SQL 2005 using .Net 2005 ?

Sunday, February 19, 2012

can a textbox hold the value of more than 1 field? maybe use funct

Thursday, February 16, 2012

Can a managed udf contain a static dictionary<>? Is this a wise idea?

Calling Webservice

Blog Archive

About Me