MS SQL Server 2005 timeout error when using Management Studio

Hi,
When I try to update a column value using MS SQL Server 2005 Management
Studio (SMS), I am getting "timeout period elapsed prior to the completion
of the operation".
Our server setup is:
2 identical MS SQL 2005 installed in Windows 2003 OS and the first server
connected to second server using linked server. So if I update any column
value in Server A, it should automatically change the value in Server B
also. This works fine when I use update query in management studio. But if
directly open the table in management studio and try to update the column
value, I am keep getting the above mentioned error even though I have set
the timeout to large value (say 600 to 3,000 secs) in all places where ever
values related to timeout.
The above mentioned scenario works fine with 2 MS SQL 2000 servers running
in Win 2k OS and it is being in production for long time without having any
problem.
Any idea, as I have tried out all possible solutions.
Thanks
VN
Hi
You seem to have posted this separately to several news groups. This can
cause people unnecessary effort if the solution is solved elsewhere.
John
"Venkat N" wrote:

> Hi,
> When I try to update a column value using MS SQL Server 2005 Management
> Studio (SMS), I am getting "timeout period elapsed prior to the completion
> of the operation".
> Our server setup is:
> 2 identical MS SQL 2005 installed in Windows 2003 OS and the first server
> connected to second server using linked server. So if I update any column
> value in Server A, it should automatically change the value in Server B
> also. This works fine when I use update query in management studio. But if
> directly open the table in management studio and try to update the column
> value, I am keep getting the above mentioned error even though I have set
> the timeout to large value (say 600 to 3,000 secs) in all places where ever
> values related to timeout.
> The above mentioned scenario works fine with 2 MS SQL 2000 servers running
> in Win 2k OS and it is being in production for long time without having any
> problem.
> Any idea, as I have tried out all possible solutions.
> Thanks
> VN
>
>

MS SQL Server 2005 timeout error when using Management Studio

Hi,
When I try to update a column value using MS SQL Server 2005 Management
Studio (SMS), I am getting "timeout period elapsed prior to the completion
of the operation".
Our server setup is:
2 identical MS SQL 2005 installed in Windows 2003 OS and the first server
connected to second server using linked server. So if I update any column
value in Server A, it should automatically change the value in Server B
also. This works fine when I use update query in management studio. But if
directly open the table in management studio and try to update the column
value, I am keep getting the above mentioned error even though I have set
the timeout to large value (say 600 to 3,000 secs) in all places where ever
values related to timeout.
The above mentioned scenario works fine with 2 MS SQL 2000 servers running
in Win 2k OS and it is being in production for long time without having any
problem.
Any idea, as I have tried out all possible solutions.
Thanks
VNHi
You seem to have posted this separately to several news groups. This can
cause people unnecessary effort if the solution is solved elsewhere.
John
"Venkat N" wrote:

> Hi,
> When I try to update a column value using MS SQL Server 2005 Management
> Studio (SMS), I am getting "timeout period elapsed prior to the completion
> of the operation".
> Our server setup is:
> 2 identical MS SQL 2005 installed in Windows 2003 OS and the first server
> connected to second server using linked server. So if I update any column
> value in Server A, it should automatically change the value in Server B
> also. This works fine when I use update query in management studio. But if
> directly open the table in management studio and try to update the column
> value, I am keep getting the above mentioned error even though I have set
> the timeout to large value (say 600 to 3,000 secs) in all places where eve
r
> values related to timeout.
> The above mentioned scenario works fine with 2 MS SQL 2000 servers running
> in Win 2k OS and it is being in production for long time without having an
y
> problem.
> Any idea, as I have tried out all possible solutions.
> Thanks
> VN
>
>sql

MS SQL SERVER 2005 BUG? Cannot insert NULL into column diagram_id

Hi friends,
when trying to save a diagram I got an error:
The sp_creatediagram procedure attempted to return a status of NULL, which is not allowed.
Whats with this??I had the same issue an just fixed it by turning the "diagram_id" field in SysDiagrams table to "identitity".

I dropped the table and ran the following script.

CREATE TABLE [dbo].[sysdiagrams](
[name] [nvarchar](128) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[principal_id] [int] NOT NULL,
[diagram_id] [int] identity(1,1),
[version] [int] NULL,
[definition] [varbinary](max) NULL
) ON [PRIMARY]

