1) Unforgiveablity - this is an advantage and disadvantage of service broker. The advantage is that it forces developers to be precise with the formation and decomposition of messages as well as forcing disciplined use of transactions. The disadvantages lie in the same. With the slightest error in dialog management, the transaction log grows out of control. With any solution that is incorporating SQL Express (and thanks to the limitations for surfacing the problems), the transaction log, invariably - at some point - grows uncontrollably, especially if you have a web application monitoring queues. There really needs to be better checkpoints and obeyance of normal SQL Server contraints. It seems that sometimes service broker has a mind of its own and takes over not only the SQL instance, but the disk and everything else.
2) Administration/Management capabilities - as far as I am concerned, these are non-existent. I find it hard to believe that I can't even script my service broker objects using the native management studio scripting tool. Its ridiculous that - in an enterprise system - I have to manually script out scores of service broker objects. The operations group agrees with me and this does nothing to enhance the reputation of SQL Server as an enterprise player. You can't produce a SP that provides these capabilities? I have had to build in diagnostics and recovery stats by way of user tables that collect and analyze service broker message activity.
3) Internal Activity - what the hell is going on in these internal tables? I can't find any documentation about the use of tempdb or anything else. Please provide something explaining where all the activity occurs and where space is consumed. This is kind of important when operating on restricted platforms, like SQL Express.
4) Locking - seems like you need a more granular locking strategy. This is just conjecture, but it seems when service broker wants; service broker takes. How about letting the application architect/developer decide when service broker takes?
5) Dialog Pool Management - this is very complex to implement and almost all "real" applications need it. You need to provide an out-of-the-box, starter implementation because this leads to problems mentioned in #1. Remember how the VS application wizard killed the Borland dev environment? Well, SQL Server needs an equivalent. Stop making us write the same thing over and over again; and stop making me write the same queries over and over again.
6) Evangelists - Most people don't even know what service broker is, and it makes it a very hard sell. Even experienced SQL developers are not sure how and why it should be used. I have interviewed over 20 pretty strong SQL candidates over the last year and I can't name one that new ANYTHING about service broker.
7) Guidelines for Conversation Cleanup - well, it's a fact of life - we have to cleanup bad conversation handles from time to time. OK, maybe this wouldn't happen if we didn't have careless or error-prone programming, but we do. What is the proper way to cleanup dialogs between two endpoints? How about some sample queries to run on the initiator and the target? Or even an english language description of how to go about cleaning up and starting over with conversations that have gotten into a "bad" state?
8) UDP/Broadcast/Multicast Messages - This is a pretty big problem in my opinion. I can't believe this has not been added to 2008 (or at least, I did not see it in the feature list). Roger Wolter said it would be in the next version when the 2005 articles were written. So, I am supposed to keep cursoring through message endpoints and tricking up the TRANSPORT route to achieve what a broadcast implementation could achieve?
I guess thats enough for now. I look forward to the responses.
I'm sure Microsoft has heard most of these complaints many, many times, but I just have to rant about the issues I've encountered with SSIS over the last few weeks. Basically, SSIS, IMO, is a pile of dog ***. Yes, it gets the job done, but only in an extremely inefficient fashion:
Objects are ID'd by a GUID. This means that, (for instance) if you change a connection by deleting an existing connection and replace it by another, all of the objects using the old connection break -- EVEN IF YOU USE EXACTLY THE SAME CONNECTION NAME -- because they only know the connection by the GUID. Deleting the old Connection, even if you rebuild the new one with exactly the same name, pointing at exactly the same server, etc., breaks everything because all objects are ID'd by a GUID, not by a name (what genius at Microsoft thought this was a good idea?) All you get is an error message like "Connection manager {C72CC467-C142-4696-AF31-58B00DAC6ADE} does not exist" -- why isn't BIDS smart enough to resolve references by both the GUID and the Name? Why isn't there a "Find and replace" when updating or replacing objects?
SSIS is unbelieveably slow! Opening up a large project means you may as well step out for a cup of coffee because it takes so long to load. I believe this is because the project is based on the XML in the DTSX file, which means SSIS has to read the file in and interpret all the tags. Even on a fast machine, it's much, much slower to load an SSIS project than a Visual Studio project. Why the SQL Server team chose to use a different paradigm (one big, fat, slow file) than .NET and C# projects (a separate file for each object, and only opening the file when the developer opens the object) is beyond me. Visual Studio has a paradigm that works, yet the SSIS team chose to go their own way.
(SQL Sever Management Studio is also unbelievably slow, but that's an issue for another forum.)
Over and over again I've encountered issues seemingly related to the XML storage paradigm. The most common is editing a Task Script, then having BIDS complain about missing binary content when I return to UI. The suggested solution is to "visit" the code module so BIDS can rebuild the binary content. This is obviously a bug in BIDS, and should never have gotten past beta testing. If BIDS can't detect a change in the code behind a Task Script and regenerate the binary without the user having to "visit" the code, there's a serious problem with the internals.
Why is the user interface so cumbersome? Why can't I just right-click on a Script Task and drop directly into the code behind the object, rather than having to double-click, wait for the UI to appear, move to the "Script" tab, and select the "Design Script" button? Was this UI ever reviewed by beta testers? Does Microsoft really believe the BIDS interface is efficient? Too many properties are only seen when double-clicking on an object, while others appear in the property window. ALL properties should be accessible in the property window so that we don't have to double-click on an object and work through its inefficient editor.
Then, there's a myriad of stupid issues, such as -- when you change the connection for a Data Flow Component, all the exising metadata is invalidated, and you have to manually mark it all for deletion, close the editor, and re-open the editor. This is nonsense -- it's like some 12-year old decided that developers have all the time in the world to perform tasks that BIDS should be doing for us. Why can't just mark and delete mappings without having to close and reopen it
I am really sick and tired of the slow and inefficient user interface, and having to deal with the half-baked paradigms implemented in BIDS's features. It really has a "beta product" feel to it, as if it was rushed to market when it really needed another six months of development. I've got a job to get done here, and I'm spending the majority of my time working around the limitations built into BIDS. I could have written this package using bare VB.Net code in Visual Studio in half the time I've already spent on it, and I'm nowhere near done. IOW, BIDS has been a major drain on productivity.
The query should return only cnsmr_id=2 since all the complaints is closed (blank/NULL) i have achieved this using having but is there is more performance way of doing this?
Hi am creating a new database not sure which way is the best way to go. Here are the questions
I have a history table which has its an identity field and an identification field which shows what type of history field it is. As in payment, printout, change and so on.... For this field should i use Full text which has the payment, printout, change and so on and make it easy on the eye and for queries and coded to use the same text all the time have varchar(2) type of field which holds an understandable field PR, PO, CH and a reference table for it as in PR= PAYMENT, PO = Printout, CH as in change and so on or have a integer field as in 1, 2, 3 and ref. table 1=payment, 2 = printout, 3=change and so on I have a address table which holds multiple types of addresses which are really limited count maybe upto 4 different types have a integer field which links to a reference table as above have bit fields which can be used to check if it gets in one of the group types have a full text option like above Im open for any type of suggestions and i would love to hear the reasons why its picked TY
1. Is it legal and OK to use a MSDN SQL copy on a production environment or is it strickly for test environments ??
2. If I own a legal copy of SQL 7 with 5 cals, can I legally use SQL MSDE and have more than 5 people access my SQL server or am I also limited to 5 users as my original ??
I'm using DTS to import data from an Access memo field into a SQL Server ntext field. DTS is only importing the first 255 characters of the memo field and truncating the rest.I'd appreciate any insights into what may be causing this problem, and what I can do about it.Thanks in advance for any help!
I need to pass in null/blank value in the date field or declare the field as string and convert date back to string.
I tried the 2nd option but I am having trouble converting the two digits of the recordset (rs_get_msp_info(2), 1, 2))) into a four digit yr. But it will only the yr in two digits. The mfg_start_date is delcared as a string variable
option 1 I will have to declare the mfg_start_date as date but I need to send in a blank value for this variable in the stored procedure. It won't accept a null or blank value.
I am trying to drag data from Informix to Sql Server. When I kick off the package using an OLE DB Source and a SQL Server Destination, I get DT_DBDATE to DT_DBTIMESTAMP errors on two fields from Informix which are date data ....no timestamp part
I tried a couple of things:
Created a view of the Informix table where I cast the date fields as datetime year to fraction(5), which failed.
Altered the view to convert the date fields to char(10) with the hopes that SQL Server would implicitly cast them as datetime but it failed.
Hi ,Have a Visual C++ app that use odbc to access sql server database.Doing a select to get value of binary field and bind a char to thatfield as follows , field in database in binary(16)char lpResourceID[32+1];rc = SQLBindCol(hstmt, 1, SQL_C_CHAR,&lpResourceID,RESOURCE_ID_LEN_PLUS_NULL , &nLen1);and this works fine , however trying to move codebase to UNICODE antested the followingWCHAR lpResourceID[32+1];rc = SQLBindCol(hstmt, 1, SQL_W_CHAR,&lpResourceID,RESOURCE_ID_LEN_PLUS_NULL , &nLen1);but only returns 1/2 the data .Any ideas , thoughts this would work fine , nit sure why loosing dataAll ideas welcome.JOhn
Hi,another problem I have is that have compounded fields in my sql table.Exampleproduct@customerI need a simple function to return "customer", so it should return the valueafter "@", unfortunate "@" will sometimes be character number 6, sometimescharacter number 7 etc.regardsJorgen
I have a very strange situation. I've increased the size of an NVARCHAR field from 8 to 9 in a database table. The format of the data that I enter will either be an 8 character field (123456-8) or a 9 character field (1234567-9). The '-' is critical.
It used to only accept the 8 character version, but after increasing the field size, if I try to insert the 9 character field version, it gets truncated after the '-', as though it's still only allowing 8 characters. But that only occurs when I include the '-' or other such characters like '#'. If I try to insert 1234567a9, it works. The following explains the outcomes:
I need create a field to store tax rate. I need only 2 decimal points. I defined the field as decimal, precision=5 and scale=2. Does it mean that it can hold value from 0.00 to 999.99?
I'm importing an Access database to SQL Server 2000. The issue I ran into is pretty frustrating... All Memo fields that get copied over (as Text fields) appear to be fine and visible in SQL Server Enterprise Manager... except when I display them on the web via ASP - everything is blank (no content at all).
I didn't have that problem with Access, so I ruled out the possibility that there's something wrong with the original data.
Is this some sort of an encoding problem that arose during database import? I would appreciate any pointers.
We have a stock code table with a description field and a brand field - when the data was entered, some of the records were entered with the brand field in the description field.
ie. Code Description Brand ABC1 BLANK DVD SONY ABC2 SONY BLANK DVD SONY
what I need to do is identify where the Brand is in the Description field ...
I have a table called BidItem which has another table calledBidAddendum related to it by foreign key. I have another table calledBidFolder which is related to both BidItem and BidAddendum, based on acolumn called RefId and one called Type, i.e. type 1 is a relationshipto BidItem and type 2 is a relationship to BidAddendum.Is there any way to specify a foreign key that will allow for thedifferent types indicating which table the relationship should existon? Or do I have to have two separate tables with identical columns(and remove the type column) ?? I would prefer not to have multipleidentical tables.
I am trying to populate a field in a SQL table based on the valuesreturned from using substring on a text field.Example:Field Name = RecNumField Value = 024071023The 7th and 8th character of this number is the year. I am able toget those digits by saying substring(recnum,7,2) and I get '02'. Nowwhat I need to do is determine if this is >= 50 then concatenate a'19' to the front of it or if it is less that '50' concatenate a '20'.This particular example should return '2002'. Then I want to take theresult of this and populate a field called TaxYear.Any help would be greatly apprecaietd.Mark
I know there has to be a way to do this, but I've gone brain dead. Thescenario..a varchar field in a table contains a date range (i.e. June 1,2004 - June 15, 2004 or September 1, 2004 - September 30, 2004 or...). Theusers have decided thats a bad way to do this (!) so they want to split thatfield into two new fields. Everything before the space/dash ( -) goes intoa 'FromDate' field, everything after the dash/space goes into the 'ToDate'field. I've played around with STRING commands, but haven't stumbled on ityet. Any help at all would be appreciated! DTS?
i've a reasonable amount of experience with MS Access and less experience with SQL Server. I've just written an .NET application that uses an SQL Server database. I need to collate lots of data from around the company in the simplest way, that can then be loaded into the SQL Server database.
I decided to collect the info in Excel because that's what most people know best and is the quickest to use. The idea being i could just copy and paste the records directly into the SQL Server database table (in the same format) using the SQL Server Management Studio, for example.
Trouble is, i have a problem with line feed characters. If an Excel cell contains a chunk of text with line breaks (Chr(10) or Chr(13)) then the copy'n'paste doesn't work - only the text up to the first line break is pasted into the SQL Server database cell. The rest is not pasted for some reason.
I've tried with MS Access too, copying and pasting the contents of a memo field into SQL Server database, but with exactly the same problem. I've tried with 'text' or 'varchar' SQL Server database field formats.
Since i've no experience of using different types of databases interacting together, can someone suggest the simplest way of transferring the data without getting this problem with the line feeds? I don't want to spend hours writing scripts/programs when it's just this linefeed problem that is preventing the whole lot just being cut'n'pasted in 5 seconds!
I have a table with a column that is currently a varchar(50), but I want to convert it into an int. When I try to just change the type in design mode I get an error that conversion cannot proceed. When I look at the field it appears some of the entries have special characters appended at the end, I see a box after the value.
How can I remove all speical characters and then convert that field to an int?
Also I tried the following query which did not work as well, same error about conversion.
Can anyone point me any solution how to export a MEMO field from an Access database to a TEXT field from an MS SQL Server 2000. The import export tool from SQL server doesn't import these fields if they are very large - around 9000 characters.
HAX603 JULY 1 OCTOBER 31 HAX317 DECEMBER 1 DECEMBER 31 HAX317 MARCH 1 MARCH 31 HAX317 July 1 July 28
[Code] ...
Final Output
NUM STATUS ACTIVITYCODE <SEASONS>
HAX603 Completed 0x45845a 1 JULY - 31 OCTOBER, 1 DECEMBER - 31 DECEMBER HAX317 Completed 0x112z44 1 DECEMBER - 31 DECEMBER, 1 MARCH - 31 MARCH, 1 July - 30 July HAX465 Completed 0x1155x4 1 MARCH - 31 MARCH, 1 July - 28 July, 1 August - 30 August HAX523 Completed 0x124c69 1 November - 30 November
I have written a query to join the values of multiple field, but lacking in as how will I formulate a view which will check for the duplicate values of Num fields and merge there values in a single field like season.
select num, (CAST(startday AS VARCHAR(3)) + ' ' + startmonth + ' - ' + CAST(endday AS VARCHAR(3)) + ' ' + endmonth)AS Season from seasons;
Hi, I have a concern about adding a new field to a table with image field - which is huge. Will there be a problem with some databases, where they have a hard time locating data correctly after such a large field? Previously this happened to me, and what was advised to put all the big fields at the end of the table. Thanks.
Hi,I am running SQLServer2000 with ASP.NET. I have 3 fields:DATE_TIME (date time 8)DATEF ()TIMEF ()...when my default.aspx page is called, i need to execute a stored procedure that will parse my DATE_TIME field by the space in between, then update my DATEF and TIMEF fields with the appropriatevalues. NOw i have 2 fields to query from instead of 1. How would i write a SQL expression to do this? and what datatypes should the DATEF and TIMEF fields be?thxj
Hi all, I have a problem about a query to update a table
UPDATE Email SET EmailDT='31 Mar 2004' WHERE Idx={BDF51DBD-9E4F-4990-A751-5B25D071E288}
where Idx field is a uniqueidentifier type and EmailDT is datetime type. I found that when this query calling by a VB app. then it have error "[Microsoft][ODBC SQL Server Driver]Syntax error or access violation" and i have tried again in Query Analyzer, same error also occur, the MS SQL server is version 7. Please help. thanks.
