better method for DTS: February 2012

Friday, February 24, 2012

Is there any sql query where my tables stored in the databas

in excell
clcik on
data >get external data> new database query
"Chandra" wrote:

> Hi Satish
> You can check the following query
> http://chanduas.blogspot.com/2005/0...n-database.html
> this will list the tables in the database. You can save the result in
> the excel sheet
> please let me know if u have any questions
> best Regards,
> Chandra
> http://www.SQLResource.com/
> http://chanduas.blogspot.com/
> ---
> *** Sent via Developersdex http://www.examnotes.net ***
>well, this is also a very good idea
--
best Regards,
Chandra
http://chanduas.blogspot.com/
http://www.SQLResource.com/
---
"jose g. de jesus jr mcp, mcdba" wrote:
> in excell
> clcik on
> data >get external data> new database query
>
> "Chandra" wrote:
>

Is there any sp deals with the table sysproperties ?

Hi,
Is the table sysproperties a new added one to sql 8.0 ?
Because I cannot found the description about it in SQL
Server Online book.
BTW: Is there any sp deals with this table ?
Thansk
.Hi,
Thanks.
>--Original Message--
>Hi,
>sysproperties is used to store the extended
properties .Adding and
>retrieving extended properties was introduced in
SQL2000.But for that there
>is no need to use sysproperties, instead you can use the
system functions
>sp_addextendedproperty and ::fn_listextendedproperty.
>--
>Dinesh.
>SQL Server FAQ at
>http://www.tkdinesh.com
>"gohigh" <gohigh@.sh163.net> wrote in message
>news:01e201c356b2$45835c30$a101280a@.phx.gbl...
>> Hi,
>> Is the table sysproperties a new added one to sql 8.0 ?
>> Because I cannot found the description about it in SQL
>> Server Online book.
>> BTW: Is there any sp deals with this table ?
>> Thansk
>> .
>>
>
>.
>

Is there any simple or quick guides let me to study how to create reports, deloy and runni

Is there any simple or quick guides let me to study how to create reports,
deloy and running the reports services?Hi,
Best quide is Microsoft online help for reporting services. If you want a
quick guide best bet is "HOW TO's" in the online help. You can quickly learn
how to create, deploy and Manage.
Regards
Amarnath
"ABC" wrote:
> Is there any simple or quick guides let me to study how to create reports,
> deloy and running the reports services?
>
>

Is there any sample code which creates login and user of one database using SMO?

Dear All

Is there any sample code which creates login and user for database using SMO?I cannot find any C# sample code can do this.

Any help would be appreciated:)

Here's a sample, you can build from it to allow for creation of group or other login types, etc.

Set up connection to server and database:

_srv = new Server(@."MySqlServer\Instancename");

_dbname = @."MyDatabase";

_db = _srv.Databases[ DBName];

lgn.DefaultDatabase = _dbname;

lgn.LoginType = LoginType.WindowsUser;

lgn.Create();

User usr = new User(_db, @."MyUserName");

usr.Login = loginId;

usr.Create();

--Stan

|||

My bad, I was reading this topic in reverse date order and just noticed that another thread asked a similar question and got basically the same response.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=195387&SiteID=1

|||

Hi,

sorry the same day you posted the question I wrote quick application, I imported that in a q&d Winform solution, which might help you to see the way to do it:

http://www.sqlserver2005.de/SharedFiles/UserMappingwithSMO.zip

HTH; Jens Suessmeyer.

http://www.sqlserver2005.de

Is there any sample code to demo the SSB send messages with same sql instance?

my case is very simple:

I want write a stored procedure to send a xml to another database. The stored procedure is called by tables triggers when some data is changed under the specific conditions.

this should be exactly what you need:

http://www.sqlteam.com/article/centralized-asynchronous-auditing-with-service-broker

Is there any requirement to install the client component of RS?

Hi all,
I only install the client component of reporting service,
at the beginning, when it is install the support file, it
always failed with only an 'error' message. No other
messages.
I check the log file. There is
Internet Information Services 5.0 (IIS) or later is either
not installed, or it is configured in a way that is
incompatible with a Reporting Services installation.
ASP.NET version 1.1 is not installed or is not registered
with your Web server. ASP.Net is required for the Report
Server and the Report Manager components.
and in the stlog file, I notice
<Prerequisite Name="IsIISInstalled" Checked="Metabase"
Item="/" Found="Can not open metabase" Expected="No error"
Result="Fail" />
To only install the client component, should I install IIS
and ASP1.1 and how do I check that every thing is OK to
install it.
Thanks.
Lindayou need Visual studio.Net 2003 and .Net framework 1.1
IIS is not required
"YUKON2005" <anonymous@.discussions.microsoft.com> a écrit dans le message de
news:38d201c471e9$c199d760$a501280a@.phx.gbl...
> Hi all,
> I only install the client component of reporting service,
> at the beginning, when it is install the support file, it
> always failed with only an 'error' message. No other
> messages.
> I check the log file. There is
> Internet Information Services 5.0 (IIS) or later is either
> not installed, or it is configured in a way that is
> incompatible with a Reporting Services installation.
> ASP.NET version 1.1 is not installed or is not registered
> with your Web server. ASP.Net is required for the Report
> Server and the Report Manager components.
> and in the stlog file, I notice
> <Prerequisite Name="IsIISInstalled" Checked="Metabase"
> Item="/" Found="Can not open metabase" Expected="No error"
> Result="Fail" />
> To only install the client component, should I install IIS
> and ASP1.1 and how do I check that every thing is OK to
> install it.
> Thanks.
> Linda
>|||Thank you!
I will check it.
:)

Is there any reported issues running SQL Server 2005 Enterprise 64bit Management Studio version?

At the moment I'm trying to find out if there are any reported issues with Management Studio running on a SQL Server 2005 Ent 64bit edition?

Can anyone help?

My reason for asking is really one of curiosity, one of my colleagues has said it doesn't work, I'm trying to research this claim as I do not have access to a 64 bit machine to try it on but would like to move my future project in this direction hence why I'm asking.

Many Thanks.

Dave

Hi Dave,

Technically, there aren't different versions of Management Studio, only 1 (client tools are distributed with all editions, except Express)...though, there are different platform builds (i.e. IA64,x64,x86)...

Management studio with Sql2k5 is fully 64bit compatible, and will run natively on an IA64 or x64 box...

HTH

|||There are issuses with SQL Server 2005 64 bit and the Management Studio that are explained here http://support.microsoft.com/default.aspx/kb/906892.
The Management Studio will run on the 64 bit platform but it will be slow and tend to freeze, the reason being it's still a 32 bit application. MS solution is to run the Management Studio on a 32 bit computer and connect to the SQL server that way. Why they don't have a 64 bit version of the Management Studio is anyone's guess.
RLB

Is there any reported issues running SQL Server 2005 Enterprise 64bit Management Studio version?

At the moment I'm trying to find out if there are any reported issues with Management Studio running on a SQL Server 2005 Ent 64bit edition?

Can anyone help?

Many Thanks.

Dave

Hi Dave,

Management studio with Sql2k5 is fully 64bit compatible, and will run natively on an IA64 or x64 box...

HTH

Is there any REGEXP library for TSQL?

Hi guys,

Sounds a bit strange, however, if we could put some calculation in
stored procedure it would be quite convenient, just... where can I find
a REGEXP library for matching checking? thanks.

yours,
athos"athos" <athos.liu@.gmail.com> wrote in message
news:1130877928.278288.62290@.g44g2000cwa.googlegro ups.com...
> Hi guys,
> Sounds a bit strange, however, if we could put some calculation in
> stored procedure it would be quite convenient, just... where can I find
> a REGEXP library for matching checking? thanks.
> yours,
> athos

Take a look at the LIKE topic in Books Online to see if it meets your
requirements. Not regex but it does support some simple pattern matching.

--
David Portas
SQL Server MVP
--|||LIKE is not powerful enough. btw, COM is prohibited. thanks.|||athos (athos.liu@.gmail.com) writes:
> Sounds a bit strange, however, if we could put some calculation in
> stored procedure it would be quite convenient, just... where can I find
> a REGEXP library for matching checking? thanks.

It does not sound strange at all. Some DB Engines have SIMILAR TO, and
this might even be in ANSI. I believe this uses some form of regexps.
I've been longing for it myself at times.

But for SQL2000 there is only LIKE which is far from whole covering.
You can use patindex or charindex for some stuff, but in essence it's
all very primitive.

In SQL 2005, there is no better support in T-SQL, but you can call a CLR
routine that uses the RegEx classes in .Net.

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||> In SQL 2005, there is no better support in T-SQL, but you can call a CLR
> routine that uses the RegEx classes in .Net.

I guess for SQL 2000, you could use a non-COM library as an "extended
procedure"?

--
With regards,

But performance would be awful and the code would be messy.

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||> >> In SQL 2005, there is no better support in T-SQL, but you can call a
CLR
> >> routine that uses the RegEx classes in .Net.
> > I guess for SQL 2000, you could use a non-COM library as an "extended
> > procedure"?
> But performance would be awful and the code would be messy.

I've never written any extended procedures, so perhaps you could
explain why this would give awful performance?

I imagine the call could be as:

select ...
from ...
where myregexp_match(mycolumn, myexpression, myvalue)

Why would this be any slower than COM or .NET? Isn't this partly
what extended procedures were meant for?

--
With regards,

