Do I need to verify that NewID() returns a unique GUID?

I'm migrating a web based system to SQL server. I'm planning on using the SQL server function NewID() to create unique keys for many of my records in many different tables. I'm just wondering if NewID() is guaranteed to return a value that does not already exist in my database. I mean obviously once you have a certain number of records (a hell of a lot) you'd be breaking the odds to never come up with a duplicate.

Do I need to make sure the result of NEWID() doesn't already exist?

Thanks

No this algorithm is guaranteed to always return a unique value. At least for a really, really long time,

Hope this helps,

Do I need recursion ?

Hi there, Any tips on my problem would be most welcome...

right, the scenario.

A web Blog

blogger1 posts a blog_entry, e.g I love the simpsons
blogger2 comments on that blog_entry, e.g No, I hate the simpsons
Blogger3 comments on that comment, ie, How can you hate the simpsons.

so you can comment on a comment on a comment etc.. lool.

right, i have got two tables... Blog_entry & comment. i need to be able to search for a blog_entry + all the comments on that blog_entry..

at the moment i can search for the comments on the blog_entry using the FK in the comment table.

blog_entry
INSERT INTO Blog_Entry VALUES(0001,'I love the Simpsons');

comments
INSERT INTO Comment VALUES(0001,' No I hate the simpsons, cID 1000);

but i need to be able to search for the comments on comments

INSERT INTO Comment VALUES(0001,' How can you hate the simpsons, cID1000);

hopefully you can see the problem here, with only one comments table how can i get the search for the comment on the comment.. theres nothing linking them...
I could make a sub comments table, and use a FK (as with the blog_entry & first comment)

but then I would have to make another sub sub table to be able to get those comments on the first sub table... this would go on and on for each comment on comment.

you can see the cID1000 (comment PK) I can't use this to get the comment because its duplicating the PK...

So, I need to be able to search for the comments on comments eg. I need to be able to search for blogger2, and any comments that were made on his comments.

Someone I know mention using recursion to get the comment on comment info, is this right ?

Hehe, I hope you understand what im asking here the is my first exploration of SQL, so any tips, hints, would be most welcome.

Thanks loads

PS: if there anything you dont understand about what I have written, or what im asking please say so

Spence.you could actually do it with just one table

when you add a comment, add also the id of the original entry

thus when you add a comment on a comment, the original entry id is available, so you can add it to the comment on the comment too

then to retrieve all comments, and comments on comments, for a specific entry, it's just a simple WHERE originalentry=5

so recursion is not required|||you could actually do it with just one table
when you add a comment, add also the id of the original entry
thus when you add a comment on a comment, the original entry id is available, so you can add it to the comment on the comment too
then to retrieve all comments, and comments on comments, for a specific entry, it's just a simple WHERE originalentry=5
so recursion is not required

Hi rudy, thank you very much for your input here.

using one table, I take it you mean just use the Blog_entry tbl, as the attributes in the blog_entry & comment tbls are the same.. the only difference is the actual text...

but, If i were to add to original entryId in the new comment tuple (But in the same table) it would break the PK constraint, there would be duplicate blog_entryID's.. So i need to keep both the comments and the Blog_entry tables seperate..

To able to make a comment on a comment, dont i need something to identify each individual comment ? if so, then im back to square one...

sheesh, this is confusing...|||yikes! (can't read it, not sure i need to, though)

yes you can do it in one table, but you don't have to
create table entries
( id integer not null primary key auto_increment
, name varchar(255) not null
, unique index entriespk (name)
, thread integer null
, foreign key threadstarter (thread) references entries (id)
, replyto integer null
, foreign key replytothread (replyto) references entries (id)
, entry text
);
entries that start a new thread have both thread and replyto NULL

when you reply to an entry, you link to it via replyto, and via thread to the id of the original entry which started the thread

(so replies to the original new entry have the same id in thread and replyto)|||yikes! (can't read it, not sure i need to, though)

yes you can do it in one table, but you don't have to
create table entries
( id integer not null primary key auto_increment
, name varchar(255) not null
, unique index entriespk (name)
, thread integer null
, foreign key threadstarter (thread) references entries (id)
, replyto integer null
, foreign key replytothread (replyto) references entries (id)
, entry text
);
entries that start a new thread have both thread and replyto NULL

when you reply to an entry, you link to it via replyto, and via thread to the id of the original entry which started the thread

(so replies to the original new entry have the same id in thread and replyto)

LoooL rudy,

Cheers for the help m8..

Sweet stuff!

Have a good day.|||The problem is an hierarchical one, each comment has exactly one parent (except the original thread), and 0 to n childs. The standard approach to store such facts is a recursive one, as shown by Rudy. Such a structure, however, isn't to query using standard SQL.

Usual solutions for this are
* recursive T-SQL stored procedure
* bridging

Acutally, the solution presented here, is a bridge solution, however, just a bridge to the original thread. To be able to sort on (sub)threads and to format the solution (e.g. by using indents) youwill need to have the complete path. This is, of cource, redundant, but not a problem because such a path will not change,so there is no danger for inconsistency.

If you can estimate the maximum deepth of a discussion, you can make a structure acoordingly. If you can't, you may also consider to store your information without the path, but dynamically create a temp table with your actual path length and put the data in it for presentation.

I hope I made myself sufficiently clear.|||the solution presented here did not (yet) include queries, so i don't understand why it's a "bridge" solution -- it is a classic hierarchical solution capable of supporting true recursion

further, one does not need to store the path (and i have not allowed for it), so the redundancy only comes into the design if you want it to come into the design, and i don't

further, i am not going to argue whether the thread column (which points from every comment in a thread, no matter at what level, to the thread starter) is redundant, because it isn't

but i agree whole-heartedly about knowing the maximum depth -- this allows you to write a query consisting of N self-joins, which means that no matter where you are in the hierarchy, you can get all nodes below the node you're on, with indentation, in one single query, without a temp table

recursion and/or temp tables are only required if you do not know the maximum depth in the entire hierarchy|||but i agree whole-heartedly about knowing the maximum depth -- this allows you to write a query consisting of N self-joins, which means that no matter where you are in the hierarchy, you can get all nodes below the node you're on, with indentation, in one single query, without a temp table

recursion and/or temp tables are only required if you do not know the maximum depth in the entire hierarchy
I agree on this as a third option.

further, i am not going to argue whether the thread column (which points from every comment in a thread, no matter at what level, to the thread starter) is redundant, because it isn't
Why not? You can get it by recursively selecting the parent of a comment, or not? However, redundancy isn't the problem here, I would even add the entire path by having 1st level comment, 2nd level comment, 3rd le... etc. The problem is the presentation of hierarchical data.|||just because you can get the thread starter recursively does not make it redundant

also, you can achieve indentation without storing the path, by using the N-level self-join|||just because you can get the thread starter recursively does not make it redundant
Then, with all respect, you have your own, wrong definiton of redundancy, see Definition Redundancy (http://www.hyperdictionary.com/search.aspx?define=redundancy)

But, again, I don't have a problem with redundancy here, since a discussion branche will not be moved, probably. My point is, however, to make either the whole path redundant, or nothing and solve it in this case with other means (N views if you know the maximum deepth, or with a recursive T-SQL procedure). Just to have a reference to the root thread will not help at all.

Please note, that my proposal to store the whole path, is something similiar to your N-View approach with the difference, that the complexitity of your execution plan multiplies with every additional level, while the complexity of my execution plan is almost liniar growing.|||heh, that's pretty amusing, citing a dictionary as an authoritative reference for a relational database concept

the reference to the root thread does so help, if you wanted to quickly list all the ids and titles within a thread without regard to indentation

the complexity of my self-join-N-times does not grow at all, unless you change N, which is assumed to be the maximum depth, and if you change the maximum depth, then it's not a maximum, is it

i do realize that storing the path has certain benefits, i just do not happen to value them over my method

for more on paths, read this thread (http://www.sitepoint.com/forums/showthread.php?t=84833)

in fact, one of the participants in that thread has a tutorial on his method here:

Multi-Threaded Message Board Solution (Hierarchical Data without the Adjacency Model) (http://morgankelsey.com/code/multi-threaded_board/)|||heh, that's pretty amusing, citing a dictionary as an authoritative reference for a relational database concept
I hope that - beside the entainment - you got my point: if the same fact (root of a comment) is stored twice (both as [Thread] and [ReplyTo]), it is redundant!
the reference to the root thread does so help, if you wanted to quickly list all the ids and titles within a thread without regard to indentation
... and structure. You can, of cource, sort by time of ID to list all comments in chronological order, but you can't help the user to detect the mutual relationships, except by printing IDs and let the user puzzle by himself.
the complexity of my self-join-N-times does not grow at all, unless you change N, which is assumed to be the maximum depth, and if you change the maximum depth, then it's not a maximum, is it
Please see my previous comment. My point was that the complexity of your preferred solution depends on N.
i do realize that storing the path has certain benefits, i just do not happen to value them over my method
Okay, we don't need to agree in our recommendations, as long as the poster gets a picture of the possiblities and can choose the one fitting to his needs.
for more on paths, read this thread (http://www.sitepoint.com/forums/showthread.php?t=84833)

in fact, one of the participants in that thread has a tutorial on his method here:

Multi-Threaded Message Board Solution (Hierarchical Data without the Adjacency Model) (http://morgankelsey.com/code/multi-threaded_board/)
I took a look, and storing the comment level is a manner to get at least the indentation. To present the thread with its comment-on-comment rel;ationships, however, this method still needs recursion, while my proposal of storing the whole path is non-recursive.

I guess we made our solutions sufficiently clear for the poster; I'm curious of Spencers reaction.|||if the same fact (root of a comment) is stored twice (both as [Thread] and [ReplyTo]), it is redundant!Thread and ReplyTo aren't the same fact!!

:) :) :)|||Either you or me are making a considerable thinking mistake. Please see the following example, just getting three fields: ID, Thread, ReplyTo

1,null,null
2,1,1
3,1,2

In the second row, Thread=ReplyTo
In the third row: Thread = ReplyTo of ReplyTo

In both cases, Thread can be derived from ReplyTo, so it is stored twice, which is called redundant storage.|||please apply 1NF, 2NF, 3NF rules to this example: (ID, Thread, ReplyTo)

i'd be interested to know when it violates an actual normalization rule, not a doktorblue "it looks redundant to me" normalization rule|||From the normalization point of view, your model isn't in the 3rd nf:

A table is in 3NF if: It is in 2NF and it contains no transitive dependencies.

The Thread depends transitively on the ReplyTo. You can remove the Thread column without removing the Thread information.|||you misunderstand transitivity, it does not work across rows, it is a rule for columns within a row|||Do either of you see the humor or irony in trading post after post on the subject of redundancy? ;)|||I don't anymore ... :cool:|||i see the irony, but not the humour

:)

okay, another recursion example

- each employee row contains the foreign key of his/her supervisor
- each employee must work in the same department as his/her supervisor

where would you store the information about which department an employee is in?|||There is just one employee table, containing employees and their supervisiors, right?! I would store the department information in a associative entity DepEmp(DepID, EmpID), containing just the employess which are the bosses of their department. However, I'm not sure whether this will work nice, so I'd probably add some redundancy here by

* repeating the department for every employee
* or adding a reference to every department boss to every employee

What had you in mind?|||thank you, doktorblue, you have just copied my design for the threads

* repeating the [department]thread for every [employee]post
* or adding a reference to every [department boss]thread starter to every [employee]post

i'm sure this will "work nice" as you say, even though you yourself don't like it because it's "redundant"|||Do you realize that you are admitting that your design contains redundancy?

Regarding my "work nice" comment: the final structure heavely depends on the usage. If you are frequently using the entire suipervisor hierarchy, you will get another design than if you are using only the direct supervisor and department information. If you don't use regularly department information, I would not even consider to duplicate the data.|||no, i do not realize it, because as i said earlier, there is no redundancy

but it is very nice to see you adopt the same design

:) :) :)|||So, according to your logica, the total order amount on order level isn't redundant with the sum of the amount of indivudual order lines? Its not on the same row, is it? I guess 1st class informatica students can tell you better.

Or, according to your same logica, the following table isn't inconsistent, because inconsistency can only happen if there is redundant data

ID, ReplyTo, Thread
1, null, null
2, 1, 1
3, 1, 2

Comment 3, however, belongs to both thread 1 and 2, which can't be.

I hope for you, or better for your clients, that they don't have any complex problems with redundancy.|||inconsistency can only happen if there is redundant dataonly? inconsistency can happen without redundancy, too

if the database cannot enforce the consistency rules, then the application developer had better assume this responsibility

are you suggesting i am not a good enough developer to cover for the weaknesses of current database products?

;)

