hi,
I want to execute two user created stored procedures in a multithreaded manner in ms-sql server . Can some tell me how i can do that.
Thanks in advance.
Regards,
ManpreetOriginally posted by chugh_manpreet
hi,
I want to execute two users
Ya know....I've ALWAYS wanted to do that...
but then who would use the system?
Well, I guess if you limit to just 2....
To answer though...every exec od a sproc is threaded...it's not serial...
Are you talking about withIN the sproc itself?|||By its design the SQL engine is single-process multi-threaded. A series of asynchronous calls always result in multi-threaded processing on the server. I guess I have the same question as Brett, - are you talking about multi-threading within a stored procedure? And why do you need that?|||No chuckles?
Anyway...To thread in a sproc, create jobs and start'em...
They'll be aysyncronus and thread...
although I swear (and can't confirm) that using xp_cmdshell does...even though everywhere I read it's synchronous...(I couldn;t explain some blocking awhile back...still don't know why)|||I can! In pre-SP3 era when everybody and their mother was firing it while trying to be cute, and coming back with OSQL through it. There was a hell of a blocking going on. It is actually due to the nature of OSQL, rather than xp_cmdshell, but those who were using it didn't even bother to check into the possibility of it to occur, and we were stuck to debug, and what's most frustrating, - explain, how it happened. Now they are all squealing, because they can't do any more damage :)|||Yeah, but in a sproc if you do...
DELETE FROM myTable99
master xp_cmdshell 'bcp command...for myTable99'
Wouldn't you expect the DELETE to be completed before the bcp?
It actually launches 2 spids (of course) but spid 1 (the execution of the sproc and the delete) blocks spid 2, the execution of xp_cmdshell
huh?
Is this a fundamental thing I'm totally missing?|||hi,
thanks for your reply. Actually i have written two stored procedures which call a dll(non-sql) funtion. Now i want these two stored procedures simultaneously in a multi threaded manner.
I tried executing it like this
exec sp_Procedure1
exec sp_Procedure2
but the second stored procedures is not getting executed untill first gets executed. I want both the stored procedures execute simultaneously in a multi threaded manner.
Any inputs in this regard are welcome
Regards,
Manpreet
Originally posted by Brett Kaiser
No chuckles?
Anyway...To thread in a sproc, create jobs and start'em...
They'll be aysyncronus and thread...
although I swear (and can't confirm) that using xp_cmdshell does...even though everywhere I read it's synchronous...(I couldn;t explain some blocking awhile back...still don't know why)|||hi,
thanks for your reply. Actually i have written two stored procedures which call a dll(non-sql) funtion. Now i want these two stored procedures simultaneously in a multi threaded manner.
I tried executing it like this
exec sp_Procedure1
exec sp_Procedure2
but the second stored procedures is not getting executed untill first gets executed. I want both the stored procedures execute simultaneously in a multi threaded manner.
Any inputs in this regard are welcome
Regards,
Manpreet
Sorry for bumping an old post, but I am looking to do the same thing, pseudocode below for those that want to know why.
---------------
CREATE PROC COMPANY_ISP
@.company_id INT
AS
INSERT COMPANY(COMPANY_ID)
SELECT @.company_id
EXEC COMPANY_REBUILD_INDEX @.company_id
RETURN
---------------
The proc is actaully a little longer then the above but you get the idea, COMPANY_REBUILD_INDEX takes about 10 seconds to run and waiting for it to process is not critical to the web page calling this proc, but rather a hindrance since the application needs to wait for this process to finish.
I suppose the application could call this proc separately behind the scenes somehow, I can talk with the .Net developer to see what he thinks, but thought it would be nice to have a solution that would work in SQL. xp_cmdshell? I thought that was only for DOS commands, does not seem like it is the solution I am looking for.
Thanks,
-John|||Applicaton design issues should be handled by the application designers.
If I want an application to run two stored procedures simultaneously, then I expect the application to thread the executions. If the applicaton wants serial execution, then serialize them.
You should consider not only the near term results, but also the long term maintainability. Cute solutions usually compromise the latter for the former.
For your solution, it seems you are expecting an inherently single threaded application (browser) to handle asynchronous communications.
The reindex should probably be done via a job that detects the request (writes a flag somewhere), and then you can write a control that polls a progress table for completion. This way, a user can refresh their browser w/o losing (needing to maintain) the state of the ReIndex.|||first thing that bothers me here is the fact that you are reindexing after every insert. why not just reindex the table overnight in a job.
I wonder what effect of (and I would never try this exepcially with a 10 second execution) is of putting your reindex into an insert trigger (which I use sparingly) on the table. Would the ASP page go about it's merry business before the trigger completed or would you still get held up on your page waiting on the trigger to execute.
By the way if your trying to insert and reindex at the same time on the same table I do imagine there would be some blocking even if you used multi-threading as implimented in JAVA.|||first thing that bothers me here is the fact that you are reindexing after every insert. why not just reindex the table overnight in a job.
I wonder what effect of (and I would never try this exepcially with a 10 second execution) is of putting your reindex into an insert trigger (which I use sparingly) on the table. Would the ASP page go about it's merry business before the trigger completed or would you still get held up on your page waiting on the trigger to execute.
By the way if your trying to insert and reindex at the same time on the same table I do imagine there would be some blocking even if you used multi-threading as implimented in JAVA.
I was curious myself about the trigger, if I created a trigger would the calling proc wait for the trigger to finish before returning to the application?
The reason I need to incrementally rebuild the index (I currently have an overnight job btw) is that when a user adds a new company they need a way to be able to search for it later, our search page does a phonetic search for companies (in case they misspelled it), if the job only runs nightly then the user would have to wait until the next day to see the company in the search results. This is an intranet application for about 50 users and adding companies is not going to be a very frequent process so I am not concerned with the strain on the server.
For anyone interested in implementing a phonetic search here is the pseudocode:
1. Split the company name into words
2. Translate each word into its phonetic representation (soundex is one option albeit bad - I implemented a double metaphone translation)
3. Remove duplicate phonetic words per company
4. Aggregate the occurrences of each phonetic
5. Give a score based on (in)frequency of each phonetic to aid in improved search results.
6. Increase the score for exact word matches
7. Increase the score for exact phrase matches
8. Return the results by score descending
There are about 40k unique phonetic words that are created as a result of this (out of a pool of close to a million companies).|||I was curious myself about the trigger, if I created a trigger would the calling proc wait for the trigger to finish before returning to the application?
Try it and let me know.|||Maybe I'm missing something really fundamental, but what on earth are you trying to accomplish? Reindexing is a performance issue, it can improve the internal structure of an index after massive inserts or deletes. Reindexing had better not have any effect at all on what rows are visible in the table!
Did I miss a meeting, or is this entire discussion moot?
-PatP|||Pat,
from what it sounds like his sp (COMPANY_REBUILD_INDEX) is a misnomer. It sounds like he is really doing all of his processing for his phonetic thingy majiggy and not a DBCC DBREINDEX. (which sounds like a lot of trouble to go through to tame some bad data entry).|||It's a nessary evil, we have over 100k council members that enter their own data, the search is used to help normalize the company name that they enter into their job history profile (each member can enter multiple job profiles based on their work history). The search had to be as flexible as possible and had to search on a per word basis and handle spelling mistakes, I think in the next version they want us to use a thesaurus to be able to match firm to company but in my mind THAT is going way overboard.|||did you try the trigger thing. i assumed you were't really doing a reindex as in dbcc. how did it go?
Showing posts with label ms-sql. Show all posts
Showing posts with label ms-sql. Show all posts
Friday, March 30, 2012
Monday, March 19, 2012
MSSQLServer Service terminated unexpectedly MS-SQL 2000 with SP4
MSSQLServer service terminated unexpectedly
We are running SQL Server 2000 with SP4. This server has been running for the past six months with no SQL server problems. A Max of 10 users have access to the application running on this server.
The Event Viewer Log shows the
Source: MSSQLServer
Category: (2)
Type: Error
EventID: 17052
Description: The MSSQLSERVER service terminated unexpectedly.
No other error messages were seen in the SQL server log or windows event viewer.
The time when it terminated was not during peak load/activity.
The server is Windows 2003 with SP1. Any help would be appreciated.Are there any *.mdmp or *.txt files in the \mssql\log directory where the errorlogs reside?|||Sorry for my late response. There are a few mdmp and txt files in the log directory but all the log files have dates prior ( probably couple of months old file) to the day the crash occurred.
Wednesday, March 7, 2012
MS-SQL: wheres auto increment?
It's been a long time since I've had to check an index for the highest value, then add 1, to create a new unique key. These past few years, it seems this is usually done for you. But now that I'm working with MS-SQL, I don't see it. Is it there? It's doesn't seem to be inherent in the definition.you will need to set the field/column to be an identity column
Make it an integer - don't allow nulls - then, depending on which app you're using to create it - set the column to be an identity column|||Set Identity Seed = yes for the column|||Identity Seed is numeric and indicates the starting number to use for Auto Numbering...It cannot be set to "Yes"|||I think he meant "Identity". You set the Identity to yes and identity seed is the starting number.|||Yea, sorry, it was supposed to be "Identity"
ms-sql to sapdb
hi there,
we have a ms-sql server 7.0 and want to migrate to sapdb on unix.
are there any tools to convert a ms-sql to sapdb, including
stored procedures, keys and views?
tia
stefanHi
I have never heard of sapdb, but you should ask your supplier. Microsoft
provides many tools to port to SQL Server, and there are alot of other third
party tools that will also help, but they are obviously not going to provide
you with assistance if you wish to move to a rival vendor.
John
"stefan" <stefanluedecke@.gmx.de> wrote in message
news:ea0e4b2e.0308022043.73010886@.posting.google.c om...
> hi there,
> we have a ms-sql server 7.0 and want to migrate to sapdb on unix.
> are there any tools to convert a ms-sql to sapdb, including
> stored procedures, keys and views?
> tia
> stefan|||"John Bell" <jbellnewsposts@.hotmail.com> wrote in message news:<3f2cc1e9$0$15038$ed9e5944@.reading.news.pipex.net>...
> Hi
> I have never heard of sapdb, but you should ask your supplier. Microsoft
> provides many tools to port to SQL Server, and there are alot of other third
> party tools that will also help, but they are obviously not going to provide
> you with assistance if you wish to move to a rival vendor.
hi john,
sapdb (http://www.sapdb.org) is an open source db from sap.
with the export tool from ms-sql we are able to export the pure
data, but none of the things arround the db, such as stored procedures,
trigger etc.
with access (the db-frontend from microsoft) we could also transfer
data.
microsoft provide really a lot of tools to migrate to sql-server, but not the
other way ;-))
thank you
stefan|||Ray Higdon <rayhigdon@.higdonconsulting.com> wrote in message news:<3f2d13c1$0$195$75868355@.news.frii.net>...
> Are you talking about transferring data to an SAP database? If so, hehe,
> no simple answer exists. SAP uses business and function calls for all
> data entry, you input data by calling their functions, not going to the
> underlying data. One tool I have seen out there to help with this
> process is the Business Connector (read more here
> http://www.plurb.com/ebXML/Scalable...operability.pdf) it uses XML
> in and out and you can call it by a web service and run it as a Windows
> service. I have a buddy that wrote a paper on the BC, if this is what
> you are talking about I'll see if it is published yet and get you a copy
> if you want.
hi ray,
thank you for your help, but we can transfer data. the problem is
to transfer stored procedures, keys and trigger. and it would be a
few weeks work to do it manually.
stefan|||Hi
It is highly unlikely that your database will support T-SQL, and if it does
it is probably violating copyright. Therefore about all you can do is to
script them and maybe write a few macros to globally convert the syntax,
then it will be a manual task to finish it off.
John
"stefan" <stefanluedecke@.gmx.de> wrote in message
news:ea0e4b2e.0308031042.552d0eb0@.posting.google.c om...
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:<3f2cc1e9$0$15038$ed9e5944@.reading.news.pipex.net>...
> > Hi
> > I have never heard of sapdb, but you should ask your supplier. Microsoft
> > provides many tools to port to SQL Server, and there are alot of other
third
> > party tools that will also help, but they are obviously not going to
provide
> > you with assistance if you wish to move to a rival vendor.
> hi john,
> sapdb (http://www.sapdb.org) is an open source db from sap.
> with the export tool from ms-sql we are able to export the pure
> data, but none of the things arround the db, such as stored procedures,
> trigger etc.
> with access (the db-frontend from microsoft) we could also transfer
> data.
> microsoft provide really a lot of tools to migrate to sql-server, but not
the
> other way ;-))
> thank you
> stefan
Saturday, February 25, 2012
MS-SQL SQL to Access SQL
I have some SQL that looks like this (see below) it is for MySQL and MS-SQL (so I am told) however I need to impliment the data structure in Access. I have written a VBscript Class that converts and adds all the tables etc but the constraints are not going to work.
My script can strip out the "[dbo]." that access doesn't need but what I need to get any further is to reconstruct this SQL (See below) so that I have SQL that does the same thing in Access.
Once there I can create a few string manipulations to transform it and bingo job done.
I need some help as I am very weak in the ALTER TABLE department.
ALTER TABLE [dbo].[openwiki_macrohelp] ADD
CONSTRAINT [DF__openwiki___macro__7908F585] DEFAULT (1) FOR [macro_builtin],
CONSTRAINT [DF__openwiki___macro__79FD19BE] DEFAULT (0) FOR [macro_numparams],
CONSTRAINT [DF__openwiki___macro__7AF13DF7] DEFAULT ('No description available') FOR [macro_description],
CONSTRAINT [DF__openwiki___macro__7BE56230] DEFAULT ('None') FOR [macro_param1],
CONSTRAINT [DF__openwiki___macro__7CD98669] DEFAULT ('None') FOR [macro_param2],
CONSTRAINT [DF__openwiki___macro__7DCDAAA2] DEFAULT ('None') FOR [macro_param3],
CONSTRAINT [DF__openwiki___macro__7EC1CEDB] DEFAULT ('None') FOR [macro_comment]
GOI can't see why you'd need any code in MS-Access. Just set the default values specified using the MS-Access GUI, and it should handle the rest for you.
While MS-Access doesn't scale well compared to MS-SQL, it certainly is easier to use!
-PatP
My script can strip out the "[dbo]." that access doesn't need but what I need to get any further is to reconstruct this SQL (See below) so that I have SQL that does the same thing in Access.
Once there I can create a few string manipulations to transform it and bingo job done.
I need some help as I am very weak in the ALTER TABLE department.
ALTER TABLE [dbo].[openwiki_macrohelp] ADD
CONSTRAINT [DF__openwiki___macro__7908F585] DEFAULT (1) FOR [macro_builtin],
CONSTRAINT [DF__openwiki___macro__79FD19BE] DEFAULT (0) FOR [macro_numparams],
CONSTRAINT [DF__openwiki___macro__7AF13DF7] DEFAULT ('No description available') FOR [macro_description],
CONSTRAINT [DF__openwiki___macro__7BE56230] DEFAULT ('None') FOR [macro_param1],
CONSTRAINT [DF__openwiki___macro__7CD98669] DEFAULT ('None') FOR [macro_param2],
CONSTRAINT [DF__openwiki___macro__7DCDAAA2] DEFAULT ('None') FOR [macro_param3],
CONSTRAINT [DF__openwiki___macro__7EC1CEDB] DEFAULT ('None') FOR [macro_comment]
GOI can't see why you'd need any code in MS-Access. Just set the default values specified using the MS-Access GUI, and it should handle the rest for you.
While MS-Access doesn't scale well compared to MS-SQL, it certainly is easier to use!
-PatP
MS-SQL Server help me
hai, we r developing a application in VB6.0 with MS SQL Server as
backend. we are facing problem of SQL Server getting hanged after
running continously for more than 4 hrs. our application will be posting
and updating data continously to database. also we have client machines
were we can view these data(these are seperate machines).
we have SQL Server and our application running in seperate machines. SQL
Server is running in a server class machine PIII processor, 256 MB ram
and our application running on a PIV processor, 256 MB ram.
please help me to resolve the problem.
regards
Suriya.
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!Look lik you are stucking into a deadlock situation. If so, try to check the
activity in SQL Server EM for Deadlocking. If Deadlocks occur , try to use
another locking method in your programs and scripts, that´ll help.
Jens Süßmeyer.
"Suriya Narayanan Vadivel Murugan" <suriyasj@.rediffmail.com> schrieb im
Newsbeitrag news:ex5Z2VgZDHA.4020@.tk2msftngp13.phx.gbl...
> hai, we r developing a application in VB6.0 with MS SQL Server as
> backend. we are facing problem of SQL Server getting hanged after
> running continously for more than 4 hrs. our application will be posting
> and updating data continously to database. also we have client machines
> were we can view these data(these are seperate machines).
> we have SQL Server and our application running in seperate machines. SQL
> Server is running in a server class machine PIII processor, 256 MB ram
> and our application running on a PIV processor, 256 MB ram.
> please help me to resolve the problem.
> regards
> Suriya.
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!
backend. we are facing problem of SQL Server getting hanged after
running continously for more than 4 hrs. our application will be posting
and updating data continously to database. also we have client machines
were we can view these data(these are seperate machines).
we have SQL Server and our application running in seperate machines. SQL
Server is running in a server class machine PIII processor, 256 MB ram
and our application running on a PIV processor, 256 MB ram.
please help me to resolve the problem.
regards
Suriya.
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!Look lik you are stucking into a deadlock situation. If so, try to check the
activity in SQL Server EM for Deadlocking. If Deadlocks occur , try to use
another locking method in your programs and scripts, that´ll help.
Jens Süßmeyer.
"Suriya Narayanan Vadivel Murugan" <suriyasj@.rediffmail.com> schrieb im
Newsbeitrag news:ex5Z2VgZDHA.4020@.tk2msftngp13.phx.gbl...
> hai, we r developing a application in VB6.0 with MS SQL Server as
> backend. we are facing problem of SQL Server getting hanged after
> running continously for more than 4 hrs. our application will be posting
> and updating data continously to database. also we have client machines
> were we can view these data(these are seperate machines).
> we have SQL Server and our application running in seperate machines. SQL
> Server is running in a server class machine PIII processor, 256 MB ram
> and our application running on a PIV processor, 256 MB ram.
> please help me to resolve the problem.
> regards
> Suriya.
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!
MS-SQL Server equivalent to Oracle 9i?
All,
Oracle 9i provides a "USING" clause option for inner joins, that
allows me to say:
SELECT * FROM TBL1 JOIN TBL2 USING KeyColumn
assuming KeyColumn is in both TBL1 and TBL2. This is HIGHLY desirable
for our software make use of, but we also support SQL Server. There
is no USING option available, and
SELECT * FROM TBL1 JOIN TBL2 ON TBL1.KeyColumn = TBL2.KeyColumn
causes an ambiguous column error on KeyColumn.
Is there any equivalent to this Oracle functionality on SQL Server?
KingGreg>> "SELECT * FROM TBL1 JOIN TBL2 ON TBL1.KeyColumn = TBL2.KeyColumn"
causes an ambiguous column error on KeyColumn <<
And the USING clause is limited to equi-joins. But the real problem is
that good SQL programmers do not use "SELECT *" in production code. It
changes at run time and is too unclear and dangerous.
NATURAL JOIN and USING were two of the worst ideas we put into SQL-92.
I hope they get deprecated soon.
--CELKO--
===========================
Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are.
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||On 14 May 2004 13:02:13 -0700, KingGreg wrote:
>All,
>Oracle 9i provides a "USING" clause option for inner joins, that
>allows me to say:
>SELECT * FROM TBL1 JOIN TBL2 USING KeyColumn
>assuming KeyColumn is in both TBL1 and TBL2. This is HIGHLY desirable
>for our software make use of, but we also support SQL Server. There
>is no USING option available, and
>SELECT * FROM TBL1 JOIN TBL2 ON TBL1.KeyColumn = TBL2.KeyColumn
>causes an ambiguous column error on KeyColumn.
I can't reproduce this error:
create table TBL1 (KeyColumn int not null primary key)
create table TBL2 (KeyColumn int not null primary key)
insert TBL1 (KeyColumn)
values(1)
insert TBL1 (KeyColumn)
values(2)
insert TBL2 (KeyColumn)
values(1)
insert TBL2 (KeyColumn)
values(3)
SELECT * FROM TBL1 JOIN TBL2 ON TBL1.KeyColumn = TBL2.KeyColumn
drop table TBL1
drop table TBL2
KeyColumn KeyColumn
---- ----
1 1
(1 row(s) affected)
Can you post the actual SQL that returns this error, as I assume there is
an error somewhere in the query.
>Is there any equivalent to this Oracle functionality on SQL Server?
No, there isn't.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||I understand that I was not clear because you have to be using derived
table. See below:
> create table TBL1 (KeyColumn int not null primary key)
> create table TBL2 (KeyColumn int not null primary key)
> insert TBL1 (KeyColumn)
> values(1)
> insert TBL1 (KeyColumn)
> values(2)
> insert TBL2 (KeyColumn)
> values(1)
> insert TBL2 (KeyColumn)
> values(3)
> SELECT * FROM TBL1 JOIN TBL2 ON TBL1.KeyColumn = TBL2.KeyColumn
> drop table TBL1
> drop table TBL2
>
> KeyColumn KeyColumn
> ---- ----
> 1 1
> (1 row(s) affected)
Try :
1 SELECT KeyColumn
2 FROM (
3 SELECT * FROM TBL1 JOIN TBL2 ON TBL1.KeyColumn = TBL2.KeyColumn
4 ) TBL
Gives error : Column 'KeyColumn' specified multiple times for TBL
As you noted it does not fail when running line 3 by itself.
I guess I must be a bad SQL programmer, but this is nonetheless the
direction I am pursuing because of numerous other limitations in SQL
Server or Oracle that prevents using some other solution.
KingGreg|||Can you be more specific?
I agree with Joe that USING and NATURAL JOIN are undesiravel features,
especially since their only purpose in life seems to be to add convenience.
Obviously you are of a different opinion. As a developer I (and quite
likely MS folks listening in) am curious to learn where you see the
value ad.
Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab|||KingGreg wrote:
> I understand that I was not clear because you have to be using derived
> table. See below:
>
>>create table TBL1 (KeyColumn int not null primary key)
>>create table TBL2 (KeyColumn int not null primary key)
>>insert TBL1 (KeyColumn)
>>values(1)
>>insert TBL1 (KeyColumn)
>>values(2)
>>insert TBL2 (KeyColumn)
>>values(1)
>>insert TBL2 (KeyColumn)
>>values(3)
>>SELECT * FROM TBL1 JOIN TBL2 ON TBL1.KeyColumn = TBL2.KeyColumn
>>drop table TBL1
>>drop table TBL2
>>
>>
>>KeyColumn KeyColumn
>>---- ----
>>1 1
>>
>>(1 row(s) affected)
>>
>
> Try :
> 1 SELECT KeyColumn
> 2 FROM (
> 3 SELECT * FROM TBL1 JOIN TBL2 ON TBL1.KeyColumn = TBL2.KeyColumn
> 4 ) TBL
> Gives error : Column 'KeyColumn' specified multiple times for TBL
> As you noted it does not fail when running line 3 by itself.
> I guess I must be a bad SQL programmer, but this is nonetheless the
> direction I am pursuing because of numerous other limitations in SQL
> Server or Oracle that prevents using some other solution.
> KingGreg
If in Oracle ... I suspect what you are trying to do is:
SELECT KeyColumn
FROM (
SELECT *
FROM TBL1
WHERE TBL1.KeyColumn = TBL2.KeyColumn);
Using ISO standard syntax. If in 9i or above you could also use
ANSI standard syntax.
--
Daniel Morgan
http://www.outreach.washington.edu/...oad/oad_crs.asp
http://www.outreach.washington.edu/...aoa/aoa_crs.asp
damorgan@.x.washington.edu
(replace 'x' with a 'u' to reply)|||As you have an INNER JOIN it doesn't matter which value of keycolumn you
reference as long as you specify an alias. It's best to avoid using SELECT *
in production code anyway (except in an EXISTS subquery). Try this:
SELECT keycolumn
FROM
(SELECT Tbl1.keycolumn
FROM Tbl1 JOIN Tbl2
ON Tbl1.keycolumn = Tbl2.keycolumn) TBL
--
David Portas
SQL Server MVP
--|||Joe Celko wrote:
>>>"SELECT * FROM TBL1 JOIN TBL2 ON TBL1.KeyColumn = TBL2.KeyColumn"
> causes an ambiguous column error on KeyColumn <<
> And the USING clause is limited to equi-joins. But the real problem is
> that good SQL programmers do not use "SELECT *" in production code. It
> changes at run time and is too unclear and dangerous.
It's not dangerous if your client code accesses the return fields by
name, and not by number. It is, however, generally returning more
data than you need, so it's a waster of resources, and you still
shouldn't do it.
Bill
Monday, February 20, 2012
ms-sql server "light"
I have read that there is a free version of Ms-Sql Server for educational
purposes. Is it true? thank you.Microsoft does have discounted licensing programmes available for
educational institutions but, as far as I'm aware, there isn't a special
"educational version".
http://www.microsoft.com/education/?ID=HowToBuy
purposes. Is it true? thank you.Microsoft does have discounted licensing programmes available for
educational institutions but, as far as I'm aware, there isn't a special
"educational version".
http://www.microsoft.com/education/?ID=HowToBuy
MSDE is a free distributable version of SQLServer with certain limitations
and without the client tools:
http://www.microsoft.com/sql/msde/
There is also a 120 day evaluation available as a free download:
http://www.microsoft.com/sql/evalua...ial/default.asp
--
David Portas
----
Please reply only to the newsgroup
--
"billo" <jalden@.NOSPAM.it> wrote in message
news:dK%xb.121072$hV.4371271@.news2.tin.it...
> I have read that there is a free version of Ms-Sql Server for educational
> purposes. Is it true? thank you.|||Should have mentioned that there is also a Developer Edition priced at
$49.95. However, there are licensing restrictions on the use of this. You
would need to verify that your intended use comes within the terms of the
licence.
http://www.microsoft.com/sql/howtobuy/development.asp
--
David Portas
----
Please reply only to the newsgroup
--
"billo" <jalden@.NOSPAM.it> wrote in message
news:dK%xb.121072$hV.4371271@.news2.tin.it...
> I have read that there is a free version of Ms-Sql Server for educational
> purposes. Is it true? thank you.|||"billo" <jalden@.NOSPAM.it> wrote in message
news:dK%xb.121072$hV.4371271@.news2.tin.it...
> I have read that there is a free version of Ms-Sql Server for educational
> purposes. Is it true? thank you.
You're probably thinking of MSDE:
http://www.microsoft.com/sql/msde/d...ds/download.asp
Simon|||Ok it could be the MSDE2000. But with it what can I do? is like
MsSqlServer(ide,...) or is only an engine?
"Simon Hayes" <sql@.hayes.ch> ha scritto nel messaggio
news:3fc89409_1@.news.bluewin.ch...
> "billo" <jalden@.NOSPAM.it> wrote in message
> news:dK%xb.121072$hV.4371271@.news2.tin.it...
> > I have read that there is a free version of Ms-Sql Server for
educational
> > purposes. Is it true? thank you.
> You're probably thinking of MSDE:
> http://www.microsoft.com/sql/msde/d...ds/download.asp
> Simon|||"billo" <jalden@.NOSPAM.it> wrote in message news:<jZ8yb.123661$hV.4486085@.news2.tin.it>...
> Ok it could be the MSDE2000. But with it what can I do? is like
> MsSqlServer(ide,...) or is only an engine?
> "Simon Hayes" <sql@.hayes.ch> ha scritto nel messaggio
> news:3fc89409_1@.news.bluewin.ch...
> > "billo" <jalden@.NOSPAM.it> wrote in message
> > news:dK%xb.121072$hV.4371271@.news2.tin.it...
> > > I have read that there is a free version of Ms-Sql Server for
> educational
> > > purposes. Is it true? thank you.
> > > > You're probably thinking of MSDE:
> > http://www.microsoft.com/sql/msde/d...ds/download.asp
> > Simon
You can see the details of what's in MSDE on the MS web site. The main
points are that databases are limited to 2GB, performance drops with 5
or more concurrent connections, and there are no client tools except
for command line ones such as osql.exe (no Query Analyzer, Enterprise
Manager, Profiler etc.).
If those limitations aren't acceptable, then you could look at the
development edition, which I believe is the same as Enterprise
Edition, but only licensed for development.
Simon|||It's "only" the engine. If your purpose is to teach people the SQL Server
tools/IDE stuff then you'll need to check with MS what the best licensing
scheme for you is for the full SQL Server product.
> Ok it could be the MSDE2000. But with it what can I do? is like
> MsSqlServer(ide,...) or is only an engine?
Neil Pike MVP/MCSE. Protech Computing Ltd
Reply here - no email
SQL FAQ (484 entries) see
http://forumsb.compuserve.com/gvfor...p?SRV=MSDevApps
(faqxxx.zip in lib 7)
or http://www.ntfaq.com/Articles/Index...epartmentID=800
or www.sqlserverfaq.com
or www.mssqlserver.com/faq
MS-SQL Search by keyword performance
Hello experts,
I'm a newbie here and do not know if this is the right place to ask this question, or if there are some one else already solving this from elsewhere. if so, please accept my appologies.
My problem is that, i do not know what is the right solution to dealing with the search module which will need to be implemented in my application.
e.g: i have a master table is Order with the following fields
(Order_id, Product_Id, Order_Number, Cus_name, Cus_address)
Product(Product_id, Category_Id, ProductName, Price)
Category(Category_id, Description)
My Search support for user to enter a string, once hit on search, system would need to returned all matched Order for the search string.
example: if i enter [Toy], then system will return all Order which:
- The Cus_name contains [toy] or Cus_address contains [toy] or productname contain [toy] or category description contains [toy]
actually, the real senarios might be more complex than this sample and the database is a huge db which might contains mililion of records. If i doing a standard SQL join to perform the SQL selection, i would afraid about the performance of the whole syste
m.
Is Full-text index search could be applied for this or is there any other solution?
Thanks for your helps!
Doan
Message posted via http://www.sqlmonster.com
This is exactly what full-text search is intended to do... Setting it up is
documented in books on line... after you have indexed all of the fields, you
can to a multi column search ie..
select * from Orders where contains(*,'Toy')
The * in the contains clause says to search ALL indexed text columns
have fun
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Doan Ly via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:cb1e2d67657544229ad2c2b16d7513ea@.SQLMonster.c om...
> Hello experts,
> I'm a newbie here and do not know if this is the right place to ask this
question, or if there are some one else already solving this from elsewhere.
if so, please accept my appologies.
> My problem is that, i do not know what is the right solution to dealing
with the search module which will need to be implemented in my application.
> e.g: i have a master table is Order with the following fields
> (Order_id, Product_Id, Order_Number, Cus_name, Cus_address)
> Product(Product_id, Category_Id, ProductName, Price)
> Category(Category_id, Description)
> My Search support for user to enter a string, once hit on search, system
would need to returned all matched Order for the search string.
> example: if i enter [Toy], then system will return all Order which:
> - The Cus_name contains [toy] or Cus_address contains [toy] or productname
contain [toy] or category description contains [toy]
> actually, the real senarios might be more complex than this sample and the
database is a huge db which might contains mililion of records. If i doing a
standard SQL join to perform the SQL selection, i would afraid about the
performance of the whole system.
> Is Full-text index search could be applied for this or is there any other
solution?
> Thanks for your helps!
> Doan
> --
> Message posted via http://www.sqlmonster.com
|||Thanks Wayne for your speedy suggest.
by the way:
>select * from Orders where contains(*,'Toy')
>The * in the contains clause says to search ALL indexed text columns
Could it also look for the matched full-text indexed fields in its child tables? (Product & Category), or need i consider some special skill here?
Thanks
Doan
Message posted via http://www.sqlmonster.com
I'm a newbie here and do not know if this is the right place to ask this question, or if there are some one else already solving this from elsewhere. if so, please accept my appologies.
My problem is that, i do not know what is the right solution to dealing with the search module which will need to be implemented in my application.
e.g: i have a master table is Order with the following fields
(Order_id, Product_Id, Order_Number, Cus_name, Cus_address)
Product(Product_id, Category_Id, ProductName, Price)
Category(Category_id, Description)
My Search support for user to enter a string, once hit on search, system would need to returned all matched Order for the search string.
example: if i enter [Toy], then system will return all Order which:
- The Cus_name contains [toy] or Cus_address contains [toy] or productname contain [toy] or category description contains [toy]
actually, the real senarios might be more complex than this sample and the database is a huge db which might contains mililion of records. If i doing a standard SQL join to perform the SQL selection, i would afraid about the performance of the whole syste
m.
Is Full-text index search could be applied for this or is there any other solution?
Thanks for your helps!
Doan
Message posted via http://www.sqlmonster.com
This is exactly what full-text search is intended to do... Setting it up is
documented in books on line... after you have indexed all of the fields, you
can to a multi column search ie..
select * from Orders where contains(*,'Toy')
The * in the contains clause says to search ALL indexed text columns
have fun
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Doan Ly via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:cb1e2d67657544229ad2c2b16d7513ea@.SQLMonster.c om...
> Hello experts,
> I'm a newbie here and do not know if this is the right place to ask this
question, or if there are some one else already solving this from elsewhere.
if so, please accept my appologies.
> My problem is that, i do not know what is the right solution to dealing
with the search module which will need to be implemented in my application.
> e.g: i have a master table is Order with the following fields
> (Order_id, Product_Id, Order_Number, Cus_name, Cus_address)
> Product(Product_id, Category_Id, ProductName, Price)
> Category(Category_id, Description)
> My Search support for user to enter a string, once hit on search, system
would need to returned all matched Order for the search string.
> example: if i enter [Toy], then system will return all Order which:
> - The Cus_name contains [toy] or Cus_address contains [toy] or productname
contain [toy] or category description contains [toy]
> actually, the real senarios might be more complex than this sample and the
database is a huge db which might contains mililion of records. If i doing a
standard SQL join to perform the SQL selection, i would afraid about the
performance of the whole system.
> Is Full-text index search could be applied for this or is there any other
solution?
> Thanks for your helps!
> Doan
> --
> Message posted via http://www.sqlmonster.com
|||Thanks Wayne for your speedy suggest.
by the way:
>select * from Orders where contains(*,'Toy')
>The * in the contains clause says to search ALL indexed text columns
Could it also look for the matched full-text indexed fields in its child tables? (Product & Category), or need i consider some special skill here?
Thanks
Doan
Message posted via http://www.sqlmonster.com
MS-SQL Search by keyword performance
Hello experts,
I'm a newbie here and do not know if this is the right place to ask this que
stion, or if there are some one else already solving this from elsewhere. if
so, please accept my appologies.
My problem is that, i do not know what is the right solution to dealing with
the search module which will need to be implemented in my application.
e.g: i have a master table is Order with the following fields
(Order_id, Product_Id, Order_Number, Cus_name, Cus_address)
Product(Product_id, Category_Id, ProductName, Price)
Category(Category_id, Description)
My Search support for user to enter a string, once hit on search, system wou
ld need to returned all matched Order for the search string.
example: if i enter [Toy], then system will return all Order which:
- The Cus_name contains [toy] or Cus_address contains [toy] or produ
ctname contain [toy] or category description contains [toy]
actually, the real senarios might be more complex than this sample and the d
atabase is a huge db which might contains mililion of records. If i doing a
standard SQL join to perform the SQL selection, i would afraid about the per
formance of the whole syste
m.
Is Full-text index search could be applied for this or is there any other so
lution?
Thanks for your helps!
Doan
Message posted via http://www.droptable.comThis is exactly what full-text search is intended to do... Setting it up is
documented in books on line... after you have indexed all of the fields, you
can to a multi column search ie..
select * from Orders where contains(*,'Toy')
The * in the contains clause says to search ALL indexed text columns
have fun
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Doan Ly via droptable.com" <forum@.droptable.com> wrote in message
news:cb1e2d67657544229ad2c2b16d7513ea@.SQ
droptable.com...
> Hello experts,
> I'm a newbie here and do not know if this is the right place to ask this
question, or if there are some one else already solving this from elsewhere.
if so, please accept my appologies.
> My problem is that, i do not know what is the right solution to dealing
with the search module which will need to be implemented in my application.
> e.g: i have a master table is Order with the following fields
> (Order_id, Product_Id, Order_Number, Cus_name, Cus_address)
> Product(Product_id, Category_Id, ProductName, Price)
> Category(Category_id, Description)
> My Search support for user to enter a string, once hit on search, system
would need to returned all matched Order for the search string.
> example: if i enter [Toy], then system will return all Order which:
> - The Cus_name contains [toy] or Cus_address contains [toy] or productname
contain [toy] or category description contains [toy]
> actually, the real senarios might be more complex than this sample and the
database is a huge db which might contains mililion of records. If i doing a
standard SQL join to perform the SQL selection, i would afraid about the
performance of the whole system.
> Is Full-text index search could be applied for this or is there any other
solution?
> Thanks for your helps!
> Doan
> --
> Message posted via http://www.droptable.com|||Thanks Wayne for your speedy suggest.
by the way:
>select * from Orders where contains(*,'Toy')
>The * in the contains clause says to search ALL indexed text columns
Could it also look for the matched full-text indexed fields in its child tab
les? (Product & Category), or need i consider some special skill here?
Thanks
Doan
Message posted via http://www.droptable.com
I'm a newbie here and do not know if this is the right place to ask this que
stion, or if there are some one else already solving this from elsewhere. if
so, please accept my appologies.
My problem is that, i do not know what is the right solution to dealing with
the search module which will need to be implemented in my application.
e.g: i have a master table is Order with the following fields
(Order_id, Product_Id, Order_Number, Cus_name, Cus_address)
Product(Product_id, Category_Id, ProductName, Price)
Category(Category_id, Description)
My Search support for user to enter a string, once hit on search, system wou
ld need to returned all matched Order for the search string.
example: if i enter [Toy], then system will return all Order which:
- The Cus_name contains [toy] or Cus_address contains [toy] or produ
ctname contain [toy] or category description contains [toy]
actually, the real senarios might be more complex than this sample and the d
atabase is a huge db which might contains mililion of records. If i doing a
standard SQL join to perform the SQL selection, i would afraid about the per
formance of the whole syste
m.
Is Full-text index search could be applied for this or is there any other so
lution?
Thanks for your helps!
Doan
Message posted via http://www.droptable.comThis is exactly what full-text search is intended to do... Setting it up is
documented in books on line... after you have indexed all of the fields, you
can to a multi column search ie..
select * from Orders where contains(*,'Toy')
The * in the contains clause says to search ALL indexed text columns
have fun
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Doan Ly via droptable.com" <forum@.droptable.com> wrote in message
news:cb1e2d67657544229ad2c2b16d7513ea@.SQ
droptable.com...
> Hello experts,
> I'm a newbie here and do not know if this is the right place to ask this
question, or if there are some one else already solving this from elsewhere.
if so, please accept my appologies.
> My problem is that, i do not know what is the right solution to dealing
with the search module which will need to be implemented in my application.
> e.g: i have a master table is Order with the following fields
> (Order_id, Product_Id, Order_Number, Cus_name, Cus_address)
> Product(Product_id, Category_Id, ProductName, Price)
> Category(Category_id, Description)
> My Search support for user to enter a string, once hit on search, system
would need to returned all matched Order for the search string.
> example: if i enter [Toy], then system will return all Order which:
> - The Cus_name contains [toy] or Cus_address contains [toy] or productname
contain [toy] or category description contains [toy]
> actually, the real senarios might be more complex than this sample and the
database is a huge db which might contains mililion of records. If i doing a
standard SQL join to perform the SQL selection, i would afraid about the
performance of the whole system.
> Is Full-text index search could be applied for this or is there any other
solution?
> Thanks for your helps!
> Doan
> --
> Message posted via http://www.droptable.com|||Thanks Wayne for your speedy suggest.
by the way:
>select * from Orders where contains(*,'Toy')
>The * in the contains clause says to search ALL indexed text columns
Could it also look for the matched full-text indexed fields in its child tab
les? (Product & Category), or need i consider some special skill here?
Thanks
Doan
Message posted via http://www.droptable.com
MS-SQL Search by keyword performance
Hello experts,
I'm a newbie here and do not know if this is the right place to ask this question, or if there are some one else already solving this from elsewhere. if so, please accept my appologies.
My problem is that, i do not know what is the right solution to dealing with the search module which will need to be implemented in my application.
e.g: i have a master table is Order with the following fields
(Order_id, Product_Id, Order_Number, Cus_name, Cus_address)
Product(Product_id, Category_Id, ProductName, Price)
Category(Category_id, Description)
My Search support for user to enter a string, once hit on search, system would need to returned all matched Order for the search string.
example: if i enter [Toy], then system will return all Order which:
- The Cus_name contains [toy] or Cus_address contains [toy] or productname contain [toy] or category description contains [toy]
actually, the real senarios might be more complex than this sample and the database is a huge db which might contains mililion of records. If i doing a standard SQL join to perform the SQL selection, i would afraid about the performance of the whole system.
Is Full-text index search could be applied for this or is there any other solution?
Thanks for your helps!
Doan
--
Message posted via http://www.sqlmonster.comThis is exactly what full-text search is intended to do... Setting it up is
documented in books on line... after you have indexed all of the fields, you
can to a multi column search ie..
select * from Orders where contains(*,'Toy')
The * in the contains clause says to search ALL indexed text columns
have fun
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Doan Ly via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:cb1e2d67657544229ad2c2b16d7513ea@.SQLMonster.com...
> Hello experts,
> I'm a newbie here and do not know if this is the right place to ask this
question, or if there are some one else already solving this from elsewhere.
if so, please accept my appologies.
> My problem is that, i do not know what is the right solution to dealing
with the search module which will need to be implemented in my application.
> e.g: i have a master table is Order with the following fields
> (Order_id, Product_Id, Order_Number, Cus_name, Cus_address)
> Product(Product_id, Category_Id, ProductName, Price)
> Category(Category_id, Description)
> My Search support for user to enter a string, once hit on search, system
would need to returned all matched Order for the search string.
> example: if i enter [Toy], then system will return all Order which:
> - The Cus_name contains [toy] or Cus_address contains [toy] or productname
contain [toy] or category description contains [toy]
> actually, the real senarios might be more complex than this sample and the
database is a huge db which might contains mililion of records. If i doing a
standard SQL join to perform the SQL selection, i would afraid about the
performance of the whole system.
> Is Full-text index search could be applied for this or is there any other
solution?
> Thanks for your helps!
> Doan
> --
> Message posted via http://www.sqlmonster.com|||Thanks Wayne for your speedy suggest.
by the way:
>select * from Orders where contains(*,'Toy')
>The * in the contains clause says to search ALL indexed text columns
Could it also look for the matched full-text indexed fields in its child tables? (Product & Category), or need i consider some special skill here?
Thanks
Doan
--
Message posted via http://www.sqlmonster.com
I'm a newbie here and do not know if this is the right place to ask this question, or if there are some one else already solving this from elsewhere. if so, please accept my appologies.
My problem is that, i do not know what is the right solution to dealing with the search module which will need to be implemented in my application.
e.g: i have a master table is Order with the following fields
(Order_id, Product_Id, Order_Number, Cus_name, Cus_address)
Product(Product_id, Category_Id, ProductName, Price)
Category(Category_id, Description)
My Search support for user to enter a string, once hit on search, system would need to returned all matched Order for the search string.
example: if i enter [Toy], then system will return all Order which:
- The Cus_name contains [toy] or Cus_address contains [toy] or productname contain [toy] or category description contains [toy]
actually, the real senarios might be more complex than this sample and the database is a huge db which might contains mililion of records. If i doing a standard SQL join to perform the SQL selection, i would afraid about the performance of the whole system.
Is Full-text index search could be applied for this or is there any other solution?
Thanks for your helps!
Doan
--
Message posted via http://www.sqlmonster.comThis is exactly what full-text search is intended to do... Setting it up is
documented in books on line... after you have indexed all of the fields, you
can to a multi column search ie..
select * from Orders where contains(*,'Toy')
The * in the contains clause says to search ALL indexed text columns
have fun
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Doan Ly via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:cb1e2d67657544229ad2c2b16d7513ea@.SQLMonster.com...
> Hello experts,
> I'm a newbie here and do not know if this is the right place to ask this
question, or if there are some one else already solving this from elsewhere.
if so, please accept my appologies.
> My problem is that, i do not know what is the right solution to dealing
with the search module which will need to be implemented in my application.
> e.g: i have a master table is Order with the following fields
> (Order_id, Product_Id, Order_Number, Cus_name, Cus_address)
> Product(Product_id, Category_Id, ProductName, Price)
> Category(Category_id, Description)
> My Search support for user to enter a string, once hit on search, system
would need to returned all matched Order for the search string.
> example: if i enter [Toy], then system will return all Order which:
> - The Cus_name contains [toy] or Cus_address contains [toy] or productname
contain [toy] or category description contains [toy]
> actually, the real senarios might be more complex than this sample and the
database is a huge db which might contains mililion of records. If i doing a
standard SQL join to perform the SQL selection, i would afraid about the
performance of the whole system.
> Is Full-text index search could be applied for this or is there any other
solution?
> Thanks for your helps!
> Doan
> --
> Message posted via http://www.sqlmonster.com|||Thanks Wayne for your speedy suggest.
by the way:
>select * from Orders where contains(*,'Toy')
>The * in the contains clause says to search ALL indexed text columns
Could it also look for the matched full-text indexed fields in its child tables? (Product & Category), or need i consider some special skill here?
Thanks
Doan
--
Message posted via http://www.sqlmonster.com
MS-SQL Script Question
MS-SQL 2000
Is there any way to run a SQL script against MSDE other than with OSQL? (No Enterprise manager or Query Analyzer)
TIA
--
Tim Morrison
------------------------
Vehicle Web Studio - The easiest way to create and maintain your vehicle related website.
http://www.vehiclewebstudio.comYou will have to have a client app of some sort (osql/isql/etc). Basically, the
client app will open a connection to your server, parse the content of your
script file into batches delimited by GO, then execute the batches against the
server.
It's quite easy to implement an ado connection to sqlserver, parse the script
file and execute it. QALite on the site does just that.
--
-oj
http://www.rac4sql.net
"Tim Morrison" <sales@.kjmsoftware.com> wrote in message
news:LnnKb.753782$Tr4.2103435@.attbi_s03...
MS-SQL 2000
Is there any way to run a SQL script against MSDE other than with OSQL? (No
Enterprise manager or Query Analyzer)
TIA
--
Tim Morrison
------------------------
Vehicle Web Studio - The easiest way to create and maintain your vehicle related
website.
http://www.vehiclewebstudio.com|||The script can be registered as a task and run automatically
without operator intervention.
"Tim Morrison" <sales@.kjmsoftware.com> wrote in message
news:LnnKb.753782$Tr4.2103435@.attbi_s03...
MS-SQL 2000
Is there any way to run a SQL script against MSDE other than with OSQL? (No
Enterprise manager or Query Analyzer)
TIA
--
Tim Morrison
-----------------------
--
Vehicle Web Studio - The easiest way to create and maintain your vehicle
related website.
http://www.vehiclewebstudio.com
MS-SQL Replication
I need help setting this up, I am not a DBA but I am a MCSE, and network administrator. My company has moved to MS-SQL from as400 and I need to set up replication between California and Pennsylvania. I am having many problems doing this.
I get login errors. timed out errors, or "name cannot be NULL"
Is there someone that can walk me thru this to get this replication set up and working. I am on a tight time line our go live date is March 1 and I have to have PA set up by Feb 21 for testing.
mjedsLogin error: ensure that domain accounts of trusted domains are used;
Timeouts: can be of different nature (login, command?), be specific;
NULL name: you're probably including BE code with dynamic SQL, which is not a good candidate for replication.
You also need to tell us what type of replication you're trying to set up.|||A. Determine replication model - update frequency and connectivity are key factors. Push vs. Pull is also important. For example, don't attempt near-real time replication over dial-up. Bottom Line - what does your business need/expect? Note: what they want sometimes != what is possible.
B. Start simple - like, with 0 and 1 and then progress to a whole byte. Document what you do as you do it. You won't succeed on your first attempt, so be prepared for the second (i think i finally saw the light on my 417th attempt).
C. Make sure your servers have names for themselves in sysservers. This doesn't qualify as high-level guidance, but it will solve the problems you quoted. Timeout is likely due to the fact one server it is attempting to connect to another server that does not exist - like, NULL, for example.
D. Chill, if you have reliable network connectivity and required database resources, you'll have this working in less than two days.
E. More info: http://www.dbforums.com/showthread.php?p=4093086
Just remember, we're all counting on you. Leslie Nielsen, Airplane|||Ok let me go in detail on this:
Server in California: Attached to a Windows Server 2003 Active Directory Domain, I have an internal DNS server and this SQL is being resolved.
Server in PA: Is a domain contoller (back up to CA server), also has DNS for the PA site.
CA is hooked to PA via IPSec VPN tunnel. via T1's on both sides. all Externel IP's are static and NAT'd. Both systems are on same/shared subnet.
What is needed:
Real time replication, when changes are made at either site the databases must be updated and match. Both side use unique invoice and shipping numbering sequences so over lapping invoices and shipping information won't occur.
So now what I need (and excuse me for sounding novice) is a step by step instruction on how to set-up, test, and run replication between the servers via the IPSec VPN.
Thanks for any help you can provide.
mjeds--|||Sounds like you need Merge Replication, the most difficult to understand and implement. FYI - I lean towards Pull vs. Push, especially if the servers aren't on the same 1 Gigabit or better subnet. In otherwords, create Pull Subscription(s).
There is no such thing as real-time replication. You must communicate this to your business and help them understand they should be expecting five minute delays b/w the machines. You should be able to deliver < 2 minute delays, consistently. Wow 'em.
Do not attempt continuous (sorry, forget the correct term and don't have access to BOL, but I would encourage you to expect connection problems, and attempting continuously updating subscriptions is not for the replication newbie) or real-time replication on this network environment unless the business can afford extended downtime and you enjoy staring at replication monitors 24x7 (it gets old, fast).
Before attempting merge replication, start simple (see B. supra).
0. You will remove the following - it is a suggestion/excercise to familiarize you with replication. START SMALL.
1. Set up a pull subscription from PA to CA. CA is the publisher (with the distribution database on the same server) and PA is the subscriber.
2. Use pubs or a test database of your own. Create YourTest0 table.
3. Create a publication/article on CA.pubs.YourTest0 table. Make sure the CA server is in the master..sysservers table.
4. Create a subscription on PA.pubs.
5. Insert a row in CA.pubs..YourTest. Ensure it replicates to PA.pubs.YourTest0. Now do an Update and confirm the change on PA.pubs.YourTest0.
6. You're 45 % there! Repeat steps 2-5, swapping CA for PA and PA for CA , and YourTest1 for YourTest0. When successful, you will have bi-directional replication between CA and PA.
7. You're 90% there! Unfortunately, the final 10% is 90% of the effort.
8. Final 10% - merge YourTable0 and YourTable1. Too much to explain with my weak typing skills, so memorize replication in BOL. J/K - but, become extremely familiar with it. When done, make a plan and then work on your real data. Note: I hope you have a test environment or you are really good at testing in production or you like to work during off-hours.
PLAN, PLAN, PLAN or regret. You have to understand replication before you can plan for it. After you get the tests working, you should be able to write out and execute a plan for your production environment. Trust me, the benefit from creating the plan is > 10X the cost of winging it. Been there done that.
Make it so. Captain Picard|||OK I setup a merge replication, CA as the distributer, PA as subscriber. I made a backup of our production DB and restored it under a different name (orig name was LOALIVE, test is MJEDSTEST1) so this is a full production DB.
I am able to push but not pull, I set the schedule up to snapshot every 15 mins (it takes 11 min just to create the snapshot) and the replication also at 15 min.) my testing IPSec is a 384k DSL, the live connection will be a 1.5mbps T1, so I think the timing should be better.
The "pull" still gives me the NULL error. But push seems to work just fine.
Now am I to understand that I should also make the PA server a distributor and reverse the setup so that both PA and CA are pushing to each other in non concurrent time frames? i.e. CA (distributor) --> PA every 15 min and PA (distributor) --> CA every 20? min or something to that effect?
mjeds--
Update: the push errored out the error (Unable to bulk copy $$$$$$ to table $$$$$)
also when I try to connect to the database with the client software I get the following: "The object table has no ROWVERSION column in the SQL server table description"|||You've made tremendous progress and although you have been more successful with Push, you still want to go w/ Pull. Let's get that working.
Recompile your stored procedures, or better yet, see article in General Replication Info., below, to fix the ROWVERSION problem.
The Null problem is because sysservers does not have correct info. Go here. (http://groups-beta.google.com/group/comp.databases.ms-sqlserver/browse_frm/thread/1ab91a3bab5d438f/931ee622e49ac83a?q=replication+name+sysservers+@.@.s ervername&_done=%2Fgroups%3Fhl%3Den%26q%3Dreplication+name+s ysservers+@.@.servername%26qt_s%3DSearch+Groups%26&_doneTitle=Back+to+Search&&d#931ee622e49ac83a)
Bi-directional Replication. (http://support.microsoft.com/default.aspx?scid=kb;en-us;820675)
General Replication Info. (http://www.replicationanswers.com/General.htm)
You owe me a nickel. Next post will cost you a cup of coffee.|||I'll buy you a cup of coffee, hell I'll send you a pound of coffee beans if I get this to work.
I don't understand where to find or change the info in sysserver, remember I am not a DBA this is my first experience with SQL.
Screen shots would be great if possible, my email is medwards@.lightsofamerica.com.
Thanks for your help you have been great so far. :D
mjeds--
update: another issue, after a successful push i attempted to open the database in our client software (navision 3.70a) and got this error:
the (database name) database on the (sql server name) does not contain Microsoft Navision system objects and cannot be opened.|||Test everything from Microsoft SQL Server Tools (Query Analyzer, SQL Enterprise Manager, iSQL, oSQL) whatever blows your skirt up. Test your app. last. Don't do battle with Navision, yet.
Punt the Push - stick to the Pull or we're going to have difficulty seeing eye-to-eye (or, keyboard-to-keyboard or post-to-post :D ).
Per the Go here. mentioned previously:
Check to see that the entry in sysservers for your database says "local." @.@.servername should "work" if this is the case.
To make sure that the local server is correctly described in sysservers, you need to do 'exec sp_addserver <SERVERNAME>, 'local'
Provide a current status after confirming my suggestions above, then I'll email you if necessary to earn that pound of beans (my consulting rate just jumped!).
You're very close to flying... just gotta miss the ground.|||I know this sounds stupid, but I don't know where to find, check or change the sysservers table. okay I found the table in the master - what do I do with it. Again my apologies for sounding stupid, but a week ago I had never seen SQL.
And like a lot (a lot) of companies; mine does not feel the necessity to hire an SQL consultant or otherwise, I'm the "computer guy" the ASSumption is I know everything and anything related to the computer world. And though I am pretty good I don't know everything yet (however as Comm Wil Riker once said "I plan to live forever")
Anyway, I am a newbie at this, how where what do I do to fix change modify the sysserver table.
I get login errors. timed out errors, or "name cannot be NULL"
Is there someone that can walk me thru this to get this replication set up and working. I am on a tight time line our go live date is March 1 and I have to have PA set up by Feb 21 for testing.
mjedsLogin error: ensure that domain accounts of trusted domains are used;
Timeouts: can be of different nature (login, command?), be specific;
NULL name: you're probably including BE code with dynamic SQL, which is not a good candidate for replication.
You also need to tell us what type of replication you're trying to set up.|||A. Determine replication model - update frequency and connectivity are key factors. Push vs. Pull is also important. For example, don't attempt near-real time replication over dial-up. Bottom Line - what does your business need/expect? Note: what they want sometimes != what is possible.
B. Start simple - like, with 0 and 1 and then progress to a whole byte. Document what you do as you do it. You won't succeed on your first attempt, so be prepared for the second (i think i finally saw the light on my 417th attempt).
C. Make sure your servers have names for themselves in sysservers. This doesn't qualify as high-level guidance, but it will solve the problems you quoted. Timeout is likely due to the fact one server it is attempting to connect to another server that does not exist - like, NULL, for example.
D. Chill, if you have reliable network connectivity and required database resources, you'll have this working in less than two days.
E. More info: http://www.dbforums.com/showthread.php?p=4093086
Just remember, we're all counting on you. Leslie Nielsen, Airplane|||Ok let me go in detail on this:
Server in California: Attached to a Windows Server 2003 Active Directory Domain, I have an internal DNS server and this SQL is being resolved.
Server in PA: Is a domain contoller (back up to CA server), also has DNS for the PA site.
CA is hooked to PA via IPSec VPN tunnel. via T1's on both sides. all Externel IP's are static and NAT'd. Both systems are on same/shared subnet.
What is needed:
Real time replication, when changes are made at either site the databases must be updated and match. Both side use unique invoice and shipping numbering sequences so over lapping invoices and shipping information won't occur.
So now what I need (and excuse me for sounding novice) is a step by step instruction on how to set-up, test, and run replication between the servers via the IPSec VPN.
Thanks for any help you can provide.
mjeds--|||Sounds like you need Merge Replication, the most difficult to understand and implement. FYI - I lean towards Pull vs. Push, especially if the servers aren't on the same 1 Gigabit or better subnet. In otherwords, create Pull Subscription(s).
There is no such thing as real-time replication. You must communicate this to your business and help them understand they should be expecting five minute delays b/w the machines. You should be able to deliver < 2 minute delays, consistently. Wow 'em.
Do not attempt continuous (sorry, forget the correct term and don't have access to BOL, but I would encourage you to expect connection problems, and attempting continuously updating subscriptions is not for the replication newbie) or real-time replication on this network environment unless the business can afford extended downtime and you enjoy staring at replication monitors 24x7 (it gets old, fast).
Before attempting merge replication, start simple (see B. supra).
0. You will remove the following - it is a suggestion/excercise to familiarize you with replication. START SMALL.
1. Set up a pull subscription from PA to CA. CA is the publisher (with the distribution database on the same server) and PA is the subscriber.
2. Use pubs or a test database of your own. Create YourTest0 table.
3. Create a publication/article on CA.pubs.YourTest0 table. Make sure the CA server is in the master..sysservers table.
4. Create a subscription on PA.pubs.
5. Insert a row in CA.pubs..YourTest. Ensure it replicates to PA.pubs.YourTest0. Now do an Update and confirm the change on PA.pubs.YourTest0.
6. You're 45 % there! Repeat steps 2-5, swapping CA for PA and PA for CA , and YourTest1 for YourTest0. When successful, you will have bi-directional replication between CA and PA.
7. You're 90% there! Unfortunately, the final 10% is 90% of the effort.
8. Final 10% - merge YourTable0 and YourTable1. Too much to explain with my weak typing skills, so memorize replication in BOL. J/K - but, become extremely familiar with it. When done, make a plan and then work on your real data. Note: I hope you have a test environment or you are really good at testing in production or you like to work during off-hours.
PLAN, PLAN, PLAN or regret. You have to understand replication before you can plan for it. After you get the tests working, you should be able to write out and execute a plan for your production environment. Trust me, the benefit from creating the plan is > 10X the cost of winging it. Been there done that.
Make it so. Captain Picard|||OK I setup a merge replication, CA as the distributer, PA as subscriber. I made a backup of our production DB and restored it under a different name (orig name was LOALIVE, test is MJEDSTEST1) so this is a full production DB.
I am able to push but not pull, I set the schedule up to snapshot every 15 mins (it takes 11 min just to create the snapshot) and the replication also at 15 min.) my testing IPSec is a 384k DSL, the live connection will be a 1.5mbps T1, so I think the timing should be better.
The "pull" still gives me the NULL error. But push seems to work just fine.
Now am I to understand that I should also make the PA server a distributor and reverse the setup so that both PA and CA are pushing to each other in non concurrent time frames? i.e. CA (distributor) --> PA every 15 min and PA (distributor) --> CA every 20? min or something to that effect?
mjeds--
Update: the push errored out the error (Unable to bulk copy $$$$$$ to table $$$$$)
also when I try to connect to the database with the client software I get the following: "The object table has no ROWVERSION column in the SQL server table description"|||You've made tremendous progress and although you have been more successful with Push, you still want to go w/ Pull. Let's get that working.
Recompile your stored procedures, or better yet, see article in General Replication Info., below, to fix the ROWVERSION problem.
The Null problem is because sysservers does not have correct info. Go here. (http://groups-beta.google.com/group/comp.databases.ms-sqlserver/browse_frm/thread/1ab91a3bab5d438f/931ee622e49ac83a?q=replication+name+sysservers+@.@.s ervername&_done=%2Fgroups%3Fhl%3Den%26q%3Dreplication+name+s ysservers+@.@.servername%26qt_s%3DSearch+Groups%26&_doneTitle=Back+to+Search&&d#931ee622e49ac83a)
Bi-directional Replication. (http://support.microsoft.com/default.aspx?scid=kb;en-us;820675)
General Replication Info. (http://www.replicationanswers.com/General.htm)
You owe me a nickel. Next post will cost you a cup of coffee.|||I'll buy you a cup of coffee, hell I'll send you a pound of coffee beans if I get this to work.
I don't understand where to find or change the info in sysserver, remember I am not a DBA this is my first experience with SQL.
Screen shots would be great if possible, my email is medwards@.lightsofamerica.com.
Thanks for your help you have been great so far. :D
mjeds--
update: another issue, after a successful push i attempted to open the database in our client software (navision 3.70a) and got this error:
the (database name) database on the (sql server name) does not contain Microsoft Navision system objects and cannot be opened.|||Test everything from Microsoft SQL Server Tools (Query Analyzer, SQL Enterprise Manager, iSQL, oSQL) whatever blows your skirt up. Test your app. last. Don't do battle with Navision, yet.
Punt the Push - stick to the Pull or we're going to have difficulty seeing eye-to-eye (or, keyboard-to-keyboard or post-to-post :D ).
Per the Go here. mentioned previously:
Check to see that the entry in sysservers for your database says "local." @.@.servername should "work" if this is the case.
To make sure that the local server is correctly described in sysservers, you need to do 'exec sp_addserver <SERVERNAME>, 'local'
Provide a current status after confirming my suggestions above, then I'll email you if necessary to earn that pound of beans (my consulting rate just jumped!).
You're very close to flying... just gotta miss the ground.|||I know this sounds stupid, but I don't know where to find, check or change the sysservers table. okay I found the table in the master - what do I do with it. Again my apologies for sounding stupid, but a week ago I had never seen SQL.
And like a lot (a lot) of companies; mine does not feel the necessity to hire an SQL consultant or otherwise, I'm the "computer guy" the ASSumption is I know everything and anything related to the computer world. And though I am pretty good I don't know everything yet (however as Comm Wil Riker once said "I plan to live forever")
Anyway, I am a newbie at this, how where what do I do to fix change modify the sysserver table.
MS-SQL Related
Hello,
A uniqueidentifier is a 128-bit value, which is generated in such a way
that it is guaranteed to be unique in the whole universe. (Well, at least
this planet.) They are not specific to SQL Server, but Windows has them
all over the place, but calls them GUIDs.
Huh? You can use almost any data type for primary keys. Since a primary
key is an index, the total key size may not execeed 900 bytes, so you
cannot use things like text or nvarchar(MAX).
>
A uniqueidentifier is a 128-bit value, which is generated in such a way
that it is guaranteed to be unique in the whole universe. (Well, at least
this planet.) They are not specific to SQL Server, but Windows has them
all over the place, but calls them GUIDs.
>
>
Huh? You can use almost any data type for primary keys. Since a primary
key is an index, the total key size may not execeed 900 bytes, so you
cannot use things like text or nvarchar(MAX).
>
>
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
>
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx
What is uniqueidentifier as a data type?
Also what is the data type for setting unique STRINGS ((nchar,
nvarchar), for example to be used for emails and user names in a user
registration system).
SQL Server does not allow me set primary keys for columns where data
types are not INT.
Thanks in advance.A uniqueidentifier is equivelent to a GUID, if you've ever used it in
any other programming language. As for SQL Server not allowing you to
use other data types than int, you must be misinterpreting the errror
message. What error message do you see?
Dot Net Daddy wrote:
Quote:
Originally Posted by
Hello,
>
What is uniqueidentifier as a data type?
>
Also what is the data type for setting unique STRINGS ((nchar,
nvarchar), for example to be used for emails and user names in a user
registration system).
>
SQL Server does not allow me set primary keys for columns where data
types are not INT.
>
Thanks in advance.
Quote:
Originally Posted by
What is uniqueidentifier as a data type?
A uniqueidentifier is a 128-bit value, which is generated in such a way
that it is guaranteed to be unique in the whole universe. (Well, at least
this planet.) They are not specific to SQL Server, but Windows has them
all over the place, but calls them GUIDs.
Quote:
Originally Posted by
Also what is the data type for setting unique STRINGS ((nchar,
nvarchar), for example to be used for emails and user names in a user
registration system).
>
SQL Server does not allow me set primary keys for columns where data
types are not INT.
Huh? You can use almost any data type for primary keys. Since a primary
key is an index, the total key size may not execeed 900 bytes, so you
cannot use things like text or nvarchar(MAX).
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Great. It has done it. I was using nvarchar(MAX) as you told.
Now it is ok.
Thank you very much for your help guys.
Erland Sommarskog wrote:
Quote:
Originally Posted by
Dot Net Daddy (cagriandac@.gmail.com) writes:
Quote:
Originally Posted by
What is uniqueidentifier as a data type?
>
A uniqueidentifier is a 128-bit value, which is generated in such a way
that it is guaranteed to be unique in the whole universe. (Well, at least
this planet.) They are not specific to SQL Server, but Windows has them
all over the place, but calls them GUIDs.
>
Quote:
Originally Posted by
Also what is the data type for setting unique STRINGS ((nchar,
nvarchar), for example to be used for emails and user names in a user
registration system).
SQL Server does not allow me set primary keys for columns where data
types are not INT.
>
Huh? You can use almost any data type for primary keys. Since a primary
key is an index, the total key size may not execeed 900 bytes, so you
cannot use things like text or nvarchar(MAX).
>
>
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
>
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx
MS-SQL one .NDF file missing How to Recover/Rebuild DB?
Dear All,
SOS Please Help.
I have a MS-SQL DB with 4 .ndf files. One (first) .ndf file is missing.
somehow got deleted??. Is there any way can rebuild my DB.
SOS Please Help.
I have a MS-SQL DB with 4 .ndf files. One (first) .ndf file is missing.
somehow got deleted??. Is there any way can rebuild my DB.
The .MDF and .LDF files are in tact.
Please help asap.
Dhumbak
*** Sent via Developersdex http://www.developersdex.com ***It depends what this NDF was created for in the first place. If it was
storing indexes then you can rebuild these indexes. If it was data and
you have no backup on the filegroup then you probably lost your data in
loosing the NDF.
Dhungu Dhumbak wrote:
Quote:
Originally Posted by
Dear All,
SOS Please Help.
I have a MS-SQL DB with 4 .ndf files. One (first) .ndf file is missing.
somehow got deleted??. Is there any way can rebuild my DB.
>
The .MDF and .LDF files are in tact.
Please help asap.
>
Dhumbak
>
*** Sent via Developersdex http://www.developersdex.com ***
Subscribe to:
Posts (Atom)