General Layout

Hello,

is it possible to create a general header which can be used from all reports?

I want to change only this one header and all reports from this Project get the same header, too!

Greez DYDaniel

You can create a report template where you add your header, check out this blog entry.

However: These templates are not "connected" to the reports created from them. So when you update the template, the reports created from the template won't be updated to reflect the changes.

General configuration question - maximum performance.

This is a hyperthetical question.
If a web site is running slowly then we can create a web farm and spilt the
problem between multiple servers.
However if my database is running slowly, and given that its schema is well
designed and the application that is communicating with it is well designed,
what options do I have to speed it up? Does SQL Server have a concept of
a database farm? Can I throw another server at it?
I understand that I could peel off read-only data to another database but
what would happen if it was all read-write? eg maybe like a banks computer
system or a credit card processing authority?
I could throw more CPU, RAM, hard disk, etc at it but ultimately this only
goes so far.
What databases do the BIG database users use? eg AMEX? Walmart? etc. I
guess they are not on SQL Server?
Regards
Dave ADave
I'd turn on SQL Server Profiler to identify long running queries , stored
procedures and try to imrove them. How to improve, that is probably playing
with indexes and/or if you are in SQL Server 2005 let the 'problematic'
query run throu Tuning wizard to advise you.
> What databases do the BIG database users use? eg AMEX? Walmart? etc. I
> guess they are not on SQL Server?
SQL Server has no problem to keep TERA byte databases , the point is how
well the db is designed?
"Dave A" <dave@.sigmasolutionsdonotspamme.com.au> wrote in message
news:e5kphEFJIHA.1208@.TK2MSFTNGP03.phx.gbl...
> This is a hyperthetical question.
> If a web site is running slowly then we can create a web farm and spilt
> the problem between multiple servers.
> However if my database is running slowly, and given that its schema is
> well designed and the application that is communicating with it is well
> designed, what options do I have to speed it up? Does SQL Server have a
> concept of a database farm? Can I throw another server at it?
> I understand that I could peel off read-only data to another database but
> what would happen if it was all read-write? eg maybe like a banks computer
> system or a credit card processing authority?
> I could throw more CPU, RAM, hard disk, etc at it but ultimately this only
> goes so far.
> What databases do the BIG database users use? eg AMEX? Walmart? etc. I
> guess they are not on SQL Server?
> Regards
> Dave A
>|||Remember, this is a hyperthetical question. There is no need to run Profile
since there is no database to profile.
I have no doubt that SQL Server can handle multiterabyte datbases, my
problem is when there are a million conurrenct users updating and reading
that data, what options does an SQL Server designer have at thier disposal?
(Don't worry, I am an SQL Server zealot. I am just wondering what happens
when we push the limits and how far can it go.)
So, my question remains. Is there a concept of a farm of sql servers? - just
like there is a cluster of sql servers or a farm of web servers.
Regards
Dave A
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:ec0KCYFJIHA.5468@.TK2MSFTNGP05.phx.gbl...
> Dave
> I'd turn on SQL Server Profiler to identify long running queries , stored
> procedures and try to imrove them. How to improve, that is probably
> playing with indexes and/or if you are in SQL Server 2005 let the
> 'problematic' query run throu Tuning wizard to advise you.
>> What databases do the BIG database users use? eg AMEX? Walmart? etc. I
>> guess they are not on SQL Server?
>
> SQL Server has no problem to keep TERA byte databases , the point is how
> well the db is designed?
>
>
> "Dave A" <dave@.sigmasolutionsdonotspamme.com.au> wrote in message
> news:e5kphEFJIHA.1208@.TK2MSFTNGP03.phx.gbl...
>> This is a hyperthetical question.
>> If a web site is running slowly then we can create a web farm and spilt
>> the problem between multiple servers.
>> However if my database is running slowly, and given that its schema is
>> well designed and the application that is communicating with it is well
>> designed, what options do I have to speed it up? Does SQL Server have
>> a concept of a database farm? Can I throw another server at it?
>> I understand that I could peel off read-only data to another database but
>> what would happen if it was all read-write? eg maybe like a banks
>> computer system or a credit card processing authority?
>> I could throw more CPU, RAM, hard disk, etc at it but ultimately this
>> only goes so far.
>> What databases do the BIG database users use? eg AMEX? Walmart? etc. I
>> guess they are not on SQL Server?
>> Regards
>> Dave A
>|||When it comes to CPU and RAM, you can only scale up, not scale out. So
"throwing another server at it" will not help.
With the long standing trend of ever faster CPU's and the growing number
of cores per computer this can go a long way.
But even if SQL Server supported scaling out, this would only go "so
far" too. If you have one server and add an identical server, the
performance gain will never be 100%, it will be less. There is a limit
as to how much you can scale out, just as there is a limit to how much
you can scale up.
You should have a look at the TPC numbers show (see www.tpc.org). An
interesting chart is the Top 10 TPC-C by Performance. You will notice
that there is only one clustered configuration in this top 10, which
uses Oracle 10g. All others are nonclustered configuration are
nonclustered, including 4 other Oracle 10g configurations.
I therefore conclude that currently, there is no practical need for a
clustered SQL Server solution.
--
Gert-Jan
Dave A wrote:
> This is a hyperthetical question.
> If a web site is running slowly then we can create a web farm and spilt the
> problem between multiple servers.
> However if my database is running slowly, and given that its schema is well
> designed and the application that is communicating with it is well designed,
> what options do I have to speed it up? Does SQL Server have a concept of
> a database farm? Can I throw another server at it?
> I understand that I could peel off read-only data to another database but
> what would happen if it was all read-write? eg maybe like a banks computer
> system or a credit card processing authority?
> I could throw more CPU, RAM, hard disk, etc at it but ultimately this only
> goes so far.
> What databases do the BIG database users use? eg AMEX? Walmart? etc. I
> guess they are not on SQL Server?
> Regards
> Dave A|||On Sun, 11 Nov 2007 20:54:20 +1000, "Dave A"
<dave@.sigmasolutionsdonotspamme.com.au> wrote:
>This is a hyperthetical question.
>If a web site is running slowly then we can create a web farm and spilt the
>problem between multiple servers.
>However if my database is running slowly, and given that its schema is well
>designed and the application that is communicating with it is well designed,
>what options do I have to speed it up? Does SQL Server have a concept of
>a database farm? Can I throw another server at it?
No. Not automagically. You are of course free to put all the A's on
one server, etc, and replicate data between them. This is often
enough done, but it is a lot of work. SQL Server 2008 looks like it's
aimed at much bigger configs, but I'm not up on the details.
>I could throw more CPU, RAM, hard disk, etc at it but ultimately this only
>goes so far.
It goes pretty far, on a maxed-out Wintel machine, these days, with
SAN storage, etc. But increasingly, you actually have to know what
you're doing, config the hardware right, and be very careful of the
execution plans going sour on you. But do all that, and we're
probably running about 10^6 bigger and faster than SQL 6.5 on a 100mhz
server ten years ago! On a given hardware platform, for an average
transactional mix (whatever that might mean), or for a preplanned OLAP
environment, SQLServer is at least competitive with anyone.
>What databases do the BIG database users use? eg AMEX? Walmart? etc. I
>guess they are not on SQL Server?
Oracle is still your game if you want to throw hardware at the
problem. Teradata is another way to go on humongous databases and
ad-hoc queries. I suppose DB2 fits in there somewhere, but I don't
know quite where.
J.

General configuration question - maximum performance.

This is a hyperthetical question.
If a web site is running slowly then we can create a web farm and spilt the
problem between multiple servers.
However if my database is running slowly, and given that its schema is well
designed and the application that is communicating with it is well designed,
what options do I have to speed it up? Does SQL Server have a concept of
a database farm? Can I throw another server at it?
I understand that I could peel off read-only data to another database but
what would happen if it was all read-write? eg maybe like a banks computer
system or a credit card processing authority?
I could throw more CPU, RAM, hard disk, etc at it but ultimately this only
goes so far.
What databases do the BIG database users use? eg AMEX? Walmart? etc. I
guess they are not on SQL Server?
Regards
Dave ADave
I'd turn on SQL Server Profiler to identify long running queries , stored
procedures and try to imrove them. How to improve, that is probably playing
with indexes and/or if you are in SQL Server 2005 let the 'problematic'
query run throu Tuning wizard to advise you.

> What databases do the BIG database users use? eg AMEX? Walmart? etc. I
> guess they are not on SQL Server?
SQL Server has no problem to keep TERA byte databases , the point is how
well the db is designed?
"Dave A" <dave@.sigmasolutionsdonotspamme.com.au> wrote in message
news:e5kphEFJIHA.1208@.TK2MSFTNGP03.phx.gbl...
> This is a hyperthetical question.
> If a web site is running slowly then we can create a web farm and spilt
> the problem between multiple servers.
> However if my database is running slowly, and given that its schema is
> well designed and the application that is communicating with it is well
> designed, what options do I have to speed it up? Does SQL Server have a
> concept of a database farm? Can I throw another server at it?
> I understand that I could peel off read-only data to another database but
> what would happen if it was all read-write? eg maybe like a banks computer
> system or a credit card processing authority?
> I could throw more CPU, RAM, hard disk, etc at it but ultimately this only
> goes so far.
> What databases do the BIG database users use? eg AMEX? Walmart? etc. I
> guess they are not on SQL Server?
> Regards
> Dave A
>|||Remember, this is a hyperthetical question. There is no need to run Profile
since there is no database to profile.
I have no doubt that SQL Server can handle multiterabyte datbases, my
problem is when there are a million conurrenct users updating and reading
that data, what options does an SQL Server designer have at thier disposal?
(Don't worry, I am an SQL Server zealot. I am just wondering what happens
when we push the limits and how far can it go.)
So, my question remains. Is there a concept of a farm of sql servers? - just
like there is a cluster of sql servers or a farm of web servers.
Regards
Dave A
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:ec0KCYFJIHA.5468@.TK2MSFTNGP05.phx.gbl...
> Dave
> I'd turn on SQL Server Profiler to identify long running queries , stored
> procedures and try to imrove them. How to improve, that is probably
> playing with indexes and/or if you are in SQL Server 2005 let the
> 'problematic' query run throu Tuning wizard to advise you.
>
>
> SQL Server has no problem to keep TERA byte databases , the point is how
> well the db is designed?
>
>
> "Dave A" <dave@.sigmasolutionsdonotspamme.com.au> wrote in message
> news:e5kphEFJIHA.1208@.TK2MSFTNGP03.phx.gbl...
>|||When it comes to CPU and RAM, you can only scale up, not scale out. So
"throwing another server at it" will not help.
With the long standing trend of ever faster CPU's and the growing number
of cores per computer this can go a long way.
But even if SQL Server supported scaling out, this would only go "so
far" too. If you have one server and add an identical server, the
performance gain will never be 100%, it will be less. There is a limit
as to how much you can scale out, just as there is a limit to how much
you can scale up.
You should have a look at the TPC numbers show (see www.tpc.org). An
interesting chart is the Top 10 TPC-C by Performance. You will notice
that there is only one clustered configuration in this top 10, which
uses Oracle 10g. All others are nonclustered configuration are
nonclustered, including 4 other Oracle 10g configurations.
I therefore conclude that currently, there is no practical need for a
clustered SQL Server solution.
Gert-Jan
Dave A wrote:
> This is a hyperthetical question.
> If a web site is running slowly then we can create a web farm and spilt th
e
> problem between multiple servers.
> However if my database is running slowly, and given that its schema is wel
l
> designed and the application that is communicating with it is well designe
d,
> what options do I have to speed it up? Does SQL Server have a concept o
f
> a database farm? Can I throw another server at it?
> I understand that I could peel off read-only data to another database but
> what would happen if it was all read-write? eg maybe like a banks computer
> system or a credit card processing authority?
> I could throw more CPU, RAM, hard disk, etc at it but ultimately this only
> goes so far.
> What databases do the BIG database users use? eg AMEX? Walmart? etc. I
> guess they are not on SQL Server?
> Regards
> Dave A|||On Sun, 11 Nov 2007 20:54:20 +1000, "Dave A"
<dave@.sigmasolutionsdonotspamme.com.au> wrote:

>This is a hyperthetical question.
>If a web site is running slowly then we can create a web farm and spilt the
>problem between multiple servers.
>However if my database is running slowly, and given that its schema is well
>designed and the application that is communicating with it is well designed
,
>what options do I have to speed it up? Does SQL Server have a concept of
>a database farm? Can I throw another server at it?
No. Not automagically. You are of course free to put all the A's on
one server, etc, and replicate data between them. This is often
enough done, but it is a lot of work. SQL Server 2008 looks like it's
aimed at much bigger configs, but I'm not up on the details.

>I could throw more CPU, RAM, hard disk, etc at it but ultimately this only
>goes so far.
It goes pretty far, on a maxed-out Wintel machine, these days, with
SAN storage, etc. But increasingly, you actually have to know what
you're doing, config the hardware right, and be very careful of the
execution plans going sour on you. But do all that, and we're
probably running about 10^6 bigger and faster than SQL 6.5 on a 100mhz
server ten years ago! On a given hardware platform, for an average
transactional mix (whatever that might mean), or for a preplanned OLAP
environment, SQLServer is at least competitive with anyone.

>What databases do the BIG database users use? eg AMEX? Walmart? etc. I
>guess they are not on SQL Server?
Oracle is still your game if you want to throw hardware at the
problem. Teradata is another way to go on humongous databases and
ad-hoc queries. I suppose DB2 fits in there somewhere, but I don't
know quite where.
J.

further to problem with obtaining first/last occurance of contiguous blocks of data

Hi again, I was hoping someone could help me create a sql query to
minimizing the content in my table based on a few rules.
Further to my previous post...
f2b9da63" target="_blank">http://groups.google.com.au/group/m...br />
f2b9da63
... I have decided to use triggers to process my table (see trigger solutio
n
in my previous post). However since triggers are slow at processing, I aim
to search for a solution to minimize my initial table structure so the
triggers have less data to work with (hence process it much quicker). What I
am looking for is a method to perform the following:
This is my initial table (as an example):
[system] [date] [isOn]
A 01 0
A 04 1
A 05 1
A 06 0
A 20 1
A 21 0
A 25 0
A 27 1
A 32 1
A 33 1
A 34 0
A 40 1
B 41 1
B 45 0
B 49 1
B 50 1
B 51 1
B 53 1
B 67 0
I want my final table to look like this:
[system] [date] [isOn]
A 01 0
A 04 1
-
A 06 0
A 20 1
A 21 0
-
A 27 1
-
-
A 34 0
A 40 1
B 41 1
B 45 0
B 49 1
-
-
-
B 67 0
... where i have placed a '-' sign to indicate the rows i need deleted
I am deleting rows under the following conditions:
1. for a contiguous block of 1's WITHIN the same client range, delete all
but the first one ( i.e.the one at the earliest date)
2. for a contiguous block of 0's WITHIN the same client range, delete all
but the first one (i.e. the one at the earliest date)
i.e. notice above that even though there is a contiguous block of 1's from
date = 40 to 41, I have not remove the 2nd '1' as that crosses into client
B.
After this table is thus processed, I can use my cursor on it.
Any help would be really appreciated!
many thanks
wileyI have scrapped my cursor implementation for a much faster set-based
approach put forward by Itzik Ben-Gan (as im my previous post). Thanks
everyone!
cheers
wiley
"wiley" <wiley@.nospam.com> wrote in message
news:uK5SI4SFGHA.216@.TK2MSFTNGP15.phx.gbl...
> Hi again, I was hoping someone could help me create a sql query to
> minimizing the content in my table based on a few rules.
> Further to my previous post...
> 29f2b9da63" target="_blank">http://groups.google.com.au/group/m... />
29f2b9da63
> ... I have decided to use triggers to process my table (see trigger
> solution in my previous post). However since triggers are slow at
> processing, I aim to search for a solution to minimize my initial table
> structure so the triggers have less data to work with (hence process it
> much quicker). What I am looking for is a method to perform the following:
> This is my initial table (as an example):
> [system] [date] [isOn]
> A 01 0
> A 04 1
> A 05 1
> A 06 0
> A 20 1
> A 21 0
> A 25 0
> A 27 1
> A 32 1
> A 33 1
> A 34 0
> A 40 1
> B 41 1
> B 45 0
> B 49 1
> B 50 1
> B 51 1
> B 53 1
> B 67 0
> I want my final table to look like this:
> [system] [date] [isOn]
> A 01 0
> A 04 1
> -
> A 06 0
> A 20 1
> A 21 0
> -
> A 27 1
> -
> -
> A 34 0
> A 40 1
> B 41 1
> B 45 0
> B 49 1
> -
> -
> -
> B 67 0
> ... where i have placed a '-' sign to indicate the rows i need deleted
> I am deleting rows under the following conditions:
> 1. for a contiguous block of 1's WITHIN the same client range, delete all
> but the first one ( i.e.the one at the earliest date)
> 2. for a contiguous block of 0's WITHIN the same client range, delete all
> but the first one (i.e. the one at the earliest date)
> i.e. notice above that even though there is a contiguous block of 1's from
> date = 40 to 41, I have not remove the 2nd '1' as that crosses into client
> B.
> After this table is thus processed, I can use my cursor on it.
> Any help would be really appreciated!
> many thanks
> wiley
>

Funny problem (bug?) using MAX() on SQL Mobile

This sure looks like a bug, but I'm still new...

First I create a simple database:

SqlCeEngine eng = new SqlCeEngine(@."Data Source=\My Documents\test.sdf");
eng.CreateDatabase();
eng.Dispose();

Then I put something in it:

SqlCeConnection conn = new SqlCeConnection(@."Data Source=\My Documents\test.sdf");
SqlCeCommand cmd = new SqlCeCommand("CREATE TABLE myTable (ID int, name nchar(10))");
cmd.Connection=conn;
conn.Open();
cmd.ExecuteNonQuery();
cmd.CommandText="INSERT INTO myTable (ID, name) VALUES (1,'bill')";
cmd.ExecuteNonQuery();

Then I try to get the maximum value from the first column.

cmd.CommandText = "SELECT max(ID) FROM myTable";
int index = (int)cmd.ExecuteScalar();

At this point I get an InvalidCastException raised. Using the datareader to extract the value produces the same exception, however I can go through the Query Analyzer on the device and execute the query and it comes out fine...?

This has really got me confused. Any thoughts?

What object type you're actually getting?|||Good question--how do I find that out?|||