you know what, doktorblue? i like you

i like you a lot

i like the way you think

you are going on my buddy list

:cool:|||you know what, doktorblue? i like you

i like you a lot

i like the way you think

you are going on my buddy list
:cool:
Don't get personal, will you?!

:D|||What!? That's it?

C'mon, please start fighting again. It's so refreshing for us Yanks to watch an international conflict in which we AREN'T involved...|||Ha Rudy, there is somebody asking for troubles! Let's get him!

:D:D|||the blindman is actually one o' the good guys

he has helped me a hunnert times

:cool:|||Ha! Bullcr@.p! You haven't needed help. But nice of you to say, though. I like posting on this board and I've learned a lot from reading everybody's posts, especially yours.|||Oh heck, I can usually get Rudy pretty riled up. Maybe we can get this show back on the road if I can find the appropriate taunt...

Heavens, it was just getting fun, watching the "mud cannons" getting loaded with huge quantities of various forms of "organic fertilizer" on both sides of the Atlantic!

-PatP|||Oh, its easy to get Rudy riled. You know those Canadians have no sense of humor. That's why all their decent comics come down here to the U S of A.

Now, as far as jibes at people from the Netherlands...I'm drawing a complete blank. What do they do over there, anyway?|||Now, as far as jibes at people from the Netherlands...I'm drawing a complete blank. What do they do over there, anyway?Diamonds, drugs, sex, and flowers for the most part. Delft does gorgeous porcelin.