Martijn Tonies
Database Workbench - tool for InterBase, Firebird, MySQL, Oracle & MS SQL
Server
Upscene Productions
http://www.upscene.com
Database development questions? Check the forum!
http://www.databasedevelopmentforum.com|||"Martijn Tonies" <m.tonies@.upscene-removethis.nospam.com> wrote in message
news:11mhdiekuhe26e9@.corp.supernews.com...
> > >> In SQL 2005, there is no better support in T-SQL, but you can call a
> CLR
> > >> routine that uses the RegEx classes in .Net.
> > > > I guess for SQL 2000, you could use a non-COM library as an "extended
> > > procedure"?
> > But performance would be awful and the code would be messy.
> I've never written any extended procedures, so perhaps you could
> explain why this would give awful performance?
> I imagine the call could be as:
> select ...
> from ...
> where myregexp_match(mycolumn, myexpression, myvalue)
> Why would this be any slower than COM or .NET? Isn't this partly
> what extended procedures were meant for?

I'm guessing the main reason is that in SQL 2000, it executes outside of SQL
Server, which means for every call there's delay as it has to call out of
its address space. SQL 2005 CLR code executes within the same memory space
as SQL Server.

> --
> With regards,
> Martijn Tonies
> Database Workbench - tool for InterBase, Firebird, MySQL, Oracle & MS SQL
> Server
> Upscene Productions
> http://www.upscene.com
> Database development questions? Check the forum!
> http://www.databasedevelopmentforum.com|||Martijn Tonies (m.tonies@.upscene-removethis.nospam.com) writes:
> I've never written any extended procedures, so perhaps you could
> explain why this would give awful performance?
> I imagine the call could be as:
> select ...
> from ...
> where myregexp_match(mycolumn, myexpression, myvalue)

That's not really how you call extended stored procedure. But you could
encapsulate the XP in a user-defined function to get this syntax. However,
there is a big overhead for calling a UDF in a WHERE clause in SQL 2000
(this overhead has been reduced in SQL 2005). If you then add a call to
extended stored procedure that gives you context switches and all, it's
getting really bad.

Then add to this that if you have a bug in your XP that causes an
access violation or similar, it's not only the XP that crashes. You
blow away the entire SQL Server.

> Why would this be any slower than COM or .NET? Isn't this partly
> what extended procedures were meant for?