It worked.

MS SQL Server 2000 & ADP Write Conflict Error

Im new to SQL and am having some difficulty.
I created a DB that has about 5 tables, all of which are related via the
client_id column.
I created a trigger which upon insert into my main table shoots the
client_id into the corresponding column in the other tables.
My front end is a MS ADP project. When I try to insert a new record
with "ALL" the fields in my MS Access ADP Form filled in I get a "Write
COnflict" Error. But when I try to insert a new record with "ONLY"
filling in the client_id filed the record is created without a proble?
Can anyone help me?
*** Sent via Developersdex http://www.examnotes.net ***Two possible things to check...
1. Do any of the other tables have columns that do not permit null?
If so, when the trigger tries to do the insert, you can't complete.
2. Is the ForeignKey in any of the tables you are writing to, .. identity.
If so, you are creating a duplicate.
"Anon" <anonymous@.devdex.com> wrote in message
news:OLHVYmlMGHA.3732@.TK2MSFTNGP10.phx.gbl...
>
> Im new to SQL and am having some difficulty.
> I created a DB that has about 5 tables, all of which are related via the
> client_id column.
> I created a trigger which upon insert into my main table shoots the
> client_id into the corresponding column in the other tables.
> My front end is a MS ADP project. When I try to insert a new record
> with "ALL" the fields in my MS Access ADP Form filled in I get a "Write
> COnflict" Error. But when I try to insert a new record with "ONLY"
> filling in the client_id filed the record is created without a proble?
> Can anyone help me?
>
> *** Sent via Developersdex http://www.examnotes.net ***|||1. I added a default value to the columns in the "other" tables that do
not allow nulls
2. The foreignkeys in all the other tables are the primary keys...if
that makes any sense (Is there a work around?)
Two possible things to check...
1. Do any of the other tables have columns that do not permit null?
If so, when the trigger tries to do the insert, you can't complete.
2. Is the ForeignKey in any of the tables you are writing to, ..
identity.
If so, you are creating a duplicate.
-RS
*** Sent via Developersdex http://www.examnotes.net ***|||1. I added a default value to the columns in the "other" tables that do
not allow nulls
2. The foreignkeys in all the other tables are the primary keys...if
that makes any sense (Is there a work around?)
Two possible things to check...
1. Do any of the other tables have columns that do not permit null?
If so, when the trigger tries to do the insert, you can't complete.
2. Is the ForeignKey in any of the tables you are writing to, ..
identity.
If so, you are creating a duplicate.
-RS
*** Sent via Developersdex http://www.examnotes.net ***sql

MS SQL query, whats the default order the rows returned are sorted by?

i have a table and a column called req_id, i have it set as the primary key.. so if i just do SELECT * FROM table, shouldnt the rows returned be sorted by the order that the rows were inserted?

this database was improted from an access database.. when i did that in access it would return the rows in sorted order by the order the row was inserted.. but now in MS SQL, its not sorted in that order.. i can't really tell what type of order it's inIf you want an order, specify the order with the ORDER BY clause. If you are willing to take whatever order the optimizer decides on, omit the ORDER BY clause.

-PatP|||hmm this is weird.. in the access database if i select it, they're returned in the order the rows were inserted.. but after importing that database into ms sql, and selecting that table, the order isn't the same row i got when i ran the query in the access db|||Jet, the default database engine used by MS-Access is rather "simple-minded" when it comes to query optimization. MS-SQL has a much more powerful optimizer, which is a two-edged sword... The MS-SQL optimizer is able to easily process queries that Jet would never complete, but it does that processing in a very different way. As an interesting side effect, it also means that unless you specify an order in your query, there is no guarantee that running the exact same query on the same box will ever return the rows in the same order, even though it often will return them in a consistant order.

-PatP|||Ahhh ok i see what you're saying.

In my query, i had a left join statement in there.. i took that out and used a subquery instead of the join and it returns the rows fine now in the order they were inserted. Looks like the join caused the problem.|||No, the JOIN did not cause the problem. The absence of an ORDER BY statement caused the problem.|||The problem is actually a lot simpler than "the join caused the problem". If you want an order, specify it with the ORDER BY clause. If you don't care about an order and are willing to accept the order determined by the optimizer at the moment, omit the ORDER BY clause.