I don't want to get them riled up personally though, I want to keep them on the database track... That's lots more fun to watch. I was thinking about throwing in a few comments about the advantages of using Excel as a database manager to avoid all those problems that people have with constraints and datatypes.

-PatP|||Why, Excel is a great database application. You don't have to worry about a lot of indexes and constraints and crap, which makes it much more flexible for enterprise application development than a rigid system like SQL Server.

Plus, it's got pivot tables that will do dynamic crosstabs. SQL Server can't even touch that!

;)|||Yeah, with those kind of obvious benefits, how can you go wrong?

-PatP|||Diamonds, drugs, sex, and flowers for the most part. Delft does gorgeous porcelin.-PatP
Fox News told you Americans also recently about massive killings of babies in clinics, and you forgot the clogs and cheese.|||Hey, I may be American, but I DON'T watch Fox News!

I rode my bike through the Netherlands about 20 years ago. I remember you also have a lot of wind. A LOT of wind, and it was all headwind.|||Fox News told you Americans also recently about massive killings of babies in clinics, and you forgot the clogs and cheese.Yeah, but who doesn't like killing babies?

Don't the clogs and cheese just kind of grow there, like mushrooms and bicycles?

Actually, I have some really interesting memories of a bar called "The Three Flies" in Amsterdam. Someday I need to go back to see if it is still there!