The CLR stuff in SQL 2005 is a lot more integrated in SQL Server and there
is far less overhead for invoking CLR. In fact, say that you have a decently
complex operation like some string manipulation that you can perform in
T-SQL, it is very likely to perform better in a CLR UDF. (But if you
start do data access from the CLR, it's a different picture.)

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||> > I've never written any extended procedures, so perhaps you could
> > explain why this would give awful performance?
> > I imagine the call could be as:
> > select ...
> > from ...
> > where myregexp_match(mycolumn, myexpression, myvalue)
> That's not really how you call extended stored procedure. But you could
> encapsulate the XP in a user-defined function to get this syntax. However,
> there is a big overhead for calling a UDF in a WHERE clause in SQL 2000
> (this overhead has been reduced in SQL 2005). If you then add a call to
> extended stored procedure that gives you context switches and all, it's
> getting really bad.

Then when are XPs actually useful?

> Then add to this that if you have a bug in your XP that causes an
> access violation or similar, it's not only the XP that crashes. You
> blow away the entire SQL Server.

I understand this part, seems to be the case with pretty much all
extending to DB engines (unless managed or Java or whatever).|||Martijn Tonies (m.tonies@.upscene-removethis.nospam.com) writes:
>> That's not really how you call extended stored procedure. But you could
>> encapsulate the XP in a user-defined function to get this syntax.
>> However, there is a big overhead for calling a UDF in a WHERE clause in
>> SQL 2000 (this overhead has been reduced in SQL 2005). If you then add
>> a call to extended stored procedure that gives you context switches and
>> all, it's getting really bad.
> Then when are XPs actually useful?

When the stuff you want to do with them are not used to evaluate queries.
For instance, we have an extended stored procedure that performs a loopback
and writes messages to a log table when an error is detected. (The point
with the loopback is that we want the log records to persist even if there
is a rollback.)

Another possible application is some sort of signaling, to inform some
external process "Hey, I've just inserted 10000 rows, you might be
interested in those".

But it is correct that XP:s, as well as sp_OAcreate & co for calling
OLE objects, have limited use, and something you only use for special
cases.

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||> But it is correct that XP:s, as well as sp_OAcreate & co for calling
> OLE objects, have limited use, and something you only use for special
> cases.

Thanks for the explanation.

--
With regards,

Is there any reason to use port 1433?

Hello
I am new to SQL security.
Some days ago I happened to open my event log and found lots of attacks have
been going on to my SQL server 2005. Maybe for some months. I have been
using firewall and Windows integrated authentication. The attacks might be
not quite successful. Anyway, I changed the SQL server port from 1433 to
something another. Strikingly all the attacks have gone. Now I am curious
why they use port number 1433. Is there any reason to use the port, benefit
or something? Or is there any danger or caution to use something other than
1433?Han
By default sql server uses tcp/ip 1433 port for communication. so it is
quite common to get attack on that port.
VT
"Han" <hp4444@.kornet.net.korea> wrote in message
news:O3yHPmyKHHA.1280@.TK2MSFTNGP04.phx.gbl...
> Hello
> I am new to SQL security.
> Some days ago I happened to open my event log and found lots of attacks
> have been going on to my SQL server 2005. Maybe for some months. I have
> been using firewall and Windows integrated authentication. The attacks
> might be not quite successful. Anyway, I changed the SQL server port from
> 1433 to something another. Strikingly all the attacks have gone. Now I am
> curious why they use port number 1433. Is there any reason to use the
> port, benefit or something? Or is there any danger or caution to use
> something other than 1433?
>|||Thank you for your quick reply, vt.
Yes, 1433 is one of well-known ports. So, what I am asking is, why you
should use the well-known port.
To now one problem I found giving up the 1433 is adding my own port number
to my connection strings.
server=server-name,10001; ...
That's just trivial pain compared with the freedom from lots of ridiculous
attacks. I hope the pain is all I should consider, but I am not sure...
"vt" <vinu.t.1976@.gmail.com> wrote in message
news:uCgwesyKHHA.4992@.TK2MSFTNGP04.phx.gbl...
> Han
> By default sql server uses tcp/ip 1433 port for communication. so it is
> quite common to get attack on that port.
>
> VT
>
>
> "Han" <hp4444@.kornet.net.korea> wrote in message
> news:O3yHPmyKHHA.1280@.TK2MSFTNGP04.phx.gbl...
>|||Han
Well you don't need to take all those hazels of passing port number, you can
configure sql server to use which ever port you want
To do that, if you are using sql 2000
Open sql server network utility under Microsoft SQL Server program group.
Select property of TCP/IP and change the port number there..
thats it , sql srever will start listining to that port
VT
"Han" <hp4444@.kornet.net.korea> wrote in message
news:%23QvONA0KHHA.1424@.TK2MSFTNGP04.phx.gbl...
> Thank you for your quick reply, vt.
> Yes, 1433 is one of well-known ports. So, what I am asking is, why you
> should use the well-known port.
> To now one problem I found giving up the 1433 is adding my own port number
> to my connection strings.
> server=server-name,10001; ...
> That's just trivial pain compared with the freedom from lots of ridiculous
> attacks. I hope the pain is all I should consider, but I am not sure...
> "vt" <vinu.t.1976@.gmail.com> wrote in message
> news:uCgwesyKHHA.4992@.TK2MSFTNGP04.phx.gbl...
>|||Han,
1433 is the default port that SQL Server uses for TCP/IP client
communication. You can configure that to be another port, but hackers
will find them and attempt hacking there too.
I wrote a utility to do a bit of self defense for these hacking attempts.
http://www.creeksolutions.com/Produ...92/Default.aspx
BlockSSHacking runs as a Windows service protecting your SQL Server from
brute force hacking attempts coming from the Internet.
The service checks your system every 5 minutes (configurable setting)
for evidence of ongoing hacking. If such attempts are in progress then
those source addresses are blocked from future access to your server
(quickly leading to you recovering your bandwidth).
BlockSSHacking notifies you via email when it has blocked someone from
hacking your system.
Henrik
Han wrote:
> Hello
> I am new to SQL security.
> Some days ago I happened to open my event log and found lots of attacks ha
ve
> been going on to my SQL server 2005. Maybe for some months. I have been
> using firewall and Windows integrated authentication. The attacks might be
> not quite successful. Anyway, I changed the SQL server port from 1433 to
> something another. Strikingly all the attacks have gone. Now I am curious
> why they use port number 1433. Is there any reason to use the port, benefi
t
> or something? Or is there any danger or caution to use something other tha
n
> 1433?
>

Is there any reason to have Rpt Svc installed on a machine without IIS?

We have a server that has the report databases installed but I can't
deploy my report. I get the error - The report server cannot open a
connection to the report server database. A connection to the datgbase
is required for all request and processing.RS is an asp.net application. It requires IIS. If you want RS on the same
machine as the SQL Server DB then that machine has to have IIS installed on
it.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
<phillip_putzback@.insightbb.com> wrote in message
news:1128115012.134115.42460@.g43g2000cwa.googlegroups.com...
> We have a server that has the report databases installed but I can't
> deploy my report. I get the error - The report server cannot open a
> connection to the report server database. A connection to the datgbase
> is required for all request and processing.
>

Is there any Property Like Rownum in oracle

Hi All...

I have to write one query where i have to display the ID as 1,2 3, 4 in oracle we used to ROWNUM to display ...in MS SQL server is there any property to show?

i have to display like

ID ProgramCount
1 4
2 6
3 5
4 1
5 2

AshutoshHave look here:
http://www.microsoft.com/resources/documentation/sql/2000/all/reskit/en-us/part2/c0761.mspx

Is there any point in views? vs stored procedures?

I do all my data access through stored procedures. Is there any benefit in creating views that the stored procedure accesses. At the moment, I tend to just write the select and join within the stored procedure.

Do you have to do any manipulation of the data? Are the stored procedures doing anything else than just returning data? Will the data change? Do you need to pass any parameters to the stored procedure?

As you can probably guess by my questions, there isn't a straight forward answer as it really depends on the data and what you are doing with it as to which method you should choose for each function.

|||

An interesting philosophical discussion is no doubt about to erupt.

If you are going to be re-using a very complex join, it makes sense to use a view; kind of like a re-usable business object. Its generally easier to maintain than a sp and is more accessible to people coming behind you. You can more easily make little tweaks if you need to make variations on a theme.

But of course sps are a lot more powerful.

|||I have seperate stored procedures for updating the data. These stored procedures return data. But my real question is, within the stored procedure, is it better to do this:

CREATE PROCEDURE mySp1
@.x int
AS
SELECT *
FROM View_myView
WHERE x = x

Or do this

CREATE PROCEDURE mySp1
@.x int
AS
SELECT *
FROM myTable1
INNER JOIN myTable2 ON ...
INNER JOIN myTable3 ON ...
WHERE x = x|||

jagdipa:
is it better to do this:
CREATE PROCEDURE mySp1
@.x int
AS
SELECT *
FROM View_myView
WHERE x = x
Or do this
CREATE PROCEDURE mySp1
@.x int
AS
SELECT *
FROM myTable1
INNER JOIN myTable2 ON ...
INNER JOIN myTable3 ON ...
WHERE x = x

There isn't really a right answer. You've given a dummy scenario so we can only really offer general advice and we can't say whether one method will be better than the other.

Either method will work, and it really depends on what other objects will be accessing this data as to whether a view is needed or not. You will also have to look at the execution plans to see if there are any performance issues to take note of.

|||"Either method will work, and it really depends on what other objects will be accessing this data as to whether a view is needed or not."

This is really what I want to know. The example I gave is actually what I am doing (with a few more tables).

As far as I understand, a view is optimised. But so is a stored procedure. So, in theory, using either method should have the exact same performance (they are optomised in the same way using the same algorithms). I am probably wrong here.

But there is also the design point of view. Is there a design reason for using views? (exect the obvious putting security on a view seperatly from a stored procedure).|||

jagdipa:
This is really what I want to know.

But we don't know what other objects will be accessing the data. It's your database so only you know the answer to that question.

As I've said there is no right answer. You will have to decide for yourself which is the best approach, based on how often the data gets accessed, what other functions may need the same data and any performance issues that come out of your testing. Sorry I can't give you a direct answer as to which one is best but that's because one isn't simply better than the other. It all boils down to the individual needs of the database and that's something that only you can answer.

jagdipa:
But there is also the design point of view. Is there a design reason for using views?

If the data will be reused in several places then it makes sense to consolidate it into a view. Again, this is just theory and may not be the best solution for your needs.

|||

There are a number of real-world parameters to consider beyond ease of initial coding. Its probably easier to maintain a view in a large organization, plus its a whole lot easier for someone to look at in order to determine whether it's something they need to use (you could set up a view schema to act like a business object library). If you leave, the person coming in behind you can look at the views with a click,as opposed to having to execute the stored procedures.

Generally, the more static an object is, the easier it is to use a view for it's datasource. If you have something that gets databound on page load and doesn't change, a view is fine and dandy. And you could use a view as your base datasource and add filters to it if you are doing cascading ddls, for example.

But if you build a large object library, especially if you build generators for it, it's probably better to go with stored procedures. People following after you will have to be pretty high-speed anyway to be able to work with your code, so accessability really isn't an issue, and users can look at using your business objects as datasources instead of your views. I guess the higher you are scaling, the better sps are versis views.

|||

I stand firmly and forthrightly with those who say, "It depends!"

I would just add these comments (but really I agree with just about everything everyone else has said):

1. If the join is complicated, using a view sure makes it easier to reuse. It's much less error prone.

2. Views sometimes come with an unanticipated consquence, namely that you are guaranteed to access all the tables in the view. Other programmers, who may not know exactly what's in the view, may use a view incorrectly as a result. For example, lets say you have a view that joins 5 tables. Someone else comes along and retrieves data from the view, but really they only need data from 3 of the tables. By using the view, you not only force them to access all 5 but -- more importantly -- it may affect what data gets returned depending on whether you're using inner joins vs outer joins. I've seen this happen.

3. Views also have a nice security implication -- you can control access through views, though in practice this isn't done all that much.

Also, remember that your choice is NOT view vs proc because you can (should, some would argue) use a proc with a view.

|||Thanks for the clarifications. Views are ok, but I think I will use them sparingly. The reason for this is because I use a few user defined functions as well. In a stored procedure, I can create a temp table and pull the data I want into this. Then I can run the function on just that data (instead of all the data).

I think dbland07666 is right - especially with point 2. When I did not have much experience, I stuck everything in views, even when only pulling out very little. The view died very quickly when I added another 5 user defined functions to it !!!|||

Now that you are more experienced, I think you should look into developing a business object layer. It will allow you to do things programatically that would require dozens of lines of code with a single call. For example, you could so something like Personnel.GetList() to create a collection of Personnel objects which you can sort or filter or what-have-you in the object layer as opposed to calling stored procedures to do these various things with parameters. It's faster than going to the database and can be made to reside in cache so it scales up really nicely for multi-user environments.

|||Hi Charles,

I would love to learn more on this. Have you got a good tutorial I can use?
At the moment, I am going towards a sort of middle tier - I am new at this so its not great. I just create classes that access the relevant data (via stored procedures). It has come in very useful at times. But I am only creating web pages for a website that is probably only accessed 5 times an hour !! (its a B2B website).|||

http://aonaware.com/OOP1.htm

You can do stuff like build server-side validation into your objects to protect your database, you can write generic screens that have behaviors that are inherited by child screens which handle specific objects, all sorts of good stuff.

Here's the framework we use at this shop. It's free and its growing...

http://forums.lhotka.net/forums/default.aspx

Is there any plan to add new techniques to improve the data mining part of SQL Server

I have worked with many data mining softwares and found SQL Data mining part of Microsoft product needs to imrove a lot to whoo the customers.

We continue to advance the data mining functionality in future versions of SQL Server. What aspects do you feel need improvement in order to "whoo" customers?

Thx

-Jamie

|||I think It'd be great if you guys were able to implement a little more in the way of the IDE telling you what was/wasn't possible... Such as not being able to create a lift chart for association models, and describing cases/nested tables for example. Or simply refer people to the MSDN documentation. Also, improving the UI, at least for the predictions tab would be nice. One thing I've noticed that annoys me is that when you hit the results button when writing DMX from the "SQL Page", it switches the button to automatically go to the design view, rather than the SQL page again. So everytime I must click on the small dropdown arrow and click "SQL" again...and again. Overall it's very functional and seems to be a big step up from the last version!|||

I will explain.

1.If you add more algorithms like logistic regressions or regression models, Machine learning (both supervised and unsupervised learnings), more advance decision trees algorithms(like CHAID, CART, QUEST and EXHAUSTIVE QUEST), more associative modelings like market basket analysis & sequential modeling, pricipal componenet analysis, it will be great and all algorithms are very good kind of decision making algorithms in respetive fields like Finance, Banking, market research, retail, telecom, CRM etc. For eg: If take risk modeling in Banking or finance domian, the logistic regression model plays an important role to score the default risk.

2. I have seen that all major players in Data mining like SAS or SPSS, they have features to connect with MS SQL SEVRVER and share the modeling features of later. Is there any feature vice versa?

3. Data mining plays a key role in modern CRM. If you have a better data mining features and you can have better CRM analytic software itself.

4. Text mining plays a key role in call center data analysis, Credit card analysis, telecom data modeling and even we can use this for better web mining. Why can not you try this kind of advanced features.

I think this explationation sufficient for your question to whoo customers. A better decision making algorithms and its proper implimentation in time and monitoring helps companies to save millions of dollors, in single year itself (eg: citi corp, DSP Merril lynch, Chase, Wal-Mart, Target (Even I was in the analytical team for some time), etc).

Thanks

Ajesh

|||

Thanks Ajesh - as Jamie says, we will continue to invest in improving the features of SQL Server data mining. It is great to get feedback from people on the forum or in mail that helps us to plan future versions. And, in fact, some of what you are asking for is already in the product - logistic regression, association rules (for market basket analysis) for example. Sharing of models is enabled between different tools mostly by PMML which SLQ Server, SPSS and SAS all support in our own ways.

Data mining does indeed play an important role in CRM. Microsoft Dynamics CRM has released a special "Analytics Foundation" which enables CRM users to integrate SQL Server Analysis Services (OLAP and Data Mining)

You can read more about the Analytics Foundation here:

http://www.microsoft.com/dynamics/crm/product/analyticsfoundation.mspx

And there is an interesting article here in destinationCRM about the role of data mining in the future of CRM and Microsoft's impact: http://www.destinationcrm.com/articles/default.asp?ArticleID=6833

Text mining is available in SQL Server through the SSIS text mining components. See the tutorial here: http://www.sqlserverdatamining.com/DMCommunity/Tutorials/default.aspx

So, as you can see, we have made some very significant investments in these areas, and we fully expect to continue that momentum through many releases to come.

|||One can also add features to support Ontologies, that can be useful for semantic analysis. Also a standardised ontology representation could be maintained to enable any kind of APIs to access the database as and when required.|||

I really agree with Jamie and Donald but I have a suggession that if you can integrate data mining, text mining and webmining into one platform or one single module, this will be helpful for thousands of users.

Thanks

Visiting lecturer

Madras University-Chennai

India

Also Analyst

JDA Software India, India

|||

Thanks - that is certainly something we should consider for the future.

When you say "web mining" are there specific features and functions that you would like to see?

|||

I am suggesting the following 2 concepts where you can look into.

1. web mining for business analysis
2. web mining for technical analysis

Also I appreaciate special functional enhancements for applications like

1. Customer profiling
2. personalization
3. market segmentation
4. Target marketing
5. Cross-selling
6. Integration with CRM

Over all a "COMPLETE E-BUSINESS MARKETING SOLUTION" and "Implementaion methodology for solutions". This kind of solution already exist for DataStage, etc.

Thanks

Ajesh

Is there any plan to add new techniques to improve the data mining part of SQL Server

I have worked with many data mining softwares and found SQL Data mining part of Microsoft product needs to imrove a lot to whoo the customers.

We continue to advance the data mining functionality in future versions of SQL Server. What aspects do you feel need improvement in order to "whoo" customers?

Thx

-Jamie

I will explain.

2. I have seen that all major players in Data mining like SAS or SPSS, they have features to connect with MS SQL SEVRVER and share the modeling features of later. Is there any feature vice versa?

3. Data mining plays a key role in modern CRM. If you have a better data mining features and you can have better CRM analytic software itself.

Thanks

Ajesh

|||

You can read more about the Analytics Foundation here:

http://www.microsoft.com/dynamics/crm/product/analyticsfoundation.mspx

And there is an interesting article here in destinationCRM about the role of data mining in the future of CRM and Microsoft's impact: http://www.destinationcrm.com/articles/default.asp?ArticleID=6833

Text mining is available in SQL Server through the SSIS text mining components. See the tutorial here: http://www.sqlserverdatamining.com/DMCommunity/Tutorials/default.aspx

So, as you can see, we have made some very significant investments in these areas, and we fully expect to continue that momentum through many releases to come.

Thanks

Visiting lecturer

Madras University-Chennai

India

Also Analyst

JDA Software India, India

|||

Thanks - that is certainly something we should consider for the future.

When you say "web mining" are there specific features and functions that you would like to see?

|||

I am suggesting the following 2 concepts where you can look into.

1. web mining for business analysis
2. web mining for technical analysis

Also I appreaciate special functional enhancements for applications like

1. Customer profiling
2. personalization
3. market segmentation
4. Target marketing
5. Cross-selling
6. Integration with CRM

Over all a "COMPLETE E-BUSINESS MARKETING SOLUTION" and "Implementaion methodology for solutions". This kind of solution already exist for DataStage, etc.

Thanks

Ajesh

Is there any place I can find the listing of SQL Reporting fuction

Is there any place I can find the listing of SQL Reporting pre-defined fuction?
ThanksUse this as a starting point - it will lead to built-in RS aggregate
functions, various object collections in the ReportObjectModel, etc.:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/rscreate/htm/rcr_creating_expressions_v1_6fhv.asp
Besides that, you can use anything available in the VB.NET run-time library:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vblr7/html/vaoriVBRuntimeLibraryKeywords.asp
And by default, everything that is contained within the following
namespaces:
* System
* System.Math
* System.Convert
* Microsoft.VisualBasic
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"Laipond" <Laipond@.discussions.microsoft.com> wrote in message
news:7B1CB9BF-9902-48B2-BC77-6DFCA3DEAD5A@.microsoft.com...
> Is there any place I can find the listing of SQL Reporting pre-defined
> fuction?
> Thanks|||Thank you Robert
"Robert Bruckner [MSFT]" wrote:
> Use this as a starting point - it will lead to built-in RS aggregate
> functions, various object collections in the ReportObjectModel, etc.:
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/rscreate/htm/rcr_creating_expressions_v1_6fhv.asp
> Besides that, you can use anything available in the VB.NET run-time library:
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vblr7/html/vaoriVBRuntimeLibraryKeywords.asp
> And by default, everything that is contained within the following
> namespaces:
> * System
> * System.Math
> * System.Convert
> * Microsoft.VisualBasic
> -- Robert
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "Laipond" <Laipond@.discussions.microsoft.com> wrote in message
> news:7B1CB9BF-9902-48B2-BC77-6DFCA3DEAD5A@.microsoft.com...
> > Is there any place I can find the listing of SQL Reporting pre-defined
> > fuction?
> >
> > Thanks
>
>

Is there any performance differences between function type IF and TF?

I could not found more references in BOA about functions that return table.
Does anybody know if there is any performance differences between these two
type of functions?
IF = Inlined table-function
TF = Table function
Thanks,
Lijun
Lijun Zhang (nospam@.nospam.nospam) writes:
> I could not found more references in BOA about functions that return
> table. Does anybody know if there is any performance differences between
> these two type of functions?
> IF = Inlined table-function
> TF = Table function
Yes, there is.
An inline function is in fact not a function at all; it is a macro. The
optimiser pastes the text of the function into the query and optimizes
the result.
A multi-step function returns data to a table variable, and the result
of the function is opaque to the optimizer.
Thus, in the former case the optimizer have more information, and
thus better possibilities to create a better execution plan.
But sometimes it can be too much information, so in fact it leads
to poorer performance. But in the long run, inline functions will
give you better performance.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
|||Yes, there may be a significant difference, although of course that
depends on exactly what you are doing.
Despite the similar syntax *inline* table-valued functions are
implemented very differently from *multi-statement* table-valued
functions.
A inline table-valued function consists of a single query that works
very like a view. That is, when the function is referenced in another
query the SQL from both the calling query and the function itself is
considered together so as to arrive at an optimal execution plan.
With a multi-statement table-valued function that kind of optimization
isn't possible. In a multi-statement function the function code is
executed more like a stored procedure and then a result returned to the
calling code for further processing.
If you want to encapsulate a single query in a function then use an
inline TVF, or use a view.
If you need to put multiple statements in a function then you'll have
to use a multi-statement TVF.
David Portas
SQL Server MVP

Is there any performance differences between function type IF and TF?

I could not found more references in BOA about functions that return table.
Does anybody know if there is any performance differences between these two
type of functions?
IF = Inlined table-function
TF = Table function
Thanks,
LijunLijun Zhang (nospam@.nospam.nospam) writes:
> I could not found more references in BOA about functions that return
> table. Does anybody know if there is any performance differences between
> these two type of functions?
> IF = Inlined table-function
> TF = Table function
Yes, there is.
An inline function is in fact not a function at all; it is a macro. The
optimiser pastes the text of the function into the query and optimizes
the result.
A multi-step function returns data to a table variable, and the result
of the function is opaque to the optimizer.
Thus, in the former case the optimizer have more information, and
thus better possibilities to create a better execution plan.
But sometimes it can be too much information, so in fact it leads
to poorer performance. But in the long run, inline functions will
give you better performance.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Yes, there may be a significant difference, although of course that
depends on exactly what you are doing.
Despite the similar syntax *inline* table-valued functions are
implemented very differently from *multi-statement* table-valued
functions.
A inline table-valued function consists of a single query that works
very like a view. That is, when the function is referenced in another
query the SQL from both the calling query and the function itself is
considered together so as to arrive at an optimal execution plan.
With a multi-statement table-valued function that kind of optimization
isn't possible. In a multi-statement function the function code is
executed more like a stored procedure and then a result returned to the
calling code for further processing.
If you want to encapsulate a single query in a function then use an
inline TVF, or use a view.
If you need to put multiple statements in a function then you'll have
to use a multi-statement TVF.
David Portas
SQL Server MVP
--

Is there any performance differences between function type IF and TF?

I could not found more references in BOA about functions that return table.
Does anybody know if there is any performance differences between these two
type of functions?
IF = Inlined table-function
TF = Table function
Thanks,
LijunLijun Zhang (nospam@.nospam.nospam) writes:
> I could not found more references in BOA about functions that return
> table. Does anybody know if there is any performance differences between
> these two type of functions?
> IF = Inlined table-function
> TF = Table function
Yes, there is.
An inline function is in fact not a function at all; it is a macro. The
optimiser pastes the text of the function into the query and optimizes
the result.
A multi-step function returns data to a table variable, and the result
of the function is opaque to the optimizer.
Thus, in the former case the optimizer have more information, and
thus better possibilities to create a better execution plan.
But sometimes it can be too much information, so in fact it leads
to poorer performance. But in the long run, inline functions will
give you better performance.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp|||Yes, there may be a significant difference, although of course that
depends on exactly what you are doing.
Despite the similar syntax *inline* table-valued functions are
implemented very differently from *multi-statement* table-valued
functions.
A inline table-valued function consists of a single query that works
very like a view. That is, when the function is referenced in another
query the SQL from both the calling query and the function itself is
considered together so as to arrive at an optimal execution plan.
With a multi-statement table-valued function that kind of optimization
isn't possible. In a multi-statement function the function code is
executed more like a stored procedure and then a result returned to the
calling code for further processing.
If you want to encapsulate a single query in a function then use an
inline TVF, or use a view.
If you need to put multiple statements in a function then you'll have
to use a multi-statement TVF.
--
David Portas
SQL Server MVP
--

Is there any other faster method to compare and update table?

TABLE1 has 5,000,000 records, TABLE2 has 1,000,000 records.
I must compare these two tables and insert to TABLE3 and update TABLE1.
Is there any other faster method can replace the following method?
Thanks.
---
CREATE PROCEDURE RMSTEST1 AS
DECLARE tb1_cursor CURSOR
FOR
SELECT A5,A11,A28,A30 FROM TABLE1
OPEN tb1_cursor
DECLARE @.V5 CHAR(13),@.V11 CHAR(8),@.V28 INT,@.V30 INT
FETCH NEXT FROM tb1_cursor INTO @.V5,@.V11,@.V28,@.V30
WHILE (@.@.FETCH_STATUS <> -1)
BEGIN
IF @.V11 IN (SELECT B2 FROM TABLE2)
BEGIN
INSERT INTO TABLE3 VALUES (@.V5,'11110000',@.V30-@.V28,'D')
INSERT INTO TABLE3 VALUES (@.V5,'22220000',@.V30-@.V28,'C')
END
ELSE
BEGIN
INSERT INTO TABLE3 VALUES (@.V5,'11120000',@.V30-@.V28,'D')
INSERT INTO TABLE3 VALUES (@.V5,'22230000',@.V30-@.V28,'C')
END
UPDATE TABLE1 SET A39='Y' WHERE CURRENT OF tb1_cursor
FETCH NEXT FROM tb1_cursor INTO @.V5,@.V11,@.V28,@.V30
END
CLOSE tb1_cursor
deallocate tb1_cursorEllen
At first glance a I'd use NOT EXISTS clause to eliminate the rows
SELECT <columns list> FROM Table1
WHERE NOT EXISTS
(SELECT * FROM Table2 WHERE Table1.PK=Table2.PK)
You can insert an output into a temporary table and then to manipulate with
UPDATE statement as you need.
"Ellen" <Ellen@.discussions.microsoft.com> wrote in message
news:EF2570C6-2A36-416E-AF70-BFBB53A20475@.microsoft.com...
> TABLE1 has 5,000,000 records, TABLE2 has 1,000,000 records.
> I must compare these two tables and insert to TABLE3 and update TABLE1.
> Is there any other faster method can replace the following method?
> Thanks.
> ---
> CREATE PROCEDURE RMSTEST1 AS
> DECLARE tb1_cursor CURSOR
> FOR
> SELECT A5,A11,A28,A30 FROM TABLE1
> OPEN tb1_cursor
> DECLARE @.V5 CHAR(13),@.V11 CHAR(8),@.V28 INT,@.V30 INT
> FETCH NEXT FROM tb1_cursor INTO @.V5,@.V11,@.V28,@.V30
> WHILE (@.@.FETCH_STATUS <> -1)
> BEGIN
> IF @.V11 IN (SELECT B2 FROM TABLE2)
> BEGIN
> INSERT INTO TABLE3 VALUES (@.V5,'11110000',@.V30-@.V28,'D')
> INSERT INTO TABLE3 VALUES (@.V5,'22220000',@.V30-@.V28,'C')
> END
> ELSE
> BEGIN
> INSERT INTO TABLE3 VALUES (@.V5,'11120000',@.V30-@.V28,'D')
> INSERT INTO TABLE3 VALUES (@.V5,'22230000',@.V30-@.V28,'C')
> END
> UPDATE TABLE1 SET A39='Y' WHERE CURRENT OF tb1_cursor
> FETCH NEXT FROM tb1_cursor INTO @.V5,@.V11,@.V28,@.V30
> END
> CLOSE tb1_cursor
> deallocate tb1_cursor|||Please include DDL with your posts otherwise we can only guess at your
table structure and exact requirements. Here's an example, assuming B2
is unique in Table2:
INSERT INTO Table3 (/* ... columns list? */)
SELECT T1.a5, ...
CASE WHEN T2.b2 IS NOT NULL THEN '11110000' ELSE '11120000' END,
CASE WHEN T2.b2 IS NOT NULL THEN '22220000' ELSE '22230000' END,
CASE WHEN T2.b2 IS NOT NULL THEN 'D' ELSE 'C' END
FROM Table1 AS T1
LEFT JOIN Table2 AS AS T2
ON T1.a11 = T2.b2 /* B2 is unique? */
--
David Portas
SQL Server MVP
--|||The full script is:
-- Create Tables
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[SUSTES1]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[SUSTES1]
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[SUSTES2]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[SUSTES2]
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[SUSTES3]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[SUSTES3]
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[SUSTES4]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[SUSTES4]
GO
CREATE TABLE [dbo].[SUSTES1] (
[A1] [varchar] (6) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[A2] [varchar] (6) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[A3] [varchar] (6) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[A4] [varchar] (6) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[A5] [varchar] (13) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[A6] [varchar] (6) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[A7] [varchar] (6) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[A8] [varchar] (6) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[A9] [varchar] (6) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[A10] [varchar] (6) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[A11] [varchar] (8) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[A12] [varchar] (6) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[A13] [varchar] (6) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[A14] [varchar] (6) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[A15] [varchar] (6) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[A16] [varchar] (6) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[A17] [varchar] (6) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[A18] [varchar] (6) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[A19] [varchar] (6) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[A20] [varchar] (6) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[A21] [varchar] (6) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[A22] [varchar] (6) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[A23] [varchar] (6) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[A24] [varchar] (6) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[A25] [varchar] (6) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[A26] [varchar] (6) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[A27] [varchar] (6) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[A28] [int] NULL ,
[A29] [varchar] (6) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[A30] [int] NULL ,
[A31] [varchar] (6) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[A32] [varchar] (6) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[A33] [varchar] (6) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[A34] [varchar] (6) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[A35] [varchar] (6) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[A36] [varchar] (6) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[A37] [varchar] (6) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[A38] [varchar] (6) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[A39] [varchar] (6) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[SUSTES2] (
[B1] [char] (9) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[B2] [char] (8) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[B3] [char] (9) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[B4] [char] (9) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[B5] [char] (9) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[B6] [char] (9) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[B7] [char] (9) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[B8] [char] (9) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[B9] [char] (9) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[B10] [char] (9) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[B11] [char] (9) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[B12] [char] (9) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[B13] [char] (9) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[B14] [char] (9) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[B15] [char] (9) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[B16] [char] (9) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[B17] [char] (9) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[B18] [char] (9) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[B19] [char] (9) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[B20] [char] (9) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[B21] [char] (9) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[SUSTES3] (
[C1] [varchar] (13) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[C2] [char] (8) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[C3] [int] NULL ,
[C4] [char] (1) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[SUSTES4] (
[D1] [char] (8) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[D2] [int] NULL ,
[D3] [int] NULL
) ON [PRIMARY]
GO
--Import Datat
BULK INSERT SUSTES1 FROM 'D:\Table1.csv'
WITH (
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n'
)
BULK INSERT SUSTES2 FROM 'D:\Table2.csv'
WITH (
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n'
)
--Create INDEX
CREATE INDEX PK_B2
ON SUSTES2(B2)
GO
--Main Process
DECLARE tb1_cursor CURSOR
FOR
SELECT A5,A11,A28,A30 FROM SUSTES1
OPEN tb1_cursor
DECLARE @.V5 CHAR(13),@.V11 CHAR(8),@.V28 INT,@.V30 INT
FETCH NEXT FROM tb1_cursor INTO @.V5,@.V11,@.V28,@.V30
WHILE (@.@.FETCH_STATUS <> -1)
BEGIN
IF @.V11 IN (SELECT B2 FROM SUSTES2)
BEGIN
INSERT INTO SUSTES3 VALUES (@.V5,'11110000',@.V30-@.V28,'D')
INSERT INTO SUSTES3 VALUES (@.V5,'22220000',@.V30-@.V28,'C')
END
ELSE
BEGIN
INSERT INTO SUSTES3 VALUES (@.V5,'11120000',@.V30-@.V28,'D')
INSERT INTO SUSTES3 VALUES (@.V5,'22230000',@.V30-@.V28,'C')
END
UPDATE SUSTES1 SET A39='Y' WHERE CURRENT OF tb1_cursor
FETCH NEXT FROM tb1_cursor INTO @.V5,@.V11,@.V28,@.V30
END
CLOSE tb1_cursor
deallocate tb1_cursor
--Update Table4
INSERT INTO SUSTES4
SELECT C2,SUM(C3),COUNT(*)
FROM SUSTES3
GROUP BY C2
Ellen
"David Portas" wrote:

> Please include DDL with your posts otherwise we can only guess at your
> table structure and exact requirements. Here's an example, assuming B2
> is unique in Table2:
> INSERT INTO Table3 (/* ... columns list? */)
> SELECT T1.a5, ...
> CASE WHEN T2.b2 IS NOT NULL THEN '11110000' ELSE '11120000' END,
> CASE WHEN T2.b2 IS NOT NULL THEN '22220000' ELSE '22230000' END,
> CASE WHEN T2.b2 IS NOT NULL THEN 'D' ELSE 'C' END
> FROM Table1 AS T1
> LEFT JOIN Table2 AS AS T2
> ON T1.a11 = T2.b2 /* B2 is unique? */
> --
> David Portas
> SQL Server MVP
> --
>|||This should take care of the cursor altogether.
INSERT SUITES3
SELECT A5,CASE WHEN B2 IS NULL THEN 10000 ELSE 0 END +I,A30-A28,J
FROM SUITES1 LEFT JOIN SUITES2 ON SUITES1.A5=SUITES2.B2
CROSS JOIN (SELECT 11110000,'D' UNION SELECT 22220000,'C')X(I,J)
-- WHERE A39='N'
-- UPDATE SUSTES1 SET A39='Y'
-oj
"Ellen Huang" <Ellen Huang@.discussions.microsoft.com> wrote in message
news:BCC149AC-6728-4EF1-B42A-E34C21FF5B8C@.microsoft.com...
> The full script is:
> -- Create Tables
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[SUSTES1]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
> drop table [dbo].[SUSTES1]
> GO
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[SUSTES2]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
> drop table [dbo].[SUSTES2]
> GO
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[SUSTES3]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
> drop table [dbo].[SUSTES3]
> GO
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[SUSTES4]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
> drop table [dbo].[SUSTES4]
> GO
> CREATE TABLE [dbo].[SUSTES1] (
> [A1] [varchar] (6) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
> [A2] [varchar] (6) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
> [A3] [varchar] (6) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
> [A4] [varchar] (6) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
> [A5] [varchar] (13) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
> [A6] [varchar] (6) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
> [A7] [varchar] (6) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
> [A8] [varchar] (6) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
> [A9] [varchar] (6) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
> [A10] [varchar] (6) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
> [A11] [varchar] (8) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
> [A12] [varchar] (6) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
> [A13] [varchar] (6) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
> [A14] [varchar] (6) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
> [A15] [varchar] (6) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
> [A16] [varchar] (6) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
> [A17] [varchar] (6) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
> [A18] [varchar] (6) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
> [A19] [varchar] (6) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
> [A20] [varchar] (6) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
> [A21] [varchar] (6) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
> [A22] [varchar] (6) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
> [A23] [varchar] (6) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
> [A24] [varchar] (6) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
> [A25] [varchar] (6) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
> [A26] [varchar] (6) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
> [A27] [varchar] (6) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
> [A28] [int] NULL ,
> [A29] [varchar] (6) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
> [A30] [int] NULL ,
> [A31] [varchar] (6) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
> [A32] [varchar] (6) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
> [A33] [varchar] (6) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
> [A34] [varchar] (6) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
> [A35] [varchar] (6) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
> [A36] [varchar] (6) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
> [A37] [varchar] (6) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
> [A38] [varchar] (6) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
> [A39] [varchar] (6) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL
> ) ON [PRIMARY]
> GO
> CREATE TABLE [dbo].[SUSTES2] (
> [B1] [char] (9) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
> [B2] [char] (8) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
> [B3] [char] (9) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
> [B4] [char] (9) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
> [B5] [char] (9) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
> [B6] [char] (9) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
> [B7] [char] (9) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
> [B8] [char] (9) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
> [B9] [char] (9) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
> [B10] [char] (9) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
> [B11] [char] (9) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
> [B12] [char] (9) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
> [B13] [char] (9) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
> [B14] [char] (9) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
> [B15] [char] (9) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
> [B16] [char] (9) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
> [B17] [char] (9) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
> [B18] [char] (9) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
> [B19] [char] (9) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
> [B20] [char] (9) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
> [B21] [char] (9) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL
> ) ON [PRIMARY]
> GO
> CREATE TABLE [dbo].[SUSTES3] (
> [C1] [varchar] (13) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
> [C2] [char] (8) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
> [C3] [int] NULL ,
> [C4] [char] (1) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL
> ) ON [PRIMARY]
> GO
> CREATE TABLE [dbo].[SUSTES4] (
> [D1] [char] (8) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
> [D2] [int] NULL ,
> [D3] [int] NULL
> ) ON [PRIMARY]
> GO
> --Import Datat
> BULK INSERT SUSTES1 FROM 'D:\Table1.csv'
> WITH (
> FIELDTERMINATOR = ',',
> ROWTERMINATOR = '\n'
> )
> BULK INSERT SUSTES2 FROM 'D:\Table2.csv'
> WITH (
> FIELDTERMINATOR = ',',
> ROWTERMINATOR = '\n'
> )
>
> --Create INDEX
> CREATE INDEX PK_B2
> ON SUSTES2(B2)
> GO
>
> --Main Process
> DECLARE tb1_cursor CURSOR
> FOR
> SELECT A5,A11,A28,A30 FROM SUSTES1
> OPEN tb1_cursor
> DECLARE @.V5 CHAR(13),@.V11 CHAR(8),@.V28 INT,@.V30 INT
> FETCH NEXT FROM tb1_cursor INTO @.V5,@.V11,@.V28,@.V30
> WHILE (@.@.FETCH_STATUS <> -1)
> BEGIN
> IF @.V11 IN (SELECT B2 FROM SUSTES2)
> BEGIN
> INSERT INTO SUSTES3 VALUES (@.V5,'11110000',@.V30-@.V28,'D')
> INSERT INTO SUSTES3 VALUES (@.V5,'22220000',@.V30-@.V28,'C')
> END
> ELSE
> BEGIN
> INSERT INTO SUSTES3 VALUES (@.V5,'11120000',@.V30-@.V28,'D')
> INSERT INTO SUSTES3 VALUES (@.V5,'22230000',@.V30-@.V28,'C')
> END
> UPDATE SUSTES1 SET A39='Y' WHERE CURRENT OF tb1_cursor
> FETCH NEXT FROM tb1_cursor INTO @.V5,@.V11,@.V28,@.V30
> END
> CLOSE tb1_cursor
> deallocate tb1_cursor
> --Update Table4
> INSERT INTO SUSTES4
> SELECT C2,SUM(C3),COUNT(*)
> FROM SUSTES3
> GROUP BY C2
>
> Ellen
> "David Portas" wrote:
>

Is there any option to create a formula object by copy paste

Friends,
crystal report 8.0
I really fed with creating formulas in crystal report. there have hundreds of formula. and almost same formula is there but I couldn't copy this to new.
please help me. thanks in advance.Can you give more information on where you are having problem?

is there any open source db tools for MSSQL ?

hi, good day, is there any good open source database tools available for mssql ? thank youThere is a good designer tool called DBDesigner4,you can check this out link (http://fabforce.net/downloads.php)

Is there any one use PowerBulder?

I'm a Chinese programer,and I want to find some English PowerBuilder forums,but I find nothing!
Who can help me?No body?

Is there any new feature on SQL Server 2005 for paging result?

We are web developer. The paging features of SQL Server providing will
great to improve the development time. Is there any new features on SQL
Server 2005 for paging?Yes, ROW_NUMBER() should help.
http://www.windowsitpro.com/SQLServ...3922/43922.html
You should compare these methods to those described in
http://www.aspfaq.com/2120 ... I still have some testing to do for an
article re-write, but my initial testing in an isolated environment showed
ROWCOUNT was still the winner...
A
"ABC" <abc.abc.com> wrote in message
news:OLAmPTd2FHA.3876@.TK2MSFTNGP09.phx.gbl...
> We are web developer. The paging features of SQL Server providing will
> great to improve the development time. Is there any new features on SQL
> Server 2005 for paging?
>

Is there any method to back up the database to place outside of the local server system?

Hi, all experts here,

Thank you very much for your kind attention.

I am wondering if we could back up the databases to any place outside of the local server system? As I found, we can only back up the database to the local server system, so we have needs to share databases on network places. Is there any method to back up the database on network place rather than first of all I have to back up the database on a local server system, then copy it to the network place, that just sounds really inconvenient.

Thanks a lot in advance for your help and I am looking forward to hearing from you shortly.

With best regards,

Yours sincerely,

Yes, you can use script

Code Snippet

BACKUP DATABASE [DB_NAME]
TO DISK = '\\FileServer\Backups\file_name.bak'
WITH INIT -- overwrite anything

I think in SQL2005 SP2, you can specify Network Path for backup as well

Otherwise, try Maintainence Plan/Wizard as well (that's what we use)

SSMS -> Management -> create a plan -> Add "Back Up Database Task", and put the network path manually under "Folder" , under "Create a backup file for every database"

|||You'll need to make sure the service that runs mssql has access to the network drive where you are going to be placing the backup files.|||

Hi, thank you all very much for your very kind advices and help. It's been very helpful.

With best regards,

Yours sincerely,

|||

No problem at all, we are all here to help

don't forget to mark the answer to your question, so the thread is considered "resolved"

Is there any max concurrent connections for SQL 2005?

If I pick up server+CAL mode, then for each user/device which has license, is
there any max concurrent connections?
jinshuang,
A client who has a valid CAL may connect as many times as needed to the
server or servers he can access.
RLF
"jinshuang" <jinshuang@.discussions.microsoft.com> wrote in message
news:110BB0C8-9121-472F-A303-283FA5733B4F@.microsoft.com...
> If I pick up server+CAL mode, then for each user/device which has license,
> is
> there any max concurrent connections?

Is there any max concurrent connections for SQL 2005?

If I pick up server+CAL mode, then for each user/device which has license, is
there any max concurrent connections?jinshuang,
A client who has a valid CAL may connect as many times as needed to the
server or servers he can access.
RLF
"jinshuang" <jinshuang@.discussions.microsoft.com> wrote in message
news:110BB0C8-9121-472F-A303-283FA5733B4F@.microsoft.com...
> If I pick up server+CAL mode, then for each user/device which has license,
> is
> there any max concurrent connections?

Is there any limit to Left Join ?

I am going to create a query with more than 1 LEFT JOIN.
I would like to know is there any limit (Like only 1 can
be used) in SQL Server 2000 ?
ThanksThis is a multi-part message in MIME format.
--050208090800080108030302
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
Nah - no practical limit (the maximum tables per SELECT statement in SQL
Server 8.0 is 256). If you look at the grammar for the FROM clause in
BOL you'll see it's a recursive grammar.
If you were to join a couple hundred tables in your FROM clause you'd
probably hit some limits when you started running out of memory in the
procedure cache for your query plan and the CPU ground to a halt trying
to process the mother of all queries but if you stay under 200 joins in
a single query you should be OK. ;-)
Happy joining!
--
*mike hodgson* |/ database administrator/ | mallesons stephen jaques
*T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907
*E* mailto:mike.hodgson@.mallesons.nospam.com |* W* http://www.mallesons.com
Peter wrote:
>I am going to create a query with more than 1 LEFT JOIN.
>I would like to know is there any limit (Like only 1 can
>be used) in SQL Server 2000 ?
>Thanks
>
--050208090800080108030302
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
<tt>Nah - no practical limit (the maximum tables per SELECT statement
in SQL Server 8.0 is 256). If you look at the grammar for the FROM
clause in BOL you'll see it's a recursive grammar. 
 
If you were to join a couple hundred tables in your FROM clause you'd
probably hit some limits when you started running out of memory in the
procedure cache for your query plan and the CPU ground to a halt trying
to process the mother of all queries but if you stay under 200 joins in
a single query you should be OK. 
;-) 
 
Happy joining!</tt> 
<div class="moz-signature">
<title></title>
<meta http-equiv="Content-Type" content="text/html; ">
-- 
 mike
hodgson | database administrator
| mallesons stephen jaques 
T +61 (2) 9296 3668 | F +61
(2) 9296 3885 | M +61
(408) 675 907 
E
<a href="http://links.10026.com/?link=mailto:mike.hodgson@.mallesons.nospam.com">
mailto:mike.hodgson@.mallesons.nospam.com</a> | W <a href="http://links.10026.com/?link=/">http://www.mallesons.com">
http://www.mallesons.com</a> 
</div>
 
 
Peter wrote:
<blockquote cite="mid0e3f01c51894$b60fc380$a401280a@.phx.gbl" type="cite">
<pre wrap="">I am going to create a query with more than 1 LEFT JOIN.
I would like to know is there any limit (Like only 1 can
be used) in SQL Server 2000 ?
Thanks
</pre>
</blockquote>
</body>
</html>
--050208090800080108030302--|||"Mike Hodgson" <mike.hodgson@.mallesons.nospam.com> wrote in message
news:OlTsSgJGFHA.3908@.TK2MSFTNGP12.phx.gbl...
> but if you stay under 200 joins in a single query you should be OK. ;-)
LOL! I love a well-designed database... :-)

Is there any limit to Left Join ?

I am going to create a query with more than 1 LEFT JOIN.
I would like to know is there any limit (Like only 1 can
be used) in SQL Server 2000 ?
Thanks
Nah - no practical limit (the maximum tables per SELECT statement in SQL
Server 8.0 is 256). If you look at the grammar for the FROM clause in
BOL you'll see it's a recursive grammar.
If you were to join a couple hundred tables in your FROM clause you'd
probably hit some limits when you started running out of memory in the
procedure cache for your query plan and the CPU ground to a halt trying
to process the mother of all queries but if you stay under 200 joins in
a single query you should be OK. ;-)
Happy joining!
*mike hodgson* |/ database administrator/ | mallesons stephen jaques
*T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907
*E* mailto:mike.hodgson@.mallesons.nospam.com |* W* http://www.mallesons.com
Peter wrote:

>I am going to create a query with more than 1 LEFT JOIN.
>I would like to know is there any limit (Like only 1 can
>be used) in SQL Server 2000 ?
>Thanks
>
|||"Mike Hodgson" <mike.hodgson@.mallesons.nospam.com> wrote in message
news:OlTsSgJGFHA.3908@.TK2MSFTNGP12.phx.gbl...

> but if you stay under 200 joins in a single query you should be OK. ;-)
LOL! I love a well-designed database... :-)

