SQL Adding a User account: dear

Showing posts with label dear. Show all posts

Monday, March 19, 2012

advanced SQL tutorials

Dear All,
Can you suggest a good book or set of websites which have advanced SQL tutorials for SQL server? Got the basics but looking to build on my knowledge.
...cause knowledge is power...Here are some books

http://www.sqlteam.com/store.asp

And read every post here, and over at sql team.

Use Books online to try and figure out a posters question in Query Analyzer. Most often than not you will see someone answer the question.

Good Luck|||You might want to be a bit more specific. SQL Server has components that cover a broad range of topics. Are you a developer seeking to increase your knowledge of T-SQL and ADO? Are you a DBA seeking the best practices for securing your data and performance tuning? Are you a business analyst looking to learn MDX and OLAP?

Lots of sites to browse. Google is your friend.

Regards,

hmscott|||You might want to be a bit more specific. SQL Server has components that cover a broad range of topics. Are you a developer seeking to increase your knowledge of T-SQL and ADO? Are you a DBA seeking the best practices for securing your data and performance tuning? Are you a business analyst looking to learn MDX and OLAP?

Lots of sites to browse. Google is your friend.

Regards,

hmscott

Sorry for late reply and thanks for recomendations! Yes trying to learn more about complex queries with t-sql and db design and implementation for real life apps, had DB modules at Uni but it seems like hardly anything compared to the problems I face now in my job!

Sunday, March 11, 2012

Advance SQL

Dear Expert,

How can I reuse the column, instead of select the whole things again.
Example as below :

select
column1 as A,
column2 as B,
column3 * A as C
from dummy ;

in MS SQL I have to do like this,

select
column1 as A,
column2 as B,
column3 * column1 as C
from dummy ;

Thanks

DesmondOn 1 Jun 2004 03:56:08 -0700, Desmond wrote:

>Dear Expert,
>How can I reuse the column, instead of select the whole things again.
>Example as below :
>select
> column1 as A,
> column2 as B,
> column3 * A as C
>from dummy ;
>in MS SQL I have to do like this,
>select
> column1 as A,
> column2 as B,
> column3 * column1 as C
>from dummy ;
>
>Thanks
>Desmond

Hi Desmond,

You can use a derived table:

select A, B, column3 * A as C
from (select column1 as A,
column2 as B,
column3
from dummy) AS t

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||>> How can I reuse the column, instead of selecting the whole thing
again. <<

You can put it into a VIEW or derived table:

SELECT a, b, a*column3 AS c
FROM (SELECT column1, column2, column3
FROM Dummy) AS X(a, b, column3);

But that is not your problem. The real problem is that you do not
understand how SQL -- real SQL -- works.

Here is how a SELECT works in SQL ... at least in theory. Real
products will optimize things when they can.

a) Start in the FROM clause and build a working table from all of the
joins, unions, intersections, and whatever other table constructors
are there. The table expression> AS <correlation name> option allows
you give a name to this working table which you then have to use for
the rest of the containing query.

b) Go to the WHERE clause and remove rows that do not pass criteria;
that is, that do not test to TRUE (reject UNKNOWN and FALSE). The
WHERE clause is applied to the working set in the FROM clause.

c) Go to the optional GROUP BY clause, make groups and reduce each
group to a single row, replacing the original working table with the
new grouped table. The rows of a grouped table must be group
characteristics: (1) a grouping column (2) a statistic about the group
(i.e. aggregate functions) (3) a function or (4) an expression made up
those three items.

d) Go to the optional HAVING clause and apply it against the grouped
working table; if there was no GROUP BY clause, treat the entire table
as one group.

e) Go to the SELECT clause and construct the expressions in the list.
This means that the scalar subqueries, function calls and expressions
in the SELECT are done after all the other clauses are done. The "AS"
operator can also give names to expressions in the SELECT list. These
new names come into existence all at once, but after the WHERE clause,
GROUP BY clause and HAVING clause has been executed; you cannot use
them in the SELECT list or the WHERE clause for that reason.