-PatP

MS SQL Query statement.

Hi All,
Problem statement:
Group 2 tables.
B(B_ID,B_DES)
C(C_ID,B_ID,C_BY)
select B.*,C.*
from b bx inner join cx on bx.b_id=cx.b_id
group by B column,,C column
Output:
B_ID column | C_BY
1 we
1 xy
2 DF
Above result is not what i desired, instead, i needs:
1 we
xy
2 DF
How to solve it'
thank you in advance..Help will be appreciated.
Best regards,
Gin Lye KhorRepresent the results in a report engine using Group & Detail bands?
"Daniel" <Daniel@.discussions.microsoft.com> wrote in message
news:9B5D30B5-ADFB-404D-97D1-5CC5DB252BC4@.microsoft.com...
> Hi All,
> Problem statement:
> Group 2 tables.
> B(B_ID,B_DES)
> C(C_ID,B_ID,C_BY)
> select B.*,C.*
> from b bx inner join cx on bx.b_id=cx.b_id
> group by B column,,C column
> Output:
> B_ID column | C_BY
> 1 we
> 1 xy
> 2 DF
>
> Above result is not what i desired, instead, i needs:
> 1 we
> xy
> 2 DF
> How to solve it'
> thank you in advance..Help will be appreciated.
> Best regards,
> Gin Lye Khor
>
>

MS SQL problem: fill down variable number of rows from last known value.

Using Microsoft query analyser SQL version 8, I believe.

Got a SQL table with a column that has NULLS in it. What I want to do is fill down the value in the row above based on a condition in another column (recordtype). Came up with the following code but unfortunately it only fills in the record directly below. Not those further down (variable number of NULL rows). Any idea how to get these to fill down? Suspect the answer may lie with block cursors but 1) I'm told they're slow (I have 2 and 1/2 million records in this table) and 2) I've never used cursors before!

________________

Update dbo.NSTS

Set dbo.NSTS.Number = NSTS_1.Number

FROM dbo.NSTS LEFT OUTER JOIN
dbo.NSTS AS NSTS_1 ON dbo.NSTS.[Id] = ((NSTS_1.[Id])+1)

WHERE dbo.NSTS.RecordType like '101' AND (dbo.NSTS.Number is null or dbo.NSTS.Number like '')

________________

Example of data table here:

ID RecordType Number
1 100 4325273182
2 101 NULL
3 101 NULL
4 101 NULL
5 100 4342684020
6 100 4342696800
7 100 4304028782
8 100 4305914743
9 101 NULL
10 101 NULL
11 100 4328179942
12 101 NULL
13 101 NULL
14 101 NULL
15 101 NULL
16 100 4329313804
17 100 4329874164
18 101 NULL
19 100 4330053764
20 101 NULL
_________________

I did get a cursor loop working but can only retrieve 1 row and (try as I might with the so called help examples) can't seem to pull down 2 rows at a time. Just can't seem to get it to accept this command at all: SQLSetStmtAttr(hStmt, SQL_ATTR_ROW_ARRAY_SIZE, (SQLPOINTER) 2 ,0).

Help would be very much appreciated!not sure I understand...when you say fill...fill with what?

Also the order of data in a database is meaningless, so what does "next" mean?|||Beg your pardon. Can't see the wood for the trees.

In this case the data is in a strictly sequential order as defined by the ID field. So, first has ID = 1 and next = current ID + 1.

Data comes from another source in a rather weird format which I won't bother you with. Each row with recordtype of 100 has latest data. If something has changed in the past then there will be another row with a recordtype of 101 and with the detail of the value that changed in the appropriate column. If a column hasn't changed then a NULL will be present. In this instance, I want to take the value in that column (Number in this instance) from the row that has an ID of ID-1 (the newer record.) It should be noted that it is possible to have NULL values in the number field so you could have a record type of 100 with a NULL value. In this case it needs to be left as NULL.