Is there any limit to Left Join ?

I am going to create a query with more than 1 LEFT JOIN.
I would like to know is there any limit (Like only 1 can
be used) in SQL Server 2000 ?
ThanksNah - no practical limit (the maximum tables per SELECT statement in SQL
Server 8.0 is 256). If you look at the grammar for the FROM clause in
BOL you'll see it's a recursive grammar.
If you were to join a couple hundred tables in your FROM clause you'd
probably hit some limits when you started running out of memory in the
procedure cache for your query plan and the CPU ground to a halt trying
to process the mother of all queries but if you stay under 200 joins in
a single query you should be OK. ;-)
Happy joining!
*mike hodgson* |/ database administrator/ | mallesons stephen jaques
*T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907
*E* mailto:mike.hodgson@.mallesons.nospam.com |* W* http://www.mallesons.com
Peter wrote:

>I am going to create a query with more than 1 LEFT JOIN.
>I would like to know is there any limit (Like only 1 can
>be used) in SQL Server 2000 ?
>Thanks
>|||"Mike Hodgson" <mike.hodgson@.mallesons.nospam.com> wrote in message
news:OlTsSgJGFHA.3908@.TK2MSFTNGP12.phx.gbl...

> but if you stay under 200 joins in a single query you should be OK. ;-)
LOL! I love a well-designed database... :-)

