moving a database causes it to be read-only

Using SS2000 SP4. I've done this many times before without problem so I don'
t
know why I'm having trouble this time. I'm running out of space on a drive s
o
I'm moving some databases to another drive. I've tried this with EM and QA
using this query:
EXEC sp_detach_db 'jg', 'false'
EXEC sp_attach_db @.dbname = 'jg',
@.filename1 = 'S:\SQL\Data\jg_Data.mdf',
@.filename2 = 'E:\SQL\LOGS\jg_log.ldf'
After attaching the database, the database comes up read-only. Can someone
tell me why?
Thanks,
--
Dan D.Hi Dan
"Dan D." wrote:

> Using SS2000 SP4. I've done this many times before without problem so I do
n't
> know why I'm having trouble this time. I'm running out of space on a drive
so
> I'm moving some databases to another drive. I've tried this with EM and QA
> using this query:
> EXEC sp_detach_db 'jg', 'false'
> EXEC sp_attach_db @.dbname = 'jg',
> @.filename1 = 'S:\SQL\Data\jg_Data.mdf',
> @.filename2 = 'E:\SQL\LOGS\jg_log.ldf'
> After attaching the database, the database comes up read-only. Can someone
> tell me why?
> Thanks,
> --
> Dan D.
Do you get an error when you try to make them not read-only?
Have you checked the readonly attribute on these files? To uncheck them you
will need to detach the database again.
How did you move the files?
John|||I didn't get an error. I did this twice on two different databases and had
the same problem both times. I didn't check the readonly attribute but since
I moved the files using drag-and-drop, I wouldn't expect the attribute to
change.
I had a backup of the database and I restored it and didn't have any problem
.
Thanks,
--
Dan D.
"John Bell" wrote:

> Hi Dan
> "Dan D." wrote:
>
> Do you get an error when you try to make them not read-only?
> Have you checked the readonly attribute on these files? To uncheck them yo
u
> will need to detach the database again.
> How did you move the files?
> John|||Hi Dan
"Dan D." wrote:

> I didn't get an error. I did this twice on two different databases and had
> the same problem both times. I didn't check the readonly attribute but sin
ce
> I moved the files using drag-and-drop, I wouldn't expect the attribute to
> change.
> I had a backup of the database and I restored it and didn't have any probl
em.
> Thanks,
> --
> Dan D.
If you didn't get an error changing the database from readonly the file
attributes must be ok. I did find this
http://forums.microsoft.com/MSDN/Sh...303056&SiteID=1 where
the service accounts permissions on the directory has an impact, so it may b
e
your permissions rather than the service accounts if you backup/restore work
s
ok.
John
John|||Hi,
I know that in SQL 2005 when attaching databases they come up in a state of
reduced functinality. This is a security measure to make sure that someone
cannot attach a database that gives them more permsissions than they might
otherwise have. Maybe some of this concept made it into SQL2000 SP4.
Thank you,
Daniel Jameson
SQL Server DBA
Children's Oncology Group
www.childrensoncologygroup.org
"Dan D." <DanD@.discussions.microsoft.com> wrote in message
news:5F9EE66C-31AC-4AC6-A971-7613346DBE1D@.microsoft.com...
> Using SS2000 SP4. I've done this many times before without problem so I
> don't
> know why I'm having trouble this time. I'm running out of space on a drive
> so
> I'm moving some databases to another drive. I've tried this with EM and QA
> using this query:
> EXEC sp_detach_db 'jg', 'false'
> EXEC sp_attach_db @.dbname = 'jg',
> @.filename1 = 'S:\SQL\Data\jg_Data.mdf',
> @.filename2 = 'E:\SQL\LOGS\jg_log.ldf'
> After attaching the database, the database comes up read-only. Can someone
> tell me why?
> Thanks,
> --
> Dan D.|||It was the security on the folder that I was moving the data to. The user
didn't have enough permissions.
Thanks,
--
Dan D.
"John Bell" wrote:

> Hi Dan
> "Dan D." wrote:
>
> If you didn't get an error changing the database from readonly the file
> attributes must be ok. I did find this
> http://forums.microsoft.com/MSDN/Sh...303056&SiteID=1 wher
e
> the service accounts permissions on the directory has an impact, so it may
be
> your permissions rather than the service accounts if you backup/restore wo
rks
> ok.
> John
> John

moving a database causes it to be read-only

Using SS2000 SP4. I've done this many times before without problem so I don't
know why I'm having trouble this time. I'm running out of space on a drive so
I'm moving some databases to another drive. I've tried this with EM and QA
using this query:
EXEC sp_detach_db 'jg', 'false'
EXEC sp_attach_db @.dbname = 'jg',
@.filename1 = 'S:\SQL\Data\jg_Data.mdf',
@.filename2 = 'E:\SQL\LOGS\jg_log.ldf'
After attaching the database, the database comes up read-only. Can someone
tell me why?
Thanks,
--
Dan D.Hi Dan
"Dan D." wrote:
> Using SS2000 SP4. I've done this many times before without problem so I don't
> know why I'm having trouble this time. I'm running out of space on a drive so
> I'm moving some databases to another drive. I've tried this with EM and QA
> using this query:
> EXEC sp_detach_db 'jg', 'false'
> EXEC sp_attach_db @.dbname = 'jg',
> @.filename1 = 'S:\SQL\Data\jg_Data.mdf',
> @.filename2 = 'E:\SQL\LOGS\jg_log.ldf'
> After attaching the database, the database comes up read-only. Can someone
> tell me why?
> Thanks,
> --
> Dan D.
Do you get an error when you try to make them not read-only?
Have you checked the readonly attribute on these files? To uncheck them you
will need to detach the database again.
How did you move the files?
John|||I didn't get an error. I did this twice on two different databases and had
the same problem both times. I didn't check the readonly attribute but since
I moved the files using drag-and-drop, I wouldn't expect the attribute to
change.
I had a backup of the database and I restored it and didn't have any problem.
Thanks,
--
Dan D.
"John Bell" wrote:
> Hi Dan
> "Dan D." wrote:
> > Using SS2000 SP4. I've done this many times before without problem so I don't
> > know why I'm having trouble this time. I'm running out of space on a drive so
> > I'm moving some databases to another drive. I've tried this with EM and QA
> > using this query:
> > EXEC sp_detach_db 'jg', 'false'
> > EXEC sp_attach_db @.dbname = 'jg',
> > @.filename1 = 'S:\SQL\Data\jg_Data.mdf',
> > @.filename2 = 'E:\SQL\LOGS\jg_log.ldf'
> >
> > After attaching the database, the database comes up read-only. Can someone
> > tell me why?
> >
> > Thanks,
> > --
> > Dan D.
> Do you get an error when you try to make them not read-only?
> Have you checked the readonly attribute on these files? To uncheck them you
> will need to detach the database again.
> How did you move the files?
> John|||Hi Dan
"Dan D." wrote:
> I didn't get an error. I did this twice on two different databases and had
> the same problem both times. I didn't check the readonly attribute but since
> I moved the files using drag-and-drop, I wouldn't expect the attribute to
> change.
> I had a backup of the database and I restored it and didn't have any problem.
> Thanks,
> --
> Dan D.
If you didn't get an error changing the database from readonly the file
attributes must be ok. I did find this
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=303056&SiteID=1 where
the service accounts permissions on the directory has an impact, so it may be
your permissions rather than the service accounts if you backup/restore works
ok.
John
John|||Hi,
I know that in SQL 2005 when attaching databases they come up in a state of
reduced functinality. This is a security measure to make sure that someone
cannot attach a database that gives them more permsissions than they might
otherwise have. Maybe some of this concept made it into SQL2000 SP4.
--
Thank you,
Daniel Jameson
SQL Server DBA
Children's Oncology Group
www.childrensoncologygroup.org
"Dan D." <DanD@.discussions.microsoft.com> wrote in message
news:5F9EE66C-31AC-4AC6-A971-7613346DBE1D@.microsoft.com...
> Using SS2000 SP4. I've done this many times before without problem so I
> don't
> know why I'm having trouble this time. I'm running out of space on a drive
> so
> I'm moving some databases to another drive. I've tried this with EM and QA
> using this query:
> EXEC sp_detach_db 'jg', 'false'
> EXEC sp_attach_db @.dbname = 'jg',
> @.filename1 = 'S:\SQL\Data\jg_Data.mdf',
> @.filename2 = 'E:\SQL\LOGS\jg_log.ldf'
> After attaching the database, the database comes up read-only. Can someone
> tell me why?
> Thanks,
> --
> Dan D.|||It was the security on the folder that I was moving the data to. The user
didn't have enough permissions.
Thanks,
--
Dan D.
"John Bell" wrote:
> Hi Dan
> "Dan D." wrote:
> > I didn't get an error. I did this twice on two different databases and had
> > the same problem both times. I didn't check the readonly attribute but since
> > I moved the files using drag-and-drop, I wouldn't expect the attribute to
> > change.
> >
> > I had a backup of the database and I restored it and didn't have any problem.
> >
> > Thanks,
> > --
> > Dan D.
> If you didn't get an error changing the database from readonly the file
> attributes must be ok. I did find this
> http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=303056&SiteID=1 where
> the service accounts permissions on the directory has an impact, so it may be
> your permissions rather than the service accounts if you backup/restore works
> ok.
> John
> John

