How do i measure bandwith usage for merge replication SQL 2005 to Sql server mobile 2005?

Hi,

Is there any way to measure bandwith usage during merge replication between sql server 2005 and sql server mobile 2005 running on a cradled wm5 mobile device.

Attaching the windows performance monitor to the network connection established over usb would work although I was wondering if there was something specific for this case integrated into Sql server 2005 / sql server mobile 2005 / Sql server management studio / third party tools that i could use ?

thnx,

pdns.

Not for mobile, but if you had a SQL Server subscriber (Dev, Ent, Std, Express) you can add (unsupported) merge agent parameter "-T 101" which will give command-line output statistics for that particular sync, there might be some net statistics IO output which is not always accurate unfortunately.

However we can take this into consideration for hte next release of SQL Server, although I cannot make any guarantees.

|||

can u tell me full command syntax that how we could use it on command line

thanxs in advanc

Ahmad Drshen

|||Search BOoks Online for topic "Replication merge agent", you should see the command line utility topic. YOu can also drill down into the agent job steps to see the exact parameters and values that your job is using to execute a push/pull merge agent.

how do I make 30 sec running query (select c1 sum(x) from t1 where c1 > 1000 group by c1) run

It seems when I run the query with the set staticts IO on then statistic reports back with the 'work table', and the query takes 30+ sec. if the worktable is ommited(whatever the reason?) the query take less 1 sec.

Here is my take, I believe work table is created in tempdb...and if not then whole query is using the cached page, am I right?

if I am right then the theory is, if I increase the (via sp_configure) server min memory setting and min query memory, the query ought use the cached page and return in less 1 sec. (specially there is absolutely no one but me on the server), so far I can't make it go faster...what setting am I missing to make it run faster?

Another question is if the query can not avoid but use the tempdb, is it going to always be 30 sec+ time? why is tempdb involvement make it go so much slower?

Thanks in for you help in advance

if the memory available is not enough for internal operations like aggregation and ordering, SQL Server will implictly go to Tempdb and will store the results intermediately here. You cannot avoid is, beside putting more available RAM on the process. Don′t know why this slows down your process that much, did you had a look in the SQL Server logs, esprically on database growth ? Maybe SQL Server is increasing the data files one by one, leading to the problem that the query wioll be halted for the time needed to extend the database.

Jens K. Suessmeyer

http://www.sqlserver2005.de

How do I know if my sql server needs more memroy ?

