Need for Multi-Threaded Visual Basic.NET to SQL Server Data Access Example Code

Of all the Visual Basic.NET data access books that I have purchased and all the Internet site example code that I have reviewed, none have had any good examples of multi-threaded VB.NET code doing data access.

I am trying to avoid the non-responsiveness in a VB app while a simple data retrieval from SQL Server 2005 is in progress.

If anyone knows of any book titles or web sites that have example code (good or not) of multi-threaded VB.NET applications doing data access against Microsoft SQL Server (7, 2000, or 2005) or even against Microsoft Access(TM), it would be very much appreciated if you could provide the book title or URL to point me in the right direction.

The more examples the better.

Thanks in advance.

Sounds like you need to make your calls Async.

http://msdn2.microsoft.com/en-us/library/ms379553(VS.80).aspx

|||

I've never seen any database programming books that cover this specific topic. You may want to start by looking for .NET books that cover multi-threaded programming in .NET. From my past experience, I remember it is a bit tricky.

|||

Async ADO.NET2.0- Summary of Best Search Results

The best discussions re async commands (transactions) using SQL Server 2005 that I have found are:

Books:

WROX?’s ‘Professional ADO.NET 2’

Web Articles :

http://msdn2.microsoft.com/en-us/vstudio/aa718334.aspx (Asych Query Samples from Data Access Samples) with source (this is the sample I chose)

www.codeproject.com/dotnet/asynchronousdataaccess.asp (with source)

http://msdn2.microsoft.com/en-us/library/ms379553.aspx (with source), but note warning at:

http://blogs.msdn.com/angelsb/archieve/2004/09/02/224964.aspx

http://nayyeri.net/archieve/2006/08/29/Asynchronous-command-execution-in-.NET-2.0.aspx

http://msdn.microsoft.com/library/en-us/cpgenref/html/cpconAsynchronousExecution.asp

http://msdn2.microsoft.com/en-us/library/ms228972(vs.80,d=printer).aspx

http://msdn2.microsoft.com/en-us/library/ms228975(VS.80).aspx

http://msdn2.microsoft.com/en-us/library/wewwczdw(VS.80).aspx

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpgenref/html/cpconAsynchronousProgrammingDesignPattern.asp

|||

Most books talk about how to do basic async execution from a button click this is fine but the trouble begins when you want to populate the UI with the results of an async db command. The .NET UI is NOT thread safe and you have to serialize everything to the UI thread when doing things like updating a datagrid control for example. If you stick with the data driven events you are OK but if you want to do things more complex it can get troublesome.

I remember doing this back in the old days (.NET 1.0 and 1.1 days) and I would end up with a dead datagrid control with a big red X on it when the grid control got out of sync. So what I finally did after much pain and suffering was write my own custom message passing system that would pass messages to background threads and queue responses back to UI thread, then final bit of data manipulation had to be done on the UI thread.

I remember even creating/manipulating a new datarow on a secondary thread would break the datagrid/dataset (note I would just call NewRow on background thread and then serialize the actual adding of the new row on primary UI thread). This stuff might be fixed in .NET 2.0, so maybe I am being overly cautious here.

need example of sproc

looking for an example of how to correctly build up a dynamic where clause
based upon input parametersHi,
See the link for the detailed article on dynamic sql.
http://www.microsoft.com/technet/pr...n/spltmirr.mspx
Thanks
Hari
SQL Server MVP
"TJS" <nospam@.here.com> wrote in message
news:e2Ixgr6aFHA.1660@.tk2msftngp13.phx.gbl...
> looking for an example of how to correctly build up a dynamic where clause
> based upon input parameters
>|||WHERE name = ' + @.name +N'
"TJS" wrote:

> looking for an example of how to correctly build up a dynamic where clause
> based upon input parameters
>
>

Need Example of Source Component with Multiple non-Error Outputs

Can someone please point me to one or more examples of a Source component that has two or more outputs?

I wrote a source, which works with a single output, but after adding a second output, I see no rows there. I've single-stepped the code in the debugger, and it looks like it should be adding rows to the second output, but the downstream component never sees any.

Thanks.

Synchronous or async?

Here's a synchronous one I wrote a while back:

http://agilebi.com/cs/blogs/jwelch/archive/2007/07/12/processing-a-flat-file-with-header-and-detail-rows.aspx

Here's a different one from Jamie:

http://blogs.conchango.com/jamiethomson/archive/2005/09/05/SSIS-Nugget_3A00_-Multiple-outputs-from-a-synchronous-script-transform.aspx

|||

A source will always be synchronous, by definition. This is a full source component, not a script.

I do not have a suitable sample I can give away, but I can describe it-

Use ProvideComponentProperties to add two outputs, setting ExternalMetadataColumnCollection.IsUsed to true. I also add a custom property as a marker to identify the purpose of each output later on.

Validate and RMD are much the same as usual, do the work that needs doing. PreExecute I use to get the ID of each output and store it in a suitable variable. Same concept as we do with the column index, and we do that too.

In PrimeOutput the array of output ID values can be compared with the ID values you cached in PreExecute. The index in the outputIDs array matches the index in the buffers array, so you can determine which buffer in that array matches which functional output.

Then just add data as normal, add rows and set column values. Do this to each buffer as required.

Finally be sure to call buffer.SetEndOfRowset() on all buffers in the buffers array.

Any help?

|||

Thanks, that's just about what I'm doing, though my (unusual) source doesn't have external metadata. Does it need to?

Like I said, I'm probably doing something silly. I think my next step is to abstract away the specifics of my source. I'll create a source that just returns 10 rows to one output and 10 rows to the other. If that works, then my silly mistake will be in how I send rows to the second output.

Thanks to all.

|||

You do not need it I guess, but it is usual. It acts as a pattern that can be used to describe any external dataset in SSIS terms. We normally work on a model where you can select one or more columns from the external data source, but it does not have o be all. The metadata acts as the choice and output columns are the selection. It also helps for maintenance and validation of columns, because of the separation layer. Still if you had a very stable closed source it may make sense not to bother. I tend to write more flexible components aimed at a wider market, rather than a specific project component.