moving a database causes it to be read-only

Using SS2000 SP4. I've done this many times before without problem so I don't
know why I'm having trouble this time. I'm running out of space on a drive so
I'm moving some databases to another drive. I've tried this with EM and QA
using this query:
EXEC sp_detach_db 'jg', 'false'
EXEC sp_attach_db @.dbname = 'jg',
@.filename1 = 'S:\SQL\Data\jg_Data.mdf',
@.filename2 = 'E:\SQL\LOGS\jg_log.ldf'
After attaching the database, the database comes up read-only. Can someone
tell me why?
Thanks,
Dan D.
Hi Dan
"Dan D." wrote:

> Using SS2000 SP4. I've done this many times before without problem so I don't
> know why I'm having trouble this time. I'm running out of space on a drive so
> I'm moving some databases to another drive. I've tried this with EM and QA
> using this query:
> EXEC sp_detach_db 'jg', 'false'
> EXEC sp_attach_db @.dbname = 'jg',
> @.filename1 = 'S:\SQL\Data\jg_Data.mdf',
> @.filename2 = 'E:\SQL\LOGS\jg_log.ldf'
> After attaching the database, the database comes up read-only. Can someone
> tell me why?
> Thanks,
> --
> Dan D.
Do you get an error when you try to make them not read-only?
Have you checked the readonly attribute on these files? To uncheck them you
will need to detach the database again.
How did you move the files?
John
|||I didn't get an error. I did this twice on two different databases and had
the same problem both times. I didn't check the readonly attribute but since
I moved the files using drag-and-drop, I wouldn't expect the attribute to
change.
I had a backup of the database and I restored it and didn't have any problem.
Thanks,
Dan D.
"John Bell" wrote:

> Hi Dan
> "Dan D." wrote:
>
> Do you get an error when you try to make them not read-only?
> Have you checked the readonly attribute on these files? To uncheck them you
> will need to detach the database again.
> How did you move the files?
> John
|||Hi Dan
"Dan D." wrote:

> I didn't get an error. I did this twice on two different databases and had
> the same problem both times. I didn't check the readonly attribute but since
> I moved the files using drag-and-drop, I wouldn't expect the attribute to
> change.
> I had a backup of the database and I restored it and didn't have any problem.
> Thanks,
> --
> Dan D.
If you didn't get an error changing the database from readonly the file
attributes must be ok. I did find this
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=303056&SiteID=1 where
the service accounts permissions on the directory has an impact, so it may be
your permissions rather than the service accounts if you backup/restore works
ok.
John
John
|||Hi,
I know that in SQL 2005 when attaching databases they come up in a state of
reduced functinality. This is a security measure to make sure that someone
cannot attach a database that gives them more permsissions than they might
otherwise have. Maybe some of this concept made it into SQL2000 SP4.
Thank you,
Daniel Jameson
SQL Server DBA
Children's Oncology Group
www.childrensoncologygroup.org
"Dan D." <DanD@.discussions.microsoft.com> wrote in message
news:5F9EE66C-31AC-4AC6-A971-7613346DBE1D@.microsoft.com...
> Using SS2000 SP4. I've done this many times before without problem so I
> don't
> know why I'm having trouble this time. I'm running out of space on a drive
> so
> I'm moving some databases to another drive. I've tried this with EM and QA
> using this query:
> EXEC sp_detach_db 'jg', 'false'
> EXEC sp_attach_db @.dbname = 'jg',
> @.filename1 = 'S:\SQL\Data\jg_Data.mdf',
> @.filename2 = 'E:\SQL\LOGS\jg_log.ldf'
> After attaching the database, the database comes up read-only. Can someone
> tell me why?
> Thanks,
> --
> Dan D.
|||It was the security on the folder that I was moving the data to. The user
didn't have enough permissions.
Thanks,
Dan D.
"John Bell" wrote:

> Hi Dan
> "Dan D." wrote:
>
> If you didn't get an error changing the database from readonly the file
> attributes must be ok. I did find this
> http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=303056&SiteID=1 where
> the service accounts permissions on the directory has an impact, so it may be
> your permissions rather than the service accounts if you backup/restore works
> ok.
> John
> John
sql

movie db query problems

Hi everyone!

this is the first time I use this forum although I already got plenty of help from it!

My problem is the following. I have created a movie database. I am using Oracle iSQL*Plus.

One table is called 'movie_t' another one 'person_t' and inside this last table is nexted a table called 'castmembers'.

one of my query is : "For all movies,list the leading actress, i.e. the first billed actress (in the order of credits). Show the movie Title, Genre, Director and Name of the actress ".

I found a complex solution that works:

SELECT title, name, creditorder "CREDIT ORDER"
FROM (select RANK() OVER (partition by m.title ORDER BY c.creditorder) rankcredit, m.title, p.name, c.creditorder
FROM person_t p, table(p.castmembers) c, movie_t m
WHERE c.movie_ref = ref(m) and p.gender = 'F') T WHERE RANKCREDIT = 1;

However, I am pretty sure that there must be a simple solution without using a method and partition.

I tried the following statement:

select m.title, m.director.name, m.genre, p.name, c.creditorder
from movie_t m, person_t p, table(p.castmembers)c
where ref(m) = c.movie_ref
and c.creditorder in
(Select min(d.creditorder)
from movie_t n, person_t q, table(q.castmembers)d
where m.title=n.title and m.director.name=n.director.name and

m.genre=n.genre and p.name=q.name and p.gender = 'F'
group by n.title, n.director.name, n.genre )

This unfortunately does not work, as it returns me all actresses!

please check my .doc file, it makes it easier to understand!

if anyone could help me a little bit, I would greatly appreciate, I have spent my whole sunday on that and it's getting realy frustrating for me!

thanks all,
Gaetansorry guys, my file did not atached, I try one more time :-)|||i finally found the solution :-)

SELECT c.movie_ref.title, p.name, c.creditorder, m.genre, m.director.name
FROM movie_t m, person_t p, table(p.castmembers)c
WHERE c.movie_ref= ref(m) and p.gender = 'F' and (c.creditorder) = all
( select min(c.creditorder) from person_t p, table(p.castmembers)c
WHERE c.movie_ref= ref(m) and p.gender = 'F');

if any of u think about anything else let me know, maybe I can also use the "having" function?

gaetan :-)sql

Move the contents of a table from local server to remote server(main server)