Hi,
We have an OLTP system with 600+ connections, 30 gb database running on a
4 CPU and 4.2gb memory, out of which 3.5 gb is allocated to SQL Server (usin
g
fixed memory). SQL Server is on its own dedicated machine.
Our clients are complaining about performance. Disk utilization is around
50%.Memory util always says 90% since I've set it to use 'fixed' memory. Ho
w
do I tell if the sql server needs more memory ?
TIA
MOThe following describes how to use Performance Monitor and performance
related SQL Server Profiler traces. In regard to available memory, keep in
mind that it is normal and beneficial for SQL Server to dynamically buffer
almost all the memory allocated to it, and it's excessive memory paging and
a poor cache hit ratio that you want to look for.
SQL Server 2000 Administrator's Pocket Consultant: Profiling and Monitoring
http://www.microsoft.com/technet/pr...s/c10ppcsq.mspx
SQL Server 2000 Operations Guide: Monitoring and Control
http://www.microsoft.com/technet/pr...in/sqlops5.mspx
Chapter 14 - Improving SQL Server Performance
http://msdn.microsoft.com/library/d...
etchapt14.asp
"MO" <MO@.discussions.microsoft.com> wrote in message
news:1E532BC5-08AC-4651-A9E5-A313E3C80C0A@.microsoft.com...
> Hi,
> We have an OLTP system with 600+ connections, 30 gb database running on a
> 4 CPU and 4.2gb memory, out of which 3.5 gb is allocated to SQL Server
> (using
> fixed memory). SQL Server is on its own dedicated machine.
> Our clients are complaining about performance. Disk utilization is around
> 50%.Memory util always says 90% since I've set it to use 'fixed' memory.
> How
> do I tell if the sql server needs more memory ?
> TIA
> MO|||MO wrote:
> Hi,
> We have an OLTP system with 600+ connections, 30 gb database running
> on a 4 CPU and 4.2gb memory, out of which 3.5 gb is allocated to SQL
> Server (using fixed memory). SQL Server is on its own dedicated
> machine.
> Our clients are complaining about performance. Disk utilization is
> around 50%.Memory util always says 90% since I've set it to use
> 'fixed' memory. How do I tell if the sql server needs more memory ?
> TIA
> MO
You also need to look at your disk subsytem and how you are using it
with SQL Server. The wrong RAID solution or poor placement of data, log,
and tempdb can all play into bad overall performance. As always, I first
recommend you performance tune your SQL. Bad SQL = Bad performance,
regardless of how much hardware you throw at it.
David Gugick - SQL Server MVP
Quest Software|||Ditto with that bad sql. I now have to go back fix all my bad SQL that I
wrote few years ago. The performance has been improving and no new hardware
upgraded was needed.
"David Gugick" <david.gugick-nospam@.quest.com> wrote in message
news:uC6D8RDKGHA.312@.TK2MSFTNGP09.phx.gbl...
> MO wrote:
> You also need to look at your disk subsytem and how you are using it with
> SQL Server. The wrong RAID solution or poor placement of data, log, and
> tempdb can all play into bad overall performance. As always, I first
> recommend you performance tune your SQL. Bad SQL = Bad performance,
> regardless of how much hardware you throw at it.
> --
> David Gugick - SQL Server MVP
> Quest Software
>|||In addition to upgrading hardware or optimizing your queries / transactions,
you may want to even consider architectural changes to your database model.
If your OLTP system is currently the data source for data mining, financial
reporting, or other types of resource intensive queries that would block
daily operations type transactions, then perhaps the queries could instead
be performed against a standby or reporting server.
http://vyaskn.tripod.com/maintainin..._sql_server.htm
Understanding and Resolving SQL Server Blocking Problems:
http://support.microsoft.com/defaul...kb;EN-US;224453
Is it really necessary to retain 30 GB of (historical?) data in an online
transaction processing (OLTP) system? If this is an operational system, then
perhaps you only need the current month's worth of data, and the remaining
data can be migrated to an operational data store (ODS) database or server.
The Operational Data Store:
http://www.dmreview.com/article_sub.cfm?articleId=469
Using Partitions in a Microsoft SQL Server 2000 Data Warehouse
http://msdn.microsoft.com/library/d...nDW.
htm
http://www.microsoft.com/technet/pr.../2005/spdw.mspx
"MO" <MO@.discussions.microsoft.com> wrote in message
news:1E532BC5-08AC-4651-A9E5-A313E3C80C0A@.microsoft.com...
> Hi,
> We have an OLTP system with 600+ connections, 30 gb database running on a
> 4 CPU and 4.2gb memory, out of which 3.5 gb is allocated to SQL Server
> (using
> fixed memory). SQL Server is on its own dedicated machine.
> Our clients are complaining about performance. Disk utilization is around
> 50%.Memory util always says 90% since I've set it to use 'fixed' memory.
> How
> do I tell if the sql server needs more memory ?
> TIA
> MO

How do I know if it is working?

Hi
I think I have installed MSDE sucessfully. The new servie is running, but how can I test ifit is working? Can I place my files anywhere on the system?
All help appreciatedAs the service is running try to access it using "osql" utility.

If you are talking about data files, yes you can declare the path while creating your database...

check create database syntax for thissql

How do I know if an index column is in descending order from SQL Server?

