Hi Guys,
I am new in analysis service. I just installed analysis service in my
personal computer that has a sql server enterprise installed. When I opened
analysis manager and try to open the server in the left pane, I got error
that no register sql server or it is not in the olap group(roughly, I cannot
remember clearly), then server icon because red(closed). it seems like I
couldn't connect to database server. But it is same server and of cause I
have already register this server. What is wrong with this? Thanks.
it would help if 'clearly' and not 'roughly' took the time to post the full
error and in return we will 'clearly' answer your question!
It sounds to me the user account you are logged on as isn't in the OlapAdmin
group. Add your logged on account to this group and it should do the trick.
Immy
"Iter" <Iter@.discussions.microsoft.com> wrote in message
news:75B81FA5-1CEC-4815-A597-95BBCA829BDB@.microsoft.com...
> Hi Guys,
> I am new in analysis service. I just installed analysis service in my
> personal computer that has a sql server enterprise installed. When I
> opened
> analysis manager and try to open the server in the left pane, I got error
> that no register sql server or it is not in the olap group(roughly, I
> cannot
> remember clearly), then server icon because red(closed). it seems like I
> couldn't connect to database server. But it is same server and of cause I
> have already register this server. What is wrong with this? Thanks.
>
|||You will get this behavior if you are trying to run an earlier version of
Analysis Services 2000 on Win XP Pro. If this is the case, you need to
upgrade both SQL Server 2000 and As 2000 to SP4. Search the MSKB for
details.
John G.
"Iter" <Iter@.discussions.microsoft.com> wrote in message
news:75B81FA5-1CEC-4815-A597-95BBCA829BDB@.microsoft.com...
> Hi Guys,
> I am new in analysis service. I just installed analysis service in my
> personal computer that has a sql server enterprise installed. When I
> opened
> analysis manager and try to open the server in the left pane, I got error
> that no register sql server or it is not in the olap group(roughly, I
> cannot
> remember clearly), then server icon because red(closed). it seems like I
> couldn't connect to database server. But it is same server and of cause I
> have already register this server. What is wrong with this? Thanks.
>
Friday, March 30, 2012
Regarding indexes (I think)
I have an issue where i can do the following
Select count(*) from table and it returns 181,000
Select count(*) from table where ncolumn is not null and it returns 174,000
Select count(*) from table where ncolumn is null and it returns 153,000
Now, those numbers should at up to the count of the top one,
I've ran dbcc checkdb, and it returns no errors. I've also tried dbcc
dbreindex with the same problem.
Anyone have any suggestions?Try,
DBCC UPDATEUSAGE ('db_name', 'table_name')
go
select count(*), count(case when ncolumn is not null then 1 end),
count(ncolumn is null then 1 end)
from table
go
AMB
"Gary" wrote:
> I have an issue where i can do the following
> Select count(*) from table and it returns 181,000
> Select count(*) from table where ncolumn is not null and it returns 174,00
0
> Select count(*) from table where ncolumn is null and it returns 153,000
> Now, those numbers should at up to the count of the top one,
> I've ran dbcc checkdb, and it returns no errors. I've also tried dbcc
> dbreindex with the same problem.
> Anyone have any suggestions?
>
>|||Correction,
DBCC UPDATEUSAGE ('db_name', 'table_name')
go
select count(*), count(case when ncolumn is not null then 1 end),
count(case when ncolumn is null then 1 end)
from table
go
AMB
"Alejandro Mesa" wrote:
> Try,
> DBCC UPDATEUSAGE ('db_name', 'table_name')
> go
> select count(*), count(case when ncolumn is not null then 1 end),
> count(ncolumn is null then 1 end)
> from table
> go
>
> AMB
> "Gary" wrote:
>|||Are there inserts into the table between each select?
"Gary" <clgary@.yahoo.com> wrote in message
news:unQeCtRGFHA.1188@.tk2msftngp13.phx.gbl...
> I have an issue where i can do the following
> Select count(*) from table and it returns 181,000
> Select count(*) from table where ncolumn is not null and it returns
174,000
> Select count(*) from table where ncolumn is null and it returns 153,000
> Now, those numbers should at up to the count of the top one,
> I've ran dbcc checkdb, and it returns no errors. I've also tried dbcc
> dbreindex with the same problem.
> Anyone have any suggestions?
>|||Thanks for the help, it sort of worked
Select count(*) from table and it returns 181,000
Select count(*) from table where ncolumn is not null and it returns 174,000
Select count(*) from table where ncolumn is null and it returns 153,000
Same results there, however,
select count(*), count(case when ncolumn is not null then 1 end),
count(ncolumn is null then 1 end)
from table
returns
181,000 159,000 22,000
which is correct,.but I'm still not sure whats going on there.
Another poster I saw asked if there were inserts happening during the
selects, and its not.
So, I'm still baffled. Something else to note, even if i run them
individually, instead of all 3 as a batch in QA, it still returns the wrong
count.
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:61BE7216-D2F7-435D-B1E4-656E4D683C5E@.microsoft.com...
> Try,
> DBCC UPDATEUSAGE ('db_name', 'table_name')
> go
> select count(*), count(case when ncolumn is not null then 1 end),
> count(ncolumn is null then 1 end)
> from table
> go
>
> AMB
> "Gary" wrote:
>
174,000|||If you are running parallel queries:
http://support.microsoft.com/kb/814509/en-us
http://support.microsoft.com/default.aspx/kb/838166 (for getting the 878
build).
S. L.
"Gary" <clgary@.yahoo.com> wrote in message
news:unQeCtRGFHA.1188@.tk2msftngp13.phx.gbl...
>I have an issue where i can do the following
> Select count(*) from table and it returns 181,000
> Select count(*) from table where ncolumn is not null and it returns
> 174,000
> Select count(*) from table where ncolumn is null and it returns 153,000
> Now, those numbers should at up to the count of the top one,
> I've ran dbcc checkdb, and it returns no errors. I've also tried dbcc
> dbreindex with the same problem.
> Anyone have any suggestions?
>|||Perhaps uncommitted inserts or updates are being held open by other
processes and your current transaction isolation level allows your query to
include "dirty reads". Sometimes a large number of updates (100,000s) can
take 1/2 hour or more to rollback if a batch transaction fails. Try running
the following, it will lock any records as it reads and will not include
"dirty reads".
Run sp_lock to see what other processes may have records locked. Also, if
the queries seems to take longer than usual to run using this locking
method, then you can use sp_who2 to determine if your SPID is blocked by
another process.
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
Select count(*) from table
Select count(*) from table where ncolumn is not null
Select count(*) from table where ncolumn is null
"Gary" <clgary@.yahoo.com> wrote in message
news:uDpR8ISGFHA.4004@.tk2msftngp13.phx.gbl...
> Thanks for the help, it sort of worked
> Select count(*) from table and it returns 181,000
> Select count(*) from table where ncolumn is not null and it returns
174,000
> Select count(*) from table where ncolumn is null and it returns 153,000
> Same results there, however,
> select count(*), count(case when ncolumn is not null then 1 end),
> count(ncolumn is null then 1 end)
> from table
> returns
> 181,000 159,000 22,000
> which is correct,.but I'm still not sure whats going on there.
> Another poster I saw asked if there were inserts happening during the
> selects, and its not.
> So, I'm still baffled. Something else to note, even if i run them
> individually, instead of all 3 as a batch in QA, it still returns the
wrong
> count.
> "Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in
message
> news:61BE7216-D2F7-435D-B1E4-656E4D683C5E@.microsoft.com...
> 174,000
153,000
>|||Thanks,
That seems to do the trick
"Sylvain Lafontaine" <sylvain aei ca (fill the blanks, no spam please)>
wrote in message news:OOqE3YSGFHA.2296@.TK2MSFTNGP15.phx.gbl...
> If you are running parallel queries:
> http://support.microsoft.com/kb/814509/en-us
> http://support.microsoft.com/default.aspx/kb/838166 (for getting the 878
> build).
> S. L.
> "Gary" <clgary@.yahoo.com> wrote in message
> news:unQeCtRGFHA.1188@.tk2msftngp13.phx.gbl...
>sql
Select count(*) from table and it returns 181,000
Select count(*) from table where ncolumn is not null and it returns 174,000
Select count(*) from table where ncolumn is null and it returns 153,000
Now, those numbers should at up to the count of the top one,
I've ran dbcc checkdb, and it returns no errors. I've also tried dbcc
dbreindex with the same problem.
Anyone have any suggestions?Try,
DBCC UPDATEUSAGE ('db_name', 'table_name')
go
select count(*), count(case when ncolumn is not null then 1 end),
count(ncolumn is null then 1 end)
from table
go
AMB
"Gary" wrote:
> I have an issue where i can do the following
> Select count(*) from table and it returns 181,000
> Select count(*) from table where ncolumn is not null and it returns 174,00
0
> Select count(*) from table where ncolumn is null and it returns 153,000
> Now, those numbers should at up to the count of the top one,
> I've ran dbcc checkdb, and it returns no errors. I've also tried dbcc
> dbreindex with the same problem.
> Anyone have any suggestions?
>
>|||Correction,
DBCC UPDATEUSAGE ('db_name', 'table_name')
go
select count(*), count(case when ncolumn is not null then 1 end),
count(case when ncolumn is null then 1 end)
from table
go
AMB
"Alejandro Mesa" wrote:
> Try,
> DBCC UPDATEUSAGE ('db_name', 'table_name')
> go
> select count(*), count(case when ncolumn is not null then 1 end),
> count(ncolumn is null then 1 end)
> from table
> go
>
> AMB
> "Gary" wrote:
>|||Are there inserts into the table between each select?
"Gary" <clgary@.yahoo.com> wrote in message
news:unQeCtRGFHA.1188@.tk2msftngp13.phx.gbl...
> I have an issue where i can do the following
> Select count(*) from table and it returns 181,000
> Select count(*) from table where ncolumn is not null and it returns
174,000
> Select count(*) from table where ncolumn is null and it returns 153,000
> Now, those numbers should at up to the count of the top one,
> I've ran dbcc checkdb, and it returns no errors. I've also tried dbcc
> dbreindex with the same problem.
> Anyone have any suggestions?
>|||Thanks for the help, it sort of worked
Select count(*) from table and it returns 181,000
Select count(*) from table where ncolumn is not null and it returns 174,000
Select count(*) from table where ncolumn is null and it returns 153,000
Same results there, however,
select count(*), count(case when ncolumn is not null then 1 end),
count(ncolumn is null then 1 end)
from table
returns
181,000 159,000 22,000
which is correct,.but I'm still not sure whats going on there.
Another poster I saw asked if there were inserts happening during the
selects, and its not.
So, I'm still baffled. Something else to note, even if i run them
individually, instead of all 3 as a batch in QA, it still returns the wrong
count.
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:61BE7216-D2F7-435D-B1E4-656E4D683C5E@.microsoft.com...
> Try,
> DBCC UPDATEUSAGE ('db_name', 'table_name')
> go
> select count(*), count(case when ncolumn is not null then 1 end),
> count(ncolumn is null then 1 end)
> from table
> go
>
> AMB
> "Gary" wrote:
>
174,000|||If you are running parallel queries:
http://support.microsoft.com/kb/814509/en-us
http://support.microsoft.com/default.aspx/kb/838166 (for getting the 878
build).
S. L.
"Gary" <clgary@.yahoo.com> wrote in message
news:unQeCtRGFHA.1188@.tk2msftngp13.phx.gbl...
>I have an issue where i can do the following
> Select count(*) from table and it returns 181,000
> Select count(*) from table where ncolumn is not null and it returns
> 174,000
> Select count(*) from table where ncolumn is null and it returns 153,000
> Now, those numbers should at up to the count of the top one,
> I've ran dbcc checkdb, and it returns no errors. I've also tried dbcc
> dbreindex with the same problem.
> Anyone have any suggestions?
>|||Perhaps uncommitted inserts or updates are being held open by other
processes and your current transaction isolation level allows your query to
include "dirty reads". Sometimes a large number of updates (100,000s) can
take 1/2 hour or more to rollback if a batch transaction fails. Try running
the following, it will lock any records as it reads and will not include
"dirty reads".
Run sp_lock to see what other processes may have records locked. Also, if
the queries seems to take longer than usual to run using this locking
method, then you can use sp_who2 to determine if your SPID is blocked by
another process.
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
Select count(*) from table
Select count(*) from table where ncolumn is not null
Select count(*) from table where ncolumn is null
"Gary" <clgary@.yahoo.com> wrote in message
news:uDpR8ISGFHA.4004@.tk2msftngp13.phx.gbl...
> Thanks for the help, it sort of worked
> Select count(*) from table and it returns 181,000
> Select count(*) from table where ncolumn is not null and it returns
174,000
> Select count(*) from table where ncolumn is null and it returns 153,000
> Same results there, however,
> select count(*), count(case when ncolumn is not null then 1 end),
> count(ncolumn is null then 1 end)
> from table
> returns
> 181,000 159,000 22,000
> which is correct,.but I'm still not sure whats going on there.
> Another poster I saw asked if there were inserts happening during the
> selects, and its not.
> So, I'm still baffled. Something else to note, even if i run them
> individually, instead of all 3 as a batch in QA, it still returns the
wrong
> count.
> "Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in
message
> news:61BE7216-D2F7-435D-B1E4-656E4D683C5E@.microsoft.com...
> 174,000
153,000
>|||Thanks,
That seems to do the trick
"Sylvain Lafontaine" <sylvain aei ca (fill the blanks, no spam please)>
wrote in message news:OOqE3YSGFHA.2296@.TK2MSFTNGP15.phx.gbl...
> If you are running parallel queries:
> http://support.microsoft.com/kb/814509/en-us
> http://support.microsoft.com/default.aspx/kb/838166 (for getting the 878
> build).
> S. L.
> "Gary" <clgary@.yahoo.com> wrote in message
> news:unQeCtRGFHA.1188@.tk2msftngp13.phx.gbl...
>sql
Regarding how to compress the data file
Hi Guys,
I have question Regarding how to compress the data file. IN my company, the
data file is getting bigger and bigger. Is that a way to compress the
database file to make it smaller so that we can save some disk space. Thanks.
Iter,
First: Do NOT try to used compressed drives.
http://support.microsoft.com/kb/231347/EN-US/
Use DBCC SHOWCONTIG to determine if your dataspace is badly fragmented or,
in SQL 2005, examine the sys.dm_db_index_physical_stats dynamic management
view. If that is a problem, then reorganizing the database space will help
some. Use:
DBCC DBREINDEX or (SQL 2005) ALTER INDEX ... REBUILD ...
However, the biggest problem is usually retaining more data online than is
actually needed. That would require a policy decision on data retention and
then some development to either archive or delete the expired data. (Of
course, if you keep your archive online, then you have not saved a lot of
disk space, but you may have improved database performance.)
RLF
"Iter" <Iter@.discussions.microsoft.com> wrote in message
news:7786B9FF-FE35-44E6-B92F-8158977FFFFB@.microsoft.com...
> Hi Guys,
> I have question Regarding how to compress the data file. IN my company,
> the
> data file is getting bigger and bigger. Is that a way to compress the
> database file to make it smaller so that we can save some disk space.
> Thanks.
>
|||Hi
"Iter" wrote:
> Hi Guys,
> I have question Regarding how to compress the data file. IN my company, the
> data file is getting bigger and bigger. Is that a way to compress the
> database file to make it smaller so that we can save some disk space. Thanks.
>
I don't think having data files on compressed drives is officially
supported, and compressing the datafile will have a sever impact on
performance.
If you are actually wanting to shrink the database file then, this is not
usually recommended as it can effect performance (see
http://www.karaszi.com/SQLServer/info_dont_shrink.asp) unless you have an
unusually high amount of free space in the database that you are unlikely to
use (such as the growth rate is a percentage rather than a fixed amount).
Look at sp_spaceused to see the unused space. DBCC SHRINKFILE can be used to
shrink the file.
John
sql
I have question Regarding how to compress the data file. IN my company, the
data file is getting bigger and bigger. Is that a way to compress the
database file to make it smaller so that we can save some disk space. Thanks.
Iter,
First: Do NOT try to used compressed drives.
http://support.microsoft.com/kb/231347/EN-US/
Use DBCC SHOWCONTIG to determine if your dataspace is badly fragmented or,
in SQL 2005, examine the sys.dm_db_index_physical_stats dynamic management
view. If that is a problem, then reorganizing the database space will help
some. Use:
DBCC DBREINDEX or (SQL 2005) ALTER INDEX ... REBUILD ...
However, the biggest problem is usually retaining more data online than is
actually needed. That would require a policy decision on data retention and
then some development to either archive or delete the expired data. (Of
course, if you keep your archive online, then you have not saved a lot of
disk space, but you may have improved database performance.)
RLF
"Iter" <Iter@.discussions.microsoft.com> wrote in message
news:7786B9FF-FE35-44E6-B92F-8158977FFFFB@.microsoft.com...
> Hi Guys,
> I have question Regarding how to compress the data file. IN my company,
> the
> data file is getting bigger and bigger. Is that a way to compress the
> database file to make it smaller so that we can save some disk space.
> Thanks.
>
|||Hi
"Iter" wrote:
> Hi Guys,
> I have question Regarding how to compress the data file. IN my company, the
> data file is getting bigger and bigger. Is that a way to compress the
> database file to make it smaller so that we can save some disk space. Thanks.
>
I don't think having data files on compressed drives is officially
supported, and compressing the datafile will have a sever impact on
performance.
If you are actually wanting to shrink the database file then, this is not
usually recommended as it can effect performance (see
http://www.karaszi.com/SQLServer/info_dont_shrink.asp) unless you have an
unusually high amount of free space in the database that you are unlikely to
use (such as the growth rate is a percentage rather than a fixed amount).
Look at sp_spaceused to see the unused space. DBCC SHRINKFILE can be used to
shrink the file.
John
sql
Regarding how to compress the data file
Hi Guys,
I have question Regarding how to compress the data file. IN my company, the
data file is getting bigger and bigger. Is that a way to compress the
database file to make it smaller so that we can save some disk space. Thanks
.Iter,
First: Do NOT try to used compressed drives.
http://support.microsoft.com/kb/231347/EN-US/
Use DBCC SHOWCONTIG to determine if your dataspace is badly fragmented or,
in SQL 2005, examine the sys.dm_db_index_physical_stats dynamic management
view. If that is a problem, then reorganizing the database space will help
some. Use:
DBCC DBREINDEX or (SQL 2005) ALTER INDEX ... REBUILD ...
However, the biggest problem is usually retaining more data online than is
actually needed. That would require a policy decision on data retention and
then some development to either archive or delete the expired data. (Of
course, if you keep your archive online, then you have not saved a lot of
disk space, but you may have improved database performance.)
RLF
"Iter" <Iter@.discussions.microsoft.com> wrote in message
news:7786B9FF-FE35-44E6-B92F-8158977FFFFB@.microsoft.com...
> Hi Guys,
> I have question Regarding how to compress the data file. IN my company,
> the
> data file is getting bigger and bigger. Is that a way to compress the
> database file to make it smaller so that we can save some disk space.
> Thanks.
>|||Hi
"Iter" wrote:
> Hi Guys,
> I have question Regarding how to compress the data file. IN my company, th
e
> data file is getting bigger and bigger. Is that a way to compress the
> database file to make it smaller so that we can save some disk space. Than
ks.
>
I don't think having data files on compressed drives is officially
supported, and compressing the datafile will have a sever impact on
performance.
If you are actually wanting to shrink the database file then, this is not
usually recommended as it can effect performance (see
http://www.karaszi.com/SQLServer/info_dont_shrink.asp) unless you have an
unusually high amount of free space in the database that you are unlikely to
use (such as the growth rate is a percentage rather than a fixed amount).
Look at sp_spaceused to see the unused space. DBCC SHRINKFILE can be used to
shrink the file.
John
I have question Regarding how to compress the data file. IN my company, the
data file is getting bigger and bigger. Is that a way to compress the
database file to make it smaller so that we can save some disk space. Thanks
.Iter,
First: Do NOT try to used compressed drives.
http://support.microsoft.com/kb/231347/EN-US/
Use DBCC SHOWCONTIG to determine if your dataspace is badly fragmented or,
in SQL 2005, examine the sys.dm_db_index_physical_stats dynamic management
view. If that is a problem, then reorganizing the database space will help
some. Use:
DBCC DBREINDEX or (SQL 2005) ALTER INDEX ... REBUILD ...
However, the biggest problem is usually retaining more data online than is
actually needed. That would require a policy decision on data retention and
then some development to either archive or delete the expired data. (Of
course, if you keep your archive online, then you have not saved a lot of
disk space, but you may have improved database performance.)
RLF
"Iter" <Iter@.discussions.microsoft.com> wrote in message
news:7786B9FF-FE35-44E6-B92F-8158977FFFFB@.microsoft.com...
> Hi Guys,
> I have question Regarding how to compress the data file. IN my company,
> the
> data file is getting bigger and bigger. Is that a way to compress the
> database file to make it smaller so that we can save some disk space.
> Thanks.
>|||Hi
"Iter" wrote:
> Hi Guys,
> I have question Regarding how to compress the data file. IN my company, th
e
> data file is getting bigger and bigger. Is that a way to compress the
> database file to make it smaller so that we can save some disk space. Than
ks.
>
I don't think having data files on compressed drives is officially
supported, and compressing the datafile will have a sever impact on
performance.
If you are actually wanting to shrink the database file then, this is not
usually recommended as it can effect performance (see
http://www.karaszi.com/SQLServer/info_dont_shrink.asp) unless you have an
unusually high amount of free space in the database that you are unlikely to
use (such as the growth rate is a percentage rather than a fixed amount).
Look at sp_spaceused to see the unused space. DBCC SHRINKFILE can be used to
shrink the file.
John
Regarding how to compress the data file
Hi Guys,
I have question Regarding how to compress the data file. IN my company, the
data file is getting bigger and bigger. Is that a way to compress the
database file to make it smaller so that we can save some disk space. Thanks.Iter,
First: Do NOT try to used compressed drives.
http://support.microsoft.com/kb/231347/EN-US/
Use DBCC SHOWCONTIG to determine if your dataspace is badly fragmented or,
in SQL 2005, examine the sys.dm_db_index_physical_stats dynamic management
view. If that is a problem, then reorganizing the database space will help
some. Use:
DBCC DBREINDEX or (SQL 2005) ALTER INDEX ... REBUILD ...
However, the biggest problem is usually retaining more data online than is
actually needed. That would require a policy decision on data retention and
then some development to either archive or delete the expired data. (Of
course, if you keep your archive online, then you have not saved a lot of
disk space, but you may have improved database performance.)
RLF
"Iter" <Iter@.discussions.microsoft.com> wrote in message
news:7786B9FF-FE35-44E6-B92F-8158977FFFFB@.microsoft.com...
> Hi Guys,
> I have question Regarding how to compress the data file. IN my company,
> the
> data file is getting bigger and bigger. Is that a way to compress the
> database file to make it smaller so that we can save some disk space.
> Thanks.
>|||Hi
"Iter" wrote:
> Hi Guys,
> I have question Regarding how to compress the data file. IN my company, the
> data file is getting bigger and bigger. Is that a way to compress the
> database file to make it smaller so that we can save some disk space. Thanks.
>
I don't think having data files on compressed drives is officially
supported, and compressing the datafile will have a sever impact on
performance.
If you are actually wanting to shrink the database file then, this is not
usually recommended as it can effect performance (see
http://www.karaszi.com/SQLServer/info_dont_shrink.asp) unless you have an
unusually high amount of free space in the database that you are unlikely to
use (such as the growth rate is a percentage rather than a fixed amount).
Look at sp_spaceused to see the unused space. DBCC SHRINKFILE can be used to
shrink the file.
John
I have question Regarding how to compress the data file. IN my company, the
data file is getting bigger and bigger. Is that a way to compress the
database file to make it smaller so that we can save some disk space. Thanks.Iter,
First: Do NOT try to used compressed drives.
http://support.microsoft.com/kb/231347/EN-US/
Use DBCC SHOWCONTIG to determine if your dataspace is badly fragmented or,
in SQL 2005, examine the sys.dm_db_index_physical_stats dynamic management
view. If that is a problem, then reorganizing the database space will help
some. Use:
DBCC DBREINDEX or (SQL 2005) ALTER INDEX ... REBUILD ...
However, the biggest problem is usually retaining more data online than is
actually needed. That would require a policy decision on data retention and
then some development to either archive or delete the expired data. (Of
course, if you keep your archive online, then you have not saved a lot of
disk space, but you may have improved database performance.)
RLF
"Iter" <Iter@.discussions.microsoft.com> wrote in message
news:7786B9FF-FE35-44E6-B92F-8158977FFFFB@.microsoft.com...
> Hi Guys,
> I have question Regarding how to compress the data file. IN my company,
> the
> data file is getting bigger and bigger. Is that a way to compress the
> database file to make it smaller so that we can save some disk space.
> Thanks.
>|||Hi
"Iter" wrote:
> Hi Guys,
> I have question Regarding how to compress the data file. IN my company, the
> data file is getting bigger and bigger. Is that a way to compress the
> database file to make it smaller so that we can save some disk space. Thanks.
>
I don't think having data files on compressed drives is officially
supported, and compressing the datafile will have a sever impact on
performance.
If you are actually wanting to shrink the database file then, this is not
usually recommended as it can effect performance (see
http://www.karaszi.com/SQLServer/info_dont_shrink.asp) unless you have an
unusually high amount of free space in the database that you are unlikely to
use (such as the growth rate is a percentage rather than a fixed amount).
Look at sp_spaceused to see the unused space. DBCC SHRINKFILE can be used to
shrink the file.
John
Regarding History tables in Replication...
Hi,
How frequently will the history tables get deleted in Merge Replication and Transactional Replication with updatable subscriptions?
When transactional replication with updatable subscriptions is running in the continuous mode, will the history tables get deleted frequently?
Parameters :
Version : SQL Server 2005 SP1
Mode : Continuous running mode
Subscription type : Pull (for transactional)
Regards,
Swapna.B.
There's a history cleanup job that runs and will clean up rows based on your history retention.Regarding Full-Text search
Hello,
we have full-text search service running under local system account and we
removed Builtin/Administrators
from our logins and now our full-text catalogs are not getting populated and
now the only resolution i see is
adding NT Authority/System as a login and granting sysadmin privileges .are
there any pros and cons in doing this ..in terms of security..
Thanks In Advance!
No, basically the reason dba's remove the builtin\admin account is to ensure
that NT admin's are automatically system admins on the SQL Server box. NT
Authority/System merely gives the system account rights to SQL Server which
MSSearch requires to contact SQL Server and vice versa. With NT
Authority/System the NT admin's can no longer automatically administer your
SQL Server.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"msqldba m via droptable.com" <forum@.droptable.com> wrote in message
news:5111AFEC65E20@.droptable.com...
> Hello,
> we have full-text search service running under local system account and we
> removed Builtin/Administrators
> from our logins and now our full-text catalogs are not getting populated
and
> now the only resolution i see is
> adding NT Authority/System as a login and granting sysadmin privileges
..are
> there any pros and cons in doing this ..in terms of security..
>
> Thanks In Advance!
|||Hilary,
So there are no problems in making NT Authority/System a sysadmin(SA) in sql
server
Thanks,
Hilary Cotter wrote:[vbcol=seagreen]
>No, basically the reason dba's remove the builtin\admin account is to ensure
>that NT admin's are automatically system admins on the SQL Server box. NT
>Authority/System merely gives the system account rights to SQL Server which
>MSSearch requires to contact SQL Server and vice versa. With NT
>Authority/System the NT admin's can no longer automatically administer your
>SQL Server.
>[quoted text clipped - 6 lines]
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums...earch/200507/1
|||msqldba,
No there is no problem in making NT Authority/System login have sysadmin
privileges, in fact it is required if you have removed the
BUILTIN\Administrators login as the external MSSearch services needs
sysadmin privileges to access SQL Server. Note, this is by design. You can
implement this via the following T-SQL code:
exec sp_grantlogin N'NT Authority\System'
exec sp_defaultdb N'NT Authority\System', N'master'
exec sp_defaultlanguage N'NT Authority\System','us_english'
exec sp_addsrvrolemember N'NT Authority\System', sysadmin
Additionally, see KB article Q263712 "INF: How To Prevent Windows NT
Administrators From Administering a Clustered SQL Server" at
http://support.microsoft.com/default...;EN-US;q263712 as this KB
article recommends: "If a full-text search will be used on the cluster, you
must add the [NT Authority\System] account to the server's "sysadmin"
group."
Regards,
John
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"msqldba m via droptable.com" <forum@.droptable.com> wrote in message
news:5116CE83ADBA0@.droptable.com...
> Hilary,
> So there are no problems in making NT Authority/System a sysadmin(SA) in
sql[vbcol=seagreen]
> server
> Thanks,
>
> Hilary Cotter wrote:
ensure[vbcol=seagreen]
which[vbcol=seagreen]
your
>
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forums...earch/200507/1
we have full-text search service running under local system account and we
removed Builtin/Administrators
from our logins and now our full-text catalogs are not getting populated and
now the only resolution i see is
adding NT Authority/System as a login and granting sysadmin privileges .are
there any pros and cons in doing this ..in terms of security..
Thanks In Advance!
No, basically the reason dba's remove the builtin\admin account is to ensure
that NT admin's are automatically system admins on the SQL Server box. NT
Authority/System merely gives the system account rights to SQL Server which
MSSearch requires to contact SQL Server and vice versa. With NT
Authority/System the NT admin's can no longer automatically administer your
SQL Server.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"msqldba m via droptable.com" <forum@.droptable.com> wrote in message
news:5111AFEC65E20@.droptable.com...
> Hello,
> we have full-text search service running under local system account and we
> removed Builtin/Administrators
> from our logins and now our full-text catalogs are not getting populated
and
> now the only resolution i see is
> adding NT Authority/System as a login and granting sysadmin privileges
..are
> there any pros and cons in doing this ..in terms of security..
>
> Thanks In Advance!
|||Hilary,
So there are no problems in making NT Authority/System a sysadmin(SA) in sql
server
Thanks,
Hilary Cotter wrote:[vbcol=seagreen]
>No, basically the reason dba's remove the builtin\admin account is to ensure
>that NT admin's are automatically system admins on the SQL Server box. NT
>Authority/System merely gives the system account rights to SQL Server which
>MSSearch requires to contact SQL Server and vice versa. With NT
>Authority/System the NT admin's can no longer automatically administer your
>SQL Server.
>[quoted text clipped - 6 lines]
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums...earch/200507/1
|||msqldba,
No there is no problem in making NT Authority/System login have sysadmin
privileges, in fact it is required if you have removed the
BUILTIN\Administrators login as the external MSSearch services needs
sysadmin privileges to access SQL Server. Note, this is by design. You can
implement this via the following T-SQL code:
exec sp_grantlogin N'NT Authority\System'
exec sp_defaultdb N'NT Authority\System', N'master'
exec sp_defaultlanguage N'NT Authority\System','us_english'
exec sp_addsrvrolemember N'NT Authority\System', sysadmin
Additionally, see KB article Q263712 "INF: How To Prevent Windows NT
Administrators From Administering a Clustered SQL Server" at
http://support.microsoft.com/default...;EN-US;q263712 as this KB
article recommends: "If a full-text search will be used on the cluster, you
must add the [NT Authority\System] account to the server's "sysadmin"
group."
Regards,
John
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"msqldba m via droptable.com" <forum@.droptable.com> wrote in message
news:5116CE83ADBA0@.droptable.com...
> Hilary,
> So there are no problems in making NT Authority/System a sysadmin(SA) in
sql[vbcol=seagreen]
> server
> Thanks,
>
> Hilary Cotter wrote:
ensure[vbcol=seagreen]
which[vbcol=seagreen]
your
>
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forums...earch/200507/1
Regarding Form controls in Reporting Sql
I am doing 350 controls as in a form like in reporting service . Due to so many controls the reporting sql server after deployment the preview and report must be slow. and i will checked the data transmission in stored procedure from sql server it will be faster.I am having doubt in controls in form will be slower.
Is there any suggestions without decrement the controls and to be report will be faster.
sql
Regarding filegroups
Dear all,
I was just wondering myself how could I do for to obtain to which filegroup
belong a specific table.
Imagine that you have primary, primary_history, secondary_history,
index_history, bla,bla
Any idea or though would be very appreciated.
Please post DDL, DCL and DML statements as well as any error message in
order to understand better your request. It''''s hard to provide information
without seeing the code. location: Alicante (ES)What version of SQL Server? For SQL Server 2000, use the groupid column in s
ysindexes.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Enric" <vtam13@.terra.es.(donotspam)> wrote in message
news:43476F7C-8EC3-4C59-B80B-DBF0E9E36B74@.microsoft.com...
> Dear all,
> I was just wondering myself how could I do for to obtain to which filegrou
p
> belong a specific table.
> Imagine that you have primary, primary_history, secondary_history,
> index_history, bla,bla
> Any idea or though would be very appreciated.
> --
> Please post DDL, DCL and DML statements as well as any error message in
> order to understand better your request. It''''s hard to provide informati
on
> without seeing the code. location: Alicante (ES)|||Hi Tibor,
It's ok. That's fine but I'm looking for user tables no indexes.
Where does sql stores that?
I'm seeing sysobjects table with xtype = 'u'
--
Please post DDL, DCL and DML statements as well as any error message in
order to understand better your request. It''''s hard to provide information
without seeing the code. location: Alicante (ES)
"Tibor Karaszi" wrote:
> What version of SQL Server? For SQL Server 2000, use the groupid column in
sysindexes.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Enric" <vtam13@.terra.es.(donotspam)> wrote in message
> news:43476F7C-8EC3-4C59-B80B-DBF0E9E36B74@.microsoft.com...
>|||The physical aspects if a table is represented in sysindexes. Use WHERE indi
d IN (0,1). See Books
Online, sysindexes for more details about sysindexes.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Enric" <vtam13@.terra.es.(donotspam)> wrote in message
news:9EA44F73-094E-4458-8A0C-FF1BBD34123E@.microsoft.com...
> Hi Tibor,
> It's ok. That's fine but I'm looking for user tables no indexes.
> Where does sql stores that?
> I'm seeing sysobjects table with xtype = 'u'
> --
> Please post DDL, DCL and DML statements as well as any error message in
> order to understand better your request. It''''s hard to provide informati
on
> without seeing the code. location: Alicante (ES)
>
> "Tibor Karaszi" wrote:
>|||I've seen how, using DMO...
Another possibility?
--
Please post DDL, DCL and DML statements as well as any error message in
order to understand better your request. It''''s hard to provide information
without seeing the code. location: Alicante (ES)
"Tibor Karaszi" wrote:
> What version of SQL Server? For SQL Server 2000, use the groupid column in
sysindexes.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Enric" <vtam13@.terra.es.(donotspam)> wrote in message
> news:43476F7C-8EC3-4C59-B80B-DBF0E9E36B74@.microsoft.com...
>|||USE pubs
SELECT sfg.groupname
FROM sysfilegroups AS sfg
INNER JOIN sysindexes AS si
ON si.groupid = sfg.groupid
WHERE si.id = OBJECT_ID('dbo.authors')
AND indid IN(0,1)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Enric" <vtam13@.terra.es.(donotspam)> wrote in message
news:9A0F784A-A179-4593-9C8A-1226B0941772@.microsoft.com...
> I've seen how, using DMO...
> Another possibility?
> --
> Please post DDL, DCL and DML statements as well as any error message in
> order to understand better your request. It''''s hard to provide informati
on
> without seeing the code. location: Alicante (ES)
>
> "Tibor Karaszi" wrote:
>
I was just wondering myself how could I do for to obtain to which filegroup
belong a specific table.
Imagine that you have primary, primary_history, secondary_history,
index_history, bla,bla
Any idea or though would be very appreciated.
Please post DDL, DCL and DML statements as well as any error message in
order to understand better your request. It''''s hard to provide information
without seeing the code. location: Alicante (ES)What version of SQL Server? For SQL Server 2000, use the groupid column in s
ysindexes.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Enric" <vtam13@.terra.es.(donotspam)> wrote in message
news:43476F7C-8EC3-4C59-B80B-DBF0E9E36B74@.microsoft.com...
> Dear all,
> I was just wondering myself how could I do for to obtain to which filegrou
p
> belong a specific table.
> Imagine that you have primary, primary_history, secondary_history,
> index_history, bla,bla
> Any idea or though would be very appreciated.
> --
> Please post DDL, DCL and DML statements as well as any error message in
> order to understand better your request. It''''s hard to provide informati
on
> without seeing the code. location: Alicante (ES)|||Hi Tibor,
It's ok. That's fine but I'm looking for user tables no indexes.
Where does sql stores that?
I'm seeing sysobjects table with xtype = 'u'
--
Please post DDL, DCL and DML statements as well as any error message in
order to understand better your request. It''''s hard to provide information
without seeing the code. location: Alicante (ES)
"Tibor Karaszi" wrote:
> What version of SQL Server? For SQL Server 2000, use the groupid column in
sysindexes.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Enric" <vtam13@.terra.es.(donotspam)> wrote in message
> news:43476F7C-8EC3-4C59-B80B-DBF0E9E36B74@.microsoft.com...
>|||The physical aspects if a table is represented in sysindexes. Use WHERE indi
d IN (0,1). See Books
Online, sysindexes for more details about sysindexes.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Enric" <vtam13@.terra.es.(donotspam)> wrote in message
news:9EA44F73-094E-4458-8A0C-FF1BBD34123E@.microsoft.com...
> Hi Tibor,
> It's ok. That's fine but I'm looking for user tables no indexes.
> Where does sql stores that?
> I'm seeing sysobjects table with xtype = 'u'
> --
> Please post DDL, DCL and DML statements as well as any error message in
> order to understand better your request. It''''s hard to provide informati
on
> without seeing the code. location: Alicante (ES)
>
> "Tibor Karaszi" wrote:
>|||I've seen how, using DMO...
Another possibility?
--
Please post DDL, DCL and DML statements as well as any error message in
order to understand better your request. It''''s hard to provide information
without seeing the code. location: Alicante (ES)
"Tibor Karaszi" wrote:
> What version of SQL Server? For SQL Server 2000, use the groupid column in
sysindexes.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Enric" <vtam13@.terra.es.(donotspam)> wrote in message
> news:43476F7C-8EC3-4C59-B80B-DBF0E9E36B74@.microsoft.com...
>|||USE pubs
SELECT sfg.groupname
FROM sysfilegroups AS sfg
INNER JOIN sysindexes AS si
ON si.groupid = sfg.groupid
WHERE si.id = OBJECT_ID('dbo.authors')
AND indid IN(0,1)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Enric" <vtam13@.terra.es.(donotspam)> wrote in message
news:9A0F784A-A179-4593-9C8A-1226B0941772@.microsoft.com...
> I've seen how, using DMO...
> Another possibility?
> --
> Please post DDL, DCL and DML statements as well as any error message in
> order to understand better your request. It''''s hard to provide informati
on
> without seeing the code. location: Alicante (ES)
>
> "Tibor Karaszi" wrote:
>
regarding extended property
hi,
Can anyone tell me how to use the extended stored property like sp_addextendedproperty in java code?
Thanks,
Meghna
It is used just like any stored procedure. I would search for a java group for this. If you know know to use procedures, it is reasonably straightforward to use the procedure, if a bit convoluted.
Books Online (http://msdn2.microsoft.com/en-US/library/ms130214.aspx) has a very good set of examples, like this one to add a property to the database:
USE AdventureWorks;
GO
--Add a caption to the AdventureWorks Database object itself.
EXEC sp_addextendedproperty
@.name = N'Caption', @.value = 'AdventureWorks Sample OLTP Database';
Hi,
what u have given me is the SQL querry for using sp_addextendedproperty, but if i want to do the same thing from a java program will it be possible?
Also how can i view the added extended property using ::fn_listextendedproperty from a java program?
Thanks,
Meghna
regarding extended property
hi,
Can anyone tell me how to use the extended stored property like sp_addextendedproperty in java code?
Thanks,
Meghna
It is used just like any stored procedure. I would search for a java group for this. If you know know to use procedures, it is reasonably straightforward to use the procedure, if a bit convoluted.
Books Online (http://msdn2.microsoft.com/en-US/library/ms130214.aspx) has a very good set of examples, like this one to add a property to the database:
USE AdventureWorks;
GO
--Add a caption to the AdventureWorks Database object itself.
EXEC sp_addextendedproperty
@.name = N'Caption', @.value = 'AdventureWorks Sample OLTP Database';
Hi,
what u have given me is the SQL querry for using sp_addextendedproperty, but if i want to do the same thing from a java program will it be possible?
Also how can i view the added extended property using ::fn_listextendedproperty from a java program?
Thanks,
Meghna
Regarding ETL, urgent
Dear all,
I've got two DTS with a lot of transformations and within of them VbScript
snippets and so on. My issue is that both has been modified incorrectly but
I
haven't idea where and how. So this way I was wondering if exists any
third-tool on the market (I'm not thinking in the own Sql Server) which be
able to compare automatically two dts from the most low level. Otherwise I'l
l
be to compare section by section using WinMerge or something like that.
Any input or comment would be very welcomed.You have a few options :-
DTS keeps a version history that will enable you to rollback to previous
package (Simply right click the package and choose version)
Red Gate do a package called "DTS Compare"
Restore msdb from backups to another server and extract the package you
require.
HTH. Ryan
"Enric" <Enric@.discussions.microsoft.com> wrote in message
news:36CA63FC-E150-4BD0-BB14-27F715BAE47D@.microsoft.com...
> Dear all,
> I've got two DTS with a lot of transformations and within of them VbScript
> snippets and so on. My issue is that both has been modified incorrectly
> but I
> haven't idea where and how. So this way I was wondering if exists any
> third-tool on the market (I'm not thinking in the own Sql Server) which be
> able to compare automatically two dts from the most low level. Otherwise
> I'll
> be to compare section by section using WinMerge or something like that.
> Any input or comment would be very welcomed.
>|||Thanks in advance Ryan,
"Ryan" wrote:
> You have a few options :-
> DTS keeps a version history that will enable you to rollback to previo
us
> package (Simply right click the package and choose version)
> Red Gate do a package called "DTS Compare"
> Restore msdb from backups to another server and extract the package yo
u
> require.
> --
> HTH. Ryan
> "Enric" <Enric@.discussions.microsoft.com> wrote in message
> news:36CA63FC-E150-4BD0-BB14-27F715BAE47D@.microsoft.com...
>
>|||Hi Enric
as a built-in alternative - not as slick I'm sure as a dedicated tool
such as DTS Compare - you can save any DTS package as a VB module. So
assuming you're comfortable with VB, you should be able to compare the
two packages by extracting them both to VB modules and comparing the
two files. Even if you don't have Visual Studio, a basic file-compare
utility should be able to show you the differences.
cheers
Seb
I've got two DTS with a lot of transformations and within of them VbScript
snippets and so on. My issue is that both has been modified incorrectly but
I
haven't idea where and how. So this way I was wondering if exists any
third-tool on the market (I'm not thinking in the own Sql Server) which be
able to compare automatically two dts from the most low level. Otherwise I'l
l
be to compare section by section using WinMerge or something like that.
Any input or comment would be very welcomed.You have a few options :-
DTS keeps a version history that will enable you to rollback to previous
package (Simply right click the package and choose version)
Red Gate do a package called "DTS Compare"
Restore msdb from backups to another server and extract the package you
require.
HTH. Ryan
"Enric" <Enric@.discussions.microsoft.com> wrote in message
news:36CA63FC-E150-4BD0-BB14-27F715BAE47D@.microsoft.com...
> Dear all,
> I've got two DTS with a lot of transformations and within of them VbScript
> snippets and so on. My issue is that both has been modified incorrectly
> but I
> haven't idea where and how. So this way I was wondering if exists any
> third-tool on the market (I'm not thinking in the own Sql Server) which be
> able to compare automatically two dts from the most low level. Otherwise
> I'll
> be to compare section by section using WinMerge or something like that.
> Any input or comment would be very welcomed.
>|||Thanks in advance Ryan,
"Ryan" wrote:
> You have a few options :-
> DTS keeps a version history that will enable you to rollback to previo
us
> package (Simply right click the package and choose version)
> Red Gate do a package called "DTS Compare"
> Restore msdb from backups to another server and extract the package yo
u
> require.
> --
> HTH. Ryan
> "Enric" <Enric@.discussions.microsoft.com> wrote in message
> news:36CA63FC-E150-4BD0-BB14-27F715BAE47D@.microsoft.com...
>
>|||Hi Enric
as a built-in alternative - not as slick I'm sure as a dedicated tool
such as DTS Compare - you can save any DTS package as a VB module. So
assuming you're comfortable with VB, you should be able to compare the
two packages by extracting them both to VB modules and comparing the
two files. Even if you don't have Visual Studio, a basic file-compare
utility should be able to show you the differences.
cheers
Seb
Regarding Dynamic query...
Hi,
I have basic design question regarding dynamic query,
When we have to build a dynamic query (which has table name also as an input parameter),
->Is it better to write a stored procedure ..?
or
->directly specify the dynamic query and set the command type as text in .NET code...?
I think,since dynamic queries may not have the advantage of precompliation, it may not yield any performance in using SP's in such case..
Please through some light on this,
TIA
I don't think there's any right answer to this question. Personally I'd prefer to build a stored procedure for this because I like all of my database access code to be centrally located. Others will say it's a waste of time and effort to put this type of dynamic code into a stored procedure because it buys you nothing in terms of performance and it litters the database unnecessarily.
This is the type of topic that can generate a lot of debate. We'll see if anyone else bites. :-)
|||Why do you need to pass in a table name? Why does yourapplication have that much knowledge of your database? It soundsas though you have a serious architectural problem. You'vetightly coupled your application to your database. Figure out howto DECOUPLE the systems so that changing one won't break theother. One of the main benefits of stored procedures isencapsulation; passing in a table name defeats that goal.sql
Regarding DTS
Hi,
I want to copy the data from Oracle table to excel file in DTS packages.
I have used transformation task, but it is asking for table but excel is a file.
please any body could tell em about this?
Thanks and Regards,
Purushotham,Use connection first.
In connection you will be able to find Excel files or Oracle.
Good Luck.
Regarding drop and restore replication database
Hi Guys,
I have two questions regarding replication:
1, If I don't continue to use replication at all, how can I drop replication
completely? Because too many command, e.g. sp_removedbreplication or
sp_dropplication and so on. Which one can I use?
2. If I want to retore the replication database and want to keep replication
so that I can continue to use replication, do I need to setup some steps
before restore database?
Thanks.
To remove replication, use sp_removedbreplication. There might be a few
orphaned objects left behind but these can be deleted by hand or simply left
there as they won't cause any problems.
To restore a replicated database, pls have a look in BOL for the topic
"replication, backup and restore operations".
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||Thank you very much. I use sp_removedbreplication to remove the replication,
it is ok. But like what you said, there are many orphaned objects and mark as
crose with read color, it is very uncomfortable. I use sp_dropdistpublisher
and sp_dropsubscriber to remove those objects and it is still marked even
though there isn't those objects. How to get rid of red color? thanks.
"Paul Ibison" wrote:
> To remove replication, use sp_removedbreplication. There might be a few
> orphaned objects left behind but these can be deleted by hand or simply left
> there as they won't cause any problems.
> To restore a replicated database, pls have a look in BOL for the topic
> "replication, backup and restore operations".
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com .
>
>
|||The replication monitor gets its info from
tempdb.dbo.MSreplication_agent_status and running
sp_MSload_replication_status refreshes this table. As restarting the SQL
Server Service causes the tempdb to be recreated from the model database,
this also removes the problem.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
I have two questions regarding replication:
1, If I don't continue to use replication at all, how can I drop replication
completely? Because too many command, e.g. sp_removedbreplication or
sp_dropplication and so on. Which one can I use?
2. If I want to retore the replication database and want to keep replication
so that I can continue to use replication, do I need to setup some steps
before restore database?
Thanks.
To remove replication, use sp_removedbreplication. There might be a few
orphaned objects left behind but these can be deleted by hand or simply left
there as they won't cause any problems.
To restore a replicated database, pls have a look in BOL for the topic
"replication, backup and restore operations".
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||Thank you very much. I use sp_removedbreplication to remove the replication,
it is ok. But like what you said, there are many orphaned objects and mark as
crose with read color, it is very uncomfortable. I use sp_dropdistpublisher
and sp_dropsubscriber to remove those objects and it is still marked even
though there isn't those objects. How to get rid of red color? thanks.
"Paul Ibison" wrote:
> To remove replication, use sp_removedbreplication. There might be a few
> orphaned objects left behind but these can be deleted by hand or simply left
> there as they won't cause any problems.
> To restore a replicated database, pls have a look in BOL for the topic
> "replication, backup and restore operations".
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com .
>
>
|||The replication monitor gets its info from
tempdb.dbo.MSreplication_agent_status and running
sp_MSload_replication_status refreshes this table. As restarting the SQL
Server Service causes the tempdb to be recreated from the model database,
this also removes the problem.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
Regarding domain name for mirroring
Hi guys, may I know whether the domain name needed to be same for the principal server and mirror server in order to get a connection between both the servers and setting up mirroring section successful? Is it possible to use different domain name for both servers for setting up the mirroring section. Can someone share his/her idea or experience like how to setting up the mirroring section and the configuration for the windows before setting up. Thx and hope can get any assistance here.
Best regards,
Hans
Yes, Database Miroring can be setup across two different domains.Please look at the Books Online at http://msdn2.microsoft.com/en-us/library/ms177412.aspx for details.Regarding dimension renaming in AS2005 while migrtion
Hi,
My datamart has used dots in the dimension name. i.e. I have following dimensions
Users.Sales1 and Users.Sales2.
Currently my application has 2 cubes: Sales1 and Sales2.
Cube Sales1 is assocated with
"Parent Child" dimension Users.Sales1
which is based on view vw_Users_Sales1
Cube Sales2 is assocated with
"Parent Child" dimension Users.Sales2
which is based on view vw_Users_Sales2
Now when I am trying migration wizard, I am getting following dimensions
Users.Sales1 and Users1.Sales2.
Is there some way I can retain the existing naming convension?
And If not them what is the best convension or what will
will be the best possible to handle this scenario.
There are other dimensions as well which are getting renamed like this.
So I will like to know how others are handling this.
Thanks In Advance,
Rahul
The dimension model was changed in 2005. In 2000, you could have dimensions based off different tables that shared a common dimension name. The intention behind this was to support multiple hierarchies for the same dimension, but each one was still implemented as a seperate dimension, they just shared a common root name (Users, in your case).
2005 can now support multiple hierachies in the same dimension. However, they need to come from the same table or view. Since, in your case, they are comming from two different views, the wizard is creating two seperate dimensions. And you since you can't have two dimensions with the same name, the wizard is renaming one of them.
|||John is right, you might find that you are able to create a view or a named query as a quick way to join these tables together.Regarding dblib
hi..I am running DBLIB (sample application of SQL Server)
between 2k and 2k3. Well seems working perfect..but want
to know whats going on in the Database..I think some
DB configuration (like DB connection or Table creation)
is required to see the effect of executed/commited
transactions..need some guidence.
Thanks in advance
Umer
between 2k and 2k3. Well seems working perfect..but want
to know whats going on in the Database..I think some
DB configuration (like DB connection or Table creation)
is required to see the effect of executed/commited
transactions..need some guidence.
Thanks in advance
Umer
Try some SQL Engine specific forum, e.g. http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=93&SiteID=1
This forum is for Integration Services component, not sure if there are appropriate people to respond to this question here.
sqlRegarding dblib
hi..I am running DBLIB (sample application of SQL Server)
between 2k and 2k3. Well seems working perfect..but want
to know whats going on in the Database..I think some
DB configuration (like DB connection or Table creation)
is required to see the effect of executed/commited
transactions..need some guidence.
Thanks in advance
Umer
You shouldn't really invest any more time in DBLib, and move to some other
data access APIs (ADO.NET, OLEDB, ODBC, etc). You might be talking about
SQL2000, but consider what SQL2005 BOL says about Db-Library:
"Although the SQL Server 2005 Database Engine still supports connections
from existing applications using the DB-Library and Embedded SQL APIs, it
does not include the files or documentation needed to do programming work on
applications that use these APIs." on page
http://msdn2.microsoft.com/en-us/library/ms143729(en-US,SQL.90).aspx
Linchi
<umer.zafar@.gmail.com> wrote in message
news:1131628582.386464.75780@.g47g2000cwa.googlegro ups.com...
> hi..I am running DBLIB (sample application of SQL Server)
> between 2k and 2k3. Well seems working perfect..but want
> to know whats going on in the Database..I think some
> DB configuration (like DB connection or Table creation)
> is required to see the effect of executed/commited
> transactions..need some guidence.
> Thanks in advance
> Umer
>
between 2k and 2k3. Well seems working perfect..but want
to know whats going on in the Database..I think some
DB configuration (like DB connection or Table creation)
is required to see the effect of executed/commited
transactions..need some guidence.
Thanks in advance
Umer
You shouldn't really invest any more time in DBLib, and move to some other
data access APIs (ADO.NET, OLEDB, ODBC, etc). You might be talking about
SQL2000, but consider what SQL2005 BOL says about Db-Library:
"Although the SQL Server 2005 Database Engine still supports connections
from existing applications using the DB-Library and Embedded SQL APIs, it
does not include the files or documentation needed to do programming work on
applications that use these APIs." on page
http://msdn2.microsoft.com/en-us/library/ms143729(en-US,SQL.90).aspx
Linchi
<umer.zafar@.gmail.com> wrote in message
news:1131628582.386464.75780@.g47g2000cwa.googlegro ups.com...
> hi..I am running DBLIB (sample application of SQL Server)
> between 2k and 2k3. Well seems working perfect..but want
> to know whats going on in the Database..I think some
> DB configuration (like DB connection or Table creation)
> is required to see the effect of executed/commited
> transactions..need some guidence.
> Thanks in advance
> Umer
>
Regarding dblib
hi..I am running DBLIB (sample application of SQL Server)
between 2k and 2k3. Well seems working perfect..but want
to know whats going on in the Database..I think some
DB configuration (like DB connection or Table creation)
is required to see the effect of executed/commited
transactions..need some guidence.
Thanks in advance
UmerTry running the SQL Profiler to trace the database activity.|||(umer.zafar@.gmail.com) writes:
> hi..I am running DBLIB (sample application of SQL Server)
> between 2k and 2k3. Well seems working perfect..but want
> to know whats going on in the Database..I think some
> DB configuration (like DB connection or Table creation)
> is required to see the effect of executed/commited
> transactions..need some guidence.
Difficult to understand what you mean. One very nice feature of DB-Library
is that it does not do things behind your back like those modern API are
fond of doing.
But, alas, Microsoft has put DB-Library on the deprecation list, and
DB-Library does not have support for features added to SQL 7 and later.
While you still connect to SQL 2005 with DB-Library, SQL 2005 does not
come with any files to support DB-Library. A future version of SQL Server
will not support DB-Library at all.
Thus, unless you have some legacy application to maintain, you are best
off staying away from DB-Library.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Thanks..helping to get me my answer
between 2k and 2k3. Well seems working perfect..but want
to know whats going on in the Database..I think some
DB configuration (like DB connection or Table creation)
is required to see the effect of executed/commited
transactions..need some guidence.
Thanks in advance
UmerTry running the SQL Profiler to trace the database activity.|||(umer.zafar@.gmail.com) writes:
> hi..I am running DBLIB (sample application of SQL Server)
> between 2k and 2k3. Well seems working perfect..but want
> to know whats going on in the Database..I think some
> DB configuration (like DB connection or Table creation)
> is required to see the effect of executed/commited
> transactions..need some guidence.
Difficult to understand what you mean. One very nice feature of DB-Library
is that it does not do things behind your back like those modern API are
fond of doing.
But, alas, Microsoft has put DB-Library on the deprecation list, and
DB-Library does not have support for features added to SQL 7 and later.
While you still connect to SQL 2005 with DB-Library, SQL 2005 does not
come with any files to support DB-Library. A future version of SQL Server
will not support DB-Library at all.
Thus, unless you have some legacy application to maintain, you are best
off staying away from DB-Library.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Thanks..helping to get me my answer
Regarding dblib
hi..I am running DBLIB (sample application of SQL Server)
between 2k and 2k3. Well seems working perfect..but want
to know whats going on in the Database..I think some
DB configuration (like DB connection or Table creation)
is required to see the effect of executed/commited
transactions..need some guidence.
Thanks in advance
Umer
Hi
Run SQL Server Profiler to see what is being sent to SQL Server.
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/
<umer.zafar@.gmail.com> wrote in message
news:1131628639.794052.79080@.g47g2000cwa.googlegro ups.com...
> hi..I am running DBLIB (sample application of SQL Server)
> between 2k and 2k3. Well seems working perfect..but want
> to know whats going on in the Database..I think some
> DB configuration (like DB connection or Table creation)
> is required to see the effect of executed/commited
> transactions..need some guidence.
> Thanks in advance
> Umer
>
|||Hi..Thanks a lot ..answer was very helping.
Regards
Umer
between 2k and 2k3. Well seems working perfect..but want
to know whats going on in the Database..I think some
DB configuration (like DB connection or Table creation)
is required to see the effect of executed/commited
transactions..need some guidence.
Thanks in advance
Umer
Hi
Run SQL Server Profiler to see what is being sent to SQL Server.
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/
<umer.zafar@.gmail.com> wrote in message
news:1131628639.794052.79080@.g47g2000cwa.googlegro ups.com...
> hi..I am running DBLIB (sample application of SQL Server)
> between 2k and 2k3. Well seems working perfect..but want
> to know whats going on in the Database..I think some
> DB configuration (like DB connection or Table creation)
> is required to see the effect of executed/commited
> transactions..need some guidence.
> Thanks in advance
> Umer
>
|||Hi..Thanks a lot ..answer was very helping.
Regards
Umer
regarding datetime in asp.net
hi ,
i had textbox which is used to enter datetime . in the database, the data type is date/time. now my requirement is when the textbox is empty. i want 2 store a default value. which value is suitable for datetime.
i had given as follows
if textbox12.text = "" then
textbox12.text = 5/5/2000 ---- it is storing this value.
end if
but if i want 2 update this date means it is not supporting . if i select current date using calender it was saved as 12/30/1899.
can anyone help me.
regards
m.arunachalam
You can trytextbox12.Text = DateTime.Today
Regarding DataMining in SQL Server 2005 Analysis Services
Hi to all
I have a complex scenario , so first I want to ask out the feasibility of it . I think its better if I state the scenario and some one on this forum reply to it , I need to build an application (e.g credit card application , Loan Application etc ) that requires some approval from expert , what I firstly want is that I apply datamining on this data so that next time when I enter the data the result (approval or reject ) should be given by datamining tool , this I gather is poosible by using Analysis Services in SQL 2005 , but I also want the bases of that decision ( I mean the rules/some thing else that the Datamining created agaisnt the data entered ) , So can any one do any help on this , you can also reach me at razi_rais@.yahoo.com . its preety much urgent so your prompt response is higly appreciated.
Thanks and Regards
Razi Bin Rais
Moving to DM forum
Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
Data Mining definately has the ability to do this. Since you are apparently new at data mining I would recommend the Data Mining with SQL Server 2005 book and also using the Data Mining Addins for Office 2007 to get started in learning and exploring the tools.
Instructions for getting the preview build of the addins as well as eval editions of Office and SQLServer are here: http://blogs.msdn.com/jamiemac/archive/2006/12/29/try-out-the-data-mining-addins-for-office-2007-free-for-60-days.aspx
|||Hi Jamie
Thanks for your feedback , I will certainly look into resources you specify . For now can you please amplify on two points
1) Is DM in SQL Server 2005 provides any way to get the rules out of it , on basics of which it makes decision. I mean through object model , or plain xml ... anything?
2) Can you give me simple example of application (step wise / demograpic / architecutal ) like say credit card application or loan approval application that has very simple schema and only have accept or reject option apart from simple personal details of customer that will be predicted by DM (After entering some sampel data ) , and if possible the above point 1 for it .
Thanks again
Razi
|||
This link might be helpful: http://www.sql-server-performance.com/ec_data_mining.asp
It includes an high-level example of a credit approval application utilizing data mining...
Frank
sqlregarding datamigration using ssis packages
hai,
Currently i am working with data migration using SSIS packages.
i would like to load the data from the flat file to database(siebel).
But i need three extra columns which are not there in flat file to load into the database.
the columns are as follows.
1.ROW_BATCHfixed value(eg:100)
2.ROW_ID-(should be unique just like a sequence)
3.ROW_STATfixed value(string)
The flatfile columns along with the above specified columns should be loaded into the siebel database using SSIS package.
please help me.
You can use a Script-Component in the DataFlow-Task:
1. Drag and configure a Flat-File-Connector to a DataFlow-Task.
2. Drag a Scrip-Component to your DataFlow-Task and select transformation.
3. Connect the Output of your Flat-File-Connector to thr Script-Component.
4. Double-Click the Script-Component and select thr Input and Outputs
5. Add 2 new columns. The new column Fix has the DataType string. The new column ID has the DataTyp [DT_i4].
6 Navigate to the Script tab and Click Design Script.
7. Edit your Script:
Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper
Public Class ScriptMain
Inherits UserComponent
Dim id As Integer = 0
Public Overrides Sub Input_ProcessInputRow(ByVal Row As InputBuffer)
Row.Fix = "123"
id = id + 1
Row.ID = id
End Sub
End Class
8. Connect the Output of the Script-Component to your destination.
|||thanks loom , thank u very much for your help
Regarding Database Tuning Advisor
Hi guys, I am new to use Database Tuning Advisor. Right now I just save a workload as a sql and runs it in Database Tuning Advisor. The script that I wrote is kinda simple ( Select EID,EName,EDescription From Events Where EID = 300) . However, after I finish analyzed it, there's nothing to display on the recommendations session. Is it suppose got any details in it ? Or does it mean I no need to tune my query performance ? Hope someone can help me out here. Thx guys.
Best Regards,
Hans
It sounds like there were no recommendations -which I would suspect for such a simplistic script if you had basic indexes.
Normally, to generate a 'workload', you would run Profiler for a period of time, capturing all work on the server to a file (or table), and then use the DTA with that workload. I normally recommend, depending upon the amount of 'traffic', to capture a 24 hours block of work, or several key one hour segments during a day.
The primary idea with DTA is to try and reproduce what is happening on the server so that it can try to determine if the indexing is appropriate considering all that is being requested.
Regarding Data Transpose
Hello,
I have got a table, which consists 5 columns. If suppose there are ten
rows, then I want to insert the data present in this table into another
temporary table, which consists of 50 columns. So, effectively I want to
convert all the rows into one row by transposing. How can I do this?
--With Regards,
Sheshadrinath.R"Sheshadrinath R" <SheshadrinathR@.discussions.microsoft.com> wrote in
message news:37037772-7988-45E4-B957-62439844EFD8@.microsoft.com...
> Hello,
> I have got a table, which consists 5 columns. If suppose there are ten
> rows, then I want to insert the data present in this table into another
> temporary table, which consists of 50 columns. So, effectively I want to
> convert all the rows into one row by transposing. How can I do this?
> --With Regards,
> Sheshadrinath.R
Why? Have you considered just changing the way you display the data rather
than attempting such a thing in the database?
You haven't given enough information to answer your question fully. How
should we determine which rows get transposed to which columns for example?
Google this group for "transpose" and "crosstab" and you'll find plenty of
examples that might help you.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--
I have got a table, which consists 5 columns. If suppose there are ten
rows, then I want to insert the data present in this table into another
temporary table, which consists of 50 columns. So, effectively I want to
convert all the rows into one row by transposing. How can I do this?
--With Regards,
Sheshadrinath.R"Sheshadrinath R" <SheshadrinathR@.discussions.microsoft.com> wrote in
message news:37037772-7988-45E4-B957-62439844EFD8@.microsoft.com...
> Hello,
> I have got a table, which consists 5 columns. If suppose there are ten
> rows, then I want to insert the data present in this table into another
> temporary table, which consists of 50 columns. So, effectively I want to
> convert all the rows into one row by transposing. How can I do this?
> --With Regards,
> Sheshadrinath.R
Why? Have you considered just changing the way you display the data rather
than attempting such a thing in the database?
You haven't given enough information to answer your question fully. How
should we determine which rows get transposed to which columns for example?
Google this group for "transpose" and "crosstab" and you'll find plenty of
examples that might help you.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--
regarding data grid
what datafield shoud i put in my database if its a checkbox option?
coz in my datagrid i added a checkedbox so that i can easily manipulate it.but when i run it. it produces error. and doesnt recognize the checkbox column.
it said that there none in the datasource.but i dont know what should i put in the database! pls help!thanks
You could have your field in the table set to anything you want. You would however need to translate what "checked" or "not checked" in a datagrid means. Google for "Using checkbox columns in datagrid". Generally I have seen boolean columns, some "tinyint" type columns being used to store the check selection.Regarding Custom Code using table or list control(data set)
I have the report like this
list1
{
list2
{
table1
table header
table fields
table footer
}
}
now i want to calculate the sum of one field in table when it contains particular value using CUSTOM CODE ONLY
and i want to display it in table footer. please send me answer to this id as soon as possible.
Thanks
Sateesh Maduri
Maybe I'm missing something, but couldn't you just use a conditional aggregation expression like the following in the table footer:
=Sum(iif(Fields!A.Value = "abc", CInt(Fields!B.Value), 0))
-- Robert
|||Hello Robert
Actually I already know that what u have given using sum().But I need Custom Code for that ,I mean if I have a dataset which fills the data in the table control How should I write Custom Code to calculate sum of one field when it contains particular value.
Thanks
sqlRegarding Custom Code
Hello
I have report like this
list1
list2(includes in list1)
table (includes in list2)
end of list2
end of list1
Now I want to calculate the sum of one column in table when it contains particular value and I want to display in table footer . I already know using sum() fuction.I want to know using custom code calculation for the above problem(How Can I repeat my data set in Custom code function for each row). So , If anyone knows Please reply.
It's Very Urgent
Thanks
Have a look at these
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=541699&SiteID=1
http://blogs.msdn.com/bwelcker/archive/2005/05/10/416306.aspx
Regarding Creating Stored Procedures If One Does Not Exist
Hello,
Very quick question (I hope) regarding SQL Server:
I want to create a query that basically says that if a stored procedure (say
sproc_foo) does not exist, then create the stored procedure. I know that
there are easy ways to do this with creating tables/databases if they don't
exist, but how is this possible with stored procedures?
Regards,
James Simpson
Straightway Technologies Inc.Hi James,
You can try something like this
IF NOT EXISTS (SELECT * FROM sys.objects
WHERE object_id = OBJECT_ID(N'[dbo].[uspGetBillOfMaterials]')
AND type in (N'P', N'PC'))
BEGIN
CREATE PROCEDURE [dbo].[uspGetBillOfMaterials]
...
Hope this helps,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"James Simpson" wrote:
> Hello,
> Very quick question (I hope) regarding SQL Server:
> I want to create a query that basically says that if a stored procedure (say
> sproc_foo) does not exist, then create the stored procedure. I know that
> there are easy ways to do this with creating tables/databases if they don't
> exist, but how is this possible with stored procedures?
> Regards,
> James Simpson
> Straightway Technologies Inc.
>|||Ben,
Alas, that will not work as is, since CREATE PROCEDURE must be the first
statement in a batch. Scripting from SQL Server 2005 with the If Not Exists
check turned on produces:
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id =OBJECT_ID(N'[dbo].[MyProc]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @.statement = N'
CREATE PROC [dbo].[MyProc]
@.Parm1 char(6)
AS
-- Do Something'
So, as you can see it is a 'dynamic SQL' implementation. (I believe that
there is a CONNECT request for something better than this.)
RLF
"Ben Nevarez" <BenNevarez@.discussions.microsoft.com> wrote in message
news:36E61DF6-EBAB-45E8-B0E5-A91610CA5D46@.microsoft.com...
> Hi James,
> You can try something like this
> IF NOT EXISTS (SELECT * FROM sys.objects
> WHERE object_id = OBJECT_ID(N'[dbo].[uspGetBillOfMaterials]')
> AND type in (N'P', N'PC'))
> BEGIN
> CREATE PROCEDURE [dbo].[uspGetBillOfMaterials]
> ...
> Hope this helps,
> Ben Nevarez
> Senior Database Administrator
> AIG SunAmerica
>
> "James Simpson" wrote:
>> Hello,
>> Very quick question (I hope) regarding SQL Server:
>> I want to create a query that basically says that if a stored procedure
>> (say
>> sproc_foo) does not exist, then create the stored procedure. I know that
>> there are easy ways to do this with creating tables/databases if they
>> don't
>> exist, but how is this possible with stored procedures?
>> Regards,
>> James Simpson
>> Straightway Technologies Inc.|||Dear Ben/Russel,
Ben - I tried the solution you gave and for whatever reason it does not
appear to work. I have also seen this type of example online and this query
fails miserably on SQL Server 2005 Express. Russel, the solution you gave
works perfectly, and hopefully Microsoft resolves this issue.
Regards,
James Simpson
Straightway Technologies Inc.|||Hi Russell,
I agree with your note but that was not intended to be a final and complete
solution and my primary purpose was to show the IF NOT EXISTS part.
Perhaps it is like saying that your code does not work as is either, because
you have BEGIN but END is missing :-)
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"Russell Fields" wrote:
> Ben,
> Alas, that will not work as is, since CREATE PROCEDURE must be the first
> statement in a batch. Scripting from SQL Server 2005 with the If Not Exists
> check turned on produces:
> IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id => OBJECT_ID(N'[dbo].[MyProc]') AND type in (N'P', N'PC'))
> BEGIN
> EXEC dbo.sp_executesql @.statement = N'
> CREATE PROC [dbo].[MyProc]
> @.Parm1 char(6)
> AS
> -- Do Something'
> So, as you can see it is a 'dynamic SQL' implementation. (I believe that
> there is a CONNECT request for something better than this.)
> RLF
>
> "Ben Nevarez" <BenNevarez@.discussions.microsoft.com> wrote in message
> news:36E61DF6-EBAB-45E8-B0E5-A91610CA5D46@.microsoft.com...
> >
> > Hi James,
> >
> > You can try something like this
> >
> > IF NOT EXISTS (SELECT * FROM sys.objects
> > WHERE object_id = OBJECT_ID(N'[dbo].[uspGetBillOfMaterials]')
> > AND type in (N'P', N'PC'))
> > BEGIN
> > CREATE PROCEDURE [dbo].[uspGetBillOfMaterials]
> > ...
> >
> > Hope this helps,
> >
> > Ben Nevarez
> > Senior Database Administrator
> > AIG SunAmerica
> >
> >
> >
> > "James Simpson" wrote:
> >
> >> Hello,
> >> Very quick question (I hope) regarding SQL Server:
> >> I want to create a query that basically says that if a stored procedure
> >> (say
> >> sproc_foo) does not exist, then create the stored procedure. I know that
> >> there are easy ways to do this with creating tables/databases if they
> >> don't
> >> exist, but how is this possible with stored procedures?
> >>
> >> Regards,
> >>
> >> James Simpson
> >> Straightway Technologies Inc.
> >>
>
>|||Ben, OK. Sorry. I was just trying to emphasize that the 'natural' way will
not work as expected. - RLF
"Ben Nevarez" <BenNevarez@.discussions.microsoft.com> wrote in message
news:3C07E41D-E259-4887-ABCE-2C403FD6929A@.microsoft.com...
> Hi Russell,
> I agree with your note but that was not intended to be a final and
> complete
> solution and my primary purpose was to show the IF NOT EXISTS part.
> Perhaps it is like saying that your code does not work as is either,
> because
> you have BEGIN but END is missing :-)
> Ben Nevarez
> Senior Database Administrator
> AIG SunAmerica
>
> "Russell Fields" wrote:
>> Ben,
>> Alas, that will not work as is, since CREATE PROCEDURE must be the first
>> statement in a batch. Scripting from SQL Server 2005 with the If Not
>> Exists
>> check turned on produces:
>> IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id =>> OBJECT_ID(N'[dbo].[MyProc]') AND type in (N'P', N'PC'))
>> BEGIN
>> EXEC dbo.sp_executesql @.statement = N'
>> CREATE PROC [dbo].[MyProc]
>> @.Parm1 char(6)
>> AS
>> -- Do Something'
>> So, as you can see it is a 'dynamic SQL' implementation. (I believe that
>> there is a CONNECT request for something better than this.)
>> RLF
>>
>> "Ben Nevarez" <BenNevarez@.discussions.microsoft.com> wrote in message
>> news:36E61DF6-EBAB-45E8-B0E5-A91610CA5D46@.microsoft.com...
>> >
>> > Hi James,
>> >
>> > You can try something like this
>> >
>> > IF NOT EXISTS (SELECT * FROM sys.objects
>> > WHERE object_id = OBJECT_ID(N'[dbo].[uspGetBillOfMaterials]')
>> > AND type in (N'P', N'PC'))
>> > BEGIN
>> > CREATE PROCEDURE [dbo].[uspGetBillOfMaterials]
>> > ...
>> >
>> > Hope this helps,
>> >
>> > Ben Nevarez
>> > Senior Database Administrator
>> > AIG SunAmerica
>> >
>> >
>> >
>> > "James Simpson" wrote:
>> >
>> >> Hello,
>> >> Very quick question (I hope) regarding SQL Server:
>> >> I want to create a query that basically says that if a stored
>> >> procedure
>> >> (say
>> >> sproc_foo) does not exist, then create the stored procedure. I know
>> >> that
>> >> there are easy ways to do this with creating tables/databases if they
>> >> don't
>> >> exist, but how is this possible with stored procedures?
>> >>
>> >> Regards,
>> >>
>> >> James Simpson
>> >> Straightway Technologies Inc.
>> >>
>>|||Russell, I think you were right. Thanks for the additional explanation.
I also got the code using the SQL Server Scripts Wizard and the 'Include if
NOT EXISTS' option turned on. The problem was that I replaced EXEC
dbo.sp_executesql with CREATE PROCEDURE to make the code easier to read and
understand.
Thanks,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"Russell Fields" wrote:
> Ben, OK. Sorry. I was just trying to emphasize that the 'natural' way will
> not work as expected. - RLF
> "Ben Nevarez" <BenNevarez@.discussions.microsoft.com> wrote in message
> news:3C07E41D-E259-4887-ABCE-2C403FD6929A@.microsoft.com...
> >
> > Hi Russell,
> >
> > I agree with your note but that was not intended to be a final and
> > complete
> > solution and my primary purpose was to show the IF NOT EXISTS part.
> >
> > Perhaps it is like saying that your code does not work as is either,
> > because
> > you have BEGIN but END is missing :-)
> >
> > Ben Nevarez
> > Senior Database Administrator
> > AIG SunAmerica
> >
> >
> >
> > "Russell Fields" wrote:
> >
> >> Ben,
> >>
> >> Alas, that will not work as is, since CREATE PROCEDURE must be the first
> >> statement in a batch. Scripting from SQL Server 2005 with the If Not
> >> Exists
> >> check turned on produces:
> >>
> >> IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id => >> OBJECT_ID(N'[dbo].[MyProc]') AND type in (N'P', N'PC'))
> >> BEGIN
> >> EXEC dbo.sp_executesql @.statement = N'
> >> CREATE PROC [dbo].[MyProc]
> >> @.Parm1 char(6)
> >> AS
> >> -- Do Something'
> >>
> >> So, as you can see it is a 'dynamic SQL' implementation. (I believe that
> >> there is a CONNECT request for something better than this.)
> >>
> >> RLF
> >>
> >>
> >> "Ben Nevarez" <BenNevarez@.discussions.microsoft.com> wrote in message
> >> news:36E61DF6-EBAB-45E8-B0E5-A91610CA5D46@.microsoft.com...
> >> >
> >> > Hi James,
> >> >
> >> > You can try something like this
> >> >
> >> > IF NOT EXISTS (SELECT * FROM sys.objects
> >> > WHERE object_id = OBJECT_ID(N'[dbo].[uspGetBillOfMaterials]')
> >> > AND type in (N'P', N'PC'))
> >> > BEGIN
> >> > CREATE PROCEDURE [dbo].[uspGetBillOfMaterials]
> >> > ...
> >> >
> >> > Hope this helps,
> >> >
> >> > Ben Nevarez
> >> > Senior Database Administrator
> >> > AIG SunAmerica
> >> >
> >> >
> >> >
> >> > "James Simpson" wrote:
> >> >
> >> >> Hello,
> >> >> Very quick question (I hope) regarding SQL Server:
> >> >> I want to create a query that basically says that if a stored
> >> >> procedure
> >> >> (say
> >> >> sproc_foo) does not exist, then create the stored procedure. I know
> >> >> that
> >> >> there are easy ways to do this with creating tables/databases if they
> >> >> don't
> >> >> exist, but how is this possible with stored procedures?
> >> >>
> >> >> Regards,
> >> >>
> >> >> James Simpson
> >> >> Straightway Technologies Inc.
> >> >>
> >>
> >>
> >>
>
>
Very quick question (I hope) regarding SQL Server:
I want to create a query that basically says that if a stored procedure (say
sproc_foo) does not exist, then create the stored procedure. I know that
there are easy ways to do this with creating tables/databases if they don't
exist, but how is this possible with stored procedures?
Regards,
James Simpson
Straightway Technologies Inc.Hi James,
You can try something like this
IF NOT EXISTS (SELECT * FROM sys.objects
WHERE object_id = OBJECT_ID(N'[dbo].[uspGetBillOfMaterials]')
AND type in (N'P', N'PC'))
BEGIN
CREATE PROCEDURE [dbo].[uspGetBillOfMaterials]
...
Hope this helps,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"James Simpson" wrote:
> Hello,
> Very quick question (I hope) regarding SQL Server:
> I want to create a query that basically says that if a stored procedure (say
> sproc_foo) does not exist, then create the stored procedure. I know that
> there are easy ways to do this with creating tables/databases if they don't
> exist, but how is this possible with stored procedures?
> Regards,
> James Simpson
> Straightway Technologies Inc.
>|||Ben,
Alas, that will not work as is, since CREATE PROCEDURE must be the first
statement in a batch. Scripting from SQL Server 2005 with the If Not Exists
check turned on produces:
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id =OBJECT_ID(N'[dbo].[MyProc]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @.statement = N'
CREATE PROC [dbo].[MyProc]
@.Parm1 char(6)
AS
-- Do Something'
So, as you can see it is a 'dynamic SQL' implementation. (I believe that
there is a CONNECT request for something better than this.)
RLF
"Ben Nevarez" <BenNevarez@.discussions.microsoft.com> wrote in message
news:36E61DF6-EBAB-45E8-B0E5-A91610CA5D46@.microsoft.com...
> Hi James,
> You can try something like this
> IF NOT EXISTS (SELECT * FROM sys.objects
> WHERE object_id = OBJECT_ID(N'[dbo].[uspGetBillOfMaterials]')
> AND type in (N'P', N'PC'))
> BEGIN
> CREATE PROCEDURE [dbo].[uspGetBillOfMaterials]
> ...
> Hope this helps,
> Ben Nevarez
> Senior Database Administrator
> AIG SunAmerica
>
> "James Simpson" wrote:
>> Hello,
>> Very quick question (I hope) regarding SQL Server:
>> I want to create a query that basically says that if a stored procedure
>> (say
>> sproc_foo) does not exist, then create the stored procedure. I know that
>> there are easy ways to do this with creating tables/databases if they
>> don't
>> exist, but how is this possible with stored procedures?
>> Regards,
>> James Simpson
>> Straightway Technologies Inc.|||Dear Ben/Russel,
Ben - I tried the solution you gave and for whatever reason it does not
appear to work. I have also seen this type of example online and this query
fails miserably on SQL Server 2005 Express. Russel, the solution you gave
works perfectly, and hopefully Microsoft resolves this issue.
Regards,
James Simpson
Straightway Technologies Inc.|||Hi Russell,
I agree with your note but that was not intended to be a final and complete
solution and my primary purpose was to show the IF NOT EXISTS part.
Perhaps it is like saying that your code does not work as is either, because
you have BEGIN but END is missing :-)
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"Russell Fields" wrote:
> Ben,
> Alas, that will not work as is, since CREATE PROCEDURE must be the first
> statement in a batch. Scripting from SQL Server 2005 with the If Not Exists
> check turned on produces:
> IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id => OBJECT_ID(N'[dbo].[MyProc]') AND type in (N'P', N'PC'))
> BEGIN
> EXEC dbo.sp_executesql @.statement = N'
> CREATE PROC [dbo].[MyProc]
> @.Parm1 char(6)
> AS
> -- Do Something'
> So, as you can see it is a 'dynamic SQL' implementation. (I believe that
> there is a CONNECT request for something better than this.)
> RLF
>
> "Ben Nevarez" <BenNevarez@.discussions.microsoft.com> wrote in message
> news:36E61DF6-EBAB-45E8-B0E5-A91610CA5D46@.microsoft.com...
> >
> > Hi James,
> >
> > You can try something like this
> >
> > IF NOT EXISTS (SELECT * FROM sys.objects
> > WHERE object_id = OBJECT_ID(N'[dbo].[uspGetBillOfMaterials]')
> > AND type in (N'P', N'PC'))
> > BEGIN
> > CREATE PROCEDURE [dbo].[uspGetBillOfMaterials]
> > ...
> >
> > Hope this helps,
> >
> > Ben Nevarez
> > Senior Database Administrator
> > AIG SunAmerica
> >
> >
> >
> > "James Simpson" wrote:
> >
> >> Hello,
> >> Very quick question (I hope) regarding SQL Server:
> >> I want to create a query that basically says that if a stored procedure
> >> (say
> >> sproc_foo) does not exist, then create the stored procedure. I know that
> >> there are easy ways to do this with creating tables/databases if they
> >> don't
> >> exist, but how is this possible with stored procedures?
> >>
> >> Regards,
> >>
> >> James Simpson
> >> Straightway Technologies Inc.
> >>
>
>|||Ben, OK. Sorry. I was just trying to emphasize that the 'natural' way will
not work as expected. - RLF
"Ben Nevarez" <BenNevarez@.discussions.microsoft.com> wrote in message
news:3C07E41D-E259-4887-ABCE-2C403FD6929A@.microsoft.com...
> Hi Russell,
> I agree with your note but that was not intended to be a final and
> complete
> solution and my primary purpose was to show the IF NOT EXISTS part.
> Perhaps it is like saying that your code does not work as is either,
> because
> you have BEGIN but END is missing :-)
> Ben Nevarez
> Senior Database Administrator
> AIG SunAmerica
>
> "Russell Fields" wrote:
>> Ben,
>> Alas, that will not work as is, since CREATE PROCEDURE must be the first
>> statement in a batch. Scripting from SQL Server 2005 with the If Not
>> Exists
>> check turned on produces:
>> IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id =>> OBJECT_ID(N'[dbo].[MyProc]') AND type in (N'P', N'PC'))
>> BEGIN
>> EXEC dbo.sp_executesql @.statement = N'
>> CREATE PROC [dbo].[MyProc]
>> @.Parm1 char(6)
>> AS
>> -- Do Something'
>> So, as you can see it is a 'dynamic SQL' implementation. (I believe that
>> there is a CONNECT request for something better than this.)
>> RLF
>>
>> "Ben Nevarez" <BenNevarez@.discussions.microsoft.com> wrote in message
>> news:36E61DF6-EBAB-45E8-B0E5-A91610CA5D46@.microsoft.com...
>> >
>> > Hi James,
>> >
>> > You can try something like this
>> >
>> > IF NOT EXISTS (SELECT * FROM sys.objects
>> > WHERE object_id = OBJECT_ID(N'[dbo].[uspGetBillOfMaterials]')
>> > AND type in (N'P', N'PC'))
>> > BEGIN
>> > CREATE PROCEDURE [dbo].[uspGetBillOfMaterials]
>> > ...
>> >
>> > Hope this helps,
>> >
>> > Ben Nevarez
>> > Senior Database Administrator
>> > AIG SunAmerica
>> >
>> >
>> >
>> > "James Simpson" wrote:
>> >
>> >> Hello,
>> >> Very quick question (I hope) regarding SQL Server:
>> >> I want to create a query that basically says that if a stored
>> >> procedure
>> >> (say
>> >> sproc_foo) does not exist, then create the stored procedure. I know
>> >> that
>> >> there are easy ways to do this with creating tables/databases if they
>> >> don't
>> >> exist, but how is this possible with stored procedures?
>> >>
>> >> Regards,
>> >>
>> >> James Simpson
>> >> Straightway Technologies Inc.
>> >>
>>|||Russell, I think you were right. Thanks for the additional explanation.
I also got the code using the SQL Server Scripts Wizard and the 'Include if
NOT EXISTS' option turned on. The problem was that I replaced EXEC
dbo.sp_executesql with CREATE PROCEDURE to make the code easier to read and
understand.
Thanks,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"Russell Fields" wrote:
> Ben, OK. Sorry. I was just trying to emphasize that the 'natural' way will
> not work as expected. - RLF
> "Ben Nevarez" <BenNevarez@.discussions.microsoft.com> wrote in message
> news:3C07E41D-E259-4887-ABCE-2C403FD6929A@.microsoft.com...
> >
> > Hi Russell,
> >
> > I agree with your note but that was not intended to be a final and
> > complete
> > solution and my primary purpose was to show the IF NOT EXISTS part.
> >
> > Perhaps it is like saying that your code does not work as is either,
> > because
> > you have BEGIN but END is missing :-)
> >
> > Ben Nevarez
> > Senior Database Administrator
> > AIG SunAmerica
> >
> >
> >
> > "Russell Fields" wrote:
> >
> >> Ben,
> >>
> >> Alas, that will not work as is, since CREATE PROCEDURE must be the first
> >> statement in a batch. Scripting from SQL Server 2005 with the If Not
> >> Exists
> >> check turned on produces:
> >>
> >> IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id => >> OBJECT_ID(N'[dbo].[MyProc]') AND type in (N'P', N'PC'))
> >> BEGIN
> >> EXEC dbo.sp_executesql @.statement = N'
> >> CREATE PROC [dbo].[MyProc]
> >> @.Parm1 char(6)
> >> AS
> >> -- Do Something'
> >>
> >> So, as you can see it is a 'dynamic SQL' implementation. (I believe that
> >> there is a CONNECT request for something better than this.)
> >>
> >> RLF
> >>
> >>
> >> "Ben Nevarez" <BenNevarez@.discussions.microsoft.com> wrote in message
> >> news:36E61DF6-EBAB-45E8-B0E5-A91610CA5D46@.microsoft.com...
> >> >
> >> > Hi James,
> >> >
> >> > You can try something like this
> >> >
> >> > IF NOT EXISTS (SELECT * FROM sys.objects
> >> > WHERE object_id = OBJECT_ID(N'[dbo].[uspGetBillOfMaterials]')
> >> > AND type in (N'P', N'PC'))
> >> > BEGIN
> >> > CREATE PROCEDURE [dbo].[uspGetBillOfMaterials]
> >> > ...
> >> >
> >> > Hope this helps,
> >> >
> >> > Ben Nevarez
> >> > Senior Database Administrator
> >> > AIG SunAmerica
> >> >
> >> >
> >> >
> >> > "James Simpson" wrote:
> >> >
> >> >> Hello,
> >> >> Very quick question (I hope) regarding SQL Server:
> >> >> I want to create a query that basically says that if a stored
> >> >> procedure
> >> >> (say
> >> >> sproc_foo) does not exist, then create the stored procedure. I know
> >> >> that
> >> >> there are easy ways to do this with creating tables/databases if they
> >> >> don't
> >> >> exist, but how is this possible with stored procedures?
> >> >>
> >> >> Regards,
> >> >>
> >> >> James Simpson
> >> >> Straightway Technologies Inc.
> >> >>
> >>
> >>
> >>
>
>
Regarding Creating Stored Procedures If One Does Not Exist
Hello,
Very quick question (I hope) regarding SQL Server:
I want to create a query that basically says that if a stored procedure (say
sproc_foo) does not exist, then create the stored procedure. I know that
there are easy ways to do this with creating tables/databases if they don't
exist, but how is this possible with stored procedures?
Regards,
James Simpson
Straightway Technologies Inc.
Hi James,
You can try something like this
IF NOT EXISTS (SELECT * FROM sys.objects
WHERE object_id = OBJECT_ID(N'[dbo].[uspGetBillOfMaterials]')
AND type in (N'P', N'PC'))
BEGIN
CREATE PROCEDURE [dbo].[uspGetBillOfMaterials]
...
Hope this helps,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"James Simpson" wrote:
> Hello,
> Very quick question (I hope) regarding SQL Server:
> I want to create a query that basically says that if a stored procedure (say
> sproc_foo) does not exist, then create the stored procedure. I know that
> there are easy ways to do this with creating tables/databases if they don't
> exist, but how is this possible with stored procedures?
> Regards,
> James Simpson
> Straightway Technologies Inc.
>
|||Ben,
Alas, that will not work as is, since CREATE PROCEDURE must be the first
statement in a batch. Scripting from SQL Server 2005 with the If Not Exists
check turned on produces:
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id =
OBJECT_ID(N'[dbo].[MyProc]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @.statement = N'
CREATE PROC [dbo].[MyProc]
@.Parm1 char(6)
AS
-- Do Something'
So, as you can see it is a 'dynamic SQL' implementation. (I believe that
there is a CONNECT request for something better than this.)
RLF
"Ben Nevarez" <BenNevarez@.discussions.microsoft.com> wrote in message
news:36E61DF6-EBAB-45E8-B0E5-A91610CA5D46@.microsoft.com...[vbcol=seagreen]
> Hi James,
> You can try something like this
> IF NOT EXISTS (SELECT * FROM sys.objects
> WHERE object_id = OBJECT_ID(N'[dbo].[uspGetBillOfMaterials]')
> AND type in (N'P', N'PC'))
> BEGIN
> CREATE PROCEDURE [dbo].[uspGetBillOfMaterials]
> ...
> Hope this helps,
> Ben Nevarez
> Senior Database Administrator
> AIG SunAmerica
>
> "James Simpson" wrote:
|||Dear Ben/Russel,
Ben - I tried the solution you gave and for whatever reason it does not
appear to work. I have also seen this type of example online and this query
fails miserably on SQL Server 2005 Express. Russel, the solution you gave
works perfectly, and hopefully Microsoft resolves this issue.
Regards,
James Simpson
Straightway Technologies Inc.
|||Hi Russell,
I agree with your note but that was not intended to be a final and complete
solution and my primary purpose was to show the IF NOT EXISTS part.
Perhaps it is like saying that your code does not work as is either, because
you have BEGIN but END is missing :-)
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"Russell Fields" wrote:
> Ben,
> Alas, that will not work as is, since CREATE PROCEDURE must be the first
> statement in a batch. Scripting from SQL Server 2005 with the If Not Exists
> check turned on produces:
> IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id =
> OBJECT_ID(N'[dbo].[MyProc]') AND type in (N'P', N'PC'))
> BEGIN
> EXEC dbo.sp_executesql @.statement = N'
> CREATE PROC [dbo].[MyProc]
> @.Parm1 char(6)
> AS
> -- Do Something'
> So, as you can see it is a 'dynamic SQL' implementation. (I believe that
> there is a CONNECT request for something better than this.)
> RLF
>
> "Ben Nevarez" <BenNevarez@.discussions.microsoft.com> wrote in message
> news:36E61DF6-EBAB-45E8-B0E5-A91610CA5D46@.microsoft.com...
>
>
|||Ben, OK. Sorry. I was just trying to emphasize that the 'natural' way will
not work as expected. - RLF
"Ben Nevarez" <BenNevarez@.discussions.microsoft.com> wrote in message
news:3C07E41D-E259-4887-ABCE-2C403FD6929A@.microsoft.com...[vbcol=seagreen]
> Hi Russell,
> I agree with your note but that was not intended to be a final and
> complete
> solution and my primary purpose was to show the IF NOT EXISTS part.
> Perhaps it is like saying that your code does not work as is either,
> because
> you have BEGIN but END is missing :-)
> Ben Nevarez
> Senior Database Administrator
> AIG SunAmerica
>
> "Russell Fields" wrote:
|||Russell, I think you were right. Thanks for the additional explanation.
I also got the code using the SQL Server Scripts Wizard and the 'Include if
NOT EXISTS' option turned on. The problem was that I replaced EXEC
dbo.sp_executesql with CREATE PROCEDURE to make the code easier to read and
understand.
Thanks,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"Russell Fields" wrote:
> Ben, OK. Sorry. I was just trying to emphasize that the 'natural' way will
> not work as expected. - RLF
> "Ben Nevarez" <BenNevarez@.discussions.microsoft.com> wrote in message
> news:3C07E41D-E259-4887-ABCE-2C403FD6929A@.microsoft.com...
>
>
Very quick question (I hope) regarding SQL Server:
I want to create a query that basically says that if a stored procedure (say
sproc_foo) does not exist, then create the stored procedure. I know that
there are easy ways to do this with creating tables/databases if they don't
exist, but how is this possible with stored procedures?
Regards,
James Simpson
Straightway Technologies Inc.
Hi James,
You can try something like this
IF NOT EXISTS (SELECT * FROM sys.objects
WHERE object_id = OBJECT_ID(N'[dbo].[uspGetBillOfMaterials]')
AND type in (N'P', N'PC'))
BEGIN
CREATE PROCEDURE [dbo].[uspGetBillOfMaterials]
...
Hope this helps,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"James Simpson" wrote:
> Hello,
> Very quick question (I hope) regarding SQL Server:
> I want to create a query that basically says that if a stored procedure (say
> sproc_foo) does not exist, then create the stored procedure. I know that
> there are easy ways to do this with creating tables/databases if they don't
> exist, but how is this possible with stored procedures?
> Regards,
> James Simpson
> Straightway Technologies Inc.
>
|||Ben,
Alas, that will not work as is, since CREATE PROCEDURE must be the first
statement in a batch. Scripting from SQL Server 2005 with the If Not Exists
check turned on produces:
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id =
OBJECT_ID(N'[dbo].[MyProc]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @.statement = N'
CREATE PROC [dbo].[MyProc]
@.Parm1 char(6)
AS
-- Do Something'
So, as you can see it is a 'dynamic SQL' implementation. (I believe that
there is a CONNECT request for something better than this.)
RLF
"Ben Nevarez" <BenNevarez@.discussions.microsoft.com> wrote in message
news:36E61DF6-EBAB-45E8-B0E5-A91610CA5D46@.microsoft.com...[vbcol=seagreen]
> Hi James,
> You can try something like this
> IF NOT EXISTS (SELECT * FROM sys.objects
> WHERE object_id = OBJECT_ID(N'[dbo].[uspGetBillOfMaterials]')
> AND type in (N'P', N'PC'))
> BEGIN
> CREATE PROCEDURE [dbo].[uspGetBillOfMaterials]
> ...
> Hope this helps,
> Ben Nevarez
> Senior Database Administrator
> AIG SunAmerica
>
> "James Simpson" wrote:
|||Dear Ben/Russel,
Ben - I tried the solution you gave and for whatever reason it does not
appear to work. I have also seen this type of example online and this query
fails miserably on SQL Server 2005 Express. Russel, the solution you gave
works perfectly, and hopefully Microsoft resolves this issue.
Regards,
James Simpson
Straightway Technologies Inc.
|||Hi Russell,
I agree with your note but that was not intended to be a final and complete
solution and my primary purpose was to show the IF NOT EXISTS part.
Perhaps it is like saying that your code does not work as is either, because
you have BEGIN but END is missing :-)
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"Russell Fields" wrote:
> Ben,
> Alas, that will not work as is, since CREATE PROCEDURE must be the first
> statement in a batch. Scripting from SQL Server 2005 with the If Not Exists
> check turned on produces:
> IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id =
> OBJECT_ID(N'[dbo].[MyProc]') AND type in (N'P', N'PC'))
> BEGIN
> EXEC dbo.sp_executesql @.statement = N'
> CREATE PROC [dbo].[MyProc]
> @.Parm1 char(6)
> AS
> -- Do Something'
> So, as you can see it is a 'dynamic SQL' implementation. (I believe that
> there is a CONNECT request for something better than this.)
> RLF
>
> "Ben Nevarez" <BenNevarez@.discussions.microsoft.com> wrote in message
> news:36E61DF6-EBAB-45E8-B0E5-A91610CA5D46@.microsoft.com...
>
>
|||Ben, OK. Sorry. I was just trying to emphasize that the 'natural' way will
not work as expected. - RLF
"Ben Nevarez" <BenNevarez@.discussions.microsoft.com> wrote in message
news:3C07E41D-E259-4887-ABCE-2C403FD6929A@.microsoft.com...[vbcol=seagreen]
> Hi Russell,
> I agree with your note but that was not intended to be a final and
> complete
> solution and my primary purpose was to show the IF NOT EXISTS part.
> Perhaps it is like saying that your code does not work as is either,
> because
> you have BEGIN but END is missing :-)
> Ben Nevarez
> Senior Database Administrator
> AIG SunAmerica
>
> "Russell Fields" wrote:
|||Russell, I think you were right. Thanks for the additional explanation.
I also got the code using the SQL Server Scripts Wizard and the 'Include if
NOT EXISTS' option turned on. The problem was that I replaced EXEC
dbo.sp_executesql with CREATE PROCEDURE to make the code easier to read and
understand.
Thanks,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"Russell Fields" wrote:
> Ben, OK. Sorry. I was just trying to emphasize that the 'natural' way will
> not work as expected. - RLF
> "Ben Nevarez" <BenNevarez@.discussions.microsoft.com> wrote in message
> news:3C07E41D-E259-4887-ABCE-2C403FD6929A@.microsoft.com...
>
>
Regarding Creating Stored Procedures If One Does Not Exist
Hello,
Very quick question (I hope) regarding SQL Server:
I want to create a query that basically says that if a stored procedure (say
sproc_foo) does not exist, then create the stored procedure. I know that
there are easy ways to do this with creating tables/databases if they don't
exist, but how is this possible with stored procedures?
Regards,
James Simpson
Straightway Technologies Inc.Hi James,
You can try something like this
IF NOT EXISTS (SELECT * FROM sys.objects
WHERE object_id = OBJECT_ID(N'[dbo].[uspGetBillOfMaterials]')
AND type in (N'P', N'PC'))
BEGIN
CREATE PROCEDURE [dbo].[uspGetBillOfMaterials]
...
Hope this helps,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"James Simpson" wrote:
> Hello,
> Very quick question (I hope) regarding SQL Server:
> I want to create a query that basically says that if a stored procedure (s
ay
> sproc_foo) does not exist, then create the stored procedure. I know that
> there are easy ways to do this with creating tables/databases if they don'
t
> exist, but how is this possible with stored procedures?
> Regards,
> James Simpson
> Straightway Technologies Inc.
>|||Ben,
Alas, that will not work as is, since CREATE PROCEDURE must be the first
statement in a batch. Scripting from SQL Server 2005 with the If Not Exists
check turned on produces:
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id =
OBJECT_ID(N'[dbo].[MyProc]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @.statement = N'
CREATE PROC [dbo].[MyProc]
@.Parm1 char(6)
AS
-- Do Something'
So, as you can see it is a 'dynamic SQL' implementation. (I believe that
there is a CONNECT request for something better than this.)
RLF
"Ben Nevarez" <BenNevarez@.discussions.microsoft.com> wrote in message
news:36E61DF6-EBAB-45E8-B0E5-A91610CA5D46@.microsoft.com...[vbcol=seagreen]
> Hi James,
> You can try something like this
> IF NOT EXISTS (SELECT * FROM sys.objects
> WHERE object_id = OBJECT_ID(N'[dbo].[uspGetBillOfMaterials]')
> AND type in (N'P', N'PC'))
> BEGIN
> CREATE PROCEDURE [dbo].[uspGetBillOfMaterials]
> ...
> Hope this helps,
> Ben Nevarez
> Senior Database Administrator
> AIG SunAmerica
>
> "James Simpson" wrote:
>|||Dear Ben/Russel,
Ben - I tried the solution you gave and for whatever reason it does not
appear to work. I have also seen this type of example online and this query
fails miserably on SQL Server 2005 Express. Russel, the solution you gave
works perfectly, and hopefully Microsoft resolves this issue.
Regards,
James Simpson
Straightway Technologies Inc.|||Hi Russell,
I agree with your note but that was not intended to be a final and complete
solution and my primary purpose was to show the IF NOT EXISTS part.
Perhaps it is like saying that your code does not work as is either, because
you have BEGIN but END is missing :-)
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"Russell Fields" wrote:
> Ben,
> Alas, that will not work as is, since CREATE PROCEDURE must be the first
> statement in a batch. Scripting from SQL Server 2005 with the If Not Exis
ts
> check turned on produces:
> IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id =
> OBJECT_ID(N'[dbo].[MyProc]') AND type in (N'P', N'PC'))
> BEGIN
> EXEC dbo.sp_executesql @.statement = N'
> CREATE PROC [dbo].[MyProc]
> @.Parm1 char(6)
> AS
> -- Do Something'
> So, as you can see it is a 'dynamic SQL' implementation. (I believe that
> there is a CONNECT request for something better than this.)
> RLF
>
> "Ben Nevarez" <BenNevarez@.discussions.microsoft.com> wrote in message
> news:36E61DF6-EBAB-45E8-B0E5-A91610CA5D46@.microsoft.com...
>
>|||Ben, OK. Sorry. I was just trying to emphasize that the 'natural' way will
not work as expected. - RLF
"Ben Nevarez" <BenNevarez@.discussions.microsoft.com> wrote in message
news:3C07E41D-E259-4887-ABCE-2C403FD6929A@.microsoft.com...[vbcol=seagreen]
> Hi Russell,
> I agree with your note but that was not intended to be a final and
> complete
> solution and my primary purpose was to show the IF NOT EXISTS part.
> Perhaps it is like saying that your code does not work as is either,
> because
> you have BEGIN but END is missing :-)
> Ben Nevarez
> Senior Database Administrator
> AIG SunAmerica
>
> "Russell Fields" wrote:
>|||Russell, I think you were right. Thanks for the additional explanation.
I also got the code using the SQL Server Scripts Wizard and the 'Include if
NOT EXISTS' option turned on. The problem was that I replaced EXEC
dbo.sp_executesql with CREATE PROCEDURE to make the code easier to read and
understand.
Thanks,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"Russell Fields" wrote:
> Ben, OK. Sorry. I was just trying to emphasize that the 'natural' way wil
l
> not work as expected. - RLF
> "Ben Nevarez" <BenNevarez@.discussions.microsoft.com> wrote in message
> news:3C07E41D-E259-4887-ABCE-2C403FD6929A@.microsoft.com...
>
>sql
Very quick question (I hope) regarding SQL Server:
I want to create a query that basically says that if a stored procedure (say
sproc_foo) does not exist, then create the stored procedure. I know that
there are easy ways to do this with creating tables/databases if they don't
exist, but how is this possible with stored procedures?
Regards,
James Simpson
Straightway Technologies Inc.Hi James,
You can try something like this
IF NOT EXISTS (SELECT * FROM sys.objects
WHERE object_id = OBJECT_ID(N'[dbo].[uspGetBillOfMaterials]')
AND type in (N'P', N'PC'))
BEGIN
CREATE PROCEDURE [dbo].[uspGetBillOfMaterials]
...
Hope this helps,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"James Simpson" wrote:
> Hello,
> Very quick question (I hope) regarding SQL Server:
> I want to create a query that basically says that if a stored procedure (s
ay
> sproc_foo) does not exist, then create the stored procedure. I know that
> there are easy ways to do this with creating tables/databases if they don'
t
> exist, but how is this possible with stored procedures?
> Regards,
> James Simpson
> Straightway Technologies Inc.
>|||Ben,
Alas, that will not work as is, since CREATE PROCEDURE must be the first
statement in a batch. Scripting from SQL Server 2005 with the If Not Exists
check turned on produces:
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id =
OBJECT_ID(N'[dbo].[MyProc]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @.statement = N'
CREATE PROC [dbo].[MyProc]
@.Parm1 char(6)
AS
-- Do Something'
So, as you can see it is a 'dynamic SQL' implementation. (I believe that
there is a CONNECT request for something better than this.)
RLF
"Ben Nevarez" <BenNevarez@.discussions.microsoft.com> wrote in message
news:36E61DF6-EBAB-45E8-B0E5-A91610CA5D46@.microsoft.com...[vbcol=seagreen]
> Hi James,
> You can try something like this
> IF NOT EXISTS (SELECT * FROM sys.objects
> WHERE object_id = OBJECT_ID(N'[dbo].[uspGetBillOfMaterials]')
> AND type in (N'P', N'PC'))
> BEGIN
> CREATE PROCEDURE [dbo].[uspGetBillOfMaterials]
> ...
> Hope this helps,
> Ben Nevarez
> Senior Database Administrator
> AIG SunAmerica
>
> "James Simpson" wrote:
>|||Dear Ben/Russel,
Ben - I tried the solution you gave and for whatever reason it does not
appear to work. I have also seen this type of example online and this query
fails miserably on SQL Server 2005 Express. Russel, the solution you gave
works perfectly, and hopefully Microsoft resolves this issue.
Regards,
James Simpson
Straightway Technologies Inc.|||Hi Russell,
I agree with your note but that was not intended to be a final and complete
solution and my primary purpose was to show the IF NOT EXISTS part.
Perhaps it is like saying that your code does not work as is either, because
you have BEGIN but END is missing :-)
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"Russell Fields" wrote:
> Ben,
> Alas, that will not work as is, since CREATE PROCEDURE must be the first
> statement in a batch. Scripting from SQL Server 2005 with the If Not Exis
ts
> check turned on produces:
> IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id =
> OBJECT_ID(N'[dbo].[MyProc]') AND type in (N'P', N'PC'))
> BEGIN
> EXEC dbo.sp_executesql @.statement = N'
> CREATE PROC [dbo].[MyProc]
> @.Parm1 char(6)
> AS
> -- Do Something'
> So, as you can see it is a 'dynamic SQL' implementation. (I believe that
> there is a CONNECT request for something better than this.)
> RLF
>
> "Ben Nevarez" <BenNevarez@.discussions.microsoft.com> wrote in message
> news:36E61DF6-EBAB-45E8-B0E5-A91610CA5D46@.microsoft.com...
>
>|||Ben, OK. Sorry. I was just trying to emphasize that the 'natural' way will
not work as expected. - RLF
"Ben Nevarez" <BenNevarez@.discussions.microsoft.com> wrote in message
news:3C07E41D-E259-4887-ABCE-2C403FD6929A@.microsoft.com...[vbcol=seagreen]
> Hi Russell,
> I agree with your note but that was not intended to be a final and
> complete
> solution and my primary purpose was to show the IF NOT EXISTS part.
> Perhaps it is like saying that your code does not work as is either,
> because
> you have BEGIN but END is missing :-)
> Ben Nevarez
> Senior Database Administrator
> AIG SunAmerica
>
> "Russell Fields" wrote:
>|||Russell, I think you were right. Thanks for the additional explanation.
I also got the code using the SQL Server Scripts Wizard and the 'Include if
NOT EXISTS' option turned on. The problem was that I replaced EXEC
dbo.sp_executesql with CREATE PROCEDURE to make the code easier to read and
understand.
Thanks,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"Russell Fields" wrote:
> Ben, OK. Sorry. I was just trying to emphasize that the 'natural' way wil
l
> not work as expected. - RLF
> "Ben Nevarez" <BenNevarez@.discussions.microsoft.com> wrote in message
> news:3C07E41D-E259-4887-ABCE-2C403FD6929A@.microsoft.com...
>
>sql
Regarding Collation for mirroring & Error 1418
Can I know is it possible that if the database servers' collation in both principal and mirror server might unable to get the mirroring? Because one of database collation is Latin1_General_CI_AS while another server (mirror) is SQL_Latin1_General_CP1_CI_AS. I did several type of troubleshooting but still unable to solve the Error 1418. All the ports are stated as started, able to use telnet to connect to the server's port but still unable to get a connection for the database mirroring. I do really need help on it. Thx for the assistance and kindness.
Best Regards,
Hans
Hello Hans,
Database mirroring is the copy of your data syncronous or assyncronous, when you do the copy of data between database with collation different occurs the error: Cannot resolve the collation conflict.
I believe your error is the same.
Try build the database mirroring with the same Collation on Source and destination.
For start the database mirroring you need restore the database in destination with the NORECOVERY option.
Good Luck!
Regards,
|||Hello Fernandes,
Thanx for the reply and your information. I guess I forgot to give an useful information regarding this issue. Actually the database collation that I mention is wrong , suppose to be the server's collation (inside server properties). Both servers' collation are different which are Latin1_General_CI_AS and SQL_Latin1_General_CP1_CI_AS. However, the database (AdventureWorks) that used for mirroring is the same collation for principal and mirror. Is there any conflict as well ? I did try on using NORECOVERY when I restored it into the mirror server. All the steps I just follow which stated in BOL. Can we discuss through msn messenger anyway? Hope to hear from you soon again. Thx again and have a nice day.
From,
Hans
|||Hello Hans,
Server's collation different not problem, because database mirroring work inside the database.
Both SQL Server running with same domain account?
Do you restore your database in mirror with the NORECOVERY option?
Can we discuss through msn messenger anyway?
Ok, send invite for me: lobo-fernandes@.hotmail.com
But I live in Rio de Janeiro - Brazil.
Where do you live? What your timezone?
Regards,
|||
Oic, means Collation will not causing the problem then. Domain account? Is it 'Log on as ' Local Systemn, Local Service and Network Service?
Yup , the database that i restored in mirror database is usinfg the NORECOVERY option. Ok , let's discuss through msn then. I already added u into my msn list. I am from Malaysia (Asia) , GMT+8. Hope able to meet up with u thru msn then. See ya and have a nice day. :)
From,
Hans
Regarding BulkInsert
Hello,
I am trying to do this and i am getting this error..
create table #temptbl (rec varchar(284))
BULK INSERT #temptbl FROM 'z:\Testline.txt'
WITH ( DATAFILETYPE = 'char',
FIELDTERMINATOR = '\r\n',
CODEPAGE = 'RAW',
MAXERRORS = 10000,
FIRSTROW = 2)
Server: Msg 8104, Level 16, State 2, Line 3
The current user is not the database or object owner of table '#timberline'.
Cannot perform SET operation.
User has BulkAdmin rights and he is a dbo in the database.
Pls advise.
Thanks,
Message posted via http://www.droptable.com
> User has BulkAdmin rights and he is a dbo in the database.
This should work according to:
http://support.microsoft.com/default...b;en-us;302621
http://support.microsoft.com/default...b;en-us;243023
Are you certain the user is dbo or a db_owner role member? Please confirm
with SELECT USER, IS_MEMBER('db_owner') in the tempdb database. Tempdb is
recreated at startup so any permissions in that database are lost.
Hope this helps.
Dan Guzman
SQL Server MVP
"msqldba m via droptable.com" <u11604@.uwe> wrote in message
news:582f80513d042@.uwe...
> Hello,
> I am trying to do this and i am getting this error..
> create table #temptbl (rec varchar(284))
> BULK INSERT #temptbl FROM 'z:\Testline.txt'
> WITH ( DATAFILETYPE = 'char',
> FIELDTERMINATOR = '\r\n',
> CODEPAGE = 'RAW',
> MAXERRORS = 10000,
> FIRSTROW = 2)
> Server: Msg 8104, Level 16, State 2, Line 3
> The current user is not the database or object owner of table
> '#timberline'.
> Cannot perform SET operation.
>
> User has BulkAdmin rights and he is a dbo in the database.
> Pls advise.
> Thanks,
> --
> Message posted via http://www.droptable.com
I am trying to do this and i am getting this error..
create table #temptbl (rec varchar(284))
BULK INSERT #temptbl FROM 'z:\Testline.txt'
WITH ( DATAFILETYPE = 'char',
FIELDTERMINATOR = '\r\n',
CODEPAGE = 'RAW',
MAXERRORS = 10000,
FIRSTROW = 2)
Server: Msg 8104, Level 16, State 2, Line 3
The current user is not the database or object owner of table '#timberline'.
Cannot perform SET operation.
User has BulkAdmin rights and he is a dbo in the database.
Pls advise.
Thanks,
Message posted via http://www.droptable.com
> User has BulkAdmin rights and he is a dbo in the database.
This should work according to:
http://support.microsoft.com/default...b;en-us;302621
http://support.microsoft.com/default...b;en-us;243023
Are you certain the user is dbo or a db_owner role member? Please confirm
with SELECT USER, IS_MEMBER('db_owner') in the tempdb database. Tempdb is
recreated at startup so any permissions in that database are lost.
Hope this helps.
Dan Guzman
SQL Server MVP
"msqldba m via droptable.com" <u11604@.uwe> wrote in message
news:582f80513d042@.uwe...
> Hello,
> I am trying to do this and i am getting this error..
> create table #temptbl (rec varchar(284))
> BULK INSERT #temptbl FROM 'z:\Testline.txt'
> WITH ( DATAFILETYPE = 'char',
> FIELDTERMINATOR = '\r\n',
> CODEPAGE = 'RAW',
> MAXERRORS = 10000,
> FIRSTROW = 2)
> Server: Msg 8104, Level 16, State 2, Line 3
> The current user is not the database or object owner of table
> '#timberline'.
> Cannot perform SET operation.
>
> User has BulkAdmin rights and he is a dbo in the database.
> Pls advise.
> Thanks,
> --
> Message posted via http://www.droptable.com
Regarding BulkInsert
Hello,
I am trying to do this and i am getting this error..
create table #temptbl (rec varchar(284))
BULK INSERT #temptbl FROM 'z:\Testline.txt'
WITH ( DATAFILETYPE = 'char',
FIELDTERMINATOR = '\r\n',
CODEPAGE = 'RAW',
MAXERRORS = 10000,
FIRSTROW = 2)
Server: Msg 8104, Level 16, State 2, Line 3
The current user is not the database or object owner of table '#timberline'.
Cannot perform SET operation.
User has BulkAdmin rights and he is a dbo in the database.
Pls advise.
Thanks,
--
Message posted via http://www.sqlmonster.com> User has BulkAdmin rights and he is a dbo in the database.
This should work according to:
http://support.microsoft.com/default.aspx?scid=kb;en-us;302621
http://support.microsoft.com/default.aspx?scid=kb;en-us;243023
Are you certain the user is dbo or a db_owner role member? Please confirm
with SELECT USER, IS_MEMBER('db_owner') in the tempdb database. Tempdb is
recreated at startup so any permissions in that database are lost.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"msqldba m via SQLMonster.com" <u11604@.uwe> wrote in message
news:582f80513d042@.uwe...
> Hello,
> I am trying to do this and i am getting this error..
> create table #temptbl (rec varchar(284))
> BULK INSERT #temptbl FROM 'z:\Testline.txt'
> WITH ( DATAFILETYPE = 'char',
> FIELDTERMINATOR = '\r\n',
> CODEPAGE = 'RAW',
> MAXERRORS = 10000,
> FIRSTROW = 2)
> Server: Msg 8104, Level 16, State 2, Line 3
> The current user is not the database or object owner of table
> '#timberline'.
> Cannot perform SET operation.
>
> User has BulkAdmin rights and he is a dbo in the database.
> Pls advise.
> Thanks,
> --
> Message posted via http://www.sqlmonster.com
I am trying to do this and i am getting this error..
create table #temptbl (rec varchar(284))
BULK INSERT #temptbl FROM 'z:\Testline.txt'
WITH ( DATAFILETYPE = 'char',
FIELDTERMINATOR = '\r\n',
CODEPAGE = 'RAW',
MAXERRORS = 10000,
FIRSTROW = 2)
Server: Msg 8104, Level 16, State 2, Line 3
The current user is not the database or object owner of table '#timberline'.
Cannot perform SET operation.
User has BulkAdmin rights and he is a dbo in the database.
Pls advise.
Thanks,
--
Message posted via http://www.sqlmonster.com> User has BulkAdmin rights and he is a dbo in the database.
This should work according to:
http://support.microsoft.com/default.aspx?scid=kb;en-us;302621
http://support.microsoft.com/default.aspx?scid=kb;en-us;243023
Are you certain the user is dbo or a db_owner role member? Please confirm
with SELECT USER, IS_MEMBER('db_owner') in the tempdb database. Tempdb is
recreated at startup so any permissions in that database are lost.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"msqldba m via SQLMonster.com" <u11604@.uwe> wrote in message
news:582f80513d042@.uwe...
> Hello,
> I am trying to do this and i am getting this error..
> create table #temptbl (rec varchar(284))
> BULK INSERT #temptbl FROM 'z:\Testline.txt'
> WITH ( DATAFILETYPE = 'char',
> FIELDTERMINATOR = '\r\n',
> CODEPAGE = 'RAW',
> MAXERRORS = 10000,
> FIRSTROW = 2)
> Server: Msg 8104, Level 16, State 2, Line 3
> The current user is not the database or object owner of table
> '#timberline'.
> Cannot perform SET operation.
>
> User has BulkAdmin rights and he is a dbo in the database.
> Pls advise.
> Thanks,
> --
> Message posted via http://www.sqlmonster.com
Regarding BulkInsert
Hello,
I am trying to do this and i am getting this error..
create table #temptbl (rec varchar(284))
BULK INSERT #temptbl FROM 'z:\Testline.txt'
WITH ( DATAFILETYPE = 'char',
FIELDTERMINATOR = '\r\n',
CODEPAGE = 'RAW',
MAXERRORS = 10000,
FIRSTROW = 2)
Server: Msg 8104, Level 16, State 2, Line 3
The current user is not the database or object owner of table '#timberline'.
Cannot perform SET operation.
User has BulkAdmin rights and he is a dbo in the database.
Pls advise.
Thanks,
Message posted via http://www.droptable.com> User has BulkAdmin rights and he is a dbo in the database.
This should work according to:
http://support.microsoft.com/defaul...kb;en-us;302621
http://support.microsoft.com/defaul...kb;en-us;243023
Are you certain the user is dbo or a db_owner role member? Please confirm
with SELECT USER, IS_MEMBER('db_owner') in the tempdb database. Tempdb is
recreated at startup so any permissions in that database are lost.
Hope this helps.
Dan Guzman
SQL Server MVP
"msqldba m via droptable.com" <u11604@.uwe> wrote in message
news:582f80513d042@.uwe...
> Hello,
> I am trying to do this and i am getting this error..
> create table #temptbl (rec varchar(284))
> BULK INSERT #temptbl FROM 'z:\Testline.txt'
> WITH ( DATAFILETYPE = 'char',
> FIELDTERMINATOR = '\r\n',
> CODEPAGE = 'RAW',
> MAXERRORS = 10000,
> FIRSTROW = 2)
> Server: Msg 8104, Level 16, State 2, Line 3
> The current user is not the database or object owner of table
> '#timberline'.
> Cannot perform SET operation.
>
> User has BulkAdmin rights and he is a dbo in the database.
> Pls advise.
> Thanks,
> --
> Message posted via http://www.droptable.com
I am trying to do this and i am getting this error..
create table #temptbl (rec varchar(284))
BULK INSERT #temptbl FROM 'z:\Testline.txt'
WITH ( DATAFILETYPE = 'char',
FIELDTERMINATOR = '\r\n',
CODEPAGE = 'RAW',
MAXERRORS = 10000,
FIRSTROW = 2)
Server: Msg 8104, Level 16, State 2, Line 3
The current user is not the database or object owner of table '#timberline'.
Cannot perform SET operation.
User has BulkAdmin rights and he is a dbo in the database.
Pls advise.
Thanks,
Message posted via http://www.droptable.com> User has BulkAdmin rights and he is a dbo in the database.
This should work according to:
http://support.microsoft.com/defaul...kb;en-us;302621
http://support.microsoft.com/defaul...kb;en-us;243023
Are you certain the user is dbo or a db_owner role member? Please confirm
with SELECT USER, IS_MEMBER('db_owner') in the tempdb database. Tempdb is
recreated at startup so any permissions in that database are lost.
Hope this helps.
Dan Guzman
SQL Server MVP
"msqldba m via droptable.com" <u11604@.uwe> wrote in message
news:582f80513d042@.uwe...
> Hello,
> I am trying to do this and i am getting this error..
> create table #temptbl (rec varchar(284))
> BULK INSERT #temptbl FROM 'z:\Testline.txt'
> WITH ( DATAFILETYPE = 'char',
> FIELDTERMINATOR = '\r\n',
> CODEPAGE = 'RAW',
> MAXERRORS = 10000,
> FIRSTROW = 2)
> Server: Msg 8104, Level 16, State 2, Line 3
> The current user is not the database or object owner of table
> '#timberline'.
> Cannot perform SET operation.
>
> User has BulkAdmin rights and he is a dbo in the database.
> Pls advise.
> Thanks,
> --
> Message posted via http://www.droptable.com
Regarding Bulk Insert
Hello,
I have a DTS package where it does bulk inserts into multiple tables
it works fine and all of a sudden it throws a stack dump in the sql server
error
log with this error..
Error: 0, Severity: 19, State: 0
> SqlDumpExceptionHandler: Process 82 generated fatal exception c0000005
EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process..
Whenever you see a SqlDump... error, check the default LOG path for a
SQLDUMPxx.txt file. It will give you a more detail stack dump trace.
However, this usually indicates a binary mismatch for the SQL Server
executables or code bug.
You should contact MS PSS immediately for code bug resolution.
Sincerely,
Anthony Thomas
"chinn" <chinn@.discussions.microsoft.com> wrote in message
news:59C507C6-08AB-4155-BD3E-153B558AE779@.microsoft.com...
Hello,
I have a DTS package where it does bulk inserts into multiple tables
it works fine and all of a sudden it throws a stack dump in the sql server
error
log with this error..
Error: 0, Severity: 19, State: 0
> SqlDumpExceptionHandler: Process 82 generated fatal exception c0000005
EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process..
sql
I have a DTS package where it does bulk inserts into multiple tables
it works fine and all of a sudden it throws a stack dump in the sql server
error
log with this error..
Error: 0, Severity: 19, State: 0
> SqlDumpExceptionHandler: Process 82 generated fatal exception c0000005
EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process..
Whenever you see a SqlDump... error, check the default LOG path for a
SQLDUMPxx.txt file. It will give you a more detail stack dump trace.
However, this usually indicates a binary mismatch for the SQL Server
executables or code bug.
You should contact MS PSS immediately for code bug resolution.
Sincerely,
Anthony Thomas
"chinn" <chinn@.discussions.microsoft.com> wrote in message
news:59C507C6-08AB-4155-BD3E-153B558AE779@.microsoft.com...
Hello,
I have a DTS package where it does bulk inserts into multiple tables
it works fine and all of a sudden it throws a stack dump in the sql server
error
log with this error..
Error: 0, Severity: 19, State: 0
> SqlDumpExceptionHandler: Process 82 generated fatal exception c0000005
EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process..
sql
Regarding BULK insert
I am doing a bulk insert and i am A DBO on the database and i can't do it unless i
added my self to the bulkinsert admin server role.
do any body why is that.why it needs a server role..
chinn,
Because of the way BULK INSERT is implemented. The data goes straight
into the server as an OLEDB row set. From Books Online: "the BULK INSERT
statement requires read access to any data on the network and machine
the server is running on." That is why it is locked down.
You might also want to be aware of this gotcha:
BUG: Cannot Perform BULK INSERT with Bulkadmin Privileges
http://support.microsoft.com/?id=302621
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
chinn wrote:
> I am doing a bulk insert and i am A DBO on the database and i can't do it unless i
> added my self to the bulkinsert admin server role.
> do any body why is that.why it needs a server role..
added my self to the bulkinsert admin server role.
do any body why is that.why it needs a server role..
chinn,
Because of the way BULK INSERT is implemented. The data goes straight
into the server as an OLEDB row set. From Books Online: "the BULK INSERT
statement requires read access to any data on the network and machine
the server is running on." That is why it is locked down.
You might also want to be aware of this gotcha:
BUG: Cannot Perform BULK INSERT with Bulkadmin Privileges
http://support.microsoft.com/?id=302621
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
chinn wrote:
> I am doing a bulk insert and i am A DBO on the database and i can't do it unless i
> added my self to the bulkinsert admin server role.
> do any body why is that.why it needs a server role..
Regarding Bulk Insert
Hello,
I have a DTS package where it does bulk inserts into multiple tables
it works fine and all of a sudden it throws a stack dump in the sql server
error
log with this error..
Error: 0, Severity: 19, State: 0
> SqlDumpExceptionHandler: Process 82 generated fatal exception c0000005
EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process..Whenever you see a SqlDump... error, check the default LOG path for a
SQLDUMPxx.txt file. It will give you a more detail stack dump trace.
However, this usually indicates a binary mismatch for the SQL Server
executables or code bug.
You should contact MS PSS immediately for code bug resolution.
Sincerely,
Anthony Thomas
"chinn" <chinn@.discussions.microsoft.com> wrote in message
news:59C507C6-08AB-4155-BD3E-153B558AE779@.microsoft.com...
Hello,
I have a DTS package where it does bulk inserts into multiple tables
it works fine and all of a sudden it throws a stack dump in the sql server
error
log with this error..
Error: 0, Severity: 19, State: 0
> SqlDumpExceptionHandler: Process 82 generated fatal exception c0000005
EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process..
I have a DTS package where it does bulk inserts into multiple tables
it works fine and all of a sudden it throws a stack dump in the sql server
error
log with this error..
Error: 0, Severity: 19, State: 0
> SqlDumpExceptionHandler: Process 82 generated fatal exception c0000005
EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process..Whenever you see a SqlDump... error, check the default LOG path for a
SQLDUMPxx.txt file. It will give you a more detail stack dump trace.
However, this usually indicates a binary mismatch for the SQL Server
executables or code bug.
You should contact MS PSS immediately for code bug resolution.
Sincerely,
Anthony Thomas
"chinn" <chinn@.discussions.microsoft.com> wrote in message
news:59C507C6-08AB-4155-BD3E-153B558AE779@.microsoft.com...
Hello,
I have a DTS package where it does bulk inserts into multiple tables
it works fine and all of a sudden it throws a stack dump in the sql server
error
log with this error..
Error: 0, Severity: 19, State: 0
> SqlDumpExceptionHandler: Process 82 generated fatal exception c0000005
EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process..
Regarding BULK insert
I am doing a bulk insert and i am A DBO on the database and i can't do it u
nless i
added my self to the bulkinsert admin server role.
do any body why is that.why it needs a server role..chinn,
Because of the way BULK INSERT is implemented. The data goes straight
into the server as an OLEDB row set. From Books Online: "the BULK INSERT
statement requires read access to any data on the network and machine
the server is running on." That is why it is locked down.
You might also want to be aware of this gotcha:
BUG: Cannot Perform BULK INSERT with Bulkadmin Privileges
http://support.microsoft.com/?id=302621
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
chinn wrote:
> I am doing a bulk insert and i am A DBO on the database and i can't do it
unless i
> added my self to the bulkinsert admin server role.
> do any body why is that.why it needs a server role..
nless i
added my self to the bulkinsert admin server role.
do any body why is that.why it needs a server role..chinn,
Because of the way BULK INSERT is implemented. The data goes straight
into the server as an OLEDB row set. From Books Online: "the BULK INSERT
statement requires read access to any data on the network and machine
the server is running on." That is why it is locked down.
You might also want to be aware of this gotcha:
BUG: Cannot Perform BULK INSERT with Bulkadmin Privileges
http://support.microsoft.com/?id=302621
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
chinn wrote:
> I am doing a bulk insert and i am A DBO on the database and i can't do it
unless i
> added my self to the bulkinsert admin server role.
> do any body why is that.why it needs a server role..
Regarding Bulk Insert
Hello,
I have a DTS package where it does bulk inserts into multiple tables
it works fine and all of a sudden it throws a stack dump in the sql server
error
log with this error..
Error: 0, Severity: 19, State: 0
> SqlDumpExceptionHandler: Process 82 generated fatal exception c0000005
EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process..Whenever you see a SqlDump... error, check the default LOG path for a
SQLDUMPxx.txt file. It will give you a more detail stack dump trace.
However, this usually indicates a binary mismatch for the SQL Server
executables or code bug.
You should contact MS PSS immediately for code bug resolution.
Sincerely,
Anthony Thomas
"chinn" <chinn@.discussions.microsoft.com> wrote in message
news:59C507C6-08AB-4155-BD3E-153B558AE779@.microsoft.com...
Hello,
I have a DTS package where it does bulk inserts into multiple tables
it works fine and all of a sudden it throws a stack dump in the sql server
error
log with this error..
Error: 0, Severity: 19, State: 0
> SqlDumpExceptionHandler: Process 82 generated fatal exception c0000005
EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process..
I have a DTS package where it does bulk inserts into multiple tables
it works fine and all of a sudden it throws a stack dump in the sql server
error
log with this error..
Error: 0, Severity: 19, State: 0
> SqlDumpExceptionHandler: Process 82 generated fatal exception c0000005
EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process..Whenever you see a SqlDump... error, check the default LOG path for a
SQLDUMPxx.txt file. It will give you a more detail stack dump trace.
However, this usually indicates a binary mismatch for the SQL Server
executables or code bug.
You should contact MS PSS immediately for code bug resolution.
Sincerely,
Anthony Thomas
"chinn" <chinn@.discussions.microsoft.com> wrote in message
news:59C507C6-08AB-4155-BD3E-153B558AE779@.microsoft.com...
Hello,
I have a DTS package where it does bulk inserts into multiple tables
it works fine and all of a sudden it throws a stack dump in the sql server
error
log with this error..
Error: 0, Severity: 19, State: 0
> SqlDumpExceptionHandler: Process 82 generated fatal exception c0000005
EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process..
Regarding Bulk Copy in Transactional Replication..
Hi,
I got typical scenario in transacrtional replication. i want to avoid bulk
copy during replication.
Let me explain my scenario.
I’m doing transactional replication between two databases.
When publisher and subscriber created the data going to be bulk copied from
publisher table to subscriber table.
My main intension was to create replication between different tables with
different fields in which I got succeeded.
But main problem is I want to stop this bulk copy from publisher to
subscriber.
Scenario 1: my subscriber table may contain some previous data which will be
replaced with publisher data due to bulk copy.
I don’t want this .I want to avoid this bulk copy and wants to create
procedures(for insert, update and delete transactions) in subscriber which
will take care of replication.
I achieved almost everything but not able to avoid this bulk copy during
the creation of subscriber.
As I know the only way I can stop bulk copy is by creating subscription
without subscription agent. But here without subscription agent the
procedures(for insert, update and delete transactions)
won’t get created in subscriber.
Help me regarding the above scenario and I need it urgently.
Regards
Baji Prasad
I'm not entirely clear about your scenario, but it seems to me that you are
talking about nosync initialisations. Please have a look here and see if this
applies:
http://www.replicationanswers.com/NoSyncInitializations.asp
HTH,
Paul Ibison
|||hi paul..
im very thankfull for ur reply.
yes it helped me a lot atleast i got an idea how to avoid bulk copy.
again thanks man...
Regards
Baji Prasad
"Paul Ibison" wrote:
> I'm not entirely clear about your scenario, but it seems to me that you are
> talking about nosync initialisations. Please have a look here and see if this
> applies:
> http://www.replicationanswers.com/NoSyncInitializations.asp
> HTH,
> Paul Ibison
>
sql
I got typical scenario in transacrtional replication. i want to avoid bulk
copy during replication.
Let me explain my scenario.
I’m doing transactional replication between two databases.
When publisher and subscriber created the data going to be bulk copied from
publisher table to subscriber table.
My main intension was to create replication between different tables with
different fields in which I got succeeded.
But main problem is I want to stop this bulk copy from publisher to
subscriber.
Scenario 1: my subscriber table may contain some previous data which will be
replaced with publisher data due to bulk copy.
I don’t want this .I want to avoid this bulk copy and wants to create
procedures(for insert, update and delete transactions) in subscriber which
will take care of replication.
I achieved almost everything but not able to avoid this bulk copy during
the creation of subscriber.
As I know the only way I can stop bulk copy is by creating subscription
without subscription agent. But here without subscription agent the
procedures(for insert, update and delete transactions)
won’t get created in subscriber.
Help me regarding the above scenario and I need it urgently.
Regards
Baji Prasad
I'm not entirely clear about your scenario, but it seems to me that you are
talking about nosync initialisations. Please have a look here and see if this
applies:
http://www.replicationanswers.com/NoSyncInitializations.asp
HTH,
Paul Ibison
|||hi paul..
im very thankfull for ur reply.
yes it helped me a lot atleast i got an idea how to avoid bulk copy.
again thanks man...
Regards
Baji Prasad
"Paul Ibison" wrote:
> I'm not entirely clear about your scenario, but it seems to me that you are
> talking about nosync initialisations. Please have a look here and see if this
> applies:
> http://www.replicationanswers.com/NoSyncInitializations.asp
> HTH,
> Paul Ibison
>
sql
Wednesday, March 28, 2012
Regarding Bulk Copy During Transactional Replication
Hi ,
I got a problem in regarding Transactional Replication.
Let me explain my scenario.
I’m doing transactional replication between two databases.
When publisher and subscriber created the data going to be bulk copied from publisher table to subscriber table.
My main intension was to create replication between different tables with different fields in which I got succeeded.
But main problem is I want to stop this bulk copy from publisher to subscriber.
Scenario 1: my subscriber table may contain some previous data which will be replaced with publisher data due to bulk copy.
I don’t want this .I want to avoid this bulk copy and wants to create procedures(for insert, update and delete transactions) in subscriber which will take care of replication.
I achieved almost everything but not able to avoid this bulk copy during the creation of subscriber.
As I know the only way I can stop bulk copy is by creating subscription without subscription agent. But here without subscription agent the procedures(for insert, update and delete transactions)
won’t get created in subscriber.
Help me regarding the above scenario and I need it urgently.
in sp_addsubscription, look at paramter @.sync_type, you can specify 'replication support only' and 'initialize with backup'. That allows you to skip the bcp. You can find more information about the latter here: http://msdn2.microsoft.com/de-de/library/ms147834(SQL.90).aspx.
Regarding Bulk Copy During Transactional Replication
Hi ,
I got a problem in regarding Transactional Replication.
Let me explain my scenario.
I’m doing transactional replication between two databases.
When publisher and subscriber created the data going to be bulk copied from publisher table to subscriber table.
My main intension was to create replication between different tables with different fields in which I got succeeded.
But main problem is I want to stop this bulk copy from publisher to subscriber.
Scenario 1: my subscriber table may contain some previous data which will be replaced with publisher data due to bulk copy.
I don’t want this .I want to avoid this bulk copy and wants to create procedures(for insert, update and delete transactions) in subscriber which will take care of replication.
I achieved almost everything but not able to avoid this bulk copy during the creation of subscriber.
As I know the only way I can stop bulk copy is by creating subscription without subscription agent. But here without subscription agent the procedures(for insert, update and delete transactions)
won’t get created in subscriber.
Help me regarding the above scenario and I need it urgently.
in sp_addsubscription, look at paramter @.sync_type, you can specify 'replication support only' and 'initialize with backup'. That allows you to skip the bcp. You can find more information about the latter here: http://msdn2.microsoft.com/de-de/library/ms147834(SQL.90).aspx.
Subscribe to:
Posts (Atom)