-PatP

Do I need a cursor here

I have a web page where users can change information about themselves
and submitted to a database. The database holds these changes in the
table UserChanges. When the user completes the changes the database for
the website is updated and changes are reflected right away. The user
can then go in again and make further changes thus creating another
record in the UserChanges table. Once a day these changes are brought
down to our main database. I would like to bring down only the latest
record for each users instead of bring down all their records they
created that day. The problem is that one of the fields is a bit field
that indicates if the email address was changed or not. This could
cause problems if the user changed their email address on a previous
record they created but not on the last one.
Example
ID PersonID EmailAddress EmailChange Downloaded
1 200 test@.test.com False False
2 200 blank@.blank.com True False
3 200 blank@.blank.com False False
What I want to do is after the user submits the record
1.Check to see if they have any previous record that have not been
downloaded yet
2.If previous records exists check to see if any of the records email
change flag is set to True.
3.If it is, then update the email change flag to True in the last
record.
Is this possible to do with out using a cursor?A trigger should be able to do it:
create trigger tri_UserChanges on UserChanges after insert
as
if @.@.rowcount = 0
return
update u
set
EmailChange = 1
from
UserChanges u
join inserted i on i.ID = u.ID
where exists
(
select
*
from
UserChanges u2
where
u2.PersonID = i.PersonID
and u2.ID <> i.ID
and u2.EmailChange = 1
and u2.Doenloaded = 0
)
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
"rocky20" <goldbond_8@.hotmail.com> wrote in message
news:1140289185.842651.4140@.o13g2000cwo.googlegroups.com...
I have a web page where users can change information about themselves
and submitted to a database. The database holds these changes in the
table UserChanges. When the user completes the changes the database for
the website is updated and changes are reflected right away. The user
can then go in again and make further changes thus creating another
record in the UserChanges table. Once a day these changes are brought
down to our main database. I would like to bring down only the latest
record for each users instead of bring down all their records they
created that day. The problem is that one of the fields is a bit field
that indicates if the email address was changed or not. This could
cause problems if the user changed their email address on a previous
record they created but not on the last one.
Example
ID PersonID EmailAddress EmailChange Downloaded
1 200 test@.test.com False False
2 200 blank@.blank.com True False
3 200 blank@.blank.com False False
What I want to do is after the user submits the record
1.Check to see if they have any previous record that have not been
downloaded yet
2.If previous records exists check to see if any of the records email
change flag is set to True.
3.If it is, then update the email change flag to True in the last
record.
Is this possible to do with out using a cursor?|||Just as a side note, you may want to reconsider your design. As you
can obviously see, a bit field doesn't really tell you much :) I'm
also assuming that "previous" has meaning to you, because a rows in a
table don't really have an order to them.
Here's a quick-and-dirty stab at it:
DECLARE @.s TABLE (ID int, PersonID int, EmailAddress varchar(20),
EmailChange bit, Downloaded bit)
INSERT INTO @.s
SELECT 1, 200, 'test@.test.com', 0, 0
UNION ALL
SELECT 2, 200, 'blank@.blank.com', 1, 0
UNION ALL
SELECT 3, 200, 'blank@.blank.com', 0, 0
UNION ALL
SELECT 4, 500, 'test@.test.com', 0, 0
UNION ALL
SELECT 5, 500, 'blank@.blank.com', 1, 0
UNION ALL
SELECT 6, 500, 'blank@.blank.com', 0, 1
SELECT *
FROM @.s
SELECT DISTINCT s.ID, s.PersonID, s.EmailAddress,
EmailChange = COALESCE(s2.EmailChange, s.EmailChange),
s.Downloaded
FROM @.s s LEFT JOIN @.s s2 ON s.PersonID = s2.PersonID
AND s.ID > s2.ID
AND s2.EmailChange = 1
AND s2.Downloaded = 0
WHERE s.ID IN (SELECT MAX(ID)
FROM @.s
WHERE Downloaded=0
GROUP BY PersonID)
HTH,
Stu|||rocky20 wrote:
> I have a web page where users can change information about themselves
> and submitted to a database. The database holds these changes in the
> table UserChanges. When the user completes the changes the database for
> the website is updated and changes are reflected right away. The user
> can then go in again and make further changes thus creating another
> record in the UserChanges table. Once a day these changes are brought
> down to our main database. I would like to bring down only the latest
> record for each users instead of bring down all their records they
> created that day. The problem is that one of the fields is a bit field
> that indicates if the email address was changed or not. This could
> cause problems if the user changed their email address on a previous
> record they created but not on the last one.
> Example
> ID PersonID EmailAddress EmailChange Downloaded
> 1 200 test@.test.com False False
> 2 200 blank@.blank.com True False
> 3 200 blank@.blank.com False False
>
> What I want to do is after the user submits the record
> 1.Check to see if they have any previous record that have not been
> downloaded yet
> 2.If previous records exists check to see if any of the records email
> change flag is set to True.
> 3.If it is, then update the email change flag to True in the last
> record.
> Is this possible to do with out using a cursor?
It doesn't seem like you'll need a cursor to do this. I'm not clear
about a few things though. Firstly what is/are the keys in this table?
Secondly how do we know which row is the latest? Don't use an IDENTITY
column to track the latest row. Add a DATETIME column to do that.
Finally, what's the point of the EmailChange column? It looks redundant
to me, given that you preserve the history of the email values anyway.
I think you should drop EmailChange.
Assuming you have a column to indicate the date and time of the change
you can retrieve the latest version like this:
/* Get the new email address only where the
latest version hasn't been downloaded */
SELECT emailaddress
FROM UserChanges AS U
WHERE changed_datetime =
(SELECT MAX(changed_datetime)
FROM UserChanges
WHERE personid = U.personid
HAVING MAX(changed_datetime) =
MAX(CASE WHEN downloaded = 'False' THEN changed_datetime END));
AND downloaded = 'False' /* should be 0? */
(untested)
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
--|||Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications. It is very hard to debug code when you do not let us
see it.
Doesn't that hit you as "a bit" redundant? (sorry, had to do the pun)
Just over-write the old email with the new one, based on a timestamp
One of your major problems is that you do not know that fields and
records are not part of SQL; columns and rows are different creatures.
When someone logs into the routine. check to see if they have data in
the working tables. If not, copy all the old data over to your working
table. Let the user UPDATE the working data on a column by column
basis in the working database. Or if you really need to keep every
change, then add that timestamp to get the last copy.
Then do your data scrubbing and replace the old data with the new. I
would guess that a VIEW with INSTEAD OF TRIGGERs would help.
No cursors needed.