is there any limit to how long of a string SqlDataReader.GetString() can return?

is there any limit to how long of a string SqlDataReader.GetString() can
return?No.
And next time please don't re-post your question in every single group.
Asking in ONE group should be sufficient.
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"Daniel" <softwareengineer98037@.yahoo.com> wrote in message
news:%23fR04M2YGHA.3704@.TK2MSFTNGP03.phx.gbl...
> is there any limit to how long of a string SqlDataReader.GetString() can
> return?
>

is there any limit to how long of a string SqlDataReader.GetString() can return?

is there any limit to how long of a string SqlDataReader.GetString() can
return?no
Jack Vamvas
___________________________________
Receive free SQL tips - www.ciquery.com/sqlserver.htm
"Daniel" <softwareengineer98037@.yahoo.com> wrote in message
news:ewcJ9M2YGHA.508@.TK2MSFTNGP02.phx.gbl...
> is there any limit to how long of a string SqlDataReader.GetString() can
> return?
>

is there any limit to how long of a string SqlDataReader.GetString() can return?

is there any limit to how long of a string SqlDataReader.GetString() can
return?no
--
Jack Vamvas
___________________________________
Receive free SQL tips - www.ciquery.com/sqlserver.htm
"Daniel" <softwareengineer98037@.yahoo.com> wrote in message
news:ewcJ9M2YGHA.508@.TK2MSFTNGP02.phx.gbl...
> is there any limit to how long of a string SqlDataReader.GetString() can
> return?
>