Hey all that I want is to be able to run from another DB.
This "sp_MShelpindex" like the "indexkey_property" only work running from
the DB where the index sits.
I need to join this information from multiple DBs in a single query, and I
was wondering if (and how) this is possible.
Thanks
>
> "GregO" <grego@.community.nospam> wrote in message
> news:uX7vlRJrFHA.904@.tk2msftngp13.phx.gbl...
>> Hi Peter
>>
>> EXECUTE sp_MShelpindex N'authors', N'aunmind'
>>
>> try this in PUBS
>>
>> http://www.sql-server-performance.com/ac_sql_server_7_undocumented_sp.asp
>>
>>
>> --
>> kind regards
>> Greg O
>> Need to document your databases. Use the firs and still the best AGS SQL
>> Scribe
>> http://www.ag-software.com
>>
>>
>> "Peter Reid" <noreply@.microsoft.com> wrote in message
>> news:eubQTNIrFHA.904@.tk2msftngp13.phx.gbl...
>> How do I know if an index column is in descending order from SQL Server?
>>
>> I don't want to use "indexkey_property" as it doesn't work from another
>> DB.
>> I also know that I can do something like this:
>>
>> USE <db1>
>> select into a temp table
>>
>> USE <db2>
>> select into another temp table
>>
>>
>> But what I'm actually interested in knowing is where this information is
>> stored in SQL Server (as it doesn't seam to on the sysindexkeys table),
>> and furthermore how to query it.
>>
>> Thanks
>>
>>
>>
>>
>
>Hi Peter
Did you try:
EXECUTE pubs..sp_MShelpindex N'authors', N'aunmind'
John
"Peter Reid" wrote:
> Hey all that I want is to be able to run from another DB.
> This "sp_MShelpindex" like the "indexkey_property" only work running from
> the DB where the index sits.
> I need to join this information from multiple DBs in a single query, and I
> was wondering if (and how) this is possible.
> Thanks
> >
> > "GregO" <grego@.community.nospam> wrote in message
> > news:uX7vlRJrFHA.904@.tk2msftngp13.phx.gbl...
> >> Hi Peter
> >>
> >> EXECUTE sp_MShelpindex N'authors', N'aunmind'
> >>
> >> try this in PUBS
> >>
> >> http://www.sql-server-performance.com/ac_sql_server_7_undocumented_sp.asp
> >>
> >>
> >> --
> >> kind regards
> >> Greg O
> >> Need to document your databases. Use the firs and still the best AGS SQL
> >> Scribe
> >> http://www.ag-software.com
> >>
> >>
> >> "Peter Reid" <noreply@.microsoft.com> wrote in message
> >> news:eubQTNIrFHA.904@.tk2msftngp13.phx.gbl...
> >> How do I know if an index column is in descending order from SQL Server?
> >>
> >> I don't want to use "indexkey_property" as it doesn't work from another
> >> DB.
> >> I also know that I can do something like this:
> >>
> >> USE <db1>
> >> select into a temp table
> >>
> >> USE <db2>
> >> select into another temp table
> >>
> >>
> >> But what I'm actually interested in knowing is where this information is
> >> stored in SQL Server (as it doesn't seam to on the sysindexkeys table),
> >> and furthermore how to query it.
> >>
> >> Thanks
> >>
> >>
> >>
> >>
> >
> >
>
>sql

How do i know if a trace is running?

Hi all,

We had a problem with a database so i used profiler to see where it came from a couple of days ago. Now the performnce on the server has gone really bad, so one of the things i want to check if maybe the trace is still running somewhere on the background. How can i check this? When i run the profiler, i can't see it in current activity. How can be certain this is or is not the problem?

I'm using SQL 2000 SP4.

Thanks in advance,

John

There is a system function that you can run that will return the information for a trace, or all existing traces. The function call is:

select*from::fn_trace_getinfo(default)

|||

Thanks Tim,

There were none, the problem was somewhere else.

Thanks,

John

How do I keep a report from automatically running?

This is a multi-part message in MIME format.
--=_NextPart_000_000A_01C75C1E.0326B0B0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Perhaps this is just behavior in the dev environment, but if I provide = default values for all of my paramters, the report runs automatically = when I switch to preview.
Is there a way to provide defaults, but still require the user to click = view before the report runs?
Thanks.
--=_NextPart_000_000A_01C75C1E.0326B0B0
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
Perhaps this is just behavior in the = dev environment, but if I provide default values for all of my paramters, = the report runs automatically when I switch to preview.

Is there a way to provide defaults, but = still require the user to click view before the report runs?

Thanks.
--=_NextPart_000_000A_01C75C1E.0326B0B0--On Mar 1, 5:23 pm, <rlrc...@.newsgroups.nospam> wrote:
> Perhaps this is just behavior in the dev environment, but if I provide default values for all of my paramters, the report runs automatically when I switch to preview.
> Is there a way to provide defaults, but still require the user to click view before the report runs?
> Thanks.
As far as I know, there is not a way to keep the report from
automatically running without removing at least one of the parameter's
default values.
Regards,
Enrique Martinez
Sr. SQL Server Developer|||You can probably create a parameter and hide it...and then not provide it
default value.
-
HelpSeeker
"rlrcstr@.newsgroups.nospam" wrote:
> Perhaps this is just behavior in the dev environment, but if I provide default values for all of my paramters, the report runs automatically when I switch to preview.
> Is there a way to provide defaults, but still require the user to click view before the report runs?
> Thanks|||Hi rlrcstr,
How is everything going? Please feel free to let me know if you need any
assistance.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.

How do I interpret results from a failed integrity test?

I have setup a database maintenance plan that consistently fails when
attempting to check the integrity of my database. I have tried running
the maintenance plan switching the include/exclude indexes options,
without any difference to the result. The logging report for the
failing integrity step of the maintenance plan includes the following:
=================================================================== Microsoft (R) SQLMaint Utility (Unicode), Version Logged on to SQL
Server 'myServer' as 'myServer\myUserName' (trusted)
Starting maintenance plan 'myDataBase Maintenance Plan' on 26/08/2004
11:20:23 AM
[1] Database myDataBase: Check Data Linkage...
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 8903:
[Microsoft][ODBC SQL Server Driver][SQL Server]Extent (1:357424) in
database ID 7 is allocated in both GAM (1:2) and SGAM (1:3).
[Microsoft][ODBC SQL Server Driver][SQL Server]CHECKDB found 1
allocation errors and 0 consistency errors not associated with any
single object.
[Microsoft][ODBC SQL Server Driver][SQL Server]CHECKDB found 1
allocation errors and 0 consistency errors in database 'myDataBase'.
[Microsoft][ODBC SQL Server Driver][SQL Server]repair_allow_data_loss
is the minimum repair level for the errors found by DBCC CHECKDB
(myDataBase noindex).
The following errors were found:
[Microsoft][ODBC SQL Server Driver][SQL Server]Extent (1:357424) in
database ID 7 is allocated in both GAM (1:2) and SGAM (1:3).
[Microsoft][ODBC SQL Server Driver][SQL Server]CHECKDB found 1
allocation errors and 0 consistency errors not associated with any
single object.
[Microsoft][ODBC SQL Server Driver][SQL Server]CHECKDB found 1
allocation errors and 0 consistency errors in database 'myDataBase'.
[Microsoft][ODBC SQL Server Driver][SQL Server]repair_allow_data_loss
is the minimum repair level for the errors found by DBCC CHECKDB
(myDataBase noindex).
** Execution Time: 0 hrs, 0 mins, 8 secs **
Deleting old text reports... 1 file(s) deleted.
End of maintenance plan 'myDataBase Maintenance Plan' on 26/08/2004
11:20:31 AM
SQLMAINT.EXE Process Exit Code: 1 (Failed)
===================================================================
From this I assume that "Extent (1:357424) in database ID 7 is
allocated in both GAM (1:2) and SGAM (1:3)" is the primary problem.
What does this mean and how do I resolve the problem?
Thanks,
StephenStephen,
Check the article at
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/trblsql/tr_reslsyserr_2_8ape.asp.
--
Dejan Sarka, SQL Server MVP
Associate Mentor
Solid Quality Learning
More than just Training
www.SolidQualityLearning.com
"Stephen Miller" <jsausten@.hotmail.com> wrote in message
news:cdb404de.0408251803.4bd6cfdd@.posting.google.com...
> I have setup a database maintenance plan that consistently fails when
> attempting to check the integrity of my database. I have tried running
> the maintenance plan switching the include/exclude indexes options,
> without any difference to the result. The logging report for the
> failing integrity step of the maintenance plan includes the following:
> ===================================================================> Microsoft (R) SQLMaint Utility (Unicode), Version Logged on to SQL
> Server 'myServer' as 'myServer\myUserName' (trusted)
> Starting maintenance plan 'myDataBase Maintenance Plan' on 26/08/2004
> 11:20:23 AM
> [1] Database myDataBase: Check Data Linkage...
> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 8903:
> [Microsoft][ODBC SQL Server Driver][SQL Server]Extent (1:357424) in
> database ID 7 is allocated in both GAM (1:2) and SGAM (1:3).
> [Microsoft][ODBC SQL Server Driver][SQL Server]CHECKDB found 1
> allocation errors and 0 consistency errors not associated with any
> single object.
> [Microsoft][ODBC SQL Server Driver][SQL Server]CHECKDB found 1
> allocation errors and 0 consistency errors in database 'myDataBase'.
> [Microsoft][ODBC SQL Server Driver][SQL Server]repair_allow_data_loss
> is the minimum repair level for the errors found by DBCC CHECKDB
> (myDataBase noindex).
> The following errors were found:
> [Microsoft][ODBC SQL Server Driver][SQL Server]Extent (1:357424) in
> database ID 7 is allocated in both GAM (1:2) and SGAM (1:3).
> [Microsoft][ODBC SQL Server Driver][SQL Server]CHECKDB found 1
> allocation errors and 0 consistency errors not associated with any
> single object.
> [Microsoft][ODBC SQL Server Driver][SQL Server]CHECKDB found 1
> allocation errors and 0 consistency errors in database 'myDataBase'.
> [Microsoft][ODBC SQL Server Driver][SQL Server]repair_allow_data_loss
> is the minimum repair level for the errors found by DBCC CHECKDB
> (myDataBase noindex).
> ** Execution Time: 0 hrs, 0 mins, 8 secs **
> Deleting old text reports... 1 file(s) deleted.
> End of maintenance plan 'myDataBase Maintenance Plan' on 26/08/2004
> 11:20:31 AM
> SQLMAINT.EXE Process Exit Code: 1 (Failed)
> ===================================================================>
> From this I assume that "Extent (1:357424) in database ID 7 is
> allocated in both GAM (1:2) and SGAM (1:3)" is the primary problem.
> What does this mean and how do I resolve the problem?
> Thanks,
> Stephen|||Stephen,
If you enounter corruption related error messages the avenue most often
suggested is to restore from a known good backup. If for some reason this
is not available to you I would suggest contacting Microsoft SQL Server
Support to discuss your options and the possible implications of running
dbcc with the repair allow data loss option. Running this has some
significant risks and you may have other options.
http://support.microsoft.com/default.aspx?scid=fh;en-us;Prodoffer41a&sd=GN
Thanks,
David Gerard
Microsoft SQL Server Support

How Do I give EXECUTE Permissions on Stored Procedures?

Hey guys,

I'm pretty new to SQL configuration, and I need to give EXECUTE
persmissions for one of the SQL user roles. I am running SQL 2005
Management Studio Express - free version. I found the list of my
stored procedures, but I can not locate any permissions screen. Can
someone help point me in the right direction? Thanks!alvinstraight38@.hotmail.com wrote:

Quote:

Originally Posted by

Hey guys,
>
I'm pretty new to SQL configuration, and I need to give EXECUTE
persmissions for one of the SQL user roles. I am running SQL 2005
Management Studio Express - free version. I found the list of my
stored procedures, but I can not locate any permissions screen. Can
someone help point me in the right direction? Thanks!
>

if you right click on the SP and click properties, that should bring up
the options.

--sharif|||alvinstraight38@.hotmail.com (alvinstraight38@.hotmail.com) writes:

Quote:

Originally Posted by

I'm pretty new to SQL configuration, and I need to give EXECUTE
persmissions for one of the SQL user roles. I am running SQL 2005
Management Studio Express - free version. I found the list of my
stored procedures, but I can not locate any permissions screen. Can
someone help point me in the right direction? Thanks!

If you want to use the GUI, make sure that you have SP2. I think that
alternative was missing in RTM and SP1.

Then again, in the long run you are better of using GRANT commands.

--
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|||On Sep 20, 4:11 pm, Erland Sommarskog <esq...@.sommarskog.sewrote:

Quote:

Originally Posted by

alvinstraigh...@.hotmail.com (alvinstraigh...@.hotmail.com) writes:

Quote:

Originally Posted by

I'm pretty new to SQL configuration, and I need to give EXECUTE
persmissions for one of the SQL user roles. I am running SQL 2005
Management Studio Express - free version. I found the list of my
stored procedures, but I can not locate any permissions screen. Can
someone help point me in the right direction? Thanks!

>
If you want to use the GUI, make sure that you have SP2. I think that
alternative was missing in RTM and SP1.
>
Then again, in the long run you are better of using GRANT commands.
>
--
Erland Sommarskog, SQL Server MVP, esq...@.sommarskog.se
>
Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books...
Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

Ahh, I see now why I was lost. I right click on the SP, and there is
no option for Properties. Yet, I can set permissions on tables. How
stupid. You mention SP2. How can I tell which service pack I am
running? I went to Help - About and it shows:

Microsoft SQL Server Management Studio Express Version 9.00.2047.00|||alvinstraight38@.hotmail.com (alvinstraight38@.hotmail.com) writes:

Quote:

Originally Posted by

Ahh, I see now why I was lost. I right click on the SP, and there is
no option for Properties. Yet, I can set permissions on tables. How
stupid. You mention SP2. How can I tell which service pack I am
running? I went to Help - About and it shows:
>
Microsoft SQL Server Management Studio Express Version 9.00.2047.00

That's SP1.

--
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|||On Sep 20, 10:26 pm, "alvinstraigh...@.hotmail.com"
<alvinstraigh...@.hotmail.comwrote:

Quote:

Originally Posted by

On Sep 20, 4:11 pm, Erland Sommarskog <esq...@.sommarskog.sewrote:
>
>
>
>
>

Quote:

Originally Posted by

alvinstraigh...@.hotmail.com (alvinstraigh...@.hotmail.com) writes:

Quote:

Originally Posted by

I'm pretty new to SQL configuration, and I need to give EXECUTE
persmissions for one of the SQL user roles. I am running SQL 2005
Management Studio Express - free version. I found the list of my
stored procedures, but I can not locate any permissions screen. Can
someone help point me in the right direction? Thanks!

>

Quote:

Originally Posted by

If you want to use the GUI, make sure that you have SP2. I think that
alternative was missing in RTM and SP1.

>

Quote:

Originally Posted by

Then again, in the long run you are better of using GRANT commands.

>

Quote:

Originally Posted by

--
Erland Sommarskog, SQL Server MVP, esq...@.sommarskog.se

>

Quote:

Originally Posted by

Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books...
Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

>
Ahh, I see now why I was lost. I right click on the SP, and there is
no option for Properties. Yet, I can set permissions on tables. How
stupid. You mention SP2. How can I tell which service pack I am
running? I went to Help - About and it shows:
>
Microsoft SQL Server Management Studio Express Version 9.00.2047.00- Hide quoted text -
>
- Show quoted text -

You can grant permissions dynamically in this way to all db objects:
/* tables and views*/
select 'Grant select,insert,update,delete on '+name+ ' to USER'
from sysobjects
where xtype in ('U','V')

/*Stored procedures*/
select 'Grant exec on '+name+ ' to USER'
from sysobjects
where xtype in ('P')|||abu hisham wrote:

Quote:

Originally Posted by

You can grant permissions dynamically in this way to all db objects:
/* tables and views*/
select 'Grant select,insert,update,delete on '+name+ ' to USER'
from sysobjects
where xtype in ('U','V')
>
/*Stored procedures*/
select 'Grant exec on '+name+ ' to USER'
from sysobjects
where xtype in ('P')

To clarify, this will not directly grant the permissions, but will
output SQL code that can be copy+pasted into Query Analyzer and
executed to grant the permissions.|||abu hisham (yjogee@.hotmail.co.uk) writes:

Quote:

Originally Posted by

You can grant permissions dynamically in this way to all db objects:
/* tables and views*/
select 'Grant select,insert,update,delete on '+name+ ' to USER'
from sysobjects
where xtype in ('U','V')
>
/*Stored procedures*/
select 'Grant exec on '+name+ ' to USER'
from sysobjects
where xtype in ('P')

In SQL 2005 this can be achieved with a single statement:

GRANT SELECT, INSERT, UPDATE, DELETE, EXECUTE
ON SCHEMA::schema_name TO user

Access granted on schema level are inherited by objects in the schema, which
means that it also applies to future objects.
--
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

How do I get Visual Studio to open a deployed report?

Greetings,

I have a report that was previously deployed to a reporting services host (running Reporting Services 2005 SP2) and I'm running the version of Visual Studio that came with reporting services on the same reporting serviceshost. The deployed report was never opened on this particular server, so it doesn't appear in the recent files list. Is there a trick to open a deployed report in all of Visual Studio's report editing glory? Visual Studio has a File Open option to open an analysis services database, a project, or a file. A "deployed report" seems to be none of these options since it's an RDL file stored somewhere that IIS can find it. I know that I can use Report Manager and export the RDL for the report and then edit that RDL file in Visual Studio, but doing that appears to disable all of Visual Studio's report editing features (ie it works no better than notepad). What's the trick to get Visual Studio to open the deployed report and allow all of it's editing tools to work properly and then save the updated report back to the reporting services server?

Thanks,

Ron

In report manager, go to the report properties.

there is a button in there 'save/edit' or similar

you can then save the file locally and open it in vs.net

NB don't forget 2000 rdl files will be automatically upgraded to 2005

How do I fix an intenal NS SP error?

I am in the process of moving some Notification Services 2.0 applications to another server running SQL Server 2005 with Notification Services 2005. I have modified my old ADF files to get rid of the Notify() function as it is no longer supported in version 2005.

When I try to create a new instance using the old IDF file I get some errors, that I believe is internal Notification Services errors: The creation-script tries to create an SP named NSGet<MyAlertName>SMTPNotificationsDeveloperDefined but failed because of an undeclared scalar variable @.workItemId. This is not a variable I have created.

Looking at my old application, currently running fine on SQL Server 2000, I can not find the NSGet<MyAlertName>SMTPNotificationsDeveloperDefined SP, but one that is named NSGet<MyAlertName>SMTPNotifications instead. This SP takes the @.workItemId variable as a parameter, but the new SP (NSGet<MyAlertName>SMTPNotificationsDeveloperDefined) does not.

These stored procedures and the variable is as far as I know created by Notification Services so I don't see how I can correct the error? But as things are now, I'm not able to create the new instance because of this error. I have tried on both Management Studio and the NS command promt - but the same error occurs.

Anyone know how to fix this issue?

Please ignore or delete my post.

It seems that I have done some querying on the old SP which had @.workItemId via an <SQLExpression>.

Sorry for the inconvinience.

How do I find out whats blocking port 1433?

I am running SQL Server 2000 sp4 in local mode on my laptop. I have some
other software which uses 1433 to access SQL. Remember this is all running
locally on a laptop. Port 1433 is being blocked. I have both the Windows
Firewall and the Mcaffee Firewalls disabled.
How can I find out what is blocking the port?
How do you know it's an issue with a blocked port?
If you are receiving an error, post the exact error number
and all details of the message.
-Sue
On Tue, 27 Mar 2007 19:04:07 -0700, KenRoy
<KenRoy@.discussions.microsoft.com> wrote:

>I am running SQL Server 2000 sp4 in local mode on my laptop. I have some
>other software which uses 1433 to access SQL. Remember this is all running
>locally on a laptop. Port 1433 is being blocked. I have both the Windows
>Firewall and the Mcaffee Firewalls disabled.
>How can I find out what is blocking the port?
|||Are you sure you have TCP/IP enabled (and configured for port 1433)? Try
enabling Named Pipes and specify that in an alias and try to connect with QA
that way.
You can use the DOS tracert command to check paths from one ip point to
another.
TheSQLGuru
President
Indicium Resources, Inc.
"KenRoy" <KenRoy@.discussions.microsoft.com> wrote in message
news:69A0FF05-54DD-4135-B9BC-6EA2BECEDEF3@.microsoft.com...
>I am running SQL Server 2000 sp4 in local mode on my laptop. I have some
> other software which uses 1433 to access SQL. Remember this is all
> running
> locally on a laptop. Port 1433 is being blocked. I have both the Windows
> Firewall and the Mcaffee Firewalls disabled.
> How can I find out what is blocking the port?
|||Yes, I am sure it is enabled for port 1433.
"TheSQLGuru" wrote:

> Are you sure you have TCP/IP enabled (and configured for port 1433)? Try
> enabling Named Pipes and specify that in an alias and try to connect with QA
> that way.
> You can use the DOS tracert command to check paths from one ip point to
> another.
>
> --
> TheSQLGuru
> President
> Indicium Resources, Inc.
> "KenRoy" <KenRoy@.discussions.microsoft.com> wrote in message
> news:69A0FF05-54DD-4135-B9BC-6EA2BECEDEF3@.microsoft.com...
>
>
|||Tried to connect with Telnet (telnet localhost 1433) and it say's it blocked.
"Sue Hoegemeier" wrote:

> How do you know it's an issue with a blocked port?
> If you are receiving an error, post the exact error number
> and all details of the message.
> -Sue
> On Tue, 27 Mar 2007 19:04:07 -0700, KenRoy
> <KenRoy@.discussions.microsoft.com> wrote:
>
>
|||I have been trying to solve this problem myself over the last three days
having moved to a new dedicated server with a new host.
Old server was Win2k3 SP1
New server is Win2k3 SP2
I found that the IP Security policy was, by default, set to drop all
packets to 1433\1434.
Add the IP Security Policy snap in to MMC and you'll find it.
This change is in addition to configuring windows firewall.
Jarrod.
*** Sent via Developersdex http://www.codecomments.com ***

How do I find out whats blocking port 1433?

I am running SQL Server 2000 sp4 in local mode on my laptop. I have some
other software which uses 1433 to access SQL. Remember this is all running
locally on a laptop. Port 1433 is being blocked. I have both the Windows
Firewall and the Mcaffee Firewalls disabled.
How can I find out what is blocking the port'How do you know it's an issue with a blocked port?
If you are receiving an error, post the exact error number
and all details of the message.
-Sue
On Tue, 27 Mar 2007 19:04:07 -0700, KenRoy
<KenRoy@.discussions.microsoft.com> wrote:

>I am running SQL Server 2000 sp4 in local mode on my laptop. I have some
>other software which uses 1433 to access SQL. Remember this is all running
>locally on a laptop. Port 1433 is being blocked. I have both the Windows
>Firewall and the Mcaffee Firewalls disabled.
>How can I find out what is blocking the port'|||Are you sure you have TCP/IP enabled (and configured for port 1433)? Try
enabling Named Pipes and specify that in an alias and try to connect with QA
that way.
You can use the DOS tracert command to check paths from one ip point to
another.
TheSQLGuru
President
Indicium Resources, Inc.
"KenRoy" <KenRoy@.discussions.microsoft.com> wrote in message
news:69A0FF05-54DD-4135-B9BC-6EA2BECEDEF3@.microsoft.com...
>I am running SQL Server 2000 sp4 in local mode on my laptop. I have some
> other software which uses 1433 to access SQL. Remember this is all
> running
> locally on a laptop. Port 1433 is being blocked. I have both the Windows
> Firewall and the Mcaffee Firewalls disabled.
> How can I find out what is blocking the port'|||Yes, I am sure it is enabled for port 1433.
"TheSQLGuru" wrote:

> Are you sure you have TCP/IP enabled (and configured for port 1433)? Try
> enabling Named Pipes and specify that in an alias and try to connect with
QA
> that way.
> You can use the DOS tracert command to check paths from one ip point to
> another.
>
> --
> TheSQLGuru
> President
> Indicium Resources, Inc.
> "KenRoy" <KenRoy@.discussions.microsoft.com> wrote in message
> news:69A0FF05-54DD-4135-B9BC-6EA2BECEDEF3@.microsoft.com...
>
>|||Tried to connect with Telnet (telnet localhost 1433) and it say's it blocked
.
"Sue Hoegemeier" wrote:

> How do you know it's an issue with a blocked port?
> If you are receiving an error, post the exact error number
> and all details of the message.
> -Sue
> On Tue, 27 Mar 2007 19:04:07 -0700, KenRoy
> <KenRoy@.discussions.microsoft.com> wrote:
>
>|||I have been trying to solve this problem myself over the last three days
having moved to a new dedicated server with a new host.
Old server was Win2k3 SP1
New server is Win2k3 SP2
I found that the IP Security policy was, by default, set to drop all
packets to 1433\1434.
Add the IP Security Policy snap in to MMC and you'll find it.
This change is in addition to configuring windows firewall.
Jarrod.
*** Sent via Developersdex http://www.codecomments.com ***

How do I find out what version of SQL Server 2000 I'm running?

How do I find out what version of SQL Server 2000 I'm running?
The 'about' dialog box on the SQL Server Service Manager
says my version is 8.00.760 but the latest service pack 3
is version 8.00.760.03. I thought I installed this service
pack already.
How can I know for sure?
Thanks
Brad760 is SP3. You have SP3, regardless of the sub-sub-sub-version number.
"Brad" <bradfordneal2@.yahoo.com> wrote in message
news:00fe01c351f7$379fb010$a101280a@.phx.gbl...
> How do I find out what version of SQL Server 2000 I'm running?
> The 'about' dialog box on the SQL Server Service Manager
> says my version is 8.00.760 but the latest service pack 3
> is version 8.00.760.03. I thought I installed this service
> pack already.
> How can I know for sure?
> Thanks
> Brad|||Never mind I found out by reading the reply a 8:16 am today.
>--Original Message--
>How do I find out what version of SQL Server 2000 I'm running?
>The 'about' dialog box on the SQL Server Service Manager
>says my version is 8.00.760 but the latest service pack 3
>is version 8.00.760.03. I thought I installed this service
>pack already.
>How can I know for sure?
>Thanks
>Brad
>.
>|||Brad,
The following in the readme file for SP3a. (
http://support.microsoft.com/default.aspx?scid=/support/servicepacks/SQL/2000/SP3ReadMe.asp)
Distinguishing Between SP3 and SP3a
To determine whether you have SP3 or SP3a installed, look at the version
number of the Net-Library file, Ssnetlib.dll. If the version number of this
file is 2000.80.760.0, you have SP3; if the version number of this file is
2000.80.766.0, you have SP3a.
Assuming that an instance was installed using the default settings, you can
find the Ssnetlib.dll file in one of these locations:
a.. Default instance: C:\Program Files\Microsoft
SQLServer\Binn\Ssnetlib.dll
b.. Named instance: C:\Program Files\Microsoft
SQLServer\MSSQL$<InstanceName>\Binn\Ssnetlib.dll
To find out whether you have the SP3 or SP3a setup folders, open the
SP3Readme.htm file and read the first page. The service pack version, SP3a
or SP3, is listed at the top of the first page.
Dinesh.
SQL Server FAQ at
http://www.tkdinesh.com
"Brad" <bradfordneal2@.yahoo.com> wrote in message
news:00fe01c351f7$379fb010$a101280a@.phx.gbl...
> How do I find out what version of SQL Server 2000 I'm running?
> The 'about' dialog box on the SQL Server Service Manager
> says my version is 8.00.760 but the latest service pack 3
> is version 8.00.760.03. I thought I installed this service
> pack already.
> How can I know for sure?
> Thanks
> Brad

How do I find out what tables belong to a filegroup?

Is there anyway to find out what tables are in a particular filegroup in SQL2000 apart from running sp_help {tblnm}?You could use undocumented stored procedure...

declare @.id int
set @.i = object_id('mytb')
exec sp_objectfilegroup @.i

How do I find out what query someone ran?

Hello All,

We have an app that we do not have the source code for that is behaving badly. I'd like to find out what queries it is running in order to possibly fix the issue form the SQL server side of things. Anyone know what table/view I should select off of to find the queries that have been run recently?

Thanks in advance!

Kenny, your best bet is to fire up SQL Profiler and do some filtering so you only see the queries being executed by the application.

Thanks,
Sam Lester (MSFT)

How do i discover SQL server is running ...

Hello ,
I'm trying to create an application, which would say if sql server is
running on a machine.
Hope i can get this from windows registry. Is there any other way do
achieve this programatically through windows SDK , or checking the
services programatically ?
If i need to check the registry what are the fields that i should look
at ?
thanks in advance,
VeluChecking the registry would only tell you that SQL Server has been
installed -it wouldn't tell you IF it is running, or incompletely removed,
etc.
Asking SQL directly would require that you knew the SQL Server
name -normally the same as the host, but not necessarily.
Checking the services for 'Started' (or not) could work.
Arnie Rowland*
"To be successful, your heart must accompany your knowledge."
<thirumalaivelu@.gmail.com> wrote in message
news:1152253106.670394.42180@.k73g2000cwa.googlegroups.com...
> Hello ,
> I'm trying to create an application, which would say if sql server is
> running on a machine.
> Hope i can get this from windows registry. Is there any other way do
> achieve this programatically through windows SDK , or checking the
> services programatically ?
> If i need to check the registry what are the fields that i should look
> at ?
> thanks in advance,
> Velu
>|||Arnie,
So what is the best way to do it ?
thanks,
Velu|||Velu,
Not sure if this is what you are after, but you could just create a very
quick .NET application that trys to open a connection to an IP (or hostname)
address on the usual SQL Server port, and display a message box if it did, o
r
didn't connect?
Is all you are after, is, a way to find out if a server is running on a
system or not?
Jamie.
"thirumalaivelu@.gmail.com" wrote:

> Arnie,
> So what is the best way to do it ?
> thanks,
> Velu
>|||Jamie,
thanks a lot this is wat i wanted ...
velu