CLR Deployment

I'm attempting to deploy CLR Stored Procedures from Visual Studio 2005.

I have a VS Database project with stored procedures for all of the tables in our database. (I would really like to keep everything together in 1 DLL), everything seems fine except...

I keep receiving the following error when attempting to deploy to SQL Server:

Error 1 Timeout expired. The timeout period elapsed prior to completion of
the operation or the server is not responding.

After some experimentation I have determined that its because the DLL is too
large: 1.8 mb. (I also have a larger one).

I have looked through various documentation sources but have found none
concerning this problem other than specifing something aboug a 100ms
execution timeout (not sure if this is the same).

Deployment works if I trim down the size of the DLL (i.e., remove procedures from the solution)

Is there anyway to deploy large DLLs to SQL Server without having to break
them up? If so, where is the setting to allow a longer timeout period for
deployment?

You could create script manualy. Just use CREATE ASSEMBLY/CREATE PROCEDURE and other statements.|||

Thanks for the reply.

Yes I know I could do that, but it is so much easier to use VS.

Also the major time consuming problem is the number of tables and procedures.

Heres the scenario.

We have 2 databases, 1 flat, 1 relational. (Same problem is occuring on both).

The flat database has been imported from FoxPro 2.6 DBF's for conversion into the relational to supply historic data to an application upgrade. Needless to say there are a large number of tables. I have utilized ApexSQL to generate CLR Stored Procedues for each table. (i.e., INSERT,UPDATE,DELETE,SELECT, and so on...)

VS Compiles the source with no errors, it is just during deployment to SQL Server. As a work around for now I have split the .CS files into somewhat logical groups and created a seperate VS solution for each, all of these deploy with no problems.

For documentation and maintenance I would rather keep everything together in one VS Solution for each Database.

Again thanks for your reply.

Glenn

CLR Deployment

I'm attempting to deploy CLR Stored Procedures from Visual Studio 2005.

I have a VS Database project with stored procedures for all of the tables in our database. (I would really like to keep everything together in 1 DLL), everything seems fine except...

I keep receiving the following error when attempting to deploy to SQL Server:

Error 1 Timeout expired. The timeout period elapsed prior to completion of
the operation or the server is not responding.

After some experimentation I have determined that its because the DLL is too
large: 1.8 mb. (I also have a larger one).

I have looked through various documentation sources but have found none
concerning this problem other than specifing something aboug a 100ms
execution timeout (not sure if this is the same).

Deployment works if I trim down the size of the DLL (i.e., remove procedures from the solution)

Is there anyway to deploy large DLLs to SQL Server without having to break
them up? If so, where is the setting to allow a longer timeout period for
deployment?

You could create script manualy. Just use CREATE ASSEMBLY/CREATE PROCEDURE and other statements.|||

Thanks for the reply.

Yes I know I could do that, but it is so much easier to use VS.

Also the major time consuming problem is the number of tables and procedures.

Heres the scenario.

We have 2 databases, 1 flat, 1 relational. (Same problem is occuring on both).

The flat database has been imported from FoxPro 2.6 DBF's for conversion into the relational to supply historic data to an application upgrade. Needless to say there are a large number of tables. I have utilized ApexSQL to generate CLR Stored Procedues for each table. (i.e., INSERT,UPDATE,DELETE,SELECT, and so on...)

VS Compiles the source with no errors, it is just during deployment to SQL Server. As a work around for now I have split the .CS files into somewhat logical groups and created a seperate VS solution for each, all of these deploy with no problems.

For documentation and maintenance I would rather keep everything together in one VS Solution for each Database.

Again thanks for your reply.

Glenn

Closest Time in SQL Table

