Is it possible to issue the CREATE TABLE command and specify a multiple-colu
mn primary key?
If so, what is the syntax? I've checked BOL and as far as I can tell, you ma
y only select
a single column as the primary key *within the CREATE TABLE command*; ALTER
TABLE must be used
for multiple-column primary keys.
For example (this does *not* work):
CREATE TABLE #TEMPProcedures (ProcedureID int NOT NULL, ProcedureSuffix int
NOT NULL
PRIMARY KEY ProcedureID, ProcedureSuffix)
Thanks in advance --
CarlOnly a matter of a comma and a parenthesis. Below work fine:
CREATE TABLE #TEMPProcedures
(ProcedureID int NOT NULL
,ProcedureSuffix int NOT NULL
,PRIMARY KEY (ProcedureID, ProcedureSuffix))
I prefer to name all my contraints...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Carl Imthurn" <nospam@.all.com> wrote in message news:uIvG984GGHA.1032@.TK2MSFTNGP12.phx.gbl
..
> Is it possible to issue the CREATE TABLE command and specify a multiple-co
lumn primary key?
> If so, what is the syntax? I've checked BOL and as far as I can tell, you
may only select
> a single column as the primary key *within the CREATE TABLE command*; ALTE
R TABLE must be used
> for multiple-column primary keys.
> For example (this does *not* work):
> CREATE TABLE #TEMPProcedures (ProcedureID int NOT NULL, ProcedureSuffix in
t NOT NULL
> PRIMARY KEY ProcedureID, ProcedureSuffix)
> Thanks in advance --
> Carl
>|||Thanks Tibor - that worked.
And, your comment about naming all constraints is well taken.
Carl
Tibor Karaszi wrote:
> Only a matter of a comma and a parenthesis. Below work fine:
> CREATE TABLE #TEMPProcedures (ProcedureID int NOT NULL
> ,ProcedureSuffix int NOT NULL
> ,PRIMARY KEY (ProcedureID, ProcedureSuffix))
> I prefer to name all my contraints...
Showing posts with label command. Show all posts
Showing posts with label command. Show all posts
Thursday, March 29, 2012
Tuesday, March 27, 2012
create table from result set
how can i put the results of the command 'RESTORE
FILELISTONLY' into a table?Hi
create database test
go
backup database test to disk='d:\test1.bak'
go
Create table #test
(
LogicalName varchar(100),
PhysicalName varchar(100),
Type char(1),
FileGroupName varchar(100),
[Size]varchar(100),
[MaxSize]varchar(100)
)
insert into #test exec('RESTORE FILELISTONLY FROM disk= ''d:\test1.bak''')
go
select * from #test
go
drop database test
<anonymous@.discussions.microsoft.com> wrote in message
news:af6501c4076e$ed70b4c0$a501280a@.phx.gbl...
> how can i put the results of the command 'RESTORE
> FILELISTONLY' into a table?
>|||You can use INSERT ... EXEC. For example:
--FILELISTONLY
IF OBJECT_ID(N'tempdb..#FileList') IS NOT NULL
DROP TABLE #FileList
CREATE TABLE #FileList
(
LogicalName nvarchar(128) NOT NULL,
PhysicalName nvarchar(260) NOT NULL,
Type char(1) NOT NULL,
FileGroupName nvarchar(120) NULL,
Size numeric(20, 0) NOT NULL,
MaxSize numeric(20, 0) NOT NULL
)
INSERT INTO #FileList
EXEC('RESTORE FILELISTONLY FROM DISK=''C:\Backups\MyDatabase.bak''')
--HEADERONLY
IF OBJECT_ID(N'tempdb..#BackupHeader') IS NOT NULL
DROP TABLE #BackupHeader
CREATE TABLE #BackupHeader
(
BackupName nvarchar(128),
BackupDescription nvarchar(255),
BackupType smallint,
ExpirationDate datetime,
Compressed tinyint,
Position smallint,
DeviceType tinyint,
UserName nvarchar(128),
ServerName nvarchar(128),
DatabaseName nvarchar(128),
DatabaseVersion int,
DatabaseCreationDate datetime,
BackupSize numeric(20,0),
FirstLSN numeric(25,0),
LastLSN numeric(25,0),
CheckpointLSN numeric(25,0),
DatabaseBackupLSN numeric(25,0),
BackupStartDate datetime,
BackupFinishDate datetime,
SortOrder smallint,
CodePage smallint,
UnicodeLocaleId int,
UnicodeComparisonStyle int,
CompatibilityLevel tinyint,
SoftwareVendorId int,
SoftwareVersionMajor int,
SoftwareVersionMinor int,
SoftwareVersionBuild int,
MachineName nvarchar(128),
Flags int,
BindingID uniqueidentifier,
RecoveryForkID uniqueidentifier,
Collation nvarchar(128)
)
INSERT INTO #BackupHeader
EXEC ('RESTORE HEADERONLY FROM DISK=''C:\Backups\MyDatabase.bak''')
SELECT * FROM #FileList
SELECT * FROM #BackupHeader
DROP TABLE #FileList
DROP TABLE #BackupHeader
Hope this helps.
Dan Guzman
SQL Server MVP
<anonymous@.discussions.microsoft.com> wrote in message
news:af6501c4076e$ed70b4c0$a501280a@.phx.gbl...
> how can i put the results of the command 'RESTORE
> FILELISTONLY' into a table?
>sql
FILELISTONLY' into a table?Hi
create database test
go
backup database test to disk='d:\test1.bak'
go
Create table #test
(
LogicalName varchar(100),
PhysicalName varchar(100),
Type char(1),
FileGroupName varchar(100),
[Size]varchar(100),
[MaxSize]varchar(100)
)
insert into #test exec('RESTORE FILELISTONLY FROM disk= ''d:\test1.bak''')
go
select * from #test
go
drop database test
<anonymous@.discussions.microsoft.com> wrote in message
news:af6501c4076e$ed70b4c0$a501280a@.phx.gbl...
> how can i put the results of the command 'RESTORE
> FILELISTONLY' into a table?
>|||You can use INSERT ... EXEC. For example:
--FILELISTONLY
IF OBJECT_ID(N'tempdb..#FileList') IS NOT NULL
DROP TABLE #FileList
CREATE TABLE #FileList
(
LogicalName nvarchar(128) NOT NULL,
PhysicalName nvarchar(260) NOT NULL,
Type char(1) NOT NULL,
FileGroupName nvarchar(120) NULL,
Size numeric(20, 0) NOT NULL,
MaxSize numeric(20, 0) NOT NULL
)
INSERT INTO #FileList
EXEC('RESTORE FILELISTONLY FROM DISK=''C:\Backups\MyDatabase.bak''')
--HEADERONLY
IF OBJECT_ID(N'tempdb..#BackupHeader') IS NOT NULL
DROP TABLE #BackupHeader
CREATE TABLE #BackupHeader
(
BackupName nvarchar(128),
BackupDescription nvarchar(255),
BackupType smallint,
ExpirationDate datetime,
Compressed tinyint,
Position smallint,
DeviceType tinyint,
UserName nvarchar(128),
ServerName nvarchar(128),
DatabaseName nvarchar(128),
DatabaseVersion int,
DatabaseCreationDate datetime,
BackupSize numeric(20,0),
FirstLSN numeric(25,0),
LastLSN numeric(25,0),
CheckpointLSN numeric(25,0),
DatabaseBackupLSN numeric(25,0),
BackupStartDate datetime,
BackupFinishDate datetime,
SortOrder smallint,
CodePage smallint,
UnicodeLocaleId int,
UnicodeComparisonStyle int,
CompatibilityLevel tinyint,
SoftwareVendorId int,
SoftwareVersionMajor int,
SoftwareVersionMinor int,
SoftwareVersionBuild int,
MachineName nvarchar(128),
Flags int,
BindingID uniqueidentifier,
RecoveryForkID uniqueidentifier,
Collation nvarchar(128)
)
INSERT INTO #BackupHeader
EXEC ('RESTORE HEADERONLY FROM DISK=''C:\Backups\MyDatabase.bak''')
SELECT * FROM #FileList
SELECT * FROM #BackupHeader
DROP TABLE #FileList
DROP TABLE #BackupHeader
Hope this helps.
Dan Guzman
SQL Server MVP
<anonymous@.discussions.microsoft.com> wrote in message
news:af6501c4076e$ed70b4c0$a501280a@.phx.gbl...
> how can i put the results of the command 'RESTORE
> FILELISTONLY' into a table?
>sql
Sunday, March 25, 2012
CREATE TABLE and column order
I create a table by sending a CREATE TABLE command to the database. The
create is successful but when I look at the table in Enterprise Manager
the order of the columns is in alphabetic order and not in the order I
specified when I issued the CREATE TABLE command. Have the columns
really been created in the order in which I see them under Enterprise
Manager ? If so, how do I enforce that the order of the columns is the
same as the order I specify when I created the table ?"Edward Diener" <eddielee_no_spam_here@.tropicsoft.com> wrote in message
news:#Ygoih6XFHA.796@.TK2MSFTNGP09.phx.gbl...
> I create a table by sending a CREATE TABLE command to the database. The
> create is successful but when I look at the table in Enterprise Manager
> the order of the columns is in alphabetic order and not in the order I
> specified when I issued the CREATE TABLE command. Have the columns
> really been created in the order in which I see them under Enterprise
> Manager ? If so, how do I enforce that the order of the columns is the
> same as the order I specify when I created the table ?
Edward,
Does it really matter what order the columns are in? On the data page
itself, the column are put in to an order that SQL Server specifies complete
with headers on each row, null and varchar bitmaps for the row data, and
then the actual row data. If you are using blob objects (text, ntext,
image) then they don't necessarily even live in the row itself, but have
16-byte pointers to other data pages.
To ensure that your data is SELECTed INSERTed and UPDATEed properly, ensure
that you specify a column list in these statements.
Rick Sawtell
MCT, MCSD, MCDBA|||Edward
Are you sure?
I did small test
CREATE TABLE Test8
(
B INT,
A INT
)
Looking in EM I see the same order
Why the order of the columns is so important for you?
When you perform SELECT statement you can specify any order of columns.
"Edward Diener" <eddielee_no_spam_here@.tropicsoft.com> wrote in message
news:%23Ygoih6XFHA.796@.TK2MSFTNGP09.phx.gbl...
> I create a table by sending a CREATE TABLE command to the database. The
> create is successful but when I look at the table in Enterprise Manager
> the order of the columns is in alphabetic order and not in the order I
> specified when I issued the CREATE TABLE command. Have the columns
> really been created in the order in which I see them under Enterprise
> Manager ? If so, how do I enforce that the order of the columns is the
> same as the order I specify when I created the table ?|||You can create a view on the table specifying the order on your own.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Edward Diener" <eddielee_no_spam_here@.tropicsoft.com> schrieb im
Newsbeitrag news:%23Ygoih6XFHA.796@.TK2MSFTNGP09.phx.gbl...
>I create a table by sending a CREATE TABLE command to the database. The
>create is successful but when I look at the table in Enterprise Manager the
>order of the columns is in alphabetic order and not in the order I
>specified when I issued the CREATE TABLE command. Have the columns really
>been created in the order in which I see them under Enterprise Manager ? If
>so, how do I enforce that the order of the columns is the same as the order
>I specify when I created the table ?|||Even though tables in relational databases do not have a "column order"
associated with them, SQL often associates positional significance to the
order of columns in a table. However except in a few circumstances, such
significance of the column order offer little or no benefits.
Not necessarily. To find the order of columns in t-SQL, you can query the
metadata and verify the ORDINAL_POSITION column like:
EXEC sp_columns tbl
Anith|||Rick Sawtell wrote:
> "Edward Diener" <eddielee_no_spam_here@.tropicsoft.com> wrote in message
> news:#Ygoih6XFHA.796@.TK2MSFTNGP09.phx.gbl...
>
>
> Edward,
> Does it really matter what order the columns are in?
Very much so. Are you telling me that I can not ensure the column order
in SQL Server when I create a table ?
> On the data page
> itself, the column are put in to an order that SQL Server specifies comple
te
> with headers on each row, null and varchar bitmaps for the row data, and
> then the actual row data.
When you say "the data page", to what are you referring ?
> If you are using blob objects (text, ntext,
> image) then they don't necessarily even live in the row itself, but have
> 16-byte pointers to other data pages.
> To ensure that your data is SELECTed INSERTed and UPDATEed properly, ensur
e
> that you specify a column list in these statements.
That is not the issue. I need to ensure the actual order of columns is
the same as what I specified when I created the table. Is this the case,
and Enterprise Manager is not showing me the actual column order ? Or is
it the case that SQL Server actually changes the column order from what
I specified when I created the table ? The latter would be terrible.|||Anith Sen wrote:
> Even though tables in relational databases do not have a "column order"
> associated with them, SQL often associates positional significance to the
> order of columns in a table. However except in a few circumstances, such
> significance of the column order offer little or no benefits.
>
>
> Not necessarily. To find the order of columns in t-SQL, you can query the
> metadata and verify the ORDINAL_POSITION column like:
> EXEC sp_columns tbl
>
Sorry, this does work but the order of columns is not what I specified
when I created the table. SQL Server has moved the order of columns.
This is really horrible. There must be some way to ensure that the order
of columns in the table is the same as what I specified when I created
the table.|||Uri Dimant wrote:
> Edward
> Are you sure?
> I did small test
> CREATE TABLE Test8
> (
> B INT,
> A INT
> )
> Looking in EM I see the same order
Try adding primary keys not on the first column.
> Why the order of the columns is so important for you?
I am migrating data from one RDBMS to SQL Server. It is much easier if
the column order is the same in the from and to tables.
> When you perform SELECT statement you can specify any order of columns.
>
>
> "Edward Diener" <eddielee_no_spam_here@.tropicsoft.com> wrote in message
> news:%23Ygoih6XFHA.796@.TK2MSFTNGP09.phx.gbl...
>|||Did you specify the correct table name in place of "tbl"?
Note that the column returned as ORDINAL_POSITION by sp_columns does
NOT necessarily reflect the order in which the columns were defined. If
you insert columns with EM then the table is recreated and you may not
see the result you expect. Also, this behaviour is subject to change in
future versions because EM is going away. Don't rely on it.
David Portas
SQL Server MVP
--|||Edward wrote on Mon, 23 May 2005 11:41:44 -0400:
> Anith Sen wrote:
> Running this stored procedure under SQL Server 7 Query Analyzer I get no
> rows returned.
Check you are putting your own table name in place of tbl, and you are in
the right database. Works fine here on my SQL 7 and SQL 2K servers.
Dan
create is successful but when I look at the table in Enterprise Manager
the order of the columns is in alphabetic order and not in the order I
specified when I issued the CREATE TABLE command. Have the columns
really been created in the order in which I see them under Enterprise
Manager ? If so, how do I enforce that the order of the columns is the
same as the order I specify when I created the table ?"Edward Diener" <eddielee_no_spam_here@.tropicsoft.com> wrote in message
news:#Ygoih6XFHA.796@.TK2MSFTNGP09.phx.gbl...
> I create a table by sending a CREATE TABLE command to the database. The
> create is successful but when I look at the table in Enterprise Manager
> the order of the columns is in alphabetic order and not in the order I
> specified when I issued the CREATE TABLE command. Have the columns
> really been created in the order in which I see them under Enterprise
> Manager ? If so, how do I enforce that the order of the columns is the
> same as the order I specify when I created the table ?
Edward,
Does it really matter what order the columns are in? On the data page
itself, the column are put in to an order that SQL Server specifies complete
with headers on each row, null and varchar bitmaps for the row data, and
then the actual row data. If you are using blob objects (text, ntext,
image) then they don't necessarily even live in the row itself, but have
16-byte pointers to other data pages.
To ensure that your data is SELECTed INSERTed and UPDATEed properly, ensure
that you specify a column list in these statements.
Rick Sawtell
MCT, MCSD, MCDBA|||Edward
Are you sure?
I did small test
CREATE TABLE Test8
(
B INT,
A INT
)
Looking in EM I see the same order
Why the order of the columns is so important for you?
When you perform SELECT statement you can specify any order of columns.
"Edward Diener" <eddielee_no_spam_here@.tropicsoft.com> wrote in message
news:%23Ygoih6XFHA.796@.TK2MSFTNGP09.phx.gbl...
> I create a table by sending a CREATE TABLE command to the database. The
> create is successful but when I look at the table in Enterprise Manager
> the order of the columns is in alphabetic order and not in the order I
> specified when I issued the CREATE TABLE command. Have the columns
> really been created in the order in which I see them under Enterprise
> Manager ? If so, how do I enforce that the order of the columns is the
> same as the order I specify when I created the table ?|||You can create a view on the table specifying the order on your own.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Edward Diener" <eddielee_no_spam_here@.tropicsoft.com> schrieb im
Newsbeitrag news:%23Ygoih6XFHA.796@.TK2MSFTNGP09.phx.gbl...
>I create a table by sending a CREATE TABLE command to the database. The
>create is successful but when I look at the table in Enterprise Manager the
>order of the columns is in alphabetic order and not in the order I
>specified when I issued the CREATE TABLE command. Have the columns really
>been created in the order in which I see them under Enterprise Manager ? If
>so, how do I enforce that the order of the columns is the same as the order
>I specify when I created the table ?|||Even though tables in relational databases do not have a "column order"
associated with them, SQL often associates positional significance to the
order of columns in a table. However except in a few circumstances, such
significance of the column order offer little or no benefits.
Not necessarily. To find the order of columns in t-SQL, you can query the
metadata and verify the ORDINAL_POSITION column like:
EXEC sp_columns tbl
Anith|||Rick Sawtell wrote:
> "Edward Diener" <eddielee_no_spam_here@.tropicsoft.com> wrote in message
> news:#Ygoih6XFHA.796@.TK2MSFTNGP09.phx.gbl...
>
>
> Edward,
> Does it really matter what order the columns are in?
Very much so. Are you telling me that I can not ensure the column order
in SQL Server when I create a table ?
> On the data page
> itself, the column are put in to an order that SQL Server specifies comple
te
> with headers on each row, null and varchar bitmaps for the row data, and
> then the actual row data.
When you say "the data page", to what are you referring ?
> If you are using blob objects (text, ntext,
> image) then they don't necessarily even live in the row itself, but have
> 16-byte pointers to other data pages.
> To ensure that your data is SELECTed INSERTed and UPDATEed properly, ensur
e
> that you specify a column list in these statements.
That is not the issue. I need to ensure the actual order of columns is
the same as what I specified when I created the table. Is this the case,
and Enterprise Manager is not showing me the actual column order ? Or is
it the case that SQL Server actually changes the column order from what
I specified when I created the table ? The latter would be terrible.|||Anith Sen wrote:
> Even though tables in relational databases do not have a "column order"
> associated with them, SQL often associates positional significance to the
> order of columns in a table. However except in a few circumstances, such
> significance of the column order offer little or no benefits.
>
>
> Not necessarily. To find the order of columns in t-SQL, you can query the
> metadata and verify the ORDINAL_POSITION column like:
> EXEC sp_columns tbl
>
Sorry, this does work but the order of columns is not what I specified
when I created the table. SQL Server has moved the order of columns.
This is really horrible. There must be some way to ensure that the order
of columns in the table is the same as what I specified when I created
the table.|||Uri Dimant wrote:
> Edward
> Are you sure?
> I did small test
> CREATE TABLE Test8
> (
> B INT,
> A INT
> )
> Looking in EM I see the same order
Try adding primary keys not on the first column.
> Why the order of the columns is so important for you?
I am migrating data from one RDBMS to SQL Server. It is much easier if
the column order is the same in the from and to tables.
> When you perform SELECT statement you can specify any order of columns.
>
>
> "Edward Diener" <eddielee_no_spam_here@.tropicsoft.com> wrote in message
> news:%23Ygoih6XFHA.796@.TK2MSFTNGP09.phx.gbl...
>|||Did you specify the correct table name in place of "tbl"?
Note that the column returned as ORDINAL_POSITION by sp_columns does
NOT necessarily reflect the order in which the columns were defined. If
you insert columns with EM then the table is recreated and you may not
see the result you expect. Also, this behaviour is subject to change in
future versions because EM is going away. Don't rely on it.
David Portas
SQL Server MVP
--|||Edward wrote on Mon, 23 May 2005 11:41:44 -0400:
> Anith Sen wrote:
> Running this stored procedure under SQL Server 7 Query Analyzer I get no
> rows returned.
Check you are putting your own table name in place of tbl, and you are in
the right database. Works fine here on my SQL 7 and SQL 2K servers.
Dan
Thursday, March 22, 2012
CREATE STATISTICS - use...
What is the use of running the command "CREATE STATISTICS"?
SQL 2K.
Thanks,
HarryHi,
You can use the CREATE STATISTICS command to create statistics on nonindexed
columns. Also, you can execute
the sp_createstats stored procedure, which creates single-column statistics
for all eligible columns for all user tables in the current database.
Thanks
Hari
SQL Server MVP
"HarrySmith" <HarrySmith_56@.hotmail.com> wrote in message
news:u2UUTBdrFHA.3604@.tk2msftngp13.phx.gbl...
> What is the use of running the command "CREATE STATISTICS"?
> SQL 2K.
> Thanks,
> Harry
>|||Further to Hari's post, statistics are used by the query optimiser when
it's calculating the best possible way to access the data you're after.
If you're after more info about stats, this whitepaper is really good (I
just read it last week):
Statistics Used by the Query Optimiser in Microsoft SQL Server 2005
<http://www.microsoft.com/technet/pr...5/qrystats.mspx>
It talks specifically about SQL 2005 but the concepts are mostly related
pretty closely to SQL 2000 too.
*mike hodgson*
blog: http://sqlnerd.blogspot.com
Hari Prasad wrote:
>Hi,
>You can use the CREATE STATISTICS command to create statistics on nonindexe
d
>columns. Also, you can execute
>the sp_createstats stored procedure, which creates single-column statistics
>for all eligible columns for all user tables in the current database.
>Thanks
>Hari
>SQL Server MVP
>
>"HarrySmith" <HarrySmith_56@.hotmail.com> wrote in message
>news:u2UUTBdrFHA.3604@.tk2msftngp13.phx.gbl...
>
>
>|||Thank you very much to both of you.
Harry
"Mike Hodgson" <mike.hodgson@.mallesons.nospam.com> wrote in message news:eFg
PIQerFHA.3884@.TK2MSFTNGP11.phx.gbl...
Further to Hari's post, statistics are used by the query optimiser when it's
calculating the best possible way to access the data you're after. If you'
re after more info about stats, this whitepaper is really good (I just read
it last week):
Statistics Used by the Query Optimiser in Microsoft SQL Server 2005
It talks specifically about SQL 2005 but the concepts are mostly related pre
tty closely to SQL 2000 too.
mike hodgson
blog: http://sqlnerd.blogspot.com
Hari Prasad wrote:
Hi,
You can use the CREATE STATISTICS command to create statistics on nonindexed
columns. Also, you can execute
the sp_createstats stored procedure, which creates single-column statistics
for all eligible columns for all user tables in the current database.
Thanks
Hari
SQL Server MVP
"HarrySmith" <HarrySmith_56@.hotmail.com> wrote in message
news:u2UUTBdrFHA.3604@.tk2msftngp13.phx.gbl...
What is the use of running the command "CREATE STATISTICS"?
SQL 2K.
Thanks,
Harrysql
SQL 2K.
Thanks,
HarryHi,
You can use the CREATE STATISTICS command to create statistics on nonindexed
columns. Also, you can execute
the sp_createstats stored procedure, which creates single-column statistics
for all eligible columns for all user tables in the current database.
Thanks
Hari
SQL Server MVP
"HarrySmith" <HarrySmith_56@.hotmail.com> wrote in message
news:u2UUTBdrFHA.3604@.tk2msftngp13.phx.gbl...
> What is the use of running the command "CREATE STATISTICS"?
> SQL 2K.
> Thanks,
> Harry
>|||Further to Hari's post, statistics are used by the query optimiser when
it's calculating the best possible way to access the data you're after.
If you're after more info about stats, this whitepaper is really good (I
just read it last week):
Statistics Used by the Query Optimiser in Microsoft SQL Server 2005
<http://www.microsoft.com/technet/pr...5/qrystats.mspx>
It talks specifically about SQL 2005 but the concepts are mostly related
pretty closely to SQL 2000 too.
*mike hodgson*
blog: http://sqlnerd.blogspot.com
Hari Prasad wrote:
>Hi,
>You can use the CREATE STATISTICS command to create statistics on nonindexe
d
>columns. Also, you can execute
>the sp_createstats stored procedure, which creates single-column statistics
>for all eligible columns for all user tables in the current database.
>Thanks
>Hari
>SQL Server MVP
>
>"HarrySmith" <HarrySmith_56@.hotmail.com> wrote in message
>news:u2UUTBdrFHA.3604@.tk2msftngp13.phx.gbl...
>
>
>|||Thank you very much to both of you.
Harry
"Mike Hodgson" <mike.hodgson@.mallesons.nospam.com> wrote in message news:eFg
PIQerFHA.3884@.TK2MSFTNGP11.phx.gbl...
Further to Hari's post, statistics are used by the query optimiser when it's
calculating the best possible way to access the data you're after. If you'
re after more info about stats, this whitepaper is really good (I just read
it last week):
Statistics Used by the Query Optimiser in Microsoft SQL Server 2005
It talks specifically about SQL 2005 but the concepts are mostly related pre
tty closely to SQL 2000 too.
mike hodgson
blog: http://sqlnerd.blogspot.com
Hari Prasad wrote:
Hi,
You can use the CREATE STATISTICS command to create statistics on nonindexed
columns. Also, you can execute
the sp_createstats stored procedure, which creates single-column statistics
for all eligible columns for all user tables in the current database.
Thanks
Hari
SQL Server MVP
"HarrySmith" <HarrySmith_56@.hotmail.com> wrote in message
news:u2UUTBdrFHA.3604@.tk2msftngp13.phx.gbl...
What is the use of running the command "CREATE STATISTICS"?
SQL 2K.
Thanks,
Harrysql
CREATE STATISTICS - use...
What is the use of running the command "CREATE STATISTICS"?
SQL 2K.
Thanks,
Harry
Hi,
You can use the CREATE STATISTICS command to create statistics on nonindexed
columns. Also, you can execute
the sp_createstats stored procedure, which creates single-column statistics
for all eligible columns for all user tables in the current database.
Thanks
Hari
SQL Server MVP
"HarrySmith" <HarrySmith_56@.hotmail.com> wrote in message
news:u2UUTBdrFHA.3604@.tk2msftngp13.phx.gbl...
> What is the use of running the command "CREATE STATISTICS"?
> SQL 2K.
> Thanks,
> Harry
>
|||Further to Hari's post, statistics are used by the query optimiser when
it's calculating the best possible way to access the data you're after.
If you're after more info about stats, this whitepaper is really good (I
just read it last week):
Statistics Used by the Query Optimiser in Microsoft SQL Server 2005
<http://www.microsoft.com/technet/pro.../qrystats.mspx>
It talks specifically about SQL 2005 but the concepts are mostly related
pretty closely to SQL 2000 too.
*mike hodgson*
blog: http://sqlnerd.blogspot.com
Hari Prasad wrote:
>Hi,
>You can use the CREATE STATISTICS command to create statistics on nonindexed
>columns. Also, you can execute
>the sp_createstats stored procedure, which creates single-column statistics
>for all eligible columns for all user tables in the current database.
>Thanks
>Hari
>SQL Server MVP
>
>"HarrySmith" <HarrySmith_56@.hotmail.com> wrote in message
>news:u2UUTBdrFHA.3604@.tk2msftngp13.phx.gbl...
>
>
>
|||Thank you very much to both of you.
Harry
"Mike Hodgson" <mike.hodgson@.mallesons.nospam.com> wrote in message news:eFgPIQerFHA.3884@.TK2MSFTNGP11.phx.gbl...
Further to Hari's post, statistics are used by the query optimiser when it's calculating the best possible way to access the data you're after. If you're after more info about stats, this whitepaper is really good (I just read it last week):
Statistics Used by the Query Optimiser in Microsoft SQL Server 2005
It talks specifically about SQL 2005 but the concepts are mostly related pretty closely to SQL 2000 too.
mike hodgson
blog: http://sqlnerd.blogspot.com
Hari Prasad wrote:
Hi,
You can use the CREATE STATISTICS command to create statistics on nonindexed
columns. Also, you can execute
the sp_createstats stored procedure, which creates single-column statistics
for all eligible columns for all user tables in the current database.
Thanks
Hari
SQL Server MVP
"HarrySmith" <HarrySmith_56@.hotmail.com> wrote in message
news:u2UUTBdrFHA.3604@.tk2msftngp13.phx.gbl...
What is the use of running the command "CREATE STATISTICS"?
SQL 2K.
Thanks,
Harry
SQL 2K.
Thanks,
Harry
Hi,
You can use the CREATE STATISTICS command to create statistics on nonindexed
columns. Also, you can execute
the sp_createstats stored procedure, which creates single-column statistics
for all eligible columns for all user tables in the current database.
Thanks
Hari
SQL Server MVP
"HarrySmith" <HarrySmith_56@.hotmail.com> wrote in message
news:u2UUTBdrFHA.3604@.tk2msftngp13.phx.gbl...
> What is the use of running the command "CREATE STATISTICS"?
> SQL 2K.
> Thanks,
> Harry
>
|||Further to Hari's post, statistics are used by the query optimiser when
it's calculating the best possible way to access the data you're after.
If you're after more info about stats, this whitepaper is really good (I
just read it last week):
Statistics Used by the Query Optimiser in Microsoft SQL Server 2005
<http://www.microsoft.com/technet/pro.../qrystats.mspx>
It talks specifically about SQL 2005 but the concepts are mostly related
pretty closely to SQL 2000 too.
*mike hodgson*
blog: http://sqlnerd.blogspot.com
Hari Prasad wrote:
>Hi,
>You can use the CREATE STATISTICS command to create statistics on nonindexed
>columns. Also, you can execute
>the sp_createstats stored procedure, which creates single-column statistics
>for all eligible columns for all user tables in the current database.
>Thanks
>Hari
>SQL Server MVP
>
>"HarrySmith" <HarrySmith_56@.hotmail.com> wrote in message
>news:u2UUTBdrFHA.3604@.tk2msftngp13.phx.gbl...
>
>
>
|||Thank you very much to both of you.
Harry
"Mike Hodgson" <mike.hodgson@.mallesons.nospam.com> wrote in message news:eFgPIQerFHA.3884@.TK2MSFTNGP11.phx.gbl...
Further to Hari's post, statistics are used by the query optimiser when it's calculating the best possible way to access the data you're after. If you're after more info about stats, this whitepaper is really good (I just read it last week):
Statistics Used by the Query Optimiser in Microsoft SQL Server 2005
It talks specifically about SQL 2005 but the concepts are mostly related pretty closely to SQL 2000 too.
mike hodgson
blog: http://sqlnerd.blogspot.com
Hari Prasad wrote:
Hi,
You can use the CREATE STATISTICS command to create statistics on nonindexed
columns. Also, you can execute
the sp_createstats stored procedure, which creates single-column statistics
for all eligible columns for all user tables in the current database.
Thanks
Hari
SQL Server MVP
"HarrySmith" <HarrySmith_56@.hotmail.com> wrote in message
news:u2UUTBdrFHA.3604@.tk2msftngp13.phx.gbl...
What is the use of running the command "CREATE STATISTICS"?
SQL 2K.
Thanks,
Harry
Wednesday, March 21, 2012
Create SQL Server Objects from Command Prompts
Hi
Is there any why to Create SQL Server Objects from Command Prompts like (Databases , Tables, Stored Procedures, …) ??
If you will Install some Applications Like this forums you will see the SQL Server object Created from Command Prompts
How Can I do that .. ??
And thanks with my regarding
FraasHave a look at OSQL in SQL Server BOL
Create Snapshot -> FAILED! you dont have sufficient permission to run this command
Hi all,
So, wait for SP3 or get this hotfix update if you really need it, but the hotfix is not fully tested and official.
i'm trying to create a publication and its snapshot in the default snapshot folder of MS SQL Server 2005.
It's all done by RMO.
Following Scenario:
1. PublicationDB was created by User1(sysadmin) ... successful
2. Enable PublicationDB for Publishing ... successful
2. Creating the publication: executed as User2(db_owner)
2.1 publication.Create(); ... successful
2.2 publication.CreateSnapshotAgent(); ... successful
2.3 Add Articles to publication ... successful
2.4 Generate Snapshot with
agent = new SnapshotGenerationAgent(); and setting all parameters for it, then execute by
agent.GenerateSnapshot();
And at this point,i got an error message, because the snapshot agent cant be executed ...2007-09-12 12:05:46.58 User-specified agent parameter values:
2007-09-12 12:05:46.58 --
2007-09-12 12:05:46.60 -Publisher EDOM04\SQLstandard
2007-09-12 12:05:46.60 -PublisherDB TMS4X_PublicationDB
2007-09-12 12:05:46.60 -Publication TMS4X_PublicationTest
2007-09-12 12:05:46.60 -ReplicationType 2
2007-09-12 12:05:46.60 -Distributor EDOM04\SQLstandard
2007-09-12 12:05:46.60 -DistributorSecurityMode 1
2007-09-12 12:05:46.60 -PublisherSecurityMode 1
2007-09-12 12:05:46.60 --
2007-09-12 12:05:46.63 Connecting to Distributor 'EDOM04\SQLstandard'
2007-09-12 12:05:46.96 The replication agent had encountered an exception.
2007-09-12 12:05:46.96 Source: Replication
2007-09-12 12:05:46.96 Exception Type: Microsoft.SqlServer.Replication.ReplicationAgentSqlException
2007-09-12 12:05:46.96 Exception Message: You do not have sufficient permission to run this command.
Contact your system administrator.
2007-09-12 12:05:46.96 Message Code: 14260
2007-09-12 12:05:46.96
Configurations:
-All Users have rights for read and write on the snapshotfolder including the Agent
-All users are defined in the same windows domain
-the snapshotagent account has sysadmin rights on the server and is assigned to the predefined MS SQL User Role
This scenario is workin completely fine when i exceute everything as a "sysadmin"!
But when executing it all as "db_owner" of the database, its not workin!!!
Does anybody has any resolutions for this problem?
I appreciate any support.
MariJo
Hi,
Just double check, when you say the account is 'db_owner', is it an db_owner of both publication database and distribution database? Replication requires both. For complete replication agent security requirement, you can refer to http://technet.microsoft.com/en-us/library/ms151868.aspx
If it still does not work, please let me know.
Peng
|||Hi,thx for your reply.
I read already a lot about it in the msdn, also that link that you mentioned. I found out about the proxies and credentials used by sql server.
Following:
When creating the publication by a User (db_owner), it only works when the User has db_owner rights for both, the publication and distribution database. But i dont want to grant this user account the db_owner rights for the system database 'distributionDb'. Thats i would like to solve it by impersonating and that process account for the agent.
I also created the windows account "repl_snapshot" just for the Snapshot Agent.
When creating the Snapshot agent for the publication, i also use the "impersonating ... process account", therefore the "repl_snapshot" should excecute the snapshot creation, cause the agent is defined like this. When creating this snapshot agent: the proxy and credential are automatically created.
Next point is that i need to assign the account User1 (db_owner) to that proxy, right?!
But then i get the message:
it starts the agent ... success
executing ... no rights!
and then! Big problem ... nothing works anymore on that database. and then i need to use the sp_removedbreplication and create a new db.
So i dont understand completely how to configure this proxy for this agent job. Cause i found out, that i need to assign the proxy to each agent job step, but i cant do that! cause when i open the agent job to assign the proxy to the steps: no steps are shown. But when starting the agent job over the context menu, i shows the three steps, sth like: start egent, execute, end.
What do you think? Am i on the right way?
I think, I will have the same problems when creating/Synchronizing a subscription form an sql express server for the merge Replication at the end, cause there is also the 'repl_merge' merge agent as the process account.
MariJo
|||
Hi,
When you create the publication and configure snapshot agent, there are two sections in the "snapshot agent security" dialog: a windows account that snapshot agent process that runs under (snapshot agent use this account to connect to distributor), and if you would like to connect to publisher by impersonating process account. You need to assign the db_owner role to the proxy account to the distribution db and publication db (if you choose to connect to publisher by impersonating process account). From your reply, it is still not clear to me if you assign the db_owner role to the proxy account at both distribution/publication DB.
To use another proxy account or simply sqlagent service account for snapshot agent, goto "publication properties" dialog and choose "agent securities" tab and you should be able to launch "snapshot agent security" dialog and modify it.
Peng
|||
hi,
Sorry if it wasnt clear. Indeed, i did assign the db_owner role to the proxy account at both distribution/publication DB.
Its just that i want to run that Snapshot agent in a context of a proxy and thats not working, so i found out sth on the Support page of Microsoft. Microsoft has a hotfix for exactly that problem, i think ... i didnt try it yet ... but for sure im going to do that on monday.
Hotfix: A SQL Server Agent job fails when you run the SQL Server Agent job in the context of a proxy account in SQL Server 2005, http://support.microsoft.com/kb/938086
Will let you know for more when i've tested it ...
MariJo
|||Its working with this hotifx!!!!So, wait for SP3 or get this hotfix update if you really need it, but the hotfix is not fully tested and official.
Regards,
MariJo
Create Snapshot -> FAILED! you dont have sufficient permission to run this command
Hi all,
So, wait for SP3 or get this hotfix update if you really need it, but the hotfix is not fully tested and official.
i'm trying to create a publication and its snapshot in the default snapshot folder of MS SQL Server 2005.
It's all done by RMO.
Following Scenario:
1. PublicationDB was created by User1(sysadmin) ... successful
2. Enable PublicationDB for Publishing ... successful
2. Creating the publication: executed as User2(db_owner)
2.1 publication.Create(); ... successful
2.2 publication.CreateSnapshotAgent(); ... successful
2.3 Add Articles to publication ... successful
2.4 Generate Snapshot with
agent = new SnapshotGenerationAgent(); and setting all parameters for it, then execute by
agent.GenerateSnapshot();
And at this point,i got an error message, because the snapshot agent cant be executed ...2007-09-12 12:05:46.58 User-specified agent parameter values:
2007-09-12 12:05:46.58 --
2007-09-12 12:05:46.60 -Publisher EDOM04\SQLstandard
2007-09-12 12:05:46.60 -PublisherDB TMS4X_PublicationDB
2007-09-12 12:05:46.60 -Publication TMS4X_PublicationTest
2007-09-12 12:05:46.60 -ReplicationType 2
2007-09-12 12:05:46.60 -Distributor EDOM04\SQLstandard
2007-09-12 12:05:46.60 -DistributorSecurityMode 1
2007-09-12 12:05:46.60 -PublisherSecurityMode 1
2007-09-12 12:05:46.60 --
2007-09-12 12:05:46.63 Connecting to Distributor 'EDOM04\SQLstandard'
2007-09-12 12:05:46.96 The replication agent had encountered an exception.
2007-09-12 12:05:46.96 Source: Replication
2007-09-12 12:05:46.96 Exception Type: Microsoft.SqlServer.Replication.ReplicationAgentSqlException
2007-09-12 12:05:46.96 Exception Message: You do not have sufficient permission to run this command.
Contact your system administrator.
2007-09-12 12:05:46.96 Message Code: 14260
2007-09-12 12:05:46.96
Configurations:
-All Users have rights for read and write on the snapshotfolder including the Agent
-All users are defined in the same windows domain
-the snapshotagent account has sysadmin rights on the server and is assigned to the predefined MS SQL User Role
This scenario is workin completely fine when i exceute everything as a "sysadmin"!
But when executing it all as "db_owner" of the database, its not workin!!!
Does anybody has any resolutions for this problem?
I appreciate any support.
MariJo
Hi,
Just double check, when you say the account is 'db_owner', is it an db_owner of both publication database and distribution database? Replication requires both. For complete replication agent security requirement, you can refer to http://technet.microsoft.com/en-us/library/ms151868.aspx
If it still does not work, please let me know.
Peng
|||Hi,thx for your reply.
I read already a lot about it in the msdn, also that link that you mentioned. I found out about the proxies and credentials used by sql server.
Following:
When creating the publication by a User (db_owner), it only works when the User has db_owner rights for both, the publication and distribution database. But i dont want to grant this user account the db_owner rights for the system database 'distributionDb'. Thats i would like to solve it by impersonating and that process account for the agent.
I also created the windows account "repl_snapshot" just for the Snapshot Agent.
When creating the Snapshot agent for the publication, i also use the "impersonating ... process account", therefore the "repl_snapshot" should excecute the snapshot creation, cause the agent is defined like this. When creating this snapshot agent: the proxy and credential are automatically created.
Next point is that i need to assign the account User1 (db_owner) to that proxy, right?!
But then i get the message:
it starts the agent ... success
executing ... no rights!
and then! Big problem ... nothing works anymore on that database. and then i need to use the sp_removedbreplication and create a new db.
So i dont understand completely how to configure this proxy for this agent job. Cause i found out, that i need to assign the proxy to each agent job step, but i cant do that! cause when i open the agent job to assign the proxy to the steps: no steps are shown. But when starting the agent job over the context menu, i shows the three steps, sth like: start egent, execute, end.
What do you think? Am i on the right way?
I think, I will have the same problems when creating/Synchronizing a subscription form an sql express server for the merge Replication at the end, cause there is also the 'repl_merge' merge agent as the process account.
MariJo
|||
Hi,
When you create the publication and configure snapshot agent, there are two sections in the "snapshot agent security" dialog: a windows account that snapshot agent process that runs under (snapshot agent use this account to connect to distributor), and if you would like to connect to publisher by impersonating process account. You need to assign the db_owner role to the proxy account to the distribution db and publication db (if you choose to connect to publisher by impersonating process account). From your reply, it is still not clear to me if you assign the db_owner role to the proxy account at both distribution/publication DB.
To use another proxy account or simply sqlagent service account for snapshot agent, goto "publication properties" dialog and choose "agent securities" tab and you should be able to launch "snapshot agent security" dialog and modify it.
Peng
|||
hi,
Sorry if it wasnt clear. Indeed, i did assign the db_owner role to the proxy account at both distribution/publication DB.
Its just that i want to run that Snapshot agent in a context of a proxy and thats not working, so i found out sth on the Support page of Microsoft. Microsoft has a hotfix for exactly that problem, i think ... i didnt try it yet ... but for sure im going to do that on monday.
Hotfix: A SQL Server Agent job fails when you run the SQL Server Agent job in the context of a proxy account in SQL Server 2005, http://support.microsoft.com/kb/938086
Will let you know for more when i've tested it ...
MariJo
|||Its working with this hotifx!!!!So, wait for SP3 or get this hotfix update if you really need it, but the hotfix is not fully tested and official.
Regards,
MariJo
Create Snapshot -> FAILED! you dont have sufficient permission to run this command
Hi all,
So, wait for SP3 or get this hotfix update if you really need it, but the hotfix is not fully tested and official.
i'm trying to create a publication and its snapshot in the default snapshot folder of MS SQL Server 2005.
It's all done by RMO.
Following Scenario:
1. PublicationDB was created by User1(sysadmin) ... successful
2. Enable PublicationDB for Publishing ... successful
2. Creating the publication: executed as User2(db_owner)
2.1 publication.Create(); ... successful
2.2 publication.CreateSnapshotAgent(); ... successful
2.3 Add Articles to publication ... successful
2.4 Generate Snapshot with
agent = new SnapshotGenerationAgent(); and setting all parameters for it, then execute by
agent.GenerateSnapshot();
And at this point,i got an error message, because the snapshot agent cant be executed ...2007-09-12 12:05:46.58 User-specified agent parameter values:
2007-09-12 12:05:46.58 --
2007-09-12 12:05:46.60 -Publisher EDOM04\SQLstandard
2007-09-12 12:05:46.60 -PublisherDB TMS4X_PublicationDB
2007-09-12 12:05:46.60 -Publication TMS4X_PublicationTest
2007-09-12 12:05:46.60 -ReplicationType 2
2007-09-12 12:05:46.60 -Distributor EDOM04\SQLstandard
2007-09-12 12:05:46.60 -DistributorSecurityMode 1
2007-09-12 12:05:46.60 -PublisherSecurityMode 1
2007-09-12 12:05:46.60 --
2007-09-12 12:05:46.63 Connecting to Distributor 'EDOM04\SQLstandard'
2007-09-12 12:05:46.96 The replication agent had encountered an exception.
2007-09-12 12:05:46.96 Source: Replication
2007-09-12 12:05:46.96 Exception Type: Microsoft.SqlServer.Replication.ReplicationAgentSqlException
2007-09-12 12:05:46.96 Exception Message: You do not have sufficient permission to run this command.
Contact your system administrator.
2007-09-12 12:05:46.96 Message Code: 14260
2007-09-12 12:05:46.96
Configurations:
-All Users have rights for read and write on the snapshotfolder including the Agent
-All users are defined in the same windows domain
-the snapshotagent account has sysadmin rights on the server and is assigned to the predefined MS SQL User Role
This scenario is workin completely fine when i exceute everything as a "sysadmin"!
But when executing it all as "db_owner" of the database, its not workin!!!
Does anybody has any resolutions for this problem?
I appreciate any support.
MariJo
Hi,
Just double check, when you say the account is 'db_owner', is it an db_owner of both publication database and distribution database? Replication requires both. For complete replication agent security requirement, you can refer to http://technet.microsoft.com/en-us/library/ms151868.aspx
If it still does not work, please let me know.
Peng
|||Hi,thx for your reply.
I read already a lot about it in the msdn, also that link that you mentioned. I found out about the proxies and credentials used by sql server.
Following:
When creating the publication by a User (db_owner), it only works when the User has db_owner rights for both, the publication and distribution database. But i dont want to grant this user account the db_owner rights for the system database 'distributionDb'. Thats i would like to solve it by impersonating and that process account for the agent.
I also created the windows account "repl_snapshot" just for the Snapshot Agent.
When creating the Snapshot agent for the publication, i also use the "impersonating ... process account", therefore the "repl_snapshot" should excecute the snapshot creation, cause the agent is defined like this. When creating this snapshot agent: the proxy and credential are automatically created.
Next point is that i need to assign the account User1 (db_owner) to that proxy, right?!
But then i get the message:
it starts the agent ... success
executing ... no rights!
and then! Big problem ... nothing works anymore on that database. and then i need to use the sp_removedbreplication and create a new db.
So i dont understand completely how to configure this proxy for this agent job. Cause i found out, that i need to assign the proxy to each agent job step, but i cant do that! cause when i open the agent job to assign the proxy to the steps: no steps are shown. But when starting the agent job over the context menu, i shows the three steps, sth like: start egent, execute, end.
What do you think? Am i on the right way?
I think, I will have the same problems when creating/Synchronizing a subscription form an sql express server for the merge Replication at the end, cause there is also the 'repl_merge' merge agent as the process account.
MariJo
|||
Hi,
When you create the publication and configure snapshot agent, there are two sections in the "snapshot agent security" dialog: a windows account that snapshot agent process that runs under (snapshot agent use this account to connect to distributor), and if you would like to connect to publisher by impersonating process account. You need to assign the db_owner role to the proxy account to the distribution db and publication db (if you choose to connect to publisher by impersonating process account). From your reply, it is still not clear to me if you assign the db_owner role to the proxy account at both distribution/publication DB.
To use another proxy account or simply sqlagent service account for snapshot agent, goto "publication properties" dialog and choose "agent securities" tab and you should be able to launch "snapshot agent security" dialog and modify it.
Peng
|||
hi,
Sorry if it wasnt clear. Indeed, i did assign the db_owner role to the proxy account at both distribution/publication DB.
Its just that i want to run that Snapshot agent in a context of a proxy and thats not working, so i found out sth on the Support page of Microsoft. Microsoft has a hotfix for exactly that problem, i think ... i didnt try it yet ... but for sure im going to do that on monday.
Hotfix: A SQL Server Agent job fails when you run the SQL Server Agent job in the context of a proxy account in SQL Server 2005, http://support.microsoft.com/kb/938086
Will let you know for more when i've tested it ...
MariJo
|||Its working with this hotifx!!!!So, wait for SP3 or get this hotfix update if you really need it, but the hotfix is not fully tested and official.
Regards,
MariJo
Create Snapshot -> FAILED! you dont have sufficient permission to run this command
Hi all,
So, wait for SP3 or get this hotfix update if you really need it, but the hotfix is not fully tested and official.
i'm trying to create a publication and its snapshot in the default snapshot folder of MS SQL Server 2005.
It's all done by RMO.
Following Scenario:
1. PublicationDB was created by User1(sysadmin) ... successful
2. Enable PublicationDB for Publishing ... successful
2. Creating the publication: executed as User2(db_owner)
2.1 publication.Create(); ... successful
2.2 publication.CreateSnapshotAgent(); ... successful
2.3 Add Articles to publication ... successful
2.4 Generate Snapshot with
agent = new SnapshotGenerationAgent(); and setting all parameters for it, then execute by
agent.GenerateSnapshot();
And at this point,i got an error message, because the snapshot agent cant be executed ...2007-09-12 12:05:46.58 User-specified agent parameter values:
2007-09-12 12:05:46.58 --
2007-09-12 12:05:46.60 -Publisher EDOM04\SQLstandard
2007-09-12 12:05:46.60 -PublisherDB TMS4X_PublicationDB
2007-09-12 12:05:46.60 -Publication TMS4X_PublicationTest
2007-09-12 12:05:46.60 -ReplicationType 2
2007-09-12 12:05:46.60 -Distributor EDOM04\SQLstandard
2007-09-12 12:05:46.60 -DistributorSecurityMode 1
2007-09-12 12:05:46.60 -PublisherSecurityMode 1
2007-09-12 12:05:46.60 --
2007-09-12 12:05:46.63 Connecting to Distributor 'EDOM04\SQLstandard'
2007-09-12 12:05:46.96 The replication agent had encountered an exception.
2007-09-12 12:05:46.96 Source: Replication
2007-09-12 12:05:46.96 Exception Type: Microsoft.SqlServer.Replication.ReplicationAgentSqlException
2007-09-12 12:05:46.96 Exception Message: You do not have sufficient permission to run this command.
Contact your system administrator.
2007-09-12 12:05:46.96 Message Code: 14260
2007-09-12 12:05:46.96
Configurations:
-All Users have rights for read and write on the snapshotfolder including the Agent
-All users are defined in the same windows domain
-the snapshotagent account has sysadmin rights on the server and is assigned to the predefined MS SQL User Role
This scenario is workin completely fine when i exceute everything as a "sysadmin"!
But when executing it all as "db_owner" of the database, its not workin!!!
Does anybody has any resolutions for this problem?
I appreciate any support.
MariJo
Hi,
Just double check, when you say the account is 'db_owner', is it an db_owner of both publication database and distribution database? Replication requires both. For complete replication agent security requirement, you can refer to http://technet.microsoft.com/en-us/library/ms151868.aspx
If it still does not work, please let me know.
Peng
|||Hi,thx for your reply.
I read already a lot about it in the msdn, also that link that you mentioned. I found out about the proxies and credentials used by sql server.
Following:
When creating the publication by a User (db_owner), it only works when the User has db_owner rights for both, the publication and distribution database. But i dont want to grant this user account the db_owner rights for the system database 'distributionDb'. Thats i would like to solve it by impersonating and that process account for the agent.
I also created the windows account "repl_snapshot" just for the Snapshot Agent.
When creating the Snapshot agent for the publication, i also use the "impersonating ... process account", therefore the "repl_snapshot" should excecute the snapshot creation, cause the agent is defined like this. When creating this snapshot agent: the proxy and credential are automatically created.
Next point is that i need to assign the account User1 (db_owner) to that proxy, right?!
But then i get the message:
it starts the agent ... success
executing ... no rights!
and then! Big problem ... nothing works anymore on that database. and then i need to use the sp_removedbreplication and create a new db.
So i dont understand completely how to configure this proxy for this agent job. Cause i found out, that i need to assign the proxy to each agent job step, but i cant do that! cause when i open the agent job to assign the proxy to the steps: no steps are shown. But when starting the agent job over the context menu, i shows the three steps, sth like: start egent, execute, end.
What do you think? Am i on the right way?
I think, I will have the same problems when creating/Synchronizing a subscription form an sql express server for the merge Replication at the end, cause there is also the 'repl_merge' merge agent as the process account.
MariJo
|||
Hi,
When you create the publication and configure snapshot agent, there are two sections in the "snapshot agent security" dialog: a windows account that snapshot agent process that runs under (snapshot agent use this account to connect to distributor), and if you would like to connect to publisher by impersonating process account. You need to assign the db_owner role to the proxy account to the distribution db and publication db (if you choose to connect to publisher by impersonating process account). From your reply, it is still not clear to me if you assign the db_owner role to the proxy account at both distribution/publication DB.
To use another proxy account or simply sqlagent service account for snapshot agent, goto "publication properties" dialog and choose "agent securities" tab and you should be able to launch "snapshot agent security" dialog and modify it.
Peng
|||
hi,
Sorry if it wasnt clear. Indeed, i did assign the db_owner role to the proxy account at both distribution/publication DB.
Its just that i want to run that Snapshot agent in a context of a proxy and thats not working, so i found out sth on the Support page of Microsoft. Microsoft has a hotfix for exactly that problem, i think ... i didnt try it yet ... but for sure im going to do that on monday.
Hotfix: A SQL Server Agent job fails when you run the SQL Server Agent job in the context of a proxy account in SQL Server 2005, http://support.microsoft.com/kb/938086
Will let you know for more when i've tested it ...
MariJo
|||Its working with this hotifx!!!!So, wait for SP3 or get this hotfix update if you really need it, but the hotfix is not fully tested and official.
Regards,
MariJosql
Sunday, March 11, 2012
Create Procedure Command
Good morning,
The Transact-SQL Reference documentation states:
"All data types, including text, ntext and image, can be used as a parameter
for a stored procedure."
I would like to pass a table variable... is this possible?
Thanks,
FOrch
>I would like to pass a table variable... is this possible?
NO
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
"Forch" <Forch@.discussions.microsoft.com> wrote in message
news:1AAA1E85-4F2D-4110-A6D0-81DDE4255694@.microsoft.com...
> Good morning,
> The Transact-SQL Reference documentation states:
> "All data types, including text, ntext and image, can be used as a
> parameter
> for a stored procedure."
> I would like to pass a table variable... is this possible?
> Thanks,
> FOrch
>
The Transact-SQL Reference documentation states:
"All data types, including text, ntext and image, can be used as a parameter
for a stored procedure."
I would like to pass a table variable... is this possible?
Thanks,
FOrch
>I would like to pass a table variable... is this possible?
NO
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
"Forch" <Forch@.discussions.microsoft.com> wrote in message
news:1AAA1E85-4F2D-4110-A6D0-81DDE4255694@.microsoft.com...
> Good morning,
> The Transact-SQL Reference documentation states:
> "All data types, including text, ntext and image, can be used as a
> parameter
> for a stored procedure."
> I would like to pass a table variable... is this possible?
> Thanks,
> FOrch
>
Create Procedure Command
Good morning,
The Transact-SQL Reference documentation states:
"All data types, including text, ntext and image, can be used as a parameter
for a stored procedure."
I would like to pass a table variable... is this possible?
Thanks,
FOrch>I would like to pass a table variable... is this possible?
NO
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
"Forch" <Forch@.discussions.microsoft.com> wrote in message
news:1AAA1E85-4F2D-4110-A6D0-81DDE4255694@.microsoft.com...
> Good morning,
> The Transact-SQL Reference documentation states:
> "All data types, including text, ntext and image, can be used as a
> parameter
> for a stored procedure."
> I would like to pass a table variable... is this possible?
> Thanks,
> FOrch
>
The Transact-SQL Reference documentation states:
"All data types, including text, ntext and image, can be used as a parameter
for a stored procedure."
I would like to pass a table variable... is this possible?
Thanks,
FOrch>I would like to pass a table variable... is this possible?
NO
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
"Forch" <Forch@.discussions.microsoft.com> wrote in message
news:1AAA1E85-4F2D-4110-A6D0-81DDE4255694@.microsoft.com...
> Good morning,
> The Transact-SQL Reference documentation states:
> "All data types, including text, ntext and image, can be used as a
> parameter
> for a stored procedure."
> I would like to pass a table variable... is this possible?
> Thanks,
> FOrch
>
Create Procedure Command
Good morning,
The Transact-SQL Reference documentation states:
"All data types, including text, ntext and image, can be used as a parameter
for a stored procedure."
I would like to pass a table variable... is this possible?
Thanks,
FOrch>I would like to pass a table variable... is this possible?
NO
--
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
"Forch" <Forch@.discussions.microsoft.com> wrote in message
news:1AAA1E85-4F2D-4110-A6D0-81DDE4255694@.microsoft.com...
> Good morning,
> The Transact-SQL Reference documentation states:
> "All data types, including text, ntext and image, can be used as a
> parameter
> for a stored procedure."
> I would like to pass a table variable... is this possible?
> Thanks,
> FOrch
>
The Transact-SQL Reference documentation states:
"All data types, including text, ntext and image, can be used as a parameter
for a stored procedure."
I would like to pass a table variable... is this possible?
Thanks,
FOrch>I would like to pass a table variable... is this possible?
NO
--
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
"Forch" <Forch@.discussions.microsoft.com> wrote in message
news:1AAA1E85-4F2D-4110-A6D0-81DDE4255694@.microsoft.com...
> Good morning,
> The Transact-SQL Reference documentation states:
> "All data types, including text, ntext and image, can be used as a
> parameter
> for a stored procedure."
> I would like to pass a table variable... is this possible?
> Thanks,
> FOrch
>
Create Or Replace
Looking for something analogous to the Create Or Replace option in Oracle. Is there an equivalent command in SQL Server?ALTER PROC <PROC Name>
You want to do this because of the GRANTS?
SQL Server sometimes tends to be finicky...I alway DROP & CREATE, then GRANT|||Hey man, thanks for the reply. I'm just trying to get use to SQL Server and I'm working on a script to create a db, tables, grants, yadayadayada.
I guess that no different then any other ANSI stanard DB...
Drop... DUH! my bad, boy am I rusty...
:o
You want to do this because of the GRANTS?
SQL Server sometimes tends to be finicky...I alway DROP & CREATE, then GRANT|||Hey man, thanks for the reply. I'm just trying to get use to SQL Server and I'm working on a script to create a db, tables, grants, yadayadayada.
I guess that no different then any other ANSI stanard DB...
Drop... DUH! my bad, boy am I rusty...
:o
Friday, February 24, 2012
CREATE GLOBAL CUBE in dataset MDX query.
Hello,
I am attempting to use a CREATE GLOBAL CUBE command when defining a data set in MSRS. The intent is to use the MSRS security and scheduling capabilities to dump local cube files from our data warehouse to a file share, then use MSRS's delivery mechanisms to deliver the cubes to the intended recipient(s). The problem (I believe) is that the command does not return any data, so when rendering the report I bound the dataset to, the server just stops responding. I assume it is waiting for data that will never come. The report itself only contains a text box with a string literal. The good news is that when I try to render the report, the local cube file is generated...I just need the server to finish rendering the "dummy" report. Any ideas? Thanks.
-Rob HoffmanRob, did you look at DTS as an alternative solution?
Reporting Services isn't really meant to be used in the way you're trying to
use it. One area of concern is that you mention the server stops
responding. It it only this particular report that doesn't get rendered, or
does the server stop responding to any request?
-Lukasz
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Rob Hoffman" <Rob Hoffman@.discussions.microsoft.com> wrote in message
news:6E2F8425-C095-4F36-9A40-C1CD6ADD73DD@.microsoft.com...
> Hello,
> I am attempting to use a CREATE GLOBAL CUBE command when defining a data
> set in MSRS. The intent is to use the MSRS security and scheduling
> capabilities to dump local cube files from our data warehouse to a file
> share, then use MSRS's delivery mechanisms to deliver the cubes to the
> intended recipient(s). The problem (I believe) is that the command does
> not return any data, so when rendering the report I bound the dataset to,
> the server just stops responding. I assume it is waiting for data that
> will never come. The report itself only contains a text box with a string
> literal. The good news is that when I try to render the report, the local
> cube file is generated...I just need the server to finish rendering the
> "dummy" report. Any ideas? Thanks.
> -Rob Hoffman|||Lukasz,
Yes, but RS looked like a better choice out of the box because of its built-in security model, job scheduling, delivery mechanisms and extensibility. The requirements of the project I'm working on make it an expedient alternative to writing code that allows users to define, schedule and deliver local OLAP cubes from our data warehouse source.
To answer your question, when I view (view tab) my dummy report in Report Manager, the report never renders, and my aspnet_wp process consumes all processing power on the server. When I open a second browser window and try to access Report Manager, the page never loads. It looks like there is some sort of infinite loop occurring. I have never observed a timeout, but I have waited several minutes for the process to complete. I ended up having to close the first browser window (stop rendering the dummy report) to get RS to return to normal operation. When I do that, the second browser window finishes rendering my Report Manager screen almost immediately. I'd appreciate any help you or anyone else can provide in resolving this.
-Rob
"Lukasz Pawlowski [MSFT]" wrote:
> Rob, did you look at DTS as an alternative solution?
> Reporting Services isn't really meant to be used in the way you're trying to
> use it. One area of concern is that you mention the server stops
> responding. It it only this particular report that doesn't get rendered, or
> does the server stop responding to any request?
> -Lukasz
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "Rob Hoffman" <Rob Hoffman@.discussions.microsoft.com> wrote in message
> news:6E2F8425-C095-4F36-9A40-C1CD6ADD73DD@.microsoft.com...
> > Hello,
> >
> > I am attempting to use a CREATE GLOBAL CUBE command when defining a data
> > set in MSRS. The intent is to use the MSRS security and scheduling
> > capabilities to dump local cube files from our data warehouse to a file
> > share, then use MSRS's delivery mechanisms to deliver the cubes to the
> > intended recipient(s). The problem (I believe) is that the command does
> > not return any data, so when rendering the report I bound the dataset to,
> > the server just stops responding. I assume it is waiting for data that
> > will never come. The report itself only contains a text box with a string
> > literal. The good news is that when I try to render the report, the local
> > cube file is generated...I just need the server to finish rendering the
> > "dummy" report. Any ideas? Thanks.
> >
> > -Rob Hoffman
>
>
I am attempting to use a CREATE GLOBAL CUBE command when defining a data set in MSRS. The intent is to use the MSRS security and scheduling capabilities to dump local cube files from our data warehouse to a file share, then use MSRS's delivery mechanisms to deliver the cubes to the intended recipient(s). The problem (I believe) is that the command does not return any data, so when rendering the report I bound the dataset to, the server just stops responding. I assume it is waiting for data that will never come. The report itself only contains a text box with a string literal. The good news is that when I try to render the report, the local cube file is generated...I just need the server to finish rendering the "dummy" report. Any ideas? Thanks.
-Rob HoffmanRob, did you look at DTS as an alternative solution?
Reporting Services isn't really meant to be used in the way you're trying to
use it. One area of concern is that you mention the server stops
responding. It it only this particular report that doesn't get rendered, or
does the server stop responding to any request?
-Lukasz
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Rob Hoffman" <Rob Hoffman@.discussions.microsoft.com> wrote in message
news:6E2F8425-C095-4F36-9A40-C1CD6ADD73DD@.microsoft.com...
> Hello,
> I am attempting to use a CREATE GLOBAL CUBE command when defining a data
> set in MSRS. The intent is to use the MSRS security and scheduling
> capabilities to dump local cube files from our data warehouse to a file
> share, then use MSRS's delivery mechanisms to deliver the cubes to the
> intended recipient(s). The problem (I believe) is that the command does
> not return any data, so when rendering the report I bound the dataset to,
> the server just stops responding. I assume it is waiting for data that
> will never come. The report itself only contains a text box with a string
> literal. The good news is that when I try to render the report, the local
> cube file is generated...I just need the server to finish rendering the
> "dummy" report. Any ideas? Thanks.
> -Rob Hoffman|||Lukasz,
Yes, but RS looked like a better choice out of the box because of its built-in security model, job scheduling, delivery mechanisms and extensibility. The requirements of the project I'm working on make it an expedient alternative to writing code that allows users to define, schedule and deliver local OLAP cubes from our data warehouse source.
To answer your question, when I view (view tab) my dummy report in Report Manager, the report never renders, and my aspnet_wp process consumes all processing power on the server. When I open a second browser window and try to access Report Manager, the page never loads. It looks like there is some sort of infinite loop occurring. I have never observed a timeout, but I have waited several minutes for the process to complete. I ended up having to close the first browser window (stop rendering the dummy report) to get RS to return to normal operation. When I do that, the second browser window finishes rendering my Report Manager screen almost immediately. I'd appreciate any help you or anyone else can provide in resolving this.
-Rob
"Lukasz Pawlowski [MSFT]" wrote:
> Rob, did you look at DTS as an alternative solution?
> Reporting Services isn't really meant to be used in the way you're trying to
> use it. One area of concern is that you mention the server stops
> responding. It it only this particular report that doesn't get rendered, or
> does the server stop responding to any request?
> -Lukasz
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "Rob Hoffman" <Rob Hoffman@.discussions.microsoft.com> wrote in message
> news:6E2F8425-C095-4F36-9A40-C1CD6ADD73DD@.microsoft.com...
> > Hello,
> >
> > I am attempting to use a CREATE GLOBAL CUBE command when defining a data
> > set in MSRS. The intent is to use the MSRS security and scheduling
> > capabilities to dump local cube files from our data warehouse to a file
> > share, then use MSRS's delivery mechanisms to deliver the cubes to the
> > intended recipient(s). The problem (I believe) is that the command does
> > not return any data, so when rendering the report I bound the dataset to,
> > the server just stops responding. I assume it is waiting for data that
> > will never come. The report itself only contains a text box with a string
> > literal. The good news is that when I try to render the report, the local
> > cube file is generated...I just need the server to finish rendering the
> > "dummy" report. Any ideas? Thanks.
> >
> > -Rob Hoffman
>
>
CREATE GLOBAL CUBE and properties
Hello
in the syntax description of the command
CREATE GLOBAL CUBE
there is a properties part at the end
Are there any examples out whta can be done with this?
I want to force the builkding of a local cube file to ignore errors due to missing attribute keys (like it is possible in the VS IDE)
Thanks in advance
Klaus Wiesel
See my reply to your previous post.
Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
Create Excel File
Is there any way we can create the Excel File on the run time through any
SQL Command or any script.
Thanks
You could by using the sp_OAxxx stored procedures but it
really wouldn't be a good idea. You can probably accomplish
what you want in a cleaner way by using DTS.
-Sue
On Thu, 15 Dec 2005 11:21:16 -0500, "Rogers"
<naissani@.hotmail.com> wrote:
>Is there any way we can create the Excel File on the run time through any
>SQL Command or any script.
>
>Thanks
>
SQL Command or any script.
Thanks
You could by using the sp_OAxxx stored procedures but it
really wouldn't be a good idea. You can probably accomplish
what you want in a cleaner way by using DTS.
-Sue
On Thu, 15 Dec 2005 11:21:16 -0500, "Rogers"
<naissani@.hotmail.com> wrote:
>Is there any way we can create the Excel File on the run time through any
>SQL Command or any script.
>
>Thanks
>
Tuesday, February 14, 2012
Create database fails
HI All,
Environment:
Windows2003,
SQL : Microsoft SQL Server 2000 - 8.00.818 (sp3+hotfix)
When I execute below simple create database command without specifying the
file name, i got below error. If i explicitly specify the file ( just mdf
file), the database is getting created.
CREATE DATABASE TESTDB
Error:
Server: Msg 5105, Level 16, State 2, Line 1
Device activation error. The physical file name '\TestDB.mdf' may be
incorrect.
Server: Msg 1802, Level 16, State 1, Line 1
CREATE DATABASE failed. Some file names listed could not be created. Check
previous errors.
The
HKEY_LOCAL_MACHINE\FTWARE\Microsoft\MSSQLServer\MS SQLServer\'DefaultData'
and
HKEY_LOCAL_MACHINE\FTWARE\Microsoft\MSSQLServer\Se tup\SQLDataRoot
are having same value ( E:\data )......
Can any one tell me how to resolve this?
Thanks,
Suchi
Are these same values listed in Enterprise Manager when you right-click your
server, select Properties, Database Settings tab and see the current values
for Default data and log directory?
Ben Nevarez
"Suchi" wrote:
> HI All,
> Environment:
> Windows2003,
> SQL : Microsoft SQL Server 2000 - 8.00.818 (sp3+hotfix)
> When I execute below simple create database command without specifying the
> file name, i got below error. If i explicitly specify the file ( just mdf
> file), the database is getting created.
>
> CREATE DATABASE TESTDB
> Error:
> Server: Msg 5105, Level 16, State 2, Line 1
> Device activation error. The physical file name '\TestDB.mdf' may be
> incorrect.
> Server: Msg 1802, Level 16, State 1, Line 1
> CREATE DATABASE failed. Some file names listed could not be created. Check
> previous errors.
>
> The
> HKEY_LOCAL_MACHINE\FTWARE\Microsoft\MSSQLServer\MS SQLServer\'DefaultData'
> and
> HKEY_LOCAL_MACHINE\FTWARE\Microsoft\MSSQLServer\Se tup\SQLDataRoot
> are having same value ( E:\data )......
> Can any one tell me how to resolve this?
> Thanks,
> Suchi
>
|||YES. Same values listed out there.
It is taking path as \test.mdf .. not starting with e:\data\testdb.mdf as
it supposed to ...
Thanks,
Suchi
"Ben Nevarez" wrote:
[vbcol=seagreen]
> Are these same values listed in Enterprise Manager when you right-click your
> server, select Properties, Database Settings tab and see the current values
> for Default data and log directory?
> Ben Nevarez
>
>
> "Suchi" wrote:
Environment:
Windows2003,
SQL : Microsoft SQL Server 2000 - 8.00.818 (sp3+hotfix)
When I execute below simple create database command without specifying the
file name, i got below error. If i explicitly specify the file ( just mdf
file), the database is getting created.
CREATE DATABASE TESTDB
Error:
Server: Msg 5105, Level 16, State 2, Line 1
Device activation error. The physical file name '\TestDB.mdf' may be
incorrect.
Server: Msg 1802, Level 16, State 1, Line 1
CREATE DATABASE failed. Some file names listed could not be created. Check
previous errors.
The
HKEY_LOCAL_MACHINE\FTWARE\Microsoft\MSSQLServer\MS SQLServer\'DefaultData'
and
HKEY_LOCAL_MACHINE\FTWARE\Microsoft\MSSQLServer\Se tup\SQLDataRoot
are having same value ( E:\data )......
Can any one tell me how to resolve this?
Thanks,
Suchi
Are these same values listed in Enterprise Manager when you right-click your
server, select Properties, Database Settings tab and see the current values
for Default data and log directory?
Ben Nevarez
"Suchi" wrote:
> HI All,
> Environment:
> Windows2003,
> SQL : Microsoft SQL Server 2000 - 8.00.818 (sp3+hotfix)
> When I execute below simple create database command without specifying the
> file name, i got below error. If i explicitly specify the file ( just mdf
> file), the database is getting created.
>
> CREATE DATABASE TESTDB
> Error:
> Server: Msg 5105, Level 16, State 2, Line 1
> Device activation error. The physical file name '\TestDB.mdf' may be
> incorrect.
> Server: Msg 1802, Level 16, State 1, Line 1
> CREATE DATABASE failed. Some file names listed could not be created. Check
> previous errors.
>
> The
> HKEY_LOCAL_MACHINE\FTWARE\Microsoft\MSSQLServer\MS SQLServer\'DefaultData'
> and
> HKEY_LOCAL_MACHINE\FTWARE\Microsoft\MSSQLServer\Se tup\SQLDataRoot
> are having same value ( E:\data )......
> Can any one tell me how to resolve this?
> Thanks,
> Suchi
>
|||YES. Same values listed out there.
It is taking path as \test.mdf .. not starting with e:\data\testdb.mdf as
it supposed to ...
Thanks,
Suchi
"Ben Nevarez" wrote:
[vbcol=seagreen]
> Are these same values listed in Enterprise Manager when you right-click your
> server, select Properties, Database Settings tab and see the current values
> for Default data and log directory?
> Ben Nevarez
>
>
> "Suchi" wrote:
Create database fails
HI All,
Environment:
Windows2003,
SQL : Microsoft SQL Server 2000 - 8.00.818 (sp3+hotfix)
When I execute below simple create database command without specifying the
file name, i got below error. If i explicitly specify the file ( just mdf
file), the database is getting created.
CREATE DATABASE TESTDB
Error:
Server: Msg 5105, Level 16, State 2, Line 1
Device activation error. The physical file name '\TestDB.mdf' may be
incorrect.
Server: Msg 1802, Level 16, State 1, Line 1
CREATE DATABASE failed. Some file names listed could not be created. Check
previous errors.
The
HKEY_LOCAL_MACHINE\FTWARE\Microsoft\MSSQLServer\MSSQLServer\'DefaultData'
and
HKEY_LOCAL_MACHINE\FTWARE\Microsoft\MSSQLServer\Setup\SQLDataRoot
are having same value ( E:\data )......
Can any one tell me how to resolve this?
Thanks,
SuchiAre these same values listed in Enterprise Manager when you right-click your
server, select Properties, Database Settings tab and see the current values
for Default data and log directory?
Ben Nevarez
"Suchi" wrote:
> HI All,
> Environment:
> Windows2003,
> SQL : Microsoft SQL Server 2000 - 8.00.818 (sp3+hotfix)
> When I execute below simple create database command without specifying the
> file name, i got below error. If i explicitly specify the file ( just mdf
> file), the database is getting created.
>
> CREATE DATABASE TESTDB
> Error:
> Server: Msg 5105, Level 16, State 2, Line 1
> Device activation error. The physical file name '\TestDB.mdf' may be
> incorrect.
> Server: Msg 1802, Level 16, State 1, Line 1
> CREATE DATABASE failed. Some file names listed could not be created. Check
> previous errors.
>
> The
> HKEY_LOCAL_MACHINE\FTWARE\Microsoft\MSSQLServer\MSSQLServer\'DefaultData'
> and
> HKEY_LOCAL_MACHINE\FTWARE\Microsoft\MSSQLServer\Setup\SQLDataRoot
> are having same value ( E:\data )......
> Can any one tell me how to resolve this?
> Thanks,
> Suchi
>|||YES. Same values listed out there.
It is taking path as \test.mdf .. not starting with e:\data\testdb.mdf as
it supposed to ...
Thanks,
Suchi
"Ben Nevarez" wrote:
> Are these same values listed in Enterprise Manager when you right-click your
> server, select Properties, Database Settings tab and see the current values
> for Default data and log directory?
> Ben Nevarez
>
>
> "Suchi" wrote:
> > HI All,
> >
> > Environment:
> >
> > Windows2003,
> >
> > SQL : Microsoft SQL Server 2000 - 8.00.818 (sp3+hotfix)
> >
> > When I execute below simple create database command without specifying the
> > file name, i got below error. If i explicitly specify the file ( just mdf
> > file), the database is getting created.
> >
> >
> > CREATE DATABASE TESTDB
> >
> > Error:
> >
> > Server: Msg 5105, Level 16, State 2, Line 1
> > Device activation error. The physical file name '\TestDB.mdf' may be
> > incorrect.
> > Server: Msg 1802, Level 16, State 1, Line 1
> > CREATE DATABASE failed. Some file names listed could not be created. Check
> > previous errors.
> >
> >
> > The
> > HKEY_LOCAL_MACHINE\FTWARE\Microsoft\MSSQLServer\MSSQLServer\'DefaultData'
> > and
> > HKEY_LOCAL_MACHINE\FTWARE\Microsoft\MSSQLServer\Setup\SQLDataRoot
> > are having same value ( E:\data )......
> >
> > Can any one tell me how to resolve this?
> >
> > Thanks,
> > Suchi
> >
Environment:
Windows2003,
SQL : Microsoft SQL Server 2000 - 8.00.818 (sp3+hotfix)
When I execute below simple create database command without specifying the
file name, i got below error. If i explicitly specify the file ( just mdf
file), the database is getting created.
CREATE DATABASE TESTDB
Error:
Server: Msg 5105, Level 16, State 2, Line 1
Device activation error. The physical file name '\TestDB.mdf' may be
incorrect.
Server: Msg 1802, Level 16, State 1, Line 1
CREATE DATABASE failed. Some file names listed could not be created. Check
previous errors.
The
HKEY_LOCAL_MACHINE\FTWARE\Microsoft\MSSQLServer\MSSQLServer\'DefaultData'
and
HKEY_LOCAL_MACHINE\FTWARE\Microsoft\MSSQLServer\Setup\SQLDataRoot
are having same value ( E:\data )......
Can any one tell me how to resolve this?
Thanks,
SuchiAre these same values listed in Enterprise Manager when you right-click your
server, select Properties, Database Settings tab and see the current values
for Default data and log directory?
Ben Nevarez
"Suchi" wrote:
> HI All,
> Environment:
> Windows2003,
> SQL : Microsoft SQL Server 2000 - 8.00.818 (sp3+hotfix)
> When I execute below simple create database command without specifying the
> file name, i got below error. If i explicitly specify the file ( just mdf
> file), the database is getting created.
>
> CREATE DATABASE TESTDB
> Error:
> Server: Msg 5105, Level 16, State 2, Line 1
> Device activation error. The physical file name '\TestDB.mdf' may be
> incorrect.
> Server: Msg 1802, Level 16, State 1, Line 1
> CREATE DATABASE failed. Some file names listed could not be created. Check
> previous errors.
>
> The
> HKEY_LOCAL_MACHINE\FTWARE\Microsoft\MSSQLServer\MSSQLServer\'DefaultData'
> and
> HKEY_LOCAL_MACHINE\FTWARE\Microsoft\MSSQLServer\Setup\SQLDataRoot
> are having same value ( E:\data )......
> Can any one tell me how to resolve this?
> Thanks,
> Suchi
>|||YES. Same values listed out there.
It is taking path as \test.mdf .. not starting with e:\data\testdb.mdf as
it supposed to ...
Thanks,
Suchi
"Ben Nevarez" wrote:
> Are these same values listed in Enterprise Manager when you right-click your
> server, select Properties, Database Settings tab and see the current values
> for Default data and log directory?
> Ben Nevarez
>
>
> "Suchi" wrote:
> > HI All,
> >
> > Environment:
> >
> > Windows2003,
> >
> > SQL : Microsoft SQL Server 2000 - 8.00.818 (sp3+hotfix)
> >
> > When I execute below simple create database command without specifying the
> > file name, i got below error. If i explicitly specify the file ( just mdf
> > file), the database is getting created.
> >
> >
> > CREATE DATABASE TESTDB
> >
> > Error:
> >
> > Server: Msg 5105, Level 16, State 2, Line 1
> > Device activation error. The physical file name '\TestDB.mdf' may be
> > incorrect.
> > Server: Msg 1802, Level 16, State 1, Line 1
> > CREATE DATABASE failed. Some file names listed could not be created. Check
> > previous errors.
> >
> >
> > The
> > HKEY_LOCAL_MACHINE\FTWARE\Microsoft\MSSQLServer\MSSQLServer\'DefaultData'
> > and
> > HKEY_LOCAL_MACHINE\FTWARE\Microsoft\MSSQLServer\Setup\SQLDataRoot
> > are having same value ( E:\data )......
> >
> > Can any one tell me how to resolve this?
> >
> > Thanks,
> > Suchi
> >
create database command in trigger
Hi,
As part of setting up automated replication between two servers, I need an insert trigger on a table in a database on server 1 to run a 'create database xxx' command on server 2. Once I've got that I'm sorted.
I tried using linked servers but didn't get anywhere. Finally, I tried creating a trigger on server 1 which ran a dts package (the dts package contained the SQL to create the database on server 2). The dts pacakge ran on its own (I ran it using dtsrun), but not as part of the trigger.
I know that SQL server doesn't support 'create database' commands in triggers, but I would have thought the dts approach would have got around that. Any suggestions? Here's my trigger
CREATE TRIGGER dblist_trigger
ON dblist
FOR INSERT
AS
commit work
EXEC master..xp_cmdshell 'dtsrun /s mbuksqltst03 /u sa /s /n createdb'
Thanks,
IanPlease explain something more about your process|||Hi,
The application I'm working with uses SQL Server and creates databases as part of its operation. So in essence, I'm trying to replicate an entire server rather than just a particular database. I have a script which will create a full set of replication objects for a given database. The problem I have is that when a database is created on the main server, I can't automatically create a blank database on the replicated server to run my replication objects script against.
I've got the replicated server to maintain a list of databases on the main server (a table called dblist - updated by a trigger on the main server). What I was trying to do was create some sort of trigger which will run a create database command when the dblist table on the replicated server has a row inserted in (indicating a new database has been created on the main server). This syntax works, but not when I use it in a trigger
declare @.sqltxt nvarchar (2000),
@.maxid int,
@.name varchar (256)
set @.maxid=(select max(id) from dblist)
set @.name=(select name from dblist where id=@.maxid)
set @.sqltxt=(select 'create database '+ @.name)
EXEC sp_executesql @.sqlTxt
I have even tried putting this sytax in a separate stored procedure and as a T-SQL object in a dts package. But I still can't get it triggered automatically.
Of course, if there is a more elegant way of setting up the replication, I'm open to suggestions.
I hope this is some use.
Ian
As part of setting up automated replication between two servers, I need an insert trigger on a table in a database on server 1 to run a 'create database xxx' command on server 2. Once I've got that I'm sorted.
I tried using linked servers but didn't get anywhere. Finally, I tried creating a trigger on server 1 which ran a dts package (the dts package contained the SQL to create the database on server 2). The dts pacakge ran on its own (I ran it using dtsrun), but not as part of the trigger.
I know that SQL server doesn't support 'create database' commands in triggers, but I would have thought the dts approach would have got around that. Any suggestions? Here's my trigger
CREATE TRIGGER dblist_trigger
ON dblist
FOR INSERT
AS
commit work
EXEC master..xp_cmdshell 'dtsrun /s mbuksqltst03 /u sa /s /n createdb'
Thanks,
IanPlease explain something more about your process|||Hi,
The application I'm working with uses SQL Server and creates databases as part of its operation. So in essence, I'm trying to replicate an entire server rather than just a particular database. I have a script which will create a full set of replication objects for a given database. The problem I have is that when a database is created on the main server, I can't automatically create a blank database on the replicated server to run my replication objects script against.
I've got the replicated server to maintain a list of databases on the main server (a table called dblist - updated by a trigger on the main server). What I was trying to do was create some sort of trigger which will run a create database command when the dblist table on the replicated server has a row inserted in (indicating a new database has been created on the main server). This syntax works, but not when I use it in a trigger
declare @.sqltxt nvarchar (2000),
@.maxid int,
@.name varchar (256)
set @.maxid=(select max(id) from dblist)
set @.name=(select name from dblist where id=@.maxid)
set @.sqltxt=(select 'create database '+ @.name)
EXEC sp_executesql @.sqlTxt
I have even tried putting this sytax in a separate stored procedure and as a T-SQL object in a dts package. But I still can't get it triggered automatically.
Of course, if there is a more elegant way of setting up the replication, I'm open to suggestions.
I hope this is some use.
Ian
Subscribe to:
Posts (Atom)