Do I HAVE to have IIS installed to use SQL Server Express 2005? and do I have to install SQLE2K5

Hi,

I'm an absolute beginner trying to learn SQL and Visual Basic/Visual Web developer.

I've downloaded SQL Express 2005 and want to install it on my laptop (Dell XPS, 2gb Ram) so that I can start doing the tutorials (for SQL and VB/VWS). I dont want any connection to the internet - i only want to have it on my local pc.

I'm confused as I 'm sure I read that I dont need to have the IIS service running, and that it wont require a server...however, I keep reading that IIS, and a server is a MUST, and for installing SQLE2005.

I'm so confused, and frustrated that I dare'nt install SQLE2005...

...Can anyone put me out of my misery?

All advice greatly appreciated.

SQLEB

Hi,

If you have downloaded SQL Express edition with Advance Services then only you require to have IIS installed on your system Refer http://download.microsoft.com/download/b/d/1/bd1e0745-0e65-43a5-ac6a-f6173f58d80e/RequirementsSQLEXP2005Advanced.htm

and if you have installed SQL Express w/o Advance Services you don't require to have IIS Refer http://download.microsoft.com/download/b/d/1/bd1e0745-0e65-43a5-ac6a-f6173f58d80e/RequirementsSQLEXP2005.htm

HTH

