How can I import an xml file to SQL at the same time every night? I will
need to create a new database first via the import after that I will be
appending to the database. Then I need to xport the data into a difference
xml file.
Do I have to have the orginal xml file on my server or can I point to the
location of the xml file?
Thank you
Dee
Hi
You don't give the version of SQL Server that you are using! You can write a
stored procedure that will create the database/table if they do not exist and
then pass the database name to a DTS/SSIS package that will load the file.
Using this global variable for the package you can then change the connection
properties.
You could use OPENXML to load the file and compare the two entries (assuming
the same structure) and FOR XML to produce your output which would not need
DTS/SSIS.
John
"Dee" wrote:
> How can I import an xml file to SQL at the same time every night? I will
> need to create a new database first via the import after that I will be
> appending to the database. Then I need to xport the data into a difference
> xml file.
> Do I have to have the orginal xml file on my server or can I point to the
> location of the xml file?
> Thank you
> Dee
|||John,
I am using SQl 2005 on Windows XP. I have the SQl 2005 express installed
and the standard for Windows XP installed.
Will this work for both.
Thanks
Dee
"John Bell" wrote:
[vbcol=seagreen]
> Hi
> You don't give the version of SQL Server that you are using! You can write a
> stored procedure that will create the database/table if they do not exist and
> then pass the database name to a DTS/SSIS package that will load the file.
> Using this global variable for the package you can then change the connection
> properties.
> You could use OPENXML to load the file and compare the two entries (assuming
> the same structure) and FOR XML to produce your output which would not need
> DTS/SSIS.
> John
> "Dee" wrote:
|||Hi
Import/Export and Integration services is not on the feature list for SQL
Express see
http://www.microsoft.com/sql/prodinfo/features/compare-features.mspx.
Therefore using OPENXML and FOR XML (use BCP or SQLCMD to create a file) is
probably the way to go.
John
"Dee" wrote:
[vbcol=seagreen]
> John,
> I am using SQl 2005 on Windows XP. I have the SQl 2005 express installed
> and the standard for Windows XP installed.
> Will this work for both.
> Thanks
> Dee
> "John Bell" wrote:
|||But I also have SQL 2005 Standard installed. Can I do an Import/Export from
there. I also have SQL 2005 Enterprise installed at work. How do I do it
from there?
Thanks Dee
"John Bell" wrote:
[vbcol=seagreen]
> Hi
> Import/Export and Integration services is not on the feature list for SQL
> Express see
> http://www.microsoft.com/sql/prodinfo/features/compare-features.mspx.
> Therefore using OPENXML and FOR XML (use BCP or SQLCMD to create a file) is
> probably the way to go.
> John
> "Dee" wrote:
|||Hi Dee
You would be able to run a package on the Std edition that connected to the
Express edition and populated it, but what you are trying to achieve should
be codable in T-SQL without the need for a package, therefore it can be run
from a command prompt and SQLCMD on the machine that is running SQL Express.
This may help http://www.sqlis.com/31.aspx
John
"Dee" wrote:
[vbcol=seagreen]
> But I also have SQL 2005 Standard installed. Can I do an Import/Export from
> there. I also have SQL 2005 Enterprise installed at work. How do I do it
> from there?
> Thanks Dee
> "John Bell" wrote:
Showing posts with label time. Show all posts
Showing posts with label time. Show all posts
Thursday, March 29, 2012
Automation
How can I import an xml file to SQL at the same time every night? I will
need to create a new database first via the import after that I will be
appending to the database. Then I need to xport the data into a difference
xml file.
Do I have to have the orginal xml file on my server or can I point to the
location of the xml file?
Thank you
DeeHi
You don't give the version of SQL Server that you are using! You can write a
stored procedure that will create the database/table if they do not exist an
d
then pass the database name to a DTS/SSIS package that will load the file.
Using this global variable for the package you can then change the connectio
n
properties.
You could use OPENXML to load the file and compare the two entries (assuming
the same structure) and FOR XML to produce your output which would not need
DTS/SSIS.
John
"Dee" wrote:
> How can I import an xml file to SQL at the same time every night? I will
> need to create a new database first via the import after that I will be
> appending to the database. Then I need to xport the data into a differenc
e
> xml file.
> Do I have to have the orginal xml file on my server or can I point to the
> location of the xml file?
> Thank you
> Dee|||John,
I am using SQl 2005 on Windows XP. I have the SQl 2005 express installed
and the standard for Windows XP installed.
Will this work for both.
Thanks
Dee
"John Bell" wrote:
[vbcol=seagreen]
> Hi
> You don't give the version of SQL Server that you are using! You can write
a
> stored procedure that will create the database/table if they do not exist
and
> then pass the database name to a DTS/SSIS package that will load the file.
> Using this global variable for the package you can then change the connect
ion
> properties.
> You could use OPENXML to load the file and compare the two entries (assumi
ng
> the same structure) and FOR XML to produce your output which would not nee
d
> DTS/SSIS.
> John
> "Dee" wrote:
>|||Hi
Import/Export and Integration services is not on the feature list for SQL
Express see
http://www.microsoft.com/sql/prodin...-features.mspx.
Therefore using OPENXML and FOR XML (use BCP or SQLCMD to create a file) is
probably the way to go.
John
"Dee" wrote:
[vbcol=seagreen]
> John,
> I am using SQl 2005 on Windows XP. I have the SQl 2005 express installed
> and the standard for Windows XP installed.
> Will this work for both.
> Thanks
> Dee
> "John Bell" wrote:
>|||But I also have SQL 2005 Standard installed. Can I do an Import/Export fro
m
there. I also have SQL 2005 Enterprise installed at work. How do I do it
from there?
Thanks Dee
"John Bell" wrote:
[vbcol=seagreen]
> Hi
> Import/Export and Integration services is not on the feature list for SQL
> Express see
> http://www.microsoft.com/sql/prodin...-features.mspx.
> Therefore using OPENXML and FOR XML (use BCP or SQLCMD to create a file) i
s
> probably the way to go.
> John
> "Dee" wrote:
>|||Hi Dee
You would be able to run a package on the Std edition that connected to the
Express edition and populated it, but what you are trying to achieve should
be codable in T-SQL without the need for a package, therefore it can be run
from a command prompt and SQLCMD on the machine that is running SQL Express.
This may help http://www.sqlis.com/31.aspx
John
"Dee" wrote:
[vbcol=seagreen]
> But I also have SQL 2005 Standard installed. Can I do an Import/Export f
rom
> there. I also have SQL 2005 Enterprise installed at work. How do I do it
> from there?
> Thanks Dee
> "John Bell" wrote:
>
need to create a new database first via the import after that I will be
appending to the database. Then I need to xport the data into a difference
xml file.
Do I have to have the orginal xml file on my server or can I point to the
location of the xml file?
Thank you
DeeHi
You don't give the version of SQL Server that you are using! You can write a
stored procedure that will create the database/table if they do not exist an
d
then pass the database name to a DTS/SSIS package that will load the file.
Using this global variable for the package you can then change the connectio
n
properties.
You could use OPENXML to load the file and compare the two entries (assuming
the same structure) and FOR XML to produce your output which would not need
DTS/SSIS.
John
"Dee" wrote:
> How can I import an xml file to SQL at the same time every night? I will
> need to create a new database first via the import after that I will be
> appending to the database. Then I need to xport the data into a differenc
e
> xml file.
> Do I have to have the orginal xml file on my server or can I point to the
> location of the xml file?
> Thank you
> Dee|||John,
I am using SQl 2005 on Windows XP. I have the SQl 2005 express installed
and the standard for Windows XP installed.
Will this work for both.
Thanks
Dee
"John Bell" wrote:
[vbcol=seagreen]
> Hi
> You don't give the version of SQL Server that you are using! You can write
a
> stored procedure that will create the database/table if they do not exist
and
> then pass the database name to a DTS/SSIS package that will load the file.
> Using this global variable for the package you can then change the connect
ion
> properties.
> You could use OPENXML to load the file and compare the two entries (assumi
ng
> the same structure) and FOR XML to produce your output which would not nee
d
> DTS/SSIS.
> John
> "Dee" wrote:
>|||Hi
Import/Export and Integration services is not on the feature list for SQL
Express see
http://www.microsoft.com/sql/prodin...-features.mspx.
Therefore using OPENXML and FOR XML (use BCP or SQLCMD to create a file) is
probably the way to go.
John
"Dee" wrote:
[vbcol=seagreen]
> John,
> I am using SQl 2005 on Windows XP. I have the SQl 2005 express installed
> and the standard for Windows XP installed.
> Will this work for both.
> Thanks
> Dee
> "John Bell" wrote:
>|||But I also have SQL 2005 Standard installed. Can I do an Import/Export fro
m
there. I also have SQL 2005 Enterprise installed at work. How do I do it
from there?
Thanks Dee
"John Bell" wrote:
[vbcol=seagreen]
> Hi
> Import/Export and Integration services is not on the feature list for SQL
> Express see
> http://www.microsoft.com/sql/prodin...-features.mspx.
> Therefore using OPENXML and FOR XML (use BCP or SQLCMD to create a file) i
s
> probably the way to go.
> John
> "Dee" wrote:
>|||Hi Dee
You would be able to run a package on the Std edition that connected to the
Express edition and populated it, but what you are trying to achieve should
be codable in T-SQL without the need for a package, therefore it can be run
from a command prompt and SQLCMD on the machine that is running SQL Express.
This may help http://www.sqlis.com/31.aspx
John
"Dee" wrote:
[vbcol=seagreen]
> But I also have SQL 2005 Standard installed. Can I do an Import/Export f
rom
> there. I also have SQL 2005 Enterprise installed at work. How do I do it
> from there?
> Thanks Dee
> "John Bell" wrote:
>
Automation
How can I import an xml file to SQL at the same time every night? I will
need to create a new database first via the import after that I will be
appending to the database. Then I need to xport the data into a difference
xml file.
Do I have to have the orginal xml file on my server or can I point to the
location of the xml file?
Thank you
DeeHi
You don't give the version of SQL Server that you are using! You can write a
stored procedure that will create the database/table if they do not exist and
then pass the database name to a DTS/SSIS package that will load the file.
Using this global variable for the package you can then change the connection
properties.
You could use OPENXML to load the file and compare the two entries (assuming
the same structure) and FOR XML to produce your output which would not need
DTS/SSIS.
John
"Dee" wrote:
> How can I import an xml file to SQL at the same time every night? I will
> need to create a new database first via the import after that I will be
> appending to the database. Then I need to xport the data into a difference
> xml file.
> Do I have to have the orginal xml file on my server or can I point to the
> location of the xml file?
> Thank you
> Dee|||John,
I am using SQl 2005 on Windows XP. I have the SQl 2005 express installed
and the standard for Windows XP installed.
Will this work for both.
Thanks
Dee
"John Bell" wrote:
> Hi
> You don't give the version of SQL Server that you are using! You can write a
> stored procedure that will create the database/table if they do not exist and
> then pass the database name to a DTS/SSIS package that will load the file.
> Using this global variable for the package you can then change the connection
> properties.
> You could use OPENXML to load the file and compare the two entries (assuming
> the same structure) and FOR XML to produce your output which would not need
> DTS/SSIS.
> John
> "Dee" wrote:
> > How can I import an xml file to SQL at the same time every night? I will
> > need to create a new database first via the import after that I will be
> > appending to the database. Then I need to xport the data into a difference
> > xml file.
> >
> > Do I have to have the orginal xml file on my server or can I point to the
> > location of the xml file?
> >
> > Thank you
> > Dee|||Hi
Import/Export and Integration services is not on the feature list for SQL
Express see
http://www.microsoft.com/sql/prodinfo/features/compare-features.mspx.
Therefore using OPENXML and FOR XML (use BCP or SQLCMD to create a file) is
probably the way to go.
John
"Dee" wrote:
> John,
> I am using SQl 2005 on Windows XP. I have the SQl 2005 express installed
> and the standard for Windows XP installed.
> Will this work for both.
> Thanks
> Dee
> "John Bell" wrote:
> > Hi
> >
> > You don't give the version of SQL Server that you are using! You can write a
> > stored procedure that will create the database/table if they do not exist and
> > then pass the database name to a DTS/SSIS package that will load the file.
> > Using this global variable for the package you can then change the connection
> > properties.
> >
> > You could use OPENXML to load the file and compare the two entries (assuming
> > the same structure) and FOR XML to produce your output which would not need
> > DTS/SSIS.
> >
> > John
> >
> > "Dee" wrote:
> >
> > > How can I import an xml file to SQL at the same time every night? I will
> > > need to create a new database first via the import after that I will be
> > > appending to the database. Then I need to xport the data into a difference
> > > xml file.
> > >
> > > Do I have to have the orginal xml file on my server or can I point to the
> > > location of the xml file?
> > >
> > > Thank you
> > > Dee|||But I also have SQL 2005 Standard installed. Can I do an Import/Export from
there. I also have SQL 2005 Enterprise installed at work. How do I do it
from there?
Thanks Dee
"John Bell" wrote:
> Hi
> Import/Export and Integration services is not on the feature list for SQL
> Express see
> http://www.microsoft.com/sql/prodinfo/features/compare-features.mspx.
> Therefore using OPENXML and FOR XML (use BCP or SQLCMD to create a file) is
> probably the way to go.
> John
> "Dee" wrote:
> > John,
> >
> > I am using SQl 2005 on Windows XP. I have the SQl 2005 express installed
> > and the standard for Windows XP installed.
> >
> > Will this work for both.
> >
> > Thanks
> > Dee
> >
> > "John Bell" wrote:
> >
> > > Hi
> > >
> > > You don't give the version of SQL Server that you are using! You can write a
> > > stored procedure that will create the database/table if they do not exist and
> > > then pass the database name to a DTS/SSIS package that will load the file.
> > > Using this global variable for the package you can then change the connection
> > > properties.
> > >
> > > You could use OPENXML to load the file and compare the two entries (assuming
> > > the same structure) and FOR XML to produce your output which would not need
> > > DTS/SSIS.
> > >
> > > John
> > >
> > > "Dee" wrote:
> > >
> > > > How can I import an xml file to SQL at the same time every night? I will
> > > > need to create a new database first via the import after that I will be
> > > > appending to the database. Then I need to xport the data into a difference
> > > > xml file.
> > > >
> > > > Do I have to have the orginal xml file on my server or can I point to the
> > > > location of the xml file?
> > > >
> > > > Thank you
> > > > Dee|||Hi Dee
You would be able to run a package on the Std edition that connected to the
Express edition and populated it, but what you are trying to achieve should
be codable in T-SQL without the need for a package, therefore it can be run
from a command prompt and SQLCMD on the machine that is running SQL Express.
This may help http://www.sqlis.com/31.aspx
John
"Dee" wrote:
> But I also have SQL 2005 Standard installed. Can I do an Import/Export from
> there. I also have SQL 2005 Enterprise installed at work. How do I do it
> from there?
> Thanks Dee
> "John Bell" wrote:
> > Hi
> >
> > Import/Export and Integration services is not on the feature list for SQL
> > Express see
> > http://www.microsoft.com/sql/prodinfo/features/compare-features.mspx.
> > Therefore using OPENXML and FOR XML (use BCP or SQLCMD to create a file) is
> > probably the way to go.
> >
> > John
> >
> > "Dee" wrote:
> >
> > > John,
> > >
> > > I am using SQl 2005 on Windows XP. I have the SQl 2005 express installed
> > > and the standard for Windows XP installed.
> > >
> > > Will this work for both.
> > >
> > > Thanks
> > > Dee
> > >
> > > "John Bell" wrote:
> > >
> > > > Hi
> > > >
> > > > You don't give the version of SQL Server that you are using! You can write a
> > > > stored procedure that will create the database/table if they do not exist and
> > > > then pass the database name to a DTS/SSIS package that will load the file.
> > > > Using this global variable for the package you can then change the connection
> > > > properties.
> > > >
> > > > You could use OPENXML to load the file and compare the two entries (assuming
> > > > the same structure) and FOR XML to produce your output which would not need
> > > > DTS/SSIS.
> > > >
> > > > John
> > > >
> > > > "Dee" wrote:
> > > >
> > > > > How can I import an xml file to SQL at the same time every night? I will
> > > > > need to create a new database first via the import after that I will be
> > > > > appending to the database. Then I need to xport the data into a difference
> > > > > xml file.
> > > > >
> > > > > Do I have to have the orginal xml file on my server or can I point to the
> > > > > location of the xml file?
> > > > >
> > > > > Thank you
> > > > > Dee
need to create a new database first via the import after that I will be
appending to the database. Then I need to xport the data into a difference
xml file.
Do I have to have the orginal xml file on my server or can I point to the
location of the xml file?
Thank you
DeeHi
You don't give the version of SQL Server that you are using! You can write a
stored procedure that will create the database/table if they do not exist and
then pass the database name to a DTS/SSIS package that will load the file.
Using this global variable for the package you can then change the connection
properties.
You could use OPENXML to load the file and compare the two entries (assuming
the same structure) and FOR XML to produce your output which would not need
DTS/SSIS.
John
"Dee" wrote:
> How can I import an xml file to SQL at the same time every night? I will
> need to create a new database first via the import after that I will be
> appending to the database. Then I need to xport the data into a difference
> xml file.
> Do I have to have the orginal xml file on my server or can I point to the
> location of the xml file?
> Thank you
> Dee|||John,
I am using SQl 2005 on Windows XP. I have the SQl 2005 express installed
and the standard for Windows XP installed.
Will this work for both.
Thanks
Dee
"John Bell" wrote:
> Hi
> You don't give the version of SQL Server that you are using! You can write a
> stored procedure that will create the database/table if they do not exist and
> then pass the database name to a DTS/SSIS package that will load the file.
> Using this global variable for the package you can then change the connection
> properties.
> You could use OPENXML to load the file and compare the two entries (assuming
> the same structure) and FOR XML to produce your output which would not need
> DTS/SSIS.
> John
> "Dee" wrote:
> > How can I import an xml file to SQL at the same time every night? I will
> > need to create a new database first via the import after that I will be
> > appending to the database. Then I need to xport the data into a difference
> > xml file.
> >
> > Do I have to have the orginal xml file on my server or can I point to the
> > location of the xml file?
> >
> > Thank you
> > Dee|||Hi
Import/Export and Integration services is not on the feature list for SQL
Express see
http://www.microsoft.com/sql/prodinfo/features/compare-features.mspx.
Therefore using OPENXML and FOR XML (use BCP or SQLCMD to create a file) is
probably the way to go.
John
"Dee" wrote:
> John,
> I am using SQl 2005 on Windows XP. I have the SQl 2005 express installed
> and the standard for Windows XP installed.
> Will this work for both.
> Thanks
> Dee
> "John Bell" wrote:
> > Hi
> >
> > You don't give the version of SQL Server that you are using! You can write a
> > stored procedure that will create the database/table if they do not exist and
> > then pass the database name to a DTS/SSIS package that will load the file.
> > Using this global variable for the package you can then change the connection
> > properties.
> >
> > You could use OPENXML to load the file and compare the two entries (assuming
> > the same structure) and FOR XML to produce your output which would not need
> > DTS/SSIS.
> >
> > John
> >
> > "Dee" wrote:
> >
> > > How can I import an xml file to SQL at the same time every night? I will
> > > need to create a new database first via the import after that I will be
> > > appending to the database. Then I need to xport the data into a difference
> > > xml file.
> > >
> > > Do I have to have the orginal xml file on my server or can I point to the
> > > location of the xml file?
> > >
> > > Thank you
> > > Dee|||But I also have SQL 2005 Standard installed. Can I do an Import/Export from
there. I also have SQL 2005 Enterprise installed at work. How do I do it
from there?
Thanks Dee
"John Bell" wrote:
> Hi
> Import/Export and Integration services is not on the feature list for SQL
> Express see
> http://www.microsoft.com/sql/prodinfo/features/compare-features.mspx.
> Therefore using OPENXML and FOR XML (use BCP or SQLCMD to create a file) is
> probably the way to go.
> John
> "Dee" wrote:
> > John,
> >
> > I am using SQl 2005 on Windows XP. I have the SQl 2005 express installed
> > and the standard for Windows XP installed.
> >
> > Will this work for both.
> >
> > Thanks
> > Dee
> >
> > "John Bell" wrote:
> >
> > > Hi
> > >
> > > You don't give the version of SQL Server that you are using! You can write a
> > > stored procedure that will create the database/table if they do not exist and
> > > then pass the database name to a DTS/SSIS package that will load the file.
> > > Using this global variable for the package you can then change the connection
> > > properties.
> > >
> > > You could use OPENXML to load the file and compare the two entries (assuming
> > > the same structure) and FOR XML to produce your output which would not need
> > > DTS/SSIS.
> > >
> > > John
> > >
> > > "Dee" wrote:
> > >
> > > > How can I import an xml file to SQL at the same time every night? I will
> > > > need to create a new database first via the import after that I will be
> > > > appending to the database. Then I need to xport the data into a difference
> > > > xml file.
> > > >
> > > > Do I have to have the orginal xml file on my server or can I point to the
> > > > location of the xml file?
> > > >
> > > > Thank you
> > > > Dee|||Hi Dee
You would be able to run a package on the Std edition that connected to the
Express edition and populated it, but what you are trying to achieve should
be codable in T-SQL without the need for a package, therefore it can be run
from a command prompt and SQLCMD on the machine that is running SQL Express.
This may help http://www.sqlis.com/31.aspx
John
"Dee" wrote:
> But I also have SQL 2005 Standard installed. Can I do an Import/Export from
> there. I also have SQL 2005 Enterprise installed at work. How do I do it
> from there?
> Thanks Dee
> "John Bell" wrote:
> > Hi
> >
> > Import/Export and Integration services is not on the feature list for SQL
> > Express see
> > http://www.microsoft.com/sql/prodinfo/features/compare-features.mspx.
> > Therefore using OPENXML and FOR XML (use BCP or SQLCMD to create a file) is
> > probably the way to go.
> >
> > John
> >
> > "Dee" wrote:
> >
> > > John,
> > >
> > > I am using SQl 2005 on Windows XP. I have the SQl 2005 express installed
> > > and the standard for Windows XP installed.
> > >
> > > Will this work for both.
> > >
> > > Thanks
> > > Dee
> > >
> > > "John Bell" wrote:
> > >
> > > > Hi
> > > >
> > > > You don't give the version of SQL Server that you are using! You can write a
> > > > stored procedure that will create the database/table if they do not exist and
> > > > then pass the database name to a DTS/SSIS package that will load the file.
> > > > Using this global variable for the package you can then change the connection
> > > > properties.
> > > >
> > > > You could use OPENXML to load the file and compare the two entries (assuming
> > > > the same structure) and FOR XML to produce your output which would not need
> > > > DTS/SSIS.
> > > >
> > > > John
> > > >
> > > > "Dee" wrote:
> > > >
> > > > > How can I import an xml file to SQL at the same time every night? I will
> > > > > need to create a new database first via the import after that I will be
> > > > > appending to the database. Then I need to xport the data into a difference
> > > > > xml file.
> > > > >
> > > > > Do I have to have the orginal xml file on my server or can I point to the
> > > > > location of the xml file?
> > > > >
> > > > > Thank you
> > > > > Dee
Sunday, March 25, 2012
automatically restarting merge replication after time out
Hello,
every few days my merge replication fails due to a network problem and
subsequent time out. How can I trigger a replication restart automatically,
or at a certain time of day?
Bill,
you can change the workflow properties on your merge agent's job so that the
third step on completion goes to the first step.
Alternatively, you could avoid continuous replication and have the merge
agent run on a schedule.
HTH,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
every few days my merge replication fails due to a network problem and
subsequent time out. How can I trigger a replication restart automatically,
or at a certain time of day?
Bill,
you can change the workflow properties on your merge agent's job so that the
third step on completion goes to the first step.
Alternatively, you could avoid continuous replication and have the merge
agent run on a schedule.
HTH,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
Thursday, March 22, 2012
Automatically backup....?
//[Microsoft][ODBC SQL Server Driver][SQL Server]Repair
statement not processed. Database needs to be in single
user mode.
** Execution Time: 0 hrs, 0 mins, 1 secs **
End of maintenance plan 'Cybermurid maintenance' on
7/20/2004 5:55:00 AM
SQLMAINT.EXE Process Exit Code: 1 (Failed)//
How do I set automatically backup in SQL server?
Niky wrote:
> //[Microsoft][ODBC SQL Server Driver][SQL Server]Repair
> statement not processed. Database needs to be in single
> user mode.
> ** Execution Time: 0 hrs, 0 mins, 1 secs **
> End of maintenance plan 'Cybermurid maintenance' on
> 7/20/2004 5:55:00 AM
> SQLMAINT.EXE Process Exit Code: 1 (Failed)//
> How do I set automatically backup in SQL server?
Hi Niky,
you can create a new job (under SQL Agent, in Enterprise Manager):
- the activity you must add is a T-SQL script, with the instruction
"BACKUP DATABASE MyDb TO [DISK|TAPE]...".
- schedule it
David
|||Hello
I believe that you are running into the bug mentioned in the following
article :
290622 BUG: Database Maintenance Plan on System Databases Fails on Integrity
http://support.microsoft.com/?id=290622
The workaround is to disable the "Attempt to repair minor problems" option
in the Maintenance plan properties. This should stop generating the message
that you have posted.
Thank you for using Microsoft newsgroups.
Sincerely
Pankaj Agarwal
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.
statement not processed. Database needs to be in single
user mode.
** Execution Time: 0 hrs, 0 mins, 1 secs **
End of maintenance plan 'Cybermurid maintenance' on
7/20/2004 5:55:00 AM
SQLMAINT.EXE Process Exit Code: 1 (Failed)//
How do I set automatically backup in SQL server?
Niky wrote:
> //[Microsoft][ODBC SQL Server Driver][SQL Server]Repair
> statement not processed. Database needs to be in single
> user mode.
> ** Execution Time: 0 hrs, 0 mins, 1 secs **
> End of maintenance plan 'Cybermurid maintenance' on
> 7/20/2004 5:55:00 AM
> SQLMAINT.EXE Process Exit Code: 1 (Failed)//
> How do I set automatically backup in SQL server?
Hi Niky,
you can create a new job (under SQL Agent, in Enterprise Manager):
- the activity you must add is a T-SQL script, with the instruction
"BACKUP DATABASE MyDb TO [DISK|TAPE]...".
- schedule it
David
|||Hello
I believe that you are running into the bug mentioned in the following
article :
290622 BUG: Database Maintenance Plan on System Databases Fails on Integrity
http://support.microsoft.com/?id=290622
The workaround is to disable the "Attempt to repair minor problems" option
in the Maintenance plan properties. This should stop generating the message
that you have posted.
Thank you for using Microsoft newsgroups.
Sincerely
Pankaj Agarwal
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.
Automatically backup....?
//[Microsoft][ODBC SQL Server Driver][SQL Server]Repair
statement not processed. Database needs to be in single
user mode.
** Execution Time: 0 hrs, 0 mins, 1 secs **
End of maintenance plan 'Cybermurid maintenance' on
7/20/2004 5:55:00 AM
SQLMAINT.EXE Process Exit Code: 1 (Failed)//
How do I set automatically backup in SQL server?Niky wrote:
> //[Microsoft][ODBC SQL Server Driver][SQL Server]Repair
> statement not processed. Database needs to be in single
> user mode.
> ** Execution Time: 0 hrs, 0 mins, 1 secs **
> End of maintenance plan 'Cybermurid maintenance' on
> 7/20/2004 5:55:00 AM
> SQLMAINT.EXE Process Exit Code: 1 (Failed)//
> How do I set automatically backup in SQL server?
Hi Niky,
you can create a new job (under SQL Agent, in Enterprise Manager):
- the activity you must add is a T-SQL script, with the instruction
"BACKUP DATABASE MyDb TO [DISK|TAPE]...".
- schedule it
David|||Hello
I believe that you are running into the bug mentioned in the following
article :
290622 BUG: Database Maintenance Plan on System Databases Fails on Integrity
http://support.microsoft.com/?id=290622
The workaround is to disable the "Attempt to repair minor problems" option
in the Maintenance plan properties. This should stop generating the message
that you have posted.
Thank you for using Microsoft newsgroups.
Sincerely
Pankaj Agarwal
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.
statement not processed. Database needs to be in single
user mode.
** Execution Time: 0 hrs, 0 mins, 1 secs **
End of maintenance plan 'Cybermurid maintenance' on
7/20/2004 5:55:00 AM
SQLMAINT.EXE Process Exit Code: 1 (Failed)//
How do I set automatically backup in SQL server?Niky wrote:
> //[Microsoft][ODBC SQL Server Driver][SQL Server]Repair
> statement not processed. Database needs to be in single
> user mode.
> ** Execution Time: 0 hrs, 0 mins, 1 secs **
> End of maintenance plan 'Cybermurid maintenance' on
> 7/20/2004 5:55:00 AM
> SQLMAINT.EXE Process Exit Code: 1 (Failed)//
> How do I set automatically backup in SQL server?
Hi Niky,
you can create a new job (under SQL Agent, in Enterprise Manager):
- the activity you must add is a T-SQL script, with the instruction
"BACKUP DATABASE MyDb TO [DISK|TAPE]...".
- schedule it
David|||Hello
I believe that you are running into the bug mentioned in the following
article :
290622 BUG: Database Maintenance Plan on System Databases Fails on Integrity
http://support.microsoft.com/?id=290622
The workaround is to disable the "Attempt to repair minor problems" option
in the Maintenance plan properties. This should stop generating the message
that you have posted.
Thank you for using Microsoft newsgroups.
Sincerely
Pankaj Agarwal
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.
Automatic updating of datetime field
I need to automatically update a datetime field for a record to the current time whenever the record is updated.
create table t (
id bigint identity(1,1) not null primary key,
name varchar(50),
value varchar(50),
ts datetime not null default getutcdate()
)
go
insert t (name, value) values ('fred', 'bob')
go
update t set value='robert' where id=1 and name='fred'
go
One option would be to use an instead of update trigger.
create trigger update_t on t
instead of update as
update t set ts=getutcdate(),name=inserted.name, value=inserted.value from t inner join inserted on t.id=inserted.id
go
update t set value='dick' where id=1 and name='fred'
go
Sounds like I've solved my own problem, heh? Well, here's the catch ... you can't know the names of the other columns at the time you write the trigger. I.e. you only know that there is a ts field that needs to be updated internally, otherwise you want the update to do the same thing it would normally do.
Any ideas?...also, you don't know what database server it is going to be running on, so it has to be platform-independent.
...and it needs to be fully compatible with the Mayan calendar as well.
...oh yeah, and the final code must be a palindrome that reads the same way forwards as backwards! Yeah, that's it! What a kick-ass application design! Whooooo-eeeeeeee!sql
create table t (
id bigint identity(1,1) not null primary key,
name varchar(50),
value varchar(50),
ts datetime not null default getutcdate()
)
go
insert t (name, value) values ('fred', 'bob')
go
update t set value='robert' where id=1 and name='fred'
go
One option would be to use an instead of update trigger.
create trigger update_t on t
instead of update as
update t set ts=getutcdate(),name=inserted.name, value=inserted.value from t inner join inserted on t.id=inserted.id
go
update t set value='dick' where id=1 and name='fred'
go
Sounds like I've solved my own problem, heh? Well, here's the catch ... you can't know the names of the other columns at the time you write the trigger. I.e. you only know that there is a ts field that needs to be updated internally, otherwise you want the update to do the same thing it would normally do.
Any ideas?...also, you don't know what database server it is going to be running on, so it has to be platform-independent.
...and it needs to be fully compatible with the Mayan calendar as well.
...oh yeah, and the final code must be a palindrome that reads the same way forwards as backwards! Yeah, that's it! What a kick-ass application design! Whooooo-eeeeeeee!sql
Tuesday, March 20, 2012
Automatic trigger on a specific time
Hi!
I use SQL Server Express 2005 and would like that a trigger is fired automatically at a specific time. I have been developing a game which has a lot of periods with a start date and an end date. Every time a periode is finished the trigger should add new records for the next periode. The dates of the periodes are already setted at the beginning of the game. For example:
periode startdate enddate
1 23.08.2006, 15:00 24.08.2006, 17:00
2 24.08 .2006, 17:00 25.08.2006, 08:00
and so on
Can anyone help me how to do this? Thanks!
You can create a maintenance job that runs every few hours or every few days and call a stored proc and put your logic in the stored proc.
|||Hi ndinakar,
thx for your reply!
I know that jobs are an alternative but there are two problems: firstly it seems, that jobs are not available in SQL Server Express Version and secondly the trigger should actually fire on a specific time that could be every minute. So I would have to fire a job every minute but I think, that might not be a good idea.
automatic transaction - Stored procedure - @@identity
Hi, i am having a hard time understanding the concept maybe .
i am using automatic transaction as such:
[AutoComplete]
public void SaveInvoiceAndCharges()
{
//try
//{
DSPinvoiceandcharges.Tainvoice.Update(DSinvoiceAndcharges.Invoice);
// DSPinvoiceandcharges.Tainvoicecharge.Update(DSinvoiceAndcharges.InvoiceCharge);
//}
//catch (Exception e)
//{
// throw e;
//}
}
and my sp :
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [dbo].[Invoice_New]
(
@.DateIssued datetime,
@.Reference nvarchar(50),
@.Note nvarchar(50),
@.DocumentType int,
@.CompanyCode int,
@.Valid nchar(1),
@.Fees int,
@.isDistributed nchar(1)
)
AS
SET NOCOUNT OFF;
Begin try
INSERT INTO [Invoice].[Invoice] ([DateIssued], [Reference], [Note],
[DocumentType], [CompanyCode], [Valid], [Fees], [isDistributed])
VALUES (@.DateIssued, @.Reference, @.Note, @.DocumentType, @.CompanyCode,
@.Valid, @.Fees, @.isDistributed);
return @.@.identity
End try
begin catch
raiserror('Test - Error', 16, 1 )
End catch
.. the code being run is not actually throwing any error.
DSPinvoiceandcharges.Tainvoice.Update(DSinvoiceAndcharges.Invoice);
updates but actually just does nothing and i lose the invalid row.
what is happening here please?
thank you ,
hrub
hrubesh:
I am not sure what else is going on but most likely you should be returning SCOPE_IDENTITY() instead of @.@.identity. Also, what are you getting for a return code?
|||Hi, thanks for your reply,
i did not understand what u mean by what am i getting for a return code.
|||Hrubesh:
You have this line in your code:
Code Snippet
return @.@.identity
How are you invoking the stored procedure? And after the execution of the stored procedure completes, what is the value returned by the stored procedure? Or are you just ignoring the return value? Finally, what do you mean by "it just does nothing." And if you don't know what the return value is, how do you know that "it is just doing nothing."
|||hi,
i have been searching on the issue, and this is what i believe now it is not an issue with the sql part of it.
exception unhandled by user code in serviced components is what i am looking into now.
thanks for your help,
btw why the difference between @.@.identity and SCOPE_IDENTITY() ..
i have been using @.@.identity so that it automatically updates my parent row and child row with the new id , i am using
DSPinvoiceandcharges.Tainvoice.Update(DSinvoiceAndcharges.Invoice);
DSPinvoiceandcharges.Tainvoicecharge.Update(DSinvoiceAndcharges.InvoiceCharge);
invoice is the parent table and invoicecharge the child, and it works.
i will try the scope_identity asa i get the exception unhandled workaround. ..
yep so how i am invoking the sp is that Tainvoice is the sql data adapter that i generated using the wizard in my dataset, that created stored procedures on an sql command, and it automatically calls the corresponding insert/upd/del sp.
thanks a lot.
|||@.@.identity will frequently work but it is not technically the correct choice. I gave an example of how @.@.identity can go wrong in this thread last year:
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=880725&SiteID=1
Another thread in which I discussed with my friend Craig was here:
|||http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=793185&SiteID=1
thanks kent.
Automatic SQL db update at set time?
I would like to limit the number of pages a user can view on my website each day. The users logs in and I can count the number of pages viewd in a field but i want to know how i can set the page count field to reset to 0 at the end of the day (ie midnight). Is it possible to do this? and if so how? Thanks.You can set up a job to run a stored proc at that time every day using SQL Server Agent (find it in SQL Server Enterprise Manager)
A better way might be to log pageviews with a timestamp, and then you can allow X pages within any 24 hour period - simply count the pageviews in the log newer than getdate() - 1 and check it against the limit.
Does that help?|||The server will be a shared sql server and i don't have access to creating new jobs, so I think your second suggestion would be best but not sure how to implement it. Do you have an example or a link to where I can find an example? Thanks|||Normally that should not be a problem - I use a shared database server (one of those cheap .net hosters) and I can create jobs just fine.
Think about my other solution if you really can't create jobs - it's better (I believe) and it does not require a scheduled job.
Check BOL for examples of creating jobs.
Automatic Server restart ! very strange
Hi Friends,
Today 8 of my sql servers restarted at the same time. very strange
situation.
I cheked for spyware and virus and they are clean, all are patched and
I don't know any other thing.
Did this happen to anyone else ?
Thanks,
PHave you checked the power? I know this may sound base but I've seen
entire racks restart because a UPS battery was going bad.|||Did you check your Event Viewer logs?
--
Ekrem Önsoy
"Pat.Rick" <PatrickAlexander.email@.gmail.com> wrote in message
news:1192038021.582462.98140@.19g2000hsx.googlegroups.com...
> Hi Friends,
> Today 8 of my sql servers restarted at the same time. very strange
> situation.
> I cheked for spyware and virus and they are clean, all are patched and
> I don't know any other thing.
> Did this happen to anyone else ?
> Thanks,
> P
>
Today 8 of my sql servers restarted at the same time. very strange
situation.
I cheked for spyware and virus and they are clean, all are patched and
I don't know any other thing.
Did this happen to anyone else ?
Thanks,
PHave you checked the power? I know this may sound base but I've seen
entire racks restart because a UPS battery was going bad.|||Did you check your Event Viewer logs?
--
Ekrem Önsoy
"Pat.Rick" <PatrickAlexander.email@.gmail.com> wrote in message
news:1192038021.582462.98140@.19g2000hsx.googlegroups.com...
> Hi Friends,
> Today 8 of my sql servers restarted at the same time. very strange
> situation.
> I cheked for spyware and virus and they are clean, all are patched and
> I don't know any other thing.
> Did this happen to anyone else ?
> Thanks,
> P
>
Automatic Refresh Data
Is there any way in Reporting Service so that It will automatic
refresh data in the report with specified time interval ?
ThanksOn Mar 30, 11:35 pm, sqlpr...@.gmail.com wrote:
> Is there any way in Reporting Service so that It will automatic
> refresh data in the report with specified time interval ?
> Thanks
Yes, in the Reports Project, select the 'Layout' tab. Then select the
'Report' tab at the top and then 'Report Properties...' On the
'General' tab, select 'Auto Refresh' near the bottom and select the
number of seconds between refreshes.
Regards,
Enrique Martinez
Sr. Software Developer|||On Apr 1, 7:46 am, "EMartinez" <emartinez...@.gmail.com> wrote:
> On Mar 30, 11:35 pm, sqlpr...@.gmail.com wrote:
> > Is there any way in Reporting Service so that It will automatic
> > refresh data in the report with specified time interval ?
> > Thanks
> Yes, in the Reports Project, select the 'Layout' tab. Then select the
> 'Report' tab at the top and then 'Report Properties...' On the
> 'General' tab, select 'Auto Refresh' near the bottom and select the
> number of seconds between refreshes.
> Regards,
> Enrique Martinez
> Sr. Software Developer
Thanks
it has done my work.|||On Apr 1, 12:47 am, sqlpr...@.gmail.com wrote:
> On Apr 1, 7:46 am, "EMartinez" <emartinez...@.gmail.com> wrote:
>
> > On Mar 30, 11:35 pm, sqlpr...@.gmail.com wrote:
> > > Is there any way in Reporting Service so that It will automatic
> > > refresh data in the report with specified time interval ?
> > > Thanks
> > Yes, in the Reports Project, select the 'Layout' tab. Then select the
> > 'Report' tab at the top and then 'Report Properties...' On the
> > 'General' tab, select 'Auto Refresh' near the bottom and select the
> > number of seconds between refreshes.
> > Regards,
> > Enrique Martinez
> > Sr. Software Developer
> Thanks
> it has done my work.
You're welcome. Glad I could be of assistance.
Regards,
Enrique Martinez
Sr. Software Developersql
refresh data in the report with specified time interval ?
ThanksOn Mar 30, 11:35 pm, sqlpr...@.gmail.com wrote:
> Is there any way in Reporting Service so that It will automatic
> refresh data in the report with specified time interval ?
> Thanks
Yes, in the Reports Project, select the 'Layout' tab. Then select the
'Report' tab at the top and then 'Report Properties...' On the
'General' tab, select 'Auto Refresh' near the bottom and select the
number of seconds between refreshes.
Regards,
Enrique Martinez
Sr. Software Developer|||On Apr 1, 7:46 am, "EMartinez" <emartinez...@.gmail.com> wrote:
> On Mar 30, 11:35 pm, sqlpr...@.gmail.com wrote:
> > Is there any way in Reporting Service so that It will automatic
> > refresh data in the report with specified time interval ?
> > Thanks
> Yes, in the Reports Project, select the 'Layout' tab. Then select the
> 'Report' tab at the top and then 'Report Properties...' On the
> 'General' tab, select 'Auto Refresh' near the bottom and select the
> number of seconds between refreshes.
> Regards,
> Enrique Martinez
> Sr. Software Developer
Thanks
it has done my work.|||On Apr 1, 12:47 am, sqlpr...@.gmail.com wrote:
> On Apr 1, 7:46 am, "EMartinez" <emartinez...@.gmail.com> wrote:
>
> > On Mar 30, 11:35 pm, sqlpr...@.gmail.com wrote:
> > > Is there any way in Reporting Service so that It will automatic
> > > refresh data in the report with specified time interval ?
> > > Thanks
> > Yes, in the Reports Project, select the 'Layout' tab. Then select the
> > 'Report' tab at the top and then 'Report Properties...' On the
> > 'General' tab, select 'Auto Refresh' near the bottom and select the
> > number of seconds between refreshes.
> > Regards,
> > Enrique Martinez
> > Sr. Software Developer
> Thanks
> it has done my work.
You're welcome. Glad I could be of assistance.
Regards,
Enrique Martinez
Sr. Software Developersql
Monday, March 19, 2012
Automatic Query Generator
In my final project, the biggest time consuming issue was writing a query, when there are 15 fields on the average than it is certainly not easy to write an SQL statement and run it (through code)So I want to know, is there any automatic query generator ?that could work like:1) We enter all control names 1 by 12) we enter postfix text ( like .Trim(), .ToString() ) etc3) Result should be automatically generated queryIs there any software/program for that ?Infact it is very easy to develop myself, but I dont want to waste the time if such tool (most probably) already exists
A bit of advance. I figure your in school and by "final project" you mean your senior project or something along those lines. I recommend not using any "auto-generation" code tools until you are very familiar with hand writing all the code manually yourself during the learning stages. I too liked all of the visual tools while in school and used them when I had the chance (wizards, design view, etc.) Although it does make things quite easy, in the long run, it hurts no one but yourself. I've learned from experience. When changes are needed or additions, its a lot easier to go into the source of the code, or code behind to change it and not having to deal with the designer at all.
So now, I work about 95% from the source view and I stopped using the designer view. Since then I have really started to understand and learn A LOT more than the simple auto-generation or wizard methods. I mean, when there are time constraints and other variables involved, it could be benefical to use the easy methods, but if you really dont understand wants going on and the code in the back-end, any changes that are needed could take twice as long to figure out and change in the long-run. And most of the time there are always changes to a project, so understanding it key. Also, many of these auto-generated tools add a lot of "junk" in your code that you dont use and makes changes that much harder to understand.
Just a bit of advice, ive learned from doing it the "Wrong" way and then re-learning the stuff and doing it the "Right" way. Writing clean and understandable code is a must and best practice.
Anyone can write code, but if its not readable and maintainable to another person, its pretty much crap, in my eyes at least. Just keep this in mind while you develop.
Sorry if I got a little off the topic of your question. You might know a lot more than myself and are just looking for a tool to save yourself some time, but this is just a handy tip to consider, for I graduated a little over a year ago and matured a lot as a developer by trying to follow "best practices".
Automatic Purge of Transaction Log Data?
Hello,
I have a database that I am setting up in SQL Server 2005. Initially, I am doing very large imports of data. Every time I run an import, I am having the increase the size of my transaction logs, and now they are approaching 2 GB. Should these be purging themselves? I have to keep increasing the max size of the log so that I can get my data in. While this will work for now, it is not a long term solution, because I can see the log size growing quite large and the amount of space on the server obviously isn't infinite. Is there a setting that I can change so they will automatically purge? If not, how do I purge this information myself?
Thanks so much!
Christine
The management of the transaction log depends on the recovery model used in your database. There is a good overview on http://msdn2.microsoft.com/en-us/library/ms189275.aspx about the available recovery models.
With a full recovery model, you will need to backup your transaction log to reclaim its space. With a simple recovery model, the system will do this for you, but there is a greater risk for data loss - be sure to make the right tradeoff for your database!
For the problem of large imports filling up the log space, you might also want to look into switching to bulk-logged recovery for the duration of the imports, although you might not need to do this if the one of the other models works for you after the log is under control (reclaimed by the system or by regular backups).
Another SQL BOL reference that will probably come in handy is Truncating the Transaction Log at http://msdn2.microsoft.com/en-us/library/ms189085.aspx, which explains a bit more about how log truncation works.
|||Here are some good resources that will help you better understand and control the Transaction Log:
FileSize -How to stop the log file from growing
http://www.support.microsoft.com/?id=873235
FileSize -Log file filling up
http://www.support.microsoft.com/?id=110139
FileSize -Log File Grows too big
http://www.support.microsoft.com/?id=317375
FileSize -Log File issues
http://www.nigelrivett.net/TransactionLogFileGrows_1.html
FileSize -Shrinking Log in SQL Server 2000 with DBCC SHRINKFILE
http://www.support.microsoft.com/?id=272318
And especially, this article about why cautions about reducing the Log file size is worth reading:
FileSize -DB Shrink Issues
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
Automatic file grown
Hello:
I'm experiencing this problem with SQL 2000. I have a large DB (7 GB) and
fromtime to time every statement trying to insert or update data gets
blocked and dies with timeout. Looking for more detail I have found that
this happens when SQL server is growing its files. The DB is configured to
grow automatically (+100 MB) every time needed, and has no size limit.
To correct this lock, I run a INSERT statement from SQL query analizer, wait
for a minute and the statement is correctly executed, the server unlocked
and my programs continue inserting data. INSERT statements launched from
programs die with timeout and don't solve the problem.
I'd like to have more control over DB grown. Where can I see when has the DB
grown? Can I force file grown when I want, ata some fewer-work times? Where
can I find more information about this?
Thanks in advance, best regardsAuto-grow should be an emergency fail-safe only. You should actively
monitor the database sizes and grow them ahead of time in an off-peak hour.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Robert T." <r_t@.nospam.nospam> wrote in message
news:ej5%23rhqjEHA.3624@.TK2MSFTNGP10.phx.gbl...
> Hello:
> I'm experiencing this problem with SQL 2000. I have a large DB (7 GB) and
> fromtime to time every statement trying to insert or update data gets
> blocked and dies with timeout. Looking for more detail I have found that
> this happens when SQL server is growing its files. The DB is configured to
> grow automatically (+100 MB) every time needed, and has no size limit.
> To correct this lock, I run a INSERT statement from SQL query analizer,
wait
> for a minute and the statement is correctly executed, the server unlocked
> and my programs continue inserting data. INSERT statements launched from
> programs die with timeout and don't solve the problem.
> I'd like to have more control over DB grown. Where can I see when has the
DB
> grown? Can I force file grown when I want, ata some fewer-work times?
Where
> can I find more information about this?
> Thanks in advance, best regards
>|||In addition to Geoff's post, you find a couple of stored procedures at .com" target="_blank">www.dbmaint
.com (see the "free
utilities section" for jobs that can serve as inspiration...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Robert T." <r_t@.nospam.nospam> wrote in message news:ej5%23rhqjEHA.3624@.TK2MSFTNGP10.phx.gb
l...
> Hello:
> I'm experiencing this problem with SQL 2000. I have a large DB (7 GB) and
> fromtime to time every statement trying to insert or update data gets
> blocked and dies with timeout. Looking for more detail I have found that
> this happens when SQL server is growing its files. The DB is configured to
> grow automatically (+100 MB) every time needed, and has no size limit.
> To correct this lock, I run a INSERT statement from SQL query analizer, wa
it
> for a minute and the statement is correctly executed, the server unlocked
> and my programs continue inserting data. INSERT statements launched from
> programs die with timeout and don't solve the problem.
> I'd like to have more control over DB grown. Where can I see when has the
DB
> grown? Can I force file grown when I want, ata some fewer-work times? Wher
e
> can I find more information about this?
> Thanks in advance, best regards
>
I'm experiencing this problem with SQL 2000. I have a large DB (7 GB) and
fromtime to time every statement trying to insert or update data gets
blocked and dies with timeout. Looking for more detail I have found that
this happens when SQL server is growing its files. The DB is configured to
grow automatically (+100 MB) every time needed, and has no size limit.
To correct this lock, I run a INSERT statement from SQL query analizer, wait
for a minute and the statement is correctly executed, the server unlocked
and my programs continue inserting data. INSERT statements launched from
programs die with timeout and don't solve the problem.
I'd like to have more control over DB grown. Where can I see when has the DB
grown? Can I force file grown when I want, ata some fewer-work times? Where
can I find more information about this?
Thanks in advance, best regardsAuto-grow should be an emergency fail-safe only. You should actively
monitor the database sizes and grow them ahead of time in an off-peak hour.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Robert T." <r_t@.nospam.nospam> wrote in message
news:ej5%23rhqjEHA.3624@.TK2MSFTNGP10.phx.gbl...
> Hello:
> I'm experiencing this problem with SQL 2000. I have a large DB (7 GB) and
> fromtime to time every statement trying to insert or update data gets
> blocked and dies with timeout. Looking for more detail I have found that
> this happens when SQL server is growing its files. The DB is configured to
> grow automatically (+100 MB) every time needed, and has no size limit.
> To correct this lock, I run a INSERT statement from SQL query analizer,
wait
> for a minute and the statement is correctly executed, the server unlocked
> and my programs continue inserting data. INSERT statements launched from
> programs die with timeout and don't solve the problem.
> I'd like to have more control over DB grown. Where can I see when has the
DB
> grown? Can I force file grown when I want, ata some fewer-work times?
Where
> can I find more information about this?
> Thanks in advance, best regards
>|||In addition to Geoff's post, you find a couple of stored procedures at .com" target="_blank">www.dbmaint
.com (see the "free
utilities section" for jobs that can serve as inspiration...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Robert T." <r_t@.nospam.nospam> wrote in message news:ej5%23rhqjEHA.3624@.TK2MSFTNGP10.phx.gb
l...
> Hello:
> I'm experiencing this problem with SQL 2000. I have a large DB (7 GB) and
> fromtime to time every statement trying to insert or update data gets
> blocked and dies with timeout. Looking for more detail I have found that
> this happens when SQL server is growing its files. The DB is configured to
> grow automatically (+100 MB) every time needed, and has no size limit.
> To correct this lock, I run a INSERT statement from SQL query analizer, wa
it
> for a minute and the statement is correctly executed, the server unlocked
> and my programs continue inserting data. INSERT statements launched from
> programs die with timeout and don't solve the problem.
> I'd like to have more control over DB grown. Where can I see when has the
DB
> grown? Can I force file grown when I want, ata some fewer-work times? Wher
e
> can I find more information about this?
> Thanks in advance, best regards
>
Automatic file grown
Hello:
I'm experiencing this problem with SQL 2000. I have a large DB (7 GB) and
fromtime to time every statement trying to insert or update data gets
blocked and dies with timeout. Looking for more detail I have found that
this happens when SQL server is growing its files. The DB is configured to
grow automatically (+100 MB) every time needed, and has no size limit.
To correct this lock, I run a INSERT statement from SQL query analizer, wait
for a minute and the statement is correctly executed, the server unlocked
and my programs continue inserting data. INSERT statements launched from
programs die with timeout and don't solve the problem.
I'd like to have more control over DB grown. Where can I see when has the DB
grown? Can I force file grown when I want, ata some fewer-work times? Where
can I find more information about this?
Thanks in advance, best regards
Auto-grow should be an emergency fail-safe only. You should actively
monitor the database sizes and grow them ahead of time in an off-peak hour.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Robert T." <r_t@.nospam.nospam> wrote in message
news:ej5%23rhqjEHA.3624@.TK2MSFTNGP10.phx.gbl...
> Hello:
> I'm experiencing this problem with SQL 2000. I have a large DB (7 GB) and
> fromtime to time every statement trying to insert or update data gets
> blocked and dies with timeout. Looking for more detail I have found that
> this happens when SQL server is growing its files. The DB is configured to
> grow automatically (+100 MB) every time needed, and has no size limit.
> To correct this lock, I run a INSERT statement from SQL query analizer,
wait
> for a minute and the statement is correctly executed, the server unlocked
> and my programs continue inserting data. INSERT statements launched from
> programs die with timeout and don't solve the problem.
> I'd like to have more control over DB grown. Where can I see when has the
DB
> grown? Can I force file grown when I want, ata some fewer-work times?
Where
> can I find more information about this?
> Thanks in advance, best regards
>
|||In addition to Geoff's post, you find a couple of stored procedures at www.dbmaint.com (see the "free
utilities section" for jobs that can serve as inspiration...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Robert T." <r_t@.nospam.nospam> wrote in message news:ej5%23rhqjEHA.3624@.TK2MSFTNGP10.phx.gbl...
> Hello:
> I'm experiencing this problem with SQL 2000. I have a large DB (7 GB) and
> fromtime to time every statement trying to insert or update data gets
> blocked and dies with timeout. Looking for more detail I have found that
> this happens when SQL server is growing its files. The DB is configured to
> grow automatically (+100 MB) every time needed, and has no size limit.
> To correct this lock, I run a INSERT statement from SQL query analizer, wait
> for a minute and the statement is correctly executed, the server unlocked
> and my programs continue inserting data. INSERT statements launched from
> programs die with timeout and don't solve the problem.
> I'd like to have more control over DB grown. Where can I see when has the DB
> grown? Can I force file grown when I want, ata some fewer-work times? Where
> can I find more information about this?
> Thanks in advance, best regards
>
I'm experiencing this problem with SQL 2000. I have a large DB (7 GB) and
fromtime to time every statement trying to insert or update data gets
blocked and dies with timeout. Looking for more detail I have found that
this happens when SQL server is growing its files. The DB is configured to
grow automatically (+100 MB) every time needed, and has no size limit.
To correct this lock, I run a INSERT statement from SQL query analizer, wait
for a minute and the statement is correctly executed, the server unlocked
and my programs continue inserting data. INSERT statements launched from
programs die with timeout and don't solve the problem.
I'd like to have more control over DB grown. Where can I see when has the DB
grown? Can I force file grown when I want, ata some fewer-work times? Where
can I find more information about this?
Thanks in advance, best regards
Auto-grow should be an emergency fail-safe only. You should actively
monitor the database sizes and grow them ahead of time in an off-peak hour.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Robert T." <r_t@.nospam.nospam> wrote in message
news:ej5%23rhqjEHA.3624@.TK2MSFTNGP10.phx.gbl...
> Hello:
> I'm experiencing this problem with SQL 2000. I have a large DB (7 GB) and
> fromtime to time every statement trying to insert or update data gets
> blocked and dies with timeout. Looking for more detail I have found that
> this happens when SQL server is growing its files. The DB is configured to
> grow automatically (+100 MB) every time needed, and has no size limit.
> To correct this lock, I run a INSERT statement from SQL query analizer,
wait
> for a minute and the statement is correctly executed, the server unlocked
> and my programs continue inserting data. INSERT statements launched from
> programs die with timeout and don't solve the problem.
> I'd like to have more control over DB grown. Where can I see when has the
DB
> grown? Can I force file grown when I want, ata some fewer-work times?
Where
> can I find more information about this?
> Thanks in advance, best regards
>
|||In addition to Geoff's post, you find a couple of stored procedures at www.dbmaint.com (see the "free
utilities section" for jobs that can serve as inspiration...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Robert T." <r_t@.nospam.nospam> wrote in message news:ej5%23rhqjEHA.3624@.TK2MSFTNGP10.phx.gbl...
> Hello:
> I'm experiencing this problem with SQL 2000. I have a large DB (7 GB) and
> fromtime to time every statement trying to insert or update data gets
> blocked and dies with timeout. Looking for more detail I have found that
> this happens when SQL server is growing its files. The DB is configured to
> grow automatically (+100 MB) every time needed, and has no size limit.
> To correct this lock, I run a INSERT statement from SQL query analizer, wait
> for a minute and the statement is correctly executed, the server unlocked
> and my programs continue inserting data. INSERT statements launched from
> programs die with timeout and don't solve the problem.
> I'd like to have more control over DB grown. Where can I see when has the DB
> grown? Can I force file grown when I want, ata some fewer-work times? Where
> can I find more information about this?
> Thanks in advance, best regards
>
Automatic file grown
Hello:
I'm experiencing this problem with SQL 2000. I have a large DB (7 GB) and
fromtime to time every statement trying to insert or update data gets
blocked and dies with timeout. Looking for more detail I have found that
this happens when SQL server is growing its files. The DB is configured to
grow automatically (+100 MB) every time needed, and has no size limit.
To correct this lock, I run a INSERT statement from SQL query analizer, wait
for a minute and the statement is correctly executed, the server unlocked
and my programs continue inserting data. INSERT statements launched from
programs die with timeout and don't solve the problem.
I'd like to have more control over DB grown. Where can I see when has the DB
grown? Can I force file grown when I want, ata some fewer-work times? Where
can I find more information about this?
Thanks in advance, best regardsAuto-grow should be an emergency fail-safe only. You should actively
monitor the database sizes and grow them ahead of time in an off-peak hour.
--
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Robert T." <r_t@.nospam.nospam> wrote in message
news:ej5%23rhqjEHA.3624@.TK2MSFTNGP10.phx.gbl...
> Hello:
> I'm experiencing this problem with SQL 2000. I have a large DB (7 GB) and
> fromtime to time every statement trying to insert or update data gets
> blocked and dies with timeout. Looking for more detail I have found that
> this happens when SQL server is growing its files. The DB is configured to
> grow automatically (+100 MB) every time needed, and has no size limit.
> To correct this lock, I run a INSERT statement from SQL query analizer,
wait
> for a minute and the statement is correctly executed, the server unlocked
> and my programs continue inserting data. INSERT statements launched from
> programs die with timeout and don't solve the problem.
> I'd like to have more control over DB grown. Where can I see when has the
DB
> grown? Can I force file grown when I want, ata some fewer-work times?
Where
> can I find more information about this?
> Thanks in advance, best regards
>|||In addition to Geoff's post, you find a couple of stored procedures at www.dbmaint.com (see the "free
utilities section" for jobs that can serve as inspiration...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Robert T." <r_t@.nospam.nospam> wrote in message news:ej5%23rhqjEHA.3624@.TK2MSFTNGP10.phx.gbl...
> Hello:
> I'm experiencing this problem with SQL 2000. I have a large DB (7 GB) and
> fromtime to time every statement trying to insert or update data gets
> blocked and dies with timeout. Looking for more detail I have found that
> this happens when SQL server is growing its files. The DB is configured to
> grow automatically (+100 MB) every time needed, and has no size limit.
> To correct this lock, I run a INSERT statement from SQL query analizer, wait
> for a minute and the statement is correctly executed, the server unlocked
> and my programs continue inserting data. INSERT statements launched from
> programs die with timeout and don't solve the problem.
> I'd like to have more control over DB grown. Where can I see when has the DB
> grown? Can I force file grown when I want, ata some fewer-work times? Where
> can I find more information about this?
> Thanks in advance, best regards
>
I'm experiencing this problem with SQL 2000. I have a large DB (7 GB) and
fromtime to time every statement trying to insert or update data gets
blocked and dies with timeout. Looking for more detail I have found that
this happens when SQL server is growing its files. The DB is configured to
grow automatically (+100 MB) every time needed, and has no size limit.
To correct this lock, I run a INSERT statement from SQL query analizer, wait
for a minute and the statement is correctly executed, the server unlocked
and my programs continue inserting data. INSERT statements launched from
programs die with timeout and don't solve the problem.
I'd like to have more control over DB grown. Where can I see when has the DB
grown? Can I force file grown when I want, ata some fewer-work times? Where
can I find more information about this?
Thanks in advance, best regardsAuto-grow should be an emergency fail-safe only. You should actively
monitor the database sizes and grow them ahead of time in an off-peak hour.
--
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Robert T." <r_t@.nospam.nospam> wrote in message
news:ej5%23rhqjEHA.3624@.TK2MSFTNGP10.phx.gbl...
> Hello:
> I'm experiencing this problem with SQL 2000. I have a large DB (7 GB) and
> fromtime to time every statement trying to insert or update data gets
> blocked and dies with timeout. Looking for more detail I have found that
> this happens when SQL server is growing its files. The DB is configured to
> grow automatically (+100 MB) every time needed, and has no size limit.
> To correct this lock, I run a INSERT statement from SQL query analizer,
wait
> for a minute and the statement is correctly executed, the server unlocked
> and my programs continue inserting data. INSERT statements launched from
> programs die with timeout and don't solve the problem.
> I'd like to have more control over DB grown. Where can I see when has the
DB
> grown? Can I force file grown when I want, ata some fewer-work times?
Where
> can I find more information about this?
> Thanks in advance, best regards
>|||In addition to Geoff's post, you find a couple of stored procedures at www.dbmaint.com (see the "free
utilities section" for jobs that can serve as inspiration...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Robert T." <r_t@.nospam.nospam> wrote in message news:ej5%23rhqjEHA.3624@.TK2MSFTNGP10.phx.gbl...
> Hello:
> I'm experiencing this problem with SQL 2000. I have a large DB (7 GB) and
> fromtime to time every statement trying to insert or update data gets
> blocked and dies with timeout. Looking for more detail I have found that
> this happens when SQL server is growing its files. The DB is configured to
> grow automatically (+100 MB) every time needed, and has no size limit.
> To correct this lock, I run a INSERT statement from SQL query analizer, wait
> for a minute and the statement is correctly executed, the server unlocked
> and my programs continue inserting data. INSERT statements launched from
> programs die with timeout and don't solve the problem.
> I'd like to have more control over DB grown. Where can I see when has the DB
> grown? Can I force file grown when I want, ata some fewer-work times? Where
> can I find more information about this?
> Thanks in advance, best regards
>
Sunday, March 11, 2012
Automatic Date Field
Hi, I am in the process of building a table and would
like one of the rows to contain a date time field that
is automatically populated with date/time once the
submit button is hit. I can do this in access with
selecting Data Type = Date/Time and default value
= Date()
SQL ver 2K sp3.
TIA for any pointers
JohnUse GETDATE() OR CURRENT_TIMESTAMP
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Johnny" <use@.stamp.co.uk> schrieb im Newsbeitrag
news:429c1d1f$1_1@.mk-nntp-2.news.uk.tiscali.com...
> Hi, I am in the process of building a table and would
> like one of the rows to contain a date time field that
> is automatically populated with date/time once the
> submit button is hit. I can do this in access with
> selecting Data Type = Date/Time and default value
> = Date()
> SQL ver 2K sp3.
> TIA for any pointers
> John
>|||"Johnny" <use@.stamp.co.uk> wrote in message
news:429c1d1f$1_1@.mk-nntp-2.news.uk.tiscali.com...
> Hi, I am in the process of building a table and would
> like one of the rows to contain a date time field that
> is automatically populated with date/time once the
> submit button is hit. I can do this in access with
> selecting Data Type = Date/Time and default value
> = Date()
> SQL ver 2K sp3.
> TIA for any pointers
> John
Thanks Jens, Unfortunatley that makes the whole column into the same date.
John
like one of the rows to contain a date time field that
is automatically populated with date/time once the
submit button is hit. I can do this in access with
selecting Data Type = Date/Time and default value
= Date()
SQL ver 2K sp3.
TIA for any pointers
JohnUse GETDATE() OR CURRENT_TIMESTAMP
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Johnny" <use@.stamp.co.uk> schrieb im Newsbeitrag
news:429c1d1f$1_1@.mk-nntp-2.news.uk.tiscali.com...
> Hi, I am in the process of building a table and would
> like one of the rows to contain a date time field that
> is automatically populated with date/time once the
> submit button is hit. I can do this in access with
> selecting Data Type = Date/Time and default value
> = Date()
> SQL ver 2K sp3.
> TIA for any pointers
> John
>|||"Johnny" <use@.stamp.co.uk> wrote in message
news:429c1d1f$1_1@.mk-nntp-2.news.uk.tiscali.com...
> Hi, I am in the process of building a table and would
> like one of the rows to contain a date time field that
> is automatically populated with date/time once the
> submit button is hit. I can do this in access with
> selecting Data Type = Date/Time and default value
> = Date()
> SQL ver 2K sp3.
> TIA for any pointers
> John
Thanks Jens, Unfortunatley that makes the whole column into the same date.
John
Wednesday, March 7, 2012
Automated Backup
Hi there,
I currently have a MSDE Server running with a number of databases on which i
would like to backup daily and weekly at a certain time. Is there anyway of
doing this? At the moment ive just been using a simple line of code and
executing when i remeber to, but an automated system would be great. The code
ive been using is:
BACKUP DATABASE dbname TO DISK = 'C:\dbname.bak'
Any help of links to sites that explain how todo this would be excellent!!
James
Hi James,
You need to schedule a job with the sql agent. First, make sure it is
running (it is a separate service). Then take a look at sp_addjob and
sp_addjobstep.
If that sounds messy, the MSDE Manager utility at our web site has options
to do that for you and is free for personal use.
HTH,
Greg Low [MVP]
MSDE Manager SQL Tools
www.whitebearconsulting.com
"James Proctor" <JamesProctor@.discussions.microsoft.com> wrote in message
news:C6DBD816-25C1-4A0E-8E78-60FAB18A6940@.microsoft.com...
> Hi there,
> I currently have a MSDE Server running with a number of databases on which
> i
> would like to backup daily and weekly at a certain time. Is there anyway
> of
> doing this? At the moment ive just been using a simple line of code and
> executing when i remeber to, but an automated system would be great. The
> code
> ive been using is:
> BACKUP DATABASE dbname TO DISK = 'C:\dbname.bak'
> Any help of links to sites that explain how todo this would be excellent!!
> James
|||You can try SQLExecMS from www.laplas-soft.com
It allows to create maintenance plans for your databases.
"James Proctor" <JamesProctor@.discussions.microsoft.com> wrote in message
news:C6DBD816-25C1-4A0E-8E78-60FAB18A6940@.microsoft.com...
> Hi there,
> I currently have a MSDE Server running with a number of databases on which
> i
> would like to backup daily and weekly at a certain time. Is there anyway
> of
> doing this? At the moment ive just been using a simple line of code and
> executing when i remeber to, but an automated system would be great. The
> code
> ive been using is:
> BACKUP DATABASE dbname TO DISK = 'C:\dbname.bak'
> Any help of links to sites that explain how todo this would be excellent!!
> James
I currently have a MSDE Server running with a number of databases on which i
would like to backup daily and weekly at a certain time. Is there anyway of
doing this? At the moment ive just been using a simple line of code and
executing when i remeber to, but an automated system would be great. The code
ive been using is:
BACKUP DATABASE dbname TO DISK = 'C:\dbname.bak'
Any help of links to sites that explain how todo this would be excellent!!
James
Hi James,
You need to schedule a job with the sql agent. First, make sure it is
running (it is a separate service). Then take a look at sp_addjob and
sp_addjobstep.
If that sounds messy, the MSDE Manager utility at our web site has options
to do that for you and is free for personal use.
HTH,
Greg Low [MVP]
MSDE Manager SQL Tools
www.whitebearconsulting.com
"James Proctor" <JamesProctor@.discussions.microsoft.com> wrote in message
news:C6DBD816-25C1-4A0E-8E78-60FAB18A6940@.microsoft.com...
> Hi there,
> I currently have a MSDE Server running with a number of databases on which
> i
> would like to backup daily and weekly at a certain time. Is there anyway
> of
> doing this? At the moment ive just been using a simple line of code and
> executing when i remeber to, but an automated system would be great. The
> code
> ive been using is:
> BACKUP DATABASE dbname TO DISK = 'C:\dbname.bak'
> Any help of links to sites that explain how todo this would be excellent!!
> James
|||You can try SQLExecMS from www.laplas-soft.com
It allows to create maintenance plans for your databases.
"James Proctor" <JamesProctor@.discussions.microsoft.com> wrote in message
news:C6DBD816-25C1-4A0E-8E78-60FAB18A6940@.microsoft.com...
> Hi there,
> I currently have a MSDE Server running with a number of databases on which
> i
> would like to backup daily and weekly at a certain time. Is there anyway
> of
> doing this? At the moment ive just been using a simple line of code and
> executing when i remeber to, but an automated system would be great. The
> code
> ive been using is:
> BACKUP DATABASE dbname TO DISK = 'C:\dbname.bak'
> Any help of links to sites that explain how todo this would be excellent!!
> James
Automate partition creation
Hi,
Is there any example out there on how to automate the creation of time based partitions?
As an example, I would like to create 1 partition for each quarter and when a new quarter start have a new partition automatically added with the same attributes than the previous one.
I would also have the oldest 4 partitions automatically deleted as soon as the total number of partitions reaches 13.
Any thoughts or links?
Thanks,
Philippe
Hi Philippe,
The Project REAL Analysis Services Technical Drilldown discusses one implementation of such automation:
http://www.microsoft.com/technet/prodtechnol/sql/2005/realastd.mspx
>>
Project REAL: Analysis Services Technical Drilldown
SQL Server Technical Article
Published: September 2005
Appendix A: Automating Partition Creation
The Project REAL design uses partitioning quite heavily. The production system has more than 220 extremely large partitions. The sample data uses over 125 partitions that are only tens of thousands of records per partition. The full production system has 180 to 200 million records per partition. With so many partitions, extensive typing was required to create each partition every time we generated a new schema.
So, as the saying goes, “When the going gets rough, a programmer writes a program.”
This appendix documents the BuildASPartition SQL Server 2005 Integration Services package that we created to automate the building of Analysis Services measure group partitions in SQL Server 2005 Analysis Services databases. This package synchronizes the relational partition scheme with the Analysis Services partition scheme. It loops through the relational database looking for a weekly fact table partition (by using a table naming convention). If a relational table is found, it looks to see if an Analysis Services measure group partition already exists (using the same naming convention). If not, it constructs and executes a XMLA script that creates it.
>>
|||Philippe,
Add your comments here: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=461211&SiteID=1
-Jamie
Subscribe to:
Posts (Atom)