Hi. I have 2 tables which have the following formats:
TABLE A
PLC_TIME TAGNAME STATUS LOCATION
-- -- -- --
TABLE B
PLC_TIME PCVUE_TIME TAGNAME STATUS LOCATION
-- -- --
-- --
Both tables will be inserted with records by a software continuously
with the PLC_TIME of Table B null, ie
PLC_TIME is not inserted with records. For eg,
TABLE_A
PLC_TIME TAGNAME STATUS LOCATION
-- -- -- --
07-05-2007 1:00:32pm TAG.A ON SITEA
07-05-2007 1:40:11pm TAG.A OFF SITEA
07-05-2007 1:10:31pm TAG.A ON SITEA
07-05-2007 2:10:11pm TAG.B NORMAL SITEB
TABLE_B
PLC_TIME PCVUE_TIME TAGNAME STATUS LOCATION
-- -- --
-- --
null 07-05-2007 1:00:39pmTAG.A ONSITEA
null 07-05-2007 1:41:22pmTAG.A OFF SITEA
null 07-05-2007 2:10:12pm TAG.B NORMAL SITEB
I'm required to update PLC_TIME of TABLE B with the PLC_TIME from
table A
which both of them have the same TAGNAME and STATUS and the PLC_TIME
from table A must be
the CLOSEST to the PCVUE_TIME in table b. PLC_TIME is also always
earlier than PCVUE_TIME.
I have used the following SQL command in my VB program to update the
CLOSEST PLC_TIME of TABLE_B:
UPDATE TABLE_B SET PLC_TIME = (SELECT MAX(PLC_TIME) FROM TABLE_A WHERE
TAGNAME = TABLE_B.TAGNAME AND STATUS = TABLE_B.STATUS
AND PLC_TIME<TABLE_B.PCVUE_TIME AND
ABS(DATEDIFF(MINUTE,PLC_TIME,PCVUE_TIME))<31)
(Note: 31 is just an estimation of the maximum difference of PLC_TIME
and PCVUE_TIME in normal case.)
Since both TABLE_A and TABLE_B are updated randomly and the timing may
not be in order, i.e. Some earlier records may be inserted into
TABLE_A and TABLE_B
by the software, my VB program needs to "update TABLE_B set PLC_TIME"
constantly until the CLOSEST PLC_TIME is updated.
What i can think of is DATEDIFF() but DATEDIFF has a limitation that
it only compares the specific MINUTE of
PCVUE_TIME and PLC_TIME if i use DATEDIFF(minute,PLC_TIME, PCVUE_TIME)
and this will have a problem for 1:59pm and 2:00pm in which 2:00pm
will be treated
as earlier than 1:59pm. Also i may need to have multiple DATEDIFF
which compares YEAR, MONTH, DAY, HOUR and so on... which is not
feasible.
What correct SQL command can i use to update TABLE_B until the CLOSEST
PLC_TIME is updated into TABLE_B?
You can try something like this:
UPDATE TABLE_B SET PLC_TIME = A.PLC_TIME
FROM TABLE_B B
INNER JOIN
(SELECT MAX(TABLE_A.PLC_TIME) PLC_TIME, TABLE_A.TAGNAME, TABLE_A.STATUS
FROM TABLE_A
INNER JOIN TABLE_B
ON TABLE_A.TAGNAME = TABLE_B.TAGNAME AND TABLE_A.STATUS = TABLE_B.STATUS
WHERE TABLE_A.PLC_TIME<TABLE_B.PCVUE_TIME
GROUP BY TABLE_A.TAGNAME, TABLE_A.STATUS) A
ON A.TAGNAME = B.TAGNAME AND A.STATUS = B.STATUS
"albertleng" <albertleng@.gmail.com> wrote in message
news:1180449709.930509.45380@.r19g2000prf.googlegro ups.com...
> Hi. I have 2 tables which have the following formats:
> TABLE A
> PLC_TIME TAGNAME STATUS LOCATION
> -- -- -- --
> TABLE B
> PLC_TIME PCVUE_TIME TAGNAME STATUS LOCATION
> -- -- --
> -- --
> Both tables will be inserted with records by a software continuously
> with the PLC_TIME of Table B null, ie
> PLC_TIME is not inserted with records. For eg,
> TABLE_A
> PLC_TIME TAGNAME STATUS LOCATION
> -- -- -- --
> 07-05-2007 1:00:32pm TAG.A ON SITEA
> 07-05-2007 1:40:11pm TAG.A OFF SITEA
> 07-05-2007 1:10:31pm TAG.A ON SITEA
> 07-05-2007 2:10:11pm TAG.B NORMAL SITEB
>
> TABLE_B
> PLC_TIME PCVUE_TIME TAGNAME STATUS LOCATION
> -- -- --
> -- --
> null 07-05-2007 1:00:39pm TAG.A ON SITEA
> null 07-05-2007 1:41:22pm TAG.A OFF SITEA
> null 07-05-2007 2:10:12pm TAG.B NORMAL SITEB
> I'm required to update PLC_TIME of TABLE B with the PLC_TIME from
> table A
> which both of them have the same TAGNAME and STATUS and the PLC_TIME
> from table A must be
> the CLOSEST to the PCVUE_TIME in table b. PLC_TIME is also always
> earlier than PCVUE_TIME.
> I have used the following SQL command in my VB program to update the
> CLOSEST PLC_TIME of TABLE_B:
> UPDATE TABLE_B SET PLC_TIME = (SELECT MAX(PLC_TIME) FROM TABLE_A WHERE
> TAGNAME = TABLE_B.TAGNAME AND STATUS = TABLE_B.STATUS
> AND PLC_TIME<TABLE_B.PCVUE_TIME AND
> ABS(DATEDIFF(MINUTE,PLC_TIME,PCVUE_TIME))<31)
> (Note: 31 is just an estimation of the maximum difference of PLC_TIME
> and PCVUE_TIME in normal case.)
> Since both TABLE_A and TABLE_B are updated randomly and the timing may
> not be in order, i.e. Some earlier records may be inserted into
> TABLE_A and TABLE_B
> by the software, my VB program needs to "update TABLE_B set PLC_TIME"
> constantly until the CLOSEST PLC_TIME is updated.
> What i can think of is DATEDIFF() but DATEDIFF has a limitation that
> it only compares the specific MINUTE of
> PCVUE_TIME and PLC_TIME if i use DATEDIFF(minute,PLC_TIME, PCVUE_TIME)
> and this will have a problem for 1:59pm and 2:00pm in which 2:00pm
> will be treated
> as earlier than 1:59pm. Also i may need to have multiple DATEDIFF
> which compares YEAR, MONTH, DAY, HOUR and so on... which is not
> feasible.
>
> What correct SQL command can i use to update TABLE_B until the CLOSEST
> PLC_TIME is updated into TABLE_B?
>
|||Hi
"Quentin Ran" wrote:

> You can try something like this:
> UPDATE TABLE_B SET PLC_TIME = A.PLC_TIME
> FROM TABLE_B B
> INNER JOIN
> (SELECT MAX(TABLE_A.PLC_TIME) PLC_TIME, TABLE_A.TAGNAME, TABLE_A.STATUS
> FROM TABLE_A
> INNER JOIN TABLE_B
> ON TABLE_A.TAGNAME = TABLE_B.TAGNAME AND TABLE_A.STATUS = TABLE_B.STATUS
> WHERE TABLE_A.PLC_TIME<TABLE_B.PCVUE_TIME
> GROUP BY TABLE_A.TAGNAME, TABLE_A.STATUS) A
> ON A.TAGNAME = B.TAGNAME AND A.STATUS = B.STATUS
>
You should also have
WHERE B.PLC_TIME IS NULL
to both the derived table and update statement to eliminate records already
updated
i.e.
UPDATE B
SET PLC_TIME = A.PLC_TIME
FROM TABLE_B B
JOIN (SELECT MAX(C.PLC_TIME) AS PLC_TIME, C.TAGNAME, C.STATUS
FROM TABLE_A C
JOIN TABLE_B D ON C.TAGNAME = D.TAGNAME AND C.STATUS = D.STATUS
AND C.PLC_TIME< D.PCVUE_TIME AND D.PLC_TIME IS NULL
GROUP BY C.TAGNAME, C.STATUS) A
ON A.TAGNAME = B.TAGNAME AND A.STATUS = B.STATUS
WHERE B.PLC_TIME IS NULL
John

Closest Time in SQL Table