If there is a SELECT DISTINCT, then redundant duplicate rows are
removed. For purposes of defining a duplicate row, NULLs are treated
as matching (just like in the GROUP BY).

f) Nested query expressions follow the usual scoping rules you would
expect from a block structured language like C, Pascal, Algol, etc.
Namely, the innermost queries can reference columns and tables in the
queries in which they are contained.

g) The ORDER BY clause is part of a cursor, not a query. The result
set is passed to the cursor, which can only see the names in the
SELECT clause list, and the sorting is done there. The ORDER BY
clause cannot have expression in it, or references to other columns
because the result set has been converted into a sequential file
structure and that is what is being sorted.

As you can see, things happen "all at once" in SQL, not from left to
right as they would in a sequential file/proceudral language model. In
those languages, these two statements produce different results:
**READ (a, b, c) FROM File_X;
**READ (c, a, b) FROM File_X;

while these two statements return the same data:

SELECT a, b, c FROM Table_X;
SELECT c, a, b FROM Table_X;

Think about what a confused mess this statement is in the SQL model.

SELECT f(c2) AS c1, f(c1) AS c2 FROM Foobar;

That is why such nonsense is illegal syntax.

Sunday, February 19, 2012

ADO Recordset from SQL Task issue

Dear Folks,
I have a Foreach Loop that enumerates a set of files from an ADO recordset variable which is populated by a preceding SQL task. The query from the task that populates the recordset returns about 200 rows with one varchar field(a file path). The loop is long running, and so far it errors on the connection string populated by the enumeration variable after about an hour. The timeout for the SQL task is set to zero. Could it be the source recordset variable timing out, or could it be that the recordset is too large?
Thanks,
Chris

If it is related to the number of records you could start by limiting the result to 10 records, and add 10 records until you see the problem again.

Also, do you have the latest service pack? Here's a post concerning an issue prior to the service pack.

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

|||

200 rows is not too large for a recordset.

I am confused. the sql task populates the recordset and the for each loop enumerates the results. I assume that the sql task is outside (before) the loop. so, which is taking a long time? executing the sql command or running over the recordset in the loop?

|||Thanks

for the replies, and yes, the SQL Task populates the ADO recordset

variable before the loop. The loop enumerates the results for about an

hour before it errors out. The error is the same every time, reporting

that the connection string has failed to have been acquired. The

variable for the connection string is populated by enumerating the

recordset. If i change the loop's collection enumerator to a directory,

the loop runs fine.
Thanks for your help,
Chris
|||I'm on a SQL Server 2k5 x86 ,SP1, windows 2000 platform if that helps.
Thanks,
Chris
|||Also, It's definitely not the recordset itself, as I've been able to use the same ADO variable result set to enumerate loops with fewer tasks\are shorter running. Any chance this is a memory leak?
|||

Well, what are you doing with the rows from the recordset? I think the enumerator gives you a fields collection.

in other words, what is in the loop

|||The (one field per) rows from the recordset enumerate the collection, a directory of Access databases. Inside the Foreach loop are several data flow tasks. A variable is mapped from the returned row and helps populate a connection string expression for an Access connection manager. The source connection for each data flow task uses the connection manager. It works great for about an hour when i use the recordset as a collection enumerator, and the loop does not error out if I use the directory as a collection enumerator.
Thanks,
Chris
|||