is there any limit to how long of a string SqlDataReader.GetString() can return?

is there any limit to how long of a string SqlDataReader.GetString() can
return?Only 8000 characters. No more
Nathan H. Omukwenyi
"Daniel" <softwareengineer98037@.yahoo.com> wrote in message
news:uTdm7M2YGHA.500@.TK2MSFTNGP03.phx.gbl...
> is there any limit to how long of a string SqlDataReader.GetString() can
> return?
>|||I don't believe there is a limit other than available memory.
Hope this helps.
Dan Guzman
SQL Server MVP
"Nathan H. Omukwenyi" <nathan@.e-tools.com> wrote in message
news:eXO8V8wZGHA.428@.TK2MSFTNGP02.phx.gbl...
> Only 8000 characters. No more
> Nathan H. Omukwenyi
> "Daniel" <softwareengineer98037@.yahoo.com> wrote in message
> news:uTdm7M2YGHA.500@.TK2MSFTNGP03.phx.gbl...
>|||I believe the only limitation is the underlying data type in sql. For
example a varchar can only be max 8000 chars. but text could be up to 2
gigs.
"Daniel" <softwareengineer98037@.yahoo.com> wrote in message
news:uTdm7M2YGHA.500@.TK2MSFTNGP03.phx.gbl...
> is there any limit to how long of a string SqlDataReader.GetString() can
> return?
>|||Agreed. I had only tested with the varchar SQL data type. Testing with TEXT
shows that it can handle much more but reaching the maximum is taking too
much time and resources. So I suppose Dan could be right about available
memory.
Nathan H. Omukwenyi
"Jeremy" <nospam@.here.com> wrote in message
news:O5fxDqBaGHA.4752@.TK2MSFTNGP02.phx.gbl...
>I believe the only limitation is the underlying data type in sql. For
> example a varchar can only be max 8000 chars. but text could be up to 2
> gigs.
> "Daniel" <softwareengineer98037@.yahoo.com> wrote in message
> news:uTdm7M2YGHA.500@.TK2MSFTNGP03.phx.gbl...
>