Hi. I have 2 tables which have the following formats:
TABLE A
PLC_TIME TAGNAME STATUS LOCATION
-- -- -- --
TABLE B
PLC_TIME PCVUE_TIME TAGNAME STATUS LOCATION
-- -- --
-- --
Both tables will be inserted with records by a software continuously
with the PLC_TIME of Table B null, ie
PLC_TIME is not inserted with records. For eg,
TABLE_A
PLC_TIME TAGNAME STATUS LOCATION
-- -- -- --
07-05-2007 1:00:32pm TAG.A ON SITEA
07-05-2007 1:40:11pm TAG.A OFF SITEA
07-05-2007 1:10:31pm TAG.A ON SITEA
07-05-2007 2:10:11pm TAG.B NORMAL SITEB
TABLE_B
PLC_TIME PCVUE_TIME TAGNAME STATUS LOCATION
-- -- --
-- --
null 07-05-2007 1:00:39pm TAG.A ON SITEA
null 07-05-2007 1:41:22pm TAG.A OFF SITEA
null 07-05-2007 2:10:12pm TAG.B NORMAL SITEB
I'm required to update PLC_TIME of TABLE B with the PLC_TIME from
table A
which both of them have the same TAGNAME and STATUS and the PLC_TIME
from table A must be
the CLOSEST to the PCVUE_TIME in table b. PLC_TIME is also always
earlier than PCVUE_TIME.
I have used the following SQL command in my VB program to update the
CLOSEST PLC_TIME of TABLE_B:
UPDATE TABLE_B SET PLC_TIME = (SELECT MAX(PLC_TIME) FROM TABLE_A WHERE
TAGNAME = TABLE_B.TAGNAME AND STATUS = TABLE_B.STATUS
AND PLC_TIME<TABLE_B.PCVUE_TIME AND
ABS(DATEDIFF(MINUTE,PLC_TIME,PCVUE_TIME))<31)
(Note: 31 is just an estimation of the maximum difference of PLC_TIME
and PCVUE_TIME in normal case.)
Since both TABLE_A and TABLE_B are updated randomly and the timing may
not be in order, i.e. Some earlier records may be inserted into
TABLE_A and TABLE_B
by the software, my VB program needs to "update TABLE_B set PLC_TIME"
constantly until the CLOSEST PLC_TIME is updated.
What i can think of is DATEDIFF() but DATEDIFF has a limitation that
it only compares the specific MINUTE of
PCVUE_TIME and PLC_TIME if i use DATEDIFF(minute,PLC_TIME, PCVUE_TIME)
and this will have a problem for 1:59pm and 2:00pm in which 2:00pm
will be treated
as earlier than 1:59pm. Also i may need to have multiple DATEDIFF
which compares YEAR, MONTH, DAY, HOUR and so on... which is not
feasible.
What correct SQL command can i use to update TABLE_B until the CLOSEST
PLC_TIME is updated into TABLE_B?You can try something like this:
UPDATE TABLE_B SET PLC_TIME = A.PLC_TIME
FROM TABLE_B B
INNER JOIN
(SELECT MAX(TABLE_A.PLC_TIME) PLC_TIME, TABLE_A.TAGNAME, TABLE_A.STATUS
FROM TABLE_A
INNER JOIN TABLE_B
ON TABLE_A.TAGNAME = TABLE_B.TAGNAME AND TABLE_A.STATUS = TABLE_B.STATUS
WHERE TABLE_A.PLC_TIME<TABLE_B.PCVUE_TIME
GROUP BY TABLE_A.TAGNAME, TABLE_A.STATUS) A
ON A.TAGNAME = B.TAGNAME AND A.STATUS = B.STATUS
"albertleng" <albertleng@.gmail.com> wrote in message
news:1180449709.930509.45380@.r19g2000prf.googlegroups.com...
> Hi. I have 2 tables which have the following formats:
> TABLE A
> PLC_TIME TAGNAME STATUS LOCATION
> -- -- -- --
> TABLE B
> PLC_TIME PCVUE_TIME TAGNAME STATUS LOCATION
> -- -- --
> -- --
> Both tables will be inserted with records by a software continuously
> with the PLC_TIME of Table B null, ie
> PLC_TIME is not inserted with records. For eg,
> TABLE_A
> PLC_TIME TAGNAME STATUS LOCATION
> -- -- -- --
> 07-05-2007 1:00:32pm TAG.A ON SITEA
> 07-05-2007 1:40:11pm TAG.A OFF SITEA
> 07-05-2007 1:10:31pm TAG.A ON SITEA
> 07-05-2007 2:10:11pm TAG.B NORMAL SITEB
>
> TABLE_B
> PLC_TIME PCVUE_TIME TAGNAME STATUS LOCATION
> -- -- --
> -- --
> null 07-05-2007 1:00:39pm TAG.A ON SITEA
> null 07-05-2007 1:41:22pm TAG.A OFF SITEA
> null 07-05-2007 2:10:12pm TAG.B NORMAL SITEB
> I'm required to update PLC_TIME of TABLE B with the PLC_TIME from
> table A
> which both of them have the same TAGNAME and STATUS and the PLC_TIME
> from table A must be
> the CLOSEST to the PCVUE_TIME in table b. PLC_TIME is also always
> earlier than PCVUE_TIME.
> I have used the following SQL command in my VB program to update the
> CLOSEST PLC_TIME of TABLE_B:
> UPDATE TABLE_B SET PLC_TIME = (SELECT MAX(PLC_TIME) FROM TABLE_A WHERE
> TAGNAME = TABLE_B.TAGNAME AND STATUS = TABLE_B.STATUS
> AND PLC_TIME<TABLE_B.PCVUE_TIME AND
> ABS(DATEDIFF(MINUTE,PLC_TIME,PCVUE_TIME))<31)
> (Note: 31 is just an estimation of the maximum difference of PLC_TIME
> and PCVUE_TIME in normal case.)
> Since both TABLE_A and TABLE_B are updated randomly and the timing may
> not be in order, i.e. Some earlier records may be inserted into
> TABLE_A and TABLE_B
> by the software, my VB program needs to "update TABLE_B set PLC_TIME"
> constantly until the CLOSEST PLC_TIME is updated.
> What i can think of is DATEDIFF() but DATEDIFF has a limitation that
> it only compares the specific MINUTE of
> PCVUE_TIME and PLC_TIME if i use DATEDIFF(minute,PLC_TIME, PCVUE_TIME)
> and this will have a problem for 1:59pm and 2:00pm in which 2:00pm
> will be treated
> as earlier than 1:59pm. Also i may need to have multiple DATEDIFF
> which compares YEAR, MONTH, DAY, HOUR and so on... which is not
> feasible.
>
> What correct SQL command can i use to update TABLE_B until the CLOSEST
> PLC_TIME is updated into TABLE_B?
>|||Hi
"Quentin Ran" wrote:
> You can try something like this:
> UPDATE TABLE_B SET PLC_TIME = A.PLC_TIME
> FROM TABLE_B B
> INNER JOIN
> (SELECT MAX(TABLE_A.PLC_TIME) PLC_TIME, TABLE_A.TAGNAME, TABLE_A.STATUS
> FROM TABLE_A
> INNER JOIN TABLE_B
> ON TABLE_A.TAGNAME = TABLE_B.TAGNAME AND TABLE_A.STATUS = TABLE_B.STATUS
> WHERE TABLE_A.PLC_TIME<TABLE_B.PCVUE_TIME
> GROUP BY TABLE_A.TAGNAME, TABLE_A.STATUS) A
> ON A.TAGNAME = B.TAGNAME AND A.STATUS = B.STATUS
>
You should also have
WHERE B.PLC_TIME IS NULL
to both the derived table and update statement to eliminate records already
updated
i.e.
UPDATE B
SET PLC_TIME = A.PLC_TIME
FROM TABLE_B B
JOIN (SELECT MAX(C.PLC_TIME) AS PLC_TIME, C.TAGNAME, C.STATUS
FROM TABLE_A C
JOIN TABLE_B D ON C.TAGNAME = D.TAGNAME AND C.STATUS = D.STATUS
AND C.PLC_TIME< D.PCVUE_TIME AND D.PLC_TIME IS NULL
GROUP BY C.TAGNAME, C.STATUS) A
ON A.TAGNAME = B.TAGNAME AND A.STATUS = B.STATUS
WHERE B.PLC_TIME IS NULL
John