You send rows to both outputs in exactly the same way, you get multiple buffers in the array, so just add the row to the correct one. That bit the same as havig one buffer, you just need to work out which buffer is which in the array and reference it.

Good luck.

|||

John Saunders wrote:

Like I said, I'm probably doing something silly.

In fact it was so silly, I didn't even realize it. It was bad test data due to having moved from one development environment to the next. I was chaging one file but testing with another file of the same name in a different directory. Very silly.

|||

DarrenSQLIS wrote:

A source will always be synchronous, by definition.

Err...are you sure? I kinda think a source is always asynchronous cos its creating a new execution tree.

-Jamie

sql

Need Example of Source Component with Multiple non-Error Outputs

Can someone please point me to one or more examples of a Source component that has two or more outputs?

I wrote a source, which works with a single output, but after adding a second output, I see no rows there. I've single-stepped the code in the debugger, and it looks like it should be adding rows to the second output, but the downstream component never sees any.

Thanks.

Synchronous or async?

Here's a synchronous one I wrote a while back:

http://agilebi.com/cs/blogs/jwelch/archive/2007/07/12/processing-a-flat-file-with-header-and-detail-rows.aspx

Here's a different one from Jamie:

http://blogs.conchango.com/jamiethomson/archive/2005/09/05/SSIS-Nugget_3A00_-Multiple-outputs-from-a-synchronous-script-transform.aspx

|||

A source will always be synchronous, by definition. This is a full source component, not a script.

I do not have a suitable sample I can give away, but I can describe it-

Use ProvideComponentProperties to add two outputs, setting ExternalMetadataColumnCollection.IsUsed to true. I also add a custom property as a marker to identify the purpose of each output later on.

Validate and RMD are much the same as usual, do the work that needs doing. PreExecute I use to get the ID of each output and store it in a suitable variable. Same concept as we do with the column index, and we do that too.

In PrimeOutput the array of output ID values can be compared with the ID values you cached in PreExecute. The index in the outputIDs array matches the index in the buffers array, so you can determine which buffer in that array matches which functional output.

Then just add data as normal, add rows and set column values. Do this to each buffer as required.

Finally be sure to call buffer.SetEndOfRowset() on all buffers in the buffers array.

Any help?

|||

Thanks, that's just about what I'm doing, though my (unusual) source doesn't have external metadata. Does it need to?

Like I said, I'm probably doing something silly. I think my next step is to abstract away the specifics of my source. I'll create a source that just returns 10 rows to one output and 10 rows to the other. If that works, then my silly mistake will be in how I send rows to the second output.

Thanks to all.

|||

You do not need it I guess, but it is usual. It acts as a pattern that can be used to describe any external dataset in SSIS terms. We normally work on a model where you can select one or more columns from the external data source, but it does not have o be all. The metadata acts as the choice and output columns are the selection. It also helps for maintenance and validation of columns, because of the separation layer. Still if you had a very stable closed source it may make sense not to bother. I tend to write more flexible components aimed at a wider market, rather than a specific project component.

You send rows to both outputs in exactly the same way, you get multiple buffers in the array, so just add the row to the correct one. That bit the same as havig one buffer, you just need to work out which buffer is which in the array and reference it.

Good luck.

|||

John Saunders wrote:

Like I said, I'm probably doing something silly.

In fact it was so silly, I didn't even realize it. It was bad test data due to having moved from one development environment to the next. I was chaging one file but testing with another file of the same name in a different directory. Very silly.

|||

DarrenSQLIS wrote:

A source will always be synchronous, by definition.

Err...are you sure? I kinda think a source is always asynchronous cos its creating a new execution tree.

-Jamie

Need example of a "Hash" function.....

Many articles on sql server security make reference to Hash functions. Do you know of a simple example of a hash function that I could show to others?

For example, would taking the first eight bytes of the mathematical "sin" of a number be a good function? I don't know. Or is a hash "function" actually an involved algorithm, so the "simple" formula I was looking for really doesn't exist.

TIA,

Barkingdog

In SQL Server 2005 we have a new builtin to calculate hash functions: HashBytes.

For more details on this builtin I recommend consulting BOL (http://msdn2.microsoft.com/en-us/library/ms174415.aspx), but here is a short example:

SELECT HashBytes( 'sha1', '1234' )

-- returns 0x7110EDA4D09E062AA5E4A390B0A572AC0D2C0220

SELECT HashBytes( 'md5', '1234' )

--returns 0x81DC9BDB52D04DC20036DBD8313ED055

-Raul Garcia

SDE/T

SQL Server Engine

|||

I have read that both MD5 and SHA1 (its successor) have been "compromised". Is this true?

Barkingdog

Need example -- SQLCMD -y

Hi,

Please can any one let me know jhow to use -y option with SQLCMD.

The -y option affects the number of characters wide that variable width fields like varchar(max) or nvarchar(max) display as in the output.

The the following two examples...

Command:
sqlcmd -Q"SELECT CONVERT(nvarchar(max),100) AS Test"

Output:
Test

--
--
--
-
100

versus...

Command:
sqlcmd -y 10 -Q"SELECT CONVERT(nvarchar(max),100) AS Test"

Output:
Test
-
100

Notice that the output column in the first one is so wide that it's underline wraps multiple times on the screen. However, in the second command the width of the column is restricted to 10 characters.

Does that help?

|||Nice use of font and background coloring :)|||Thanks. The features are there. Might as well use them eh?|||

Hi Bret Stateham,

Here what I want to run, when i run in sqlcmd it did not show me the completecode.This -y will help me.
SELECT definition FROM sys.sql_modules WHERE object_id = OBJECT_ID('PLocation');

*************************************************************************************
Please can you let me know how I can use here

sqlcmd -y 10 -Q"SELECT definition FROM sys.sql_modules WHERE object_id = OBJECT_ID('PLocation');"

definition
-
(0 rows affected)

I am doing it wrong, Can you help to write this statement.

|||

I am not sure why you are still attempting to use the -y option after the problems I indicated in using SQLCMD to script out objects. Your query may or may not work depending on the default database for the login from which you are trying the SQLCMD command. You could use the command-line below:

sqlcmd -y 8000 -Q"SELECT definition FROM sys.sql_modules WHERE object_id = OBJECT_ID('PLocation');"