hi, I am having a problem in exporting or importind data into a table from
one server to another .Actually some time the connection breaks or the
server gives error message as primary pane full.and the process is
termineted.Can u please suggest me some method to transfer the table and its
content from one table to another in lesser time and with more accuracy.
Thanks and regard,
Irshad Ahmad.Hello Irshad,
Which method are you using to Tranfer the Data ?
Is it between SQL Servers ?
Could you post the exact error message reported by the tool ?
Thanks & Regards
Vishal|||yah i am transferring the data between sqlservers and the error generated is
"Primary File group Full" after something around 30 -40 thousand rows have
been transferred.
"Vishal Gandhi" <vishalg@.microsoft.com> wrote in message
news:KSsMFAxgGHA.4896@.TK2MSFTNGXA01.phx.gbl...
> Hello Irshad,
> Which method are you using to Tranfer the Data ?
> Is it between SQL Servers ?
> Could you post the exact error message reported by the tool ?
> Thanks & Regards
> Vishal
>|||Increase the size of the database file which is full (probably the mdf file).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Irshad Ahmad" <efextra@.newsgroups.nospam> wrote in message
news:efNZGR9gGHA.896@.TK2MSFTNGP02.phx.gbl...
> yah i am transferring the data between sqlservers and the error generated is "Primary File group
> Full" after something around 30 -40 thousand rows have been transferred.
> "Vishal Gandhi" <vishalg@.microsoft.com> wrote in message
> news:KSsMFAxgGHA.4896@.TK2MSFTNGXA01.phx.gbl...
>> Hello Irshad,
>> Which method are you using to Tranfer the Data ?
>> Is it between SQL Servers ?
>> Could you post the exact error message reported by the tool ?
>> Thanks & Regards
>> Vishal
>

Move the contents of a table from local server to remote server(main server)

hi, I am having a problem in exporting or importind data into a table from
one server to another .Actually some time the connection breaks or the
server gives error message as primary pane full.and the process is
termineted.Can u please suggest me some method to transfer the table and its
content from one table to another in lesser time and with more accuracy.
Thanks and regard,
Irshad Ahmad.Hello Irshad,
Which method are you using to Tranfer the Data ?
Is it between SQL Servers ?
Could you post the exact error message reported by the tool ?
Thanks & Regards
Vishal|||yah i am transferring the data between sqlservers and the error generated is
"Primary File group Full" after something around 30 -40 thousand rows have
been transferred.
"Vishal Gandhi" <vishalg@.microsoft.com> wrote in message
news:KSsMFAxgGHA.4896@.TK2MSFTNGXA01.phx.gbl...
> Hello Irshad,
> Which method are you using to Tranfer the Data ?
> Is it between SQL Servers ?
> Could you post the exact error message reported by the tool ?
> Thanks & Regards
> Vishal
>|||Increase the size of the database file which is full (probably the mdf file)
.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Irshad Ahmad" <efextra@.newsgroups.nospam> wrote in message
news:efNZGR9gGHA.896@.TK2MSFTNGP02.phx.gbl...
> yah i am transferring the data between sqlservers and the error generated
is "Primary File group
> Full" after something around 30 -40 thousand rows have been transferred.
> "Vishal Gandhi" <vishalg@.microsoft.com> wrote in message
> news:KSsMFAxgGHA.4896@.TK2MSFTNGXA01.phx.gbl...
>

Move Table to different filegroup.

Hi everyone,
We have a production database holding time based manufacturing data. A new
table is created on the 1st day of each month and data is saved there until
the 1st day of the next month.
Each new table holds one month's worth of data - table size is between 2.5
GB and 4 GB. For the purpose of more efficient backup strategy we want to
move tables older than 1 year to a different
filegroup. Moving a table table using Enterpise Manager takes a long time
and causes the transaction log to grow by about 7-8 GB even if I set the
Recovery model to Simple.
What can I do to be able to move the tables more quickly and efficiently ?
Best regards.
Hi
If you the table has a clustered index , just re-create an index on
different file group . It is just because a CI in leaf level has actual
data and as result all table will be moved as well
--Create a table via QA and not via EM
CREATE TABLE mywind..t1 (id int) ON new_customers
CREATE TABLE mywind..t2 (id int) ON sales
GO
"Sezgin Rafet" <anonymous@.newsgroup.com> wrote in message
news:u8ma5BATGHA.1688@.TK2MSFTNGP11.phx.gbl...
> Hi everyone,
> We have a production database holding time based manufacturing data. A new
> table is created on the 1st day of each month and data is saved there
> until the 1st day of the next month.
> Each new table holds one month's worth of data - table size is between 2.5
> GB and 4 GB. For the purpose of more efficient backup strategy we want to
> move tables older than 1 year to a different
> filegroup. Moving a table table using Enterpise Manager takes a long time
> and causes the transaction log to grow by about 7-8 GB even if I set the
> Recovery model to Simple.
> What can I do to be able to move the tables more quickly and efficiently ?
>
> Best regards.
>
>
sql

Move Table to different filegroup.

Hi everyone,
We have a production database holding time based manufacturing data. A new
table is created on the 1st day of each month and data is saved there until
the 1st day of the next month.
Each new table holds one month's worth of data - table size is between 2.5
GB and 4 GB. For the purpose of more efficient backup strategy we want to
move tables older than 1 year to a different
filegroup. Moving a table table using Enterpise Manager takes a long time
and causes the transaction log to grow by about 7-8 GB even if I set the
Recovery model to Simple.
What can I do to be able to move the tables more quickly and efficiently ?
Best regards.Hi
If you the table has a clustered index , just re-create an index on
different file group . It is just because a CI in leaf level has actual
data and as result all table will be moved as well
--Create a table via QA and not via EM
CREATE TABLE mywind..t1 (id int) ON new_customers
CREATE TABLE mywind..t2 (id int) ON sales
GO
"Sezgin Rafet" <anonymous@.newsgroup.com> wrote in message
news:u8ma5BATGHA.1688@.TK2MSFTNGP11.phx.gbl...
> Hi everyone,
> We have a production database holding time based manufacturing data. A new
> table is created on the 1st day of each month and data is saved there
> until the 1st day of the next month.
> Each new table holds one month's worth of data - table size is between 2.5
> GB and 4 GB. For the purpose of more efficient backup strategy we want to
> move tables older than 1 year to a different
> filegroup. Moving a table table using Enterpise Manager takes a long time
> and causes the transaction log to grow by about 7-8 GB even if I set the
> Recovery model to Simple.
> What can I do to be able to move the tables more quickly and efficiently ?
>
> Best regards.
>
>

Move Table to different filegroup.

Hi everyone,
We have a production database holding time based manufacturing data. A new
table is created on the 1st day of each month and data is saved there until
the 1st day of the next month.
Each new table holds one month's worth of data - table size is between 2.5
GB and 4 GB. For the purpose of more efficient backup strategy we want to
move tables older than 1 year to a different
filegroup. Moving a table table using Enterpise Manager takes a long time
and causes the transaction log to grow by about 7-8 GB even if I set the
Recovery model to Simple.
What can I do to be able to move the tables more quickly and efficiently ?
Best regards.Hi
If you the table has a clustered index , just re-create an index on
different file group . It is just because a CI in leaf level has actual
data and as result all table will be moved as well
--Create a table via QA and not via EM
CREATE TABLE mywind..t1 (id int) ON new_customers
CREATE TABLE mywind..t2 (id int) ON sales
GO
"Sezgin Rafet" <anonymous@.newsgroup.com> wrote in message
news:u8ma5BATGHA.1688@.TK2MSFTNGP11.phx.gbl...
> Hi everyone,
> We have a production database holding time based manufacturing data. A new
> table is created on the 1st day of each month and data is saved there
> until the 1st day of the next month.
> Each new table holds one month's worth of data - table size is between 2.5
> GB and 4 GB. For the purpose of more efficient backup strategy we want to
> move tables older than 1 year to a different
> filegroup. Moving a table table using Enterpise Manager takes a long time
> and causes the transaction log to grow by about 7-8 GB even if I set the
> Recovery model to Simple.
> What can I do to be able to move the tables more quickly and efficiently ?
>
> Best regards.
>
>

move record from TableA to TableB at a specified time of the record