Closest Time in SQL Table

Hi. I have 2 tables which have the following formats:
TABLE A
PLC_TIME TAGNAME STATUS LOCATION
-- -- -- --
TABLE B
PLC_TIME PCVUE_TIME TAGNAME STATUS LOCATION
-- -- --
-- --
Both tables will be inserted with records by a software continuously
with the PLC_TIME of Table B null, ie
PLC_TIME is not inserted with records. For eg,
TABLE_A
PLC_TIME TAGNAME STATUS LOCATION
-- -- -- --
07-05-2007 1:00:32pm TAG.A ON SITEA
07-05-2007 1:40:11pm TAG.A OFF SITEA
07-05-2007 1:10:31pm TAG.A ON SITEA
07-05-2007 2:10:11pm TAG.B NORMAL SITEB
TABLE_B
PLC_TIME PCVUE_TIME TAGNAME STATUS LOCATION
-- -- --
-- --
null 07-05-2007 1:00:39pm TAG.A ON SITEA
null 07-05-2007 1:41:22pm TAG.A OFF SITEA
null 07-05-2007 2:10:12pm TAG.B NORMAL SITEB
I'm required to update PLC_TIME of TABLE B with the PLC_TIME from
table A
which both of them have the same TAGNAME and STATUS and the PLC_TIME
from table A must be
the CLOSEST to the PCVUE_TIME in table b. PLC_TIME is also always
earlier than PCVUE_TIME.
I have used the following SQL command in my VB program to update the
CLOSEST PLC_TIME of TABLE_B:
UPDATE TABLE_B SET PLC_TIME = (SELECT MAX(PLC_TIME) FROM TABLE_A WHERE
TAGNAME = TABLE_B.TAGNAME AND STATUS = TABLE_B.STATUS
AND PLC_TIME<TABLE_B.PCVUE_TIME AND
ABS(DATEDIFF(MINUTE,PLC_TIME,PCVUE_TIME)
)<31)
(Note: 31 is just an estimation of the maximum difference of PLC_TIME
and PCVUE_TIME in normal case.)
Since both TABLE_A and TABLE_B are updated randomly and the timing may
not be in order, i.e. Some earlier records may be inserted into
TABLE_A and TABLE_B
by the software, my VB program needs to "update TABLE_B set PLC_TIME"
constantly until the CLOSEST PLC_TIME is updated.
What i can think of is DATEDIFF() but DATEDIFF has a limitation that
it only compares the specific MINUTE of
PCVUE_TIME and PLC_TIME if i use DATEDIFF(minute,PLC_TIME, PCVUE_TIME)
and this will have a problem for 1:59pm and 2:00pm in which 2:00pm
will be treated
as earlier than 1:59pm. Also i may need to have multiple DATEDIFF
which compares YEAR, MONTH, DAY, HOUR and so on... which is not
feasible.
What correct SQL command can i use to update TABLE_B until the CLOSEST
PLC_TIME is updated into TABLE_B?You can try something like this:
UPDATE TABLE_B SET PLC_TIME = A.PLC_TIME
FROM TABLE_B B
INNER JOIN
(SELECT MAX(TABLE_A.PLC_TIME) PLC_TIME, TABLE_A.TAGNAME, TABLE_A.STATUS
FROM TABLE_A
INNER JOIN TABLE_B
ON TABLE_A.TAGNAME = TABLE_B.TAGNAME AND TABLE_A.STATUS = TABLE_B.STATUS
WHERE TABLE_A.PLC_TIME<TABLE_B.PCVUE_TIME
GROUP BY TABLE_A.TAGNAME, TABLE_A.STATUS) A
ON A.TAGNAME = B.TAGNAME AND A.STATUS = B.STATUS
"albertleng" <albertleng@.gmail.com> wrote in message
news:1180449709.930509.45380@.r19g2000prf.googlegroups.com...
> Hi. I have 2 tables which have the following formats:
> TABLE A
> PLC_TIME TAGNAME STATUS LOCATION
> -- -- -- --
> TABLE B
> PLC_TIME PCVUE_TIME TAGNAME STATUS LOCATION
> -- -- --
> -- --
> Both tables will be inserted with records by a software continuously
> with the PLC_TIME of Table B null, ie
> PLC_TIME is not inserted with records. For eg,
> TABLE_A
> PLC_TIME TAGNAME STATUS LOCATION
> -- -- -- --
> 07-05-2007 1:00:32pm TAG.A ON SITEA
> 07-05-2007 1:40:11pm TAG.A OFF SITEA
> 07-05-2007 1:10:31pm TAG.A ON SITEA
> 07-05-2007 2:10:11pm TAG.B NORMAL SITEB
>
> TABLE_B
> PLC_TIME PCVUE_TIME TAGNAME STATUS LOCATION
> -- -- --
> -- --
> null 07-05-2007 1:00:39pm TAG.A ON SITEA
> null 07-05-2007 1:41:22pm TAG.A OFF SITEA
> null 07-05-2007 2:10:12pm TAG.B NORMAL SITEB
> I'm required to update PLC_TIME of TABLE B with the PLC_TIME from
> table A
> which both of them have the same TAGNAME and STATUS and the PLC_TIME
> from table A must be
> the CLOSEST to the PCVUE_TIME in table b. PLC_TIME is also always
> earlier than PCVUE_TIME.
> I have used the following SQL command in my VB program to update the
> CLOSEST PLC_TIME of TABLE_B:
> UPDATE TABLE_B SET PLC_TIME = (SELECT MAX(PLC_TIME) FROM TABLE_A WHERE
> TAGNAME = TABLE_B.TAGNAME AND STATUS = TABLE_B.STATUS
> AND PLC_TIME<TABLE_B.PCVUE_TIME AND
> ABS(DATEDIFF(MINUTE,PLC_TIME,PCVUE_TIME)
)<31)
> (Note: 31 is just an estimation of the maximum difference of PLC_TIME
> and PCVUE_TIME in normal case.)
> Since both TABLE_A and TABLE_B are updated randomly and the timing may
> not be in order, i.e. Some earlier records may be inserted into
> TABLE_A and TABLE_B
> by the software, my VB program needs to "update TABLE_B set PLC_TIME"
> constantly until the CLOSEST PLC_TIME is updated.
> What i can think of is DATEDIFF() but DATEDIFF has a limitation that
> it only compares the specific MINUTE of
> PCVUE_TIME and PLC_TIME if i use DATEDIFF(minute,PLC_TIME, PCVUE_TIME)
> and this will have a problem for 1:59pm and 2:00pm in which 2:00pm
> will be treated
> as earlier than 1:59pm. Also i may need to have multiple DATEDIFF
> which compares YEAR, MONTH, DAY, HOUR and so on... which is not
> feasible.
>
> What correct SQL command can i use to update TABLE_B until the CLOSEST
> PLC_TIME is updated into TABLE_B?
>|||Hi
"Quentin Ran" wrote:

> You can try something like this:
> UPDATE TABLE_B SET PLC_TIME = A.PLC_TIME
> FROM TABLE_B B
> INNER JOIN
> (SELECT MAX(TABLE_A.PLC_TIME) PLC_TIME, TABLE_A.TAGNAME, TABLE_A.STATUS
> FROM TABLE_A
> INNER JOIN TABLE_B
> ON TABLE_A.TAGNAME = TABLE_B.TAGNAME AND TABLE_A.STATUS = TABLE_B.STATUS
> WHERE TABLE_A.PLC_TIME<TABLE_B.PCVUE_TIME
> GROUP BY TABLE_A.TAGNAME, TABLE_A.STATUS) A
> ON A.TAGNAME = B.TAGNAME AND A.STATUS = B.STATUS
>
You should also have
WHERE B.PLC_TIME IS NULL
to both the derived table and update statement to eliminate records already
updated
i.e.
UPDATE B
SET PLC_TIME = A.PLC_TIME
FROM TABLE_B B
JOIN (SELECT MAX(C.PLC_TIME) AS PLC_TIME, C.TAGNAME, C.STATUS
FROM TABLE_A C
JOIN TABLE_B D ON C.TAGNAME = D.TAGNAME AND C.STATUS = D.STATUS
AND C.PLC_TIME< D.PCVUE_TIME AND D.PLC_TIME IS NULL
GROUP BY C.TAGNAME, C.STATUS) A
ON A.TAGNAME = B.TAGNAME AND A.STATUS = B.STATUS
WHERE B.PLC_TIME IS NULL
John