And add "-h-1" to remove headers. But there will be additional spaces before and after the object definition. You need to adjust -y based on the maximum length of definition or use -y 0 which will be slower to use. There are also other problems like informational messages interleaved in the output if you have a USE <dbname> in the query for example. And you have to do that in your query because the default database may not be the user database so object_id & query will be evaluated in a different database. You could use the redirect option in SQLCMD to output messages to a different file for example. See SQLCMD topic in Books Online for each option and how to use it.

|||

Hi,

It is giving me an error

C:\>sqlcmd
1> use lfin

2> sqlcmd -y 8000 -Q"SELECT definition FROM sys.sql_modules WHERE object_id = OBJECT_ID('PLocation');"
3> go

Msg 102, Level 15, State 1, Server test, Line 2
Incorrect syntax near 'sqlcmd'.

Thanks in advance

|||

This is because SQLCMD is not a SQL command, it is a DOS command. Once in sqlcmd, just execute the query, or set the db in the statement and run from dos.

C:\>sqlcmd -y 8000 -Q"SELECT definition FROM lfin.sys.sql_modules WHERE object_id = OBJECT_ID('PLocation');"

|||

HI Louis,

Thank you for the detail. It run fine but
it did not provide me the code of PLocation procedure.
It only show me - all over.

at then end say 0 row effected

Thank you for your help.

thanks

|||

Hi :

If I don't want to output in console, I want to set this value to a variable, How can I do?

Any help would be much appreciated!

Need example -- SQLCMD -y

Hi,

Please can any one let me know jhow to use -y option with SQLCMD.

The -y option affects the number of characters wide that variable width fields like varchar(max) or nvarchar(max) display as in the output.

The the following two examples...

Command:
sqlcmd -Q"SELECT CONVERT(nvarchar(max),100) AS Test"

Output:
Test

--
--
--
-
100

versus...

Command:
sqlcmd -y 10 -Q"SELECT CONVERT(nvarchar(max),100) AS Test"

Output:
Test
-
100

Notice that the output column in the first one is so wide that it's underline wraps multiple times on the screen. However, in the second command the width of the column is restricted to 10 characters.

Does that help?

|||Nice use of font and background coloring :)|||Thanks. The features are there. Might as well use them eh?|||

Hi Bret Stateham,

Here what I want to run, when i run in sqlcmd it did not show me the completecode.This -y will help me.
SELECT definition FROM sys.sql_modules WHERE object_id = OBJECT_ID('PLocation');

*************************************************************************************
Please can you let me know how I can use here

sqlcmd -y 10 -Q"SELECT definition FROM sys.sql_modules WHERE object_id = OBJECT_ID('PLocation');"

definition
-
(0 rows affected)

I am doing it wrong, Can you help to write this statement.

|||

I am not sure why you are still attempting to use the -y option after the problems I indicated in using SQLCMD to script out objects. Your query may or may not work depending on the default database for the login from which you are trying the SQLCMD command. You could use the command-line below:

sqlcmd -y 8000 -Q"SELECT definition FROM sys.sql_modules WHERE object_id = OBJECT_ID('PLocation');"

And add "-h-1" to remove headers. But there will be additional spaces before and after the object definition. You need to adjust -y based on the maximum length of definition or use -y 0 which will be slower to use. There are also other problems like informational messages interleaved in the output if you have a USE <dbname> in the query for example. And you have to do that in your query because the default database may not be the user database so object_id & query will be evaluated in a different database. You could use the redirect option in SQLCMD to output messages to a different file for example. See SQLCMD topic in Books Online for each option and how to use it.

|||

Hi,

It is giving me an error

C:\>sqlcmd
1> use lfin

2> sqlcmd -y 8000 -Q"SELECT definition FROM sys.sql_modules WHERE object_id = OBJECT_ID('PLocation');"
3> go

Msg 102, Level 15, State 1, Server test, Line 2
Incorrect syntax near 'sqlcmd'.

Thanks in advance

|||

This is because SQLCMD is not a SQL command, it is a DOS command. Once in sqlcmd, just execute the query, or set the db in the statement and run from dos.

C:\>sqlcmd -y 8000 -Q"SELECT definition FROM lfin.sys.sql_modules WHERE object_id = OBJECT_ID('PLocation');"

|||

HI Louis,

Thank you for the detail. It run fine but
it did not provide me the code of PLocation procedure.
It only show me - all over.

at then end say 0 row effected

Thank you for your help.

thanks

|||

Hi :

If I don't want to output in console, I want to set this value to a variable, How can I do?

Any help would be much appreciated!

Need basic technical MSDE information.

Hello, I'm looking for a technical faq for MSDE. I need to know the
essentials for, example, the number of simultaneous connections that MSDE
permits, etc.
Thanks
hi,
"Narwe" <Narwe@.discussions.microsoft.com> ha scritto nel messaggio
news:22828EB8-E242-4E8F-B115-96E7B768D366@.microsoft.com
> Hello, I'm looking for a technical faq for MSDE. I need to know the
> essentials for, example, the number of simultaneous connections that
> MSDE permits, etc.
> Thanks
the pricipal feature can be seen at
http://www.microsoft.com/sql/msde/pr...fo/default.asp
and some comparison beteen edition at
http://msdn.microsoft.com/library/de...ar_ts_1cdv.asp
the specific question, regarding connections, is answered at
http://msdn.microsoft.com/library/de...ar_ts_8dbn.asp ,
but you have to keep in mind the Query Workload Governor kicking in when 8
concurrent workloads are executed, like explained in
http://msdn.microsoft.com/library/?u...asp?frame=true
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.9.1 - DbaMgr ver 0.55.1
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||www.qmsdnug.org/DAT306_LOW.ppt might also help.
HTH,
Greg Low [MVP]
MSDE Manager SQL Tools
www.whitebearconsulting.com
"Andrea Montanari" <andrea.sqlDMO@.virgilio.it> wrote in message
news:2rllhiF1c75oqU1@.uni-berlin.de...
> hi,
> "Narwe" <Narwe@.discussions.microsoft.com> ha scritto nel messaggio
> news:22828EB8-E242-4E8F-B115-96E7B768D366@.microsoft.com
> the pricipal feature can be seen at
> http://www.microsoft.com/sql/msde/pr...fo/default.asp
> and some comparison beteen edition at
>
http://msdn.microsoft.com/library/de...ar_ts_1cdv.asp
> the specific question, regarding connections, is answered at
>
http://msdn.microsoft.com/library/de...ar_ts_8dbn.asp ,
> but you have to keep in mind the Query Workload Governor kicking in when 8
> concurrent workloads are executed, like explained in
>
http://msdn.microsoft.com/library/?u...asp?frame=true
> --
> Andrea Montanari (Microsoft MVP - SQL Server)
> http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
> DbaMgr2k ver 0.9.1 - DbaMgr ver 0.55.1
> (my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
> interface)
> -- remove DMO to reply
>

