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 create. Show all posts
Showing posts with label create. 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
Automating the creation of a database
Hi;
We have a program (ASP.NET) that requires a database as it's back end. We
are trying to create an install that requires as little expertise as
possible. In other words, no DBA required.
For creating the database itself we are at that point. We use the registry
to find the location of osql.exe and use that to run the schema that creates
the database. I'ld prefer an API we could call so we can more cleanly handle
errors but this works fine 98% of the time.
The remaining problem is ownership of the created database.
1) Is there a way (in .NET 2.0) to query if the database is mixed mode
authentication.
2) And if it is a way to both get a list of all users
3) And to create a user?
We can already enum all domain users. So with the above we could give them a
list of all users they can choose from as the owner and also let then create
a new one - without the user ever having to run any SqlServer tool or even
have any installed.
thanks - dave
david_at_windward_dot_net
http://www.windwardreports.com
Cubicle Wars - http://www.windwardreports.com/film.htm> The remaining problem is ownership of the created database.
> 1) Is there a way (in .NET 2.0) to query if the database is mixed mode
> authentication.
It is SERVER property not a databases
SELECT SERVERPROPERTY('IsIntegratedSecurityOnly
') AS
[IsIntegratedSecurityOnly]
> 2) And if it is a way to both get a list of all users
EXEC northwind..sp_helpuser
> 3) And to create a user?
Create Database mydb
go
use mydb
go
sp_addlogin 'mydbuser','monitor','mydb'
go
sp_adduser 'mydbuser'
go
sp_Addrolemember 'db_datawriter','mydbuser'
go
sp_Addrolemember 'db_datareader','mydbuser'
go
"David Thielen" <thielen@.nospam.nospam> wrote in message
news:25882AC6-F4EF-422A-8B68-63ABE75AACF3@.microsoft.com...
> Hi;
> We have a program (ASP.NET) that requires a database as it's back end. We
> are trying to create an install that requires as little expertise as
> possible. In other words, no DBA required.
> For creating the database itself we are at that point. We use the registry
> to find the location of osql.exe and use that to run the schema that
> creates
> the database. I'ld prefer an API we could call so we can more cleanly
> handle
> errors but this works fine 98% of the time.
> The remaining problem is ownership of the created database.
> 1) Is there a way (in .NET 2.0) to query if the database is mixed mode
> authentication.
> 2) And if it is a way to both get a list of all users
> 3) And to create a user?
> We can already enum all domain users. So with the above we could give them
> a
> list of all users they can choose from as the owner and also let then
> create
> a new one - without the user ever having to run any SqlServer tool or even
> have any installed.
> --
> thanks - dave
> david_at_windward_dot_net
> http://www.windwardreports.com
> Cubicle Wars - http://www.windwardreports.com/film.htm
>|||Hi David,
I am afraid that the database may have some synchronous problem now. Our
yesterday's replies cannot be seen from Web and we also cannot see your
replies.
So I post it again from Outlook Express and hope you could see it now. Sorry
for bringing you any inconvenience.
I understand that your application used osql.exe to create the database and
you have three questions on the ownership of the created database now:
1. How to query (in .NET 2.0) if the database is with mixed authentication
mode?
2. How to get a list of all users?
3. How to create a user?
If I have misunderstood, please let me know.
For your three questions and even your creating database function, you can
fully resolve the questions by using the SQL Server SMO component for .NET
2.0.
1. You can just create a Server object like this:
//Server Name
string strConn = "(local)";
//Instantiate SMO Server Object
Server svr = new Server(strConn);
Console.Writeline(svr.Settings.LoginMode.ToString());
2. To get the list of all users, you can use:
Database db = server.Databases["your_db_name"];
UserCollection users = db.Users;
3. To create a user, you can use:
//Instantiate SMO Login object
Login l = new Login(svr, loginName);
//If Login doesn't already exist
if (!svr.Logins.Contains(loginName))
{
//Login should be of type Sql Login
l.LoginType = LoginType.SqlLogin;
//Create the Login on the SQL Server with password: pa$$w0rd
l.Create("pa$$w0rd");
//Add the login to the sysadmin role
l.AddToRole("sysadmin");
}
//Instantiate a new database object
Database db = new Database(svr, "Fizoo2");
//Make SQL Server create the database
db.Create();
//Instantiate a new User object
User u = new User(db, "SQL_Login_user");
//associated it with the login "SQL_Login"
u.Login = loginName;
//Make SQL Server create the user
u.Create();
For more information, you can refer to the following references:
User Privileges View & Create User Tool
http://forums.microsoft.com/MSDN/Sh...840637&SiteID=1
How to: Create a Visual C# SMO Project in Visual Studio .NET
http://msdn2.microsoft.com/it-it/library/ms162129.aspx
How to: Modify SQL Server Settings in Visual Basic .NET
http://msdn2.microsoft.com/en-us/library/ms162131.aspx
If you have any other questions or concerns, please feel free to let me
know. It is my pleasure to be of assistance.
Sincerely yours,
Charles Wang
Microsoft Online Community Support
========================================
==============
When responding to posts, please "Reply to Group" via your newsreader
so that others may learn and benefit from this issue.
========================================
==============
This posting is provided "AS IS" with no warranties, and confers no rights.
========================================
==============
"David Thielen" <thielen@.nospam.nospam> wrote in message
news:25882AC6-F4EF-422A-8B68-63ABE75AACF3@.microsoft.com...
> Hi;
> We have a program (ASP.NET) that requires a database as it's back end. We
> are trying to create an install that requires as little expertise as
> possible. In other words, no DBA required.
> For creating the database itself we are at that point. We use the registry
> to find the location of osql.exe and use that to run the schema that
> creates
> the database. I'ld prefer an API we could call so we can more cleanly
> handle
> errors but this works fine 98% of the time.
> The remaining problem is ownership of the created database.
> 1) Is there a way (in .NET 2.0) to query if the database is mixed mode
> authentication.
> 2) And if it is a way to both get a list of all users
> 3) And to create a user?
> We can already enum all domain users. So with the above we could give them
> a
> list of all users they can choose from as the owner and also let then
> create
> a new one - without the user ever having to run any SqlServer tool or even
> have any installed.
> --
> thanks - dave
> david_at_windward_dot_net
> http://www.windwardreports.com
> Cubicle Wars - http://www.windwardreports.com/film.htm
>|||Hi Dave,
I understand that your application used osql.exe to create the database and
you have three questions on the ownership of the created database now:
1. How to query (in .NET 2.0) if the database is with mixed authentication
mode?
2. How to get a list of all users?
3. How to create a user?
If I have misunderstood, please let me know.
For your three questions and even your creating database function, you can
fully resolve the questions by using the SQL Server SMO component for .NET
2.0.
1. You can just create a Server object like this:
//Server Name
string strConn = "(local)";
//Instantiate SMO Server Object
Server svr = new Server(strConn);
Console.Writeline(svr.Settings.LoginMode.ToString());
2. To get the list of all users, you can use:
Database db = server.Databases["your_db_name"];
UserCollection users = db.Users;
3. To create a user, you can use:
//Instantiate SMO Login object
Login l = new Login(svr, loginName);
//If Login doesn't already exist
if (!svr.Logins.Contains(loginName))
{
//Login should be of type Sql Login
l.LoginType = LoginType.SqlLogin;
//Create the Login on the SQL Server with password: pa$$w0rd
l.Create("pa$$w0rd");
//Add the login to the sysadmin role
l.AddToRole("sysadmin");
}
//Instantiate a new database object
Database db = new Database(svr, "Fizoo2");
//Make SQL Server create the database
db.Create();
//Instantiate a new User object
User u = new User(db, "SQL_Login_user");
//associated it with the login "SQL_Login"
u.Login = loginName;
//Make SQL Server create the user
u.Create();
For more information, you can refer to the following references:
User Privileges View & Create User Tool
http://forums.microsoft.com/MSDN/Sh...840637&SiteID=1
How to: Create a Visual C# SMO Project in Visual Studio .NET
http://msdn2.microsoft.com/it-it/library/ms162129.aspx
How to: Modify SQL Server Settings in Visual Basic .NET
http://msdn2.microsoft.com/en-us/library/ms162131.aspx
If you have any other questions or concerns, please feel free to let me
know. It is my pleasure to be of assistance.
Sincerely yours,
Charles Wang
Microsoft Online Community Support
========================================
==============
When responding to posts, please "Reply to Group" via your newsreader
so that others may learn and benefit from this issue.
========================================
==============
This posting is provided "AS IS" with no warranties, and confers no rights.
========================================
==============
We have a program (ASP.NET) that requires a database as it's back end. We
are trying to create an install that requires as little expertise as
possible. In other words, no DBA required.
For creating the database itself we are at that point. We use the registry
to find the location of osql.exe and use that to run the schema that creates
the database. I'ld prefer an API we could call so we can more cleanly handle
errors but this works fine 98% of the time.
The remaining problem is ownership of the created database.
1) Is there a way (in .NET 2.0) to query if the database is mixed mode
authentication.
2) And if it is a way to both get a list of all users
3) And to create a user?
We can already enum all domain users. So with the above we could give them a
list of all users they can choose from as the owner and also let then create
a new one - without the user ever having to run any SqlServer tool or even
have any installed.
thanks - dave
david_at_windward_dot_net
http://www.windwardreports.com
Cubicle Wars - http://www.windwardreports.com/film.htm> The remaining problem is ownership of the created database.
> 1) Is there a way (in .NET 2.0) to query if the database is mixed mode
> authentication.
It is SERVER property not a databases
SELECT SERVERPROPERTY('IsIntegratedSecurityOnly
') AS
[IsIntegratedSecurityOnly]
> 2) And if it is a way to both get a list of all users
EXEC northwind..sp_helpuser
> 3) And to create a user?
Create Database mydb
go
use mydb
go
sp_addlogin 'mydbuser','monitor','mydb'
go
sp_adduser 'mydbuser'
go
sp_Addrolemember 'db_datawriter','mydbuser'
go
sp_Addrolemember 'db_datareader','mydbuser'
go
"David Thielen" <thielen@.nospam.nospam> wrote in message
news:25882AC6-F4EF-422A-8B68-63ABE75AACF3@.microsoft.com...
> Hi;
> We have a program (ASP.NET) that requires a database as it's back end. We
> are trying to create an install that requires as little expertise as
> possible. In other words, no DBA required.
> For creating the database itself we are at that point. We use the registry
> to find the location of osql.exe and use that to run the schema that
> creates
> the database. I'ld prefer an API we could call so we can more cleanly
> handle
> errors but this works fine 98% of the time.
> The remaining problem is ownership of the created database.
> 1) Is there a way (in .NET 2.0) to query if the database is mixed mode
> authentication.
> 2) And if it is a way to both get a list of all users
> 3) And to create a user?
> We can already enum all domain users. So with the above we could give them
> a
> list of all users they can choose from as the owner and also let then
> create
> a new one - without the user ever having to run any SqlServer tool or even
> have any installed.
> --
> thanks - dave
> david_at_windward_dot_net
> http://www.windwardreports.com
> Cubicle Wars - http://www.windwardreports.com/film.htm
>|||Hi David,
I am afraid that the database may have some synchronous problem now. Our
yesterday's replies cannot be seen from Web and we also cannot see your
replies.
So I post it again from Outlook Express and hope you could see it now. Sorry
for bringing you any inconvenience.
I understand that your application used osql.exe to create the database and
you have three questions on the ownership of the created database now:
1. How to query (in .NET 2.0) if the database is with mixed authentication
mode?
2. How to get a list of all users?
3. How to create a user?
If I have misunderstood, please let me know.
For your three questions and even your creating database function, you can
fully resolve the questions by using the SQL Server SMO component for .NET
2.0.
1. You can just create a Server object like this:
//Server Name
string strConn = "(local)";
//Instantiate SMO Server Object
Server svr = new Server(strConn);
Console.Writeline(svr.Settings.LoginMode.ToString());
2. To get the list of all users, you can use:
Database db = server.Databases["your_db_name"];
UserCollection users = db.Users;
3. To create a user, you can use:
//Instantiate SMO Login object
Login l = new Login(svr, loginName);
//If Login doesn't already exist
if (!svr.Logins.Contains(loginName))
{
//Login should be of type Sql Login
l.LoginType = LoginType.SqlLogin;
//Create the Login on the SQL Server with password: pa$$w0rd
l.Create("pa$$w0rd");
//Add the login to the sysadmin role
l.AddToRole("sysadmin");
}
//Instantiate a new database object
Database db = new Database(svr, "Fizoo2");
//Make SQL Server create the database
db.Create();
//Instantiate a new User object
User u = new User(db, "SQL_Login_user");
//associated it with the login "SQL_Login"
u.Login = loginName;
//Make SQL Server create the user
u.Create();
For more information, you can refer to the following references:
User Privileges View & Create User Tool
http://forums.microsoft.com/MSDN/Sh...840637&SiteID=1
How to: Create a Visual C# SMO Project in Visual Studio .NET
http://msdn2.microsoft.com/it-it/library/ms162129.aspx
How to: Modify SQL Server Settings in Visual Basic .NET
http://msdn2.microsoft.com/en-us/library/ms162131.aspx
If you have any other questions or concerns, please feel free to let me
know. It is my pleasure to be of assistance.
Sincerely yours,
Charles Wang
Microsoft Online Community Support
========================================
==============
When responding to posts, please "Reply to Group" via your newsreader
so that others may learn and benefit from this issue.
========================================
==============
This posting is provided "AS IS" with no warranties, and confers no rights.
========================================
==============
"David Thielen" <thielen@.nospam.nospam> wrote in message
news:25882AC6-F4EF-422A-8B68-63ABE75AACF3@.microsoft.com...
> Hi;
> We have a program (ASP.NET) that requires a database as it's back end. We
> are trying to create an install that requires as little expertise as
> possible. In other words, no DBA required.
> For creating the database itself we are at that point. We use the registry
> to find the location of osql.exe and use that to run the schema that
> creates
> the database. I'ld prefer an API we could call so we can more cleanly
> handle
> errors but this works fine 98% of the time.
> The remaining problem is ownership of the created database.
> 1) Is there a way (in .NET 2.0) to query if the database is mixed mode
> authentication.
> 2) And if it is a way to both get a list of all users
> 3) And to create a user?
> We can already enum all domain users. So with the above we could give them
> a
> list of all users they can choose from as the owner and also let then
> create
> a new one - without the user ever having to run any SqlServer tool or even
> have any installed.
> --
> thanks - dave
> david_at_windward_dot_net
> http://www.windwardreports.com
> Cubicle Wars - http://www.windwardreports.com/film.htm
>|||Hi Dave,
I understand that your application used osql.exe to create the database and
you have three questions on the ownership of the created database now:
1. How to query (in .NET 2.0) if the database is with mixed authentication
mode?
2. How to get a list of all users?
3. How to create a user?
If I have misunderstood, please let me know.
For your three questions and even your creating database function, you can
fully resolve the questions by using the SQL Server SMO component for .NET
2.0.
1. You can just create a Server object like this:
//Server Name
string strConn = "(local)";
//Instantiate SMO Server Object
Server svr = new Server(strConn);
Console.Writeline(svr.Settings.LoginMode.ToString());
2. To get the list of all users, you can use:
Database db = server.Databases["your_db_name"];
UserCollection users = db.Users;
3. To create a user, you can use:
//Instantiate SMO Login object
Login l = new Login(svr, loginName);
//If Login doesn't already exist
if (!svr.Logins.Contains(loginName))
{
//Login should be of type Sql Login
l.LoginType = LoginType.SqlLogin;
//Create the Login on the SQL Server with password: pa$$w0rd
l.Create("pa$$w0rd");
//Add the login to the sysadmin role
l.AddToRole("sysadmin");
}
//Instantiate a new database object
Database db = new Database(svr, "Fizoo2");
//Make SQL Server create the database
db.Create();
//Instantiate a new User object
User u = new User(db, "SQL_Login_user");
//associated it with the login "SQL_Login"
u.Login = loginName;
//Make SQL Server create the user
u.Create();
For more information, you can refer to the following references:
User Privileges View & Create User Tool
http://forums.microsoft.com/MSDN/Sh...840637&SiteID=1
How to: Create a Visual C# SMO Project in Visual Studio .NET
http://msdn2.microsoft.com/it-it/library/ms162129.aspx
How to: Modify SQL Server Settings in Visual Basic .NET
http://msdn2.microsoft.com/en-us/library/ms162131.aspx
If you have any other questions or concerns, please feel free to let me
know. It is my pleasure to be of assistance.
Sincerely yours,
Charles Wang
Microsoft Online Community Support
========================================
==============
When responding to posts, please "Reply to Group" via your newsreader
so that others may learn and benefit from this issue.
========================================
==============
This posting is provided "AS IS" with no warranties, and confers no rights.
========================================
==============
Automating Restoring of *.BAK files
I have set up a Maintenance Plans on a SQL Server 2000 SP3a on one server to
create flat file backup of full databases to *.BAK files nightly.
Is it possible to automate the restoring of such BAK files on another SQL
Server 2000 SP3a on another server (assume I have in place scripts for
copying the BAK files from the source server to the destination server)? If
so, how?http://msdn.microsoft.com/library/en-us/adminsql/ad_automate_42r7.asp
--
David Portas
SQL Server MVP
--|||Yes, I know how to create a job in general, but what exactly do I run to
restore a BAK file?
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1106572451.019746.272450@.f14g2000cwb.googlegroups.com...
> http://msdn.microsoft.com/library/en-us/adminsql/ad_automate_42r7.asp
> --
> David Portas
> SQL Server MVP
> --
>|||Use the RESTORE DATABASE command in a Transact SQL job step. See Books
Online for details of the RESTORE DATABASE command.
--
David Portas
SQL Server MVP
--|||Taking a step back, I am just wondering whether a flat-file backup-restore
would be the best way to synchronise 2 SQL Server 2000 databases? Or should
I go for a DTS package to export database on the source server to an Access
mdb file and import it on the other end? Sometimes, I find that the users
in an exported flat file, following an import on another server is not
"usable" even if the referenced user are already defined on the destination
server.
"Patrick" <patl@.reply.newsgroup.msn.com> wrote in message
news:%23CWx88hAFHA.2552@.TK2MSFTNGP09.phx.gbl...
> Yes, I know how to create a job in general, but what exactly do I run to
> restore a BAK file?
>
> "David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
> news:1106572451.019746.272450@.f14g2000cwb.googlegroups.com...
> > http://msdn.microsoft.com/library/en-us/adminsql/ad_automate_42r7.asp
> > --
> > David Portas
> > SQL Server MVP
> > --
> >
>|||"Patrick" <patl@.reply.newsgroup.msn.com> wrote in message
news:%23S0tVOhAFHA.3416@.TK2MSFTNGP09.phx.gbl...
> I have set up a Maintenance Plans on a SQL Server 2000 SP3a on one server
to
> create flat file backup of full databases to *.BAK files nightly.
> Is it possible to automate the restoring of such BAK files on another SQL
> Server 2000 SP3a on another server (assume I have in place scripts for
> copying the BAK files from the source server to the destination server)?
If
> so, how?
>
Yes.
In my case I wrote a stored proc on the restoring server and called it from
the backing up server.
CREATE procedure restore_FOO as
declare @.backup_file as varchar(255)
select @.backup_file=physical_device_name from
nell.msdb.dbo.backupmediafamily where media_set_id in (select
max(media_set_id) from BAR.msdb.dbo.backupset where database_name='foo')
print @.backup_file
restore database FOO from disk=@.backup_file with
move 'SearchActivity_Data' to 'e:\sql_data\FOO_data.mdf',
move 'SearchActivity_Log' to 'f:\SQL_LOGs\FOO_log.ldf',
move 'SearchActivity_Index' to 'g:\sql_index\FOO_Index_Data.NDF',
replace
GO
>
create flat file backup of full databases to *.BAK files nightly.
Is it possible to automate the restoring of such BAK files on another SQL
Server 2000 SP3a on another server (assume I have in place scripts for
copying the BAK files from the source server to the destination server)? If
so, how?http://msdn.microsoft.com/library/en-us/adminsql/ad_automate_42r7.asp
--
David Portas
SQL Server MVP
--|||Yes, I know how to create a job in general, but what exactly do I run to
restore a BAK file?
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1106572451.019746.272450@.f14g2000cwb.googlegroups.com...
> http://msdn.microsoft.com/library/en-us/adminsql/ad_automate_42r7.asp
> --
> David Portas
> SQL Server MVP
> --
>|||Use the RESTORE DATABASE command in a Transact SQL job step. See Books
Online for details of the RESTORE DATABASE command.
--
David Portas
SQL Server MVP
--|||Taking a step back, I am just wondering whether a flat-file backup-restore
would be the best way to synchronise 2 SQL Server 2000 databases? Or should
I go for a DTS package to export database on the source server to an Access
mdb file and import it on the other end? Sometimes, I find that the users
in an exported flat file, following an import on another server is not
"usable" even if the referenced user are already defined on the destination
server.
"Patrick" <patl@.reply.newsgroup.msn.com> wrote in message
news:%23CWx88hAFHA.2552@.TK2MSFTNGP09.phx.gbl...
> Yes, I know how to create a job in general, but what exactly do I run to
> restore a BAK file?
>
> "David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
> news:1106572451.019746.272450@.f14g2000cwb.googlegroups.com...
> > http://msdn.microsoft.com/library/en-us/adminsql/ad_automate_42r7.asp
> > --
> > David Portas
> > SQL Server MVP
> > --
> >
>|||"Patrick" <patl@.reply.newsgroup.msn.com> wrote in message
news:%23S0tVOhAFHA.3416@.TK2MSFTNGP09.phx.gbl...
> I have set up a Maintenance Plans on a SQL Server 2000 SP3a on one server
to
> create flat file backup of full databases to *.BAK files nightly.
> Is it possible to automate the restoring of such BAK files on another SQL
> Server 2000 SP3a on another server (assume I have in place scripts for
> copying the BAK files from the source server to the destination server)?
If
> so, how?
>
Yes.
In my case I wrote a stored proc on the restoring server and called it from
the backing up server.
CREATE procedure restore_FOO as
declare @.backup_file as varchar(255)
select @.backup_file=physical_device_name from
nell.msdb.dbo.backupmediafamily where media_set_id in (select
max(media_set_id) from BAR.msdb.dbo.backupset where database_name='foo')
print @.backup_file
restore database FOO from disk=@.backup_file with
move 'SearchActivity_Data' to 'e:\sql_data\FOO_data.mdf',
move 'SearchActivity_Log' to 'f:\SQL_LOGs\FOO_log.ldf',
move 'SearchActivity_Index' to 'g:\sql_index\FOO_Index_Data.NDF',
replace
GO
>
Tuesday, March 27, 2012
Automating Daily Database Inserts...Contd
I used thetutorial on creating a windows service. i was able to succesfully create a service. however, after going through the whole tutorial i realized this is something that is always running. i need to run the service only once a day ( usually past midnight).
however, i dont know what i messed up. i have been fiddling with it for some time now. i had already created 2-3 windows applications- one for creating a bunch of html pages , another for opening each of these files in a word app and printing it. now i was trying to merge all these processes into one windoes service. so i cut/pasted some code..etc. now it throws an error :
"
cannot start service from command line or a debugger. A Windows Service must first be installed (using instalutil.exe) and then started with the Server Explorer, Windows Services Administrative tool or the NET START command."
does anyone know how i can configure it so it runs only at a specified time and not always.
thanks.The service will always be running since that's how services work. The service periodically checks every Timer.Internval milliseconds whether something should be run. When it actually runs something, like a report at midnight, is up to your code.
If you are concerned about the resources used by the service you can open up the Windows Task Manager and look under the Processes tab. My own similar scheduler uses a negligible amount of CPU. It barely ticks over a few seconds per 24 hours.|||so how do i set it to run at a certain time..all my calculations r dependent on the date functions...so i need the service to run after 12 midnight. so how do i compare the timer to the time of the day...
do you know of any tutorial or some sample...
thanks McMurdoStation|||The service is always running (that is the beauty of it). You need to code it such that it looks at the time of day and runs your process when required. In your case, you can have it check the date periodically, and then whenever the date changes, run the process.|||
|||thanks both of you. i understand it better now.
// C# but this should give you the general idea// Declare class level variable to hold date process last run
private DateTime lastRunDate = System.DateTime.Today;// then handle the event the timer generates when each Timer.Interval has elapsed
private void timer1_Elapsed(object sender, System.Timers.ElapsedEventArgs e) {
DateTime today= System.DateTime.Today;
if( today > lastRunDate){
lastRunDate= today ;
RunYourStuff();
}
}
however, i dont know what i messed up. i have been fiddling with it for some time now. i had already created 2-3 windows applications- one for creating a bunch of html pages , another for opening each of these files in a word app and printing it. now i was trying to merge all these processes into one windoes service. so i cut/pasted some code..etc. now it throws an error :
"
cannot start service from command line or a debugger. A Windows Service must first be installed (using instalutil.exe) and then started with the Server Explorer, Windows Services Administrative tool or the NET START command."
i am pretty sure its not the code. also when i went to administrative tols -> services -> and browsed for my "Service1"... it shows up in the list but its not started. i was trying to start it. it says "
"The service1 on local computer started ans then stopped. some services stop automatically if they have no work to do, for example, the Performance Logs and Alerts Service."
can you help me figure this out...
thanks in advance|||anyone..|||Did you create the installer for the service? I think that article talks about that too. After you create and run the installer then you can start the service from the Services Manager (if it didn't start automatically).
If the service is crashing still then add some code to write out the event log so that you can figure out where it is when it crashes.|||actually i was able to start the service...i can see it from the admin tools -> services
i tried to schedule it using windows scheduler and when the program ran at the specified time
.. it kept throwing the error :
"cannot start service from command line or a debugger. A Windows Service must first be installed (using instalutil.exe) and then started with the Server Explorer, Windows Services Administrative tool or the NET START command."
i dont understand why we have to create a set up project..
when i schedule the task.. i should select the windowsservice1.exe right ?
i googled around for some time, but most of the articles are too brief..
thanks|||You do not have to schedule the service to run. It is designed to CONSTANTLY run, with or without a user logged in. You need to check to make sure the service continues running (if it stops running, there is a problem in your code) and if it is running, internally in the code, you need to make it do what you want to do periodically (whatever period you need).|||when i tried to build the solution i get this error:
WARNING: This setup does not contain the .NET Framework which must be installed on the target machine by running dotnetfx.exe before this setup will install. You can find dotnetfx.exe on the Visual Studio .NET 'Windows Components Update' media. Dotnetfx.exe can be redistributed with your setup.
i really need some help in getting this running.
doug, like you said i removed it from the scheduled events. i coded it as :
Dim lastrun = System.DateTime.Now.Hour
Private Sub Timer1_Elapsed(ByVal sender As System.Object, ByVal e As System.Timers.ElapsedEventArgs) Handles Timer1.Elapsed
Dim today As DateTime = System.DateTime.Today
If today.Hour > lastrun Then
lastrun = today
'add monthly charges
Call addmonthlycharges()
'create the html statements
Call createstmts()
'print the stmts
Call printstmts()
End If
End Sub
so it will run every hour ( for now ) though it needs to run once a day.
i was trying to debug the program but it keeps throwing back the error :
"cannot start service from command line or a debugger. A Windows Service must first be installed (using instalutil.exe) and then started with the Server Explorer, Windows Services Administrative tool or the NET START command."
thanks|||someone...?|||anyone......|||The warning message about dotnetfx isn't a big deal. Presumably you have the DotNet framework already on your computer so it won't matter. It would only become an issue if you want to deploy your service on a server that doesn't already have DotNet. You can worry about that later...
To install the service follow the directions on that articlehttp://authors.aspalliance.com/hrmalik/articles/2003/200302/20030203.aspx">starting on this page.
You've already done this given the dotnetfx warning message. After it has built the install file (something.msi) right click on the installer project in the solutions explorer and select "Install" from the pop-up list. Either that or navigate to the something.msi file it created an double-click.
Follow the usual instructions for the install wizard.
After the install is done go to the services manager, look up the service you just installed, and start it. In principal, it should then start working and running your update at midnight.|||i can see the status of the service as "started" under services, but its not doing anything.. the prog is actually supposed to create a new folder and a few html files inside the folder and also print them.
heres the entire code :
Public Sub New()
MyBase.New()' This call is required by the Component Designer.
InitializeComponent()' Add any initialization after the InitializeComponent() call
If Not EventLog.SourceExists("MySource") Then
EventLog.CreateEventSource("MySource", "MyNewLog")
End If
EventLog1.Source = "MySource"
EventLog1.Log = "MyNewLog"End Sub
Dim FileExists As Boolean
Dim lblmessage As String = Now()
Dim lastrun = System.DateTime.Now.HourProtected Overrides Sub OnStart(ByVal args() As String)
' Add code here to start your service. This method should set things
' in motion so your service can do its work.
EventLog1.WriteEntry("Starting")
Timer1.Start()
End SubProtected Overrides Sub OnStop()
' Add code here to perform any tear-down necessary to stop your service.
EventLog1.WriteEntry("Stopping")
Timer1.Stop()
End SubPrivate Sub Timer1_Elapsed(ByVal sender As System.Object, ByVal e As System.Timers.ElapsedEventArgs) Handles Timer1.Elapsed
Dim today As DateTime = System.DateTime.Today
If today.Hour > lastrun Then
lastrun = today
'add monthly charges
Call addmonthlycharges()
'create the html statements
Call createstmts()
'print the stmts
Call printstmts()
End If
End Sub
i am sure theres no prob with the code, since i had the same code in a windows application and it runs fine. i was just trying to automate it so it runs by itself everyday...
i'd really appreciate any help in this..
thanks
Automating Creation of Reporting Services Reports (Excel format)
Hello,
Currently, I have to manully create RS 2005 reports which I export into an Excel later. Is there a way to create a SSIS package that could automate this somehow?
Thanks for sharing your thoughts and ideas!
donnie100 wrote:
Hello,
Currently, I have to manully create RS 2005 reports which I export into an Excel later. Is there a way to create a SSIS package that could automate this somehow?
Thanks for sharing your thoughts and ideas!
You mean you want to use SSIS to build the RDL? I'm not really sure why you would want to do this but heigh-ho.
Is there a .Net API for SSRS? if there is then you could call it from a SSIS script task.
-Jamie
automaticly create a record's field
I used a field as the record's number,how can I get a automaticly created
number field (it can inrease automaticly) when I insert a record into a
table?Refer to the IDENTITY property in BOL
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
"authorking" <authorking2002@.hotmail.com> wrote in message
news:uKU01w2CFHA.3732@.TK2MSFTNGP14.phx.gbl...
>I used a field as the record's number,how can I get a automaticly created
>number field (it can inrease automaticly) when I insert a record into a
>table?
>|||First, let's clarify some concepts. In SQL, rows are identified by a Key not
by a "record number". In fact the concept of a record number is quite alien
to the relational database model. The Key is part of your data - it is some
subset of the attributes that uniquely identify a row.
What you are asking for is called a *surrogate* or *artificial* key. SQL
Server provides the IDENTITY feature as a mechanism for an artifically
generated, surrogate key so take a look at IDENTITY in Books Online.
IDENTITY is not a substitute for the natural key of your table. It is just a
surrogate for that key and may be used in foreign key references. Many times
you won't need IDENTITY at all. If you aren't familiar with some of these
key concepts then look them up in a book on relational database
fundamentals.
Hope this helps.
David Portas
SQL Server MVP
--
number field (it can inrease automaticly) when I insert a record into a
table?Refer to the IDENTITY property in BOL
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
"authorking" <authorking2002@.hotmail.com> wrote in message
news:uKU01w2CFHA.3732@.TK2MSFTNGP14.phx.gbl...
>I used a field as the record's number,how can I get a automaticly created
>number field (it can inrease automaticly) when I insert a record into a
>table?
>|||First, let's clarify some concepts. In SQL, rows are identified by a Key not
by a "record number". In fact the concept of a record number is quite alien
to the relational database model. The Key is part of your data - it is some
subset of the attributes that uniquely identify a row.
What you are asking for is called a *surrogate* or *artificial* key. SQL
Server provides the IDENTITY feature as a mechanism for an artifically
generated, surrogate key so take a look at IDENTITY in Books Online.
IDENTITY is not a substitute for the natural key of your table. It is just a
surrogate for that key and may be used in foreign key references. Many times
you won't need IDENTITY at all. If you aren't familiar with some of these
key concepts then look them up in a book on relational database
fundamentals.
Hope this helps.
David Portas
SQL Server MVP
--
Sunday, March 25, 2012
Automatically process a cube which is on another server
Hello
I want to create a DTS package on my server #1 which will process a cube on my server #2. The problem is that when I create the DTS package, I select the "Analysis Services Processing Task" and then, the only choice I have in the left box "Select the object to process" is the local server (server#1). How could it be possible to select my server#2 in that box ?
I know I can create connexions ... could that be part of the solution ?
I'm using SQL SERVER 2000 on server#1 and SSAS2000 on server #2.
Mike
When you're editing a DTS package, I believe the Analysis Services Processing Task will display servers that you've registered in Analysis Manager with your current login profile.
Open Analysis Manager and register the server that you intend to process cubes on, then try the package design again.
Are you logged directly into server #1 or term-served into it? If not, just be aware of the classic problem of DTS package design & deployment. Since Analysis services and DTS package design use live connections, what may work during DTS design time may not work once you, for instance, schedule the package to run as a job step, due to differences in permissions between the developer account credentials versus the account credentials of the service account which SQL Server Agent starts up as.
I hope this helps. I remember suffering through my first DTS package design sessions all too well.
CJB
|||Enterprise Manager only allows me to register a SQL Server , but the database is on my server#1. My server #2 just has Analysis Services installed, so it is not a SQL Server.
I tried to register my server#1 with the Analysis Manager, and it worked ( I think it's because I have a sample cube on my srever#1), but that didn't change anything.
I still can't do what i need to.
Mike
|||I think you're looking for the TreeKey setting. It's been so long since I've done DTS and AS2000 that I'm a little rusty. But have a look at:
http://msdn2.microsoft.com/en-us/library/aa902667(sql.80).aspx
Search for "TreeKey" then look at the image above that section. I believe if you set it to "YourServerName\YourCubeName" you should be able to process a cube on another server. I think you'll need a dynamic properties task to accomplish that.
And you might look at:
http://blogs.msdn.com/bi_systems/articles/141632.aspx
|||thanks for your help, furmangg, but I am not familiar with dynamic properties tasks... What are they ?
And I never used ActiveX scripts, like it is suggested there http://msdn2.microsoft.com/en-us/library/aa902667(sql.80).aspx
Could you give me more detailed explanations, please ?
|||You need a dynamic properties task to set the TreeKey property of your Analysis Services Processing Task. Here's more on dynamic properties tasks:
http://msdn2.microsoft.com/en-us/library/aa933528(sql.80).aspx
(If there's a UI way to hardcode the TreeKey for the AS Processing Task without a dynamic properties task, then you won't need one.)
I think you'll only need ActiveX script if you don't want to hardcode your TreeKey and want it to be more dynamic like that article suggests.
|||Ok, I think I understand the problem now.
You can change the treekey setting by entering "Disconnected Edit" mode in the DTS designer. The drawback here is that you won't be able to double click your Analysis Services Processing Task to edit it. But that's okay, since it's not working anyway.
Right click anywhere in the background while designing the DTS package.
A dialog should appear.
Select "Disconnected Edit..."
Expand "Tasks"
Select the Task which is processing the cubes.
A collection of Task properties will show up in the right hand panel. The bottom one is "TreeKey", and its syntax is
servername\databasename\CubeFolder\cubename
So you might edit this to read (note that spaces here are fine, the package will "wrap" them properly at runtime)
mike8srv\FoodMart 2000\CubeFolder\Sales
Inspect the other property values, particularly DataSource (should match the name in Analysis Manager "Data Sources"), Fact table, and ProcessingOption (enumerated list: 0 = full, 1 = refresh, 2 = incremental).
|||
Maybe I was getting close of a solution with furmangg, but with your last post, you really solved my problem John !
I wouldn't have imagined a simpler solution !
thanks for your help to both of you - i really appreciate
Automatically increasing field definition by SQL
How can I create/define a field so it'll be of the automatically increasing type with a SQL sentence? If it must be done during table creation, that's cool too.
ThanksCreate table a
(
name varchar2(100)
);
Alter table a
modify name varchar2(200);|||Are you asking how to create a column that will increase in value, or increase in size? If you are looking to create something analagous to Oracle's rowid, the syntax is different for each database engine, so you'll have to tell us which engine you are using for us to give you one answer.
-PatP|||It's on ACCESS.|||Originally posted by anat_sher
It's on ACCESS. That's helpful, but are you looking for an MS-Access AUTONUMBER (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/off2000/html/acconWhichTypeAutoNumberFieldCreate.asp) column, or a TEXT column that will increase in length each time you do something?
-PatP|||AUTONUMBER please..
It's not on ACCESS really, it's on a SQL server. But I figured it's about he same. No?|||create table tableA
(
id INTEGER IDENTITY(1, 1)
...
)
ThanksCreate table a
(
name varchar2(100)
);
Alter table a
modify name varchar2(200);|||Are you asking how to create a column that will increase in value, or increase in size? If you are looking to create something analagous to Oracle's rowid, the syntax is different for each database engine, so you'll have to tell us which engine you are using for us to give you one answer.
-PatP|||It's on ACCESS.|||Originally posted by anat_sher
It's on ACCESS. That's helpful, but are you looking for an MS-Access AUTONUMBER (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/off2000/html/acconWhichTypeAutoNumberFieldCreate.asp) column, or a TEXT column that will increase in length each time you do something?
-PatP|||AUTONUMBER please..
It's not on ACCESS really, it's on a SQL server. But I figured it's about he same. No?|||create table tableA
(
id INTEGER IDENTITY(1, 1)
...
)
Thursday, March 22, 2012
Automatically Create Statistics
What are the benefits of allowing sql server to automatically create
statistics? I know that auto update statistic can have drawbacks on
performance.
Regards
JTC ^..^
Hi
Without up to date statistics, the query optimizer can make terrible
decisions and produce an execution plan that is not optimal. Query
performance then goes down the drain for those queries.
Updating statistics incurs a bit of an overhead and when it kicks in, causes
a delay in completing your data modification.
In SQL Server 2005, MS have added an feature of allowing statistics to be
updated as-synchronously, not as part of the data modification.
Unless you have a very specific situation, leave Auto Statistics on.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"JTC ^..^" <dave@.(nospam)JazzTheCat.co.uk> wrote in message
news:Xns96AFD050DD23daveJTC@.213.123.26.234...
> What are the benefits of allowing sql server to automatically create
> statistics? I know that auto update statistic can have drawbacks on
> performance.
> --
> Regards
> JTC ^..^
|||Thanks for you reply, but my question is specific to Automatically Creating
Statistics?
Regards
JTC ^..^
|||Here is an excellent article by Lubor that should help explaining things for
you.
http://msdn.microsoft.com/library/de...server2000.asp
-oj
"JTC ^..^" <dave@.(nospam)JazzTheCat.co.uk> wrote in message
news:Xns96AFD050DD23daveJTC@.213.123.26.234...
> What are the benefits of allowing sql server to automatically create
> statistics? I know that auto update statistic can have drawbacks on
> performance.
> --
> Regards
> JTC ^..^
|||I think Mike responding to auto-stats...
When auto-stats is turned on you can not control WHEN it runs, and it does
lots of IO and can interfere with other production work. Additionally,
Auto-stats will automatically do a sample of rows for large tables, instead
of doing a 100% sample, which is preferred and something which you can
specify when you run stats yourself.
I agree with Mike, unless you are experiencing problems which you can
specifically trace back to auto-stats running, leave it on..
However I would still schedule complete index rebuilds and/or stats creation
during your normal maintenance.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"JTC ^..^" <dave@.(nospam)JazzTheCat.co.uk> wrote in message
news:Xns96AFD050DD23daveJTC@.213.123.26.234...
> What are the benefits of allowing sql server to automatically create
> statistics? I know that auto update statistic can have drawbacks on
> performance.
> --
> Regards
> JTC ^..^
|||On Thu, 11 Aug 2005 19:27:37 +0000 (UTC), "JTC ^..^"
<dave@.(nospam)JazzTheCat.co.uk> wrote:
>What are the benefits of allowing sql server to automatically create
>statistics? I know that auto update statistic can have drawbacks on
>performance.
It's possible if you have an unusually static database with lots of
updates that don't change any keys, that turning off the auto
statistics could save you a tiny percentage. That is, the stats
computed on day one might be close enough for the next month, that you
could save a tiny bit of processing that updates them in real time.
Anybody ever do that on purpose?
J.
|||Did you forget "sync topic" oj? ;-)
My guess is that you meant to post below URL:
http://msdn.microsoft.com/library/de...asp?frame=true
JTC,
I see that many replied about auto-update statistics and you explicitly was asking about auto
*create* statistics. In short, there are situation where the optimizer would benefit from knowing
about the distribution of the data even if you don't have an index on the column. Perhaps it would
pick different execution plans for a GROUP BY depending on uniqueness, for example. For these
scenarios, it is good to let the optimizer create statistics to aid in picking a good query plan.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"oj" <nospam_ojngo@.home.com> wrote in message news:eTIS0HsnFHA.4028@.TK2MSFTNGP10.phx.gbl...
> Here is an excellent article by Lubor that should help explaining things for you.
> http://msdn.microsoft.com/library/de...server2000.asp
>
> --
> -oj
>
> "JTC ^..^" <dave@.(nospam)JazzTheCat.co.uk> wrote in message
> news:Xns96AFD050DD23daveJTC@.213.123.26.234...
>
|||argh...thanks for posting the correct link, Tibor. ;-)
-oj
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OzN6HOxnFHA.572@.TK2MSFTNGP15.phx.gbl...
> Did you forget "sync topic" oj? ;-)
> My guess is that you meant to post below URL:
> http://msdn.microsoft.com/library/de...asp?frame=true
>
> JTC,
> I see that many replied about auto-update statistics and you explicitly
> was asking about auto *create* statistics. In short, there are situation
> where the optimizer would benefit from knowing about the distribution of
> the data even if you don't have an index on the column. Perhaps it would
> pick different execution plans for a GROUP BY depending on uniqueness, for
> example. For these scenarios, it is good to let the optimizer create
> statistics to aid in picking a good query plan.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "oj" <nospam_ojngo@.home.com> wrote in message
> news:eTIS0HsnFHA.4028@.TK2MSFTNGP10.phx.gbl...
>
|||"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
in news:OzN6HOxnFHA.572@.TK2MSFTNGP15.phx.gbl:
> Did you forget "sync topic" oj? ;-)
> My guess is that you meant to post below URL:
> http://msdn.microsoft.com/library/de...y/en-us/dnsql2
> k/html/statquery.asp?frame=true
>
> JTC,
> I see that many replied about auto-update statistics and you
> explicitly was asking about auto *create* statistics. In short, there
> are situation where the optimizer would benefit from knowing about the
> distribution of the data even if you don't have an index on the
> column. Perhaps it would pick different execution plans for a GROUP BY
> depending on uniqueness, for example. For these scenarios, it is good
> to let the optimizer create statistics to aid in picking a good query
> plan.
>
Thanks Tibor. I should have made my original post even clearer.
Regards
JTC ^..^
|||Even if you were to try this, you could leave auto-create ON and auto-update
OFF in such a case to make sure that you have not missed any cases in your
queries where statistics are needed.
In general, please just leave them on unless you have a specific scenario
where performance is specifically impacted by auto-stats. In almost all of
our user cases, leaving this on has no impact.
Thanks,
Conor Cunningham
SQL Server Query Optimization Development Lead
"jxstern" <jxstern@.nowhere.xyz> wrote in message
news:a3unf1d2d4et3ocs5um5i8i7enoirejv16@.4ax.com...
> On Thu, 11 Aug 2005 19:27:37 +0000 (UTC), "JTC ^..^"
> <dave@.(nospam)JazzTheCat.co.uk> wrote:
> It's possible if you have an unusually static database with lots of
> updates that don't change any keys, that turning off the auto
> statistics could save you a tiny percentage. That is, the stats
> computed on day one might be close enough for the next month, that you
> could save a tiny bit of processing that updates them in real time.
> Anybody ever do that on purpose?
> J.
>
statistics? I know that auto update statistic can have drawbacks on
performance.
Regards
JTC ^..^
Hi
Without up to date statistics, the query optimizer can make terrible
decisions and produce an execution plan that is not optimal. Query
performance then goes down the drain for those queries.
Updating statistics incurs a bit of an overhead and when it kicks in, causes
a delay in completing your data modification.
In SQL Server 2005, MS have added an feature of allowing statistics to be
updated as-synchronously, not as part of the data modification.
Unless you have a very specific situation, leave Auto Statistics on.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"JTC ^..^" <dave@.(nospam)JazzTheCat.co.uk> wrote in message
news:Xns96AFD050DD23daveJTC@.213.123.26.234...
> What are the benefits of allowing sql server to automatically create
> statistics? I know that auto update statistic can have drawbacks on
> performance.
> --
> Regards
> JTC ^..^
|||Thanks for you reply, but my question is specific to Automatically Creating
Statistics?
Regards
JTC ^..^
|||Here is an excellent article by Lubor that should help explaining things for
you.
http://msdn.microsoft.com/library/de...server2000.asp
-oj
"JTC ^..^" <dave@.(nospam)JazzTheCat.co.uk> wrote in message
news:Xns96AFD050DD23daveJTC@.213.123.26.234...
> What are the benefits of allowing sql server to automatically create
> statistics? I know that auto update statistic can have drawbacks on
> performance.
> --
> Regards
> JTC ^..^
|||I think Mike responding to auto-stats...
When auto-stats is turned on you can not control WHEN it runs, and it does
lots of IO and can interfere with other production work. Additionally,
Auto-stats will automatically do a sample of rows for large tables, instead
of doing a 100% sample, which is preferred and something which you can
specify when you run stats yourself.
I agree with Mike, unless you are experiencing problems which you can
specifically trace back to auto-stats running, leave it on..
However I would still schedule complete index rebuilds and/or stats creation
during your normal maintenance.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"JTC ^..^" <dave@.(nospam)JazzTheCat.co.uk> wrote in message
news:Xns96AFD050DD23daveJTC@.213.123.26.234...
> What are the benefits of allowing sql server to automatically create
> statistics? I know that auto update statistic can have drawbacks on
> performance.
> --
> Regards
> JTC ^..^
|||On Thu, 11 Aug 2005 19:27:37 +0000 (UTC), "JTC ^..^"
<dave@.(nospam)JazzTheCat.co.uk> wrote:
>What are the benefits of allowing sql server to automatically create
>statistics? I know that auto update statistic can have drawbacks on
>performance.
It's possible if you have an unusually static database with lots of
updates that don't change any keys, that turning off the auto
statistics could save you a tiny percentage. That is, the stats
computed on day one might be close enough for the next month, that you
could save a tiny bit of processing that updates them in real time.
Anybody ever do that on purpose?
J.
|||Did you forget "sync topic" oj? ;-)
My guess is that you meant to post below URL:
http://msdn.microsoft.com/library/de...asp?frame=true
JTC,
I see that many replied about auto-update statistics and you explicitly was asking about auto
*create* statistics. In short, there are situation where the optimizer would benefit from knowing
about the distribution of the data even if you don't have an index on the column. Perhaps it would
pick different execution plans for a GROUP BY depending on uniqueness, for example. For these
scenarios, it is good to let the optimizer create statistics to aid in picking a good query plan.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"oj" <nospam_ojngo@.home.com> wrote in message news:eTIS0HsnFHA.4028@.TK2MSFTNGP10.phx.gbl...
> Here is an excellent article by Lubor that should help explaining things for you.
> http://msdn.microsoft.com/library/de...server2000.asp
>
> --
> -oj
>
> "JTC ^..^" <dave@.(nospam)JazzTheCat.co.uk> wrote in message
> news:Xns96AFD050DD23daveJTC@.213.123.26.234...
>
|||argh...thanks for posting the correct link, Tibor. ;-)
-oj
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OzN6HOxnFHA.572@.TK2MSFTNGP15.phx.gbl...
> Did you forget "sync topic" oj? ;-)
> My guess is that you meant to post below URL:
> http://msdn.microsoft.com/library/de...asp?frame=true
>
> JTC,
> I see that many replied about auto-update statistics and you explicitly
> was asking about auto *create* statistics. In short, there are situation
> where the optimizer would benefit from knowing about the distribution of
> the data even if you don't have an index on the column. Perhaps it would
> pick different execution plans for a GROUP BY depending on uniqueness, for
> example. For these scenarios, it is good to let the optimizer create
> statistics to aid in picking a good query plan.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "oj" <nospam_ojngo@.home.com> wrote in message
> news:eTIS0HsnFHA.4028@.TK2MSFTNGP10.phx.gbl...
>
|||"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
in news:OzN6HOxnFHA.572@.TK2MSFTNGP15.phx.gbl:
> Did you forget "sync topic" oj? ;-)
> My guess is that you meant to post below URL:
> http://msdn.microsoft.com/library/de...y/en-us/dnsql2
> k/html/statquery.asp?frame=true
>
> JTC,
> I see that many replied about auto-update statistics and you
> explicitly was asking about auto *create* statistics. In short, there
> are situation where the optimizer would benefit from knowing about the
> distribution of the data even if you don't have an index on the
> column. Perhaps it would pick different execution plans for a GROUP BY
> depending on uniqueness, for example. For these scenarios, it is good
> to let the optimizer create statistics to aid in picking a good query
> plan.
>
Thanks Tibor. I should have made my original post even clearer.
Regards
JTC ^..^
|||Even if you were to try this, you could leave auto-create ON and auto-update
OFF in such a case to make sure that you have not missed any cases in your
queries where statistics are needed.
In general, please just leave them on unless you have a specific scenario
where performance is specifically impacted by auto-stats. In almost all of
our user cases, leaving this on has no impact.
Thanks,
Conor Cunningham
SQL Server Query Optimization Development Lead
"jxstern" <jxstern@.nowhere.xyz> wrote in message
news:a3unf1d2d4et3ocs5um5i8i7enoirejv16@.4ax.com...
> On Thu, 11 Aug 2005 19:27:37 +0000 (UTC), "JTC ^..^"
> <dave@.(nospam)JazzTheCat.co.uk> wrote:
> It's possible if you have an unusually static database with lots of
> updates that don't change any keys, that turning off the auto
> statistics could save you a tiny percentage. That is, the stats
> computed on day one might be close enough for the next month, that you
> could save a tiny bit of processing that updates them in real time.
> Anybody ever do that on purpose?
> J.
>
Automatically Create Statistics
What are the benefits of allowing sql server to automatically create
statistics? I know that auto update statistic can have drawbacks on
performance.
--
Regards
JTC ^..^Hi
Without up to date statistics, the query optimizer can make terrible
decisions and produce an execution plan that is not optimal. Query
performance then goes down the drain for those queries.
Updating statistics incurs a bit of an overhead and when it kicks in, causes
a delay in completing your data modification.
In SQL Server 2005, MS have added an feature of allowing statistics to be
updated as-synchronously, not as part of the data modification.
Unless you have a very specific situation, leave Auto Statistics on.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"JTC ^..^" <dave@.(nospam)JazzTheCat.co.uk> wrote in message
news:Xns96AFD050DD23daveJTC@.213.123.26.234...
> What are the benefits of allowing sql server to automatically create
> statistics? I know that auto update statistic can have drawbacks on
> performance.
> --
> Regards
> JTC ^..^|||Thanks for you reply, but my question is specific to Automatically Creating
Statistics?
--
Regards
JTC ^..^|||Here is an excellent article by Lubor that should help explaining things for
you.
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnanchor/html/sqlserver2000.asp
-oj
"JTC ^..^" <dave@.(nospam)JazzTheCat.co.uk> wrote in message
news:Xns96AFD050DD23daveJTC@.213.123.26.234...
> What are the benefits of allowing sql server to automatically create
> statistics? I know that auto update statistic can have drawbacks on
> performance.
> --
> Regards
> JTC ^..^|||I think Mike responding to auto-stats...
When auto-stats is turned on you can not control WHEN it runs, and it does
lots of IO and can interfere with other production work. Additionally,
Auto-stats will automatically do a sample of rows for large tables, instead
of doing a 100% sample, which is preferred and something which you can
specify when you run stats yourself.
I agree with Mike, unless you are experiencing problems which you can
specifically trace back to auto-stats running, leave it on..
However I would still schedule complete index rebuilds and/or stats creation
during your normal maintenance.
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"JTC ^..^" <dave@.(nospam)JazzTheCat.co.uk> wrote in message
news:Xns96AFD050DD23daveJTC@.213.123.26.234...
> What are the benefits of allowing sql server to automatically create
> statistics? I know that auto update statistic can have drawbacks on
> performance.
> --
> Regards
> JTC ^..^|||On Thu, 11 Aug 2005 19:27:37 +0000 (UTC), "JTC ^..^"
<dave@.(nospam)JazzTheCat.co.uk> wrote:
>What are the benefits of allowing sql server to automatically create
>statistics? I know that auto update statistic can have drawbacks on
>performance.
It's possible if you have an unusually static database with lots of
updates that don't change any keys, that turning off the auto
statistics could save you a tiny percentage. That is, the stats
computed on day one might be close enough for the next month, that you
could save a tiny bit of processing that updates them in real time.
Anybody ever do that on purpose?
J.|||Did you forget "sync topic" oj? ;-)
My guess is that you meant to post below URL:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql2k/html/statquery.asp?frame=true
JTC,
I see that many replied about auto-update statistics and you explicitly was asking about auto
*create* statistics. In short, there are situation where the optimizer would benefit from knowing
about the distribution of the data even if you don't have an index on the column. Perhaps it would
pick different execution plans for a GROUP BY depending on uniqueness, for example. For these
scenarios, it is good to let the optimizer create statistics to aid in picking a good query plan.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"oj" <nospam_ojngo@.home.com> wrote in message news:eTIS0HsnFHA.4028@.TK2MSFTNGP10.phx.gbl...
> Here is an excellent article by Lubor that should help explaining things for you.
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnanchor/html/sqlserver2000.asp
>
> --
> -oj
>
> "JTC ^..^" <dave@.(nospam)JazzTheCat.co.uk> wrote in message
> news:Xns96AFD050DD23daveJTC@.213.123.26.234...
>> What are the benefits of allowing sql server to automatically create
>> statistics? I know that auto update statistic can have drawbacks on
>> performance.
>> --
>> Regards
>> JTC ^..^
>|||argh...thanks for posting the correct link, Tibor. ;-)
--
-oj
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OzN6HOxnFHA.572@.TK2MSFTNGP15.phx.gbl...
> Did you forget "sync topic" oj? ;-)
> My guess is that you meant to post below URL:
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql2k/html/statquery.asp?frame=true
>
> JTC,
> I see that many replied about auto-update statistics and you explicitly
> was asking about auto *create* statistics. In short, there are situation
> where the optimizer would benefit from knowing about the distribution of
> the data even if you don't have an index on the column. Perhaps it would
> pick different execution plans for a GROUP BY depending on uniqueness, for
> example. For these scenarios, it is good to let the optimizer create
> statistics to aid in picking a good query plan.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "oj" <nospam_ojngo@.home.com> wrote in message
> news:eTIS0HsnFHA.4028@.TK2MSFTNGP10.phx.gbl...
>> Here is an excellent article by Lubor that should help explaining things
>> for you.
>> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnanchor/html/sqlserver2000.asp
>>
>> --
>> -oj
>>
>> "JTC ^..^" <dave@.(nospam)JazzTheCat.co.uk> wrote in message
>> news:Xns96AFD050DD23daveJTC@.213.123.26.234...
>> What are the benefits of allowing sql server to automatically create
>> statistics? I know that auto update statistic can have drawbacks on
>> performance.
>> --
>> Regards
>> JTC ^..^
>>
>|||"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
in news:OzN6HOxnFHA.572@.TK2MSFTNGP15.phx.gbl:
> Did you forget "sync topic" oj? ;-)
> My guess is that you meant to post below URL:
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql2
> k/html/statquery.asp?frame=true
>
> JTC,
> I see that many replied about auto-update statistics and you
> explicitly was asking about auto *create* statistics. In short, there
> are situation where the optimizer would benefit from knowing about the
> distribution of the data even if you don't have an index on the
> column. Perhaps it would pick different execution plans for a GROUP BY
> depending on uniqueness, for example. For these scenarios, it is good
> to let the optimizer create statistics to aid in picking a good query
> plan.
>
Thanks Tibor. I should have made my original post even clearer.
--
Regards
JTC ^..^|||Even if you were to try this, you could leave auto-create ON and auto-update
OFF in such a case to make sure that you have not missed any cases in your
queries where statistics are needed.
In general, please just leave them on unless you have a specific scenario
where performance is specifically impacted by auto-stats. In almost all of
our user cases, leaving this on has no impact.
Thanks,
Conor Cunningham
SQL Server Query Optimization Development Lead
"jxstern" <jxstern@.nowhere.xyz> wrote in message
news:a3unf1d2d4et3ocs5um5i8i7enoirejv16@.4ax.com...
> On Thu, 11 Aug 2005 19:27:37 +0000 (UTC), "JTC ^..^"
> <dave@.(nospam)JazzTheCat.co.uk> wrote:
>>What are the benefits of allowing sql server to automatically create
>>statistics? I know that auto update statistic can have drawbacks on
>>performance.
> It's possible if you have an unusually static database with lots of
> updates that don't change any keys, that turning off the auto
> statistics could save you a tiny percentage. That is, the stats
> computed on day one might be close enough for the next month, that you
> could save a tiny bit of processing that updates them in real time.
> Anybody ever do that on purpose?
> J.
>
statistics? I know that auto update statistic can have drawbacks on
performance.
--
Regards
JTC ^..^Hi
Without up to date statistics, the query optimizer can make terrible
decisions and produce an execution plan that is not optimal. Query
performance then goes down the drain for those queries.
Updating statistics incurs a bit of an overhead and when it kicks in, causes
a delay in completing your data modification.
In SQL Server 2005, MS have added an feature of allowing statistics to be
updated as-synchronously, not as part of the data modification.
Unless you have a very specific situation, leave Auto Statistics on.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"JTC ^..^" <dave@.(nospam)JazzTheCat.co.uk> wrote in message
news:Xns96AFD050DD23daveJTC@.213.123.26.234...
> What are the benefits of allowing sql server to automatically create
> statistics? I know that auto update statistic can have drawbacks on
> performance.
> --
> Regards
> JTC ^..^|||Thanks for you reply, but my question is specific to Automatically Creating
Statistics?
--
Regards
JTC ^..^|||Here is an excellent article by Lubor that should help explaining things for
you.
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnanchor/html/sqlserver2000.asp
-oj
"JTC ^..^" <dave@.(nospam)JazzTheCat.co.uk> wrote in message
news:Xns96AFD050DD23daveJTC@.213.123.26.234...
> What are the benefits of allowing sql server to automatically create
> statistics? I know that auto update statistic can have drawbacks on
> performance.
> --
> Regards
> JTC ^..^|||I think Mike responding to auto-stats...
When auto-stats is turned on you can not control WHEN it runs, and it does
lots of IO and can interfere with other production work. Additionally,
Auto-stats will automatically do a sample of rows for large tables, instead
of doing a 100% sample, which is preferred and something which you can
specify when you run stats yourself.
I agree with Mike, unless you are experiencing problems which you can
specifically trace back to auto-stats running, leave it on..
However I would still schedule complete index rebuilds and/or stats creation
during your normal maintenance.
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"JTC ^..^" <dave@.(nospam)JazzTheCat.co.uk> wrote in message
news:Xns96AFD050DD23daveJTC@.213.123.26.234...
> What are the benefits of allowing sql server to automatically create
> statistics? I know that auto update statistic can have drawbacks on
> performance.
> --
> Regards
> JTC ^..^|||On Thu, 11 Aug 2005 19:27:37 +0000 (UTC), "JTC ^..^"
<dave@.(nospam)JazzTheCat.co.uk> wrote:
>What are the benefits of allowing sql server to automatically create
>statistics? I know that auto update statistic can have drawbacks on
>performance.
It's possible if you have an unusually static database with lots of
updates that don't change any keys, that turning off the auto
statistics could save you a tiny percentage. That is, the stats
computed on day one might be close enough for the next month, that you
could save a tiny bit of processing that updates them in real time.
Anybody ever do that on purpose?
J.|||Did you forget "sync topic" oj? ;-)
My guess is that you meant to post below URL:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql2k/html/statquery.asp?frame=true
JTC,
I see that many replied about auto-update statistics and you explicitly was asking about auto
*create* statistics. In short, there are situation where the optimizer would benefit from knowing
about the distribution of the data even if you don't have an index on the column. Perhaps it would
pick different execution plans for a GROUP BY depending on uniqueness, for example. For these
scenarios, it is good to let the optimizer create statistics to aid in picking a good query plan.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"oj" <nospam_ojngo@.home.com> wrote in message news:eTIS0HsnFHA.4028@.TK2MSFTNGP10.phx.gbl...
> Here is an excellent article by Lubor that should help explaining things for you.
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnanchor/html/sqlserver2000.asp
>
> --
> -oj
>
> "JTC ^..^" <dave@.(nospam)JazzTheCat.co.uk> wrote in message
> news:Xns96AFD050DD23daveJTC@.213.123.26.234...
>> What are the benefits of allowing sql server to automatically create
>> statistics? I know that auto update statistic can have drawbacks on
>> performance.
>> --
>> Regards
>> JTC ^..^
>|||argh...thanks for posting the correct link, Tibor. ;-)
--
-oj
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OzN6HOxnFHA.572@.TK2MSFTNGP15.phx.gbl...
> Did you forget "sync topic" oj? ;-)
> My guess is that you meant to post below URL:
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql2k/html/statquery.asp?frame=true
>
> JTC,
> I see that many replied about auto-update statistics and you explicitly
> was asking about auto *create* statistics. In short, there are situation
> where the optimizer would benefit from knowing about the distribution of
> the data even if you don't have an index on the column. Perhaps it would
> pick different execution plans for a GROUP BY depending on uniqueness, for
> example. For these scenarios, it is good to let the optimizer create
> statistics to aid in picking a good query plan.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "oj" <nospam_ojngo@.home.com> wrote in message
> news:eTIS0HsnFHA.4028@.TK2MSFTNGP10.phx.gbl...
>> Here is an excellent article by Lubor that should help explaining things
>> for you.
>> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnanchor/html/sqlserver2000.asp
>>
>> --
>> -oj
>>
>> "JTC ^..^" <dave@.(nospam)JazzTheCat.co.uk> wrote in message
>> news:Xns96AFD050DD23daveJTC@.213.123.26.234...
>> What are the benefits of allowing sql server to automatically create
>> statistics? I know that auto update statistic can have drawbacks on
>> performance.
>> --
>> Regards
>> JTC ^..^
>>
>|||"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
in news:OzN6HOxnFHA.572@.TK2MSFTNGP15.phx.gbl:
> Did you forget "sync topic" oj? ;-)
> My guess is that you meant to post below URL:
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql2
> k/html/statquery.asp?frame=true
>
> JTC,
> I see that many replied about auto-update statistics and you
> explicitly was asking about auto *create* statistics. In short, there
> are situation where the optimizer would benefit from knowing about the
> distribution of the data even if you don't have an index on the
> column. Perhaps it would pick different execution plans for a GROUP BY
> depending on uniqueness, for example. For these scenarios, it is good
> to let the optimizer create statistics to aid in picking a good query
> plan.
>
Thanks Tibor. I should have made my original post even clearer.
--
Regards
JTC ^..^|||Even if you were to try this, you could leave auto-create ON and auto-update
OFF in such a case to make sure that you have not missed any cases in your
queries where statistics are needed.
In general, please just leave them on unless you have a specific scenario
where performance is specifically impacted by auto-stats. In almost all of
our user cases, leaving this on has no impact.
Thanks,
Conor Cunningham
SQL Server Query Optimization Development Lead
"jxstern" <jxstern@.nowhere.xyz> wrote in message
news:a3unf1d2d4et3ocs5um5i8i7enoirejv16@.4ax.com...
> On Thu, 11 Aug 2005 19:27:37 +0000 (UTC), "JTC ^..^"
> <dave@.(nospam)JazzTheCat.co.uk> wrote:
>>What are the benefits of allowing sql server to automatically create
>>statistics? I know that auto update statistic can have drawbacks on
>>performance.
> It's possible if you have an unusually static database with lots of
> updates that don't change any keys, that turning off the auto
> statistics could save you a tiny percentage. That is, the stats
> computed on day one might be close enough for the next month, that you
> could save a tiny bit of processing that updates them in real time.
> Anybody ever do that on purpose?
> J.
>
Automatically Create Statistics
What are the benefits of allowing sql server to automatically create
statistics? I know that auto update statistic can have drawbacks on
performance.
Regards
JTC ^..^Hi
Without up to date statistics, the query optimizer can make terrible
decisions and produce an execution plan that is not optimal. Query
performance then goes down the drain for those queries.
Updating statistics incurs a bit of an overhead and when it kicks in, causes
a delay in completing your data modification.
In SQL Server 2005, MS have added an feature of allowing statistics to be
updated as-synchronously, not as part of the data modification.
Unless you have a very specific situation, leave Auto Statistics on.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"JTC ^..^" <dave@.(nospam)JazzTheCat.co.uk> wrote in message
news:Xns96AFD050DD23daveJTC@.213.123.26.234...
> What are the benefits of allowing sql server to automatically create
> statistics? I know that auto update statistic can have drawbacks on
> performance.
> --
> Regards
> JTC ^..^|||Thanks for you reply, but my question is specific to Automatically Creating
Statistics?
Regards
JTC ^..^|||Here is an excellent article by Lubor that should help explaining things for
you.
http://msdn.microsoft.com/library/d...
server2000.asp
-oj
"JTC ^..^" <dave@.(nospam)JazzTheCat.co.uk> wrote in message
news:Xns96AFD050DD23daveJTC@.213.123.26.234...
> What are the benefits of allowing sql server to automatically create
> statistics? I know that auto update statistic can have drawbacks on
> performance.
> --
> Regards
> JTC ^..^|||I think Mike responding to auto-stats...
When auto-stats is turned on you can not control WHEN it runs, and it does
lots of IO and can interfere with other production work. Additionally,
Auto-stats will automatically do a sample of rows for large tables, instead
of doing a 100% sample, which is preferred and something which you can
specify when you run stats yourself.
I agree with Mike, unless you are experiencing problems which you can
specifically trace back to auto-stats running, leave it on..
However I would still schedule complete index rebuilds and/or stats creation
during your normal maintenance.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"JTC ^..^" <dave@.(nospam)JazzTheCat.co.uk> wrote in message
news:Xns96AFD050DD23daveJTC@.213.123.26.234...
> What are the benefits of allowing sql server to automatically create
> statistics? I know that auto update statistic can have drawbacks on
> performance.
> --
> Regards
> JTC ^..^|||On Thu, 11 Aug 2005 19:27:37 +0000 (UTC), "JTC ^..^"
<dave@.(nospam)JazzTheCat.co.uk> wrote:
>What are the benefits of allowing sql server to automatically create
>statistics? I know that auto update statistic can have drawbacks on
>performance.
It's possible if you have an unusually static database with lots of
updates that don't change any keys, that turning off the auto
statistics could save you a tiny percentage. That is, the stats
computed on day one might be close enough for the next month, that you
could save a tiny bit of processing that updates them in real time.
Anybody ever do that on purpose?
J.|||Did you forget "sync topic" oj? ;-)
My guess is that you meant to post below URL:
http://msdn.microsoft.com/library/d...asp?frame=true
JTC,
I see that many replied about auto-update statistics and you explicitly was
asking about auto
*create* statistics. In short, there are situation where the optimizer would
benefit from knowing
about the distribution of the data even if you don't have an index on the co
lumn. Perhaps it would
pick different execution plans for a GROUP BY depending on uniqueness, for e
xample. For these
scenarios, it is good to let the optimizer create statistics to aid in picki
ng a good query plan.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"oj" <nospam_ojngo@.home.com> wrote in message news:eTIS0HsnFHA.4028@.TK2MSFTNGP10.phx.gbl...[
vbcol=seagreen]
> Here is an excellent article by Lubor that should help explaining things f
or you.
> http://msdn.microsoft.com/library/d...lserver2000.asp
>
> --
> -oj
>
> "JTC ^..^" <dave@.(nospam)JazzTheCat.co.uk> wrote in message
> news:Xns96AFD050DD23daveJTC@.213.123.26.234...
>[/vbcol]|||argh...thanks for posting the correct link, Tibor. ;-)
-oj
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OzN6HOxnFHA.572@.TK2MSFTNGP15.phx.gbl...
> Did you forget "sync topic" oj? ;-)
> My guess is that you meant to post below URL:
> http://msdn.microsoft.com/library/d...asp?frame=true
>
> JTC,
> I see that many replied about auto-update statistics and you explicitly
> was asking about auto *create* statistics. In short, there are situation
> where the optimizer would benefit from knowing about the distribution of
> the data even if you don't have an index on the column. Perhaps it would
> pick different execution plans for a GROUP BY depending on uniqueness, for
> example. For these scenarios, it is good to let the optimizer create
> statistics to aid in picking a good query plan.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "oj" <nospam_ojngo@.home.com> wrote in message
> news:eTIS0HsnFHA.4028@.TK2MSFTNGP10.phx.gbl...
>|||"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
in news:OzN6HOxnFHA.572@.TK2MSFTNGP15.phx.gbl:
> Did you forget "sync topic" oj? ;-)
> My guess is that you meant to post below URL:
> http://msdn.microsoft.com/library/d...ry/en-us/dnsql2
> k/html/statquery.asp?frame=true
>
> JTC,
> I see that many replied about auto-update statistics and you
> explicitly was asking about auto *create* statistics. In short, there
> are situation where the optimizer would benefit from knowing about the
> distribution of the data even if you don't have an index on the
> column. Perhaps it would pick different execution plans for a GROUP BY
> depending on uniqueness, for example. For these scenarios, it is good
> to let the optimizer create statistics to aid in picking a good query
> plan.
>
Thanks Tibor. I should have made my original post even clearer.
--
Regards
JTC ^..^|||Even if you were to try this, you could leave auto-create ON and auto-update
OFF in such a case to make sure that you have not missed any cases in your
queries where statistics are needed.
In general, please just leave them on unless you have a specific scenario
where performance is specifically impacted by auto-stats. In almost all of
our user cases, leaving this on has no impact.
Thanks,
Conor Cunningham
SQL Server Query Optimization Development Lead
"jxstern" <jxstern@.nowhere.xyz> wrote in message
news:a3unf1d2d4et3ocs5um5i8i7enoirejv16@.
4ax.com...
> On Thu, 11 Aug 2005 19:27:37 +0000 (UTC), "JTC ^..^"
> <dave@.(nospam)JazzTheCat.co.uk> wrote:
> It's possible if you have an unusually static database with lots of
> updates that don't change any keys, that turning off the auto
> statistics could save you a tiny percentage. That is, the stats
> computed on day one might be close enough for the next month, that you
> could save a tiny bit of processing that updates them in real time.
> Anybody ever do that on purpose?
> J.
>
statistics? I know that auto update statistic can have drawbacks on
performance.
Regards
JTC ^..^Hi
Without up to date statistics, the query optimizer can make terrible
decisions and produce an execution plan that is not optimal. Query
performance then goes down the drain for those queries.
Updating statistics incurs a bit of an overhead and when it kicks in, causes
a delay in completing your data modification.
In SQL Server 2005, MS have added an feature of allowing statistics to be
updated as-synchronously, not as part of the data modification.
Unless you have a very specific situation, leave Auto Statistics on.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"JTC ^..^" <dave@.(nospam)JazzTheCat.co.uk> wrote in message
news:Xns96AFD050DD23daveJTC@.213.123.26.234...
> What are the benefits of allowing sql server to automatically create
> statistics? I know that auto update statistic can have drawbacks on
> performance.
> --
> Regards
> JTC ^..^|||Thanks for you reply, but my question is specific to Automatically Creating
Statistics?
Regards
JTC ^..^|||Here is an excellent article by Lubor that should help explaining things for
you.
http://msdn.microsoft.com/library/d...
server2000.asp
-oj
"JTC ^..^" <dave@.(nospam)JazzTheCat.co.uk> wrote in message
news:Xns96AFD050DD23daveJTC@.213.123.26.234...
> What are the benefits of allowing sql server to automatically create
> statistics? I know that auto update statistic can have drawbacks on
> performance.
> --
> Regards
> JTC ^..^|||I think Mike responding to auto-stats...
When auto-stats is turned on you can not control WHEN it runs, and it does
lots of IO and can interfere with other production work. Additionally,
Auto-stats will automatically do a sample of rows for large tables, instead
of doing a 100% sample, which is preferred and something which you can
specify when you run stats yourself.
I agree with Mike, unless you are experiencing problems which you can
specifically trace back to auto-stats running, leave it on..
However I would still schedule complete index rebuilds and/or stats creation
during your normal maintenance.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"JTC ^..^" <dave@.(nospam)JazzTheCat.co.uk> wrote in message
news:Xns96AFD050DD23daveJTC@.213.123.26.234...
> What are the benefits of allowing sql server to automatically create
> statistics? I know that auto update statistic can have drawbacks on
> performance.
> --
> Regards
> JTC ^..^|||On Thu, 11 Aug 2005 19:27:37 +0000 (UTC), "JTC ^..^"
<dave@.(nospam)JazzTheCat.co.uk> wrote:
>What are the benefits of allowing sql server to automatically create
>statistics? I know that auto update statistic can have drawbacks on
>performance.
It's possible if you have an unusually static database with lots of
updates that don't change any keys, that turning off the auto
statistics could save you a tiny percentage. That is, the stats
computed on day one might be close enough for the next month, that you
could save a tiny bit of processing that updates them in real time.
Anybody ever do that on purpose?
J.|||Did you forget "sync topic" oj? ;-)
My guess is that you meant to post below URL:
http://msdn.microsoft.com/library/d...asp?frame=true
JTC,
I see that many replied about auto-update statistics and you explicitly was
asking about auto
*create* statistics. In short, there are situation where the optimizer would
benefit from knowing
about the distribution of the data even if you don't have an index on the co
lumn. Perhaps it would
pick different execution plans for a GROUP BY depending on uniqueness, for e
xample. For these
scenarios, it is good to let the optimizer create statistics to aid in picki
ng a good query plan.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"oj" <nospam_ojngo@.home.com> wrote in message news:eTIS0HsnFHA.4028@.TK2MSFTNGP10.phx.gbl...[
vbcol=seagreen]
> Here is an excellent article by Lubor that should help explaining things f
or you.
> http://msdn.microsoft.com/library/d...lserver2000.asp
>
> --
> -oj
>
> "JTC ^..^" <dave@.(nospam)JazzTheCat.co.uk> wrote in message
> news:Xns96AFD050DD23daveJTC@.213.123.26.234...
>[/vbcol]|||argh...thanks for posting the correct link, Tibor. ;-)
-oj
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OzN6HOxnFHA.572@.TK2MSFTNGP15.phx.gbl...
> Did you forget "sync topic" oj? ;-)
> My guess is that you meant to post below URL:
> http://msdn.microsoft.com/library/d...asp?frame=true
>
> JTC,
> I see that many replied about auto-update statistics and you explicitly
> was asking about auto *create* statistics. In short, there are situation
> where the optimizer would benefit from knowing about the distribution of
> the data even if you don't have an index on the column. Perhaps it would
> pick different execution plans for a GROUP BY depending on uniqueness, for
> example. For these scenarios, it is good to let the optimizer create
> statistics to aid in picking a good query plan.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "oj" <nospam_ojngo@.home.com> wrote in message
> news:eTIS0HsnFHA.4028@.TK2MSFTNGP10.phx.gbl...
>|||"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
in news:OzN6HOxnFHA.572@.TK2MSFTNGP15.phx.gbl:
> Did you forget "sync topic" oj? ;-)
> My guess is that you meant to post below URL:
> http://msdn.microsoft.com/library/d...ry/en-us/dnsql2
> k/html/statquery.asp?frame=true
>
> JTC,
> I see that many replied about auto-update statistics and you
> explicitly was asking about auto *create* statistics. In short, there
> are situation where the optimizer would benefit from knowing about the
> distribution of the data even if you don't have an index on the
> column. Perhaps it would pick different execution plans for a GROUP BY
> depending on uniqueness, for example. For these scenarios, it is good
> to let the optimizer create statistics to aid in picking a good query
> plan.
>
Thanks Tibor. I should have made my original post even clearer.
--
Regards
JTC ^..^|||Even if you were to try this, you could leave auto-create ON and auto-update
OFF in such a case to make sure that you have not missed any cases in your
queries where statistics are needed.
In general, please just leave them on unless you have a specific scenario
where performance is specifically impacted by auto-stats. In almost all of
our user cases, leaving this on has no impact.
Thanks,
Conor Cunningham
SQL Server Query Optimization Development Lead
"jxstern" <jxstern@.nowhere.xyz> wrote in message
news:a3unf1d2d4et3ocs5um5i8i7enoirejv16@.
4ax.com...
> On Thu, 11 Aug 2005 19:27:37 +0000 (UTC), "JTC ^..^"
> <dave@.(nospam)JazzTheCat.co.uk> wrote:
> It's possible if you have an unusually static database with lots of
> updates that don't change any keys, that turning off the auto
> statistics could save you a tiny percentage. That is, the stats
> computed on day one might be close enough for the next month, that you
> could save a tiny bit of processing that updates them in real time.
> Anybody ever do that on purpose?
> J.
>
Automatically create rows
Is there a way to automatically insert a row into a table when a row is
created in another table?
For example, suppose a row is added to the "Current Data" table. I would
like another table, "Historical Data", to be automatically updated with data
from from the row added to "Current Data". Is this possible? If so how?
Thanks in advance for any help!Read-up on triggers in SQL Server Books Online. Triggers can be written to
respond to various DML statements and can do operations like inserting into
other tables etc.
--
HTH,
SriSamp
Email: srisamp@.gmail.com
Blog: http://blogs.sqlxml.org/srinivassampath
URL: http://www32.brinkster.com/srisamp
"Matt" <Matt@.discussions.microsoft.com> wrote in message
news:AF9C3D3C-518F-47DE-BF9B-9F4A0C544449@.microsoft.com...
> Is there a way to automatically insert a row into a table when a row is
> created in another table?
> For example, suppose a row is added to the "Current Data" table. I would
> like another table, "Historical Data", to be automatically updated with
> data
> from from the row added to "Current Data". Is this possible? If so how?
> Thanks in advance for any help!
>|||Matt
Lookup CREATE TRIGGER ... ON Table FOR INSERT,UPDATE in the BOL
"Matt" <Matt@.discussions.microsoft.com> wrote in message
news:AF9C3D3C-518F-47DE-BF9B-9F4A0C544449@.microsoft.com...
> Is there a way to automatically insert a row into a table when a row is
> created in another table?
> For example, suppose a row is added to the "Current Data" table. I would
> like another table, "Historical Data", to be automatically updated with
> data
> from from the row added to "Current Data". Is this possible? If so how?
> Thanks in advance for any help!
>sql
created in another table?
For example, suppose a row is added to the "Current Data" table. I would
like another table, "Historical Data", to be automatically updated with data
from from the row added to "Current Data". Is this possible? If so how?
Thanks in advance for any help!Read-up on triggers in SQL Server Books Online. Triggers can be written to
respond to various DML statements and can do operations like inserting into
other tables etc.
--
HTH,
SriSamp
Email: srisamp@.gmail.com
Blog: http://blogs.sqlxml.org/srinivassampath
URL: http://www32.brinkster.com/srisamp
"Matt" <Matt@.discussions.microsoft.com> wrote in message
news:AF9C3D3C-518F-47DE-BF9B-9F4A0C544449@.microsoft.com...
> Is there a way to automatically insert a row into a table when a row is
> created in another table?
> For example, suppose a row is added to the "Current Data" table. I would
> like another table, "Historical Data", to be automatically updated with
> data
> from from the row added to "Current Data". Is this possible? If so how?
> Thanks in advance for any help!
>|||Matt
Lookup CREATE TRIGGER ... ON Table FOR INSERT,UPDATE in the BOL
"Matt" <Matt@.discussions.microsoft.com> wrote in message
news:AF9C3D3C-518F-47DE-BF9B-9F4A0C544449@.microsoft.com...
> Is there a way to automatically insert a row into a table when a row is
> created in another table?
> For example, suppose a row is added to the "Current Data" table. I would
> like another table, "Historical Data", to be automatically updated with
> data
> from from the row added to "Current Data". Is this possible? If so how?
> Thanks in advance for any help!
>sql
Automatically create ODBC connections
Hello everybody!
I'm using the 'Data Sources (ODBC)' program that comes with windows to
create the odbc connections I need. Although that is quite fast and easy I
would like to have it more automated, since I'm using the same parameters
all the time.
Can anybody tell me (or give pointers to) how that is done?
regards
--
Johnny LjunggrenJohnny Ljunggren <johnny@.navtek.no> wrote in message news:<pan.2004.08.18.07.59.24.480484@.navtek.no>...
> Hello everybody!
> I'm using the 'Data Sources (ODBC)' program that comes with windows to
> create the odbc connections I need. Although that is quite fast and easy I
> would like to have it more automated, since I'm using the same parameters
> all the time.
> Can anybody tell me (or give pointers to) how that is done?
> regards
This article (or one of the ones linked from it) might be useful:
http://support.microsoft.com/defaul...b;en-us;Q184608
If not, you should probably ask this in an ODBC group, since it's not
really an MSSQL question.
Simon|||Den Wed, 18 Aug 2004 07:13:16 -0700, skrev Simon Hayes:
>> I'm using the 'Data Sources (ODBC)' program that comes with windows to
>> create the odbc connections I need. Although that is quite fast and easy I
>> would like to have it more automated, since I'm using the same parameters
>> all the time.
>> Can anybody tell me (or give pointers to) how that is done?
> This article (or one of the ones linked from it) might be useful:
> http://support.microsoft.com/defaul...b;en-us;Q184608
Looks like this might solve my problem. Thanks a lot.
> If not, you should probably ask this in an ODBC group, since it's not
> really an MSSQL question.
I know, but my newsprovider doesn't have any odbc-groups, so I thought
this was close enough....
--
Johnny Ljunggren
Automatically Create Linked Server?
I am trying to automatically create linked servers, then extract the DB
names from them and delete the linked server. This is for an inventory
program I am writing to keep info on all of our SQL Server up to date.
The problem I run into is the linked server procedure, even when run with
Exec(), does not run unless I separate it in its own batch, then variables I
created in my script are out of scope.
Nutshell of the sequence:
1. Create cursor with list of DB servers from table on inventory server.
2. Begin cursor loop.
3. Create linked server for the server from the list.
4. Query server for list of user databases on that server.
5. Begin another loop, using While, to insert data retrieved into inventory
database.
6. Delete linked server.
7. Go back to beginning and do next server in list.
The code:
Declare @.DBID Int --Floating DB ID number.
Declare @.MaxDB Int --Highest DB ID number.
Declare @.DBName VarChar(100)
Declare @.LastID Int --The value of the identity field for the last insert.
Declare @.ServerID Int
Declare @.Server VarChar(100)
Declare @.SQL1 VarChar(500)
Declare DBServers Cursor FAST_FORWARD For
Select S.seName, S.seServerID From Servers S Inner Join
ServerAppLink SAL On S.seServerID = SAL.slServerIDse
Open DBServers
Fetch Next From DBServers Into @.Server, @.ServerID
Print @.Server
While @.@.Fetch_Status = 0
Begin
Set @.SQL1 ='sp_AddLinkedServer @.server= ''Temp'', @.srvproduct= '''',
@.provider= ''SQLOLEDB'',
@.datasrc= ' + @.Server +',
@.catalog= ''master'''
Exec (@.SQL1)
Set @.SQL1 = 'sp_AddLinkedSrvLogin @.rmtsrvname = ''Temp'',
@.useself= ''True'', @.locallogin = ''domain\username'''
Exec (@.SQL1)
Select @.DBID = Min(dbid) From Temp.Master.dbo.sysdatabases Where sid <>
0x01
While @.DBID <= @.MaxDB
Begin
Set @.SQL1 = 'Select @.DBName = name from Temp.Master.dbo.sysdatabases
Where dbid = ' + @.DBID
Exec(@.SQL1)
Insert Into Databases (dbName, dbType)
Values(@.DBName, 'MSSQL')
Select @.LastID = @.@.Identity
Insert Into DBLink (dlDBIDdb, dlServerIDse)
Values(@.LastID, @.ServerID)
Set @.DBID = @.DBID + 1
End
Exec sp_dropserver @.server = 'Temp' , @.droplogins = 'droplogins'
Fetch Next From DBServers Into @.Server, @.ServerID
End
Close DBServers
DeAllocate DBServers
This works great, and I have no security issues, if I run the linked server
part separate from the Select from sysdatabases part. I tried putting a "GO"
after the sp_addlinkedserver, but that just put all of my variables out of
scope.
Any ideas?
Thanks,
Chris Stamey
(reply to newsgroup please)
Chris,
Have you looked at sp_executesql?
Ilya
"Torquin" <Torquin@.nospam.nospam> wrote in message
news:ubnSRYy9EHA.3376@.TK2MSFTNGP12.phx.gbl...
> I am trying to automatically create linked servers, then extract the DB
> names from them and delete the linked server. This is for an inventory
> program I am writing to keep info on all of our SQL Server up to date.
> The problem I run into is the linked server procedure, even when run with
> Exec(), does not run unless I separate it in its own batch, then variables
I
> created in my script are out of scope.
> Nutshell of the sequence:
> 1. Create cursor with list of DB servers from table on inventory server.
> 2. Begin cursor loop.
> 3. Create linked server for the server from the list.
> 4. Query server for list of user databases on that server.
> 5. Begin another loop, using While, to insert data retrieved into
inventory
> database.
> 6. Delete linked server.
> 7. Go back to beginning and do next server in list.
> The code:
> Declare @.DBID Int --Floating DB ID number.
> Declare @.MaxDB Int --Highest DB ID number.
> Declare @.DBName VarChar(100)
> Declare @.LastID Int --The value of the identity field for the last insert.
> Declare @.ServerID Int
> Declare @.Server VarChar(100)
> Declare @.SQL1 VarChar(500)
> Declare DBServers Cursor FAST_FORWARD For
> Select S.seName, S.seServerID From Servers S Inner Join
> ServerAppLink SAL On S.seServerID = SAL.slServerIDse
> Open DBServers
> Fetch Next From DBServers Into @.Server, @.ServerID
> Print @.Server
> While @.@.Fetch_Status = 0
> Begin
> Set @.SQL1 ='sp_AddLinkedServer @.server= ''Temp'', @.srvproduct= '''',
> @.provider= ''SQLOLEDB'',
> @.datasrc= ' + @.Server +',
> @.catalog= ''master'''
> Exec (@.SQL1)
> Set @.SQL1 = 'sp_AddLinkedSrvLogin @.rmtsrvname = ''Temp'',
> @.useself= ''True'', @.locallogin = ''domain\username'''
> Exec (@.SQL1)
> Select @.DBID = Min(dbid) From Temp.Master.dbo.sysdatabases Where sid <>
> 0x01
> While @.DBID <= @.MaxDB
> Begin
> Set @.SQL1 = 'Select @.DBName = name from Temp.Master.dbo.sysdatabases
> Where dbid = ' + @.DBID
> Exec(@.SQL1)
> Insert Into Databases (dbName, dbType)
> Values(@.DBName, 'MSSQL')
> Select @.LastID = @.@.Identity
> Insert Into DBLink (dlDBIDdb, dlServerIDse)
> Values(@.LastID, @.ServerID)
> Set @.DBID = @.DBID + 1
> End
> Exec sp_dropserver @.server = 'Temp' , @.droplogins = 'droplogins'
> Fetch Next From DBServers Into @.Server, @.ServerID
> End
> Close DBServers
> DeAllocate DBServers
> This works great, and I have no security issues, if I run the linked
server
> part separate from the Select from sysdatabases part. I tried putting a
"GO"
> after the sp_addlinkedserver, but that just put all of my variables out of
> scope.
> Any ideas?
> Thanks,
> Chris Stamey
> (reply to newsgroup please)
>
|||I have not. I will look into that and see if it can help this situation.
Thanks,
Chris
"Ilya Margolin" <ilya_no_spam_@.unapen.com> wrote in message
news:%238oTdtz9EHA.1452@.TK2MSFTNGP11.phx.gbl...[vbcol=seagreen]
> Chris,
> Have you looked at sp_executesql?
> Ilya
> "Torquin" <Torquin@.nospam.nospam> wrote in message
> news:ubnSRYy9EHA.3376@.TK2MSFTNGP12.phx.gbl...
with[vbcol=seagreen]
variables[vbcol=seagreen]
> I
> inventory
insert.[vbcol=seagreen]
<>[vbcol=seagreen]
> server
> "GO"
of
>
|||No, sp_ExecuteSQL does not help in this situation.
Thanks,
Chris
"Ilya Margolin" <ilya_no_spam_@.unapen.com> wrote in message
news:%238oTdtz9EHA.1452@.TK2MSFTNGP11.phx.gbl...[vbcol=seagreen]
> Chris,
> Have you looked at sp_executesql?
> Ilya
> "Torquin" <Torquin@.nospam.nospam> wrote in message
> news:ubnSRYy9EHA.3376@.TK2MSFTNGP12.phx.gbl...
with[vbcol=seagreen]
variables[vbcol=seagreen]
> I
> inventory
insert.[vbcol=seagreen]
<>[vbcol=seagreen]
> server
> "GO"
of
>
|||How about using an OpenRowSet() instead of the adding and dropping linked
servers.
As part of the query, just pull everything from sysdatabases into a local
table level variable and process it that way?
NOTE: I did not test this, it's just an idea.
Rick Sawtell
MCT, MCSD, MCDBA
|||Hi Torquin,
Thanks for your posting!
From your descritpions, I understood that you would like to use local
varibales for new batches. Have I understood you? Correct me if I was wrong.
Based on my knowledge, local variables are not able to used in a new batch.
If you will have to use GO in your statements. I am afraid you will have to
use temp table to store these variables.
BTW, I have noticed that you made a replicated topic in
microsoft.public.sqlserver.programming. To keep the integrity of newsgroup,
I will reply your follow-up questions in this thread now.
Thank you for your patience and corporation. If you have any questions or
concerns, don't hesitate to let me know. We are always here to be of
assistance!
Sincerely yours,
Michael Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
Get Secure! - http://www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!
|||It shows promise, but when I substitute the variable in place of where the
server name goes I get the following error:
Server: Msg 170, Level 15, State 1, Line 4
Line 4: Incorrect syntax near '+'.
This works, with literals:
Declare @.DBID Int
Select @.DBID = DBID from OpenRowset('SQLOLEDB', 'DRIVER={SQL
Server};SERVER=ServerDB01;Trusted_Connection=Yes;' , 'Select Min(DBID) DBID
From Master.dbo.sysdatabases Where sid <> 0x01')
This gives the error listed above:
Declare @.DBID Int
Declare @.ServerName VarChar(100)
Set @.ServerName = 'ServerDB01'
Select @.DBID = DBID from OpenRowset('SQLOLEDB', 'DRIVER={SQL
Server};SERVER=' + @.ServerName + ';Trusted_Connection=Yes;', 'Select
Min(DBID) DBID From Master.dbo.sysdatabases Where sid <> 0x01')
Any ideas will be appreciated.
Thanks,
Chris
"Rick Sawtell" <quickening@.msn.com> wrote in message
news:%23J%231H719EHA.1544@.TK2MSFTNGP11.phx.gbl...
> How about using an OpenRowSet() instead of the adding and dropping linked
> servers.
> As part of the query, just pull everything from sysdatabases into a local
> table level variable and process it that way?
> NOTE: I did not test this, it's just an idea.
>
> Rick Sawtell
> MCT, MCSD, MCDBA
>
|||Hi Torquin,
Have you checked my method of using temp table to store these variables?
How are things going? I would appreciate it if you could post here to let
me know the status of the issue. If you have any questions or concerns,
please don't hesitate to let me know. I look forward to hearing from you,
and I am happy to be of assistance.
Sincerely yours,
Michael Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
Get Secure! - http://www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!
names from them and delete the linked server. This is for an inventory
program I am writing to keep info on all of our SQL Server up to date.
The problem I run into is the linked server procedure, even when run with
Exec(), does not run unless I separate it in its own batch, then variables I
created in my script are out of scope.
Nutshell of the sequence:
1. Create cursor with list of DB servers from table on inventory server.
2. Begin cursor loop.
3. Create linked server for the server from the list.
4. Query server for list of user databases on that server.
5. Begin another loop, using While, to insert data retrieved into inventory
database.
6. Delete linked server.
7. Go back to beginning and do next server in list.
The code:
Declare @.DBID Int --Floating DB ID number.
Declare @.MaxDB Int --Highest DB ID number.
Declare @.DBName VarChar(100)
Declare @.LastID Int --The value of the identity field for the last insert.
Declare @.ServerID Int
Declare @.Server VarChar(100)
Declare @.SQL1 VarChar(500)
Declare DBServers Cursor FAST_FORWARD For
Select S.seName, S.seServerID From Servers S Inner Join
ServerAppLink SAL On S.seServerID = SAL.slServerIDse
Open DBServers
Fetch Next From DBServers Into @.Server, @.ServerID
Print @.Server
While @.@.Fetch_Status = 0
Begin
Set @.SQL1 ='sp_AddLinkedServer @.server= ''Temp'', @.srvproduct= '''',
@.provider= ''SQLOLEDB'',
@.datasrc= ' + @.Server +',
@.catalog= ''master'''
Exec (@.SQL1)
Set @.SQL1 = 'sp_AddLinkedSrvLogin @.rmtsrvname = ''Temp'',
@.useself= ''True'', @.locallogin = ''domain\username'''
Exec (@.SQL1)
Select @.DBID = Min(dbid) From Temp.Master.dbo.sysdatabases Where sid <>
0x01
While @.DBID <= @.MaxDB
Begin
Set @.SQL1 = 'Select @.DBName = name from Temp.Master.dbo.sysdatabases
Where dbid = ' + @.DBID
Exec(@.SQL1)
Insert Into Databases (dbName, dbType)
Values(@.DBName, 'MSSQL')
Select @.LastID = @.@.Identity
Insert Into DBLink (dlDBIDdb, dlServerIDse)
Values(@.LastID, @.ServerID)
Set @.DBID = @.DBID + 1
End
Exec sp_dropserver @.server = 'Temp' , @.droplogins = 'droplogins'
Fetch Next From DBServers Into @.Server, @.ServerID
End
Close DBServers
DeAllocate DBServers
This works great, and I have no security issues, if I run the linked server
part separate from the Select from sysdatabases part. I tried putting a "GO"
after the sp_addlinkedserver, but that just put all of my variables out of
scope.
Any ideas?
Thanks,
Chris Stamey
(reply to newsgroup please)
Chris,
Have you looked at sp_executesql?
Ilya
"Torquin" <Torquin@.nospam.nospam> wrote in message
news:ubnSRYy9EHA.3376@.TK2MSFTNGP12.phx.gbl...
> I am trying to automatically create linked servers, then extract the DB
> names from them and delete the linked server. This is for an inventory
> program I am writing to keep info on all of our SQL Server up to date.
> The problem I run into is the linked server procedure, even when run with
> Exec(), does not run unless I separate it in its own batch, then variables
I
> created in my script are out of scope.
> Nutshell of the sequence:
> 1. Create cursor with list of DB servers from table on inventory server.
> 2. Begin cursor loop.
> 3. Create linked server for the server from the list.
> 4. Query server for list of user databases on that server.
> 5. Begin another loop, using While, to insert data retrieved into
inventory
> database.
> 6. Delete linked server.
> 7. Go back to beginning and do next server in list.
> The code:
> Declare @.DBID Int --Floating DB ID number.
> Declare @.MaxDB Int --Highest DB ID number.
> Declare @.DBName VarChar(100)
> Declare @.LastID Int --The value of the identity field for the last insert.
> Declare @.ServerID Int
> Declare @.Server VarChar(100)
> Declare @.SQL1 VarChar(500)
> Declare DBServers Cursor FAST_FORWARD For
> Select S.seName, S.seServerID From Servers S Inner Join
> ServerAppLink SAL On S.seServerID = SAL.slServerIDse
> Open DBServers
> Fetch Next From DBServers Into @.Server, @.ServerID
> Print @.Server
> While @.@.Fetch_Status = 0
> Begin
> Set @.SQL1 ='sp_AddLinkedServer @.server= ''Temp'', @.srvproduct= '''',
> @.provider= ''SQLOLEDB'',
> @.datasrc= ' + @.Server +',
> @.catalog= ''master'''
> Exec (@.SQL1)
> Set @.SQL1 = 'sp_AddLinkedSrvLogin @.rmtsrvname = ''Temp'',
> @.useself= ''True'', @.locallogin = ''domain\username'''
> Exec (@.SQL1)
> Select @.DBID = Min(dbid) From Temp.Master.dbo.sysdatabases Where sid <>
> 0x01
> While @.DBID <= @.MaxDB
> Begin
> Set @.SQL1 = 'Select @.DBName = name from Temp.Master.dbo.sysdatabases
> Where dbid = ' + @.DBID
> Exec(@.SQL1)
> Insert Into Databases (dbName, dbType)
> Values(@.DBName, 'MSSQL')
> Select @.LastID = @.@.Identity
> Insert Into DBLink (dlDBIDdb, dlServerIDse)
> Values(@.LastID, @.ServerID)
> Set @.DBID = @.DBID + 1
> End
> Exec sp_dropserver @.server = 'Temp' , @.droplogins = 'droplogins'
> Fetch Next From DBServers Into @.Server, @.ServerID
> End
> Close DBServers
> DeAllocate DBServers
> This works great, and I have no security issues, if I run the linked
server
> part separate from the Select from sysdatabases part. I tried putting a
"GO"
> after the sp_addlinkedserver, but that just put all of my variables out of
> scope.
> Any ideas?
> Thanks,
> Chris Stamey
> (reply to newsgroup please)
>
|||I have not. I will look into that and see if it can help this situation.
Thanks,
Chris
"Ilya Margolin" <ilya_no_spam_@.unapen.com> wrote in message
news:%238oTdtz9EHA.1452@.TK2MSFTNGP11.phx.gbl...[vbcol=seagreen]
> Chris,
> Have you looked at sp_executesql?
> Ilya
> "Torquin" <Torquin@.nospam.nospam> wrote in message
> news:ubnSRYy9EHA.3376@.TK2MSFTNGP12.phx.gbl...
with[vbcol=seagreen]
variables[vbcol=seagreen]
> I
> inventory
insert.[vbcol=seagreen]
<>[vbcol=seagreen]
> server
> "GO"
of
>
|||No, sp_ExecuteSQL does not help in this situation.
Thanks,
Chris
"Ilya Margolin" <ilya_no_spam_@.unapen.com> wrote in message
news:%238oTdtz9EHA.1452@.TK2MSFTNGP11.phx.gbl...[vbcol=seagreen]
> Chris,
> Have you looked at sp_executesql?
> Ilya
> "Torquin" <Torquin@.nospam.nospam> wrote in message
> news:ubnSRYy9EHA.3376@.TK2MSFTNGP12.phx.gbl...
with[vbcol=seagreen]
variables[vbcol=seagreen]
> I
> inventory
insert.[vbcol=seagreen]
<>[vbcol=seagreen]
> server
> "GO"
of
>
|||How about using an OpenRowSet() instead of the adding and dropping linked
servers.
As part of the query, just pull everything from sysdatabases into a local
table level variable and process it that way?
NOTE: I did not test this, it's just an idea.
Rick Sawtell
MCT, MCSD, MCDBA
|||Hi Torquin,
Thanks for your posting!
From your descritpions, I understood that you would like to use local
varibales for new batches. Have I understood you? Correct me if I was wrong.
Based on my knowledge, local variables are not able to used in a new batch.
If you will have to use GO in your statements. I am afraid you will have to
use temp table to store these variables.
BTW, I have noticed that you made a replicated topic in
microsoft.public.sqlserver.programming. To keep the integrity of newsgroup,
I will reply your follow-up questions in this thread now.
Thank you for your patience and corporation. If you have any questions or
concerns, don't hesitate to let me know. We are always here to be of
assistance!
Sincerely yours,
Michael Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
Get Secure! - http://www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!
|||It shows promise, but when I substitute the variable in place of where the
server name goes I get the following error:
Server: Msg 170, Level 15, State 1, Line 4
Line 4: Incorrect syntax near '+'.
This works, with literals:
Declare @.DBID Int
Select @.DBID = DBID from OpenRowset('SQLOLEDB', 'DRIVER={SQL
Server};SERVER=ServerDB01;Trusted_Connection=Yes;' , 'Select Min(DBID) DBID
From Master.dbo.sysdatabases Where sid <> 0x01')
This gives the error listed above:
Declare @.DBID Int
Declare @.ServerName VarChar(100)
Set @.ServerName = 'ServerDB01'
Select @.DBID = DBID from OpenRowset('SQLOLEDB', 'DRIVER={SQL
Server};SERVER=' + @.ServerName + ';Trusted_Connection=Yes;', 'Select
Min(DBID) DBID From Master.dbo.sysdatabases Where sid <> 0x01')
Any ideas will be appreciated.
Thanks,
Chris
"Rick Sawtell" <quickening@.msn.com> wrote in message
news:%23J%231H719EHA.1544@.TK2MSFTNGP11.phx.gbl...
> How about using an OpenRowSet() instead of the adding and dropping linked
> servers.
> As part of the query, just pull everything from sysdatabases into a local
> table level variable and process it that way?
> NOTE: I did not test this, it's just an idea.
>
> Rick Sawtell
> MCT, MCSD, MCDBA
>
|||Hi Torquin,
Have you checked my method of using temp table to store these variables?
How are things going? I would appreciate it if you could post here to let
me know the status of the issue. If you have any questions or concerns,
please don't hesitate to let me know. I look forward to hearing from you,
and I am happy to be of assistance.
Sincerely yours,
Michael Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
Get Secure! - http://www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!
Automatically Create Linked Server?
I am trying to automatically create linked servers, then extract the DB
names from them and delete the linked server. This is for an inventory
program I am writing to keep info on all of our SQL Server up to date.
The problem I run into is the linked server procedure, even when run with
Exec(), does not run unless I separate it in its own batch, then variables I
created in my script are out of scope.
Nutshell of the sequence:
1. Create cursor with list of DB servers from table on inventory server.
2. Begin cursor loop.
3. Create linked server for the server from the list.
4. Query server for list of user databases on that server.
5. Begin another loop, using While, to insert data retrieved into inventory
database.
6. Delete linked server.
7. Go back to beginning and do next server in list.
The code:
Declare @.DBID Int --Floating DB ID number.
Declare @.MaxDB Int --Highest DB ID number.
Declare @.DBName VarChar(100)
Declare @.LastID Int --The value of the identity field for the last insert.
Declare @.ServerID Int
Declare @.Server VarChar(100)
Declare @.SQL1 VarChar(500)
Declare DBServers Cursor FAST_FORWARD For
Select S.seName, S.seServerID From Servers S Inner Join
ServerAppLink SAL On S.seServerID = SAL.slServerIDse
Open DBServers
Fetch Next From DBServers Into @.Server, @.ServerID
Print @.Server
While @.@.Fetch_Status = 0
Begin
Set @.SQL1 ='sp_AddLinkedServer @.server= ''Temp'', @.srvproduct= '''',
@.provider= ''SQLOLEDB'',
@.datasrc= ' + @.Server +',
@.catalog= ''master'''
Exec (@.SQL1)
Set @.SQL1 = 'sp_AddLinkedSrvLogin @.rmtsrvname = ''Temp'',
@.useself= ''True'', @.locallogin = ''domain\username'''
Exec (@.SQL1)
Select @.DBID = Min(dbid) From Temp.Master.dbo.sysdatabases Where sid <>
0x01
While @.DBID <= @.MaxDB
Begin
Set @.SQL1 = 'Select @.DBName = name from Temp.Master.dbo.sysdatabases
Where dbid = ' + @.DBID
Exec(@.SQL1)
Insert Into Databases (dbName, dbType)
Values(@.DBName, 'MSSQL')
Select @.LastID = @.@.Identity
Insert Into DBLink (dlDBIDdb, dlServerIDse)
Values(@.LastID, @.ServerID)
Set @.DBID = @.DBID + 1
End
Exec sp_dropserver @.server = 'Temp' , @.droplogins = 'droplogins'
Fetch Next From DBServers Into @.Server, @.ServerID
End
Close DBServers
DeAllocate DBServers
This works great, and I have no security issues, if I run the linked server
part separate from the Select from sysdatabases part. I tried putting a "GO"
after the sp_addlinkedserver, but that just put all of my variables out of
scope.
Any ideas?
Thanks,
Chris Stamey
(reply to newsgroup please)Chris,
Have you looked at sp_executesql?
Ilya
"Torquin" <Torquin@.nospam.nospam> wrote in message
news:ubnSRYy9EHA.3376@.TK2MSFTNGP12.phx.gbl...
> I am trying to automatically create linked servers, then extract the DB
> names from them and delete the linked server. This is for an inventory
> program I am writing to keep info on all of our SQL Server up to date.
> The problem I run into is the linked server procedure, even when run with
> Exec(), does not run unless I separate it in its own batch, then variables
I
> created in my script are out of scope.
> Nutshell of the sequence:
> 1. Create cursor with list of DB servers from table on inventory server.
> 2. Begin cursor loop.
> 3. Create linked server for the server from the list.
> 4. Query server for list of user databases on that server.
> 5. Begin another loop, using While, to insert data retrieved into
inventory
> database.
> 6. Delete linked server.
> 7. Go back to beginning and do next server in list.
> The code:
> Declare @.DBID Int --Floating DB ID number.
> Declare @.MaxDB Int --Highest DB ID number.
> Declare @.DBName VarChar(100)
> Declare @.LastID Int --The value of the identity field for the last insert.
> Declare @.ServerID Int
> Declare @.Server VarChar(100)
> Declare @.SQL1 VarChar(500)
> Declare DBServers Cursor FAST_FORWARD For
> Select S.seName, S.seServerID From Servers S Inner Join
> ServerAppLink SAL On S.seServerID = SAL.slServerIDse
> Open DBServers
> Fetch Next From DBServers Into @.Server, @.ServerID
> Print @.Server
> While @.@.Fetch_Status = 0
> Begin
> Set @.SQL1 ='sp_AddLinkedServer @.server= ''Temp'', @.srvproduct= '''',
> @.provider= ''SQLOLEDB'',
> @.datasrc= ' + @.Server +',
> @.catalog= ''master'''
> Exec (@.SQL1)
> Set @.SQL1 = 'sp_AddLinkedSrvLogin @.rmtsrvname = ''Temp'',
> @.useself= ''True'', @.locallogin = ''domain\username'''
> Exec (@.SQL1)
> Select @.DBID = Min(dbid) From Temp.Master.dbo.sysdatabases Where sid <>
> 0x01
> While @.DBID <= @.MaxDB
> Begin
> Set @.SQL1 = 'Select @.DBName = name from Temp.Master.dbo.sysdatabases
> Where dbid = ' + @.DBID
> Exec(@.SQL1)
> Insert Into Databases (dbName, dbType)
> Values(@.DBName, 'MSSQL')
> Select @.LastID = @.@.Identity
> Insert Into DBLink (dlDBIDdb, dlServerIDse)
> Values(@.LastID, @.ServerID)
> Set @.DBID = @.DBID + 1
> End
> Exec sp_dropserver @.server = 'Temp' , @.droplogins = 'droplogins'
> Fetch Next From DBServers Into @.Server, @.ServerID
> End
> Close DBServers
> DeAllocate DBServers
> This works great, and I have no security issues, if I run the linked
server
> part separate from the Select from sysdatabases part. I tried putting a
"GO"
> after the sp_addlinkedserver, but that just put all of my variables out of
> scope.
> Any ideas?
> Thanks,
> Chris Stamey
> (reply to newsgroup please)
>|||I have not. I will look into that and see if it can help this situation.
Thanks,
Chris
"Ilya Margolin" <ilya_no_spam_@.unapen.com> wrote in message
news:%238oTdtz9EHA.1452@.TK2MSFTNGP11.phx.gbl...
> Chris,
> Have you looked at sp_executesql?
> Ilya
> "Torquin" <Torquin@.nospam.nospam> wrote in message
> news:ubnSRYy9EHA.3376@.TK2MSFTNGP12.phx.gbl...
> > I am trying to automatically create linked servers, then extract the DB
> > names from them and delete the linked server. This is for an inventory
> > program I am writing to keep info on all of our SQL Server up to date.
> > The problem I run into is the linked server procedure, even when run
with
> > Exec(), does not run unless I separate it in its own batch, then
variables
> I
> > created in my script are out of scope.
> > Nutshell of the sequence:
> > 1. Create cursor with list of DB servers from table on inventory server.
> > 2. Begin cursor loop.
> > 3. Create linked server for the server from the list.
> > 4. Query server for list of user databases on that server.
> > 5. Begin another loop, using While, to insert data retrieved into
> inventory
> > database.
> > 6. Delete linked server.
> > 7. Go back to beginning and do next server in list.
> >
> > The code:
> > Declare @.DBID Int --Floating DB ID number.
> > Declare @.MaxDB Int --Highest DB ID number.
> > Declare @.DBName VarChar(100)
> > Declare @.LastID Int --The value of the identity field for the last
insert.
> > Declare @.ServerID Int
> > Declare @.Server VarChar(100)
> > Declare @.SQL1 VarChar(500)
> > Declare DBServers Cursor FAST_FORWARD For
> > Select S.seName, S.seServerID From Servers S Inner Join
> > ServerAppLink SAL On S.seServerID = SAL.slServerIDse
> > Open DBServers
> > Fetch Next From DBServers Into @.Server, @.ServerID
> > Print @.Server
> > While @.@.Fetch_Status = 0
> > Begin
> > Set @.SQL1 ='sp_AddLinkedServer @.server= ''Temp'', @.srvproduct= '''',
> > @.provider= ''SQLOLEDB'',
> > @.datasrc= ' + @.Server +',
> > @.catalog= ''master'''
> > Exec (@.SQL1)
> > Set @.SQL1 = 'sp_AddLinkedSrvLogin @.rmtsrvname = ''Temp'',
> > @.useself= ''True'', @.locallogin = ''domain\username'''
> > Exec (@.SQL1)
> > Select @.DBID = Min(dbid) From Temp.Master.dbo.sysdatabases Where sid
<>
> > 0x01
> >
> > While @.DBID <= @.MaxDB
> > Begin
> > Set @.SQL1 = 'Select @.DBName = name from Temp.Master.dbo.sysdatabases
> > Where dbid = ' + @.DBID
> > Exec(@.SQL1)
> > Insert Into Databases (dbName, dbType)
> > Values(@.DBName, 'MSSQL')
> > Select @.LastID = @.@.Identity
> > Insert Into DBLink (dlDBIDdb, dlServerIDse)
> > Values(@.LastID, @.ServerID)
> > Set @.DBID = @.DBID + 1
> > End
> > Exec sp_dropserver @.server = 'Temp' , @.droplogins = 'droplogins'
> > Fetch Next From DBServers Into @.Server, @.ServerID
> > End
> > Close DBServers
> > DeAllocate DBServers
> >
> > This works great, and I have no security issues, if I run the linked
> server
> > part separate from the Select from sysdatabases part. I tried putting a
> "GO"
> > after the sp_addlinkedserver, but that just put all of my variables out
of
> > scope.
> >
> > Any ideas?
> >
> > Thanks,
> > Chris Stamey
> > (reply to newsgroup please)
> >
> >
>|||No, sp_ExecuteSQL does not help in this situation.
Thanks,
Chris
"Ilya Margolin" <ilya_no_spam_@.unapen.com> wrote in message
news:%238oTdtz9EHA.1452@.TK2MSFTNGP11.phx.gbl...
> Chris,
> Have you looked at sp_executesql?
> Ilya
> "Torquin" <Torquin@.nospam.nospam> wrote in message
> news:ubnSRYy9EHA.3376@.TK2MSFTNGP12.phx.gbl...
> > I am trying to automatically create linked servers, then extract the DB
> > names from them and delete the linked server. This is for an inventory
> > program I am writing to keep info on all of our SQL Server up to date.
> > The problem I run into is the linked server procedure, even when run
with
> > Exec(), does not run unless I separate it in its own batch, then
variables
> I
> > created in my script are out of scope.
> > Nutshell of the sequence:
> > 1. Create cursor with list of DB servers from table on inventory server.
> > 2. Begin cursor loop.
> > 3. Create linked server for the server from the list.
> > 4. Query server for list of user databases on that server.
> > 5. Begin another loop, using While, to insert data retrieved into
> inventory
> > database.
> > 6. Delete linked server.
> > 7. Go back to beginning and do next server in list.
> >
> > The code:
> > Declare @.DBID Int --Floating DB ID number.
> > Declare @.MaxDB Int --Highest DB ID number.
> > Declare @.DBName VarChar(100)
> > Declare @.LastID Int --The value of the identity field for the last
insert.
> > Declare @.ServerID Int
> > Declare @.Server VarChar(100)
> > Declare @.SQL1 VarChar(500)
> > Declare DBServers Cursor FAST_FORWARD For
> > Select S.seName, S.seServerID From Servers S Inner Join
> > ServerAppLink SAL On S.seServerID = SAL.slServerIDse
> > Open DBServers
> > Fetch Next From DBServers Into @.Server, @.ServerID
> > Print @.Server
> > While @.@.Fetch_Status = 0
> > Begin
> > Set @.SQL1 ='sp_AddLinkedServer @.server= ''Temp'', @.srvproduct= '''',
> > @.provider= ''SQLOLEDB'',
> > @.datasrc= ' + @.Server +',
> > @.catalog= ''master'''
> > Exec (@.SQL1)
> > Set @.SQL1 = 'sp_AddLinkedSrvLogin @.rmtsrvname = ''Temp'',
> > @.useself= ''True'', @.locallogin = ''domain\username'''
> > Exec (@.SQL1)
> > Select @.DBID = Min(dbid) From Temp.Master.dbo.sysdatabases Where sid
<>
> > 0x01
> >
> > While @.DBID <= @.MaxDB
> > Begin
> > Set @.SQL1 = 'Select @.DBName = name from Temp.Master.dbo.sysdatabases
> > Where dbid = ' + @.DBID
> > Exec(@.SQL1)
> > Insert Into Databases (dbName, dbType)
> > Values(@.DBName, 'MSSQL')
> > Select @.LastID = @.@.Identity
> > Insert Into DBLink (dlDBIDdb, dlServerIDse)
> > Values(@.LastID, @.ServerID)
> > Set @.DBID = @.DBID + 1
> > End
> > Exec sp_dropserver @.server = 'Temp' , @.droplogins = 'droplogins'
> > Fetch Next From DBServers Into @.Server, @.ServerID
> > End
> > Close DBServers
> > DeAllocate DBServers
> >
> > This works great, and I have no security issues, if I run the linked
> server
> > part separate from the Select from sysdatabases part. I tried putting a
> "GO"
> > after the sp_addlinkedserver, but that just put all of my variables out
of
> > scope.
> >
> > Any ideas?
> >
> > Thanks,
> > Chris Stamey
> > (reply to newsgroup please)
> >
> >
>|||How about using an OpenRowSet() instead of the adding and dropping linked
servers.
As part of the query, just pull everything from sysdatabases into a local
table level variable and process it that way?
NOTE: I did not test this, it's just an idea.
Rick Sawtell
MCT, MCSD, MCDBA|||Hi Torquin,
Thanks for your posting!
From your descritpions, I understood that you would like to use local
varibales for new batches. Have I understood you? Correct me if I was wrong.
Based on my knowledge, local variables are not able to used in a new batch.
If you will have to use GO in your statements. I am afraid you will have to
use temp table to store these variables.
BTW, I have noticed that you made a replicated topic in
microsoft.public.sqlserver.programming. To keep the integrity of newsgroup,
I will reply your follow-up questions in this thread now.
Thank you for your patience and corporation. If you have any questions or
concerns, don't hesitate to let me know. We are always here to be of
assistance!
Sincerely yours,
Michael Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
---
Get Secure! - http://www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!|||It shows promise, but when I substitute the variable in place of where the
server name goes I get the following error:
Server: Msg 170, Level 15, State 1, Line 4
Line 4: Incorrect syntax near '+'.
This works, with literals:
Declare @.DBID Int
Select @.DBID = DBID from OpenRowset('SQLOLEDB', 'DRIVER={SQL
Server};SERVER=ServerDB01;Trusted_Connection=Yes;', 'Select Min(DBID) DBID
From Master.dbo.sysdatabases Where sid <> 0x01')
This gives the error listed above:
Declare @.DBID Int
Declare @.ServerName VarChar(100)
Set @.ServerName = 'ServerDB01'
Select @.DBID = DBID from OpenRowset('SQLOLEDB', 'DRIVER={SQL
Server};SERVER=' + @.ServerName + ';Trusted_Connection=Yes;', 'Select
Min(DBID) DBID From Master.dbo.sysdatabases Where sid <> 0x01')
Any ideas will be appreciated.
Thanks,
Chris
"Rick Sawtell" <quickening@.msn.com> wrote in message
news:%23J%231H719EHA.1544@.TK2MSFTNGP11.phx.gbl...
> How about using an OpenRowSet() instead of the adding and dropping linked
> servers.
> As part of the query, just pull everything from sysdatabases into a local
> table level variable and process it that way?
> NOTE: I did not test this, it's just an idea.
>
> Rick Sawtell
> MCT, MCSD, MCDBA
>|||Hi Torquin,
Have you checked my method of using temp table to store these variables?
How are things going? I would appreciate it if you could post here to let
me know the status of the issue. If you have any questions or concerns,
please don't hesitate to let me know. I look forward to hearing from you,
and I am happy to be of assistance.
Sincerely yours,
Michael Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
---
Get Secure! - http://www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!
names from them and delete the linked server. This is for an inventory
program I am writing to keep info on all of our SQL Server up to date.
The problem I run into is the linked server procedure, even when run with
Exec(), does not run unless I separate it in its own batch, then variables I
created in my script are out of scope.
Nutshell of the sequence:
1. Create cursor with list of DB servers from table on inventory server.
2. Begin cursor loop.
3. Create linked server for the server from the list.
4. Query server for list of user databases on that server.
5. Begin another loop, using While, to insert data retrieved into inventory
database.
6. Delete linked server.
7. Go back to beginning and do next server in list.
The code:
Declare @.DBID Int --Floating DB ID number.
Declare @.MaxDB Int --Highest DB ID number.
Declare @.DBName VarChar(100)
Declare @.LastID Int --The value of the identity field for the last insert.
Declare @.ServerID Int
Declare @.Server VarChar(100)
Declare @.SQL1 VarChar(500)
Declare DBServers Cursor FAST_FORWARD For
Select S.seName, S.seServerID From Servers S Inner Join
ServerAppLink SAL On S.seServerID = SAL.slServerIDse
Open DBServers
Fetch Next From DBServers Into @.Server, @.ServerID
Print @.Server
While @.@.Fetch_Status = 0
Begin
Set @.SQL1 ='sp_AddLinkedServer @.server= ''Temp'', @.srvproduct= '''',
@.provider= ''SQLOLEDB'',
@.datasrc= ' + @.Server +',
@.catalog= ''master'''
Exec (@.SQL1)
Set @.SQL1 = 'sp_AddLinkedSrvLogin @.rmtsrvname = ''Temp'',
@.useself= ''True'', @.locallogin = ''domain\username'''
Exec (@.SQL1)
Select @.DBID = Min(dbid) From Temp.Master.dbo.sysdatabases Where sid <>
0x01
While @.DBID <= @.MaxDB
Begin
Set @.SQL1 = 'Select @.DBName = name from Temp.Master.dbo.sysdatabases
Where dbid = ' + @.DBID
Exec(@.SQL1)
Insert Into Databases (dbName, dbType)
Values(@.DBName, 'MSSQL')
Select @.LastID = @.@.Identity
Insert Into DBLink (dlDBIDdb, dlServerIDse)
Values(@.LastID, @.ServerID)
Set @.DBID = @.DBID + 1
End
Exec sp_dropserver @.server = 'Temp' , @.droplogins = 'droplogins'
Fetch Next From DBServers Into @.Server, @.ServerID
End
Close DBServers
DeAllocate DBServers
This works great, and I have no security issues, if I run the linked server
part separate from the Select from sysdatabases part. I tried putting a "GO"
after the sp_addlinkedserver, but that just put all of my variables out of
scope.
Any ideas?
Thanks,
Chris Stamey
(reply to newsgroup please)Chris,
Have you looked at sp_executesql?
Ilya
"Torquin" <Torquin@.nospam.nospam> wrote in message
news:ubnSRYy9EHA.3376@.TK2MSFTNGP12.phx.gbl...
> I am trying to automatically create linked servers, then extract the DB
> names from them and delete the linked server. This is for an inventory
> program I am writing to keep info on all of our SQL Server up to date.
> The problem I run into is the linked server procedure, even when run with
> Exec(), does not run unless I separate it in its own batch, then variables
I
> created in my script are out of scope.
> Nutshell of the sequence:
> 1. Create cursor with list of DB servers from table on inventory server.
> 2. Begin cursor loop.
> 3. Create linked server for the server from the list.
> 4. Query server for list of user databases on that server.
> 5. Begin another loop, using While, to insert data retrieved into
inventory
> database.
> 6. Delete linked server.
> 7. Go back to beginning and do next server in list.
> The code:
> Declare @.DBID Int --Floating DB ID number.
> Declare @.MaxDB Int --Highest DB ID number.
> Declare @.DBName VarChar(100)
> Declare @.LastID Int --The value of the identity field for the last insert.
> Declare @.ServerID Int
> Declare @.Server VarChar(100)
> Declare @.SQL1 VarChar(500)
> Declare DBServers Cursor FAST_FORWARD For
> Select S.seName, S.seServerID From Servers S Inner Join
> ServerAppLink SAL On S.seServerID = SAL.slServerIDse
> Open DBServers
> Fetch Next From DBServers Into @.Server, @.ServerID
> Print @.Server
> While @.@.Fetch_Status = 0
> Begin
> Set @.SQL1 ='sp_AddLinkedServer @.server= ''Temp'', @.srvproduct= '''',
> @.provider= ''SQLOLEDB'',
> @.datasrc= ' + @.Server +',
> @.catalog= ''master'''
> Exec (@.SQL1)
> Set @.SQL1 = 'sp_AddLinkedSrvLogin @.rmtsrvname = ''Temp'',
> @.useself= ''True'', @.locallogin = ''domain\username'''
> Exec (@.SQL1)
> Select @.DBID = Min(dbid) From Temp.Master.dbo.sysdatabases Where sid <>
> 0x01
> While @.DBID <= @.MaxDB
> Begin
> Set @.SQL1 = 'Select @.DBName = name from Temp.Master.dbo.sysdatabases
> Where dbid = ' + @.DBID
> Exec(@.SQL1)
> Insert Into Databases (dbName, dbType)
> Values(@.DBName, 'MSSQL')
> Select @.LastID = @.@.Identity
> Insert Into DBLink (dlDBIDdb, dlServerIDse)
> Values(@.LastID, @.ServerID)
> Set @.DBID = @.DBID + 1
> End
> Exec sp_dropserver @.server = 'Temp' , @.droplogins = 'droplogins'
> Fetch Next From DBServers Into @.Server, @.ServerID
> End
> Close DBServers
> DeAllocate DBServers
> This works great, and I have no security issues, if I run the linked
server
> part separate from the Select from sysdatabases part. I tried putting a
"GO"
> after the sp_addlinkedserver, but that just put all of my variables out of
> scope.
> Any ideas?
> Thanks,
> Chris Stamey
> (reply to newsgroup please)
>|||I have not. I will look into that and see if it can help this situation.
Thanks,
Chris
"Ilya Margolin" <ilya_no_spam_@.unapen.com> wrote in message
news:%238oTdtz9EHA.1452@.TK2MSFTNGP11.phx.gbl...
> Chris,
> Have you looked at sp_executesql?
> Ilya
> "Torquin" <Torquin@.nospam.nospam> wrote in message
> news:ubnSRYy9EHA.3376@.TK2MSFTNGP12.phx.gbl...
> > I am trying to automatically create linked servers, then extract the DB
> > names from them and delete the linked server. This is for an inventory
> > program I am writing to keep info on all of our SQL Server up to date.
> > The problem I run into is the linked server procedure, even when run
with
> > Exec(), does not run unless I separate it in its own batch, then
variables
> I
> > created in my script are out of scope.
> > Nutshell of the sequence:
> > 1. Create cursor with list of DB servers from table on inventory server.
> > 2. Begin cursor loop.
> > 3. Create linked server for the server from the list.
> > 4. Query server for list of user databases on that server.
> > 5. Begin another loop, using While, to insert data retrieved into
> inventory
> > database.
> > 6. Delete linked server.
> > 7. Go back to beginning and do next server in list.
> >
> > The code:
> > Declare @.DBID Int --Floating DB ID number.
> > Declare @.MaxDB Int --Highest DB ID number.
> > Declare @.DBName VarChar(100)
> > Declare @.LastID Int --The value of the identity field for the last
insert.
> > Declare @.ServerID Int
> > Declare @.Server VarChar(100)
> > Declare @.SQL1 VarChar(500)
> > Declare DBServers Cursor FAST_FORWARD For
> > Select S.seName, S.seServerID From Servers S Inner Join
> > ServerAppLink SAL On S.seServerID = SAL.slServerIDse
> > Open DBServers
> > Fetch Next From DBServers Into @.Server, @.ServerID
> > Print @.Server
> > While @.@.Fetch_Status = 0
> > Begin
> > Set @.SQL1 ='sp_AddLinkedServer @.server= ''Temp'', @.srvproduct= '''',
> > @.provider= ''SQLOLEDB'',
> > @.datasrc= ' + @.Server +',
> > @.catalog= ''master'''
> > Exec (@.SQL1)
> > Set @.SQL1 = 'sp_AddLinkedSrvLogin @.rmtsrvname = ''Temp'',
> > @.useself= ''True'', @.locallogin = ''domain\username'''
> > Exec (@.SQL1)
> > Select @.DBID = Min(dbid) From Temp.Master.dbo.sysdatabases Where sid
<>
> > 0x01
> >
> > While @.DBID <= @.MaxDB
> > Begin
> > Set @.SQL1 = 'Select @.DBName = name from Temp.Master.dbo.sysdatabases
> > Where dbid = ' + @.DBID
> > Exec(@.SQL1)
> > Insert Into Databases (dbName, dbType)
> > Values(@.DBName, 'MSSQL')
> > Select @.LastID = @.@.Identity
> > Insert Into DBLink (dlDBIDdb, dlServerIDse)
> > Values(@.LastID, @.ServerID)
> > Set @.DBID = @.DBID + 1
> > End
> > Exec sp_dropserver @.server = 'Temp' , @.droplogins = 'droplogins'
> > Fetch Next From DBServers Into @.Server, @.ServerID
> > End
> > Close DBServers
> > DeAllocate DBServers
> >
> > This works great, and I have no security issues, if I run the linked
> server
> > part separate from the Select from sysdatabases part. I tried putting a
> "GO"
> > after the sp_addlinkedserver, but that just put all of my variables out
of
> > scope.
> >
> > Any ideas?
> >
> > Thanks,
> > Chris Stamey
> > (reply to newsgroup please)
> >
> >
>|||No, sp_ExecuteSQL does not help in this situation.
Thanks,
Chris
"Ilya Margolin" <ilya_no_spam_@.unapen.com> wrote in message
news:%238oTdtz9EHA.1452@.TK2MSFTNGP11.phx.gbl...
> Chris,
> Have you looked at sp_executesql?
> Ilya
> "Torquin" <Torquin@.nospam.nospam> wrote in message
> news:ubnSRYy9EHA.3376@.TK2MSFTNGP12.phx.gbl...
> > I am trying to automatically create linked servers, then extract the DB
> > names from them and delete the linked server. This is for an inventory
> > program I am writing to keep info on all of our SQL Server up to date.
> > The problem I run into is the linked server procedure, even when run
with
> > Exec(), does not run unless I separate it in its own batch, then
variables
> I
> > created in my script are out of scope.
> > Nutshell of the sequence:
> > 1. Create cursor with list of DB servers from table on inventory server.
> > 2. Begin cursor loop.
> > 3. Create linked server for the server from the list.
> > 4. Query server for list of user databases on that server.
> > 5. Begin another loop, using While, to insert data retrieved into
> inventory
> > database.
> > 6. Delete linked server.
> > 7. Go back to beginning and do next server in list.
> >
> > The code:
> > Declare @.DBID Int --Floating DB ID number.
> > Declare @.MaxDB Int --Highest DB ID number.
> > Declare @.DBName VarChar(100)
> > Declare @.LastID Int --The value of the identity field for the last
insert.
> > Declare @.ServerID Int
> > Declare @.Server VarChar(100)
> > Declare @.SQL1 VarChar(500)
> > Declare DBServers Cursor FAST_FORWARD For
> > Select S.seName, S.seServerID From Servers S Inner Join
> > ServerAppLink SAL On S.seServerID = SAL.slServerIDse
> > Open DBServers
> > Fetch Next From DBServers Into @.Server, @.ServerID
> > Print @.Server
> > While @.@.Fetch_Status = 0
> > Begin
> > Set @.SQL1 ='sp_AddLinkedServer @.server= ''Temp'', @.srvproduct= '''',
> > @.provider= ''SQLOLEDB'',
> > @.datasrc= ' + @.Server +',
> > @.catalog= ''master'''
> > Exec (@.SQL1)
> > Set @.SQL1 = 'sp_AddLinkedSrvLogin @.rmtsrvname = ''Temp'',
> > @.useself= ''True'', @.locallogin = ''domain\username'''
> > Exec (@.SQL1)
> > Select @.DBID = Min(dbid) From Temp.Master.dbo.sysdatabases Where sid
<>
> > 0x01
> >
> > While @.DBID <= @.MaxDB
> > Begin
> > Set @.SQL1 = 'Select @.DBName = name from Temp.Master.dbo.sysdatabases
> > Where dbid = ' + @.DBID
> > Exec(@.SQL1)
> > Insert Into Databases (dbName, dbType)
> > Values(@.DBName, 'MSSQL')
> > Select @.LastID = @.@.Identity
> > Insert Into DBLink (dlDBIDdb, dlServerIDse)
> > Values(@.LastID, @.ServerID)
> > Set @.DBID = @.DBID + 1
> > End
> > Exec sp_dropserver @.server = 'Temp' , @.droplogins = 'droplogins'
> > Fetch Next From DBServers Into @.Server, @.ServerID
> > End
> > Close DBServers
> > DeAllocate DBServers
> >
> > This works great, and I have no security issues, if I run the linked
> server
> > part separate from the Select from sysdatabases part. I tried putting a
> "GO"
> > after the sp_addlinkedserver, but that just put all of my variables out
of
> > scope.
> >
> > Any ideas?
> >
> > Thanks,
> > Chris Stamey
> > (reply to newsgroup please)
> >
> >
>|||How about using an OpenRowSet() instead of the adding and dropping linked
servers.
As part of the query, just pull everything from sysdatabases into a local
table level variable and process it that way?
NOTE: I did not test this, it's just an idea.
Rick Sawtell
MCT, MCSD, MCDBA|||Hi Torquin,
Thanks for your posting!
From your descritpions, I understood that you would like to use local
varibales for new batches. Have I understood you? Correct me if I was wrong.
Based on my knowledge, local variables are not able to used in a new batch.
If you will have to use GO in your statements. I am afraid you will have to
use temp table to store these variables.
BTW, I have noticed that you made a replicated topic in
microsoft.public.sqlserver.programming. To keep the integrity of newsgroup,
I will reply your follow-up questions in this thread now.
Thank you for your patience and corporation. If you have any questions or
concerns, don't hesitate to let me know. We are always here to be of
assistance!
Sincerely yours,
Michael Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
---
Get Secure! - http://www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!|||It shows promise, but when I substitute the variable in place of where the
server name goes I get the following error:
Server: Msg 170, Level 15, State 1, Line 4
Line 4: Incorrect syntax near '+'.
This works, with literals:
Declare @.DBID Int
Select @.DBID = DBID from OpenRowset('SQLOLEDB', 'DRIVER={SQL
Server};SERVER=ServerDB01;Trusted_Connection=Yes;', 'Select Min(DBID) DBID
From Master.dbo.sysdatabases Where sid <> 0x01')
This gives the error listed above:
Declare @.DBID Int
Declare @.ServerName VarChar(100)
Set @.ServerName = 'ServerDB01'
Select @.DBID = DBID from OpenRowset('SQLOLEDB', 'DRIVER={SQL
Server};SERVER=' + @.ServerName + ';Trusted_Connection=Yes;', 'Select
Min(DBID) DBID From Master.dbo.sysdatabases Where sid <> 0x01')
Any ideas will be appreciated.
Thanks,
Chris
"Rick Sawtell" <quickening@.msn.com> wrote in message
news:%23J%231H719EHA.1544@.TK2MSFTNGP11.phx.gbl...
> How about using an OpenRowSet() instead of the adding and dropping linked
> servers.
> As part of the query, just pull everything from sysdatabases into a local
> table level variable and process it that way?
> NOTE: I did not test this, it's just an idea.
>
> Rick Sawtell
> MCT, MCSD, MCDBA
>|||Hi Torquin,
Have you checked my method of using temp table to store these variables?
How are things going? I would appreciate it if you could post here to let
me know the status of the issue. If you have any questions or concerns,
please don't hesitate to let me know. I look forward to hearing from you,
and I am happy to be of assistance.
Sincerely yours,
Michael Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
---
Get Secure! - http://www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!
Subscribe to:
Posts (Atom)