This is odd. there certainly could be a bug somewhere in the recordset object itself (in the interest of full disclosure, I wrote the code for the ADO Recordset object back in '96 :) )

If you don't mind doing some experiments for me, I would ask you to try to separate the recordset usage from the pipeline usage as a way to try to narrow down the problem. it sounds like you have done this a bit.

instead of a dataflow task, put a script task in the loop. extract the value of the connection string and write it out somewhere so that you can see the values and make sure they all look rational. If this loop completes without error, then add some long delay (like a 5 minute wait) into the script task. this will tell us if their is some purely time related issue with the recordset (doubtful, but who knows could be a garbage collector kicking in and dropping references to the com objects of the recordset? crazy i know).

You say you can use a non-recordset list of the same exact connections and get this to succeed? that makes me think that this is not an issue with the access driver, but who knows. Exactly what error are you getting when it fails?

These two things just don't seem related.

Monday, February 13, 2012

ADO Can, EM and QA cannot

Dear all,
I'm using SQl Server 2K on a webserver and the sa password is "abcde". Using
this user name and password, and with ADO, I established our website.
Now the website is still working well,can input data,show data!!
But I cannot login to this SQL Server with "Enterprise Manager" or "Query
Analyzer" on the server. Either with sa or Windows-Authenticate. (I user the
administrator to login to the server).
I'm running EM and QA on the webserver(With administrator account), not on
the client PC.
The EM and QA can work well before.
One thing is, the webserver may be infected by virus of Spida Worm.
Currently, when I try to run EM or QA, then a long time waiting. Then told
me: connection failed,...
But the website system is still working well.
Please tell me how to run up the Enterprise Manager? Thanks a lot.Hi
It is not a good idea to have SQL Server on an externally exposed server,
and it is always better to dedicate the server for a single use.
If you are connecting using ADO then you should be able to use the client
tools using the same login, check that you are using the same protocols.
John
"goldenshine" wrote:
> Dear all,
> I'm using SQl Server 2K on a webserver and the sa password is "abcde". Using
> this user name and password, and with ADO, I established our website.
> Now the website is still working well,can input data,show data!!
> But I cannot login to this SQL Server with "Enterprise Manager" or "Query
> Analyzer" on the server. Either with sa or Windows-Authenticate. (I user the
> administrator to login to the server).
> I'm running EM and QA on the webserver(With administrator account), not on
> the client PC.
> The EM and QA can work well before.
> One thing is, the webserver may be infected by virus of Spida Worm.
> Currently, when I try to run EM or QA, then a long time waiting. Then told
> me: connection failed,...
> But the website system is still working well.
> Please tell me how to run up the Enterprise Manager? Thanks a lot.|||Thanks,John,
Can you please tell me how to check the protocols? Since the website(
Asp.net) can use ADO to connect SQL Server and works well, why
EnterpriseManager cannot?
Thanks
"John Bell" wrote:
> Hi
> It is not a good idea to have SQL Server on an externally exposed server,
> and it is always better to dedicate the server for a single use.
> If you are connecting using ADO then you should be able to use the client
> tools using the same login, check that you are using the same protocols.
> John
>
> "goldenshine" wrote:
> > Dear all,
> > I'm using SQl Server 2K on a webserver and the sa password is "abcde". Using
> > this user name and password, and with ADO, I established our website.
> > Now the website is still working well,can input data,show data!!
> >
> > But I cannot login to this SQL Server with "Enterprise Manager" or "Query
> > Analyzer" on the server. Either with sa or Windows-Authenticate. (I user the
> > administrator to login to the server).
> >
> > I'm running EM and QA on the webserver(With administrator account), not on
> > the client PC.
> > The EM and QA can work well before.
> > One thing is, the webserver may be infected by virus of Spida Worm.
> > Currently, when I try to run EM or QA, then a long time waiting. Then told
> > me: connection failed,...
> > But the website system is still working well.
> >
> > Please tell me how to run up the Enterprise Manager? Thanks a lot.|||Hi
Look in the client network configuration utility on the client and the
server network configuration utitlity on the server.
John
"goldenshine" <goldenshine@.discussions.microsoft.com> wrote in message
news:FA91D6BA-7F42-4BAA-A01C-969E63685059@.microsoft.com...
> Thanks,John,
> Can you please tell me how to check the protocols? Since the website(
> Asp.net) can use ADO to connect SQL Server and works well, why
> EnterpriseManager cannot?
> Thanks
>
> "John Bell" wrote:
>> Hi
>> It is not a good idea to have SQL Server on an externally exposed server,
>> and it is always better to dedicate the server for a single use.
>> If you are connecting using ADO then you should be able to use the client
>> tools using the same login, check that you are using the same protocols.
>> John
>>
>> "goldenshine" wrote:
>> > Dear all,
>> > I'm using SQl Server 2K on a webserver and the sa password is "abcde".
>> > Using
>> > this user name and password, and with ADO, I established our website.
>> > Now the website is still working well,can input data,show data!!
>> >
>> > But I cannot login to this SQL Server with "Enterprise Manager" or
>> > "Query
>> > Analyzer" on the server. Either with sa or Windows-Authenticate. (I
>> > user the
>> > administrator to login to the server).
>> >
>> > I'm running EM and QA on the webserver(With administrator account), not
>> > on
>> > the client PC.
>> > The EM and QA can work well before.
>> > One thing is, the webserver may be infected by virus of Spida Worm.
>> > Currently, when I try to run EM or QA, then a long time waiting. Then
>> > told
>> > me: connection failed,...
>> > But the website system is still working well.
>> >
>> > Please tell me how to run up the Enterprise Manager? Thanks a lot.