Have I explained my dilema appropriately? I've actually just tried doing this with a while (count(recordtype) ... where Number is null >0) type statement before I realised there actually were valid nulls in the Number column! :eek:|||Beg your pardon. Can't see the wood for the trees.

Guess I'm not smart enough to help on this.

Good Luck|||This is actually a good puzzle, but I don't have enough time to solve it before my next meeting. If nobody else solves it before then, I'll do it tonight.

-PatP|||Sorry I didn't get this one last night. It was still a fine puzzle!CREATE TABLE #NSTS (
id INT NOT NULL
, record INT NOT NULL
, number BIGINT NULL
)

INSERT INTO #NSTS (id, record, number)
SELECT 1, 100, 4325273182 UNION
SELECT 2, 101, NULL UNION
SELECT 3, 101, NULL UNION
SELECT 4, 101, NULL UNION
SELECT 5, 100, 4342684020 UNION
SELECT 6, 100, 4342696800 UNION
SELECT 7, 100, 4304028782 UNION
SELECT 8, 100, 4305914743 UNION
SELECT 9, 101, NULL UNION
SELECT 10, 101, NULL UNION
SELECT 11, 100, 4328179942 UNION
SELECT 12, 101, NULL UNION
SELECT 13, 101, NULL UNION
SELECT 14, 101, NULL UNION
SELECT 15, 101, NULL UNION
SELECT 16, 100, 4329313804 UNION
SELECT 17, 100, 4329874164 UNION
SELECT 18, 101, NULL UNION
SELECT 19, 100, 4330053764 UNION
SELECT 20, 101, NULL