Hemantgiri S. Goswami

|||No, IIS is NOT required for the use and operation of SQL Server.sql

Do CR/LF get stored in a text column

I am capturing user comments in a textarea field on a web form and then storing the comments in a text column in a DB table. I would like to know if the carriage returns and linefeeds get stored in the database (MS SQL 2000)?I would say yes...

USE Northwind
GO

CREATE TABLE myTable99 (col1 varchar(8000))
GO

DECLARE @.x varchar(8000)
SELECT @.x = 'Wasted away again in
Margaritaville'
INSERT INTO myTable99 (Col1) SELECT @.x
SELECT @.x
GO

DROP TABLE myTable99
GO|||[QUOTE][SIZE=1]Originally posted by Brett Kaiser
I would say yes...
[/quote

I second that.sql

DMZ Web Server and Internal SQL Server

I have a .net web application that is running on a w2k server located in our
DMZ it has a private address. The SQL server is also running on a w2k server
but it is located in our private network. I have opened port 1433 on the
firewall from the DMZ to Lan and from Lan to DMZ. Since neither server knows
the other excist I have added an entry in the local host file of the
webserver in the DMZ that points to the internal SQL server. Does an entry
also need to be placed in the host file of the SQL server? Also what would
be the proper connection string in the web.config to link these two servers
together? And would anything need to be done on the SQL server?
Thanks
You may need to specify the IP address of the firewall and 1433 to allow
the connection to succeed, since the client won't know how to resolve the
netbios or host name of the server.
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.
sql

DMZ Web Server and Internal SQL Server

I have a .net web application that is running on a w2k server located in our
DMZ it has a private address. The SQL server is also running on a w2k server
but it is located in our private network. I have opened port 1433 on the
firewall from the DMZ to Lan and from Lan to DMZ. Since neither server knows
the other excist I have added an entry in the local host file of the
webserver in the DMZ that points to the internal SQL server. Does an entry
also need to be placed in the host file of the SQL server? Also what would
be the proper connection string in the web.config to link these two servers
together? And would anything need to be done on the SQL server?
ThanksYou may need to specify the IP address of the firewall and 1433 to allow
the connection to succeed, since the client won't know how to resolve the
netbios or host name of the server.
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.

dll & Web Service in VB.Net 2005 Stored Procedure

Hi,
I am writing a vb.net2005 program that needs to create a stored procedure
with SqlServerProject Template.
Now, I have two questions for this stored procedure.
1) How can I import and execute the .dll in this Stored Procedures?
2) How can I connect the Web Service and get the result in this Stored
Procedures?
---
Partial Public Class StoredProcedures1
<Microsoft.SqlServer.Server.SqlProcedure()> _
Public Shared Sub GetTable()
Dim strSQL As String = "SELECT * FROM Table1"
Using conn As New SqlConnection("context connection=true")
Using cmd As New SqlCommand
With cmd
.Connection = conn
.CommandText = strSQL
.CommandType = CommandType.Text
conn.Open()
SqlContext.Pipe.ExecuteAndSend(cmd)
.Connection.Close()
End With
End Using
End Using
End Sub
End Class
---
Thanks!"James Wong" <cphk_msdn@.nospam.nospam> wrote in message
news:u155F84iGHA.1260@.TK2MSFTNGP05.phx.gbl...
> Hi,
> I am writing a vb.net2005 program that needs to create a stored procedure
> with SqlServerProject Template.
> Now, I have two questions for this stored procedure.
> 1) How can I import and execute the .dll in this Stored Procedures?
You need to deploy the assembly into your database (CREATE ASSEMBLY) and
then add a stored procedure referencing the method in the assembly (CREATE
PROCEDURE). Visual Studio will do this for you if you right-click on the
project and select Deploy.
Here's how you would do it by hand:
CREATE ASSEMBLY [SqlServerProject2]
FROM 'c:\SqlServerProject2.dll'
WITH PERMISSION_SET = SAFE
GO
CREATE PROCEDURE [GetTable]
AS
EXTERNAL NAME
[SqlServerProject2].[SqlServerProject2.StoredProcedures].[GetTable]