ADO can run, EnterpriseManager cannot ?

Sunday, February 12, 2012

Administrative tasks

Dear Friends
I want to do the following for my sql server kindly
suggest how i can do the same.
- Check the SQL cluster set-up and suggest
adpatations/improvements or additionnal hardware
requirements
- Check RAM caching
- Analyse partitionng options and make suggestion
- Identify performance issues and suggest
adapatations/improvements indexes, index views
- Create an adequate maintenance plan.
Thanks and best regards
Sharad1) Hire a qualified DBA
OR
2) Become a qualified DBA
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Sharad" <niitmalad@.yahoo.co.in> wrote in message
news:10df01c4cbd2$f0ce9e60$a501280a@.phx.gbl...
> Dear Friends
> I want to do the following for my sql server kindly
> suggest how i can do the same.
> - Check the SQL cluster set-up and suggest
> adpatations/improvements or additionnal hardware
> requirements
> - Check RAM caching
> - Analyse partitionng options and make suggestion
> - Identify performance issues and suggest
> adapatations/improvements indexes, index views
> - Create an adequate maintenance plan.
> Thanks and best regards
> Sharad|||Sharad,
That's not likely a list of tasks that someone can answer in
a newsgroup posting. You can find some maintenance and
monitoring guidelines in the Operations Guide at:
http://www.microsoft.com/TechNet/pr...in/sqlops0.mspx
You can find some performance monitoring and tuning articles
at:
http://www.sql-server-performance.com/
-Sue
On Tue, 16 Nov 2004 03:53:51 -0800, "Sharad"
<niitmalad@.yahoo.co.in> wrote:

>Dear Friends
>I want to do the following for my sql server kindly
>suggest how i can do the same.
>- Check the SQL cluster set-up and suggest
>adpatations/improvements or additionnal hardware
>requirements
>- Check RAM caching
>- Analyse partitionng options and make suggestion
>- Identify performance issues and suggest
>adapatations/improvements indexes, index views
>- Create an adequate maintenance plan.
>Thanks and best regards
>Sharad

Monday, March 19, 2012

advanced SQL tutorials

Sunday, March 11, 2012

Advance SQL

Sunday, February 19, 2012

ADO Recordset from SQL Task issue

Monday, February 13, 2012

ADO Can, EM and QA cannot

ADO Can, EM and QA cannot

ADO Can, EM and QA cannot

ADO can run, EnterpriseManager cannot ?

ADO can run, EnterpriseManager cannot ?

ADO can run, EnterpriseManager cannot ?

Sunday, February 12, 2012

Administrative tasks

SQL Adding a User account

Blog Archive

About Me