Hi guys, how to monitor and purge log files/transaction files? Tha

Hi guys, how to monitor and purge log files/transaction files in SQL Server?
ThanksBACKUP LOG file and then if you want to reduce physical size of the file run
DBCC SHRINKFILE command
"Iter" <Iter@.discussions.microsoft.com> wrote in message
news:8C79F464-72E9-4138-BF51-0824857DEEA4@.microsoft.com...
> Hi guys, how to monitor and purge log files/transaction files in SQL
> Server?
> Thanks
>

Hi guys, how to monitor and purge log files/transaction files? Tha

Hi guys, how to monitor and purge log files/transaction files in SQL Server?
ThanksBACKUP LOG file and then if you want to reduce physical size of the file run
DBCC SHRINKFILE command
"Iter" <Iter@.discussions.microsoft.com> wrote in message
news:8C79F464-72E9-4138-BF51-0824857DEEA4@.microsoft.com...
> Hi guys, how to monitor and purge log files/transaction files in SQL
> Server?
> Thanks
>

Hi guys, how to monitor and purge log files/transaction files? Tha

Hi guys, how to monitor and purge log files/transaction files in SQL Server?
Thanks
BACKUP LOG file and then if you want to reduce physical size of the file run
DBCC SHRINKFILE command
"Iter" <Iter@.discussions.microsoft.com> wrote in message
news:8C79F464-72E9-4138-BF51-0824857DEEA4@.microsoft.com...
> Hi guys, how to monitor and purge log files/transaction files in SQL
> Server?
> Thanks
>

Hi Guys,

I'm nithya. Can we have nested cursors in SQL2000?

Quote:

Originally Posted by nithyapriya

I'm nithya. Can we have nested cursors in SQL2000?

You can, but if you want your server code to be efficient - try not to use triggers at all. There is a very small set of tasks on SQL Server that cannot be accomplished with standard set-based operations requiring cursors to be used.|||

Quote:

Originally Posted by almaz

You can, but if you want your server code to be efficient - try not to use triggers at all. There is a very small set of tasks on SQL Server that cannot be accomplished with standard set-based operations requiring cursors to be used.

Thank you for your reply. Nithya.

hi experts, can anyone have a look my simple questions, thxxx a lot!

Hi guys,

got another simple question (I am very new to sqlserver...), I have a c# desktop program, it connected to a sqlexpress 2005 server, and I need my program to connect to sql server reomotely , so I turn on TCP/IP protocol, my details are in below:

inside the server's tcp/ip on the ip tab, IP1 is set to active, enabled, the ip address is 192.168.0.3 , there is no port number for IP1, should there be a port number? dynamic port is 0

IP2 is set to active, enable with address 127.0.0.1 and also does not have a port number, dynamic port is 0

IPAll is using dynamic port 1921 but also has no port number.

for the client TCP/IP setting the default port is 1433 and it is also enabled.

I then set up a port forwarding service in my router with port (1921, I think that's the port number ?)when I run my application and sqlexpress server on the same machine, both of the following connectionstrings are working well for the program:

server= 62.31.81.210.\SQLEXPRESS,1921; user id='sa'; password='mypassword'; Database='EvoHealthSQLex'; Integrated Security=True

server= 192.168.0.3.\SQLEXPRESS,1921; user id='sa'; password='mypassword'; Database='EvoHealthSQLex'; Integrated Security=True

(I use dynamic port 1921 as the port number, don't know if it's right, but it works, but what do we need for TCP/IP setting the default port 1433? )

62.31.81.210 is the router's IP address, I attach sqlexpress's machine's(local ip address 192.168.0.3) on port forwarding service with port 1921, so above connection string is working right as I want to test wheter I can connect to sqlexpress server via real Ip address not just local Ip address.

then there is the problem: I try to run the same program on the other computer, but within the same local network (both sqlexpress server machine and application machine are connected to the same router ), I got a error message:

Login failed for user 'BRISTOL-1\Guest'

after some researching, I did the following:

I open the sql server management studio express(also free), go to 'Database' ->'EvoHealthSQLex'-> 'properties' ->'permissions'

I add a user called 'BRISTOL-1\Guest' and granted it with 'connect' and 'control' permissions, however, I still got the same error.

In 'Database' ->'EvoHealthSQLex'-> 'properties' ->'general'

I saw the owner part said 'BRISTOL-1\Ray'

the full computer name of machine running sqlexpress is 'bristol-1', and I login as user 'ray', I guess that's why the database owner is 'BRISTOL-1\Ray', on the other computer (which run the application), the full computer name is 'ray' and I login as 'rui', I don't know whether those info is useful.

Questions:

I wonder why the same error occured after I created the user 'BRISTOL-1\Guest', and I don't know why I am recoginzed as 'BRISTOL-1\Guest' on the other machine?

I wonder if I created a user, for example,say 'BRISTOL-1\ross' (does it have to use the same prefix BRISTOL-1?), when I use the program in other computer how do I specified myself as user 'BRISTOL-1\ross' or whatever ? does it have anything to do with connectionstring? as in the connection string, user id is 'sa', i don't think I should change the connectionstring to:

server= 192.168.0.3.\SQLEXPRESS,1921; user id='BRISTOL-1\ross' ; password='mypassword'; Database='EvoHealthSQLex'; Integrated Security=True - if this case is right, what the pw suppose to be?

I know those suppose to be very simple for you experts, I am just very new to sqlserver, and I can't find any related articles online...(is it because the solution is too obvious?)

I don't have anyone around me can help me so I post it here, any help will be very appreciate!!!

Ps: I just turn on the 'Guest' account in 'User Account' of 'Control Panel', but same error.

"user id" and "password" is used for SQL Authentication, while Integrated Security" is used for windows authentication. You needs to only use one of them. If both is specified, the result is not determinstics. I know some driver ignores SQL authentication in this case.

How to use configure these two authentication is a long story, I am sure you can find plenty of articles on the web or use bookonline.

In you case, if you use sa password, make sure you don't specify Integrated Security. If you want to use windows authentication, make sure that you client account has access privilieges to the SQL Server account.

|||

Hi, thanks so much for your rapid reply, I realized that I asked many stupid questions...

I just make a small progress now, after I downloaded BOL (just now), I fint out I need to greate a login rather than just create a user! :

the whole command:

CREATE LOGIN login_name { WITH <option_list1> | FROM <sources> } <sources> ::= WINDOWS [ WITH <windows_options> [ ,... ] ] | CERTIFICATE certname | ASYMMETRIC KEY asym_key_name <option_list1> ::= PASSWORD = 'password' [ HASHED ] [ MUST_CHANGE ] [ , <option_list2> [ ,... ] ] <option_list2> ::= SID = sid | DEFAULT_DATABASE = database | DEFAULT_LANGUAGE = language | CHECK_EXPIRATION = { ON | OFF} | CHECK_POLICY = { ON | OFF} | CREDENTIAL = credential_name <windows_options> ::= DEFAULT_DATABASE = database | DEFAULT_LANGUAGE = languageIn my case I did:CREATE LOGIN [BRISTOL-1\Guest] FROM WINDOWS; GOthen it works, I wonder is it anyother computer try to connect with the same sqlexpress server will be BRISTOL-1\Guest ?now I need to figure out how to connect from outside rather than local network, any suggestions :)thanks a lot Nan Tu!

Heterogeneous queries

Hi Guys,

I have one stored procedure on SQL 6.5 and retrieving data from SQL
2000. I have defined a linked server on SQL 6.5. But
I am getting following error:-

"Heterogeneous queries require the ANSI_NULLS and ANSI_WARNINGS options
to be set for the connection. This ensures consistent query semantics.
Enable these options and then reissue your query."

I dropped and re-create the stored procedure after adding the SET
ANSI_NULLS & SET ANSI_WARNINGS ON in stored procedure but still getting
this error. I have created this sp on ISQL/W not in Enterprise Manager.

Can anyone please let me know, how to fix this problem.

Thanks
AdnanAdnan (adnanjamil58@.yahoo.ca) writes:
> I have one stored procedure on SQL 6.5 and retrieving data from SQL
> 2000. I have defined a linked server on SQL 6.5. But
> I am getting following error:-
> "Heterogeneous queries require the ANSI_NULLS and ANSI_WARNINGS options
> to be set for the connection. This ensures consistent query semantics.
> Enable these options and then reissue your query."
> I dropped and re-create the stored procedure after adding the SET
> ANSI_NULLS & SET ANSI_WARNINGS ON in stored procedure but still getting
> this error. I have created this sp on ISQL/W not in Enterprise Manager.
> Can anyone please let me know, how to fix this problem.

The setting of ANSI_NULLS is saved with the procedure, why it does
not help setting ANSI_NULLS within the procedure.

ANSI_NULLS is on by default with most interfaces - but not if you use
DB-Library, and the 6.5 tools uses DB-Library. If you insist on using
6.5 tools, be sure to always include this:

SET ANSI_DEFAULTS ON
SET IMPLICIT_TRANSACTIONS OFF
SET CURSOR_CLOSE_ON_COMMIT OFF

then you get the same settings as in the SQL 2000 tools.

Certainly far more easier is to use Query Analyzer. (You mentioned
Enterprise Manager. If you mean the 6.5 tool, it has the same issue
as ISQL/W. It you mean EM 2000, this is a poor tool for maintaining
stored procedures.)

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Erland Sommarskog (esquel@.sommarskog.se) writes:
> The setting of ANSI_NULLS is saved with the procedure, why it does
> not help setting ANSI_NULLS within the procedure.

A clarification: the setting of ANSI_NULLS that applies for a stored
procedure, is the setting that was in effect when you created the procedure.
The rest below applies as before:

> ANSI_NULLS is on by default with most interfaces - but not if you use
> DB-Library, and the 6.5 tools uses DB-Library. If you insist on using
> 6.5 tools, be sure to always include this:
> SET ANSI_DEFAULTS ON
> SET IMPLICIT_TRANSACTIONS OFF
> SET CURSOR_CLOSE_ON_COMMIT OFF
> then you get the same settings as in the SQL 2000 tools.
> Certainly far more easier is to use Query Analyzer. (You mentioned
> Enterprise Manager. If you mean the 6.5 tool, it has the same issue
> as ISQL/W. It you mean EM 2000, this is a poor tool for maintaining
> stored procedures.)

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Help: tempdb grows to 8G suddenly

Hi, guys,
The tempdb is usually 70/80M in size when SQL start, but it grows to 8G
suddently during times, how could I identify where the issue is, it happened
after application upgraded to newer version, but no sp or job changed.
application db is now <>10G.
I am using SQL2000+SP3 IN WIN2000+SP4
Thanks in advance.
Regards,
JackJack,
In my experience, sudden increases in tempdb size are usually attributable
to someone running a query which has a join that has not been qualified
properly. In such a case, a huge result set gets built up (in tempdb where
temporary working space is always allocated during query execution) in order
to satisfy the query and tempdb quite often runs out of space. As a really
basic example, in the old pre SQL-99 syntax, let's say someone was trying to
write
select * from tableA a, tableB b
where a.id = b.id
and a.surname like 'H%'
and b.country = 'Australia'
and they left off the join part of the WHERE clause
select * from tableA a, tableB b
where a.surname like 'H%'
and b.country = 'Australia'
they'd end up with the results getting pulled from the Cartesian product of
tableA & tableB (ie. a CROSS JOIN) rather than an INNER JOIN of the two
tables. Usually, you see this in much larger queries with many JOIN
statements, some with multiple join criteria (eg. multi-column primary
keys), and so it is much harder to identify but if you can get onto the
server while tempdb is filling up rapidly and check sp_who repeatedly, you
should be able to see which SPID is chewing up the CPU & I/O most rapidly;
then you can do a DBCC INPUTBUFFER (<spid>) to find out what query that SPID
is trying to execute and if you look hard enough you can often find a join
in that query that is not qualified properly.
HTH
--
Cheers,
Mike
"Jack Hwang" <jack_hc@.hotmail.com> wrote in message
news:e0WrtAq0EHA.3236@.TK2MSFTNGP15.phx.gbl...
> Hi, guys,
> The tempdb is usually 70/80M in size when SQL start, but it grows to 8G
> suddently during times, how could I identify where the issue is, it
> happened
> after application upgraded to newer version, but no sp or job changed.
> application db is now <>10G.
> I am using SQL2000+SP3 IN WIN2000+SP4
> Thanks in advance.
> Regards,
> Jack
>|||One possibility is a query that caused a lot of worktables to be created.
Since it takes quite a while to create worktables to occupy 8 Gig, you might
use profiler to monitor queries taking > x minutes. Then, shrink the
tempdb, run the query via Query Analyzer and see if tempdb grows again.
--
Peter Yeoh
http://www.yohz.com
Need smaller SQL2K backup files? Use MiniSQLBackup Lite, free!
"Jack Hwang" <jack_hc@.hotmail.com> wrote in message
news:e0WrtAq0EHA.3236@.TK2MSFTNGP15.phx.gbl...
> Hi, guys,
> The tempdb is usually 70/80M in size when SQL start, but it grows to 8G
> suddently during times, how could I identify where the issue is, it
happened
> after application upgraded to newer version, but no sp or job changed.
> application db is now <>10G.
> I am using SQL2000+SP3 IN WIN2000+SP4
> Thanks in advance.
> Regards,
> Jack
>|||Hi, Mike,
thanks for your valuable info, let me see what I can do, things are messed
up.
Regards,
Jack
"Mike Hodgson" <mwh_junk@.hotmail.com> wrote in message
news:OonAlcq0EHA.1308@.TK2MSFTNGP09.phx.gbl...
> Jack,
> In my experience, sudden increases in tempdb size are usually attributable
> to someone running a query which has a join that has not been qualified
> properly. In such a case, a huge result set gets built up (in tempdb
where
> temporary working space is always allocated during query execution) in
order
> to satisfy the query and tempdb quite often runs out of space. As a
really
> basic example, in the old pre SQL-99 syntax, let's say someone was trying
to
> write
> select * from tableA a, tableB b
> where a.id = b.id
> and a.surname like 'H%'
> and b.country = 'Australia'
> and they left off the join part of the WHERE clause
> select * from tableA a, tableB b
> where a.surname like 'H%'
> and b.country = 'Australia'
> they'd end up with the results getting pulled from the Cartesian product
of
> tableA & tableB (ie. a CROSS JOIN) rather than an INNER JOIN of the two
> tables. Usually, you see this in much larger queries with many JOIN
> statements, some with multiple join criteria (eg. multi-column primary
> keys), and so it is much harder to identify but if you can get onto the
> server while tempdb is filling up rapidly and check sp_who repeatedly, you
> should be able to see which SPID is chewing up the CPU & I/O most rapidly;
> then you can do a DBCC INPUTBUFFER (<spid>) to find out what query that
SPID
> is trying to execute and if you look hard enough you can often find a join
> in that query that is not qualified properly.
> HTH
> --
> Cheers,
> Mike
> "Jack Hwang" <jack_hc@.hotmail.com> wrote in message
> news:e0WrtAq0EHA.3236@.TK2MSFTNGP15.phx.gbl...
> > Hi, guys,
> >
> > The tempdb is usually 70/80M in size when SQL start, but it grows to 8G
> > suddently during times, how could I identify where the issue is, it
> > happened
> > after application upgraded to newer version, but no sp or job changed.
> > application db is now <>10G.
> >
> > I am using SQL2000+SP3 IN WIN2000+SP4
> >
> > Thanks in advance.
> >
> > Regards,
> >
> > Jack
> >
> >
>|||FYI...
If you're trying to find where this happens...
Profiler has an event called Missing Join Predicate in the errors and
warnings event class that will capture situation in which SQL Server thinks
that a join predicate is missing. I have never tested the logic to see how
it determines the missing predicate to know if it can absolutley catch all
partial cartesian products, but it does a pretty good job and is worth
taking a look at...
--
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"Jack Hwang" <jack_hc@.hotmail.com> wrote in message
news:O5iC7i41EHA.1264@.TK2MSFTNGP12.phx.gbl...
> Hi, Mike,
> thanks for your valuable info, let me see what I can do, things are messed
> up.
> Regards,
> Jack
> "Mike Hodgson" <mwh_junk@.hotmail.com> wrote in message
> news:OonAlcq0EHA.1308@.TK2MSFTNGP09.phx.gbl...
> > Jack,
> >
> > In my experience, sudden increases in tempdb size are usually
attributable
> > to someone running a query which has a join that has not been qualified
> > properly. In such a case, a huge result set gets built up (in tempdb
> where
> > temporary working space is always allocated during query execution) in
> order
> > to satisfy the query and tempdb quite often runs out of space. As a
> really
> > basic example, in the old pre SQL-99 syntax, let's say someone was
trying
> to
> > write
> >
> > select * from tableA a, tableB b
> > where a.id = b.id
> > and a.surname like 'H%'
> > and b.country = 'Australia'
> >
> > and they left off the join part of the WHERE clause
> >
> > select * from tableA a, tableB b
> > where a.surname like 'H%'
> > and b.country = 'Australia'
> >
> > they'd end up with the results getting pulled from the Cartesian product
> of
> > tableA & tableB (ie. a CROSS JOIN) rather than an INNER JOIN of the two
> > tables. Usually, you see this in much larger queries with many JOIN
> > statements, some with multiple join criteria (eg. multi-column primary
> > keys), and so it is much harder to identify but if you can get onto the
> > server while tempdb is filling up rapidly and check sp_who repeatedly,
you
> > should be able to see which SPID is chewing up the CPU & I/O most
rapidly;
> > then you can do a DBCC INPUTBUFFER (<spid>) to find out what query that
> SPID
> > is trying to execute and if you look hard enough you can often find a
join
> > in that query that is not qualified properly.
> >
> > HTH
> >
> > --
> > Cheers,
> > Mike
> >
> > "Jack Hwang" <jack_hc@.hotmail.com> wrote in message
> > news:e0WrtAq0EHA.3236@.TK2MSFTNGP15.phx.gbl...
> > > Hi, guys,
> > >
> > > The tempdb is usually 70/80M in size when SQL start, but it grows to
8G
> > > suddently during times, how could I identify where the issue is, it
> > > happened
> > > after application upgraded to newer version, but no sp or job changed.
> > > application db is now <>10G.
> > >
> > > I am using SQL2000+SP3 IN WIN2000+SP4
> > >
> > > Thanks in advance.
> > >
> > > Regards,
> > >
> > > Jack
> > >
> > >
> >
> >
>

Help: Join few records in a same field and same database table.

Hi guys,

I need urgent help here. How to join few records in a same field and same database table. For example:

id sequence_no itemcode description
1 1 APP Apple
1 2 BNN Banana
2 1 ORG Orange

What is the sql query that will return row as follow:

id description
1 Apple Banana
2 Orange

Thank you in advance guys...

hi, you can try this

-- create a function
CREATE FUNCTION dbo.GetFruitSequence
(
@.id int
)
RETURNS varchar(100)
AS
BEGIN
DECLARE @.FruitSequence varchar(100)
SET @.FruitSequence = ''

SELECT @.FruitSequence = @.FruitSequence + [description] + ', '
FROM Fruits
WHERE [id] = @.id
ORDER BY
[sequence_no]

IF @.FruitSequence <> '' SET @.FruitSequence = LEFT(@.FruitSequence, LEN(@.FruitSequence) - 1)

RETURN @.FruitSequence

END

GO

-- to use the function
SELECT DISTINCT [id]
, dbo.GetFruitSequence([id]) as [description]
FROM Fruits
ORDER BY
[id]

GO|||Thank you Rhamille|||

You can use the following query in SQL Server 2005

Code Snippet

Create Table #itemdata (

[id] int ,

[sequence_no] int ,

[itemcode] Varchar(100) ,

[description] Varchar(100)

);

Insert Into #itemdata Values('1','1','APP','Apple');

Insert Into #itemdata Values('1','2','BNN','Banana');

Insert Into #itemdata Values('2','1','ORG','Orange');

Select

id

,(Select [description] + ' ' as [text()] From #itemdata as Sub Where Sub.id=Main.id For XML Path(''), ELEMENTS) as List

From

#itemdata as Main