clients sometimes get locked and can't write

I have an SQL 2000 back end. We have a couple of databases with about 12-20
tables a piece. About once or twice a month there are times for up to an
hour or so where no one can write from any of the front end applications to
the SQL 2000 back end. Each application gives their own error which all are
basically a time out waiting to write. Often times the system will just
start working again on its own.
When this happens the first thing we check in the Enterprise Manager is for
any locks and there are none. We then check to see if one of the other
applications like Access 2003, Labview or others can write to the database
and we see they cannot. Next we see if we can open the table on the SQL
server and edit or append and we CAN from the Enterprise Manager'
Thinking that there might be someone locking a record or a page we kick
everyone out and just let one person (sometimes myself as an admin) try to
work and they cannot. We then restart the SQL server and the workstations
and have someone try again and they still fail.
The only thing that I have seen help get it out of this state is when I go
to my daily maintenance jobs and run both my integrity check and my
optimization plan. The Integrety Check plan was done with the wizard and
it:
1. Checks the integrity of all user database
2. Includes indexes
3. Attempts to repair minor problems.
4. Run every day at 12:00 AM
The Optimization plan is set to:
1. Reorganize Data and Index Pages
2. Change free space to 10%
3. Run every day at 1:30 AM
Finally, when I look in the Event Viewer logs for anything strange I really
do not see anything in the app, security, or system logs anywhere near the
time of the start of the errors.Sounds like the SQL Server Agents for the SQL Server instances aren't
running sometimes. Goto start->administrative tools->services and
look for the SQL Server Agent (your server instance name) entries and
see their status. Should be in "Started" state. If not started,
click on the entry and click start in top left corner of pane.
SQL Server Agents are the programs that act as proxies to enable
remote access to a SQL server instance. If they are not running,
nobody will be able to gain access to the server, and will receive
time out errors instead.
Some programs, such as backups, may stop the agents so they can take a
snapshot of the database. These programs should start the agents as
soon as they are done. If you find the agents in a stopped state in
the next outage, you should try to trace down what program was
executing at the time they were stopped, and check if that program is
configured properly.|||I don't think it is the agent because the users are able to read the data
remotely. Also, the only maintenance that is done is done at 1-3 AM. This
problem happens randomly mid day.
"Andy" <anedza@.infotek-consulting.com> wrote in message
news:e1159a72-4a2a-449d-a8d7-d22be76d6595@.q78g2000hsh.googlegroups.com...
> Sounds like the SQL Server Agents for the SQL Server instances aren't
> running sometimes. Goto start->administrative tools->services and
> look for the SQL Server Agent (your server instance name) entries and
> see their status. Should be in "Started" state. If not started,
> click on the entry and click start in top left corner of pane.
> SQL Server Agents are the programs that act as proxies to enable
> remote access to a SQL server instance. If they are not running,
> nobody will be able to gain access to the server, and will receive
> time out errors instead.
> Some programs, such as backups, may stop the agents so they can take a
> snapshot of the database. These programs should start the agents as
> soon as they are done. If you find the agents in a stopped state in
> the next outage, you should try to trace down what program was
> executing at the time they were stopped, and check if that program is
> configured properly.
>
>
>

