Hi;
I have been writing a lot of quick and diryt tsql scripts to correct
database issues.
I've been saving the output of the scripts for future records.
I have been executing them through query analyzer.
I find the messages query analyzer puts out to be obscuring to my
output: ie
" 6 rows processed "
Is there anyway to shut this off?
Steve
Hello,
when is seemd that everything works some weird behaviours comes out.
I try to summarize the problem without to post the complete code.
Service Broker is set to have a dialog between two databases on the same SQL Server instance.
The Initiator queue has retention=on and there is an activation SP to handle errors and Target's end dialog message.
The Target queue has retention=off, MAX_READER =1 and there is an activation SP to receive the message (WAIT FOR (RECEIVE (1) ...), TIMEOUT 30000 and do something with this message (sample insert into a DB).
The conversation has a Timeout Dialog to end the dialog after a while.
The problem that the message is constantly processed. The Process doens't stop is I end the dialof after the processing either.
n.b.the Receive is within a Transation that I commit at the end.
some other informations that in the meanwhile I found out :
This was my complete WAIT FOR(RECEIVE :
WAITFOR ( RECEIVE top(1) -- just handle one message at a time
@message_type=message_type_id, --the type of message received
@messagetypename=message_type_name,
@message_body=message_body, -- the message contents
@dialog = conversation_handle -- the identifier of the dialog this message was received on
FROM [TargetQueue]
), timeout 1000;
if (@@ROWCOUNT = 0)
BEGIN
COMMIT;
BREAK;
END
IF I delet TIMEOUT 1000, everything works as expected ...
Inside if (@@ROWCOUNT = 0)BEGIN..END I wrote also an Insert into a table to see wheter the end of the queue was reached but this insert never occurs (neither with not without timeout)
I'm happy that it works what if this is the solution, it make no sense to me!
Any ideas?
Thank you!
M.B.
Thank you very much
M.B.
I am losing data from time to time and can not figure out where the rows are vanishing to. In essence, I have 5 files that I process. The processing occurs on a daily basis by one SQLAgent job that calls 5 individual packages, each doing a different piece of work.
I stage each file to a temp table, then perform some minor transformations, then union the rowsets from each file before landing to another temporary table. A subsequent package performs additional transformations that apply to the entire dataset, before inserting them to their final destination. Along the way, I reject some records based on the business rules applied. Each package in the entire Job is logging(OnError, TaskFailed, Pre and Post execute). Their are no errors being generated. No rows are being rejected to my reject tables either.
Without getting into the specific transforms, etc. being used in this complex process, has anyone seen similar unexplained behaviour? I've not been able to identify any pattern, except that it is usually only 1 or 2 of the record types (specific to a source file), that will ever not be loaded. No patterns around volumes for specific source files. There are some lookups and dedupes, however I've seen the records 'drop off' before reaching these transforms. I have noticed I had my final desination load not using fast load. However sometimes the records disappear before even getting to my final staging table which is inserting using fast load. I am going to turn on logging the pipelinerowssent event. Any other suggestions for troubleshooting/tracking down these disappearing records?
Thanks
I've created a cube and it processed fine. The calculate command is there. The measure that I'm attempting to run in the SSAS/Visual Studios browser is simply a count rows measure. When I drag the measure to the window, it says no rows available. If I click on the filter that allows nulls, the only change it makes is that it goes from no rows available to "NULL".
View 4 Replies View RelatedHello all,
I have a query that keeps getting errors from our SQL Server. Can anyone see why ?
select job.key,job.job,job.item,job.job-date
From job
where job.job-date > (dateadd (year,-1,job-date))
order by job.job, job.item, job.key
I have tried bracketing the job-date but I still get errors saying it is
unable to understand the query. Thank you for your help in advance.
I am using http to access my cube (MS Analysis Server) and there are userswho are getting "Query could not be processed. Access is forbidden"intermittently. Can someone help out and let me know what is causing thisissue? Some users are not getting this error.
View 3 Replies View RelatedI have a stored procedure and one of the fields in the SELECT is a datetime type. On SELECT I am getting the date and the time but no AM/PM returned. The datetime in the query is returned in this format:
2008-03-21 08:37:20.000
Any help is appreciated.
I have created a cube with Analysis Service 2005. I then publish the pivot table (generated in Microsoft Excel 2003) as a web page. When I view the web page on the domain the LAN everything is working properly. But when the web page I view on Internet I get the error.
The query could not be processed:
* An error was encounted in the transport layer
* The peer prematurely closed the connection.
The web page is publish on the Internet Information Server, I changed the security directory of the website, but the error persists.
The firewall of the machine is disabled.
The port 2382 and 2883 are allowed.
The components needed to consult the cube are installed in the machine on which you are viewing the cube.
On the Rol of Analysis Service are allowed all users.
What other type of restriction may be?
Thanks...!!!
When I run the following query from Query Analyzer in SQL Serer 2005, I get a message back that says.
Command(s) completed successfully.
What I really need it to do is to display the results of the query. Does anyone know how to do this?
declare @SniierId as uniqueidentifierset @SniierId = '85555560-AD5D-430C-9B97-FB0AC3C7DA1F'declare @SniierAlias as nvarchar(50)declare @AlwaysShowEditButton as bitdeclare @SniierName as nvarchar (128)/* Check access for Sniier */SELECT TOP 1 @SniierName = Sniiers.SniierName, @SniierAlias = Sniiers.SniierAlias, @AlwaysShowEditButton = Sniiers.AlwaysShowEditButtonFROM SniiersWHERE Sniiers.SniierId=@SniierId
THis is so annoying. I have 3 ADO executes in my program. THe first one creates a view, the second one performs an outer join on that view and returns a result set, the third execute drops the aforementioned view. THe program that is using this is installed on about 200 computers scattered across Germany and Italy, all querying the same MSsql server 7.0. THe queries run quite quick when few users are actively using the program (after hours for example). however in the heat of the day performance goes up and down dramatically with identical queries taking from 1 to 20 seconds to return their result set. Now I initially thought 'bandwidth issue out of our server'. However I noticed that if I take those three queries and run them from the sql server enterprise manager( running on the same computer as the aforementioned program) then the queries run instantly and the data is in my result pane in less than 2 seconds ALWAYS....even when the program is dogging it with 20 second delays before the result set returns. I know it is hanging on the return of the result set as I put a stop after before each ADO execute in order to check which one was eating up my time. Why is there this dichotomy between running the queries from my enterprise manager versus running them from an ADO object. Both are using TCP/IP (no named pipes involved). I havent monkied with the attributes of the ADO result set so they are all set to default.
I have used the sql server profiler to trace these queries and they always run in less than 33 milliseconds. THe duration is also never more than 33 milliseconds. THis stinks of a network resource issue but what always leads me somewhere else is how consistent the performance of the enterprise manager is when it runs the exact same three queries.
Here is my slightly edited connection string
Public Const connection_string = "Provider=SQLOLEDB;Server=000.000.000.000;" & _
"User ID=johndoe;Password=janedoe;Network=dbmssocn;" & _
"database=fidojoe"
Here are the 3 ADO executes:
conn.Execute (sqlstr_create_view)
Set resultset1 = conn.Execute(sqlstr_get_providers_by_DMISID)
conn.Execute (sqlstr_drop_view)
When I run query in excel it gives result with different column sequence. The same query gives result with different column sequence when used in query analyzer or VBA Macro. E.g., Select * from ABC.
result in Excel 2003 SQL OLE DB query
col-A col-B col-C
values...
Result with Query Analyzer and VBA Macro
col-c col-B col-A
values...
I hope I am not asking about something that has been done before, but Ihave searched and cannot find an answer. What I am trying to do is torun a query, and then perform some logic on the rowcount and thenpossibly display the result of the query. I know it can be done withADO, but I need to do it in Query Analyzer. The query looks like this:select Varfrom DBwhere SomeCriteriaif @@Rowcount = 0select 'n/a'else if @@Rowcount = 1select -- this is the part where I need to redisplay the resultfrom the above queryelse if @@Rowcount > 1-- do something elseThe reason that I want to do it without re-running the query is that Iwant to minimize impact on the DB, and the reason that I can't useanother program is that I do not have a develpment environment where Ineed to run the queries. I would select the data into a temp table, butagain, I am concerned about impacting the DB. Any suggestions would begreatly appreciated. I am really hoping there is something as simple as@@resultset, or something to that effect.
View 6 Replies View RelatedI am trying to determine which of three stored procedure designs arefastest in the Query Analyzer:One query is a straight SELECT query with all desired rows and a dozen(tblName.RowName = @param or @param = Null) filters in the WHEREstatement.One query populates a #Temp table with the UniqueIDs from the resultsof the SELECT query in the above example, then joins that #Temp tableto get the desired rows.One query users EXEC sp_executesql @sql, @paramlist, @paramin which the @param has the dozen filters.What I'm trying to determine is which is the fastest.Each time I run the query in Query Analyzer it returns the samerecordset (duh!) but with much different Time Statistics.Are the Time Statisticts THE HOLY QRAIL as far as determining which isfastest, and what so I want to look at, the Vale or the Average? Inotice there are different numbers of bytse sen and bytes received foreach of the three queries.Any illumination on this is appreciated.lq
View 4 Replies View RelatedHi there
Running query analyzer against two different server.
the first only need 1-2 secs to return the query result,
the other return 7-8 secs for the query result.
plz advice what could cause this slow performance?
thx
Why does this code tell me that I inserted 2 rows when I really only inserted one? I am using SQL server 2005 Express. I can open up the table and there is only one record in it. Dim InsertSQL As String = "INSERT INTO dbCG_Disposition ( BouleID, UserName, CG_PFLocation ) VALUES ( @BouleID, @UserName, @CG_PFLocation )"Dim Status As Label = lblStatus
Dim ConnectionString As String = WebConfigurationManager.ConnectionStrings("HTALNBulk").ConnectionString
Dim con As New SqlConnection(ConnectionString)
Dim cmd As New SqlCommand(InsertSQL, con)
cmd.Parameters.AddWithValue("BouleID", BouleID)
cmd.Parameters.AddWithValue("UserName", UserID)
cmd.Parameters.AddWithValue("CG_PFLocation", CG_PFLocation)
Dim added As Integer = 0
Try
con.Open()
added = cmd.ExecuteNonQuery()
Status.Text &= added.ToString() & " records inserted into CG Process Flow Inventory, Located in Boule_Storage."
Catch ex As Exception
Status.Text &= "Error adding to inventory. "
Status.Text &= ex.Message.ToString()
Finally
con.Close()
End Try Anyone have any ideas? Thanks
I am new to SQL Server 7 and get the following error message when I go into the Enterprise Manager, select a specific table, right mouse click, open table, return all rows:
"An unexpected error happened during this operation.
[MS Design Tools] - Query designer encountered a MS Design Tools error: Not Implemented"
Can anyone explain what is causing this error?? I can go into SQL Query Analyzer and do a SELECT against any table and bring back data, but not as stated above.
Is Query Analyzer being droped in MS-SQL 2005? I'm using Microsoft SQL
Server Managemant Studio and I couldn't find the Store Procedure node
and Query Analyzer anymore. Help is appreciated.
Say you have a state column with the name of a state in it. And you need to add a branchId to the table. I know I should not have it in there, and the application should use proper lookup tables, but that's not the case.
Can SQL Server query analyzer loop through and update the branchid field based on a lookup in a lookup table?
How would that be done?
Hi All,
I think I post this in a wrong section but I cant find forum for sql server and i just have a quick question
basically, I installed sql server 2005 and I need to debug a store procedure.1. Does query analyzer come with sql server 2005?2. If not how can I debug store procedure in sql server 2005?Thanks in advance
Does anyone know if there is a limitation in Query Analyzer in terms of the number of characters in a query that it will parse?
I am having a problem with some large queries, and I am not sure if it is Query Analyzer, or the SQL Server query parsing engine that is causing the problem.
The query just involves a simple SELECT statement, albeit for a large number of columns.
Hi,
I'm using SQL Server 7.0. I connect to one database in Query Analyzer and I can use the "Perform Index Analysis" option under the "Query" menu option. when I connect to one of our other databases, the "Perform Index Analysis" option under the "Query" menu option is disabled (greyed out). Does anyone know what the problem could be and how might I be able to enable this option.
Thanks in advance,
Darrin
Hello
In query Analyzer of SQL2000 when i select
2 Select Queries and Run it shows in same Window as 2 Grids
I have seen people using seperate grid option to view each query results in a Tab query results Pane in MSSQL 7.
Eg Select * from table1 (in one grid)
select * from table2 (in another grid)
like a TAB so that i can move from one tab to another.
Is it possible to do that in query Analyzer?
Regards
I would like to use the Query Analyzer to create a script that will prompt the user(dialog box) for a response in SQL Server 7.0. I found a post someone mentioned DBMS can return messages and using Query Analyzer.
Thanks for your help in advance.
I'm trying to do a couple of things with some different tables.
With the first table, I have first name and last name in two separate columns.
I'm trying to create a new ID column that would do the following.
Take the first 3 letters of the last name (and remove any non characters such as ',&) and the first 3 letters of the first name, and then combine them.
I know how to use substring to take the first three, and I know how to combine them, but I'm having problems removing the non alpha characters.
For example, if the columns looked as follows:
First Last
John O'Connor
I'd want the result to look like OCOJOH, however, I'm getting O'CJOH.
The second issue is how would I do something similar if the First and Last Name were in the same column and string.
For example, if the column looks as follows:
Name
John O'Connor Publishing
I'd like to pull the first 3 from the first string before the space and then the next 3 so it would be as follows:
JOHOCO
Finally, I want to compare these new IDs, and add numbers to them.
So if I had the following
First Last
John O'Connor
Johnny O'Connell
The first would be OCOJOH01
The second would be OCOJOH02
If there are not any duplicates with the new ID, I'd like to call it OCOJOH01 and then move on.
Any help/ideas would be greatly appreciated.
Thanks
Hi;
I would like to change the format of dates that is in colum using query analyzer. The name of the colum is StartD and this how they look: 3292006 I would like to insert the / / . Can someone show me how to accomplish it.
Thank you
I have a database with couple of store procedure and views.
Is there any way that i can find out all sp or views which contains word like *svc*. I am talking about the containt of the sp and views not name.
We are replacing one module so i have to change the all sp and views which use table or view start with svc.
Need help.
Thanks
in the market place that will run a query on multiple DBs acrossmultiple servers? Without having to manually connect to each server?If so, can anyone provide a link for it.Thanks
View 3 Replies View RelatedI am new to SQL and need help running a performance check on our companies dtabase. I use SQL Enterprise Manager 2000. Could someone please help me accomplish this?
Thanks,
Carl
hi everybody!
this is my first post, so I'd like to say hallo to everyone!
I "work" on SQL Server 2000, and I would like to ask you a question.
Is it correct that i cannot set a database user as database owner (using command sp_changedbowner 'user_name')?
QA returns this error message:
"Server: Msg 15110, Level 16, State 1, Procedure sp_changedbowner, Line 46
The proposed new database owner is already a user in the database."
so I have to use sp_revokedbaccess and then I can change the db owner.
why can't a db user be the db owner?
thanks for the answers!!
Hi,
Was wondering if anyone can tell me how to get the Query Analyzer Tool installed to WM6 Professional Emulator inside of VS05?
Thanks,
John.
Hello,
how is the query analyzer opening a database??? Does someone knows the command it uses to open the connection ??
I ask this because i have corrupted datafiles stored on a flash card which i can not open with connection.open but the analyzer opens them normally. After that i can open the datafiles using normal code
Does it use any special params?? I get the corrupted files on power down and after that i can not open them from my application. I can only do this after i open them once with the query analyzer
The database is about 20 MB stored on a flash card
Thanks in advance
Hello,
I have a seach on UserID enetred in a textbox by user.
When the user types a userID which has no data in the database can I display an error msg instead of the chart and table that is displayed on entering a valid userID.
Thanks,
Kiran.
Say I have this query in my stored procedure. The problem I am facing is @OrgUID contains no value, so I put these 2 lines to debug. Am I using the right way to debug? I couldn't see the value of @OrgUID using this way. OrgUID is the PK in Organization. INSERT INTO Organization (OrgName) VALUES (@DealerName)SELECT @OrgUID = @@IDENTITYprint @OrgUIDreturn
View 4 Replies View Related