Need assistance with Duplicate Select Statment

Any help would be appreciated here. My question is two part:

1.) Select duplicates that match any of a number of columns for example...

The email is the same OR

The homephone is the same OR

The mobilephone is the same OR

The address1 is the same

The uniqueID is ConsIntID

My Select Statement: (Which does not work)

SELECT CONSINTID, FIRSTNAME, LASTNAME, EMAIL1, ADDRESS1, HOMEPHONE, MOBILEPHONE, CREATEDATE, USERIDS

FROM CONSULTANTS

WHERE CONSINTID IN (SELECT CONSINTID

FROM CONSULTANTS

HAVING COUNT(HOMEPHONE) > 1 OR COUNT(MOBILEPHONE) >1 OR etc.... )

-

Once the Select Duplicates Statement is corrected I will insert those into a ConsultantsDupe Table

The Second Question is: If I have more then one duplicate my stored_procedure fails

Here is my cursor and logic. The problem is getting multiple results in the subquery and I don't know the best way to correct this.

DECLARE DUPES CURSOR FOR

SELECT CONSINTID, FIRSTNAME, LASTNAME, EMAIL1, ADDRESS1, HOMEPHONE, MOBILEPHONE, CREATEDATE, USERIDS

FROM CONSULTANTS_DUPS

ORDER BY CREATEDATE DESC

OPEN DUPES

FETCH NEXT FROM DUPES

INTO @.CONSINTID, @.FIRSTNAME, @.LASTNAME, @.EMAIL1, @.ADDRESS1, @.HOMEPHONE, @.MOBILEPHONE, @.CREATEdATE, @.USERIDS

WHILE @.@.FETCH_STATUS = 0

BEGIN

SET @.FOUNDCONSINTID = (SELECT DISTINCT CONSINTID FROM CONSULTANTS_DUPS

WHERE CONSINTID <> @.CONSINTID AND HOMEPHONE = @.HOMEPHONE "NEED TO ADD COMPARISON FOR MOBILEPHONE, EMAIL ADDRESS HERE") <== This can provide more then one result which messes me up.

I'll take a couple shots at this. Hopefully somebody can post something even better. You'll have to forgive me. Without any sample tables I had to "shoot from the hip" and write it without any syntax checking.

SELECT * FROM Consultants C

WHERE EXISTS (SELECT * FROM Consultants CPhone

WHERE (C.HomePhone = CPhone.HomePhone

OR C.HomePhone = CPhone.CellPhone

OR C.CellPhone = CPhone.HomePhone

OR C.CellPhone = CPhone.CellPhone)

AND C.CONSINTID <> CPhone.CONSINTID

)

OR EXISTS (SELECT * FROM Consultants CEmail

WHERE C.EMAIL1 = CEmail.EMAIL1

AND C.CONSINTID <> EMAIL1.CONSINTID)

OR EXISTS ...

Here's another shot:

SELECT * FROM Consultants

WHERE HomePhone IN (SELECT HomePhone

FROM Consultants

WHERE HomePhone IS NOT NULL

GROUP BY HomePhone

HAVING COUNT(*) > 1)

OR CellPhone IN (SELECT CellPhone

FROM Consultants

WHERE CellPhone IS NOT NULL

GROUP BY CellPhone

HAVING COUNT(*) > 1)

OR Email1 IN (SELECT Email1

FROM Consultants

WHERE Email1 IS NOT NULL

GROUP BY Email1

HAVING COUNT(*) > 1)

This method suffers from the fact that you can't check HomePhone and CellPhone at the same time (with UNION ALL) because of a contact has the same phone number listed as home and cell then it could show up as a duplicate.

Here's where you might take the previous query as a natural transition to cross-check home phone against cell phone:

SELECT * FROM Consultants C

WHERE HomePhone IN (SELECT HomePhone

FROM (SELECT CONSINTID, HomePhone

FROM Consultants UNION

SELECT CONSINTID, CellPhone

FROM Consultants) SubQ

WHERE HomePhone IS NOT NULL

GROUP BY HomePhone

HAVING COUNT(*) > 1)

OR CellPhone IN (SELECT HomePhone

FROM (SELECT CONSINTID, HomePhone

FROM Consultants UNION

SELECT CONSINTID, CellPhone

FROM Consultants) SubQ

WHERE HomePhone IS NOT NULL

GROUP BY HomePhone

HAVING COUNT(*) > 1)

OR Email1 IN (SELECT Email1

FROM Consultants

WHERE Email1 IS NOT NULL

GROUP BY Email1

HAVING COUNT(*) > 1)

You may also be better off with multiple SELECT statements as it's hard to tell the cause of your duplicate in a query like this. Keep in mind that all of these queries are very read-intensive as you're dealing with multiple scans across your table. You may benefit, instead, by placing your duplicates in a temporary table (or table variable) and then querying against the temporary table.

|||something like this:

select *
from consultants
where consintid in
(select consintid
from consultants
group by email
having count(*)>1
union all
select consintid

from consultants

group by homephone

having count(*)>1

union all
select consintid

from consultants

group by cellphone

having count(*)>1

union all
select consintid

from consultants

group by address1

having count(*)>1
)|||

Jared,

Yours worked! This gives me what I need for the duplicate temp table and the assistance is greatly appreciated.

Any feedback on the second portion?

Need an example of failed CREATE PROC...

