Help Backup: records

Showing posts with label records. Show all posts

Monday, March 26, 2012

Hidden records?

Hi all,
Using vb6 ado connection I can see the record
e.g. SELECT * FROM table where id=1234
I will see the record
but
Using the same query on Enterprise Manager
the same query results to nothing.
Also
SELECT COUNT(*) AS Expr1
FROM table
Result:
vb6 query:1726 records
Ent. Manager view: 18 records
My questions is why Ent. Manager cannot see all the records
I am using SQL Server 2000 standard edition
The database itselt is nearly 21gb.
Any help or advice appreciated
Regards,
AllanAllan
Are you sure you connected to the same database?
"Allan" <Allan@.discussions.microsoft.com> wrote in message
news:A58D0D12-4BD4-4125-9542-8A45D53BA2E3@.microsoft.com...
> Hi all,
> Using vb6 ado connection I can see the record
> e.g. SELECT * FROM table where id=1234
> I will see the record
> but
> Using the same query on Enterprise Manager
> the same query results to nothing.
> Also
> SELECT COUNT(*) AS Expr1
> FROM table
> Result:
> vb6 query:1726 records
> Ent. Manager view: 18 records
> My questions is why Ent. Manager cannot see all the records
> I am using SQL Server 2000 standard edition
> The database itselt is nearly 21gb.
> Any help or advice appreciated
> Regards,
> Allan
>|||> Also
> SELECT COUNT(*) AS Expr1
> FROM table
> Result:
> vb6 query:1726 records
> Ent. Manager view: 18 records
My guess is that these are different server/databases with the same table.
Try verifying connection context with SELECT @.@.SERVERNAME, DB_NAME().
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Allan" <Allan@.discussions.microsoft.com> wrote in message
news:A58D0D12-4BD4-4125-9542-8A45D53BA2E3@.microsoft.com...
> Hi all,
> Using vb6 ado connection I can see the record
> e.g. SELECT * FROM table where id=1234
> I will see the record
> but
> Using the same query on Enterprise Manager
> the same query results to nothing.
> Also
> SELECT COUNT(*) AS Expr1
> FROM table
> Result:
> vb6 query:1726 records
> Ent. Manager view: 18 records
> My questions is why Ent. Manager cannot see all the records
> I am using SQL Server 2000 standard edition
> The database itselt is nearly 21gb.
> Any help or advice appreciated
> Regards,
> Allan
>|||Uri,
Yes. We only have one database. I connect via ip address and database name
"Uri Dimant" wrote:
> Allan
> Are you sure you connected to the same database?
> "Allan" <Allan@.discussions.microsoft.com> wrote in message
> news:A58D0D12-4BD4-4125-9542-8A45D53BA2E3@.microsoft.com...
> > Hi all,
> >
> > Using vb6 ado connection I can see the record
> > e.g. SELECT * FROM table where id=1234
> > I will see the record
> >
> > but
> > Using the same query on Enterprise Manager
> > the same query results to nothing.
> >
> > Also
> > SELECT COUNT(*) AS Expr1
> > FROM table
> >
> > Result:
> > vb6 query:1726 records
> > Ent. Manager view: 18 records
> >
> > My questions is why Ent. Manager cannot see all the records
> >
> > I am using SQL Server 2000 standard edition
> > The database itselt is nearly 21gb.
> >
> > Any help or advice appreciated
> >
> > Regards,
> > Allan
> >
>
>|||Also, after verifying the same servername/databasename, make sure you use the same table by
owner-qualifying the name, like
SELECT COUNT(*) FROM dbo.table
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:2D08ED0C-D379-4DE6-97E1-3A97037EA0AE@.microsoft.com...
>> Also
>> SELECT COUNT(*) AS Expr1
>> FROM table
>> Result:
>> vb6 query:1726 records
>> Ent. Manager view: 18 records
> My guess is that these are different server/databases with the same table. Try verifying
> connection context with SELECT @.@.SERVERNAME, DB_NAME().
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Allan" <Allan@.discussions.microsoft.com> wrote in message
> news:A58D0D12-4BD4-4125-9542-8A45D53BA2E3@.microsoft.com...
>> Hi all,
>> Using vb6 ado connection I can see the record
>> e.g. SELECT * FROM table where id=1234
>> I will see the record
>> but
>> Using the same query on Enterprise Manager
>> the same query results to nothing.
>> Also
>> SELECT COUNT(*) AS Expr1
>> FROM table
>> Result:
>> vb6 query:1726 records
>> Ent. Manager view: 18 records
>> My questions is why Ent. Manager cannot see all the records
>> I am using SQL Server 2000 standard edition
>> The database itselt is nearly 21gb.
>> Any help or advice appreciated
>> Regards,
>> Allan
>|||Hi Dan
Same reply as Uri
We only have one database. I connect via ip address and database name
I wish that was the case and the problem is solved
Will the size of the database (21 gb) makes any difference to the standard
edition of SQL server?
How about data corruption?
Thank you for your interest
"Dan Guzman" wrote:
> > Also
> > SELECT COUNT(*) AS Expr1
> > FROM table
> >
> > Result:
> > vb6 query:1726 records
> > Ent. Manager view: 18 records
> My guess is that these are different server/databases with the same table.
> Try verifying connection context with SELECT @.@.SERVERNAME, DB_NAME().
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Allan" <Allan@.discussions.microsoft.com> wrote in message
> news:A58D0D12-4BD4-4125-9542-8A45D53BA2E3@.microsoft.com...
> > Hi all,
> >
> > Using vb6 ado connection I can see the record
> > e.g. SELECT * FROM table where id=1234
> > I will see the record
> >
> > but
> > Using the same query on Enterprise Manager
> > the same query results to nothing.
> >
> > Also
> > SELECT COUNT(*) AS Expr1
> > FROM table
> >
> > Result:
> > vb6 query:1726 records
> > Ent. Manager view: 18 records
> >
> > My questions is why Ent. Manager cannot see all the records
> >
> > I am using SQL Server 2000 standard edition
> > The database itselt is nearly 21gb.
> >
> > Any help or advice appreciated
> >
> > Regards,
> > Allan
> >
>|||"Allan" <Allan@.discussions.microsoft.com> wrote in message
news:FE6482BA-6070-40EE-AC99-ABDA230A42BD@.microsoft.com...
> Hi Dan
> Same reply as Uri
> We only have one database. I connect via ip address and database name
> I wish that was the case and the problem is solved
> Will the size of the database (21 gb) makes any difference to the standard
> edition of SQL server?
Not at all.
> How about data corruption?
Highly unlikely that a query one place would return one set of data and the
same query from another place would return different data.
So sounds like something is missing in your description (like a table with
the same name was accidentally created in the MASTER DB and some records
inserted and when you connect the 2nd way you're hitting master, not your
database and seeing the wrong table.
As other have said, to be 100% sure, do a select * from
<dbname>.<owner>.tablename.
> Thank you for your interest
> "Dan Guzman" wrote:
>> > Also
>> > SELECT COUNT(*) AS Expr1
>> > FROM table
>> >
>> > Result:
>> > vb6 query:1726 records
>> > Ent. Manager view: 18 records
>> My guess is that these are different server/databases with the same
>> table.
>> Try verifying connection context with SELECT @.@.SERVERNAME, DB_NAME().
>> --
>> Hope this helps.
>> Dan Guzman
>> SQL Server MVP
>> "Allan" <Allan@.discussions.microsoft.com> wrote in message
>> news:A58D0D12-4BD4-4125-9542-8A45D53BA2E3@.microsoft.com...
>> > Hi all,
>> >
>> > Using vb6 ado connection I can see the record
>> > e.g. SELECT * FROM table where id=1234
>> > I will see the record
>> >
>> > but
>> > Using the same query on Enterprise Manager
>> > the same query results to nothing.
>> >
>> > Also
>> > SELECT COUNT(*) AS Expr1
>> > FROM table
>> >
>> > Result:
>> > vb6 query:1726 records
>> > Ent. Manager view: 18 records
>> >
>> > My questions is why Ent. Manager cannot see all the records
>> >
>> > I am using SQL Server 2000 standard edition
>> > The database itselt is nearly 21gb.
>> >
>> > Any help or advice appreciated
>> >
>> > Regards,
>> > Allan
>> >
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html|||I think Tibor's idea that you have different tables in different schema is
likely. The Best Practice is to always schema-qualify table names. Not
only does this avoid ambiguous references, it helps improve performance too.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Allan" <Allan@.discussions.microsoft.com> wrote in message
news:FE6482BA-6070-40EE-AC99-ABDA230A42BD@.microsoft.com...
> Hi Dan
> Same reply as Uri
> We only have one database. I connect via ip address and database name
> I wish that was the case and the problem is solved
> Will the size of the database (21 gb) makes any difference to the standard
> edition of SQL server?
> How about data corruption?
> Thank you for your interest
> "Dan Guzman" wrote:
>> > Also
>> > SELECT COUNT(*) AS Expr1
>> > FROM table
>> >
>> > Result:
>> > vb6 query:1726 records
>> > Ent. Manager view: 18 records
>> My guess is that these are different server/databases with the same
>> table.
>> Try verifying connection context with SELECT @.@.SERVERNAME, DB_NAME().
>> --
>> Hope this helps.
>> Dan Guzman
>> SQL Server MVP
>> "Allan" <Allan@.discussions.microsoft.com> wrote in message
>> news:A58D0D12-4BD4-4125-9542-8A45D53BA2E3@.microsoft.com...
>> > Hi all,
>> >
>> > Using vb6 ado connection I can see the record
>> > e.g. SELECT * FROM table where id=1234
>> > I will see the record
>> >
>> > but
>> > Using the same query on Enterprise Manager
>> > the same query results to nothing.
>> >
>> > Also
>> > SELECT COUNT(*) AS Expr1
>> > FROM table
>> >
>> > Result:
>> > vb6 query:1726 records
>> > Ent. Manager view: 18 records
>> >
>> > My questions is why Ent. Manager cannot see all the records
>> >
>> > I am using SQL Server 2000 standard edition
>> > The database itselt is nearly 21gb.
>> >
>> > Any help or advice appreciated
>> >
>> > Regards,
>> > Allan
>> >|||To Uri,Dan,Greg and Tibor,
Thank for all your help. It was decided to restore from last nights backup
and redo todays activities.
After restore queries from vb6 and Ent. Manager equalled
I am printing your advice for future reference
Regards,
Allan
"Allan" wrote:
> Hi all,
> Using vb6 ado connection I can see the record
> e.g. SELECT * FROM table where id=1234
> I will see the record
> but
> Using the same query on Enterprise Manager
> the same query results to nothing.
> Also
> SELECT COUNT(*) AS Expr1
> FROM table
> Result:
> vb6 query:1726 records
> Ent. Manager view: 18 records
> My questions is why Ent. Manager cannot see all the records
> I am using SQL Server 2000 standard edition
> The database itselt is nearly 21gb.
> Any help or advice appreciated
> Regards,
> Allan
>|||> Not only does this avoid ambiguous references, it helps improve
> performance too.
Is the performance gain documented any where?
/Sjang|||On Sep 3, 10:46 am, "Henrik Davidsen" <n...@.none.dk> wrote:
> > Not only does this avoid ambiguous references, it helps improve
> > performance too.
> Is the performance gain documented any where?
> /Sjang
You can see for yourself easily:
DECLARE @.i INT, @.SQL NVARCHAR(300)
SELECT @.i = 0
WHILE @.i < 10000 BEGIN
SET @.i = @.i + 1
SET @.SQL = 'CREATE PROCEDURE dbo.DummyProc' + CAST(@.i AS
NVARCHAR(10)) + ' AS RETURN 0'
EXEC sp_executesql @.SQL
END
GO
DECLARE @.i INT, @.j INT, @.d DATETIME, @.SQL NVARCHAR(300), @.SQL_to_run
NVARCHAR(300)
SELECT @.i = 0, @.j = 0, @.d = getdate()
WHILE @.j < 3 BEGIN
SET @.j = @.j + 1
IF @.j = 1 BEGIN
SET @.SQL = 'EXEC DummyProc'
END ELSE BEGIN
SET @.SQL = 'EXEC dbo.DummyProc'
END
WHILE @.i < 10000 BEGIN
SET @.i = @.i + 1
SET @.SQL_to_run = @.SQL + CAST(@.i AS NVARCHAR(10))
--PRINT @.SQL_to_run
IF @.j <3 BEGIN
EXEC sp_executesql @.SQL_to_run
END
END
SELECT DATEDIFF(ms, @.d, GEtdate()) as ms
SELECT @.i = 0, @.d = getdate()
END
GO
DECLARE @.i INT, @.SQL NVARCHAR(300)
SELECT @.i = 0
WHILE @.i < 10000 BEGIN
SET @.i = @.i + 1
SET @.SQL = 'DROP PROCEDURE dbo.DummyProc' + CAST(@.i AS
NVARCHAR(10))
EXEC sp_executesql @.SQL
END
GO
-- without dbo.
ms
--
3860
(1 row(s) affected)
-- with dbo
ms
--
640
(1 row(s) affected)
-- the overhead of running the loop
ms
--
46|||On Mon, 3 Sep 2007 17:46:30 +0200, "Henrik Davidsen" <none@.none.dk>
wrote:
>> Not only does this avoid ambiguous references, it helps improve
>> performance too.
>Is the performance gain documented any where?
Don't have a link handy, but it's mentioned in various white papers,
also just saw it mentioned in Kalen's "Storage Engine 2005" book, near
the middle, I'm too lazy to look up the page!
Per Kalen, in SQL2005 apparently it helps even a little more, if you
don't specify then there is more that SQL2005 does before it decides
you meant dbo. all along.
OK it's a small effect, but on a high-transaction system it might get
you a few TPS, and also there may be something of an increase in the
use of namespaces generally with 2005, so it might be a good practice
just on that basis.
Josh|||You *could* have had an open transaction in snapshot mode on 2005, ...
but it does sound more like corruption. Did you have any crashes
recently? Did you try running DBCC CHECKDB? Was this on a production
system? It's really a table and not a view with some kind of weird
user-sensitive parameters on it? Were you using the same login in
both cases? Was it repeatable - you could log off and log back in
again in both situations and see the same conflicting results?
I know it's moot now, but nobody likes that sort of anomaly.
J.
On Mon, 3 Sep 2007 07:00:00 -0700, Allan
<Allan@.discussions.microsoft.com> wrote:
>Hi all,
>Using vb6 ado connection I can see the record
>e.g. SELECT * FROM table where id=1234
>I will see the record
>but
>Using the same query on Enterprise Manager
>the same query results to nothing.
>Also
>SELECT COUNT(*) AS Expr1
>FROM table
>Result:
>vb6 query:1726 records
>Ent. Manager view: 18 records
>My questions is why Ent. Manager cannot see all the records
>I am using SQL Server 2000 standard edition
>The database itselt is nearly 21gb.
>Any help or advice appreciated
>Regards,
>Allan|||Hi J,
"JXStern" wrote:
> You *could* have had an open transaction in snapshot mode on 2005, ...
We are using SQL Server 2000 standard edition
> but it does sound more like corruption. Did you have any crashes
> recently?
No crashes
Did you try running DBCC CHECKDB?
No.
Was this on a production system?
We are very small company. Production and test system is the same.
It's really a table and not a view with some kind of weird user-sensitive
parameters on it?
Yes it is a table. The query was taken (copied and pasted) directly from vb6
source code that hasn't been changed for 3 years
Were you using the same login in both cases?
There is only one login "sa"
Was it repeatable - you could log off and log back in again in both
situations and see the same conflicting results?
We restarted the SQL server 2000. Same result.
Restarted Windows Server 2003 standard edition. Same result
> I know it's moot now, but nobody likes that sort of anomaly.
> J.
>
> On Mon, 3 Sep 2007 07:00:00 -0700, Allan
> <Allan@.discussions.microsoft.com> wrote:
> >Hi all,
> >
> >Using vb6 ado connection I can see the record
> >e.g. SELECT * FROM table where id=1234
> >I will see the record
> >
> >but
> >Using the same query on Enterprise Manager
> >the same query results to nothing.
> >
> >Also
> >SELECT COUNT(*) AS Expr1
> >FROM table
> >
> >Result:
> >vb6 query:1726 records
> >Ent. Manager view: 18 records
> >
> >My questions is why Ent. Manager cannot see all the records
> >
> >I am using SQL Server 2000 standard edition
> >The database itselt is nearly 21gb.
> >
> >Any help or advice appreciated
> >
> >Regards,
> >Allan
>

Friday, March 9, 2012

help--backup tables using BCP

I need to backup data (which satisfies a condition) from tables to flat files.....it worked fine using bcp...now i need to delete the records from tables which have been backed up......is ther any way out?

i wrote that bcp commands in a batch file to automate it.....

Using SSIS, you can write a package to export the table to a text file using a dataflow, and use an Execute SQL task to delete the rows afterward.

If you don't want to use SSIS, you might want to post to the T-SQL or Tools forum.

help--backup tables using BCP

i wrote that bcp commands in a batch file to automate it.....

Using SSIS, you can write a package to export the table to a text file using a dataflow, and use an Execute SQL task to delete the rows afterward.

If you don't want to use SSIS, you might want to post to the T-SQL or Tools forum.

Monday, February 27, 2012

Help: Query Timeout Expired

Hi,
I have a long running view (counting over 2,000,000 records) that regurns
"[ODBC SQL Server Driver] Timeout Expired" error after about 30 seconds.
Can somebody tell me how to increase the timeout period for running View?
Thanks in advance.
Regards
YeeYee,
were are you running the view from , EM? If you are don't. Try running it
from QA.
Timeouts are a client connection issue and can be adjusted via your ODBC
connection details, they are not a server problem.
I hope this helps
regards
Greg O MCSD
http://www.ag-software.com/ags_scribe_index.aspx. SQL Scribe Documentation
Builder, the quickest way to document your database
http://www.ag-software.com/ags_SSEPE_index.aspx. AGS SQL Server Extended
Property Extended properties manager for SQL 2000
http://www.ag-software.com/IconExtractionProgram.aspx. Free icon extraction
program
http://www.ag-software.com. Free programming tools
"Yee CN" <nospamxx_cn.yee@.attic-solutions.com> wrote in message
news:eZsXYag6DHA.2656@.TK2MSFTNGP11.phx.gbl...
> Hi,
> I have a long running view (counting over 2,000,000 records) that regurns
> "[ODBC SQL Server Driver] Timeout Expired" error after about 30 seconds.
> Can somebody tell me how to increase the timeout period for running View?
> Thanks in advance.
> Regards
> Yee
>|||Hi Yee:
The timeout may haeepn in several place. You can change it by T-SQL for
database or in the ADO for for ODBC.
Because you do not let us know what kind of operation you are doing.
What I can do is give some suggestion.
For database, you can type "timeout" in book online then you can get
information of how to set the timeout property in diffirent situation
For ODBC you can see this link and see the timeout part.
ms-help://MS.VSCC.2003/MS.MSDNQTR.2003JUL.1033/dnodbc/html/odbcsql.htm
Best Wishes
Wei Ci Zhou|||I was running it in EM. Just tried with QA, still the same problem.
I was managed to get it to run once earlier on though. It seems that the
timing the first row got returned is important here.
Regards,
Yee
"Greg Obleshchuk" <greg-n-o-s-p-a-m-@.ag-s-o-f-t-w-a-r-e.com> wrote in
message news:eGGXBsg6DHA.2056@.TK2MSFTNGP10.phx.gbl...
> Yee,
> were are you running the view from , EM? If you are don't. Try running
it
> from QA.
> Timeouts are a client connection issue and can be adjusted via your ODBC
> connection details, they are not a server problem.
>
> --
> I hope this helps
> regards
> Greg O MCSD
> http://www.ag-software.com/ags_scribe_index.aspx. SQL Scribe
Documentation
> Builder, the quickest way to document your database
> http://www.ag-software.com/ags_SSEPE_index.aspx. AGS SQL Server Extended
> Property Extended properties manager for SQL 2000
> http://www.ag-software.com/IconExtractionProgram.aspx. Free icon
extraction
> program
> http://www.ag-software.com. Free programming tools
>
> "Yee CN" <nospamxx_cn.yee@.attic-solutions.com> wrote in message
> news:eZsXYag6DHA.2656@.TK2MSFTNGP11.phx.gbl...
> > Hi,
> >
> > I have a long running view (counting over 2,000,000 records) that
regurns
> > "[ODBC SQL Server Driver] Timeout Expired" error after about 30 seconds.
> >
> > Can somebody tell me how to increase the timeout period for running
View?
> >
> > Thanks in advance.
> >
> > Regards
> > Yee
> >
> >
>|||I was opening the view from Enterprise Manager, then from Query Analyser.
Both gives the same error. I tried various things suggested in the help
file, including using sp_configure.
I also found the following from book online. The error message is what I
got - but I could not find the Tool->option screen mentioned below. Can you
shed any light?
----
--
ODBC error text
[Microsoft][ODBC SQL Server Driver]Timeout expired.
Explanation
The timeout can occur when you're updating the database with any
Transact-SQL changes.
Action
a.. Try again later to save the diagram or selected tables.
b.. Save a change script and apply it to the database at a later time.
c.. Increase the SQL Query Time-out value and try to save the diagram or
selected tables again.
To increase the SQL Query Time-out value
1.. From the Tools menu, choose Options.
2.. In the left pane, click Data Tools, and then click Data View.
3.. Type a new value in the SQL query time-out box.
----
--
Also the link that you suggested:
ms-help://MS.VSCC.2003/MS.MSDNQTR.2003JUL.1033/dnodbc/html/odbcsql.htm
- can you tell me how to get there?
Thanks a lot for your help.
Best regards,
Yee
"Wei Ci Zhou" <weicizhou@.hotmail.com.discuss> wrote in message
news:eBoSXfh6DHA.3008@.TK2MSFTNGP09.phx.gbl...
> Hi Yee:
> The timeout may haeepn in several place. You can change it by T-SQL
for
> database or in the ADO for for ODBC.
> Because you do not let us know what kind of operation you are doing.
> What I can do is give some suggestion.
> For database, you can type "timeout" in book online then you can get
> information of how to set the timeout property in diffirent situation
> For ODBC you can see this link and see the timeout part.
> Best Wishes
> Wei Ci Zhou
>

Help: Query Timeout Expired

quote:

> Hi,
> I have a long running view (counting over 2,000,000 records) that regurns
> "[ODBC SQL Server Driver] Timeout Expired" error after about 30 seconds.
> Can somebody tell me how to increase the timeout period for running View?
> Thanks in advance.
> Regards
> Yee
>

|||Hi Yee:
The timeout may haeepn in several place. You can change it by T-SQL for
database or in the ADO for for ODBC.
Because you do not let us know what kind of operation you are doing.
What I can do is give some suggestion.
For database, you can type "timeout" in book online then you can get
information of how to set the timeout property in diffirent situation
For ODBC you can see this link and see the timeout part.
ms-help://MS.VSCC.2003/MS.MSDNQTR.2003JUL.1033/dnodbc/html/odbcsql.htm
Best Wishes
Wei Ci Zhou|||I was running it in EM. Just tried with QA, still the same problem.
I was managed to get it to run once earlier on though. It seems that the
timing the first row got returned is important here.
Regards,
Yee
"Greg Obleshchuk" <greg-n-o-s-p-a-m-@.ag-s-o-f-t-w-a-r-e.com> wrote in
message news:eGGXBsg6DHA.2056@.TK2MSFTNGP10.phx.gbl...

quote:

> Yee,
> were are you running the view from , EM? If you are don't. Try running

quote:

> from QA.
> Timeouts are a client connection issue and can be adjusted via your ODBC
> connection details, they are not a server problem.
>
> --
> I hope this helps
> regards
> Greg O MCSD
> http://www.ag-software.com/ags_scribe_index.aspx. SQL Scribe

Documentation

quote:

> Builder, the quickest way to document your database
> http://www.ag-software.com/ags_SSEPE_index.aspx. AGS SQL Server Extended
> Property Extended properties manager for SQL 2000
> http://www.ag-software.com/IconExtractionProgram.aspx. Free icon

extraction

quote:

> program
> http://www.ag-software.com. Free programming tools
>
> "Yee CN" <nospamxx_cn.yee@.attic-solutions.com> wrote in message
> news:eZsXYag6DHA.2656@.TK2MSFTNGP11.phx.gbl...
regurns[QUOTE]
View?[QUOTE]
>

|||I was opening the view from Enterprise Manager, then from Query Analyser.
Both gives the same error. I tried various things suggested in the help
file, including using sp_configure.
I also found the following from book online. The error message is what I
got - but I could not find the Tool->option screen mentioned below. Can you
shed any light?
----
--
ODBC error text
[Microsoft][ODBC SQL Server Driver]Timeout expired.
Explanation
The timeout can occur when you're updating the database with any
Transact-SQL changes.
Action
a.. Try again later to save the diagram or selected tables.
b.. Save a change script and apply it to the database at a later time.
c.. Increase the SQL Query Time-out value and try to save the diagram or
selected tables again.
To increase the SQL Query Time-out value
1.. From the Tools menu, choose Options.
2.. In the left pane, click Data Tools, and then click Data View.
3.. Type a new value in the SQL query time-out box.
----
--
Also the link that you suggested:
ms-help://MS.VSCC.2003/MS.MSDNQTR.2003JUL.1033/dnodbc/html/odbcsql.htm
- can you tell me how to get there?
Thanks a lot for your help.
Best regards,
Yee
"Wei Ci Zhou" <weicizhou@.hotmail.com.discuss> wrote in message
news:eBoSXfh6DHA.3008@.TK2MSFTNGP09.phx.gbl...

quote:

> Hi Yee:
> The timeout may haeepn in several place. You can change it by T-SQL

for

quote:

> database or in the ADO for for ODBC.
> Because you do not let us know what kind of operation you are doing.
> What I can do is give some suggestion.
> For database, you can type "timeout" in book online then you can get
> information of how to set the timeout property in diffirent situation
> For ODBC you can see this link and see the timeout part.
> Best Wishes
> Wei Ci Zhou
>

Help: Purge Table Stored Procedure

I have to create a stored procedure to purge "x" # of records from a
table. I have two tables (script below):

Schedule
ScheduleHistory

I need to purge records out of ScheduleHistory. The problem is that
the # of records that needs to be "kept" is dynamic, and stored in the
Schedule table. So all records in ScheduleHistory should be purged,
except for the most recent "x" number of records.

For instance, for each Schedule row, you can specify how many records
to "keep" at all times. I want to keep the most recent "x" # of
ScheduleHistory rows for each Schedule.

In the script below, I have two schedules ("test1" and "test2"). One
of them specifies that the most recent "5" records in ScheduleHistory
should be kept, and the other specifies that the most recent "3"
records in ScheduleHistory should be kept.

So, I need a stored procedure that can clean up this table on demand.
I can't figure this one out...it's over my head.

Dan

------
SCRIPT BELOW
------

CREATE TABLE [dbo].[Schedule] (
[ScheduleID] [int] IDENTITY (1, 1) NOT NULL ,
[TypeFullName] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS
NOT NULL ,
[TimeLapse] [int] NOT NULL ,
[TimeLapseMeasurement] [varchar] (2) COLLATE
SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[RetryTimeLapse] [int] NOT NULL ,
[RetryTimeLapseMeasurement] [varchar] (2) COLLATE
SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[RetainHistoryNum] [int] NOT NULL ,
[ObjectDependencies] [varchar] (300) COLLATE
SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[ScheduleHistory] (
[ScheduleHistoryID] [int] IDENTITY (1, 1) NOT NULL ,
[ScheduleID] [int] NOT NULL ,
[StartDate] [datetime] NOT NULL ,
[EndDate] [datetime] NULL ,
[Succeeded] [bit] NULL ,
[LogNotes] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[NextStart] [datetime] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

INSERT INTO Schedule VALUES ('Test1', 1, 'd', 1, 'm', 5, '')
INSERT INTO Schedule VALUES ('Test2', 1, 'd', 1, 'm', 3, '')
INSERT INTO ScheduleHistory VALUES (1,'1/1/2000 1:00 PM', '1/1/2000
1:01 PM', 1, '', '1/2/2000 1:00 PM')
INSERT INTO ScheduleHistory VALUES (2,'1/1/2000 1:00 PM', '1/1/2000
1:01 PM', 1, '', '1/2/2000 1:00 PM')
INSERT INTO ScheduleHistory VALUES (1,'1/2/2000 1:00 PM', '1/2/2000
1:01 PM', 1, '', '1/3/2000 1:00 PM')
INSERT INTO ScheduleHistory VALUES (2,'1/2/2000 1:00 PM', '1/2/2000
1:01 PM', 1, '', '1/3/2000 1:00 PM')
INSERT INTO ScheduleHistory VALUES (1,'1/3/2000 1:00 PM', '1/3/2000
1:01 PM', 1, '', '1/4/2000 1:00 PM')
INSERT INTO ScheduleHistory VALUES (2,'1/3/2000 1:00 PM', '1/3/2000
1:01 PM', 1, '', '1/4/2000 1:00 PM')
INSERT INTO ScheduleHistory VALUES (1,'1/4/2000 1:00 PM', '1/4/2000
1:01 PM', 1, '', '1/5/2000 1:00 PM')
INSERT INTO ScheduleHistory VALUES (2,'1/4/2000 1:00 PM', '1/4/2000
1:01 PM', 1, '', '1/5/2000 1:00 PM')
INSERT INTO ScheduleHistory VALUES (1,'1/5/2000 1:00 PM', '1/5/2000
1:01 PM', 1, '', '1/6/2000 1:00 PM')
INSERT INTO ScheduleHistory VALUES (2,'1/5/2000 1:00 PM', '1/5/2000
1:01 PM', 1, '', '1/6/2000 1:00 PM')
INSERT INTO ScheduleHistory VALUES (1,'1/6/2000 1:00 PM', '1/6/2000
1:01 PM', 1, '', '1/7/2000 1:00 PM')
INSERT INTO ScheduleHistory VALUES (2,'1/6/2000 1:00 PM', '1/6/2000
1:01 PM', 1, '', '1/7/2000 1:00 PM')
INSERT INTO ScheduleHistory VALUES (1,'1/7/2000 1:00 PM', '1/7/2000
1:01 PM', 1, '', '1/8/2000 1:00 PM')
INSERT INTO ScheduleHistory VALUES (2,'1/7/2000 1:00 PM', '1/7/2000
1:01 PM', 1, '', '1/8/2000 1:00 PM')Assuming you want the keep the most recent rows based on StartDate, one
method:

DELETE FROM ScheduleHistory
FROM Schedule s
WHERE
(
SELECT COUNT(*)
FROM ScheduleHistory sh
WHERE
sh.ScheduleID = ScheduleHistory.ScheduleID AND
sh.ScheduleID = s.ScheduleID AND
sh.StartDate >= ScheduleHistory.StartDate
) > s.RetainHistoryNum

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Dan Caron" <dancaron-listserv0687@.mailblocks.com> wrote in message
news:4d6e6a6c.0404231047.60cd40d0@.posting.google.c om...
> I have to create a stored procedure to purge "x" # of records from a
> table. I have two tables (script below):
> Schedule
> ScheduleHistory
> I need to purge records out of ScheduleHistory. The problem is that
> the # of records that needs to be "kept" is dynamic, and stored in the
> Schedule table. So all records in ScheduleHistory should be purged,
> except for the most recent "x" number of records.
> For instance, for each Schedule row, you can specify how many records
> to "keep" at all times. I want to keep the most recent "x" # of
> ScheduleHistory rows for each Schedule.
> In the script below, I have two schedules ("test1" and "test2"). One
> of them specifies that the most recent "5" records in ScheduleHistory
> should be kept, and the other specifies that the most recent "3"
> records in ScheduleHistory should be kept.
> So, I need a stored procedure that can clean up this table on demand.
> I can't figure this one out...it's over my head.
> Dan
> ------
> SCRIPT BELOW
> ------
>
> CREATE TABLE [dbo].[Schedule] (
> [ScheduleID] [int] IDENTITY (1, 1) NOT NULL ,
> [TypeFullName] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS
> NOT NULL ,
> [TimeLapse] [int] NOT NULL ,
> [TimeLapseMeasurement] [varchar] (2) COLLATE
> SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [RetryTimeLapse] [int] NOT NULL ,
> [RetryTimeLapseMeasurement] [varchar] (2) COLLATE
> SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [RetainHistoryNum] [int] NOT NULL ,
> [ObjectDependencies] [varchar] (300) COLLATE
> SQL_Latin1_General_CP1_CI_AS NOT NULL
> ) ON [PRIMARY]
> GO
> CREATE TABLE [dbo].[ScheduleHistory] (
> [ScheduleHistoryID] [int] IDENTITY (1, 1) NOT NULL ,
> [ScheduleID] [int] NOT NULL ,
> [StartDate] [datetime] NOT NULL ,
> [EndDate] [datetime] NULL ,
> [Succeeded] [bit] NULL ,
> [LogNotes] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [NextStart] [datetime] NULL
> ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
> GO
> INSERT INTO Schedule VALUES ('Test1', 1, 'd', 1, 'm', 5, '')
> INSERT INTO Schedule VALUES ('Test2', 1, 'd', 1, 'm', 3, '')
> INSERT INTO ScheduleHistory VALUES (1,'1/1/2000 1:00 PM', '1/1/2000
> 1:01 PM', 1, '', '1/2/2000 1:00 PM')
> INSERT INTO ScheduleHistory VALUES (2,'1/1/2000 1:00 PM', '1/1/2000
> 1:01 PM', 1, '', '1/2/2000 1:00 PM')
> INSERT INTO ScheduleHistory VALUES (1,'1/2/2000 1:00 PM', '1/2/2000
> 1:01 PM', 1, '', '1/3/2000 1:00 PM')
> INSERT INTO ScheduleHistory VALUES (2,'1/2/2000 1:00 PM', '1/2/2000
> 1:01 PM', 1, '', '1/3/2000 1:00 PM')
> INSERT INTO ScheduleHistory VALUES (1,'1/3/2000 1:00 PM', '1/3/2000
> 1:01 PM', 1, '', '1/4/2000 1:00 PM')
> INSERT INTO ScheduleHistory VALUES (2,'1/3/2000 1:00 PM', '1/3/2000
> 1:01 PM', 1, '', '1/4/2000 1:00 PM')
> INSERT INTO ScheduleHistory VALUES (1,'1/4/2000 1:00 PM', '1/4/2000
> 1:01 PM', 1, '', '1/5/2000 1:00 PM')
> INSERT INTO ScheduleHistory VALUES (2,'1/4/2000 1:00 PM', '1/4/2000
> 1:01 PM', 1, '', '1/5/2000 1:00 PM')
> INSERT INTO ScheduleHistory VALUES (1,'1/5/2000 1:00 PM', '1/5/2000
> 1:01 PM', 1, '', '1/6/2000 1:00 PM')
> INSERT INTO ScheduleHistory VALUES (2,'1/5/2000 1:00 PM', '1/5/2000
> 1:01 PM', 1, '', '1/6/2000 1:00 PM')
> INSERT INTO ScheduleHistory VALUES (1,'1/6/2000 1:00 PM', '1/6/2000
> 1:01 PM', 1, '', '1/7/2000 1:00 PM')
> INSERT INTO ScheduleHistory VALUES (2,'1/6/2000 1:00 PM', '1/6/2000
> 1:01 PM', 1, '', '1/7/2000 1:00 PM')
> INSERT INTO ScheduleHistory VALUES (1,'1/7/2000 1:00 PM', '1/7/2000
> 1:01 PM', 1, '', '1/8/2000 1:00 PM')
> INSERT INTO ScheduleHistory VALUES (2,'1/7/2000 1:00 PM', '1/7/2000
> 1:01 PM', 1, '', '1/8/2000 1:00 PM')|||That is exactly what I needed. Thank you very much Dan.

-Dan

"Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message news:<MLvic.10091$e4.6777@.newsread2.news.pas.earthlink.n et>...
> Assuming you want the keep the most recent rows based on StartDate, one
> method:
> DELETE FROM ScheduleHistory
> FROM Schedule s
> WHERE
> (
> SELECT COUNT(*)
> FROM ScheduleHistory sh
> WHERE
> sh.ScheduleID = ScheduleHistory.ScheduleID AND
> sh.ScheduleID = s.ScheduleID AND
> sh.StartDate >= ScheduleHistory.StartDate
> ) > s.RetainHistoryNum
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Dan Caron" <dancaron-listserv0687@.mailblocks.com> wrote in message
> news:4d6e6a6c.0404231047.60cd40d0@.posting.google.c om...
> > I have to create a stored procedure to purge "x" # of records from a
> > table. I have two tables (script below):
> > Schedule
> > ScheduleHistory
> > I need to purge records out of ScheduleHistory. The problem is that
> > the # of records that needs to be "kept" is dynamic, and stored in the
> > Schedule table. So all records in ScheduleHistory should be purged,
> > except for the most recent "x" number of records.
> > For instance, for each Schedule row, you can specify how many records
> > to "keep" at all times. I want to keep the most recent "x" # of
> > ScheduleHistory rows for each Schedule.
> > In the script below, I have two schedules ("test1" and "test2"). One
> > of them specifies that the most recent "5" records in ScheduleHistory
> > should be kept, and the other specifies that the most recent "3"
> > records in ScheduleHistory should be kept.
> > So, I need a stored procedure that can clean up this table on demand.
> > I can't figure this one out...it's over my head.
> > Dan
> > ------
> > SCRIPT BELOW
> > ------
> > CREATE TABLE [dbo].[Schedule] (
> > [ScheduleID] [int] IDENTITY (1, 1) NOT NULL ,
> > [TypeFullName] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS
> > NOT NULL ,
> > [TimeLapse] [int] NOT NULL ,
> > [TimeLapseMeasurement] [varchar] (2) COLLATE
> > SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> > [RetryTimeLapse] [int] NOT NULL ,
> > [RetryTimeLapseMeasurement] [varchar] (2) COLLATE
> > SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> > [RetainHistoryNum] [int] NOT NULL ,
> > [ObjectDependencies] [varchar] (300) COLLATE
> > SQL_Latin1_General_CP1_CI_AS NOT NULL
> > ) ON [PRIMARY]
> > GO
> > CREATE TABLE [dbo].[ScheduleHistory] (
> > [ScheduleHistoryID] [int] IDENTITY (1, 1) NOT NULL ,
> > [ScheduleID] [int] NOT NULL ,
> > [StartDate] [datetime] NOT NULL ,
> > [EndDate] [datetime] NULL ,
> > [Succeeded] [bit] NULL ,
> > [LogNotes] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > [NextStart] [datetime] NULL
> > ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
> > GO
> > INSERT INTO Schedule VALUES ('Test1', 1, 'd', 1, 'm', 5, '')
> > INSERT INTO Schedule VALUES ('Test2', 1, 'd', 1, 'm', 3, '')
> > INSERT INTO ScheduleHistory VALUES (1,'1/1/2000 1:00 PM', '1/1/2000
> > 1:01 PM', 1, '', '1/2/2000 1:00 PM')
> > INSERT INTO ScheduleHistory VALUES (2,'1/1/2000 1:00 PM', '1/1/2000
> > 1:01 PM', 1, '', '1/2/2000 1:00 PM')
> > INSERT INTO ScheduleHistory VALUES (1,'1/2/2000 1:00 PM', '1/2/2000
> > 1:01 PM', 1, '', '1/3/2000 1:00 PM')
> > INSERT INTO ScheduleHistory VALUES (2,'1/2/2000 1:00 PM', '1/2/2000
> > 1:01 PM', 1, '', '1/3/2000 1:00 PM')
> > INSERT INTO ScheduleHistory VALUES (1,'1/3/2000 1:00 PM', '1/3/2000
> > 1:01 PM', 1, '', '1/4/2000 1:00 PM')
> > INSERT INTO ScheduleHistory VALUES (2,'1/3/2000 1:00 PM', '1/3/2000
> > 1:01 PM', 1, '', '1/4/2000 1:00 PM')
> > INSERT INTO ScheduleHistory VALUES (1,'1/4/2000 1:00 PM', '1/4/2000
> > 1:01 PM', 1, '', '1/5/2000 1:00 PM')
> > INSERT INTO ScheduleHistory VALUES (2,'1/4/2000 1:00 PM', '1/4/2000
> > 1:01 PM', 1, '', '1/5/2000 1:00 PM')
> > INSERT INTO ScheduleHistory VALUES (1,'1/5/2000 1:00 PM', '1/5/2000
> > 1:01 PM', 1, '', '1/6/2000 1:00 PM')
> > INSERT INTO ScheduleHistory VALUES (2,'1/5/2000 1:00 PM', '1/5/2000
> > 1:01 PM', 1, '', '1/6/2000 1:00 PM')
> > INSERT INTO ScheduleHistory VALUES (1,'1/6/2000 1:00 PM', '1/6/2000
> > 1:01 PM', 1, '', '1/7/2000 1:00 PM')
> > INSERT INTO ScheduleHistory VALUES (2,'1/6/2000 1:00 PM', '1/6/2000
> > 1:01 PM', 1, '', '1/7/2000 1:00 PM')
> > INSERT INTO ScheduleHistory VALUES (1,'1/7/2000 1:00 PM', '1/7/2000
> > 1:01 PM', 1, '', '1/8/2000 1:00 PM')
> > INSERT INTO ScheduleHistory VALUES (2,'1/7/2000 1:00 PM', '1/7/2000
> > 1:01 PM', 1, '', '1/8/2000 1:00 PM')|||That is exactly what I needed. Thank you very much Dan.

-Dan

Help: Purge Table Stored Procedure

I have to create a stored procedure to purge "x" # of records from a
table. I have two tables (script below):

Schedule
ScheduleHistory

For instance, for each Schedule row, you can specify how many records
to "keep" at all times. I want to keep the most recent "x" # of
ScheduleHistory rows for each Schedule.

So, I need a stored procedure that can clean up this table on demand.
I can't figure this one out...it's over my head.

Dan

------
SCRIPT BELOW
------

--
Hope this helps.

Dan Guzman
SQL Server MVP

Friday, February 24, 2012

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

-- 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

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

From

#itemdata as Main

Sunday, February 19, 2012

Help: Find the different records from 3 tables

I have 3 tables a , ab and b. table ab is the maping table between a
and b. table a contains multiple atid for aid. Same as table b. Now I
want find those aid and bid that contains different atid or btid.
please see the following scripts:
create table a
(aid int, atid int)
create table b
(bid int, btid int)
create table ab
(aid int, bid int)
insert into ab
values(1,1)
insert into ab
values(2,2)
insert into ab
values(3,3)
insert into ab
values(4,4)
insert into a
values(1,1)
insert into a
values(1,2)
insert into a
values(2,5)
insert into a
values(3,3)
insert into a
values(3,4)
insert into a
values(4,7)
insert into b
values(1,1)
insert into b
values(1,2)
insert into b
values(2,5)
insert into b
values(3,3)
insert into b
values(3,7)
insert into b
values(4,6)
I want get 3 and 4 .
because aid 1 contains (1,2) which is same as bid 1
aid 2 contain (5) which is same as bid 2
aid 3 contains (3,4) which is DIFF with bid 3 which contains(3,7)
aid 4 contains (7) which is DIFF with bid 4 which contains(6)
but if I execute the following I get 4 only, because one record (3, 3)
is satify the where condition.
select * from
ab
where ab.aid not in(
select a.aid from
a inner join ab on a.aid = ab.aid
inner join b on ab.bid = b.bid
where a.atid = b.btid
)
help pleaseselect distinct aid from(
select aid, count(*) cnt from(
select aid, atid from a
union all
select aid, btid atid from ab join b on ab.bid=b.bid
) t
group by aid, atid
having count(*)=1
) t|||select distinct ab.aid
from ab join a on ab.aid=a.aid
full outer join b on ab.bid=b.bid and a.atid=b.btid
where (a.aid is null or b.bid is null)
and ab.aid is not null|||Excellent, Thank you!!

Monday, March 26, 2012

Hidden records?

Friday, March 9, 2012

help--backup tables using BCP

help--backup tables using BCP

Monday, February 27, 2012

Help: Query Timeout Expired

Help: Query Timeout Expired

Help: Purge Table Stored Procedure

Help: Purge Table Stored Procedure

Friday, February 24, 2012

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

Sunday, February 19, 2012

Help: Find the different records from 3 tables

Help Backup

Blog Archive

About Me