SELECT a.id, a.record, (SELECT b.number
FROM #NSTS AS b
WHERE b.id = (SELECT Max(c.id)
FROM #NSTS AS c
WHERE c.number IS NOT NULL
AND c.id BETWEEN (SELECT Max(d.id)
FROM #NSTS AS d
WHERE d.id <= a.id
AND d.record = (SELECT Min(e.record)
FROM #NSTS AS e)) AND a.id))
FROM #NSTS AS a

DROP TABLE #NSTS
-PatP|||Wow. What's it like in your mind? All flashing cogs and some sort of weird matter distortion device that bends reality into the kind of shapes normally only encountered on class A drugs?

Many thanks for this. Much more elegant than my attempt. Actually got that to work in the end by using:

________

While (select count(RecordType) from NSTS where ((Number is null) and (RecordType like '101'))) > (select count(RecordType) from NSTS where ((Number is null) and (RecordType like '100')))

Update NSTS

Set NSTS.Number = Tbl_NSTS_PracticeDownloads_1.Number

FROM NSTS LEFT OUTER JOIN
NSTS AS Tbl_NSTS_PracticeDownloads_1 ON NSTS.[Id] = ((Tbl_NSTS_PracticeDownloads_1.[Id])+1)

WHERE NSTS.RecordType like '101' AND (NSTS.Number is null or NSTS.Number like '')

_________

Question now is: which will crunch fastest with 2/1/2 million records? Guess I'll have to find out!

Once again, many thanks for your help on this!

MS SQL getDate() Function remove Time

Hi,

I am creating creating a table with a Date column dd-mm-yyyy. But I
cant seem to find a SQL function that just returns today's date.
getDate() returns the time as well so I cant use it.

The reason is simply that I want to update/overwrite over and over
again all records from current day but not touch the ones from
yesterday etc and with the timestamp in there I just end up adding
more and more rows for the same day.

In other words I only want to preserve rows are from yesterday or
older but overwrite ones from today.

Any help will be appricated.

Thank you!

YasOn Jul 4, 3:53 pm, Yas <yas...@.gmail.comwrote:

Quote:

Originally Posted by

Hi,
>
I am creating creating a table with a Date column dd-mm-yyyy. But I
cant seem to find a SQL function that just returns today's date.
getDate() returns the time as well so I cant use it.
>
The reason is simply that I want to update/overwrite over and over
again all records from current day but not touch the ones from
yesterday etc and with the timestamp in there I just end up adding
more and more rows for the same day.
>
In other words I only want to preserve rows are from yesterday or
older but overwrite ones from today.
>
Any help will be appricated.
>
Thank you!
>
Yas

AFAIK a there is no DATE type in MS SQL, only DATETIME so you cannot
store only the date part.
You can use SELECT CONVERT(VARCHAR(8),datevalue,112) to return the
datetime in YYYYMMDD format without the time but it's stored as a
VARCHAR not a DATETIME.|||On 4 Jul, 17:14, Roy Harvey <roy_har...@.snet.netwrote:

Quote:

Originally Posted by

To remove the time from a datetime such as getdate():
>
SELECT dateadd(day,datediff(day,0,getdate()),0)
>
I strongly suggest not storing a date column as a string. Use a
datetime and just set the time to zeroes if you only need the date.

Hi I am storing the coumn as datetime and not string. However, using
the above suggestion (day,datediff(day,0,getdate()),0) I get a column
with Date+Time set to Zeros. OK, but the problem is when I run the
update/insert records command again It doesn't overwrite the columns
with today's date, its as if sql is secretly inserting the time by it
self and even though to my eyes the rows is exactly the same SQL adds
a new row thinking it is distinct.

I would like that if the table had a rowOld with: (ColValue1,
ColValue2,2007-07-04 00.00.00.000)
If I use the above suggestion and insert a rowNew with same values
(ColValue1, ColValue2, 2007-07-04 00.00.00.000)
...It should overwrite rowOld with rowNew, not insert rowNew as a new
row.
...and only insert as a new rowNew2 when this row has a different date
eg. 2007-07-05 00.00.00.000

I thought it would as time is now set to Zeros, but it doesn't. Is SQL
marking each row in the examples above with a time stamp? even though
it is not shown in the row value?

Thanks again :-)

Yas|||On Wed, 04 Jul 2007 08:55:49 -0700, Yas <yasar1@.gmail.comwrote:

Quote:

Originally Posted by

>I would like that if the table had a rowOld with: (ColValue1,
>ColValue2,2007-07-04 00.00.00.000)
>If I use the above suggestion and insert a rowNew with same values
>(ColValue1, ColValue2, 2007-07-04 00.00.00.000)
>...It should overwrite rowOld with rowNew, not insert rowNew as a new
>row.
>...and only insert as a new rowNew2 when this row has a different date
>eg. 2007-07-05 00.00.00.000

You can write an INSERT for a new row, or an UPDATE for an existing
row, but you have to choose which it is to be. In your case you have
to find out if the row exists and then run INSERT or UPDATE depending
on what you find.

Microsoft is adding MERGE to the next release of SQL Server, which
would allow you to write one command to accomplish both functions, but
it is not available today.

Roy Harvey
Beacon Falls, CT

Ms Sql Blob ?

Hi to all

I'm starting ussing Microsoft SQL2000, and i need help about how to insert any type of data file( .xls,.pdf, .jpg, .txt, ...) Into a column to extract later the files on web.

I heared about data tipe BLOB, but i can't find the way to work with it...

I'm lost... some body can help me?

Thanks.I struggled for a long time to try to store images in the SQL 2000 database. After long hours and many frustrations, I decided to leave these kinds of files in the capable hands of the file system. Once I did that, life got a lot easier.

Ling

MS SQL 2000 - SQL Select with XPATH Where Clause

In MS SQL Server 2000, is there an easy way to use an XPATH statement in a
WHERE clause? I have a table with a TEXT column that contains XML. I would
like to query this table and select any rows where the XML data field meets
my XPATH criteria.
I'm attempting to avoid using cursors and sp_xml_preparedocument on each row
of data in the table, but with SQL Server 2000, that may be my only option.
Any help would be appreciated!Bryan (Bryan@.discussions.microsoft.com) writes:
> In MS SQL Server 2000, is there an easy way to use an XPATH statement in
> a WHERE clause?
Yeah, upgrade the instance to SQL 2005. :-)

> I have a table with a TEXT column that contains XML. I
> would like to query this table and select any rows where the XML data
> field meets my XPATH criteria.
> I'm attempting to avoid using cursors and sp_xml_preparedocument on each
> row of data in the table, but with SQL Server 2000, that may be my only
> option.
OPENXML is on the only XML support that SQL 2000 offers. I agree that a
cursor and preparedocument for each is not appealing. The other alternative
would be to bring the data to the client, but is probably even less
appealing.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx