sql rowcount: created

Showing posts with label created. Show all posts

Thursday, March 22, 2012

_WA_SYS indexes

Hi,
What are the indexes's significance startign
with '_WA_SYS_*' present in sysindexes...How they are
created...?
How to omit them when we query to find the indexes that
our application is using...
Regards
Sridhar.Those are not indexes. This is statistics for a column, it is autogenerated.
Check the CREATE STATISTICS command in Books OnLine.
--
Dejan Sarka, SQL Server MVP
Associate Mentor
Solid Quality Learning
More than just Training
www.SolidQualityLearning.com
<anonymous@.discussions.microsoft.com> wrote in message
news:9a1901c433f9$2923ecb0$a501280a@.phx.gbl...
> Hi,
>
> What are the indexes's significance startign
> with '_WA_SYS_*' present in sysindexes...How they are
> created...?
> How to omit them when we query to find the indexes that
> our application is using...
>
> Regards
> Sridhar.
>|||You can exclude statistics by filtering the IsStatistics INDEXPROPERTY:
SELECT name
FROM sysindexes
WHERE indid BETWEEN 1 AND 254 AND
INDEXPROPERTY(id, name, 'IsStatistics') = 0
--
Hope this helps.
Dan Guzman
SQL Server MVP
<anonymous@.discussions.microsoft.com> wrote in message
news:9a1901c433f9$2923ecb0$a501280a@.phx.gbl...
> Hi,
>
> What are the indexes's significance startign
> with '_WA_SYS_*' present in sysindexes...How they are
> created...?
> How to omit them when we query to find the indexes that
> our application is using...
>
> Regards
> Sridhar.
>

_WA_ entries in sysindexes

Hi,
When I look at sysindexes, there are lot of _WA_% entries in there for my
database. These are constraints I believe, created by checking 'Auto create
statistics' option at the DB level. SOmewhere I read that if you see lot of
these constraints, it is better to create 'em as permanent indexes for better
performance.
1) Is that true ?
2) How do I extract the ddl for these constraints ?
TIA
KNM> When I look at sysindexes, there are lot of _WA_% entries in there for my
> database. These are constraints I believe
No, constraints is something entirely different.
> ...created by checking 'Auto create
> statistics' option at the DB level.
Correct. These entries are statistics, the same thing as when you execute the CREATE STATISTICS
command.
> SOmewhere I read that if you see lot of
> these constraints, it is better to create 'em as permanent indexes for better
> performance.
That is not necessarily the case. First, these are not indexes, it is statistics. Big difference.
Also, just because the optimizer feels like having statistics over a column doesn't mean that an
index would be beneficial (and an index comes with overhead). You should analyze what indexes your
queries would benefit from, and it is possible that some of these auto created statistics should be
indexes, but only you can know whether that is the case or not.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"MO" <MO@.discussions.microsoft.com> wrote in message
news:18235A22-6059-4F5E-B2A8-71121719E519@.microsoft.com...
> Hi,
> When I look at sysindexes, there are lot of _WA_% entries in there for my
> database. These are constraints I believe, created by checking 'Auto create
> statistics' option at the DB level. SOmewhere I read that if you see lot of
> these constraints, it is better to create 'em as permanent indexes for better
> performance.
> 1) Is that true ?
> 2) How do I extract the ddl for these constraints ?
> TIA
> KNM|||Thx for your info.
Dumb Q. How do I see what columns the stats is for ?
I know the stats name is say _WA_Sys_ord_billto_66F53242
But on what column the stats is built on ? What command will tell me that ?
TIA
Knm
"Tibor Karaszi" wrote:
> > When I look at sysindexes, there are lot of _WA_% entries in there for my
> > database. These are constraints I believe
> No, constraints is something entirely different.
>
> > ...created by checking 'Auto create
> > statistics' option at the DB level.
> Correct. These entries are statistics, the same thing as when you execute the CREATE STATISTICS
> command.
>
> > SOmewhere I read that if you see lot of
> > these constraints, it is better to create 'em as permanent indexes for better
> > performance.
> That is not necessarily the case. First, these are not indexes, it is statistics. Big difference.
> Also, just because the optimizer feels like having statistics over a column doesn't mean that an
> index would be beneficial (and an index comes with overhead). You should analyze what indexes your
> queries would benefit from, and it is possible that some of these auto created statistics should be
> indexes, but only you can know whether that is the case or not.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "MO" <MO@.discussions.microsoft.com> wrote in message
> news:18235A22-6059-4F5E-B2A8-71121719E519@.microsoft.com...
> > Hi,
> > When I look at sysindexes, there are lot of _WA_% entries in there for my
> > database. These are constraints I believe, created by checking 'Auto create
> > statistics' option at the DB level. SOmewhere I read that if you see lot of
> > these constraints, it is better to create 'em as permanent indexes for better
> > performance.
> >
> > 1) Is that true ?
> >
> > 2) How do I extract the ddl for these constraints ?
> >
> > TIA
> > KNM
>|||> Dumb Q. How do I see what columns the stats is for ?
One method is to query the system table with a query like the following:
Try
SELECT
OBJECT_NAME(i.id) ,AS TableName
i.name AS StatisticsName,
c.name AS ColumnName
FROM sysindexes i
JOIN sysindexkeys k ON
k.id = i.id and k.indid = i.indid
JOIN syscolumns c on
c.id = k.id and c.colid = k.colid
WHERE INDEXPROPERTY(i.id, i.name, 'IsStatistics') = 1
ORDER BY
OBJECT_NAME(i.id),
i.name,
c.name
--
Hope this helps.
Dan Guzman
SQL Server MVP
"MO" <MO@.discussions.microsoft.com> wrote in message
news:80809AF6-100F-46E9-8CF3-A7F9B8D2E2AF@.microsoft.com...
> Thx for your info.
> Dumb Q. How do I see what columns the stats is for ?
> I know the stats name is say _WA_Sys_ord_billto_66F53242
> But on what column the stats is built on ? What command will tell me that
> ?
> TIA
> Knm
> "Tibor Karaszi" wrote:
>> > When I look at sysindexes, there are lot of _WA_% entries in there
>> > for my
>> > database. These are constraints I believe
>> No, constraints is something entirely different.
>>
>> > ...created by checking 'Auto create
>> > statistics' option at the DB level.
>> Correct. These entries are statistics, the same thing as when you execute
>> the CREATE STATISTICS
>> command.
>>
>> > SOmewhere I read that if you see lot of
>> > these constraints, it is better to create 'em as permanent indexes for
>> > better
>> > performance.
>> That is not necessarily the case. First, these are not indexes, it is
>> statistics. Big difference.
>> Also, just because the optimizer feels like having statistics over a
>> column doesn't mean that an
>> index would be beneficial (and an index comes with overhead). You should
>> analyze what indexes your
>> queries would benefit from, and it is possible that some of these auto
>> created statistics should be
>> indexes, but only you can know whether that is the case or not.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "MO" <MO@.discussions.microsoft.com> wrote in message
>> news:18235A22-6059-4F5E-B2A8-71121719E519@.microsoft.com...
>> > Hi,
>> > When I look at sysindexes, there are lot of _WA_% entries in there
>> > for my
>> > database. These are constraints I believe, created by checking 'Auto
>> > create
>> > statistics' option at the DB level. SOmewhere I read that if you see
>> > lot of
>> > these constraints, it is better to create 'em as permanent indexes for
>> > better
>> > performance.
>> >
>> > 1) Is that true ?
>> >
>> > 2) How do I extract the ddl for these constraints ?
>> >
>> > TIA
>> > KNM
>>|||If you know the table, you can use sp_helpstats
You can pull the table name from sysindexes with this:
select object_name(id)
--
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"MO" <MO@.discussions.microsoft.com> wrote in message
news:80809AF6-100F-46E9-8CF3-A7F9B8D2E2AF@.microsoft.com...
> Thx for your info.
> Dumb Q. How do I see what columns the stats is for ?
> I know the stats name is say _WA_Sys_ord_billto_66F53242
> But on what column the stats is built on ? What command will tell me that
> ?
> TIA
> Knm
> "Tibor Karaszi" wrote:
>> > When I look at sysindexes, there are lot of _WA_% entries in there
>> > for my
>> > database. These are constraints I believe
>> No, constraints is something entirely different.
>>
>> > ...created by checking 'Auto create
>> > statistics' option at the DB level.
>> Correct. These entries are statistics, the same thing as when you execute
>> the CREATE STATISTICS
>> command.
>>
>> > SOmewhere I read that if you see lot of
>> > these constraints, it is better to create 'em as permanent indexes for
>> > better
>> > performance.
>> That is not necessarily the case. First, these are not indexes, it is
>> statistics. Big difference.
>> Also, just because the optimizer feels like having statistics over a
>> column doesn't mean that an
>> index would be beneficial (and an index comes with overhead). You should
>> analyze what indexes your
>> queries would benefit from, and it is possible that some of these auto
>> created statistics should be
>> indexes, but only you can know whether that is the case or not.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "MO" <MO@.discussions.microsoft.com> wrote in message
>> news:18235A22-6059-4F5E-B2A8-71121719E519@.microsoft.com...
>> > Hi,
>> > When I look at sysindexes, there are lot of _WA_% entries in there
>> > for my
>> > database. These are constraints I believe, created by checking 'Auto
>> > create
>> > statistics' option at the DB level. SOmewhere I read that if you see
>> > lot of
>> > these constraints, it is better to create 'em as permanent indexes for
>> > better
>> > performance.
>> >
>> > 1) Is that true ?
>> >
>> > 2) How do I extract the ddl for these constraints ?
>> >
>> > TIA
>> > KNM
>>|||Thanks for the info. I looked at a table, it has 118 entries in sysindexes
for that table. 6 indexes and 112 contraints. I dropped those constraints to
see if they are being used, but they all got created in couple of min which
tells me that they are being used. So, my question is should I convert those
constraints to an index ?
What are the cons of keeping these as a constraint and not converting to an
index in the long run ?
TIA
MO
"Kalen Delaney" wrote:
> If you know the table, you can use sp_helpstats
> You can pull the table name from sysindexes with this:
> select object_name(id)
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.solidqualitylearning.com
>
> "MO" <MO@.discussions.microsoft.com> wrote in message
> news:80809AF6-100F-46E9-8CF3-A7F9B8D2E2AF@.microsoft.com...
> > Thx for your info.
> > Dumb Q. How do I see what columns the stats is for ?
> > I know the stats name is say _WA_Sys_ord_billto_66F53242
> > But on what column the stats is built on ? What command will tell me that
> > ?
> > TIA
> > Knm
> >
> > "Tibor Karaszi" wrote:
> >
> >> > When I look at sysindexes, there are lot of _WA_% entries in there
> >> > for my
> >> > database. These are constraints I believe
> >>
> >> No, constraints is something entirely different.
> >>
> >>
> >> > ...created by checking 'Auto create
> >> > statistics' option at the DB level.
> >>
> >> Correct. These entries are statistics, the same thing as when you execute
> >> the CREATE STATISTICS
> >> command.
> >>
> >>
> >> > SOmewhere I read that if you see lot of
> >> > these constraints, it is better to create 'em as permanent indexes for
> >> > better
> >> > performance.
> >>
> >> That is not necessarily the case. First, these are not indexes, it is
> >> statistics. Big difference.
> >> Also, just because the optimizer feels like having statistics over a
> >> column doesn't mean that an
> >> index would be beneficial (and an index comes with overhead). You should
> >> analyze what indexes your
> >> queries would benefit from, and it is possible that some of these auto
> >> created statistics should be
> >> indexes, but only you can know whether that is the case or not.
> >>
> >> --
> >> Tibor Karaszi, SQL Server MVP
> >> http://www.karaszi.com/sqlserver/default.asp
> >> http://www.solidqualitylearning.com/
> >>
> >>
> >> "MO" <MO@.discussions.microsoft.com> wrote in message
> >> news:18235A22-6059-4F5E-B2A8-71121719E519@.microsoft.com...
> >> > Hi,
> >> > When I look at sysindexes, there are lot of _WA_% entries in there
> >> > for my
> >> > database. These are constraints I believe, created by checking 'Auto
> >> > create
> >> > statistics' option at the DB level. SOmewhere I read that if you see
> >> > lot of
> >> > these constraints, it is better to create 'em as permanent indexes for
> >> > better
> >> > performance.
> >> >
> >> > 1) Is that true ?
> >> >
> >> > 2) How do I extract the ddl for these constraints ?
> >> >
> >> > TIA
> >> > KNM
> >>
> >>
>
>|||They are not constraints, bit difference. They are entries for SQL Server to hold statistics over
the data in a column of the table. Whether you should create such indexes, only you can tell. It is
likely that your queries can benefit from indexes on some over the columns that these statistics has
been created. OTOH, you most probably don't want to have 118 indexes on a table (imagine the
slowness of your modifications and storage requirements). Analyze your queries, their execution
plans and create indexes that makes sense.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"MO" <MO@.discussions.microsoft.com> wrote in message
news:AE7CA10C-4F2C-49D0-B64D-CA40728053CC@.microsoft.com...
> Thanks for the info. I looked at a table, it has 118 entries in sysindexes
> for that table. 6 indexes and 112 contraints. I dropped those constraints to
> see if they are being used, but they all got created in couple of min which
> tells me that they are being used. So, my question is should I convert those
> constraints to an index ?
> What are the cons of keeping these as a constraint and not converting to an
> index in the long run ?
> TIA
> MO
> "Kalen Delaney" wrote:
>> If you know the table, you can use sp_helpstats
>> You can pull the table name from sysindexes with this:
>> select object_name(id)
>> --
>> HTH
>> Kalen Delaney, SQL Server MVP
>> www.solidqualitylearning.com
>>
>> "MO" <MO@.discussions.microsoft.com> wrote in message
>> news:80809AF6-100F-46E9-8CF3-A7F9B8D2E2AF@.microsoft.com...
>> > Thx for your info.
>> > Dumb Q. How do I see what columns the stats is for ?
>> > I know the stats name is say _WA_Sys_ord_billto_66F53242
>> > But on what column the stats is built on ? What command will tell me that
>> > ?
>> > TIA
>> > Knm
>> >
>> > "Tibor Karaszi" wrote:
>> >
>> >> > When I look at sysindexes, there are lot of _WA_% entries in there
>> >> > for my
>> >> > database. These are constraints I believe
>> >>
>> >> No, constraints is something entirely different.
>> >>
>> >>
>> >> > ...created by checking 'Auto create
>> >> > statistics' option at the DB level.
>> >>
>> >> Correct. These entries are statistics, the same thing as when you execute
>> >> the CREATE STATISTICS
>> >> command.
>> >>
>> >>
>> >> > SOmewhere I read that if you see lot of
>> >> > these constraints, it is better to create 'em as permanent indexes for
>> >> > better
>> >> > performance.
>> >>
>> >> That is not necessarily the case. First, these are not indexes, it is
>> >> statistics. Big difference.
>> >> Also, just because the optimizer feels like having statistics over a
>> >> column doesn't mean that an
>> >> index would be beneficial (and an index comes with overhead). You should
>> >> analyze what indexes your
>> >> queries would benefit from, and it is possible that some of these auto
>> >> created statistics should be
>> >> indexes, but only you can know whether that is the case or not.
>> >>
>> >> --
>> >> Tibor Karaszi, SQL Server MVP
>> >> http://www.karaszi.com/sqlserver/default.asp
>> >> http://www.solidqualitylearning.com/
>> >>
>> >>
>> >> "MO" <MO@.discussions.microsoft.com> wrote in message
>> >> news:18235A22-6059-4F5E-B2A8-71121719E519@.microsoft.com...
>> >> > Hi,
>> >> > When I look at sysindexes, there are lot of _WA_% entries in there
>> >> > for my
>> >> > database. These are constraints I believe, created by checking 'Auto
>> >> > create
>> >> > statistics' option at the DB level. SOmewhere I read that if you see
>> >> > lot of
>> >> > these constraints, it is better to create 'em as permanent indexes for
>> >> > better
>> >> > performance.
>> >> >
>> >> > 1) Is that true ?
>> >> >
>> >> > 2) How do I extract the ddl for these constraints ?
>> >> >
>> >> > TIA
>> >> > KNM
>> >>
>> >>
>>sql

_WA_ entries in sysindexes

Monday, March 19, 2012

[SQL Server 2005] Primary vs Secondary file-groups - deciding which table goes where

I created a set of primary and secondary data-files and organized them into file-groups. Now, I want to decide which tables to allocate to which file-group. What should be the factors on which I ought to decide this? Is there a significance of choosing the primary group over the secondary ones or vice versa? What is the impact of the choice in either case?filegroups in general are all about PERFORMANCE. you can gain significant performance gains if you leverage multiple disks with the multi-filegroup feature of sql server. there is no hard and fast but the general idea is to spread the workload evenly over the disks and to leverage faster disks/cache for the more intensive files|||

Thank you for your reply, Derek. Need one clarification though - would it be correct to say that a table created on a file-group gets evenly distributed across all the data-files in the file-group? In effect, what we are achieving is partitioning the table, right?

My earlier question was more about understanding how SQL Server determines which table belongs to which data-file and file-group. I suppose it might be part of metadata, which some DMV would be able to retrieve. I am not sure but, if this is indeed so, which is the DMV?

A related question that pops up is - should all (or most of the) tables that are most frequently accessed by allocated to the primary file-group? If SQL Server looks at the primary file-group first before scanning the others for the desired table-rows, it would make sense in allocating the most frequently accessed tables to the primary file-group.

Sunday, March 11, 2012

[SQL 2005] Restore question

I trying to test my restore in SQL 2005. I created a DB called test_restore
and try to restore from backup but getting this error. I have tried using
"overwrite option" but the same. I also tried T-SQL statement with MOVE and
also tried using read-only DB but still the same .
Any idea?
*************************************************
Msg 3154, Level 16, State 4, Line 1
The backup set holds a backup of a database other than the existing
*****************************************************Are you trying to overwite a existing database or a new one?
--
Thanks,
Sree
[Please specify the version of Sql Server as we can save one thread and time
asking back if its 2000 or 2005]
"rupart" wrote:
> I trying to test my restore in SQL 2005. I created a DB called test_restore
> and try to restore from backup but getting this error. I have tried using
> "overwrite option" but the same. I also tried T-SQL statement with MOVE and
> also tried using read-only DB but still the same .
> Any idea?
> *************************************************
> Msg 3154, Level 16, State 4, Line 1
> The backup set holds a backup of a database other than the existing
> *****************************************************|||Hi i didnt read your question correctly...
I got the same error while trying to resotre to a created DB. So i tried
creating on a non existing DB it worked, try that way. Iam still checking on
how to overwrite the existing DB...
--
Thanks,
Sree
[Please specify the version of Sql Server as we can save one thread and time
asking back if its 2000 or 2005]
"rupart" wrote:
> I trying to test my restore in SQL 2005. I created a DB called test_restore
> and try to restore from backup but getting this error. I have tried using
> "overwrite option" but the same. I also tried T-SQL statement with MOVE and
> also tried using read-only DB but still the same .
> Any idea?
> *************************************************
> Msg 3154, Level 16, State 4, Line 1
> The backup set holds a backup of a database other than the existing
> *****************************************************|||Yes, got the answer :)
Try this...
RESTORE DATABASE Test
FROM DISK = 'D:\Adv_Test.Bak'
WITH MOVE 'AdventureWorks_Data' TO 'C:\Program Files\Microsoft SQL
Server\MSSQL.1\MSSQL\DATA\Test.mdf',
MOVE 'AdventureWorks_Log' TO 'C:\Program Files\Microsoft SQL
Server\MSSQL.1\MSSQL\DATA\Test_log.ldf',
STATS = 1, REPLACE
GO
REPLACE is the key word while trying to overwrite existing DB.
--
Thanks,
Sree
[Please specify the version of Sql Server as we can save one thread and time
asking back if its 2000 or 2005]
"rupart" wrote:
> I trying to test my restore in SQL 2005. I created a DB called test_restore
> and try to restore from backup but getting this error. I have tried using
> "overwrite option" but the same. I also tried T-SQL statement with MOVE and
> also tried using read-only DB but still the same .
> Any idea?
> *************************************************
> Msg 3154, Level 16, State 4, Line 1
> The backup set holds a backup of a database other than the existing
> *****************************************************|||hi Sreejit,
Thank you
Yes, i wanted to test the restore into another temporary db to test it.
Are you suggesting not to create the tempdb first and run the command below
straightaway? How does the db got created then?
By the way, i am restoring files and filegroups.Will this method work? Or,
do i need to specify all the filegroups names? There are long list..Any
better way? :-s
"Sreejith G" wrote:
> Yes, got the answer :)
> Try this...
>
> RESTORE DATABASE Test
> FROM DISK = 'D:\Adv_Test.Bak'
> WITH MOVE 'AdventureWorks_Data' TO 'C:\Program Files\Microsoft SQL
> Server\MSSQL.1\MSSQL\DATA\Test.mdf',
> MOVE 'AdventureWorks_Log' TO 'C:\Program Files\Microsoft SQL
> Server\MSSQL.1\MSSQL\DATA\Test_log.ldf',
> STATS = 1, REPLACE
> GO
> REPLACE is the key word while trying to overwrite existing DB.
> --
> Thanks,
> Sree
> [Please specify the version of Sql Server as we can save one thread and time
> asking back if its 2000 or 2005]
>
> "rupart" wrote:
> > I trying to test my restore in SQL 2005. I created a DB called test_restore
> > and try to restore from backup but getting this error. I have tried using
> > "overwrite option" but the same. I also tried T-SQL statement with MOVE and
> > also tried using read-only DB but still the same .
> > Any idea?
> >
> > *************************************************
> > Msg 3154, Level 16, State 4, Line 1
> > The backup set holds a backup of a database other than the existing
> > *****************************************************|||rupart wrote:
> hi Sreejit,
> Thank you
> Yes, i wanted to test the restore into another temporary db to test it.
> Are you suggesting not to create the tempdb first and run the command below
> straightaway? How does the db got created then?
> By the way, i am restoring files and filegroups.Will this method work? Or,
> do i need to specify all the filegroups names? There are long list..Any
> better way? :-s
A RESTORE will create the database if it's not already there, so there
are no need to create it before a restore. The REPLACE option, is to
tell SQL server to overwrite an existing database with the same name as
the one that has been backed up. This is to prevent you from overwriting
a database by accident. You can read up on this in Books On Line.
Regards
Steen|||hi guys,
thx a lot for your replies. I find out a few things:
1) Using GUI, to restore, click on "Database" after Instance Name, right
click, restore database or restore files and filegroup. Then, enter your db
name(or you choose your test db that you have created from drop down).
Choosing the test db directly and attempting to restore will give you error.
2) Using T-SQL.(files and filegrp to test db)
************************************************
USE master
Go
RESTORE DATABASE TestRestore
FROM DISK = 'X:\Test.bak'
WITH RECOVERY,
MOVE 'ABC_PROD' TO 'X:\ABC_PROD.mdf',
MOVE 'ABC_1' TO 'X:\ABC_1.ndf',
MOVE 'ABC_2' TO 'X:\ABC_2.ndf',
MOVE 'ABC_log' TO 'X:\ABC_log.ldf'
GO
************************************************
"rupart" wrote:
> hi Sreejit,
> Thank you
> Yes, i wanted to test the restore into another temporary db to test it.
> Are you suggesting not to create the tempdb first and run the command below
> straightaway? How does the db got created then?
> By the way, i am restoring files and filegroups.Will this method work? Or,
> do i need to specify all the filegroups names? There are long list..Any
> better way? :-s
> "Sreejith G" wrote:
> > Yes, got the answer :)
> >
> > Try this...
> >
> >
> >
> > RESTORE DATABASE Test
> > FROM DISK = 'D:\Adv_Test.Bak'
> > WITH MOVE 'AdventureWorks_Data' TO 'C:\Program Files\Microsoft SQL
> > Server\MSSQL.1\MSSQL\DATA\Test.mdf',
> > MOVE 'AdventureWorks_Log' TO 'C:\Program Files\Microsoft SQL
> > Server\MSSQL.1\MSSQL\DATA\Test_log.ldf',
> > STATS = 1, REPLACE
> > GO
> >
> > REPLACE is the key word while trying to overwrite existing DB.
> >
> > --
> > Thanks,
> > Sree
> > [Please specify the version of Sql Server as we can save one thread and time
> > asking back if its 2000 or 2005]
> >
> >
> >
> > "rupart" wrote:
> >
> > > I trying to test my restore in SQL 2005. I created a DB called test_restore
> > > and try to restore from backup but getting this error. I have tried using
> > > "overwrite option" but the same. I also tried T-SQL statement with MOVE and
> > > also tried using read-only DB but still the same .
> > > Any idea?
> > >
> > > *************************************************
> > > Msg 3154, Level 16, State 4, Line 1
> > > The backup set holds a backup of a database other than the existing
> > > *****************************************************|||rupart wrote:
> hi guys,
> thx a lot for your replies. I find out a few things:
> 1) Using GUI, to restore, click on "Database" after Instance Name, right
> click, restore database or restore files and filegroup. Then, enter your db
> name(or you choose your test db that you have created from drop down).
> Choosing the test db directly and attempting to restore will give you error.
> 2) Using T-SQL.(files and filegrp to test db)
> ************************************************
> USE master
> Go
> RESTORE DATABASE TestRestore
> FROM DISK = 'X:\Test.bak'
> WITH RECOVERY,
> MOVE 'ABC_PROD' TO 'X:\ABC_PROD.mdf',
> MOVE 'ABC_1' TO 'X:\ABC_1.ndf',
> MOVE 'ABC_2' TO 'X:\ABC_2.ndf',
> MOVE 'ABC_log' TO 'X:\ABC_log.ldf'
> GO
> ************************************************
>
Does this means that you've got it working or do you still have
problems? Your T-SQL will fail if the TestRestore database already
exists since you haven't supplied the REPLACE option.
Regards
Steen

[SQL 2005] Restore question

Try this...
RESTORE DATABASE Test
FROM DISK = 'D:\Adv_Test.Bak'
WITH MOVE 'AdventureWorks_Data' TO 'C:\Program Files\Microsoft SQL
Server\MSSQL.1\MSSQL\DATA\Test.mdf',
MOVE 'AdventureWorks_Log' TO 'C:\Program Files\Microsoft SQL
Server\MSSQL.1\MSSQL\DATA\Test_log.ldf',
STATS = 1, REPLACE
GO
REPLACE is the key word while trying to overwrite existing DB.
Thanks,
Sree
[Please specify the version of Sql Server as we can save one thread and
time
asking back if its 2000 or 2005]
"rupart" wrote:

> I trying to test my restore in SQL 2005. I created a DB called test_restor
e
> and try to restore from backup but getting this error. I have tried using
> "overwrite option" but the same. I also tried T-SQL statement with MOVE an
d
> also tried using read-only DB but still the same .
> Any idea?
> ****************************************
*********
> Msg 3154, Level 16, State 4, Line 1
> The backup set holds a backup of a database other than the existing
> ****************************************
*************|||hi Sreejit,
Thank you
Yes, i wanted to test the restore into another temporary db to test it.
Are you suggesting not to create the tempdb first and run the command below
straightaway? How does the db got created then?
By the way, i am restoring files and filegroups.Will this method work? Or,
do i need to specify all the filegroups names? There are long list..Any
better way? :-s
"Sreejith G" wrote:
[vbcol=seagreen]
> Yes, got the answer

> Try this...
>
> RESTORE DATABASE Test
> FROM DISK = 'D:\Adv_Test.Bak'
> WITH MOVE 'AdventureWorks_Data' TO 'C:\Program Files\Microsoft SQL
> Server\MSSQL.1\MSSQL\DATA\Test.mdf',
> MOVE 'AdventureWorks_Log' TO 'C:\Program Files\Microsoft SQL
> Server\MSSQL.1\MSSQL\DATA\Test_log.ldf',
> STATS = 1, REPLACE
> GO
> REPLACE is the key word while trying to overwrite existing DB.
> --
> Thanks,
> Sree
> [Please specify the version of Sql Server as we can save one thread an
d time
> asking back if its 2000 or 2005]
>
> "rupart" wrote:
>|||rupart wrote:
> hi Sreejit,
> Thank you
> Yes, i wanted to test the restore into another temporary db to test it.
> Are you suggesting not to create the tempdb first and run the command belo
w
> straightaway? How does the db got created then?
> By the way, i am restoring files and filegroups.Will this method work? Or,
> do i need to specify all the filegroups names? There are long list..Any
> better way? :-s
A RESTORE will create the database if it's not already there, so there
are no need to create it before a restore. The REPLACE option, is to
tell SQL server to overwrite an existing database with the same name as
the one that has been backed up. This is to prevent you from overwriting
a database by accident. You can read up on this in Books On Line.
Regards
Steen|||hi guys,
thx a lot for your replies. I find out a few things:
1) Using GUI, to restore, click on "Database" after Instance Name, right
click, restore database or restore files and filegroup. Then, enter your db
name(or you choose your test db that you have created from drop down).
Choosing the test db directly and attempting to restore will give you error.
2) Using T-SQL.(files and filegrp to test db)
****************************************
********
USE master
Go
RESTORE DATABASE TestRestore
FROM DISK = 'X:\Test.bak'
WITH RECOVERY,
MOVE 'ABC_PROD' TO 'X:\ABC_PROD.mdf',
MOVE 'ABC_1' TO 'X:\ABC_1.ndf',
MOVE 'ABC_2' TO 'X:\ABC_2.ndf',
MOVE 'ABC_log' TO 'X:\ABC_log.ldf'
GO
****************************************
********
"rupart" wrote:
[vbcol=seagreen]
> hi Sreejit,
> Thank you
> Yes, i wanted to test the restore into another temporary db to test it.
> Are you suggesting not to create the tempdb first and run the command belo
w
> straightaway? How does the db got created then?
> By the way, i am restoring files and filegroups.Will this method work? Or,
> do i need to specify all the filegroups names? There are long list..Any
> better way? :-s
> "Sreejith G" wrote:
>|||rupart wrote:
> hi guys,
> thx a lot for your replies. I find out a few things:
> 1) Using GUI, to restore, click on "Database" after Instance Name, right
> click, restore database or restore files and filegroup. Then, enter your d
b
> name(or you choose your test db that you have created from drop down).
> Choosing the test db directly and attempting to restore will give you erro
r.
> 2) Using T-SQL.(files and filegrp to test db)
> ****************************************
********
> USE master
> Go
> RESTORE DATABASE TestRestore
> FROM DISK = 'X:\Test.bak'
> WITH RECOVERY,
> MOVE 'ABC_PROD' TO 'X:\ABC_PROD.mdf',
> MOVE 'ABC_1' TO 'X:\ABC_1.ndf',
> MOVE 'ABC_2' TO 'X:\ABC_2.ndf',
> MOVE 'ABC_log' TO 'X:\ABC_log.ldf'
> GO
> ****************************************
********
>
Does this means that you've got it working or do you still have
problems? Your T-SQL will fail if the TestRestore database already
exists since you haven't supplied the REPLACE option.
Regards
Steen

[SQL 2005] Restore question

Try this...
RESTORE DATABASE Test
FROM DISK = 'D:\Adv_Test.Bak'
WITH MOVE 'AdventureWorks_Data' TO 'C:\Program Files\Microsoft SQL
Server\MSSQL.1\MSSQL\DATA\Test.mdf',
MOVE 'AdventureWorks_Log' TO 'C:\Program Files\Microsoft SQL
Server\MSSQL.1\MSSQL\DATA\Test_log.ldf',
STATS = 1, REPLACE
GO
REPLACE is the key word while trying to overwrite existing DB.
Thanks,
Sree
[Please specify the version of Sql Server as we can save one thread and time
asking back if its 2000 or 2005]
"rupart" wrote:

> I trying to test my restore in SQL 2005. I created a DB called test_restore
> and try to restore from backup but getting this error. I have tried using
> "overwrite option" but the same. I also tried T-SQL statement with MOVE and
> also tried using read-only DB but still the same .
> Any idea?
> *************************************************
> Msg 3154, Level 16, State 4, Line 1
> The backup set holds a backup of a database other than the existing
> ************************************************** ***
|||hi Sreejit,
Thank you
Yes, i wanted to test the restore into another temporary db to test it.
Are you suggesting not to create the tempdb first and run the command below
straightaway? How does the db got created then?
By the way, i am restoring files and filegroups.Will this method work? Or,
do i need to specify all the filegroups names? There are long list..Any
better way? :-s
"Sreejith G" wrote:
[vbcol=seagreen]
> Yes, got the answer

> Try this...
>
> RESTORE DATABASE Test
> FROM DISK = 'D:\Adv_Test.Bak'
> WITH MOVE 'AdventureWorks_Data' TO 'C:\Program Files\Microsoft SQL
> Server\MSSQL.1\MSSQL\DATA\Test.mdf',
> MOVE 'AdventureWorks_Log' TO 'C:\Program Files\Microsoft SQL
> Server\MSSQL.1\MSSQL\DATA\Test_log.ldf',
> STATS = 1, REPLACE
> GO
> REPLACE is the key word while trying to overwrite existing DB.
> --
> Thanks,
> Sree
> [Please specify the version of Sql Server as we can save one thread and time
> asking back if its 2000 or 2005]
>
> "rupart" wrote:
|||rupart wrote:
> hi Sreejit,
> Thank you
> Yes, i wanted to test the restore into another temporary db to test it.
> Are you suggesting not to create the tempdb first and run the command below
> straightaway? How does the db got created then?
> By the way, i am restoring files and filegroups.Will this method work? Or,
> do i need to specify all the filegroups names? There are long list..Any
> better way? :-s
A RESTORE will create the database if it's not already there, so there
are no need to create it before a restore. The REPLACE option, is to
tell SQL server to overwrite an existing database with the same name as
the one that has been backed up. This is to prevent you from overwriting
a database by accident. You can read up on this in Books On Line.
Regards
Steen
|||hi guys,
thx a lot for your replies. I find out a few things:
1) Using GUI, to restore, click on "Database" after Instance Name, right
click, restore database or restore files and filegroup. Then, enter your db
name(or you choose your test db that you have created from drop down).
Choosing the test db directly and attempting to restore will give you error.
2) Using T-SQL.(files and filegrp to test db)
************************************************
USE master
Go
RESTORE DATABASE TestRestore
FROM DISK = 'X:\Test.bak'
WITH RECOVERY,
MOVE 'ABC_PROD' TO 'X:\ABC_PROD.mdf',
MOVE 'ABC_1' TO 'X:\ABC_1.ndf',
MOVE 'ABC_2' TO 'X:\ABC_2.ndf',
MOVE 'ABC_log' TO 'X:\ABC_log.ldf'
GO
************************************************
"rupart" wrote:
[vbcol=seagreen]
> hi Sreejit,
> Thank you
> Yes, i wanted to test the restore into another temporary db to test it.
> Are you suggesting not to create the tempdb first and run the command below
> straightaway? How does the db got created then?
> By the way, i am restoring files and filegroups.Will this method work? Or,
> do i need to specify all the filegroups names? There are long list..Any
> better way? :-s
> "Sreejith G" wrote:
|||rupart wrote:
> hi guys,
> thx a lot for your replies. I find out a few things:
> 1) Using GUI, to restore, click on "Database" after Instance Name, right
> click, restore database or restore files and filegroup. Then, enter your db
> name(or you choose your test db that you have created from drop down).
> Choosing the test db directly and attempting to restore will give you error.
> 2) Using T-SQL.(files and filegrp to test db)
> ************************************************
> USE master
> Go
> RESTORE DATABASE TestRestore
> FROM DISK = 'X:\Test.bak'
> WITH RECOVERY,
> MOVE 'ABC_PROD' TO 'X:\ABC_PROD.mdf',
> MOVE 'ABC_1' TO 'X:\ABC_1.ndf',
> MOVE 'ABC_2' TO 'X:\ABC_2.ndf',
> MOVE 'ABC_log' TO 'X:\ABC_log.ldf'
> GO
> ************************************************
>
Does this means that you've got it working or do you still have
problems? Your T-SQL will fail if the TestRestore database already
exists since you haven't supplied the REPLACE option.
Regards
Steen

[S0001][unixODBC][Easysoft][ODBC SQL Server Driver][SQL Server]Login failed for user 'test1'

Hello,

I am new to SQL Server, sorry for my ignorance !!!

I have installed SQL Server 2005 Express on my desktop (Win 2000 Pro.)

I have created a new database.

I just created a new login/user (SQL Authentication) and when I try to login, it

errors out:

[S0001][unixODBC][Easysoft][ODBC SQL Server Driver][SQL Server]Login failed for user 'test1'

I believe the error is from SQL Server and not from ODBC driver.

I am also only able to connect by using my own userid (the one that I am connected to my desktop): domain\userid, and then I am only able to connect to 'master' db:

///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////

/usr/local/etc>isql SQLSERVER_POS -v
++
| Connected! |
| |
| sql-statement |
| help [tablename] |
| quit |
| |
++
SQL> use pcspos
[S0001][unixODBC][Easysoft][ODBC SQL Server Driver][SQL Server]Could not locate entry in sysdatabases for database 'pcspos'. No entry found with that name. Make sure that the name is entered correctly.
[ISQL]ERROR: Could not SQLExecute
///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////

Hello,

While I browse the SSMSE (SQL Server Management Studion Express) , I see that the database state (from the

Database Properties Windows) is NORMAL | SHUTDOWN !!!!!!

Is this OK ?

In the meantime when I login via SSMSE, I can see all the tables and data in the database !!!!!!!!!

Thanks,

Tom

|||

Hello, I was able to change the Database State to NORMAL (I changed the 'Auto Close' option to False).

BUT I am still not able to connect:

/usr/local/etc>isql SQLSERVER_POS -v
[S0001][unixODBC][Easysoft][ODBC SQL Server Driver][SQL Server]Login failed for user 'test1'.
[ISQL]ERROR: Could not SQLConnect

|||If you are using Windows Authentication only, you cannot use SQL Server logins to connect to the server. (see how to switch the authentication mode on my website under the Screencasts). If you want to use WIndows authentication you will have to be aware that you cannot explicitly pass a user name within the connectionstring, the username is passed implicitly through Windows. If you have enabled Mixed authentication already, make sure that you are using the right password.

Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

Hi Jens, thank you for your reply.

I am using the SQL Authentication mode, see below I am able to connect from the DOS prompt (localy),

but I can NOT connect from a Linux server by using the ODBC driver:

///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////

C:\>isql -S (local)\SQLEXPRESS -U pos_sql05 -P passwd
1> select db_name()
2> go

--
POS

(1 row affected)
1> select * from pcs_lookup_active_status;
2> go
ACTIVE_STATUS_KEY ACTIVE_STATUS_DESC
-- --
1 Active
2 Inactive
3 Deleted

(3 rows affected)

///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////

|||I don′t know how the tool for Linux works but if you do not pass a username using SQL Server authentication there has to be somewhere a setting file where these information is stored.
|||

I am using a config file by the name odbc.ini.

It looks like the following:

/////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////

[SQLSERVER_POS]
Driver = /usr/local/easysoft/sqlserver/lib/libessqlsrv.so
Description = Easysoft SQL Server ODBC driver (unixODBC 2.2.12)
Server = 10.20.30.40\SQLEXPRESS
Port = 1433
Database = POS
User = pos_sql05
Password = passwd
Mars_Connection = No
Logging = 31
LogFile = /tmp/sqlserver.log
QuotedId = Yes
AnsiNPW = Yes
Language =
Version7 = No
ClientLB = No
Failover_Partner =
VarMaxAsLong = No

//////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////

As you cann tell, the user id and password are there.

Thanks again.

Regards,

Tom

|||

Make sure that you are using the right password for the mentioned user. In addition make sure taht the user is priviledged for the POS database, cause authentication or the connection will also fail if the initial database is not one of the allowed ones. if you are not sure, try to connect to the master database. A user which is granted access to the server should have access to the master database.

Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

Hi Jens, thanks again.

The userid and password are correct (I am able to connect via isql from the DOS prompt).

I think the problem relies on the fact that I am coming remotetly (from a Linux server), BUT I have

enabled the TCP/IP protocol connection properties. What else can I look for ?

Thank you.

Tom

|||

How are you authenticating? Is your server set to authenticate using Windows authentication only or integrated authentication? Perhaps this unix driver is trying to use SQL authentication and your SQL installation only accepts windows authenticated connections. That would explain this problem.

Hope this helps!

John (MSFT)

|||

Hi John & Jens, the issue has been resolved. I contacted the ODBC driver's vendor and they provided me with an updated copy of it.

Thank you for your help and suggestions.

Keep up the GREAT job.

Best regards,

Tom

Thursday, March 8, 2012

[Q] cpu is full.. 100%

os : win2000
sql : sql2000 enterprise
table name : job
I created fulltext catalog
table is job column name : title
full population is every sunday am 5:00
incremental population is every 2hours from 7am to next day 3am
whenever server is running the both population, cpu is full 100%(see the
task manager )
Question : can i reduce the cpu use?
the columns are updated frequently are readcnt of job table
if i remove the readcnt column, the cpu use will reduce?
thanks...
below is the result of sp_spaceused job
name rows reserved data index_size unused
Jobs 259845 468808 KB 214264 KB 28128 KB 226416 KB
column_name type
title varchar(100)
findPrsn tinyint
ageZn varchar
applyRght varchar
emplrId varchar
seq int
readCnt int
sido tinyint
workTyp char
applyDcmnt varchar
findYmd2 smalldatetime
gugun tinyint
finishYmd bit
ymd smalldatetime
particular char
position varchar
logo varchar
endymd smalldatetime
jobtype1 tinyint
jobtype2 tinyint
jobtype3 tinyint
paytype char
pay1 int
pay2 bit
suprise tinyint
ts timestamp
visible bit
createdYmd smalldatetime
parkchangsoon,
The limited duration high CPU usage you are seeing during your scheduled
Incremental Population is expected and normal as there is a lot of internal
work being processed that is not only CPU intensive, but memory and disk i/o
intensive, but only for specific durations. You can reduce the priority of
the MSSearch service via sp_fulltext_service 'resource_usage' <value>, where
<value> is by default set to 3, and set it to 1 (background). However, this
will have the affect of causing the Incremental Population to take much
longer time to complete.
Since you are using SQL Server 2000, I'd highly recommend that you look at
"Change Tracking" and "Updated Index in Background" (see the BOL for more
info on Change Tracking) as this will eliminate the need for your scheduled
Incremental Population and allow your FT Catalog to be updated in near
real-time as the FT-enabled column is updated. This will eliminate the high
cpu usage you are seeing during the scheduled Incremental Population.
Regards,
John
"parkchangsoon" <dev_sr@.albaroot.com> wrote in message
news:#PZ$lvLXEHA.2972@.TK2MSFTNGP12.phx.gbl...
> os : win2000
> sql : sql2000 enterprise
> table name : job
> I created fulltext catalog
> table is job column name : title
> full population is every sunday am 5:00
> incremental population is every 2hours from 7am to next day 3am
> whenever server is running the both population, cpu is full 100%(see the
> task manager )
> Question : can i reduce the cpu use?
> the columns are updated frequently are readcnt of job table
> if i remove the readcnt column, the cpu use will reduce?
> thanks...
>
> below is the result of sp_spaceused job
> name rows reserved data index_size unused
> ----
> Jobs 259845 468808 KB 214264 KB 28128 KB 226416 KB
> column_name type
> --
> title varchar(100)
> findPrsn tinyint
> ageZn varchar
> applyRght varchar
> emplrId varchar
> seq int
> readCnt int
> sido tinyint
> workTyp char
> applyDcmnt varchar
> findYmd2 smalldatetime
> gugun tinyint
> finishYmd bit
> ymd smalldatetime
> particular char
> position varchar
> logo varchar
> endymd smalldatetime
> jobtype1 tinyint
> jobtype2 tinyint
> jobtype3 tinyint
> paytype char
> pay1 int
> pay2 bit
> suprise tinyint
> ts timestamp
> visible bit
> createdYmd smalldatetime
>

Saturday, February 25, 2012

[Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name

From Enterpise Manager I create a user, giving all available rights. I have created a DB and set this user to be everything for it (except deny). I create tables as this user on the above DB. When I try to access these tables with an app using ODBC, the specific error appears.
Please assist fellas - situation critical.
Thanx.Refer to this KBA (http://support.microsoft.com/default.aspx?scid=http://support.microsoft.com:80/support/kb/articles/Q218/9/95.asp&NoWebContent=1) to resolve the issue.|||Thanx for the info but it didn't work.|||If you created the objects as the user, then they are owned by the user, and will be named [ownername].[objectname].

If your sql code does not specify a ownername sqlserver assumes dbo ownership, and your objects don't exists as dbo.[objectname].

Either specify the owner name when you reference the objects,
or instead of granting all individual rights to the user just grant it dbo access to the database. Then any objects it creates will be dbo owned and will not need to be fully referenced.

blindman

[Microsoft][ODBC SQL Server Driver] Timeout expired

I am getting this error when I try and look at the Top 1000 records of a view
that I have created, in Enterprise Manager. If I copy the SQL for the View
into Query Analyzer, it returns results fine (in approx. 2 minutes). In the
Enterprise Manager Properties Advanced window, I have set Query timeout to
both 0 (never times out?) and 60000 (100 minutes?) to no avail. Any
suggestions would be greatly appreciated.What is the exact error number and message? There are several types of
timeouts. If you are not specifying the correct parameter, it will continue
to timeout on that type.
Sincerely,
Anthony Thomas
"Gary W. Hinkel" <GaryWHinkel@.discussions.microsoft.com> wrote in message
news:E43E35ED-809B-4DF4-8869-0EE3A49F9EEF@.microsoft.com...
I am getting this error when I try and look at the Top 1000 records of a
view
that I have created, in Enterprise Manager. If I copy the SQL for the View
into Query Analyzer, it returns results fine (in approx. 2 minutes). In the
Enterprise Manager Properties Advanced window, I have set Query timeout to
both 0 (never times out?) and 60000 (100 minutes?) to no avail. Any
suggestions would be greatly appreciated.|||Unfortunately, there is no error number, and the error message is the subject
line of this post. Also, I am not specifying any parameters at all, as this
is happening in Enterrpise Manager, Views, right-click on view, Open View,
Return Top.
"AnthonyThomas" wrote:
> What is the exact error number and message? There are several types of
> timeouts. If you are not specifying the correct parameter, it will continue
> to timeout on that type.
> Sincerely,
>
> Anthony Thomas
>
> --
> "Gary W. Hinkel" <GaryWHinkel@.discussions.microsoft.com> wrote in message
> news:E43E35ED-809B-4DF4-8869-0EE3A49F9EEF@.microsoft.com...
> I am getting this error when I try and look at the Top 1000 records of a
> view
> that I have created, in Enterprise Manager. If I copy the SQL for the View
> into Query Analyzer, it returns results fine (in approx. 2 minutes). In the
> Enterprise Manager Properties Advanced window, I have set Query timeout to
> both 0 (never times out?) and 60000 (100 minutes?) to no avail. Any
> suggestions would be greatly appreciated.
>
>|||If you've changed the SQLEM, Tools, Options, Advanced Tab, Query Timeout
value to 0 and you are still getting the message, then you are probably being
blocked by current processes and are getting a LOCK TIMEOUT (which is really
a Lock Wait timeout). You do not have the ability to modify the Lock Timout
setting through EM; however, you can through Query Analyzer.
The Properties, Advanced Window does not exist for SQLEM. There is a
Properties, Connections, Remote Connections area for the currently selected
database, but that property is a timeout setting for RPC queries, not SQLEM
DMO.
Sincerely,
Anthony Thomas
"Gary W. Hinkel" wrote:
> Unfortunately, there is no error number, and the error message is the subject
> line of this post. Also, I am not specifying any parameters at all, as this
> is happening in Enterrpise Manager, Views, right-click on view, Open View,
> Return Top.
> "AnthonyThomas" wrote:
> > What is the exact error number and message? There are several types of
> > timeouts. If you are not specifying the correct parameter, it will continue
> > to timeout on that type.
> >
> > Sincerely,
> >
> >
> > Anthony Thomas
> >
> >
> > --
> >
> > "Gary W. Hinkel" <GaryWHinkel@.discussions.microsoft.com> wrote in message
> > news:E43E35ED-809B-4DF4-8869-0EE3A49F9EEF@.microsoft.com...
> > I am getting this error when I try and look at the Top 1000 records of a
> > view
> > that I have created, in Enterprise Manager. If I copy the SQL for the View
> > into Query Analyzer, it returns results fine (in approx. 2 minutes). In the
> > Enterprise Manager Properties Advanced window, I have set Query timeout to
> > both 0 (never times out?) and 60000 (100 minutes?) to no avail. Any
> > suggestions would be greatly appreciated.
> >
> >
> >|||Thanks for all your assisstance Anthony. This has cleared things up for me
as far as SQLEM is concerned (ie: I won't use it to display views, I'll use
QA instead).
I had initially thought that if I could resolve the timeout issue in SQLEM,
then the same issues in getting data into MapPoint, Excel, etc. through an
ODBC connection could be resolved as well.
Now at least, I can focus my efforts on getting the View data into
MapPoint/Excel etc.
Thanks again Anthony.
Sincerely,
Gary W. Hinkel
"AnthonyThomas" wrote:
> If you've changed the SQLEM, Tools, Options, Advanced Tab, Query Timeout
> value to 0 and you are still getting the message, then you are probably being
> blocked by current processes and are getting a LOCK TIMEOUT (which is really
> a Lock Wait timeout). You do not have the ability to modify the Lock Timout
> setting through EM; however, you can through Query Analyzer.
> The Properties, Advanced Window does not exist for SQLEM. There is a
> Properties, Connections, Remote Connections area for the currently selected
> database, but that property is a timeout setting for RPC queries, not SQLEM
> DMO.
> Sincerely,
>
> Anthony Thomas
>
> "Gary W. Hinkel" wrote:
> > Unfortunately, there is no error number, and the error message is the subject
> > line of this post. Also, I am not specifying any parameters at all, as this
> > is happening in Enterrpise Manager, Views, right-click on view, Open View,
> > Return Top.
> >
> > "AnthonyThomas" wrote:
> >
> > > What is the exact error number and message? There are several types of
> > > timeouts. If you are not specifying the correct parameter, it will continue
> > > to timeout on that type.
> > >
> > > Sincerely,
> > >
> > >
> > > Anthony Thomas
> > >
> > >
> > > --
> > >
> > > "Gary W. Hinkel" <GaryWHinkel@.discussions.microsoft.com> wrote in message
> > > news:E43E35ED-809B-4DF4-8869-0EE3A49F9EEF@.microsoft.com...
> > > I am getting this error when I try and look at the Top 1000 records of a
> > > view
> > > that I have created, in Enterprise Manager. If I copy the SQL for the View
> > > into Query Analyzer, it returns results fine (in approx. 2 minutes). In the
> > > Enterprise Manager Properties Advanced window, I have set Query timeout to
> > > both 0 (never times out?) and 60000 (100 minutes?) to no avail. Any
> > > suggestions would be greatly appreciated.
> > >
> > >
> > >

Sunday, February 19, 2012

[DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied - PLEASE

Hi have created an application in asp.net to connect to a sql database.
The reason why I am sooooo very confused is because the connection to the database works if i keep the project on my local machine and connect to the database on the server, but as soon as I upload the project to the server, I get the following error when I try to connect to the database:
[DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied
Can anyone tell me why this is happenning? - PLEASE - Any help will be appreciated
Thanks,
Danielle

you might want to check your connection string and make sure its pointing to the rigtht database server.

Thursday, February 16, 2012

[Bug] Jump to report from a Table (second page on HTML)

This seems like a bug:
I created a "jump to report" action on a TABLE in my report.
It works fine. But - when the table becomes long, and exceeds one
page on the HTML viewer, the "Jump to report" doesn't work anymore.
It keep throwing up blank pages.
Has anyone else encountered this?One question, is this from the IDE or from a deployed report. I have had a
variety of funky oddities that are only in the IDE, the deployed report
works fine.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Harsh" <harsh.vb@.gmail.com> wrote in message
news:1e70ecdd.0411130717.8685260@.posting.google.com...
> This seems like a bug:
> I created a "jump to report" action on a TABLE in my report.
> It works fine. But - when the table becomes long, and exceeds one
> page on the HTML viewer, the "Jump to report" doesn't work anymore.
> It keep throwing up blank pages.
> Has anyone else encountered this?

[Ask]Getting files from pocket pc emulator to desktop pc

hi...!
I'm creating a small device application using visual studio.NET 2003. Is it possible to get a sql server ce database file (.sdf) that i created at the pocket pc emulator to my desktop pc ?
If that's possible, then can you tell me how to do that ?

thanx b4

Moving this thread to SQL Mobile forum where it has got better chances of being answered.

-Thanks,

Mohit

|||

Yes, you sure can copy this file (e.g. by using file explorer on emulator and network drive on PC). But you can't use it on desktop.

|||Can you tell me what should i do to copy the file, more specifically (step by step) ?
I don't have a plan to use it on desktop, I just want to copy the .sdf file from emulator to desktop, then to the real pocket pc

thanx
|||

Please see this (don't mind it mentions wireless network, work the same way on emulator):

http://pocketpccentral.net/help/browsewlan.htm

Monday, February 13, 2012

[2005] Who created this table?

Is there a command I can use to find out WHO created a specific table?
~LeShort of asking your coworkers?

If you have Lumigent's Log Explorer, you can extract some of that information (at least when the table was created) from the transaction logs or the transaction log backups.

If you do not have the applicable transaction logs, you are pretty much reduced to trying to figure out who has rights to create tables, and asking them.|||Unless it was a sandbox (so who would care anyway), I would have total control over my databases.

I'm not too sanguin about a wild west show|||Unless it was a sandbox (so who would care anyway), I would have total control over my databases.

I'm not too sanguin about a wild west show

It is a sandbox, but we told our developers not to create dummy tables. So we are trying to crack down.

So it looks like Sql Server 2005 has NO way of determining who created a table. That's too bad.

`Le|||For 2005 you can define DDL triggers that will fire on a create table statement. You can use them to create an audit trail.

Saturday, February 11, 2012

@TimeStamp on file delivery

Hi guru,
I am running into a problem where I have no solution. I have created weekly
SQL subscription and choose delivery method to File Share which is working
fine. My manager wants to have date on each published file. I have found on
MSDN that I could use @.Timestamp variable at the end of file name that will
insert the time but the problem is the format. The timestamp is inserted in
Year_Min_Date_random numbers. It is not inserting month. e.g I published one
report today at 2:05 the time stamp was 2007_05_13_120552. I don;t know why
it is inserting min not months. Is it a bug or is there any solution to that.
I appreciate your help
Thanks
SaeedThat's strange...
I just tried it and got
Test2007_04_15_170209.xml
... which is correct.
What is the locale (regional information) for the computer that is running
Reporting Services? Anything in the way the regional formats are being
applied to date and times that might explain this?
If you open a table in the ReportServer database using Management Studio and
look at the contents of a date column in a table (say, ModifiedDate in the
Catalog table), do the contents look correct to you?
And, this is probably a completely stupid possibility but... any chance the
server itself has the WRONG date and time set?
>L<
"SA" <SA@.discussions.microsoft.com> wrote in message
news:DA8C1BFB-AFE6-419D-BAF8-3E49AB668629@.microsoft.com...
> Hi guru,
> I am running into a problem where I have no solution. I have created
> weekly
> SQL subscription and choose delivery method to File Share which is working
> fine. My manager wants to have date on each published file. I have found
> on
> MSDN that I could use @.Timestamp variable at the end of file name that
> will
> insert the time but the problem is the format. The timestamp is inserted
> in
> Year_Min_Date_random numbers. It is not inserting month. e.g I published
> one
> report today at 2:05 the time stamp was 2007_05_13_120552. I don;t know
> why
> it is inserting min not months. Is it a bug or is there any solution to
> that.
> I appreciate your help
> Thanks
> Saeed

@bitmap was not supplied to upd procedure in transactional

Initially when I had setup my transactional replication I had created it with
out taking a snapshot and by synching the databases.
Now I am trying to add new table to one of my existing publication in
production by creating scripts for ins, upd and del in test server and
deploying it to subscriber but it throws an error saying
"sp_MSupd_tablenameexpects parameter '@.bitmap', which was not supplied"
Inserts just work fine.
This is what i used to add-
exec sp_addarticle @.publication = 'Repl_Source'
, @.article = TableName
, @.source_table = TableName
, @.sync_object = null
exec sp_addsubscription @.publication = 'Repl_Source'
, @.article = TableName
, @.subscriber = SERVERNAME
, @.destination_db = 'Repl_Destination'
how many columns are in your table?
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
"SQL Replication Guy" <SQLReplicationGuy@.discussions.microsoft.com> wrote in
message news:AF5F5FE4-E43C-48C6-8FF9-9739C7C3DE62@.microsoft.com...
> Initially when I had setup my transactional replication I had created it
> with
> out taking a snapshot and by synching the databases.
> Now I am trying to add new table to one of my existing publication in
> production by creating scripts for ins, upd and del in test server and
> deploying it to subscriber but it throws an error saying
> "sp_MSupd_tablenameexpects parameter '@.bitmap', which was not supplied"
> Inserts just work fine.
> This is what i used to add-
> exec sp_addarticle @.publication = 'Repl_Source'
> , @.article = TableName
> , @.source_table = TableName
> , @.sync_object = null
> exec sp_addsubscription @.publication = 'Repl_Source'
> , @.article = TableName
> , @.subscriber = SERVERNAME
> , @.destination_db = 'Repl_Destination'
>
>
|||18 columns.
If I rememebr correctly I have tested same before a 6 months agao on
different table and i rememeber it working. Not sure what changed.
"Hilary Cotter" wrote:

> how many columns are in your table?
> --
> 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
> "SQL Replication Guy" <SQLReplicationGuy@.discussions.microsoft.com> wrote in
> message news:AF5F5FE4-E43C-48C6-8FF9-9739C7C3DE62@.microsoft.com...
>
>
|||Hi Hilary,
I think this is what thats happenening
- As I have scripted ins, upd and del scripts in dev environment by
generating them by running snapshot in transactional replication, those are
scripted with @.bitmap varaiable.
- Looks like the code I have used to add subscription
exec sp_addsubscription @.publication = 'Repl_Source'
, @.article = TableName
, @.subscriber = SERVERNAME
, @.destination_db = 'Repl_Destination'
is trying for ins, upd and del procs with no @.bitmap.
Instead of above code I have used
exec sp_addsubscription
@.publication = Repl_Source,
@.article = TableName,
@.subscriber = SERVERNAME,
@.destination_db = 'Repl_Destination',
@.sync_type = N'automatic',
@.subscription_type = N'pull',
@.update_mode = N'read only'
then when i made data changes it asked for snapshot to be execute dfirst and
it created sp's at destination with out bitmap
As all my other sp's were created during initial setup with bitmap i would
like them to be in sync . rather than some with bitmap variable and some with
out..
Any advise....
"SQL Replication Guy" wrote:
[vbcol=seagreen]
> 18 columns.
>
> If I rememebr correctly I have tested same before a 6 months agao on
> different table and i rememeber it working. Not sure what changed.
> "Hilary Cotter" wrote:
|||return to your publisher and in qa change to the publication database, then
recreate your replication procs by issuing a sp_scriptpublicationcustomprocs
'publicationName'
then copy what you find in the results pane, go to the subscriber, change to
the subscription database, and paste these results into qa and run them.
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
"SQL Replication Guy" <SQLReplicationGuy@.discussions.microsoft.com> wrote in
message news:C261558E-58A6-4810-A5E1-AB24439B9A9E@.microsoft.com...[vbcol=seagreen]
> Hi Hilary,
> I think this is what thats happenening
> - As I have scripted ins, upd and del scripts in dev environment by
> generating them by running snapshot in transactional replication, those
> are
> scripted with @.bitmap varaiable.
> - Looks like the code I have used to add subscription
> exec sp_addsubscription @.publication = 'Repl_Source'
> , @.article = TableName
> , @.subscriber = SERVERNAME
> , @.destination_db = 'Repl_Destination'
> is trying for ins, upd and del procs with no @.bitmap.
>
> Instead of above code I have used
> exec sp_addsubscription
> @.publication = Repl_Source,
> @.article = TableName,
> @.subscriber = SERVERNAME,
> @.destination_db = 'Repl_Destination',
> @.sync_type = N'automatic',
> @.subscription_type = N'pull',
> @.update_mode = N'read only'
> then when i made data changes it asked for snapshot to be execute dfirst
> and
> it created sp's at destination with out bitmap
>
> As all my other sp's were created during initial setup with bitmap i would
> like them to be in sync . rather than some with bitmap variable and some
> with
> out..
> Any advise....
>
> "SQL Replication Guy" wrote:
|||Hi Hilary,
I was trying several options soory could get to try your option but this
worked--
sp_addarticle
@.publication = 'Repl_Source',
@.article = 'TableName',
@.source_object = 'TableName',
@.destination_table = 'TableName',
@.ins_cmd = 'call sp_MSins_TableName,
@.del_cmd = 'call sp_MSdel_TableName',
@.upd_cmd = 'mcall sp_MSupd_TableName', -- MCALL CREATED bitmap in UPD sp
@.type = 'logbased',
@.pre_creation_cmd = 'drop',
@.status = 16,
@.source_owner = 'dbo',
@.vertical_partition = 'false',
@.filter = Null,
@.auto_identity_range = 'false'
and then
exec sp_addsubscription @.publication = 'Repl_Source'
, @.article = TableName
, @.subscriber = SERVERNAME
, @.destination_db = 'Repl_Destination'
"SQL Replication Guy" wrote:

> Initially when I had setup my transactional replication I had created it with
> out taking a snapshot and by synching the databases.
> Now I am trying to add new table to one of my existing publication in
> production by creating scripts for ins, upd and del in test server and
> deploying it to subscriber but it throws an error saying
> "sp_MSupd_tablenameexpects parameter '@.bitmap', which was not supplied"
> Inserts just work fine.
> This is what i used to add-
> exec sp_addarticle @.publication = 'Repl_Source'
> , @.article = TableName
> , @.source_table = TableName
> , @.sync_object = null
> exec sp_addsubscription @.publication = 'Repl_Source'
> , @.article = TableName
> , @.subscriber = SERVERNAME
> , @.destination_db = 'Repl_Destination'
>
>

Thursday, March 22, 2012

_WA_SYS indexes

_WA_ entries in sysindexes

_WA_ entries in sysindexes

Monday, March 19, 2012

[SQL Server 2005] Primary vs Secondary file-groups - deciding which table goes where

Sunday, March 11, 2012

[SQL 2005] Restore question

[SQL 2005] Restore question

[SQL 2005] Restore question

[S0001][unixODBC][Easysoft][ODBC SQL Server Driver][SQL Server]Login failed for user 'test1'

Thursday, March 8, 2012

[Q] cpu is full.. 100%

Saturday, February 25, 2012

[Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name

[Microsoft][ODBC SQL Server Driver] Timeout expired

[Microsoft][ODBC SQL Server Driver] Timeout expired

[Microsoft][ODBC SQL Server Driver] Timeout expired

Sunday, February 19, 2012

[DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied - PLEASE

Thursday, February 16, 2012

[Bug] Jump to report from a Table (second page on HTML)

[Ask]Getting files from pocket pc emulator to desktop pc

Monday, February 13, 2012

[2005] Who created this table?

Saturday, February 11, 2012

@TimeStamp on file delivery

@bitmap was not supplied to upd procedure in transactional

sql rowcount

Blog Archive

About Me