Is there any known issue with Service Broker on Vista

When I run the ServiceBroker Hello, World sample on SQL Server Standard SP2
or SQL Server Express on Vista, I observe that no message is ever entered in
any queue. When I run the same sample on Windows Server 2008 Enterprise, the
sample behaves as it should.
So, is there any known issue with Service Broker on Vista, or does Service
Broker have some dependency on services or O/S settings that I have missed.
Answering my own question:
Vista proved not to be the significant variable in the problem. The
troubleshooting documentation led me to this query:
select * from sys.transmission_queue
... which yielded this result:
An exception occurred while enqueueing a message in the target queue. Error:
15517, State: 1. Cannot execute as the database principal because the
principal "dbo" does not exist, this type of principal cannot be
impersonated, or you do not have permission.
... which is an error fixed by executing:
EXEC sp_changedbowner 'sa'
"Craig McMurtry" wrote:

> When I run the ServiceBroker Hello, World sample on SQL Server Standard SP2
> or SQL Server Express on Vista, I observe that no message is ever entered in
> any queue. When I run the same sample on Windows Server 2008 Enterprise, the
> sample behaves as it should.
> So, is there any known issue with Service Broker on Vista, or does Service
> Broker have some dependency on services or O/S settings that I have missed.

Is there any known issue with Service Broker on Vista