you should NEVER cast the result of an execute scalar before you check for DBNull (not the same as C# null or VB Nothing) being returned.

VB.NET

Dim o As Object = cmd.ExecuteScalar()
If Not IsDBNull(o) Then

' now it's safe to cast the result

End If

-Darren

|||

You can look in the debugger or you can print out type name:

object o = ...;

// Get that mysteries type here

string s = o.GetType().ToString();

// Print out string here

And yes, you do need to check for DBNull.

|||

Yes, I'm still learning this stuff...

It turns out an System.Int64 is being returned. That hadn't occured to me since I was under the impression that MAX() will return the type that it operates on (in this case an int32). Nevertheless, I am now able to successfully cast it to an int32 and use it in my program.

Thanks for your help guys. Problem solved.

Fundamental help required with SQL connection

I am writing SQL data apps using VB.NET 2003, MSDE is being used as the
server.

When I create and instance of the server, it has a the format
machinename/instancename

This is fine for the developement machine but how does my code connect to
the server when it is on another machine ? When an instance of MSDE is run
on the target, it will produce...
anotherPCname/instancename

Thanks for any helpThe 'Data Source' keyword in the connection string specifies the SQL Server
instance you want to connect to. A best practice is to store the connection
string externally (e.g. config file) rather than hard-code it in your app so
that you can connect to different servers without changing code. Connection
string examples:

Local default instance:
Data Source=ThisServer;Initial Catalog=MyDatabase;Integrated Security=SSPI

Local named instance:
Data Source=ThisServer\ThisInstance;Initial Catalog=MyDatabase;Integrated
Security=SSPI

Remote default instance:
Data Source=OtherServer;Initial Catalog=MyDatabase;Integrated Security=SSPI

Remote named instance:
Data Source=OtherServer\OtherInstance;Initial Catalog=MyDatabase;Integrated
Security=SSPI

--
Hope this helps.

Dan Guzman
SQL Server MVP

"David" <david@.orbitcoms.com> wrote in message
news:YNvEc.71214$sj4.25491@.news-server.bigpond.net.au...
> I am writing SQL data apps using VB.NET 2003, MSDE is being used as the
> server.
> When I create and instance of the server, it has a the format
> machinename/instancename
> This is fine for the developement machine but how does my code connect to
> the server when it is on another machine ? When an instance of MSDE is run
> on the target, it will produce...
> anotherPCname/instancename
> Thanks for any help
>|||Dan,

Thanks for the reply.

So, I just need to create a text file with the connection string with say

the local machine ID and database name. The application loads this at boot

and

connects to the named server.

When I deploy the application, I have the client alter the text file by

finding

the machine name and modifying the text to suit ??

I suppose I could use a Try Catch and have try with the local settings and

if it fails

it loads the file in the catch sub (or maybe the string is stored in

registry).

One thing I am also unable to find out is. How does MSDE know where the file

is located on the third party machine ? Should the Initial Catalog =

Path+dasename ?

Thanks

"Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
news:FayEc.70$oD3.17@.newsread1.news.pas.earthlink. net...
> The 'Data Source' keyword in the connection string specifies the SQL
Server
> instance you want to connect to. A best practice is to store the
connection
> string externally (e.g. config file) rather than hard-code it in your app
so
> that you can connect to different servers without changing code.
Connection
> string examples:
> Local default instance:
> Data Source=ThisServer;Initial Catalog=MyDatabase;Integrated Security=SSPI
> Local named instance:
> Data Source=ThisServer\ThisInstance;Initial Catalog=MyDatabase;Integrated
> Security=SSPI
> Remote default instance:
> Data Source=OtherServer;Initial Catalog=MyDatabase;Integrated
Security=SSPI
> Remote named instance:
> Data Source=OtherServer\OtherInstance;Initial
Catalog=MyDatabase;Integrated
> Security=SSPI
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "David" <david@.orbitcoms.com> wrote in message
> news:YNvEc.71214$sj4.25491@.news-server.bigpond.net.au...
> > I am writing SQL data apps using VB.NET 2003, MSDE is being used as the
> > server.
> > When I create and instance of the server, it has a the format
> > machinename/instancename
> > This is fine for the developement machine but how does my code connect
to
> > the server when it is on another machine ? When an instance of MSDE is
run
> > on the target, it will produce...
> > anotherPCname/instancename
> > Thanks for any help|||Hi

To connect to a different instance/server that is running your database then
the connection string will need to be changed. Quite often this sort of
thing is held in the registry and you configure it on installation.
Alternatively you may want to provide a small application to configure the
settings.

http://www.microsoft.com/sql/msde/t...integration.asp

John

"David" <david@.orbitcoms.com> wrote in message
news:YNvEc.71214$sj4.25491@.news-server.bigpond.net.au...
> I am writing SQL data apps using VB.NET 2003, MSDE is being used as the
> server.
> When I create and instance of the server, it has a the format
> machinename/instancename
> This is fine for the developement machine but how does my code connect to
> the server when it is on another machine ? When an instance of MSDE is run
> on the target, it will produce...
> anotherPCname/instancename
> Thanks for any help
>|||Thanks again for the replies.

I have tried a hard-coded option of connection and it worked.

ie.

TRY
sqlconnectionstring = ...... originalmachinename......password=..."
CATCH
sqlconnectionstring = ...... newmachinename......password=..."
END TRY

I tried to hook this into a config file but I had trouble(I used the wizard
in VB.NET to make the connection string and edited it but the area the code
was placed did not seem to like me playing with the code adding file open
command etc.

I will try by completely coding the connection in the main form load
routine.
Like this

TRY
Load my original instance for development and test
CATCH
read registry and see if validconnect string exists
if so then try to connect else
prompt user for PC name
save name to registry
try to connect
FINALLY
give up
END TRY

would this approach seem reasonable ?

"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:_FyEc.6675$%N6.73110644@.news-text.cableinet.net...
> Hi
> To connect to a different instance/server that is running your database
then
> the connection string will need to be changed. Quite often this sort of
> thing is held in the registry and you configure it on installation.
> Alternatively you may want to provide a small application to configure the
> settings.
> http://www.microsoft.com/sql/msde/t...integration.asp
> John
> "David" <david@.orbitcoms.com> wrote in message
> news:YNvEc.71214$sj4.25491@.news-server.bigpond.net.au...
> > I am writing SQL data apps using VB.NET 2003, MSDE is being used as the
> > server.
> > When I create and instance of the server, it has a the format
> > machinename/instancename
> > This is fine for the developement machine but how does my code connect
to
> > the server when it is on another machine ? When an instance of MSDE is
run
> > on the target, it will produce...
> > anotherPCname/instancename
> > Thanks for any help|||Hi

This may help:
http://support.microsoft.com/defaul...kb;en-us;309485
http://support.microsoft.com/defaul...kb;EN-US;283245

John

"David" <david@.orbitcoms.com> wrote in message
news:qazEc.71562$sj4.10264@.news-server.bigpond.net.au...
> Thanks again for the replies.
> I have tried a hard-coded option of connection and it worked.
> ie.
> TRY
> sqlconnectionstring = ......
originalmachinename......password=..."
> CATCH
> sqlconnectionstring = ...... newmachinename......password=..."
> END TRY
> I tried to hook this into a config file but I had trouble(I used the
wizard
> in VB.NET to make the connection string and edited it but the area the
code
> was placed did not seem to like me playing with the code adding file open
> command etc.
> I will try by completely coding the connection in the main form load
> routine.
> Like this
> TRY
> Load my original instance for development and test
> CATCH
> read registry and see if validconnect string exists
> if so then try to connect else
> prompt user for PC name
> save name to registry
> try to connect
> FINALLY
> give up
> END TRY
> would this approach seem reasonable ?
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:_FyEc.6675$%N6.73110644@.news-text.cableinet.net...
> > Hi
> > To connect to a different instance/server that is running your database
> then
> > the connection string will need to be changed. Quite often this sort of
> > thing is held in the registry and you configure it on installation.
> > Alternatively you may want to provide a small application to configure
the
> > settings.
> > http://www.microsoft.com/sql/msde/t...integration.asp
> > John
> > "David" <david@.orbitcoms.com> wrote in message
> > news:YNvEc.71214$sj4.25491@.news-server.bigpond.net.au...
> > > I am writing SQL data apps using VB.NET 2003, MSDE is being used as
the
> > > server.
> > > > When I create and instance of the server, it has a the format
> > > machinename/instancename
> > > > This is fine for the developement machine but how does my code connect
> to
> > > the server when it is on another machine ? When an instance of MSDE is
> run
> > > on the target, it will produce...
> > > anotherPCname/instancename
> > > > Thanks for any help
> > > >|||I finally have the problem figured out. I needed to ATTACH the database to
sql server (MSDE2000) using the osql utility.

Now, would someone be able to explain how I can automate the attachment at
the customer end.
Preferably, I would like my VN.NET application install to copy the
unattached database files and attach them to the MSDE server.

Thanks

"David" <david@.orbitcoms.com> wrote in message
news:YNvEc.71214$sj4.25491@.news-server.bigpond.net.au...
> I am writing SQL data apps using VB.NET 2003, MSDE is being used as the
> server.
> When I create and instance of the server, it has a the format
> machinename/instancename
> This is fine for the developement machine but how does my code connect to
> the server when it is on another machine ? When an instance of MSDE is run
> on the target, it will produce...
> anotherPCname/instancename
> Thanks for any help
>|||Hi
I am totally new to the VB.net, only learn this about two weeks. I
used codes from MS to create a file upload class then add function to
store the uploaded filename to a table in SQL server. However I kept
getting the error message about "cannot connect to database" No matter
what format I used. I have tried at least two dozen connection stringe
formats either in Sqlconnection or OleDBConnection. Here is the basic
formats that I used:
strCon = "Data Source =localhost;Password=;User ID=sa;Initial
Catalog=Northwind" for SqlConnection
strCon = "Provider=SQLOLEDB.1;User ID=sa;Password=;Persist Security
Info=True;Initial Catalog=Northwind;Data Source=FAL-L6388" for
OleDbConnection.
I use SQL 7, which was installed by default, i,e only have user ID =
sa, no password, use the default Northwind database. That's all. My
Server is local named FAL-L688.
I have replaced the Data Source with Server= but still give me same
error. I even used an udl to create the OleDB coonnection and put
into my string, still NO. I have exhauseted all alternatives. Does
anyone have clue? Is anyway to debug the trasaction? so i can find out
what went wrong?|||Here is my codes:
Imports System.Data.OleDb
Imports System.Configuration
...
Dim oCon As New OleDbConnection
Dim oCmd As New OleDbCommand
oCon = New OleDbConnection(ConfigurationSettings.AppSettings( "strCon"))
oCon.Open()

oCmd.CommandType = CommandType.StoredProcedure
oCmd.CommandText = "set_order_attachments"
oCmd.Parameters.Add("@.cart_id", OleDbType.VarChar, 15, cart_id)
oCmd.Parameters.Add("@.attachment", OleDbType.VarChar, 255,
strFilenamesCollection)
oCmd.Connection = oCon
Try
oCon.Open()
oCmd.ExecuteNonQuery()
...
And I have fololowing setting in the Web.config file under the
<configuration>
<appSettings>
<add key="strCon"
value="Provider=SQLOLEDB.1;User ID=sa;Password=;Initial
Catalog=Northwind;Server=(local);" />
</appSettings>
Also tried to use "Data Source=.." and enter my local machine name
there.
None of them work , came back the same "Non DB connection"

C Chang|||There was a duplicated "oCon.Open()" in my codes, but removal it does
not help. However, I tried to use the OracleCommand with the SQL query
script directly and IT WORKS!. Does anyone know why it does not work
with the Oracle procedure. I hate to change all my procedure calls to
in-line scripts. Besides if there is a complicated procedure then the
query becomes a problem.|||(chi-soon_x_chang@.raytheon.com) writes:
> There was a duplicated "oCon.Open()" in my codes, but removal it does
> not help. However, I tried to use the OracleCommand with the SQL query
> script directly and IT WORKS!. Does anyone know why it does not work
> with the Oracle procedure. I hate to change all my procedure calls to
> in-line scripts. Besides if there is a complicated procedure then the
> query becomes a problem.

I'm getting confused, are you connecting to SQL Server or to Oracle?

To go back to your original code, the error message seems like it
would from your own code. That is, I cannot really recall any error
that says "Cannot connect to database". It would help, if you could
get hold of the actual message from the client library. To that end,
it would help if you posted more parts of the code, and also if you
found out on exactly which statement, things go wrong.

Stupid check: you have SQL Server running, haven't you?

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Hi

In addition to Erlands comments, you may want to move the try block earlier
to catch the connection error. Also check out
http://www.connectionstrings.com/ for the connection string, I think you
should be using Data Source instead of Server. Try using the IP address
instead of the name if necessary.

John

<chi-soon_x_chang@.raytheon.com> wrote in message
news:1103838243.185277.162240@.z14g2000cwz.googlegr oups.com...
> There was a duplicated "oCon.Open()" in my codes, but removal it does
> not help. However, I tried to use the OracleCommand with the SQL query
> script directly and IT WORKS!. Does anyone know why it does not work
> with the Oracle procedure. I hate to change all my procedure calls to
> in-line scripts. Besides if there is a complicated procedure then the
> query becomes a problem.

Functions in SQL Server7

Is it possible to create Functions in SqlServer 7?
I have a huge query > 500,000 rows that I want to select a subset of using a
function
Select IdentityInd, ColA, ColB
From TableA Where
UDFContains(IdentityInd, ColB ) = 1
**************************************
--Function
And UDFContains will looklike
UDFContains(@.IdentityInd, @.ColB )
Returns Bit
Begin
IF EXISTS(Select IdentityInd From TableA Where
IdentityInd = @.IdentityInd AND CONTAINS(ColA,
@.ColB)) BEGIN
Return 1
End
ELSE BEGIN
Retuen 0
End
End
****************************************
******Just in SQL Server 2000 for now.
AMB
"Sanjay Pais" wrote:

> Is it possible to create Functions in SqlServer 7?
> I have a huge query > 500,000 rows that I want to select a subset of using
a
> function
> Select IdentityInd, ColA, ColB
> From TableA Where
> UDFContains(IdentityInd, ColB ) = 1
> **************************************
> --Function
> And UDFContains will looklike
> UDFContains(@.IdentityInd, @.ColB )
> Returns Bit
> Begin
> IF EXISTS(Select IdentityInd From TableA Where
> IdentityInd = @.IdentityInd AND CONTAINS(ColA,
> @.ColB)) BEGIN
> Return 1
> End
> ELSE BEGIN
> Retuen 0
> End
> End
> ****************************************
******
>
>|||No, but you can do this in the where clause:
Select IdentityInd, ColA, ColB
From TableA
Where EXISTS( Select inExists.IdentityInd
From TableA as inExists
Where IdentityInd = tableA.IdentityInd
AND CONTAINS(inExists.ColA, tableA.ColB))
Can't you? It should be preferrable performancewise anyhow, I would expect.
----
Louis Davidson - drsql@.hotmail.com
SQL Server MVP
Compass Technology Management - www.compass.net
Pro SQL Server 2000 Database Design -
http://www.apress.com/book/bookDisplay.html?bID=266
Note: Please reply to the newsgroups only unless you are interested in
consulting services. All other replies may be ignored :)
"Sanjay Pais" <spaisatnospammarketlinksolutions.com> wrote in message
news:eeK6c$UBFHA.3924@.TK2MSFTNGP10.phx.gbl...
> Is it possible to create Functions in SqlServer 7?
> I have a huge query > 500,000 rows that I want to select a subset of using
> a function
> Select IdentityInd, ColA, ColB
> From TableA Where
> UDFContains(IdentityInd, ColB ) = 1
> **************************************
> --Function
> And UDFContains will looklike
> UDFContains(@.IdentityInd, @.ColB )
> Returns Bit
> Begin
> IF EXISTS(Select IdentityInd From TableA Where
> IdentityInd = @.IdentityInd AND CONTAINS(ColA,
> @.ColB)) BEGIN
> Return 1
> End
> ELSE BEGIN
> Retuen 0
> End
> End
> ****************************************
******
>|||You can't use two columns in a contains clause which caused my dilema in the
first place :)
Sanjay
"Louis Davidson" <dr_dontspamme_sql@.hotmail.com> wrote in message
news:ul5SFhWBFHA.4004@.tk2msftngp13.phx.gbl...
> No, but you can do this in the where clause:
> Select IdentityInd, ColA, ColB
> From TableA
> Where EXISTS( Select inExists.IdentityInd
> From TableA as inExists
> Where IdentityInd = tableA.IdentityInd
> AND CONTAINS(inExists.ColA, tableA.ColB))
> Can't you? It should be preferrable performancewise anyhow, I would
> expect.
> --
> ----
--
> Louis Davidson - drsql@.hotmail.com
> SQL Server MVP
> Compass Technology Management - www.compass.net
> Pro SQL Server 2000 Database Design -
> http://www.apress.com/book/bookDisplay.html?bID=266
> Note: Please reply to the newsgroups only unless you are interested in
> consulting services. All other replies may be ignored :)
> "Sanjay Pais" <spaisatnospammarketlinksolutions.com> wrote in message
> news:eeK6c$UBFHA.3924@.TK2MSFTNGP10.phx.gbl...
>|||Ah, sorry :)
----
Louis Davidson - drsql@.hotmail.com
SQL Server MVP
Compass Technology Management - www.compass.net
Pro SQL Server 2000 Database Design -
http://www.apress.com/book/bookDisplay.html?bID=266
Note: Please reply to the newsgroups only unless you are interested in
consulting services. All other replies may be ignored :)
"Sanjay Pais" <spaisatnospammarketlinksolutions.com> wrote in message
news:uI%23xQvWBFHA.3700@.tk2msftngp13.phx.gbl...
> You can't use two columns in a contains clause which caused my dilema in
> the first place :)
> Sanjay
> "Louis Davidson" <dr_dontspamme_sql@.hotmail.com> wrote in message
> news:ul5SFhWBFHA.4004@.tk2msftngp13.phx.gbl...
>

functions in check constraint

Hi there,
Is it possible to modify a function used in a check constraint, without
having to drop the constraint first?
E.G.
Create function dbo.CheckSampleItemIssueStatus (@.sampleItemIssueId int,
@.StatusId int) Returns bit As
Begin
declare @.RetVal bit
if(@.StatusId = dbo.GetSampleItemIssueStatus(@.sampleItemIssueId))
Set @.RetVal = 1
else
Set @.RetVal = 0
Return @.RetVal
End
go
Alter table dbo.SampleItemIssue Add Constraint
CK_SampleItemIssue_StatusTypeId Check(
dbo.CheckSampleItemIssueStatus(SampleItemIssueId, StatusTypeId) = 1
)
go
Alter function dbo.CheckSampleItemIssueStatus(...
returns an error along the lines of cannot alter function because it is
referenced by constraint..
Thanks.
Fred.You have to drop the constraint first, before you can change the function.
What does the function GetSampleItemIssueStatus do? Because I think you can
solve this with foreign keys or otherwise without having to use functions.
--
Jacco Schalkwijk
SQL Server MVP
"Fred" <Fred@.discussions.microsoft.com> wrote in message
news:5EB25407-CCB1-4D31-A6A8-0AA62DB6D19D@.microsoft.com...
> Hi there,
> Is it possible to modify a function used in a check constraint, without
> having to drop the constraint first?
> E.G.
> Create function dbo.CheckSampleItemIssueStatus (@.sampleItemIssueId int,
> @.StatusId int) Returns bit As
> Begin
> declare @.RetVal bit
> if(@.StatusId = dbo.GetSampleItemIssueStatus(@.sampleItemIssueId))
> Set @.RetVal = 1
> else
> Set @.RetVal = 0
> Return @.RetVal
> End
> go
> Alter table dbo.SampleItemIssue Add Constraint
> CK_SampleItemIssue_StatusTypeId Check(
> dbo.CheckSampleItemIssueStatus(SampleItemIssueId, StatusTypeId) = 1
> )
> go
> Alter function dbo.CheckSampleItemIssueStatus(...
> returns an error along the lines of cannot alter function because it is
> referenced by constraint..
>
> Thanks.
> Fred.
>|||Thanks for the reply,
You confirmed my thoughts, I guess what I'm after is something like
Alter table disable/enable trigger, but for constraints.
That function is just an example and i can't do it via foreign keys,
because the rules governing the value of the statusId are based in part on
records from other tables.
In an other case I also need to check that a number matches the luhn
algorithm.
(http://www.brainyencyclopedia.com/encyclopedia/l/lu/luhn_algorithm.html)
Cheers.
"Jacco Schalkwijk" wrote:
> You have to drop the constraint first, before you can change the function.
> What does the function GetSampleItemIssueStatus do? Because I think you can
> solve this with foreign keys or otherwise without having to use functions.
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "Fred" <Fred@.discussions.microsoft.com> wrote in message
> news:5EB25407-CCB1-4D31-A6A8-0AA62DB6D19D@.microsoft.com...
> > Hi there,
> >
> > Is it possible to modify a function used in a check constraint, without
> > having to drop the constraint first?
> >
> > E.G.
> > Create function dbo.CheckSampleItemIssueStatus (@.sampleItemIssueId int,
> > @.StatusId int) Returns bit As
> > Begin
> > declare @.RetVal bit
> > if(@.StatusId = dbo.GetSampleItemIssueStatus(@.sampleItemIssueId))
> > Set @.RetVal = 1
> > else
> > Set @.RetVal = 0
> > Return @.RetVal
> > End
> > go
> >
> > Alter table dbo.SampleItemIssue Add Constraint
> > CK_SampleItemIssue_StatusTypeId Check(
> > dbo.CheckSampleItemIssueStatus(SampleItemIssueId, StatusTypeId) = 1
> > )
> > go
> >
> > Alter function dbo.CheckSampleItemIssueStatus(...
> >
> > returns an error along the lines of cannot alter function because it is
> > referenced by constraint..
> >
> >
> > Thanks.
> >
> > Fred.
> >
>
>

functions in check constraint

Hi there,
Is it possible to modify a function used in a check constraint, without
having to drop the constraint first?
E.G.
Create function dbo.CheckSampleItemIssueStatus (@.sampleItemIssueId int,
@.StatusId int) Returns bit As
Begin
declare @.RetVal bit
if(@.StatusId = dbo.GetSampleItemIssueStatus(@.sampleItemIssueId))
Set @.RetVal = 1
else
Set @.RetVal = 0
Return @.RetVal
End
go
Alter table dbo.SampleItemIssue Add Constraint
CK_SampleItemIssue_StatusTypeId Check(
dbo.CheckSampleItemIssueStatus(SampleItemIssueId, StatusTypeId) = 1
)
go
Alter function dbo.CheckSampleItemIssueStatus(...
returns an error along the lines of cannot alter function because it is
referenced by constraint..
Thanks.
Fred.
You have to drop the constraint first, before you can change the function.
What does the function GetSampleItemIssueStatus do? Because I think you can
solve this with foreign keys or otherwise without having to use functions.
Jacco Schalkwijk
SQL Server MVP
"Fred" <Fred@.discussions.microsoft.com> wrote in message
news:5EB25407-CCB1-4D31-A6A8-0AA62DB6D19D@.microsoft.com...
> Hi there,
> Is it possible to modify a function used in a check constraint, without
> having to drop the constraint first?
> E.G.
> Create function dbo.CheckSampleItemIssueStatus (@.sampleItemIssueId int,
> @.StatusId int) Returns bit As
> Begin
> declare @.RetVal bit
> if(@.StatusId = dbo.GetSampleItemIssueStatus(@.sampleItemIssueId))
> Set @.RetVal = 1
> else
> Set @.RetVal = 0
> Return @.RetVal
> End
> go
> Alter table dbo.SampleItemIssue Add Constraint
> CK_SampleItemIssue_StatusTypeId Check(
> dbo.CheckSampleItemIssueStatus(SampleItemIssueId, StatusTypeId) = 1
> )
> go
> Alter function dbo.CheckSampleItemIssueStatus(...
> returns an error along the lines of cannot alter function because it is
> referenced by constraint..
>
> Thanks.
> Fred.
>
|||Thanks for the reply,
You confirmed my thoughts, I guess what I'm after is something like
Alter table disable/enable trigger, but for constraints.
That function is just an example and i can't do it via foreign keys,
because the rules governing the value of the statusId are based in part on
records from other tables.
In an other case I also need to check that a number matches the luhn
algorithm.
(http://www.brainyencyclopedia.com/en...algorithm.html)
Cheers.
"Jacco Schalkwijk" wrote:

> You have to drop the constraint first, before you can change the function.
> What does the function GetSampleItemIssueStatus do? Because I think you can
> solve this with foreign keys or otherwise without having to use functions.
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "Fred" <Fred@.discussions.microsoft.com> wrote in message
> news:5EB25407-CCB1-4D31-A6A8-0AA62DB6D19D@.microsoft.com...
>
>

function with a Cellset as parameter?

Hi all,

I want to create a function (Analysis Servieces 2005) that expects a CellSet as Parameter, but I don't understand very good how to pass A Cellset to a function.
I have the next Code that should return my CellSet:

SELECT NON EMPTY{ [Measures].[InvoiceAmount] } ON COLUMNS, NON EMPTY { ([Buyer].[Company].[Company].ALLMEMBERS ) }
DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM
( SELECT ( { [Com Device].[Com Device].["ComDevice"] } ) ON COLUMNS FROM
( SELECT ( { [Invoice].[Period Code].["PeriodeCode"] } ) ON COLUMNS FROM
( SELECT ( { [Seller].[Company].["Seller"] } ) ON COLUMNS FROM [Invoicing])))
WHERE ( [Seller].[Company].[Seller], [Invoice].[Period Code].["PeriodeCode"], [Com Device].[Com Device].["ComDevice"])

Any help is appriciated.

What sort of function do you mean?
Where would you want to call this function from?
What is this function meant to do?

|||

Hi Adam,

It is a .Net function, that should return a string as value.
the select query has 2 filter-values, I want the companies that had the "comdevice" in the given "PeriodeCode".
I am calling the function from reporting services.

Hope this clears it a bit.

thanx
PS.(Is it maby possible to excute the query within the function, It is a function within the db-assemblies?)

|||

Sorry, I'm still not clear as to why you want to do this. Why write a .NET function?

My understanding is that all you want is a parametrized MDX query. Is this correct? If so reporting services supports this.

Can you describe the problem you are trying to solve rather than the solution you are trying to come up with. Maybe there's an easier solution that can accomplish what you need.

|||

Thanx for your reply Adam,

I have the next problem:
I have a table in my report, that uses dataset "Comdevice". In that same table I show some Invoice-values per Comdevice.
What I want to do is show the company that bought this comdevice. I can't do this in the same dataset, because the company that bought the device is on "SALESLEVEL 300" and the invoice-values I show are on "SALESLEVEL 240". My function would be a temporary solution to this problem.
Sorry if the solution is very clear, but I'm very new to Mdx.

thanx in advance

|||I'm not sure what you mean by "SALESLEVEL 300" and "SALESLEVEL 240". To help me understand what you are trying to do could you please mock up the desired report layout in excel and then copy paste it into a post.

Function to return week number

Does anyone now how I can create a SQL function to return a w
number for any date with the following guidlines?
-Ws begin on a Thursday and end on a Wednesday.
-1st January is always in w 1.
-W 1 for this year would go from 30/12/2004 to 05/01/2005
I have been going round in circles trying to crack this one. I would
be very grateful if anyone has any ideas.Mark
Use DateFirst and DatePart
an Example in T-SQL:
Set DateFirst 4
Declare @.D DateTime Set @.D = '20050101'
Select DatePart(wk, @.d)
To make a function:
-- **********************************
Create Functiondbo.WNumber
(@.D DateTime,
@.FDOW TinyInt) -- The day of w yo want to be first Mon = 1; Sunday= 7
Returns TinyInt
As
Begin
Declare @.WkNo TinyInt
Set @.WkNo = (DatePart(dy, @.d ) +
@.FDOW + 4) / 7
Return @.WkNo
End
-- ---
Use it like this:
Select dbo.WNumber('20050106',4)
"Mark Powell" wrote:

> Does anyone now how I can create a SQL function to return a w
> number for any date with the following guidlines?
> -Ws begin on a Thursday and end on a Wednesday.
> -1st January is always in w 1.
> -W 1 for this year would go from 30/12/2004 to 05/01/2005
> I have been going round in circles trying to crack this one. I would
> be very grateful if anyone has any ideas.
>|||The you tried the built-in DATEPART after setting appropriate SET DATEFIRST?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Mark Powell" <mark@.muddyboots.com> wrote in message
news:f10f176.0503300059.5537cf67@.posting.google.com...
> Does anyone now how I can create a SQL function to return a w
> number for any date with the following guidlines?
> -Ws begin on a Thursday and end on a Wednesday.
> -1st January is always in w 1.
> -W 1 for this year would go from 30/12/2004 to 05/01/2005
> I have been going round in circles trying to crack this one. I would
> be very grateful if anyone has any ideas.|||Thanks - that's much better than anything I could come up with. The
only problem with this function is that 30/12/04 and 31/12/04 are
returning w 53 and I want them to return w 1.|||That IS counterintuitive, that the last days of 2004 should be considered as
being in the first w of 2005, But if you want it that way, modify
function so that if value calculates to 53, it returns 1 instead
Alter Functiondbo.WNumber
(@.D DateTime,
@.FDOW TinyInt) -- The day of w yo want to be first Mon = 1; Sunday= 7
Returns TinyInt
As
Begin
Declare @.WkNo TinyInt
Set @.WkNo = (DatePart(dy, @.d ) +
@.FDOW + 4) / 7
Return Case @.WkNo When 53
Then 1 Else @.WkNo End
End
"mark@.muddyboots.com" wrote:

> Thanks - that's much better than anything I could come up with. The
> only problem with this function is that 30/12/04 and 31/12/04 are
> returning w 53 and I want them to return w 1.
>|||It doesn't make sense to me either, exept that it keeps the same w
number across the year end. It is how our client wants it, so it's the
way it needs to be done.
The next problem I have found is that the w number does not return
the correct value if I use a date from 2004 or 2006 (i.e. not 2005). I
imagine the +4 in your procedure should be a variable based on the
year, but I am not sure what it does?
Thanks for your help.
Mark|||Mark,
This was much harder than I thought at first, but this is it... Give it a
shot...
ALTER FUNCTION dbo.WNumber
(@.D DateTime, @.FDOW TinyInt)
Returns TinyInt
As
Begin
Declare @.FDOY Smallint, @.Shft Smallint, @.Yr SmallInt
Set @.Yr = Year(@.D)
Set @.FDOY = DatePart(dw, Str(@.Yr,4,0) + '0101') +
(@.@.DateFirst%7) - 1
Set @.Shft = (@.FDOY - @.FDOW + 7) % 7
Declare @.WkNo TinyInt
Set @.WkNo = (DatePart(dy, @.d) + @.Shft + 6) / 7
-- Now adjust for last partial w
Return Case When @.WkNo < 53 Then @.WkNo
When @.WkNo > 53 Or @.Shft < 5 Then 1
When @.Shft = 6 Then 53
-- Leap Year Consideration
When DatePart(dy, Str(@.Yr,4,0) + '1231') = 365
Then 1
Else 53 End
End
-- ****************************************
***************
And here is the code to test it...
Declare @.Y SmallInt Set @.Y = 2000
Set NoCOunt On
Declare @.D DateTime
Declare @.FDOW TinyInt Set @.FDOW = 4
Declare @.DP TinyInt,@.DF TinyInt,
@.DY SmallInt, @.I TInyInt
Declare @.DTs Table(DT TinyInt)
Set @.I = 0
While @.I < 7 Begin
Set @.I = @.I + 1
Insert @.DTs(DT) Values(@.I)
End
Print 'Year Day Date WNo Day Date WNo '
While @.Y < 2010 Begin
--Set @.D = STR(@.Y, 4,0) + '0101'
Set @.DF = @.@.DateFirst
--Set @.DP = datepart(wday, @.D)
--Set @.DY = datepart(dy, @.D)
Select @.Y,
Left(DateName(dw, Str(@.Y, 4,0) + '01' + Replace(Str(DT, 2,0),' ', '0')),2)
+
' ' + Str(@.Y, 4,0) +'01' + Left(Replace(Str(DT, 2,0),' ', '0'),8) +
' ' +
Cast(dbo.WNumber(Str(@.Y, 4,0) +'01' + Left(Replace(Str(DT, 2,0),'
', '0'),2), 4) as Char(2)),
Left(DateName(dw, Str(@.Y, 4,0) + '12' + Replace(Str(DT+24, 2,0),' ',
'0')),2) +
' ' + Str(@.Y, 4,0) +'12' + Left(Replace(Str(DT+24, 2,0),' ', '0'),8)
+ ' ' +
Cast(dbo.WNumber(Str(@.Y, 4,0) +'12' + Left(Replace(Str(DT+24,
2,0),' ', '0'),2), 4)as Char(2))
From @.DTs
Set @.Y = @.Y + 1
End
"mark@.muddyboots.com" wrote:

> It doesn't make sense to me either, exept that it keeps the same w
> number across the year end. It is how our client wants it, so it's the
> way it needs to be done.
> The next problem I have found is that the w number does not return
> the correct value if I use a date from 2004 or 2006 (i.e. not 2005). I
> imagine the +4 in your procedure should be a variable based on the
> year, but I am not sure what it does?
> Thanks for your help.
> Mark
>|||Sorry , error in test script...
Use the following to test UDF In Prev Post
-- ****************************************
********
Set NoCount On
Declare @.D DateTime
Declare @.FDOW TinyInt Set @.FDOW = 2
Declare @.Y SmallInt Set @.Y = 2000
-- --
Declare @.I TInyInt Set @.I = 0
Declare @.DTs Table(DT TinyInt)
While @.I < 7 Begin
Set @.I = @.I + 1
Insert @.DTs(DT) Values(@.I)
End
-- ---
Print 'Year Day Date WNo Day Date WNo '
While @.Y < 2010 Begin
Select @.Y,
Left(DateName(dw, Str(@.Y, 4,0) + '01' +
Replace(Str(DT, 2,0),' ', '0')),2) +
' ' + Str(@.Y, 4,0) +'01' +
Left(Replace(Str(DT, 2,0),' ', '0'),8) + ' ' +
Cast(dbo.WNumber(Str(@.Y, 4,0) +'01' +
Left(Replace(Str(DT, 2,0),' ', '0'),2), @.FDOW) as Char(2)),
Left(DateName(dw, Str(@.Y, 4,0) + '12' +
Replace(Str(DT+24, 2,0),' ', '0')),2) +
' ' + Str(@.Y, 4,0) +'12' +
Left(Replace(Str(DT+24, 2,0),' ', '0'),8) + ' ' +
Cast(dbo.WNumber(Str(@.Y, 4,0) +'12' +
Left(Replace(Str(DT+24, 2,0),' ', '0'),2), @.FDOW)as Char(2))
From @.DTs
Set @.Y = @.Y + 1
End
-- ****************************************
********
"mark@.muddyboots.com" wrote:

> It doesn't make sense to me either, exept that it keeps the same w
> number across the year end. It is how our client wants it, so it's the
> way it needs to be done.
> The next problem I have found is that the w number does not return
> the correct value if I use a date from 2004 or 2006 (i.e. not 2005). I
> imagine the +4 in your procedure should be a variable based on the
> year, but I am not sure what it does?
> Thanks for your help.
> Mark
>

function to create table

Hello,

how can I create table with pl/sql? I want to create function do this but error occured.
I used this for create function
CREATE OR REPLACE FUNCTION CREATE_OBJ
RETURN VARCHAR
IS
num NUMBER;
BEGIN
CREATE TABLE WST.test (
id NUMBER
);
commit;

END;
/

Any idea?

OndrejYou can't perform DDL (like CREATE TABLE) directly from within PL/SQL. You would have to use dynamic SQL:

CREATE OR REPLACE FUNCTION CREATE_OBJ
RETURN VARCHAR
IS
BEGIN
EXECUTE IMMEDIATE 'CREATE TABLE WST.test (id NUMBER)';
RETURN 'something';
END;
/

There is no need to COMMIT the DDL, that happens automatically. And a function has to RETURN something.|||Well, I try you write... compilation is ok but when I use the function in sql I get error:
select CREATE_OBJ from dual;

ORA-14552 cannot perform a DDL, commit or rollback inside a query or DML
ORA-06512: "WST.CREATE_OBJ_1", line 3

Originally posted by andrewst
You can't perform DDL (like CREATE TABLE) directly from within PL/SQL. You would have to use dynamic SQL:

CREATE OR REPLACE FUNCTION CREATE_OBJ
RETURN VARCHAR
IS
BEGIN
EXECUTE IMMEDIATE 'CREATE TABLE WST.test (id NUMBER)';
RETURN 'something';
END;
/

There is no need to COMMIT the DDL, that happens automatically. And a function has to RETURN something.|||That's correct, as the error says, you can't perform DDL (like CREATE TABLE) inside a query (like SELECT).

But you can do this:

SQL> VAR x VARCHAR2(100)
SQL> EXEC :x := CREATE_OBJ;

But really, I wouldn't create a FUNCTION to perform DDL like CREATE TABLE, I would just use a SQL Plus script.|||Great!!

It works fine after setting all needed privileges... thank you very much.

But I have another question. I want modified function CREATE_OBJ, I need give table name like parameter. So the function should be like this

CREATE OR REPLACE FUNCTION CREATE_OBJ (tName VARCHAR) RETURN VARCHAR IS
BEGIN
EXECUTE IMMEDIATE 'CREATE TABLE '+tName+' (id NUMBER)';
RETURN 'something';
END;

I'm sure this is not correct syntax.

Originally posted by andrewst
That's correct, as the error says, you can't perform DDL (like CREATE TABLE) inside a query (like SELECT).

But you can do this:

SQL> VAR x VARCHAR2(100)
SQL> EXEC :x := CREATE_OBJ;

But really, I wouldn't create a FUNCTION to perform DDL like CREATE TABLE, I would just use a SQL Plus script.|||Very nearly: just use || instead of +|||Yeaaa, that's it!!!

Thank you very much...

Ondrej

Originally posted by andrewst
Very nearly: just use || instead of +

Function to create comma separated list from any given column/table.

Hi,

I'm sure this is a common problem.. to create a single field from a
whole column, where each row would be separated by a comma.

I can do this for a specified table, and column.. and I've created a
function using VBA to achieve a more dynamic (and very slow) solution..
so I would like to implement it using a user defined function in sql server.

The problems I'm facing are, that I can't use dynamic sql in a
function.. and I also can't use temporary tables which could build up a
'standard' table from parameters given to then perform the function on.

So, with these limitations, what other options do I have?

Cheers,
ChrisDynamic SQL is so so passe'

Firstly it is a security risk , as you can't lock down rights to procedures
that use it.

secondly it is inefficient as it has to be re-generated and compiled each
time you use it

Code Generation is the all the rage now.

And how often will you be adding new code tables to do this with?

Here's how to roll your own code generator in SQL:

in Query Analyzer (aka ISQL)

write a template code block to get it to work (I've done this below)

wrap that in print statements.

create a cursor to loop thru sysobjects and/or syscolumns (or the SCHEMA
Views) to locate all your tables with the columns you need.

loop thru and generate all the script you need.

you can create a separate function/procedure for each or create a single one
with a large case in it (your call).

run the output to create the function(s).

save the script that generates the stuff and hold on to it.

every time a change is made to a code table (i.e. add new table, change
column names, etc..) rerun the script to regenerated (make sure there is a
drop in there somewhere as well)

Tally Ho! - (which is a liquor store her in Delaware, and not a Fox hunting
term, which I understand is now outlawed in the UK).

------------------
-- declare an empty string (not null)
declare @.list varchar 4000
set @.list = ''

-- select multiple rows into a single variable
select @.list = @.list + ',' + ColumnName from TableName

-- get rid of the last comma
select @.list = substring( @.list , 1, len( @.list ) -1 )
------------------

"Not Me" <Noone.is.home@.here.com> wrote in message
news:cl87ou$fam$1@.ucsnew1.ncl.ac.uk...
> Hi,
> I'm sure this is a common problem.. to create a single field from a whole
> column, where each row would be separated by a comma.
> I can do this for a specified table, and column.. and I've created a
> function using VBA to achieve a more dynamic (and very slow) solution.. so
> I would like to implement it using a user defined function in sql server.
> The problems I'm facing are, that I can't use dynamic sql in a function..
> and I also can't use temporary tables which could build up a 'standard'
> table from parameters given to then perform the function on.
> So, with these limitations, what other options do I have?
> Cheers,
> Chris

Function that returns a table

I have a function that returns a single row table with two columns:

dbo.Fun1(@.param1) : colA and colB

I tried to create a stored procedure that use this function:

select col1, col2, dbo.Fun1(col1) from table1

The result is : Invalid object name 'dbo.Fun1'

There is no join between table1 and Fun1, how can I select the both columns of Fun1 ?

Thanks in advance.

Long

Can u paste the declaration of the function?|||You are trying to use a table-valued UDF like a scalar UDF which is incorrect. You can use a table-valued function only in the FROM clause or as a table source. In any case, what you are trying to do is not possible in SQL Server 2000 since you can only pass variables or constants as parameters to table-valued UDFs. In SQL Server 2005, you can use the APPLY operator to the same.|||

Thanks, Umachandar,

I have to do the selection like this:

select col1, col2, (select colA from dbo.Fun1(col1) ), ( select colB from dbo.Fun1(col1))

from table1

It works, but I'm not satisfied, as it calculates the function twice.

Any other ideas?

Thanks in advance.

Long

|||

Hi,
due to the fact that you have to execute the statement once per row, there is no way to do it ohter than your mentioned way.

Without knowing your Function I would assume that even this is very wacky, because your Return could return more than one value ?! So you have to make sure from your query / ir function that only one row will be returned.

HTH, Jens Suessmeyer.

|||

I don't see how this will work in SQL2000. If you are on SQL2005 then you can simplify the query by using APPLY operator like:

select t.col1, t.col2, f.colA, f.colB

from table1 as t

cross apply dbo.Fun1(t.col1) as f

Function Syntax

Below is the function that I am trying to create. I am failing in trying to
select from a random table. So I pass the table name in the function but it
won't let me select from that. Can someone help?
CREATE FUNCTION dbo.GetZoneID
(
@.ID int,
@.tableName varchar(50)
)
RETURNS money AS
BEGIN
DECLARE @.MasterZoneID INT,
@.Flag INT,
@.ZoneID money,
@.TempZoneID money,
@.TempTableName varchar(50)
SET @.Flag = 0
SET @.MasterZoneID = (Select cast(ZoneID as int) from System)
SET @.TempZoneID = (@.MasterZoneID * 1000000000) + @.ID
WHILE(@.Flag = 0)
BEGIN
--Failing on the @.tableName--
SET @.ZoneID = (Select ZoneID from @.tableName where ZoneID = @.TempZoneID)
--Failing there--
IF(@.ZoneID = 0)
BEGIN
RETURN (@.MasterZoneID * 1000000000) + @.ID
END
ELSE
BEGIN
SET @.TempZoneID = @.TempZoneID + 0.0001
END
END
RETURN (@.MasterZoneID * 1000000000) + @.ID
ENDYou can't do this, sorry. The only way to "pass in" a table name is by
using dynamic SQL, which is not allowed in a UDF. What do you need this
for? Why don't you know the table name ahead of time?
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Sean McKaharay" <sean@.classactweb.com> wrote in message
news:Oy540EMJFHA.2936@.TK2MSFTNGP15.phx.gbl...
> Below is the function that I am trying to create. I am failing in trying
to
> select from a random table. So I pass the table name in the function but
it
> won't let me select from that. Can someone help?
>
> CREATE FUNCTION dbo.GetZoneID
> (
> @.ID int,
> @.tableName varchar(50)
> )
> RETURNS money AS
> BEGIN
> DECLARE @.MasterZoneID INT,
> @.Flag INT,
> @.ZoneID money,
> @.TempZoneID money,
> @.TempTableName varchar(50)
> SET @.Flag = 0
> SET @.MasterZoneID = (Select cast(ZoneID as int) from System)
> SET @.TempZoneID = (@.MasterZoneID * 1000000000) + @.ID
>
> WHILE(@.Flag = 0)
> BEGIN
> --Failing on the @.tableName--
> SET @.ZoneID = (Select ZoneID from @.tableName where ZoneID = @.TempZoneID)
> --Failing there--
> IF(@.ZoneID = 0)
> BEGIN
> RETURN (@.MasterZoneID * 1000000000) + @.ID
> END
> ELSE
> BEGIN
> SET @.TempZoneID = @.TempZoneID + 0.0001
> END
> END
> RETURN (@.MasterZoneID * 1000000000) + @.ID
> END
>
>|||The reason why it fails is because your running dynamic code.
Have a look at the following
Declare @.SQL as varchar(100)
set @.SQL = 'Select ZoneID from + ' + @.tableName + ' where ZoneID = ' +
@.TempZoneID
nb if any of these are ints then convert them to varchars
EXEC sp_executesql @.sql, N'@.ZoneID int OUTPUT', @.ZoneID OUTPUT
The sp_executesql will execute the sql then return the value into @.ZoneID.
If you are trying to random values why don't you do
declare @.Random int
set @.Random= rand() * 1000000
instead ?
Peter
"Information is the oxygen of the modern age. It seeps through the walls
topped by barbed wire, it wafts across the electrified borders.""
Ronald Reagan
"Sean McKaharay" wrote:
> Below is the function that I am trying to create. I am failing in trying to
> select from a random table. So I pass the table name in the function but it
> won't let me select from that. Can someone help?
>
> CREATE FUNCTION dbo.GetZoneID
> (
> @.ID int,
> @.tableName varchar(50)
> )
> RETURNS money AS
> BEGIN
> DECLARE @.MasterZoneID INT,
> @.Flag INT,
> @.ZoneID money,
> @.TempZoneID money,
> @.TempTableName varchar(50)
> SET @.Flag = 0
> SET @.MasterZoneID = (Select cast(ZoneID as int) from System)
> SET @.TempZoneID = (@.MasterZoneID * 1000000000) + @.ID
>
> WHILE(@.Flag = 0)
> BEGIN
> --Failing on the @.tableName--
> SET @.ZoneID = (Select ZoneID from @.tableName where ZoneID = @.TempZoneID)
> --Failing there--
> IF(@.ZoneID = 0)
> BEGIN
> RETURN (@.MasterZoneID * 1000000000) + @.ID
> END
> ELSE
> BEGIN
> SET @.TempZoneID = @.TempZoneID + 0.0001
> END
> END
> RETURN (@.MasterZoneID * 1000000000) + @.ID
> END
>
>
>|||If it's a temp table, it's unique per user. So why can't you hard-code that
name in the function? See http://www.aspfaq.com/2248 for an example.
--
http://www.aspfaq.com/
(Reverse address to reply.)
"Sean McKaharay" <sean@.classactweb.com> wrote in message
news:Oy540EMJFHA.2936@.TK2MSFTNGP15.phx.gbl...
> Below is the function that I am trying to create. I am failing in trying
to
> select from a random table. So I pass the table name in the function but
it
> won't let me select from that. Can someone help?
>
> CREATE FUNCTION dbo.GetZoneID
> (
> @.ID int,
> @.tableName varchar(50)
> )
> RETURNS money AS
> BEGIN
> DECLARE @.MasterZoneID INT,
> @.Flag INT,
> @.ZoneID money,
> @.TempZoneID money,
> @.TempTableName varchar(50)
> SET @.Flag = 0
> SET @.MasterZoneID = (Select cast(ZoneID as int) from System)
> SET @.TempZoneID = (@.MasterZoneID * 1000000000) + @.ID
>
> WHILE(@.Flag = 0)
> BEGIN
> --Failing on the @.tableName--
> SET @.ZoneID = (Select ZoneID from @.tableName where ZoneID = @.TempZoneID)
> --Failing there--
> IF(@.ZoneID = 0)
> BEGIN
> RETURN (@.MasterZoneID * 1000000000) + @.ID
> END
> ELSE
> BEGIN
> SET @.TempZoneID = @.TempZoneID + 0.0001
> END
> END
> RETURN (@.MasterZoneID * 1000000000) + @.ID
> END
>
>|||Sorry, my bad, I forgot that example uses a procedure, not a function... I
was completely thinking of a different kind of problem.
--
http://www.aspfaq.com/
(Reverse address to reply.)
"Sean McKaharay" <sean@.classactweb.com> wrote in message
news:Oy540EMJFHA.2936@.TK2MSFTNGP15.phx.gbl...
> Below is the function that I am trying to create. I am failing in trying
to
> select from a random table. So I pass the table name in the function but
it
> won't let me select from that. Can someone help?
>
> CREATE FUNCTION dbo.GetZoneID
> (
> @.ID int,
> @.tableName varchar(50)
> )
> RETURNS money AS
> BEGIN
> DECLARE @.MasterZoneID INT,
> @.Flag INT,
> @.ZoneID money,
> @.TempZoneID money,
> @.TempTableName varchar(50)
> SET @.Flag = 0
> SET @.MasterZoneID = (Select cast(ZoneID as int) from System)
> SET @.TempZoneID = (@.MasterZoneID * 1000000000) + @.ID
>
> WHILE(@.Flag = 0)
> BEGIN
> --Failing on the @.tableName--
> SET @.ZoneID = (Select ZoneID from @.tableName where ZoneID = @.TempZoneID)
> --Failing there--
> IF(@.ZoneID = 0)
> BEGIN
> RETURN (@.MasterZoneID * 1000000000) + @.ID
> END
> ELSE
> BEGIN
> SET @.TempZoneID = @.TempZoneID + 0.0001
> END
> END
> RETURN (@.MasterZoneID * 1000000000) + @.ID
> END
>
>|||Sorry forgot you can't do a rand() in function duh
"Peter 'Not Peter The Spate' Nolan" wrote:
> The reason why it fails is because your running dynamic code.
> Have a look at the following
> Declare @.SQL as varchar(100)
> set @.SQL = 'Select ZoneID from + ' + @.tableName + ' where ZoneID = ' +
> @.TempZoneID
> nb if any of these are ints then convert them to varchars
> EXEC sp_executesql @.sql, N'@.ZoneID int OUTPUT', @.ZoneID OUTPUT
> The sp_executesql will execute the sql then return the value into @.ZoneID.
> If you are trying to random values why don't you do
> declare @.Random int
> set @.Random= rand() * 1000000
> instead ?
> Peter
> "Information is the oxygen of the modern age. It seeps through the walls
> topped by barbed wire, it wafts across the electrified borders.""
> Ronald Reagan
>
> "Sean McKaharay" wrote:
> > Below is the function that I am trying to create. I am failing in trying to
> > select from a random table. So I pass the table name in the function but it
> > won't let me select from that. Can someone help?
> >
> >
> > CREATE FUNCTION dbo.GetZoneID
> > (
> > @.ID int,
> > @.tableName varchar(50)
> > )
> > RETURNS money AS
> > BEGIN
> > DECLARE @.MasterZoneID INT,
> > @.Flag INT,
> > @.ZoneID money,
> > @.TempZoneID money,
> > @.TempTableName varchar(50)
> >
> > SET @.Flag = 0
> > SET @.MasterZoneID = (Select cast(ZoneID as int) from System)
> > SET @.TempZoneID = (@.MasterZoneID * 1000000000) + @.ID
> >
> >
> > WHILE(@.Flag = 0)
> > BEGIN
> > --Failing on the @.tableName--
> > SET @.ZoneID = (Select ZoneID from @.tableName where ZoneID = @.TempZoneID)
> > --Failing there--
> > IF(@.ZoneID = 0)
> > BEGIN
> > RETURN (@.MasterZoneID * 1000000000) + @.ID
> > END
> > ELSE
> > BEGIN
> > SET @.TempZoneID = @.TempZoneID + 0.0001
> > END
> > END
> >
> > RETURN (@.MasterZoneID * 1000000000) + @.ID
> >
> > END
> >
> >
> >
> >
> >|||..or dynamic code, bad post or what ;(
"Sean McKaharay" wrote:
> Below is the function that I am trying to create. I am failing in trying to
> select from a random table. So I pass the table name in the function but it
> won't let me select from that. Can someone help?
>
> CREATE FUNCTION dbo.GetZoneID
> (
> @.ID int,
> @.tableName varchar(50)
> )
> RETURNS money AS
> BEGIN
> DECLARE @.MasterZoneID INT,
> @.Flag INT,
> @.ZoneID money,
> @.TempZoneID money,
> @.TempTableName varchar(50)
> SET @.Flag = 0
> SET @.MasterZoneID = (Select cast(ZoneID as int) from System)
> SET @.TempZoneID = (@.MasterZoneID * 1000000000) + @.ID
>
> WHILE(@.Flag = 0)
> BEGIN
> --Failing on the @.tableName--
> SET @.ZoneID = (Select ZoneID from @.tableName where ZoneID = @.TempZoneID)
> --Failing there--
> IF(@.ZoneID = 0)
> BEGIN
> RETURN (@.MasterZoneID * 1000000000) + @.ID
> END
> ELSE
> BEGIN
> SET @.TempZoneID = @.TempZoneID + 0.0001
> END
> END
> RETURN (@.MasterZoneID * 1000000000) + @.ID
> END
>
>
>

Function Syntax

Below is the function that I am trying to create. I am failing in trying to
select from a random table. So I pass the table name in the function but it
won't let me select from that. Can someone help?
CREATE FUNCTION dbo.GetZoneID
(
@.ID int,
@.tableName varchar(50)
)
RETURNS money AS
BEGIN
DECLARE @.MasterZoneID INT,
@.Flag INT,
@.ZoneID money,
@.TempZoneID money,
@.TempTableName varchar(50)
SET @.Flag = 0
SET @.MasterZoneID = (Select cast(ZoneID as int) from System)
SET @.TempZoneID = (@.MasterZoneID * 1000000000) + @.ID
WHILE(@.Flag = 0)
BEGIN
--Failing on the @.tableName--
SET @.ZoneID = (Select ZoneID from @.tableName where ZoneID = @.TempZoneID)
--Failing there--
IF(@.ZoneID = 0)
BEGIN
RETURN (@.MasterZoneID * 1000000000) + @.ID
END
ELSE
BEGIN
SET @.TempZoneID = @.TempZoneID + 0.0001
END
END
RETURN (@.MasterZoneID * 1000000000) + @.ID
ENDYou can't do this, sorry. The only way to "pass in" a table name is by
using dynamic SQL, which is not allowed in a UDF. What do you need this
for? Why don't you know the table name ahead of time?
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Sean McKaharay" <sean@.classactweb.com> wrote in message
news:Oy540EMJFHA.2936@.TK2MSFTNGP15.phx.gbl...
> Below is the function that I am trying to create. I am failing in trying
to
> select from a random table. So I pass the table name in the function but
it
> won't let me select from that. Can someone help?
>
> CREATE FUNCTION dbo.GetZoneID
> (
> @.ID int,
> @.tableName varchar(50)
> )
> RETURNS money AS
> BEGIN
> DECLARE @.MasterZoneID INT,
> @.Flag INT,
> @.ZoneID money,
> @.TempZoneID money,
> @.TempTableName varchar(50)
> SET @.Flag = 0
> SET @.MasterZoneID = (Select cast(ZoneID as int) from System)
> SET @.TempZoneID = (@.MasterZoneID * 1000000000) + @.ID
>
> WHILE(@.Flag = 0)
> BEGIN
> --Failing on the @.tableName--
> SET @.ZoneID = (Select ZoneID from @.tableName where ZoneID = @.TempZoneID)
> --Failing there--
> IF(@.ZoneID = 0)
> BEGIN
> RETURN (@.MasterZoneID * 1000000000) + @.ID
> END
> ELSE
> BEGIN
> SET @.TempZoneID = @.TempZoneID + 0.0001
> END
> END
> RETURN (@.MasterZoneID * 1000000000) + @.ID
> END
>
>|||The reason why it fails is because your running dynamic code.
Have a look at the following
Declare @.SQL as varchar(100)
set @.SQL = 'Select ZoneID from + ' + @.tableName + ' where ZoneID = ' +
@.TempZoneID
nb if any of these are ints then convert them to varchars
EXEC sp_executesql @.sql, N'@.ZoneID int OUTPUT', @.ZoneID OUTPUT
The sp_executesql will execute the sql then return the value into @.ZoneID.
If you are trying to random values why don't you do
declare @.Random int
set @.Random= rand() * 1000000
instead ?
Peter
"Information is the oxygen of the modern age. It seeps through the walls
topped by barbed wire, it wafts across the electrified borders.""
Ronald Reagan
"Sean McKaharay" wrote:

> Below is the function that I am trying to create. I am failing in trying t
o
> select from a random table. So I pass the table name in the function but i
t
> won't let me select from that. Can someone help?
>
> CREATE FUNCTION dbo.GetZoneID
> (
> @.ID int,
> @.tableName varchar(50)
> )
> RETURNS money AS
> BEGIN
> DECLARE @.MasterZoneID INT,
> @.Flag INT,
> @.ZoneID money,
> @.TempZoneID money,
> @.TempTableName varchar(50)
> SET @.Flag = 0
> SET @.MasterZoneID = (Select cast(ZoneID as int) from System)
> SET @.TempZoneID = (@.MasterZoneID * 1000000000) + @.ID
>
> WHILE(@.Flag = 0)
> BEGIN
> --Failing on the @.tableName--
> SET @.ZoneID = (Select ZoneID from @.tableName where ZoneID = @.TempZoneID)
> --Failing there--
> IF(@.ZoneID = 0)
> BEGIN
> RETURN (@.MasterZoneID * 1000000000) + @.ID
> END
> ELSE
> BEGIN
> SET @.TempZoneID = @.TempZoneID + 0.0001
> END
> END
> RETURN (@.MasterZoneID * 1000000000) + @.ID
> END
>
>
>|||If it's a temp table, it's unique per user. So why can't you hard-code that
name in the function? See http://www.aspfaq.com/2248 for an example.
http://www.aspfaq.com/
(Reverse address to reply.)
"Sean McKaharay" <sean@.classactweb.com> wrote in message
news:Oy540EMJFHA.2936@.TK2MSFTNGP15.phx.gbl...
> Below is the function that I am trying to create. I am failing in trying
to
> select from a random table. So I pass the table name in the function but
it
> won't let me select from that. Can someone help?
>
> CREATE FUNCTION dbo.GetZoneID
> (
> @.ID int,
> @.tableName varchar(50)
> )
> RETURNS money AS
> BEGIN
> DECLARE @.MasterZoneID INT,
> @.Flag INT,
> @.ZoneID money,
> @.TempZoneID money,
> @.TempTableName varchar(50)
> SET @.Flag = 0
> SET @.MasterZoneID = (Select cast(ZoneID as int) from System)
> SET @.TempZoneID = (@.MasterZoneID * 1000000000) + @.ID
>
> WHILE(@.Flag = 0)
> BEGIN
> --Failing on the @.tableName--
> SET @.ZoneID = (Select ZoneID from @.tableName where ZoneID = @.TempZoneID)
> --Failing there--
> IF(@.ZoneID = 0)
> BEGIN
> RETURN (@.MasterZoneID * 1000000000) + @.ID
> END
> ELSE
> BEGIN
> SET @.TempZoneID = @.TempZoneID + 0.0001
> END
> END
> RETURN (@.MasterZoneID * 1000000000) + @.ID
> END
>
>|||Sorry, my bad, I forgot that example uses a procedure, not a function... I
was completely thinking of a different kind of problem.
http://www.aspfaq.com/
(Reverse address to reply.)
"Sean McKaharay" <sean@.classactweb.com> wrote in message
news:Oy540EMJFHA.2936@.TK2MSFTNGP15.phx.gbl...
> Below is the function that I am trying to create. I am failing in trying
to
> select from a random table. So I pass the table name in the function but
it
> won't let me select from that. Can someone help?
>
> CREATE FUNCTION dbo.GetZoneID
> (
> @.ID int,
> @.tableName varchar(50)
> )
> RETURNS money AS
> BEGIN
> DECLARE @.MasterZoneID INT,
> @.Flag INT,
> @.ZoneID money,
> @.TempZoneID money,
> @.TempTableName varchar(50)
> SET @.Flag = 0
> SET @.MasterZoneID = (Select cast(ZoneID as int) from System)
> SET @.TempZoneID = (@.MasterZoneID * 1000000000) + @.ID
>
> WHILE(@.Flag = 0)
> BEGIN
> --Failing on the @.tableName--
> SET @.ZoneID = (Select ZoneID from @.tableName where ZoneID = @.TempZoneID)
> --Failing there--
> IF(@.ZoneID = 0)
> BEGIN
> RETURN (@.MasterZoneID * 1000000000) + @.ID
> END
> ELSE
> BEGIN
> SET @.TempZoneID = @.TempZoneID + 0.0001
> END
> END
> RETURN (@.MasterZoneID * 1000000000) + @.ID
> END
>
>|||Sorry forgot you can't do a rand() in function duh
"Peter 'Not Peter The Spate' Nolan" wrote:
[vbcol=seagreen]
> The reason why it fails is because your running dynamic code.
> Have a look at the following
> Declare @.SQL as varchar(100)
> set @.SQL = 'Select ZoneID from + ' + @.tableName + ' where ZoneID = ' +
> @.TempZoneID
> nb if any of these are ints then convert them to varchars
> EXEC sp_executesql @.sql, N'@.ZoneID int OUTPUT', @.ZoneID OUTPUT
> The sp_executesql will execute the sql then return the value into @.ZoneID
.
> If you are trying to random values why don't you do
> declare @.Random int
> set @.Random= rand() * 1000000
> instead ?
> Peter
> "Information is the oxygen of the modern age. It seeps through the walls
> topped by barbed wire, it wafts across the electrified borders.""
> Ronald Reagan
>
> "Sean McKaharay" wrote:
>|||..or dynamic code, bad post or what ;(
"Sean McKaharay" wrote:

> Below is the function that I am trying to create. I am failing in trying t
o
> select from a random table. So I pass the table name in the function but i
t
> won't let me select from that. Can someone help?
>
> CREATE FUNCTION dbo.GetZoneID
> (
> @.ID int,
> @.tableName varchar(50)
> )
> RETURNS money AS
> BEGIN
> DECLARE @.MasterZoneID INT,
> @.Flag INT,
> @.ZoneID money,
> @.TempZoneID money,
> @.TempTableName varchar(50)
> SET @.Flag = 0
> SET @.MasterZoneID = (Select cast(ZoneID as int) from System)
> SET @.TempZoneID = (@.MasterZoneID * 1000000000) + @.ID
>
> WHILE(@.Flag = 0)
> BEGIN
> --Failing on the @.tableName--
> SET @.ZoneID = (Select ZoneID from @.tableName where ZoneID = @.TempZoneID)
> --Failing there--
> IF(@.ZoneID = 0)
> BEGIN
> RETURN (@.MasterZoneID * 1000000000) + @.ID
> END
> ELSE
> BEGIN
> SET @.TempZoneID = @.TempZoneID + 0.0001
> END
> END
> RETURN (@.MasterZoneID * 1000000000) + @.ID
> END
>
>
>

Function Syntax

Below is the function that I am trying to create. I am failing in trying to
select from a random table. So I pass the table name in the function but it
won't let me select from that. Can someone help?
CREATE FUNCTION dbo.GetZoneID
(
@.ID int,
@.tableName varchar(50)
)
RETURNS money AS
BEGIN
DECLARE @.MasterZoneID INT,
@.Flag INT,
@.ZoneID money,
@.TempZoneID money,
@.TempTableName varchar(50)
SET @.Flag = 0
SET @.MasterZoneID = (Select cast(ZoneID as int) from System)
SET @.TempZoneID = (@.MasterZoneID * 1000000000) + @.ID
WHILE(@.Flag = 0)
BEGIN
--Failing on the @.tableName--
SET @.ZoneID = (Select ZoneID from @.tableName where ZoneID = @.TempZoneID)
--Failing there--
IF(@.ZoneID = 0)
BEGIN
RETURN (@.MasterZoneID * 1000000000) + @.ID
END
ELSE
BEGIN
SET @.TempZoneID = @.TempZoneID + 0.0001
END
END
RETURN (@.MasterZoneID * 1000000000) + @.ID
END
You can't do this, sorry. The only way to "pass in" a table name is by
using dynamic SQL, which is not allowed in a UDF. What do you need this
for? Why don't you know the table name ahead of time?
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
"Sean McKaharay" <sean@.classactweb.com> wrote in message
news:Oy540EMJFHA.2936@.TK2MSFTNGP15.phx.gbl...
> Below is the function that I am trying to create. I am failing in trying
to
> select from a random table. So I pass the table name in the function but
it
> won't let me select from that. Can someone help?
>
> CREATE FUNCTION dbo.GetZoneID
> (
> @.ID int,
> @.tableName varchar(50)
> )
> RETURNS money AS
> BEGIN
> DECLARE @.MasterZoneID INT,
> @.Flag INT,
> @.ZoneID money,
> @.TempZoneID money,
> @.TempTableName varchar(50)
> SET @.Flag = 0
> SET @.MasterZoneID = (Select cast(ZoneID as int) from System)
> SET @.TempZoneID = (@.MasterZoneID * 1000000000) + @.ID
>
> WHILE(@.Flag = 0)
> BEGIN
> --Failing on the @.tableName--
> SET @.ZoneID = (Select ZoneID from @.tableName where ZoneID = @.TempZoneID)
> --Failing there--
> IF(@.ZoneID = 0)
> BEGIN
> RETURN (@.MasterZoneID * 1000000000) + @.ID
> END
> ELSE
> BEGIN
> SET @.TempZoneID = @.TempZoneID + 0.0001
> END
> END
> RETURN (@.MasterZoneID * 1000000000) + @.ID
> END
>
>
|||The reason why it fails is because your running dynamic code.
Have a look at the following
Declare @.SQL as varchar(100)
set @.SQL = 'Select ZoneID from + ' + @.tableName + ' where ZoneID = ' +
@.TempZoneID
nb if any of these are ints then convert them to varchars
EXEC sp_executesql @.sql, N'@.ZoneID int OUTPUT', @.ZoneID OUTPUT
The sp_executesql will execute the sql then return the value into @.ZoneID.
If you are trying to random values why don't you do
declare @.Random int
set @.Random= rand() * 1000000
instead ?
Peter
"Information is the oxygen of the modern age. It seeps through the walls
topped by barbed wire, it wafts across the electrified borders.""
Ronald Reagan
"Sean McKaharay" wrote:

> Below is the function that I am trying to create. I am failing in trying to
> select from a random table. So I pass the table name in the function but it
> won't let me select from that. Can someone help?
>
> CREATE FUNCTION dbo.GetZoneID
> (
> @.ID int,
> @.tableName varchar(50)
> )
> RETURNS money AS
> BEGIN
> DECLARE @.MasterZoneID INT,
> @.Flag INT,
> @.ZoneID money,
> @.TempZoneID money,
> @.TempTableName varchar(50)
> SET @.Flag = 0
> SET @.MasterZoneID = (Select cast(ZoneID as int) from System)
> SET @.TempZoneID = (@.MasterZoneID * 1000000000) + @.ID
>
> WHILE(@.Flag = 0)
> BEGIN
> --Failing on the @.tableName--
> SET @.ZoneID = (Select ZoneID from @.tableName where ZoneID = @.TempZoneID)
> --Failing there--
> IF(@.ZoneID = 0)
> BEGIN
> RETURN (@.MasterZoneID * 1000000000) + @.ID
> END
> ELSE
> BEGIN
> SET @.TempZoneID = @.TempZoneID + 0.0001
> END
> END
> RETURN (@.MasterZoneID * 1000000000) + @.ID
> END
>
>
>
|||If it's a temp table, it's unique per user. So why can't you hard-code that
name in the function? See http://www.aspfaq.com/2248 for an example.
http://www.aspfaq.com/
(Reverse address to reply.)
"Sean McKaharay" <sean@.classactweb.com> wrote in message
news:Oy540EMJFHA.2936@.TK2MSFTNGP15.phx.gbl...
> Below is the function that I am trying to create. I am failing in trying
to
> select from a random table. So I pass the table name in the function but
it
> won't let me select from that. Can someone help?
>
> CREATE FUNCTION dbo.GetZoneID
> (
> @.ID int,
> @.tableName varchar(50)
> )
> RETURNS money AS
> BEGIN
> DECLARE @.MasterZoneID INT,
> @.Flag INT,
> @.ZoneID money,
> @.TempZoneID money,
> @.TempTableName varchar(50)
> SET @.Flag = 0
> SET @.MasterZoneID = (Select cast(ZoneID as int) from System)
> SET @.TempZoneID = (@.MasterZoneID * 1000000000) + @.ID
>
> WHILE(@.Flag = 0)
> BEGIN
> --Failing on the @.tableName--
> SET @.ZoneID = (Select ZoneID from @.tableName where ZoneID = @.TempZoneID)
> --Failing there--
> IF(@.ZoneID = 0)
> BEGIN
> RETURN (@.MasterZoneID * 1000000000) + @.ID
> END
> ELSE
> BEGIN
> SET @.TempZoneID = @.TempZoneID + 0.0001
> END
> END
> RETURN (@.MasterZoneID * 1000000000) + @.ID
> END
>
>
|||Sorry, my bad, I forgot that example uses a procedure, not a function... I
was completely thinking of a different kind of problem.
http://www.aspfaq.com/
(Reverse address to reply.)
"Sean McKaharay" <sean@.classactweb.com> wrote in message
news:Oy540EMJFHA.2936@.TK2MSFTNGP15.phx.gbl...
> Below is the function that I am trying to create. I am failing in trying
to
> select from a random table. So I pass the table name in the function but
it
> won't let me select from that. Can someone help?
>
> CREATE FUNCTION dbo.GetZoneID
> (
> @.ID int,
> @.tableName varchar(50)
> )
> RETURNS money AS
> BEGIN
> DECLARE @.MasterZoneID INT,
> @.Flag INT,
> @.ZoneID money,
> @.TempZoneID money,
> @.TempTableName varchar(50)
> SET @.Flag = 0
> SET @.MasterZoneID = (Select cast(ZoneID as int) from System)
> SET @.TempZoneID = (@.MasterZoneID * 1000000000) + @.ID
>
> WHILE(@.Flag = 0)
> BEGIN
> --Failing on the @.tableName--
> SET @.ZoneID = (Select ZoneID from @.tableName where ZoneID = @.TempZoneID)
> --Failing there--
> IF(@.ZoneID = 0)
> BEGIN
> RETURN (@.MasterZoneID * 1000000000) + @.ID
> END
> ELSE
> BEGIN
> SET @.TempZoneID = @.TempZoneID + 0.0001
> END
> END
> RETURN (@.MasterZoneID * 1000000000) + @.ID
> END
>
>
|||Sorry forgot you can't do a rand() in function duh
"Peter 'Not Peter The Spate' Nolan" wrote:
[vbcol=seagreen]
> The reason why it fails is because your running dynamic code.
> Have a look at the following
> Declare @.SQL as varchar(100)
> set @.SQL = 'Select ZoneID from + ' + @.tableName + ' where ZoneID = ' +
> @.TempZoneID
> nb if any of these are ints then convert them to varchars
> EXEC sp_executesql @.sql, N'@.ZoneID int OUTPUT', @.ZoneID OUTPUT
> The sp_executesql will execute the sql then return the value into @.ZoneID.
> If you are trying to random values why don't you do
> declare @.Random int
> set @.Random= rand() * 1000000
> instead ?
> Peter
> "Information is the oxygen of the modern age. It seeps through the walls
> topped by barbed wire, it wafts across the electrified borders.""
> Ronald Reagan
>
> "Sean McKaharay" wrote:
|||...or dynamic code, bad post or what ;(
"Sean McKaharay" wrote:

> Below is the function that I am trying to create. I am failing in trying to
> select from a random table. So I pass the table name in the function but it
> won't let me select from that. Can someone help?
>
> CREATE FUNCTION dbo.GetZoneID
> (
> @.ID int,
> @.tableName varchar(50)
> )
> RETURNS money AS
> BEGIN
> DECLARE @.MasterZoneID INT,
> @.Flag INT,
> @.ZoneID money,
> @.TempZoneID money,
> @.TempTableName varchar(50)
> SET @.Flag = 0
> SET @.MasterZoneID = (Select cast(ZoneID as int) from System)
> SET @.TempZoneID = (@.MasterZoneID * 1000000000) + @.ID
>
> WHILE(@.Flag = 0)
> BEGIN
> --Failing on the @.tableName--
> SET @.ZoneID = (Select ZoneID from @.tableName where ZoneID = @.TempZoneID)
> --Failing there--
> IF(@.ZoneID = 0)
> BEGIN
> RETURN (@.MasterZoneID * 1000000000) + @.ID
> END
> ELSE
> BEGIN
> SET @.TempZoneID = @.TempZoneID + 0.0001
> END
> END
> RETURN (@.MasterZoneID * 1000000000) + @.ID
> END
>
>
>