This is an example of a working CREATE PROCEDURE.
USE Northwind
GO
CREATE PROC dbo.OverdueOrders
AS
SELECT *
FROM dbo.Orders
WHERE RequiredDate < GETDATE() AND ShippedDate IS Null
What does it mean that "a CREATE PROCEDURE statement cannot be combined with
other TSQL statements in a single batch?" I see a TSQL stmt in this working
one.
Would someone provide me an example, please?
Thanks.
USE Northwind
GO
SELECT *
FROM dbo.Orders
CREATE PROC dbo.OverdueOrders
AS
SELECT *
FROM dbo.Orders
WHERE RequiredDate < GETDATE() AND ShippedDate IS Null
GO
"light_wt" <lightwt@.discussions.microsoft.com> wrote in message
news:8E891A71-BE7D-495A-B134-CC3C1D1E2367@.microsoft.com...
> This is an example of a working CREATE PROCEDURE.
> USE Northwind
> GO
> CREATE PROC dbo.OverdueOrders
> AS
> SELECT *
> FROM dbo.Orders
> WHERE RequiredDate < GETDATE() AND ShippedDate IS Null
> What does it mean that "a CREATE PROCEDURE statement cannot be combined
with
> other TSQL statements in a single batch?" I see a TSQL stmt in this
working
> one.
> Would someone provide me an example, please?
> Thanks.
|||Here you go:
USE Northwind
GO
SELECT * FROM Orders
CREATE PROC dbo.OverdueOrders
AS
SELECT *
FROM dbo.Orders
WHERE RequiredDate < GETDATE()
AND ShippedDate IS NULL
GO
Anith
|||A batch is a set of TSQL statements that are submitted together for
processing. You designate the end of a batch of commands in SQL Server
using the GO statement.
There are only two CREATE statements that can be combined in a single batch.
CREATE DATBASE and CREATE TABLE.
All of the other CREATE statements must be submitted as separate batches.
So:
CREATE DATABASE Frog (....)
CREATE TABLE LilyPad (...)
CREATE TABLE Swim(...)
GO
Would work, but
CREATE TABLE LilyPad(...)
CREATE PROC Croak ...
Would not.
You would have to submit them as:
CREATE TABLE LilyPad...
GO
CREATE PROC Croak...
GO
HTH
Rick Sawtell
MCT, MCSD, MCDBA
"light_wt" <lightwt@.discussions.microsoft.com> wrote in message
news:8E891A71-BE7D-495A-B134-CC3C1D1E2367@.microsoft.com...
> This is an example of a working CREATE PROCEDURE.
> USE Northwind
> GO
> CREATE PROC dbo.OverdueOrders
> AS
> SELECT *
> FROM dbo.Orders
> WHERE RequiredDate < GETDATE() AND ShippedDate IS Null
> What does it mean that "a CREATE PROCEDURE statement cannot be combined
with
> other TSQL statements in a single batch?" I see a TSQL stmt in this
working
> one.
> Would someone provide me an example, please?
> Thanks.
|||Thank you, all.
I appreciate your detailed and good explaination, Rick.
"Rick Sawtell" wrote:
<snip>

Need an example of a trigger.

Hello,

I'm a newbie to the MS SQL db engine, and I'd like to have some help with Transact-SQL. I'd like to know the code to create a trigger to do the following. I have two tables, one references the other. I'd like the database to automatically delete the records from table2 when the record that they reference in table1 is deleted. Much thanks.Try this

create trigger dbo.trigger_deleterecs on table1
for delete
as
begin

declare @.refcolumn datatypehere
select @.refcolumn = deleted.refcolumn from deleted
delete from table2 where table2refcolumn = @.refcolumn

end|||how about:

create trigger dbo.trigger_deleterecs on table1
for delete
as
begin
delete from table2 where refcolumn in (select refcolumn from deleted)
end

or

create trigger dbo.trigger_deleterecs on table1
for delete
as
begin
delete t2
from table2 t2
join deleted d on t2.refcolumn = d.refcolumn
end

by using either of these you will delete all records when multipule records are deleted.

Need an example for CROSS APPLY!