> 2) How can I connect the Web Service and get the result in this Stored
> Procedures?
>
From the web service just connect to the database and execute it with a
SqlCommand.
David|||Hi David,
sorry, i think that u were misunderstood my problem.
My Stored Procedure "StoredProcedures1.GetTable()" is need to call another
.dll & web service to get some value.
Thanks
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> glsD:O3xF9H5iGHA.45
04@.TK2MSFTNGP05.phx.gbl...
> "James Wong" <cphk_msdn@.nospam.nospam> wrote in message
> news:u155F84iGHA.1260@.TK2MSFTNGP05.phx.gbl...
> You need to deploy the assembly into your database (CREATE ASSEMBLY) and
> then add a stored procedure referencing the method in the assembly (CREATE
> PROCEDURE). Visual Studio will do this for you if you right-click on the
> project and select Deploy.
> Here's how you would do it by hand:
> CREATE ASSEMBLY [SqlServerProject2]
> FROM 'c:\SqlServerProject2.dll'
> WITH PERMISSION_SET = SAFE
> GO
> CREATE PROCEDURE [GetTable]
> AS
> EXTERNAL NAME
> [SqlServerProject2].[SqlServerProject2.StoredProcedures].[GetTable]
>
> From the web service just connect to the database and execute it with a
> SqlCommand.
> David
>|||"James Wong" <cphk_msdn@.nospam.nospam> wrote in message
news:eWXrxU5iGHA.412@.TK2MSFTNGP05.phx.gbl...
> Hi David,
> sorry, i think that u were misunderstood my problem.
> My Stored Procedure "StoredProcedures1.GetTable()" is need to call another
> .dll & web service to get some value.
>
To use another .dll add a reference to your database project. To use a web
service add a web reference.
David|||Hi David,
1) For DLL, VS.Net 2005 is not allow me to import a new References in
SqlServerProject Template.
2) For Web Service, it will occur error when running.
----
--
Partial Public Class StoredProcedures
<Microsoft.SqlServer.Server.SqlProcedure()> _
Public Shared Sub SPWithWebService()
Dim WebService As New SqlServerProject1.localhost.Service
Dim msg As String = CStr(WebService.HelloWorld())
SqlContext.Pipe.Send(msg)
End Sub
End Class
----
--
Thanks
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> glsD:OqYxDh5iGHA.34
96@.TK2MSFTNGP04.phx.gbl...
> "James Wong" <cphk_msdn@.nospam.nospam> wrote in message
> news:eWXrxU5iGHA.412@.TK2MSFTNGP05.phx.gbl...
> To use another .dll add a reference to your database project. To use a
> web service add a web reference.
> David
>|||Hello James,
When you are using the VS SQL Server Project you are restricted to what
assemblies you can reference. This behavior is by design. However, you
could create a normal class library project, add references as per usual
and then manually create your assembly through CREATE ASSEMBLY. Make sure
your referenced
assemblies are in the same directory as your user assembly.
If you want to use the VS SQL Server Project you can create the assembly
you want to reference in the database before you try to reference it. You
need do a manual CREATE ASSEMBLY against the DLL you want.
As for Web service , you will also need to use the CLR SDK tool SGEN.EXE to
create and register a helper assembly for the assembly generated by WSDL,
as described in:
http://blogs.msdn.com/sqlclr/archiv.../25/Vineet.aspx
Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.

