How do you get sql2005 profiler to filter on host name, (computer name of the client) like you can in sql2000 profiler. 2005 Profiler column filter options seem to omit this, this makes the application virtually useless in most situations.
View 1 RepliesHow do you get sql2005 profiler to filter on host name, (computer name of the client) like you can in sql2000 profiler. 2005 Profiler column filter options seem to omit this, this makes the application virtually useless in most situations.
View 1 Replies View Related
We have Merge Replication publications for SQL Server 2005 Compact Edition subscribers.
Some articles have filter statements that send rows to multiple subscribers, based on the value of Host_Name() supplied at run-time.
Our publications work for most subscribers, but we have at least one subscriber who downloads too many rows from one of the filtered tables.
When we run the Select SQL from the article's Filter statement it returns the intended 4 rows for this subscriber.
We cut and pasted the filter statement into query analyzer, substituted the subscriber's value for Host_Name(), executed the statement, and got the proper 4 rows for this subscriber in the results.
But when this subscriber syncs her Compact Edition database it downloads 10 rows - the proper 4 rows that the filter statement should pass, plus 6 other rows that she should not download.
Our hypothesis is that the Filter statement is not properly applied to the article when this subscriber syncs.
Other subscribers get the proper rows when they sync, so the publication's filter statement works in some cases, for some values of Host_Name().
We'd like to see the application of the filter statement at run-time (sync-time), but we have not found the text of the filter statement in SQL Profiler output. Should we expect to see the text of the filter statement in SQL Profiler output?
Is there a better way to debug this error?
FYI, here's the text of the article filter statement:
SELECT <published_columns> FROM [dbo].[TBL_USER] WHERE user_sys_id in (
select u.user_sys_id
from tbl_user u
join tbl_territory t on u.territory_gid = t.territory_gid
where t.terr_no_id like (
select
case (select t.data_access_qnty from tbl_user u join tbl_territory t on u.territory_gid = t.territory_gid where u.user_sys_id = Host_Name())
when 2 then (select t.terr_no_id from tbl_user u join tbl_territory t on u.territory_gid = t.territory_gid where u.user_sys_id = Host_Name())
when 3 then (select left(t.terr_no_id,5)+'%' from tbl_user u join tbl_territory t on u.territory_gid = t.territory_gid where u.user_sys_id = Host_Name())
end
)
)
And here's the statement we ran from Query Analyzer:
declare @id varchar(10)
select @id = 'aultnc'
SELECT * FROM [dbo].[TBL_USER] WHERE user_sys_id in (
select u.user_sys_id
from tbl_user u
join tbl_territory t on u.territory_gid = t.territory_gid
where t.terr_no_id like (
select
case (select t.data_access_qnty from tbl_user u join tbl_territory t on u.territory_gid = t.territory_gid where u.user_sys_id = @id)
when 2 then (select t.terr_no_id from tbl_user u join tbl_territory t on u.territory_gid = t.territory_gid where u.user_sys_id = @id)
when 3 then (select left(t.terr_no_id,5)+'%' from tbl_user u join tbl_territory t on u.territory_gid = t.territory_gid where u.user_sys_id = @id)
end
)
)
Thanks
I have been running traces in SQL Profiler and have not been able to get the filtering to work correctly. I am interested in capturing only statements that use CPU and have setup the filter for CPU > 0. It still returns all of the rows that have Null. Is there any way of filtering out the rows that have nunll for CPU?
Thanks,
Ken Nicholson
Hi every one,
I want to filter application name in sql profiler, so I write SQL% (for every one who connects by sql qery analyzer) in "application name" filter box but it doesn't work correctly,and shows all applications. Why it works incorrect?
Thank You
Kini
Hi, I've been a SQL Server dba for about 10 years, so i know SQL quite well. I've recently started using VWD express, and as it says, its quick and easy to create a website, which I've done. It all works great on my local machine. I've just signed up with an isp and duly ftp'd the project files plus the mdf and ldf that lives in App_Data into their content area, and rather naively I expected it all to work. The pages come up fine until some database access is called for, then i get the seemingly infamous error:
... (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified) ...
I've researched this for 2 solid days now, examined forums and posts, followed advice and enabled protocols and so on. Support at the isp in question does not really have a clue about databases and connections strings. Then I started looking closely at the words used by the isp's, none of the isp's explicitly say they support sql 2005 express. They do say SQL 2005, but not express. They do say VWD Express, but not SQL 2005 Express. So does anyone know what is/is not possible with regard to deployment of SQL 2005 express. Does anyone have experience of running SQL express on a production server, or a shared hosters machine? Its been out now for nearly a year I think, so hopefully someone has the answer.
Thanks if you can help! Charlie
I am trying to use the new Common Language Runtime (CLR) hosting feature to write stored procedures in C# i have addedMicrosfot.sqlserevr.server name spaceand ia m trying to use sqlContext Object as below
using (SqlConnection connection = new SqlConnection(dbConn)) { connection.Open(); SqlCommand sqlcmd = new SqlCommand("select @@ version", connection); SqlContext.Pipe.ExecuteAndSend(sqlcmd); }
i get the below error when i execute (SqlContext.Pipe.ExecuteAndSend(sqlcmd);)
System.InvalidOperationException was unhandled by user code Message="The requested operation requires a SqlClr context, which is only available when running in the Sql Server process." Source="System.Data"
i checked if (SqlContext.IsAvailable) and it returns false as well.
Please le me know how to make it work.
Thanks
THNQDigital
Hi,
How can one get the machine version details on which MS SQL 2005 server is installed (Host machine Version details)using SQL Query?
I want to fire one query from client which will give me the information about the plafrom version details of Server machine(including service pack details)
I need this information urgently........
Waiting for reply..................
Sambmc
Hi
I have a problem with sqlexpress
I'm using sqlexpress 2005 with asp.net 2 and all things work in my computer good
but when i upload it on the internet in my host it return this error :
An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)
I am using sql server locally and i'm not using it remotly
i just created a simple page (just have a detailsview) and upload it to the internet
and then upload my mdf and ldf files to the app_data folder
Hi guys,
I am just wondering what's the difference of sql server 2005 standard and enterprise editions. can I use SQL server 2005 to host a database for a website?
Thanks!
Daren
where in the sql 2005 profiler i set the DB that i want to trace?
i know where it is in sql 2000 but cant find it in sql 2005
thnaks
peleg
Israel -the best place to live in aftr heaven 9but no one wan't to go there so fast -:)
I have an ASP.NET application that runs under the development server. Before deploying to my host I wanted to test it under IIS. I set up a virtual directory under IIS, but when I try to open the home page I get an error Unable to open the physical file
"E:CDiskLocalWebSitesDIWSchedulingApp_DataDIWDB.MDF". Operating
system error 5: "5(Access is denied.)".and a code dump showing that the code is breaking at the open connection. A little research on the error indicates that IIS does not have permission to execute the database operation. I can not find any information on how to give IIS this permission in SQL Server 2005 Express. Any guidance would be appreciated.
Is there any way to use sql profiler with sql 2005 express or is there a similar tool that can be used with it? I only have enterprise manager with sql 2000.
View 1 Replies View RelatedDear friends,
I got script for creation trace file on server drive.
and it's work fine,when we migrate to sql server 2005 i take the same script for this trace and he create it successfully but the problem when i try to open this file for example today file:
SQL-Server1 SQL Trace20080417000000847.trc
I got the following message:
Sql server profiler :failed to open a file ,access denied.
old files created by sql server 2000 ,i can open it until now but the file created after migrate to sql 2005 ,i got that message above.
Guys/Girls
Have a problem am currently running a sql trace on a database, have the normal options setup.
Stored Procedures
RPC:Completed
RPC:Starting
TSQL
SQL:BatchCompleted
SQL:BatchStarting
But am also using the text data field but am trying to only bring back items that have a certain string like. Does anybody know how to use wild cards in the text data colounm
Lee
Hi everyone,I am steven.
I have some questions about the SQL Server Profiler 2005.
1,haoI am using TransactionScope of .Net Frameworks 2.0 for DB query and update.I want to know the lock level(table,page,row,etc) and status of lock status(u,s,x,etc) by profiler tool when I run the query and update statement.How can I do?
2,I had got some traces when I run the DB query and update.But I cann't understand the meaning of the trace.such as Binary Data,ObjectID,Type,etc.How can I know the meaning of these?
Whould anyone can help me?
I want profiler to run for 5 minutes whenever the system cpu exceeds/reaches 80%. Is this possible?
TIA
I notice this running the SQL Server Profiler under the application name Report Server. Every time it runs, it reports an error (in the Error field of Profiler). Does anyone know what this is?declare @p1 nvarchar(64)set @p1=NULLexec GetDBVersion @DBVersion=@p1 outputselect @p1
View 1 Replies View Related
Hello everybody,
I have a very stranger problem that I need to understand...
In last days I executed a plan SQL 2005 Profiler to review TSQL Duration. When reviewing the results encounter that a SP displays a value of 4037312 in field DURATION which are not normal.
Could to help me to identify why passes this?
Dear All,
I need a tool to see what transactions and in what order are being issued
against the SQL Server Express 2005 DB.
Because of the SQL Server tools don't come with SQL Server Express 2005,
then i run SQL Server 2000's Profiler with SQL Server Express 2005. But It
prompt error "Failed to start a new trace".
FYI, i don't want to use the backend sql trace .in command prompt..
Pls advise me on how to run the sql profiler to trace the transaction being executed in SQL Express 2005?
Thank you.
I SQL Server 2005 Profiler when ran the T- SQL template against some Stored Procedures, in the error column i get 2 - Abort. Can some one help me on this issue.
Thanks
Is it possible to install SQL server profiler in Express Edition. I know it can be installed in SQL server 2005. Once I tried installing in Express edition and it did not work.
What is the main diofferenve between SQL Server 2005 and Express edition?
Thanks
Someone Please Help!
How on earth can a Profile Trace be run where SSE 2005 is installed??? In the past, with MSDE, we always installed the 'tools' on a local workstation, so that we had EnterPrise Manager and its suite of tools...no problem. Yet, with Management Studio Express (err...Distress?), there's no way to do this! I've scoured the net, and I see threads where people have done it, yet, no one seems to be clear...including Microsoft...on how to obtain this MOST IMPORTANT of all tools for an SQL deployment.
Please Help! Someone...Anyone!...Thank You!...Michael
Hi, I have done a quick search for a similar topic, but I don;t think they are what I need. What I want to do is (when creating a view in the database) I want to filter entries in my table that have only been created this month (the field I am looking at is called DateDB), I'm not sure how to do this, I have tried =MONTH(GETDATE()), but this doesn't return any values at all. Is there a way of doing this?
View 2 Replies View RelatedHello,
Here is my problem:
I use SQL Server 2005. I have approx. 50 tables in my database and 30 of them have a filed named "CompanyID". Example:
create table A (ID int identity, NAME varchar(100), COMPANYID int)create table A (ID int identity, REF_ID int, FIELD1 varchar(100), FIELD2 varchar(100), COMPANYID int)
Also there are nearly 200 stored procedures that read data from these tables. Example:
create procedure ABCasbegin /* some checks and expressions here ... */ select ... from A inner join B on (A.ID = B.REF_ID) where ... /* ... */end;
All my queries in the Stored procedure does not filter the tables by CompanyID, so they process the entire data.
However, now we have a requirement to separate the data for each company. That means that we have to put a filter by CompanyID to each of those 20 tables in each query where the tables appear.
Firstly, I put the CompanyID in the context so now its value is accessible through the context_info() function. Thus I do not need now to pass it as a parameter to the stored procedures.
However, I don't know what is the easiest and fastest way to filter the tables. Example:
I modified the above mentioned procedure in the following way:
create procedure ABCasbegin /* some checks and expressions here ... */
-- gets the CompanyID from the context: DECLARE @CompanyID int; SELECT @CompanyID = CONVERT(float, CONVERT(varchar(128), context_info()))
select ... from A inner join B on (A.ID = B.REF_ID) where ...
and A.COMPANYID = @CompanyID and B.COMPANYID = @CompanyID /* ... */end;
Now I have the desired filter by CompanyID. However, modifying over 200 stored procedures is rather tedious work and I don't think that this is the best approach. Is there any functionality in SQL Server that can provide the possibility to put an automatic filter to the tables.
For example: when I wrote "SELECT * FROM A", the actual statements to be executed would be "SELECT * FROM A WHERE CompanyID = CONVERT(float, CONVERT(varchar(128), context_info()))".
I was looking for something like "INSTEAD OF SELECT" triggers but I didn't manage to find any answer.
I would very grateful is someone suggests a solution for something like "global table filter" (that will help me make an easy refactoring)?
Thanks in advance.
Best regards,
Beroetz
I don't know why my application on the internet try to access the SQL Server 2005, when the host still having the 2000 edition. I put the following connection string:
Data Source=##DBSERVER##;Initial Catalog=##DBNAME##;User ID=##DBUSER##;Password=##DBPASSWORD##;
Isn’t everything fine with that? I created the table by host but dosen’t work. I’m wondering if can I conect straight with Data Base, without register in the server, like with mdb?
This is the error that I’m getting.
When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)
I'm creating an app and have SQL Server 2005 Express installed. Most of the hosts now offering SQL Server 2005 are a bit pricey compared to those only offering 2000. Will I have any difficulty uploading my site and running the db on SQL 2000? Thanks in advance.
View 3 Replies View Relatedi have a table full of cars, from many makes and years (along with other info).
but I DONT want 2005 Ferrari's, or 2006 Ferrari's.
tried making a SQL statement, but it seems to filter out ALL ferrari's.
Here is the SQL i've tried:
SQL Code:
Original
- SQL Code
--SQL Stm #1
select COUNT(*)
from items
where account_id in (1667) and
items.ad_status IN (3,4) AND items.vehicle_type = 'passenger' AND
( (items.Make != 'Ferrari' AND items.Year != '2005')
OR (items.Make != 'Ferrari' AND items.Year != '2006') )
--SQL Stm #1
SELECT COUNT(*) from items
where account_id=1667 AND
items.ad_status IN (3,4) AND items.vehicle_type = 'passenger' AND
(items.make!='ferrari' and (items.year!=2005 OR items.year!=2006))
--SQL Stm #1SELECT COUNT(*) FROM items WHERE account_id IN (1667) AND items.ad_status IN (3,4) AND items.vehicle_type = 'passenger' AND ( (items.Make != 'Ferrari' AND items.Year != '2005') OR (items.Make != 'Ferrari' AND items.Year != '2006') ) --SQL Stm #1SELECT COUNT(*) FROM items WHERE account_id=1667 AND items.ad_status IN (3,4) AND items.vehicle_type = 'passenger' AND(items.make!='ferrari' AND (items.year!=2005 OR items.year!=2006))
both takes out all ferrari's ?
Hello Experts,
I have been playing around with RS 2005 and like it so far. I am in the process of replcating some excel reports in RS. I was wondering if there is any way to replicate the Auto Filter Functionality of Excels sheets where column is a dropdown with disticnt values of that specific column and also has Top 10 and Custom filters attached to it. So when selected from dropdown the report filters for these records.
I understand we can use parameters for this kinda report but my client has 15 columns in the report and it will be cool to give them auto filter functionality as in Excel as they are used to it.
Any help will be appreciated.
Hi,
the title of this topic about summarises it. I have a dev-machine with SQL Server 2005 Standard edition on it. My host runs SQL Server 2000. What are my options?
Is there some sort of backward compatibility? Can i export as 2000?
I have no idea and i can't find anything about this particular problem.
Thanks
Howdy,
I have a table that has a group. In this group, I want to filter by 2 different expressions, concatenated with an OR. BUT I can't change the "And/Or" column value for the first entry because it is grayed out. The column will automatically change to an OR value if both my expression column fields are the same (which I don€™t want) but if I put any other value in to the expression field of the second row, the "And/Or" field of the first row automatically changes to an AND.
PLEASE! How do I get the And/Or field "ungrayed" so I can change it to what I want?
The 2 filters I and using check the UserID = to the user, and the other is checking a count to get the Top N 1. (So just showing the current user and the top producer)
Hi
View 8 Replies View RelatedHello, I have read on the multiple places that filter for full text search of PDF files using FTS2005 is included in the Reader 8 etc. However, I have not found any document or instruction etc on adobe documents, microsoft documents or web that details on how to actually configure the filter. Please help. thanks
Kumud