Does anybody knows, how this cross apply works'
I need a simple example, because I have no idea what it's for!
(I can't found any good example in the net!?!?)
thanks in advance...This is not a great example as the query would be better done with a join,
but it should help you understand the syntax:
USE AdventureWorks
GO
CREATE FUNCTION GetLineItemsForOrder(@.SalesOrderID INT)
RETURNS TABLE
AS
RETURN
(
SELECT SalesOrderDetailID
FROM Sales.SalesOrderDetail
WHERE SalesOrderID = @.SalesOrderID
)
GO
SELECT
SH.SalesOrderID,
LI.SalesOrderDetailID
FROM Sales.SalesOrderHeader SH
CROSS APPLY GetLineItemsForOrder(SH.SalesOrderID) LI
WHERE TotalDue > 20000
GO
DROP FUNCTION GetLineItemsForOrder
GO
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--
"nullstring" <johannes.veit@.datapec.de> wrote in message
news:1127133941.948110.85450@.g44g2000cwa.googlegroups.com...
> Does anybody knows, how this cross apply works'
> I need a simple example, because I have no idea what it's for!
> (I can't found any good example in the net!?!?)
>
> thanks in advance...
>

Need an example for CROSS APPLY!

Does anybody knows, how this cross apply works?
I need a simple example, because I have no idea what it's for!
(I can't found any good example in the net!?!?)
thanks in advance...
This is not a great example as the query would be better done with a join,
but it should help you understand the syntax:
USE AdventureWorks
GO
CREATE FUNCTION GetLineItemsForOrder(@.SalesOrderID INT)
RETURNS TABLE
AS
RETURN
(
SELECT SalesOrderDetailID
FROM Sales.SalesOrderDetail
WHERE SalesOrderID = @.SalesOrderID
)
GO
SELECT
SH.SalesOrderID,
LI.SalesOrderDetailID
FROM Sales.SalesOrderHeader SH
CROSS APPLY GetLineItemsForOrder(SH.SalesOrderID) LI
WHERE TotalDue > 20000
GO
DROP FUNCTION GetLineItemsForOrder
GO
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
"nullstring" <johannes.veit@.datapec.de> wrote in message
news:1127133941.948110.85450@.g44g2000cwa.googlegro ups.com...
> Does anybody knows, how this cross apply works?
> I need a simple example, because I have no idea what it's for!
> (I can't found any good example in the net!?!?)
>
> thanks in advance...
>

Need advice. Dropdown tables and moving data

Hi,
I have the following problem. In my system, I'm going to have many
tables that use drop-down tables. For example, my 'Persons' table has a
column called 'FK_HairColors_ID' which is a FK of the table 'HairColors'
If I want to add a new person calld 'John' with black hair, I do this:
insert into haircolors (id,color) values (1,'brown')
insert into haircolors (id,color) values (2,'black')
insert into persons (siteid,id,name,fk_haircolors_id) values ('site 1'
1,'john',2)
I also though that instead of doing this, I could insert the value
directly there. Something like this:
insert into haircolors (pk) values ('brown')
insert into haircolors (pk) values ('black')
insert into persons (siteid,id,name,fk_haircolors_pk) values ('site
1',1,'john','brown')
And I would add a relation between those tables, so the the system would
check that the values that I have for the hair color in Persons are in
the HairColors table.
Obviously, the first solution is more elegant. But maybe for my problem
it will be better the second solution.
Here is my problem.
I am going to have multiple sites running this type of db. We are going
to have some processes that will transfer records from one site to
another. That's why we have that 'siteid' field there for Persons. My
question is:
Should I have siteid for the haircolors as well? (I mean to add a column
'SiteID' in the HairColors table) Does that make sense? In that case we
could have 2 'black' hair color records that came from different sites.
If not, everytime that I move a Person from one site to another, I would
have to 'reconvert' the id of the Haircolor to the one matching in the
new site. For example, if for 'site 1' the black hair color had ID=2,
and for 'site 2' has ID=200, and I moving a record from 1 to 2, I would
have to convert that ID from 2 to 200.
What do you guys think?
Thanks for reading everything. I know it's a little bit confusing, but I
hope you guys got the idea of my problem.
RegardsStar wrote:
> Hi,
> I have the following problem. In my system, I'm going to have many
> tables that use drop-down tables. For example, my 'Persons' table has a
> column called 'FK_HairColors_ID' which is a FK of the table 'HairColors'
> If I want to add a new person calld 'John' with black hair, I do this:
> insert into haircolors (id,color) values (1,'brown')
> insert into haircolors (id,color) values (2,'black')
> insert into persons (siteid,id,name,fk_haircolors_id) values ('site 1'
> 1,'john',2)
> I also though that instead of doing this, I could insert the value
> directly there. Something like this:
> insert into haircolors (pk) values ('brown')
> insert into haircolors (pk) values ('black')
> insert into persons (siteid,id,name,fk_haircolors_pk) values ('site
> 1',1,'john','brown')
> And I would add a relation between those tables, so the the system would
> check that the values that I have for the hair color in Persons are in
> the HairColors table.
> Obviously, the first solution is more elegant. But maybe for my problem
> it will be better the second solution.
> Here is my problem.
> I am going to have multiple sites running this type of db. We are going
> to have some processes that will transfer records from one site to
> another. That's why we have that 'siteid' field there for Persons. My
> question is:
> Should I have siteid for the haircolors as well? (I mean to add a column
> 'SiteID' in the HairColors table) Does that make sense? In that case we
> could have 2 'black' hair color records that came from different sites.
> If not, everytime that I move a Person from one site to another, I would
> have to 'reconvert' the id of the Haircolor to the one matching in the
> new site. For example, if for 'site 1' the black hair color had ID=2,
> and for 'site 2' has ID=200, and I moving a record from 1 to 2, I would
> have to convert that ID from 2 to 200.
> What do you guys think?
> Thanks for reading everything. I know it's a little bit confusing, but I
> hope you guys got the idea of my problem.
> Regards
"Dropdown tables"! (shudder) Do you really believe that the job of
tables is to model a user-interface for you?
Neither of your proposed solutions solves the problem you are getting
at. That is: "How do I ensure that all my sites encode hair colours in
the same way?" For example, does "Blond"="Blonde"? Does "Brown" mean
the same as "Brunette"? If you leave decisions like that to end users
then you will have a big task on your hands when you come to integrate
your data. If you have a standard set of codes that is centrally
maintained then you won't have that problem whatever method you use
(but do please choose a recognized standard coding convention if one
exists for your data).
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||David,
We cannot have a centralized site that can provide nice codes to each
one. Some sites will be working as an standalone sites without access
outside their network. We don't know if in the future they may be
interested in sharing some data. If they are later, they will provide
access to the remote sites temporarily, while the transfer is being made.
On the other hand, users want to be able to add new values to those
'dropdown tables' at any time.
In the hypothetical case that we could have a centralized site, some
sites wouldn't be interested in seeing the 'set of codes' from the
other 40 sites, when they have to select a value. Had you thought about
that?. Some 'drop-down tables' are not as simple as 'Hair Colors'... For
example, some may be 'Arrest codes' for a specific police department. We
don't want to have site 1 to see the arrest codes from each one of the
sites!! (shudder)
For that reason, and before shuddering and criticizing take a look at
the real world with real customers. It's great to answer the newsgroups
with 3 o 4 things that you got from the books that you have read, but in
the real world things are not so 'standard' and cannot be made in the
most elegant way.
David Portas wrote:
> Star wrote:
>
>
> "Dropdown tables"! (shudder) Do you really believe that the job of
> tables is to model a user-interface for you?
> Neither of your proposed solutions solves the problem you are getting
> at. That is: "How do I ensure that all my sites encode hair colours in
> the same way?" For example, does "Blond"="Blonde"? Does "Brown" mean
> the same as "Brunette"? If you leave decisions like that to end users
> then you will have a big task on your hands when you come to integrate
> your data. If you have a standard set of codes that is centrally
> maintained then you won't have that problem whatever method you use
> (but do please choose a recognized standard coding convention if one
> exists for your data).
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>|||For your hair color example I would forget using an artificial key
(HairColorID) and just use 'brown' or 'black' as the value. I do not
find using a number to be "elegent", though it is sometimes necessary.
For something more complicated, as you described in your second
message, I would be inclined to make the key two part, site and
ArrestCode. But I would have to see what sort of data went into an
ArrestCode master table before choosing the key.
Also, if you provide a simple example that is not representative of
the real problem, don't get too critical of the answers that reflect
your question as asked instead of the real problem. I also do not
know why you would assume that any particular advice is based on
examples in books. Believe it or not a lot of answers here are based
on years of hard won experience. For myself, just about any approach
I suggest avoiding is because I am trying to help others avoid my own
past mistakes. 8-)
Roy Harvey
Beacon Falls, CT
On Tue, 28 Mar 2006 22:46:58 +0200, Star <star@.nospam.com> wrote:

>Hi,
>I have the following problem. In my system, I'm going to have many
>tables that use drop-down tables. For example, my 'Persons' table has a
>column called 'FK_HairColors_ID' which is a FK of the table 'HairColors'
>If I want to add a new person calld 'John' with black hair, I do this:
>insert into haircolors (id,color) values (1,'brown')
>insert into haircolors (id,color) values (2,'black')
>insert into persons (siteid,id,name,fk_haircolors_id) values ('site 1'
>1,'john',2)
>I also though that instead of doing this, I could insert the value
>directly there. Something like this:
>insert into haircolors (pk) values ('brown')
>insert into haircolors (pk) values ('black')
>insert into persons (siteid,id,name,fk_haircolors_pk) values ('site
>1',1,'john','brown')
>And I would add a relation between those tables, so the the system would
>check that the values that I have for the hair color in Persons are in
>the HairColors table.
>Obviously, the first solution is more elegant. But maybe for my problem
>it will be better the second solution.
>Here is my problem.
>I am going to have multiple sites running this type of db. We are going
>to have some processes that will transfer records from one site to
>another. That's why we have that 'siteid' field there for Persons. My
>question is:
>Should I have siteid for the haircolors as well? (I mean to add a column
>'SiteID' in the HairColors table) Does that make sense? In that case we
>could have 2 'black' hair color records that came from different sites.
>If not, everytime that I move a Person from one site to another, I would
>have to 'reconvert' the id of the Haircolor to the one matching in the
>new site. For example, if for 'site 1' the black hair color had ID=2,
>and for 'site 2' has ID=200, and I moving a record from 1 to 2, I would
>have to convert that ID from 2 to 200.
>What do you guys think?
>Thanks for reading everything. I know it's a little bit confusing, but I
>hope you guys got the idea of my problem.
>Regards|||Thanks for your answer, Roy. I think I will end up using your
first suggestion.
Regarding David's answer... I understand that I could have given more
information about my problem, like I did in my second post, however
there are many ways of helping, and this was not the best way of doing it:

>"Dropdown tables"! (shudder) Do you really believe that the job of
>tables is to model a user-interface for you?
Don't you think? If he needed more information he could have asked
and I would have explained why I needed to have those shuddering
'Dropdown tables'. It's just I didn't think it was necessary to explain
all the reasons I was doing that. I just wanted to mention which one was
my problem.
Thanks again for helping, Roy.
Roy Harvey wrote:
> For your hair color example I would forget using an artificial key
> (HairColorID) and just use 'brown' or 'black' as the value. I do not
> find using a number to be "elegent", though it is sometimes necessary.
> For something more complicated, as you described in your second
> message, I would be inclined to make the key two part, site and
> ArrestCode. But I would have to see what sort of data went into an
> ArrestCode master table before choosing the key.
> Also, if you provide a simple example that is not representative of
> the real problem, don't get too critical of the answers that reflect
> your question as asked instead of the real problem. I also do not
> know why you would assume that any particular advice is based on
> examples in books. Believe it or not a lot of answers here are based
> on years of hard won experience. For myself, just about any approach
> I suggest avoiding is because I am trying to help others avoid my own
> past mistakes. 8-)
> Roy Harvey
> Beacon Falls, CT
>
> On Tue, 28 Mar 2006 22:46:58 +0200, Star <star@.nospam.com> wrote:
>|||Star wrote:
> We cannot have a centralized site that can provide nice codes to each
> one. Some sites will be working as an standalone sites without access
> outside their network. We don't know if in the future they may be
> interested in sharing some data. If they are later, they will provide
> access to the remote sites temporarily, while the transfer is being made.
> On the other hand, users want to be able to add new values to those
> 'dropdown tables' at any time.
In that case you will have integrate and conform the data at some
future date under either of your suggested solutions. I thought you
were asking whether you could avoid that but apparently you cannot.

> In the hypothetical case that we could have a centralized site, some
> sites wouldn't be interested in seeing the 'set of codes' from the
> other 40 sites, when they have to select a value. Had you thought about
> that?. Some 'drop-down tables' are not as simple as 'Hair Colors'... For
> example, some may be 'Arrest codes' for a specific police department. We
> don't want to have site 1 to see the arrest codes from each one of the
> sites!! (shudder)
That's a different question to the one you originally asked. In that
case you would presumably implement another table that mapped Arrect
Codes to Sites so you could track which codes each site could see.

> For that reason, and before shuddering and criticizing take a look at
> the real world with real customers. It's great to answer the newsgroups
> with 3 o 4 things that you got from the books that you have read, but in
> the real world things are not so 'standard' and cannot be made in the
> most elegant way.
I have been working with real customers' for 15 years.
The only criticism I meant to imply in my post was about your use of
the term "dropdown tables". What do you mean by that? Obviously it's
not a standard term. My interpretation was that you called them by that
name because they are used for dropdown lists in your UI. This is not a
good way to look at database design. In fact that mode of thinking
(database follows UI) leads to a lot of mistakes by developers in my
experience. Database design should proceed from business analysis to
conceptual modelling to logical modelling to physical modelling. In
that sense there is only one type of table.
So I'm not saying your solutions are right or wrong because your
example doesn't give us any criteria for that decision. What I was
suggesting was that your use of the term "dropdown table" indicates
that you may not have the right understanding of some database design
concepts.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx|||>Regarding David's answer... I understand that I could have given more
>information about my problem, like I did in my second post, however
>there are many ways of helping, and this was not the best way of doing it:
>
>Don't you think?....
As it happens, I see nothing wrong with Dave's answer. But everyone
sees things their own way.
Roy Harvey
Beacon Falls, CT|||Star skrev:

> Regarding David's answer... I understand that I could have given more
> information about my problem, like I did in my second post, however
> there are many ways of helping, and this was not the best way of doing it:
>
Just to settle this ;)
Star, frankly I think you over-reacted, don't be so sensitive, just
google for CELKO in this newsgroup and be thankful he didn't answer
your question (not yet, anyway).
Anyway, when asking for help on a newsgroup you should prepare yourself
for all sorts of possible answers, remember people are trying to answer
without getting paid or anything, and we all have our ups and downs.
Just keep your own calm, and in the end you probably get some good
answers, for free!
/impslayer, aka Birger Johansson|||impslayer,
yep... I have seen some of Celko's posts. I think he never tries to
help. In my opinion, he usually does the following:
1. Try to ridicule the person asking the question.
2. Recomend the person to buy his 'wonderful' books.
3. If he feels like helping, he will post a solution so theoretical that
will discourage the original poster. He would provide academic exercises
that have little application in the real world.
Regards|||
> The only criticism I meant to imply in my post was about your use of
> the term "dropdown tables". What do you mean by that? Obviously it's
> not a standard term. My interpretation was that you called them by that
> name because they are used for dropdown lists in your UI. This is not a
> good way to look at database design.
David,
Maybe the term 'drop-down tables' was not the best to use. I don't speak
English very well, and I didn't know a better way to say that.
Basically our customers needs to see a drop-down box in the UI with
different values to choose from for some of the fields. That information
needs to be stored in a table, don't you think?. That's why I called
like that. How would you call it?

Nearest distance

Hi
How do I get a nearest distance of a point? For example, I have two tables A and B and I want to find the nearest distance between the records of the two tables. In addition, one of the tables should also give me the distance. The data I have geo spatial data. Can this be done in SQL
Help will be appreciatedThey talk about something like that here, in their database section...

http://skyserver.fnal.gov/en/

As far as understanding it....I have enough trouble tieing my shoes...|||Hi
I looked through the site but is not of much help..can you elaborate
thanks

Originally posted by Brett Kaiser
They talk about something like that here, in their database section...

http://skyserver.fnal.gov/en/

As far as understanding it....I have enough trouble tieing my shoes...|||Originally posted by Brett Kaiser
As far as understanding it....I have enough trouble tieing my shoes...

You didn't get this part, huh...

What are you trying to do...maybe it's a simple answer...

"Distance" is a relative thing...

Got indexes?|||Hi
I need the nearest distance in miles of one point from the other.

Originally posted by namitao
Hi
How do I get a nearest distance of a point? For example, I have two tables A and B and I want to find the nearest distance between the records of the two tables. In addition, one of the tables should also give me the distance. The data I have geo spatial data. Can this be done in SQL
Help will be appreciated|||Nearest distance between two values? A distance can only be "nearest" to one point (in the general case), because you can't optimize for more than one criteria. Are you talking about some kind of linear regression between the points?

I think you need to post some sample data and an example of the result you are looking for.|||I'm confused...I originally thought you wanted the distance between rows of data...

Do you want the distance between points on a map?

I did this once for a delivery system...

You want to google the "Great Circle" trig function...I can't find the math...but here's someone who built something...

http://williams.best.vwh.net/gccalc.htm

But you need longitude and latitude of the addresses...

Is that what you're looking for?|||Hi
For Example I have 2 tables
Table A
Number Latitude Longitude Time total
1 48.2951 -122.276 1:22:49 -87
2 48.2952 -122.292 1:17:35 -92
3 48.2952 -122.292 1:16:35 -91
4 48.2952 -122.276 1:21:59 -86
5 48.2952 -122.276 1:22:48 -91
6 48.2953 -122.292 1:17:34 -87
Table B
Number Latitude Longitude Time total_c
1 48.2904 -122.271 1:24:30 -87
2 48.2904 -122.271 1:23:51 -88
3 48.2904 -122.271 1:24:29 -87
4 48.2904 -122.271 1:23:52 -85
5 48.2904 -122.271 1:24:28 -86.5
6 48.2904 -122.271 1:23:53 -86

I need to find the shortest distance between the points of the 2 tables.
I need to find the shortest distance of points in Table B to point in Table A.
I hope this can help someone answer my question
:(
Thanks


Originally posted by Brett Kaiser
I'm confused...I originally thought you wanted the distance between rows of data...

Do you want the distance between points on a map?

I did this once for a delivery system...

You want to google the "Great Circle" trig function...I can't find the math...but here's someone who built something...

http://williams.best.vwh.net/gccalc.htm

But you need longitude and latitude of the addresses...

Is that what you're looking for?|||I did this in Access once...

You need the formual

http://en2.wikipedia.org/wiki/Great_circle_distance

Then create it as a udf and join the tables passing the 2 ponts in...

Have to udf return the distance...

I should rewrite this in sql server...I'll have to dig it up...

It was actually a lot of fun building it (...geez what a geek)

Navigation in a Graph

In SQL Reporting Services, i am having a problem with
passing the label value(eg. on x-axis) as a parameter to another report.
So for example, lets say i have the days of the week as my x-axis values in
the graph. Is there any way to allow the user to click on this label
value(eg. monday) which will then pass this as a parameter to another
report?
ThanksNot from labels themselves, but you can do this with data points. Pull up
Chart Properties dialog in report designer -> Values -> Edit -> Action tab.
--
Ravi Mumulla (Microsoft)
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Kershni Chetty" <support@.sqrsoftware.com> wrote in message
news:O1FqKospEHA.1988@.TK2MSFTNGP09.phx.gbl...
> In SQL Reporting Services, i am having a problem with
> passing the label value(eg. on x-axis) as a parameter to another report.
> So for example, lets say i have the days of the week as my x-axis values
in
> the graph. Is there any way to allow the user to click on this label
> value(eg. monday) which will then pass this as a parameter to another
> report?
> Thanks
>