dling xml file over SSIS possible?

i need to download a file over HTTP and get a xml file then upload it into SSIS.

Only 2 methods are available in SSIS: FTP and web method. How come there is no HTTP transfer task in SSIS? pretty strange.

then i found a script at

http://www.sqljunkies.com/howto/49e823fd-d126-4134-893d-1fd8bd3bd3ba.scuk

What kind of SSIS tasks do i need to perform this operation? I got the script above but I don't know where to key it in.

It goes in a script task.

-Jamie

|||ok i did the script task but how do i pass the variables remoteuri and the local filename to store to the script task?|||

Put them into the ReadOnlyVariables property of the script task.

http://www.google.co.uk/search?hl=en&q=task+readonlyvariables&btnG=Search&meta=

-Jamie

|||

the readonly variables i shld put

"remoteurl, localfilename"

then how i should set the remote url to http://yahoo.com/test1.xml

localfilename to "c:\test1.xml"?

i look at the urls but not much of help....make me more confused.

Ditinct Rows Within Grouped Dataset

Hi
Here's one that is puzzling me!!
We have 2 tables, a category table and a sub category table, what we
need is to display a web control that shows unique entries within the
recordset so if the recorset returns the follow
Accomodation Hotels
Accomodation B&B
Accomodation Self Catering
Restaurants American
Restaurants Indian
we would then see
Accomodation Hotels
B&B
Self Catering
Restaurants American
Indian
To create the data we are using a stored procedure so woncered if this
type of array could be returned direct from that? Sort of DIsTINCT
within the column. Or would we have to do it in code into an array?
We would then have a web control that allows each of the trees to be
collapsed / expanded
Cheers
shaunshaunsizen@.msn.com wrote:
> Hi
> Here's one that is puzzling me!!
> We have 2 tables, a category table and a sub category table, what we
> need is to display a web control that shows unique entries within the
> recordset so if the recorset returns the follow
> Accomodation Hotels
> Accomodation B&B
> Accomodation Self Catering
> Restaurants American
> Restaurants Indian
> we would then see
> Accomodation Hotels
> B&B
> Self Catering
> Restaurants American
> Indian
> To create the data we are using a stored procedure so woncered if this
> type of array could be returned direct from that? Sort of DIsTINCT
> within the column. Or would we have to do it in code into an array?
> We would then have a web control that allows each of the trees to be
> collapsed / expanded
> Cheers
> shaun
If you have a control driving the results, then wouldn't you want the
results to include the first column in all cases, so the code knows what
tree to place the second column? If you key off the first column, then
the order of the results is irrelevant and you can remove any ORDER BY
clauses in the SQL, saving additional server resources.
--
David Gugick
Quest Software
www.imceda.com
www.quest.com|||On 4 Oct 2005 06:59:13 -0700, shaunsizen@.msn.com wrote:
>Hi
>Here's one that is puzzling me!!
>We have 2 tables, a category table and a sub category table, what we
>need is to display a web control that shows unique entries within the
>recordset so if the recorset returns the follow
>Accomodation Hotels
>Accomodation B&B
>Accomodation Self Catering
>Restaurants American
>Restaurants Indian
>we would then see
>Accomodation Hotels
> B&B
> Self Catering
>Restaurants American
> Indian
Hi shaun,
Typically, presentation issues should be handled at the front end (it's
not called "presentation tier" for nothing <g>). Use a query such as the
one below to return the data. Then use the frontend (where you'll have
to loop through the rows anyway) to blank out the first column if the
value is unchanged from the previous row.
SELECT FirstCol, SecondCol
FROM YourTable
ORDER BY FirstCol
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)