Need Direction On SSB Replacing Existing Technology
Oct 24, 2006
Hello, I have an existing suite of applications in my company where remote laptop users enter data into a local database instance. There are several different applications with several different databases (some users run application A and C, others run A and B and D, etc). All of this data needs to make its way into both their local databases, and to the front office database. In addition, some of the data goes out of the front office to other end users (supervisors get data from people under them).
Currently, we get the data back and forth by creating text files full of SQL statements that are run on either the end user servers or the main server, and we zip and ftp these SQL Script files back and forth.
I am looking into the benefits of replacing this process with a Service Broker application, but I'm still too much of a newbie to understand how this might work. What should my messages contain? Should they contain the same SQL statements that are currently in the script files, and then a SSB process would grab the SQL and run it? Or should the message types be more specific to each application?
any pointers to articles that go beyond "here's what SSB is" would be appreciated.
I have a table that stores all the processed data from other tables. How can I replace the same data in this table when I do "reprocessing"? It's kinda like a combination of delete then insert kind of thing. I cannot simply insert as it will become duplicates. any idea?
In my script below. I'm trying to update a SQL Server 2000 DB with data from SQL Server 2005. I've written a Cursor to call tables from information schema. Compare the data between 2005 and 2000 then Insert into SQL 2000 Table. But I'm getting The multi-part identifier "c.ID" could not be bound.. Can you please help me to resolve this.
DECLARE TableCursor CURSOR FOR SELECT Table_Name FROM INFORMATION_SCHEMA.TABLES WHERE Table_Type = 'Base Table' and Table_Name like 'L_%'
OPEN TableCursor
FETCH NEXT FROM TableCursor INTO @TableName WHILE @@FETCH_STATUS = 0 BEGIN
DECLARE @Line VARCHAR(1000)
SET @Line = ''
SELECT @Line = @Line + 'c.'+ c.Column_Name + ' , ' FROM INFORMATION_SCHEMA.TABLES T JOIN INFORMATION_SCHEMA.COLUMNS C ON t.Table_Name = c.Table_Name WHERE t.Table_Name = @TableName and t.Table_Name like 'L_%'
I'm contemplating a sql server archive strategy that rolls really old data off any sort of dbms and onto low cost media like dvds in a non relational archive format. I dont want to ever worry about these archives spanning different versions of sql when i go to retrieve a range of data that happens to span sql versions (eg one disc was sourced from 2005 another by 2008 but my report needs a union of both).
So I'm thinking about neutral/efficient formats for these archives and a live homegrown catalog that can determine exactly what disc(s) need to be mounted based on passed from and to date parameters...all so that the data that might span discs (and versions and maybe even schemas) can be merged and loaded into my sql version d'jour's "throw away" archive database for a one time report or other unplanned activity.
I remember raw data types being very convenient as an ETL format for our customers who have ssis, but wouldn't want our sqlexpress customers to be left without the archiving capability. Do the "things" that read and write raw data files really originate in some special T-SQL command that all sql editions can use, or is it strictly an ssis thing?
I am trying to install Microsoft SQL Server September 2005 Community Technology Preview.
I get the generic error saying "SQL Server 2005 setup has detected incompatibe components...."
So I checked the C:Program FilesMicrosoft SQL Server90Setup BootstrapLOGSQLSetup0020_Core.log file
which says
C:SQLENT2005Serverssetup.exe Version: 2005.90.1314.0 Running: LoadResourcesAction at: 2005/9/11 13:3:29 Complete: LoadResourcesAction at: 2005/9/11 13:3:29, returned true Running: ParseBootstrapOptionsAction at: 2005/9/11 13:3:29 Loaded DLL:C:SQLENT2005Serversxmlrw.dll Version:2.0.3604.0 Complete: ParseBootstrapOptionsAction at: 2005/9/11 13:3:30, returned true Running: ValidateWinNTAction at: 2005/9/11 13:3:30 Complete: ValidateWinNTAction at: 2005/9/11 13:3:30, returned true Running: ValidateMinOSAction at: 2005/9/11 13:3:30 Complete: ValidateMinOSAction at: 2005/9/11 13:3:30, returned true Running: PerformSCCAction at: 2005/9/11 13:3:30 Complete: PerformSCCAction at: 2005/9/11 13:3:31, returned true Running: ActivateLoggingAction at: 2005/9/11 13:3:31 Complete: ActivateLoggingAction at: 2005/9/11 13:3:31, returned true Running: DetectPatchedBootstrapAction at: 2005/9/11 13:3:31 Complete: DetectPatchedBootstrapAction at: 2005/9/11 13:3:31, returned true Action "LaunchPatchedBootstrapAction" will be skipped due to the following restrictions: Condition "EventCondition: __STP_LaunchPatchedBootstrap__1952" returned false. Running: PerformSCCAction2 at: 2005/9/11 13:3:31 Loaded DLL:C:WINNTsystem32msi.dll Version:3.1.4000.2435 Product "{53F5C3EE-05ED-4830-994B-50B2F0D50FCE}" versioned 9.00.1187.07 is not compatible with current builds of SQL Server.Expected at least version: 9.00.1314 The Product Name is "Microsoft SQL Server Setup Support Files (English)" Loaded DLL:C:WINNTsystem32msi.dll Version:3.1.4000.2435 Error: Action "PerformSCCAction2" threw an exception during execution. Return Code: 70032 Message displayed to user SQL Server 2005 Setup has detected incompatible components from beta versions of Visual Studio, .NET Framework, or SQL Server 2005. Use Add or Remove Programs to remove these components, and then run SQL Server 2005 Setup again. For detailed instructions on uninstalling SQL Server 2005, see the SQL Server 2005 Readme.
I understand that I need to uninstall "Microsoft SQL Server Setup Support Files (English)"
But when I try to do that I get "A network error occured while attempting to read from C:WinntInstallerSQLSupport.msi"
I have a user who is trying to load SQL server 2005 Express on a new Acer laptop computer. The computer has the following: Windows Vista Home Premium 32 bits AMD Turion 64 mobile technology CPU 1790 MB of RAM When the user tries to load SQL server 2005 Express it does not load. The user after the load is getting an €śUnhandled exception has occurred in your application€? when he tries to run the application. Does anyone know how to fix this problem?
I am the owner of a small business and run sports leagues on my website. I have been using a csv file to gather registrations from my webform and then manually importing them to Access. I also use the same method for collecting their results each week. Can I use SQL to automate my process? If so, where to I get the training I need to set that up? I'd like my customers to be able to log in to their account on the website and post their results (which attach to their name/profile). Then I'd like to extract that information in a report that I can post to the website. Or maybe have the customer sort the results by different criteria types. Can I do that with SQL? More importantly, am I even in the right ballpark for getting this done? Thanks to anyone who can give me a nudge in the right direction.
this is a seemingly simple query, but i've been googling around for a while and haven't been able to come up anything, probably because i simply can't explain what I'm trying to do
can someone please point me in the right direction? using sql server 2005.
tbl_vendorprice looks like:
id vend price 1 A 2.00 1 B 3.00 2 A 4.00 2 B 3.50 3 A 8.00 3 B 8.50
Charter Cable does not provide server technology to residential customers. I know nothing about SQL. I believe that I read that it can be setup on my computer for testing web pages.
I may not even be in the right forum. Would someone please direct me where to start?
Is it possible to have a SqlParameter setup as an Output, and get it to return @@Identity without using a stored procedure?
I have an INSERT statement, written as TEXT in my code (at the time being, I cannot create stored procedures). I'm trying to find out how to return the created IDENTITY that was generated.
Can someone please explain to me how this works? Thanks.
Hello, thanks for reading this. I am trying to learn a bit about debveloping my own website, and I am in need of a basic direction on how to acomplish this task:
I have an local program running on my PC that creates an MS Access database export of my inventory. This export can be directly uploaded to my web server in a zip format. My web site is developed in Coldfusion MX7, and uses a MS SQL 2005 database. What I would like to do is create somewhat of an "Auto update" for my website, exporting my access database and updating the MS SQL database automatically.
I really can use some good direction on this problem. Thank you for any help in advance.
Hi all. The company I work for is looking for a new SQL server. Where can I find information and or a tool for sizing information? By sizing information I mean how big a pile of hardware am I going to need to run MS SQL for x number of connected users with x size database, etc. I've been tooling around the internet and MS' site but can't find any info on this.
My task is seemingly simple. I have data on the server in MS Excel Files. I need to get the data into multiple tables in a SQL Server db on the same server.
I have been only working with SSIS for a bit, so please bear with me.
I can load the data directly from the Excel worksheet to one table, but I need to run an already defined stored procedure on the data from Excel before putting it into tables. I need to loop over all the rows and run the data from each row through the stored procedure.
So, I think I need an Execute SQL Task withing a For Each Loop, but neither is available on the Data Flow page, and I don't see how to use them in the control flow page. I don't see that any of the Data Flow transformations which are available on the dataflow page will do what I need.
I can have created the data flow Source-Query and the Destination-Query; it's the bit in between that has me hung up.
Can anyone please give me a high level overview of what I need to do, or point me to an example of something similar to what I am trying to do?
I went to look at the connection string previously entered for a dataset created in a new report, and am not seeing anything intuitive for bringing up the associated datasource dialog box that was used to enter name, type and connection string. I'm also noticing nothing intuitive for deleting an existing dataset. How do you do these two very simple things in an existing project? I dont see the dataset in solution explorer, I see it only in the text box on the data tab and in a limited kind of way on the dataset view where the columns show and maint is allowed mostly on the columns only. I tried hilighting the dataset here and hitting the delete key to no avail.
I would like to restore database using RESTORE DATABASE ... REPLACE command. If database exists already and has any open connections this command will fail. I would like to close all existing connections to specific database before running RESTORE DATABASE ... REPLACE command. I can do closing from Management Studio using checkbox "Close Existing Connection" when deleting database. Actually I need to do the same but from script.
In the SqlDataSource control if I go to the SelectQuery property and I set one parameter with the “direction� property to “Output� the result doesn’t display in the control, why?
Points: The procedure witch is in the selectquery property the parameter in it is set to output two.
Has anyone created a stored procedure that notifies a customer that their order_status has been changed from 'submitted' to 'backordered' or from 'backordered' to 'shipped'? Once written, how does the sp get automatically executed? Does anyone have an example or a book/site with an example?
We lost our Datawarehouse developer that set up this application for us, so I am now in charge... ick, and don't know what I'm doing really.
Lately no messages leaving sys.transmission_queue... all have transmission_status of: "One or more messages could not be delivered to the local service targeted by this dialog."
Setup looks like this:
CREATE MESSAGE TYPE XmlSubmission VALIDATION = WELL_FORMED_XML; CREATE CONTRACT XmlContract (XmlSubmission SENT BY INITIATOR); CREATE QUEUE ReceiverXmlQueue WITH STATUS = ON, ACTIVATION (PROCEDURE_NAME = MMRS.MMRS.WrapXmlProcessor, MAX_QUEUE_READERS = 1, EXECUTE AS SELF ); CREATE QUEUE SenderXmlQueue; CREATE SERVICE Sender ON QUEUE SenderXmlQueue; CREATE SERVICE Receiver ON QUEUE ReceiverXmlQueue(XmlContract);
ALTER QUEUE ReceiverXmlQueue WITH STATUS = on
Service Broker is enabled on database, and I've tried dropping and recreating these objects to no avail.
When functioning, these messages get recieved and procecessed into a staging SQL table, storing the XML to be loaded up into the relational tables.
Any ideas or clues for troubleshooting would be greatly appreciated!
I have some reports on a report server that needs to be reached in two ways. 1 - From a windows client application (over internet) 2 - From a web browser (over internet) Windows authentication could not be used here, since the users are not part of any domain etc, so I have implemented forms authentication for Reporting Services as described here: http://msdn2.microsoft.com/en-us/library/ms160724.aspx My first task was to access the reports from the windows forms application, and I managed to do that without too much trouble using a ReportViewer and setting the custom credentials in this way: myReportViewer.ServerReport.ReportServerCredentials.SetFormsCredentials(null, "loginname", "password", ""); Works great! However, now I'm dealing with the second problem... accessing the reports from the web. Sure, this works fine if i just type http://myserver/reports/. This brings up the custom loginpage and if I enter correct login information I get access to the reports... The problem is that I don't want to show everything 'around' (on top of) the report, I just want to show a report. Lets say I have a webpage with two frames. One on the left with a couple of links each representing a singel report. When clicking the link the report should be rendered in the right frame. What approach should I use here? When and where should the user authentication take place? I have done some testing with an ASP.NET application using a report viewer, but I seem to get the error: Client found response content type of 'text/html; charset=utf-8', but expected 'text/xml' I guess this is because Reporting services wants to bring up the login page? I can't find any way of setting the credentials for the reportViewer in my ASP.NET app, like I did in the WinForms app. I'm really not a web-developer so I might be missing some obvious points here, if I do, please point me in the right direction... I have also read about using the Report server web service to render the html-page without using the reportviewer, but when I try to make a call to a service like this: ReportingService _rs = new ReportingService(); _rs.Url = "http://myServer/ReportServer/ReportService.asmx"; CatalogItem[] items = _rs.ListChildren("/", true); I get an exception like the one above, telling: Client found response content type of 'text/html; charset=utf-8', but expected 'text/xml' Where should I go from here... any help would be much appriciated! Regards Andreas
How can you set the text direction to BT-RL 'bottom to top' in SRS? There is control for TB-RL but not BT-RL. Need this for SRS label printing application.
I am wondering if somebody out there wouldnt mind looking over my stored procedure?
The idea behind this procedure is (was) that,
(they are numbered...)
1) the procedure checks for the existence of data in the database and
2) if no data exists (if the previous statement throws an error), then execute the insert statement
3) otherwise (data exists), execute the update
I also have "sub" error handling for each statement so, if it is executing the insert, return 99 for fail, 0 for success. the same for update.
problem is that its returning the values for the first select statement and i have no idea what is going on fir the Insert or Update statements as i cant set break points in a stored procedure.
I am using the MS SQL Server Management Studio Express to create a stored procedure in one of my databases. I specify one of the parameters as OUTPUT as follows:
set ANSI_NULLS ON set QUOTED_IDENTIFIER ON GO
ALTER PROCEDURE [dbo].[ProcRetDbl]
@Threshold real, @Result real OUTPUT AS BEGIN
SET NOCOUNT ON;
SELECT @Result = Channel1 FROM DataTable WHERE Channel2 < @Threshold END
But then when I look at the properties of the @Result parameter in the Object Explorer's tree, it is shown as "Input/Output". Now, this seems like no problem at all since it will work fine as output, even though I don't need it to be able to do input as well, but I'm wondering why that is happening.
I am using ADO.Net on the other end to execute the procedure and I need to decide what parameter type to set to the SqlParameter object: "Output" or "InputOutput". I'm sure I can sort this out but I usually like to know what I'm doing. Thanks for the help.
My company has developed a VB.NET 2003 application which interfaces with the SQL Server database of another software package. We are experiencing serious performance differences at one of our client sites on a particular query that is one of the frequent queries that runs in our software which I will try to outline below.
Our database and the accounting system database are in separate instances on the same SQL Server 2005 box. Our query is a simple SELECT statement with filtering which properly utilizes the built-in indexes for that table. In their case it returns roughly 13000 records (vendor listing). There is no report of slowness regarding other queries as none of them return the volume that this particular query does.
On one offending workstation: if we run this query, it takes less than a second to get results back in SQL Management Studio's query window. This is consistent with the accounting software which takes about 2 seconds to run the query and display the results in a grid (the accounting system is Viewpoint, a leading construction accounting system written in VB6). In our system, attempting to do the same thing as the accounting system, it is taking nearly 16 seconds to retrieve and display the results. This workstation is running on WIndows XP SP2. They are experiencing this same behavior on several workstations.
At the same client site, they have identified two Windows 2000 workstations which can run the query and display the results from our system in approx. 2 seconds - which is what we would expect and is also what we experience internally on our development platform, and 12 other clients who run our software and interface with the same accounting system - regardless of OS on the workstation.
Based on the results in the query window and the results on the 2000 workstations and the success of other clients who have as many records if not more than this particular client - i don't think we have an issue with the query or the code. The results are very consistent on each workstation as well so I don't think it's a network traffic or activity issue. My gut points me at .NET Framework version differences - since the things that would affect this process are framework items - SQL Server OLEDB drivers and objects, DataGrids, Datatables and DataViews,etc.
On one of the XP workstations I had them remove .NET framework 3.0 which had no effect on the problem, but they also have v. 1.0.3705, 1.0 Hotfix (KBB886906), Framework 1.1, 1.1. Hotfix (KBB886903), Framework 2.0 with 2 security updates (KB917823 and BK922770).
Can anyone point me in a direction on things that I can check that might be different between their XP and 2000 workstations which might affect this process? I am working on some caching logic which will reduce the number of times this query is run, but I would like to understand why there is a behavioral difference.
Any help is appreciated. Please let me know if there is any other information I can provide.
Hello and thanks for reading, I would like to know if it's possible to add around 4000 users into the ASPNETDB without adding them one at a time. I have to avoid using a "create user wizard" because this login is for a private online election only. The list of users is in an access database and looks like this: Username Password DHunziker Hu8645 SEnderton En0854 BAckler Ac9576 .... Unfortunately, I don't have valid emails for all 4000 people, nor do I have the time and resources to collect security questions and answers so I don't know if this is even possible. Can you help me? Thanks!
I have been programming access databases for 8 years.Well today I find out that I need to know SQL.HUH LOLwell I don't have a clue as to how people even enter data intoa sql database or how to manipulate or retrieve data.Can someone please recommend a book to get me started?thank you very much for any and all help with this.
When running a business logic handler at the subscriber, you can override SubscriberInserts, SubscriberUpdates and SubscriberDeletes. Are these methods called when data is changed coming from the server to client, from the client to the server, or in both directions?
I just started a new job and I'm totally new to our SQL 2000 server (v. 8.00.760). My boss asked me to look into 'cleaning up the database' and has also mentioned reindexing as well. He seemed to suggest that this was a pretty minor thing to actually do but I'm absolutely clueless on where to begin.
At this point, with having no experience, I'm very hesitant to do anything that may negatively impact our SQL database. I would appreciate any and all advice from more experienced people to help get me pointed in the right direction.
I am developing a report that should be localized dynamically. The report will have both arabic and western users. Therefore I need to use the direction property of some single text boxes, which works fine. But when setting the direction property to RTL for a table it has no affect! You would assume that the whole tables content should be "reversed" and also the text in the table went from right to left. But no.
When viewing the report in IE and right clicked and click "View Source" you can see that the table do not have a direction style set, even though I set the property on the table in design mode.
I am going crazy about this issue! Could this be a MS bug?
I really need some help here...
PS. I have tried numerous of combinations of the different International properties to get the direction property to work - with no luck.