Client side text handling

I have a text/excel file on client machines. user ( at client side) will be uploading it to Oracle database tables on the server using a client side procedure. I have Oracle client 8.0 loaded on the user/client machines. How can this be achieved. Need something like utl_file. (I am not using oracle Forms or reports so I cannot use text_io)Will SQL Loader not do it? If necessary, you could load into a temporary table and then process in PL/SQL.|||No , It will not serve the purpose as I have to modify & add some contents of the file after some validation. Only then I'll be attaching & sending the same through my client email.

Clearing tables

Which is the more efficient way to clear the contents of a table:
DELETE FROM tableName
TRUNCATE TABLE tableName
Or DROP the table then create it again?
Chris Lieb
UPS CACH, Hodgekins, IL
Tech Support Group - Systems/Apps
Hi,
I will go with TRUNCATE TABLE <Table Name>
Thanks
Hari
SQL Server MVP
"Chris Lieb" <ChrisLieb@.discussions.microsoft.com> wrote in message
news:E6085077-8B80-4573-B40D-E9837967B463@.microsoft.com...
> Which is the more efficient way to clear the contents of a table:
> DELETE FROM tableName
> TRUNCATE TABLE tableName
> Or DROP the table then create it again?
> --
> Chris Lieb
> UPS CACH, Hodgekins, IL
> Tech Support Group - Systems/Apps

clearing log

As a relative newcomer to MSSQL, I've been playing around with a database,
adding and removing tables and tons of data. Now I'm ready to get down to
business.
I notice that the log (LDF file) is now three times the size of the database
itself. I'm pretty sure there's nothing in there that is of any use. Is
there a way to clear it? Is that desirable? Am I better off starting over
with a new database? This one does have some, not a lot, data in it that I'd
like to keep.Try in Query Analyser
BACKUP LOG WITH TRUNCATE_ONLY
Then
DBCC SHRINKFILE( look in BOL for more detail)
If you don't want to do this repeatedly, you can modify the Recovery
Model of the database !
Go to your Database Property, on the last tab, you'll se the recovery
model is at Full or Bulked-Logged, change it to Simple ! Your log will
stop accumulating !
You can also change the recovery model of Model database so the next
database you create will use simple recovery !
For more information on Recovery Model, search BOL
Pollus Brodeur|||If you don't do regular transaction log backups, set the database to simple
recovery mode. Shrinking
considerations: http://www.karaszi.com/SQLServer/info_dont_shrink.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Paul Pedersen" <no-reply@.swen.com> wrote in message news:eoMfbUaXFHA.616@.TK2MSFTNGP12.phx.
gbl...
> As a relative newcomer to MSSQL, I've been playing around with a database,
adding and removing
> tables and tons of data. Now I'm ready to get down to business.
> I notice that the log (LDF file) is now three times the size of the databa
se itself. I'm pretty
> sure there's nothing in there that is of any use. Is there a way to clear
it? Is that desirable?
> Am I better off starting over with a new database? This one does have some
, not a lot, data in it
> that I'd like to keep.
>
>
>
>|||Thanks to all.
"Paul Pedersen" <no-reply@.swen.com> wrote in message
news:eoMfbUaXFHA.616@.TK2MSFTNGP12.phx.gbl...
> As a relative newcomer to MSSQL, I've been playing around with a database,
> adding and removing tables and tons of data. Now I'm ready to get down to
> business.
> I notice that the log (LDF file) is now three times the size of the
> database itself. I'm pretty sure there's nothing in there that is of any
> use. Is there a way to clear it? Is that desirable? Am I better off
> starting over with a new database? This one does have some, not a lot,
> data in it that I'd like to keep.
>
>
>
>

Clearing all data from tables in a database

Hi,
I want to clear all data from 200 tables in a database from a sql query. Some of the tables have got relationship as well. I want to clear data from there as well.
What could be the best approach? Does anyone have an idea or script from where I could do the needful?
Do let me know.
Thanks

I'm not sure of this, but what i think you might be looking for is to truncate each table.
As for the references, i do not know what happens then but i think one can temporarily disable the automatic checks for the referential integrity.
It would seem logical to be able to do so...

|||

Script your tables with primary keys, foreign keys, triggers, etc and execute the script. That would be the fastest way to get things done.

Nick