When I run the ServiceBroker Hello, World sample on SQL Server Standard SP2
or SQL Server Express on Vista, I observe that no message is ever entered in
any queue. When I run the same sample on Windows Server 2008 Enterprise, the
sample behaves as it should.
So, is there any known issue with Service Broker on Vista, or does Service
Broker have some dependency on services or O/S settings that I have missed.Answering my own question:
Vista proved not to be the significant variable in the problem. The
troubleshooting documentation led me to this query:
select * from sys.transmission_queue
... which yielded this result:
An exception occurred while enqueueing a message in the target queue. Error:
15517, State: 1. Cannot execute as the database principal because the
principal "dbo" does not exist, this type of principal cannot be
impersonated, or you do not have permission.
... which is an error fixed by executing:
EXEC sp_changedbowner 'sa'
"Craig McMurtry" wrote:
> When I run the ServiceBroker Hello, World sample on SQL Server Standard SP2
> or SQL Server Express on Vista, I observe that no message is ever entered in
> any queue. When I run the same sample on Windows Server 2008 Enterprise, the
> sample behaves as it should.
> So, is there any known issue with Service Broker on Vista, or does Service
> Broker have some dependency on services or O/S settings that I have missed.

is there any IsString() function in vb.net?

i want to check that only string in textbox. the textbox shoould enterd the strings so is there IsString() function in vb.net

plz help me....

Hi,
first of all your subject does say nothing about your problem, so many people won't read it..
secondly try to use the forum search, your problem has been discusses many times..
for example:
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=432801&SiteID=1

Is there any impact on server of increasing LOG File size

Thanks to all participants.

I am using SQL Server 2000 with replication object for two location. Log size on publisher go upto 25 times of data file size, I mean 80 MB Data files has maintains 2 GB log file and it is same for all five co's working on same windows 2000 advanced server board.

Since last week server randamly get disconnected from user applications and at that time few tables are not openable at server.

Can any one give a reason ? Why this type misbehaviou done by SQL Server 2000?

Thanks.If you aren't using the log file for recovery purposes, I'd suggest you set the database recovery mode to simple. That will allow SQL Server to jettision the log data once it has been replicated.

SQL Server has a lot of tunable options. It guesses correctly on the appropriate settings for them nearly all of the time (probably more than 99.95% of the time in my experience), but occaisionally it gets something mis-adjusted.

This kind of problem is probably unavoidable (although it can still be reduced), and in those few instances you need a bit of expert help to figure out what is wrong and how to fix it.

-PatP|||Thanks for your suggession. I shrink log file upt to 100 MB and set the database in simple recovery mode by 02-01-2004. After that for last three days nothing happen with user application. Is there any co-relation between problem and log size or any thing more will be findout anywhere else.

Where I can find the reson for orginal disconnection problem, which one not occured for last three working day.

Thanks

R.Mall|||PatP Sir,

I am waiting for your reply regarding following.

Thanks for your suggession. I shrink log file upt to 100 MB and set the database in simple recovery mode by 02-01-2004. After that for last three days nothing happen with user application. Is there any co-relation between problem and log size or any thing more will be findout anywhere else.

Where I can find the reson for orginal disconnection problem, which one not occured for last three working day.

Thanks

R.Mall|||There may be some connection, but I don't know of any easy way to determine if there is a connection or not. Without a lot more information about your configuration (NT, SQL, WAN network, client machine configurations, etc), I'm at a loss to help you more.

The problem lies in the apparent complexity of your configuration. If you have multiple SQL Server databases/instances, many clients, different application loads, etc... The number of items that could interact rapidly gets beyond what I can diagnose remotely.

-PatP|||PatP Sir,

Please look over my setup-

Hardware , OS and Application
Servers-Two
Intel Original server board - 7210TP
CPU HT 2.8GHz, P-IV
HDD - 80GB SATA
1GB Ram - DDR
ATX Cabinet
OS-Windows 2000 Advanced server with service pack IV.
Database - MSSQL Server 2000 with SP3.
Replication - Merge
No.of Database- 6th (1 Master and 5 cos)
Replicated no.of tables ( 42*5 ) + 12 = 222
Publisher - One
Subscriber - One (Pull Subscription)
Transaction - Medium.

Clients-45+ (10 at subscriber site and 35+ at publisher site)
Intel Alex ATX motherboard
CPU Celeron 400
LAN Card PCI 32Bit
128MB Ram
40GB HDD
OS-Windows-98
Application form - Exe and DLL (One Exe + 70+ DLL)
No. of Application - One.
No.Of modules - 11
Application Base Power Builder 7.0

ROUTER
ZYXEL-ISDN Dialup
100IH
Connectivity ISDN Dialup
If you need any more information, so let me know, I will send you immediately.

Thanks

R.Mall|||Dear PatP Sir,

Please see the details.|||All I can do is offer guesses, based on the information that you've provided. To really do a good job, it would take something on the order of 500 pages of information, and probably 40 hours of effort on my part. That isn't something I'm willing to do at this point in time.

A single ISDN line can't always support ten users plus replication on that scale. While it can sometimes do the job, there are othertimes that it will definitely be a bottleneck for performance. This can be mitigated by how you configure the servers to use the connection, but it is still potentially the largest potential problem that I see.

A busy SQL Server would consider 1 Gb of RAM as an hors d'oeurve. I routinely configure busy servers with 8 Gb, then let someone try to talk me down. For 45 users, I might drop as low as 2 Gb, but not without considerable discussion first. I really don't think you have enough RAM in the servers.

Powerbuilder's Datawindows are very easy for developers to use, but they can be "noisy" from a database traffic perspective. As one example, if a datawindow has many rows and the default update method is used after a single row is changed, the entire contents of the database can be written back to the server. Depending on how frequently you've got your subscriber set to "pull" a merge, there can be a lot of data queued up (even with SIMPLE recovery)!

Your problem is probably complex. I don't have the time or resources to do a good job helping you fix it remotely. You really need to get either a dedicated team to help you solve this remotely, or someone to come on site (which would be much better in my opinion). I don't think that casual help via a forum is going to be enough to help you solve this problem.

-PatP|||Dear PatP Sir,

Thanks for your valuable commend on my issue, I will see as per your guidelines to that issue.

I Think you have analyze methods for analyze the problem but due to lack of time and information you can't to it.

Can you help me in another way? Just tell me analyzer methods and help material site or hard copy book, so I will do it myself.

Thanks

R.Mall|||First order of business, get VERY familiar with the NT Performance Analyzer. For this kind of problem, it can provide at least 90% of the information you need to diagnose the problem.

Just as an observation, I wouldn't recommend using a machine to monitor its own performance if I could use a second machine to do it. Running the Performance Monitor can be rather demanding, especially if you are monitoring many counters at once.

Use Performance Monitor to monitor the server at a time when performance is acceptable, and at times when the performance is not acceptable. At first, you'll want to record all of the Performance counters that seem vaguely relevant.

The whole point of this process is to determine where the "weak link" is in your system. It shouldn't take a lot of measurements to trace the problem down to just a few counters (certainly less than ten, probably only three). At first you'll need to track all of the memory, disk, cpu, and network counters... Very shortly you should be able to identify which counters change drastically as performance degrades. Those are the counters that you need to watch carefully going forward.

In the begining, what I'd recommend is to save the performance counters into a file. Run a few (three to five) basline monitors at different times of the day, including at least one with no users online at all. When your server performance starts to degrade, run another stored trace. The worse the performance gets, the more important getting these traces will be.

When you have several traces stored, you'll need to start comparing them. Look for common trace information that is very similar when performance is good as they are when performance is bad. These are "background noise" that you can safely filter out.

As you begin to see which traces change drastically when performance changes, you'll start to understand where the bottleneck is in your system. This is the area where you need to focus your attention, but be forewarned that there can be more than one bottleneck, and sometimes fixing one exposes a completely different one.

As an example of this, I think that your servers are severly RAM starved. I think that they need at least twice as much memory, and quite possibly more than that. However, when you fix that problem, you might well find that something else is also a bottleneck, but due to the RAM problems it was never exposed.

To put this in a "real world" context, if you have an truck that's in bad shape and you need to move something very heavy, you might decide to work on the truck first because you know it can't do the job in its present condition. You tune up the engine (new spark plugs and rotar), only to discover that you need a carburator overhaul too that was masked by the need for the tune up. Once you get the engine running well, you discover there isn't enough transmission fluid. As you fix one problem, it exposes another problem that has been there all along, but that wasn't serious enough to be an issue.

Don't let my comments make you depressed. Your problem is definitely something that can be fixed. I just don't want you to think you'll go in with a "magic bullet", fire one shot and the world will be lovely again. This is a problem that probably took a while to become an issue, and it will take some time and effort to fix. Actually, in my mind performance monitoring and management are part of the ongoing job... The issues are always there, they just don't always get the attention that they should!

-PatP|||PatP sir,

Lot of thanks to you for your valuable suggession, I will do it accordingly and hope I will resolve it with the help of you.

I would like to thanks more to you for past time advice also , when I was in loop of Replication 4 month ago.

Thanks

R.Mall|||Amount of activity that can be generated by the number of users you indicated can not possibly be a source of concern regarding you hardware configuration. If you're experiancing "disconnects" it's probably due to clien timeouts which are a direct result of host CPU being too busy to process any consequent requests. This is a direct affect of under-utilization of indexing capabilities (this statement is dictated purely by the amount of info you provided, and may be easily refuted if the presence of indexes is indicated). Merge replication does not pose this kind of behavior with 45+ concurrent connections. In fact it doesn't manifest itself like this even with 450+ concurrent connections with 20+ runnable processes (I bet you have less than that).

I think you need to analyze execution plans for most frequently invoked statements and concentrate your attention on optimizing them before you burry yourself in perfmon counters.

Friday, February 24, 2012

Blog Archive

About Me