How can i move record from TableA to TableB at a specified time of the
record?
Thanks for any hints.
TonyTony WONG wrote:
> How can i move record from TableA to TableB at a specified time of the
> record?
> Thanks for any hints.
> Tony
One obvious way might be like this:
CREATE VIEW TableB
AS
SELECT col1, col2, col3
FROM TableA
WHERE datetime_col <= CURRENT_TIMESTAMP
GO
If you want to archive data on a regular bases with INSERT statements
then create a proc and schedule it as a job.
In SQL Server 2005 you may want to consider using a partitioned table
instead.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Thanks for your prompt advice
may i know the best way to trigger this sql?
add a job to execute in every 5 minutes?
thanks a lot.
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org>
'?:1147069404.714636.117510@.y43g2000cwc.googlegroups.com...
> Tony WONG wrote:
> One obvious way might be like this:
> CREATE VIEW TableB
> AS
> SELECT col1, col2, col3
> FROM TableA
> WHERE datetime_col <= CURRENT_TIMESTAMP
> GO
> If you want to archive data on a regular bases with INSERT statements
> then create a proc and schedule it as a job.
> In SQL Server 2005 you may want to consider using a partitioned table
> instead.
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>|||Tony WONG wrote:
> Thanks for your prompt advice
> may i know the best way to trigger this sql?
> add a job to execute in every 5 minutes?
>
For what purpose would you want to do this every 5 minutes? That seems
much too frequent for a simple archiving process. I'd say there ought
to be a better solution - maybe replication, mirroring or triggers for
example - but that depends on just why you want two tables to replicate
the same data.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Tony WONG wrote:
> Thanks for your prompt advice
> may i know the best way to trigger this sql?
> add a job to execute in every 5 minutes?
>
For what purpose would you want to do this every 5 minutes? That seems
much too frequent for a simple archiving process. I'd say there ought
to be a better solution - maybe replication, mirroring or triggers for
example - but that depends on just why you want two tables to replicate
the same data.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||in fact it is a SMS system
TableA store the record to be sent to TableB(production table)
Once records add to TableB, the record will be distributed to SMS queue.
The question is what is the best method to do it (add a job to do every 5
minutes) in terms of hardware resource (cpu loading...)
by the way, how to add a job in every 5 minutes?
Thanks a lot for your assistance.
tony
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org>
'?:1147109579.095918.27650@.g10g2000cwb.googlegroups.com...
> Tony WONG wrote:
> For what purpose would you want to do this every 5 minutes? That seems
> much too frequent for a simple archiving process. I'd say there ought
> to be a better solution - maybe replication, mirroring or triggers for
> example - but that depends on just why you want two tables to replicate
> the same data.
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>|||Tony WONG wrote:
> in fact it is a SMS system
> TableA store the record to be sent to TableB(production table)
> Once records add to TableB, the record will be distributed to SMS queue.
> The question is what is the best method to do it (add a job to do every 5
> minutes) in terms of hardware resource (cpu loading...)
> by the way, how to add a job in every 5 minutes?
Doesn't make much sense to me as you've described it. If the date is
what determines when a row is to be processed then why do you need two
tables? You could have just one table and create a view as I first
suggested.
Two similar tables in the same database is usually considered to be a
logical design flaw. It is also inefficient if it forces you to insert
the same data twice, especially if you have to do so every 5 minutes.
Consider changing the design.
If you have to work with what you have then you could either implement
a trigger or create a job (lookup sp_add_job in Books Online). If the
inserts are always to be time driven then use a job.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--

move data from Excel file to a table (MS SQL)

I have an application , user will read information in Excel file and insert that data into my application, I think it spend a lot of time. I want to make a tool which move data from Excel file to a table in My application (MS SQL) automaticly. How to do it, anybody has tool or know how to do, pls help me.
thanks.
MS SQL server is shiped with a component named ( Import And Export data) which you can use to convert your excel data into sql table
regards|||thank for your reply,
I think you did not understand my question. User is person which use my application , so they can not import excel to MS SQL, I would like develop a tool Which can read structure of Excel file to insert data from Excel into SQL server. How to do it
thanks