Is there any sample code to demo the SSB send messages with same sql instance?
my case is very simple:
I want write a stored procedure to send a xml to another database. The stored procedure is called by tables triggers when some data is changed under the specific conditions.
In the "Example: Detecting a Poison Message" section, it reads: This Transact-SQL example shows a simple, stateless service that includes logic for handling poison messages. Before the stored procedure receives a message, the procedure saves the transaction. When the procedure cannot process a message, the procedure rolls the transaction back to the save point. The partial rollback returns the message to the queue while continuing to hold a lock on the conversation group for the message.
View 1 Replies View Relatedhello
Plesase help me by sending any of demo application and course material for WinCE visaual studio 2005 for development of smart device applications how to connect to atand alone database and for connectivity for Http to mobile .
Hi
Books online mention the existence of sample code for several custom tasks, including the one mentioned in the title. But, when I try to find this code in the location mentioned it is nowhere to be found.
I have run a search on the rest of my drive and come up empty.
Can anyone tell me where to find this?
Thanks
Hello, please help!!
I have spent days searching the web and forums for an answer to this simple question and cannot find an example.
I have built a service broker application on sql server 2005. The application puts some xml on an incoming queue which is basically a few parameters to be used in a query. This queue will then call a stored proc which does some business logic and puts the resulting results in another queue also in xml.
I have written a test harness in SQL to put messages on the inbound queue and then some sql to retrieve the returned code from the outbound queue.
What I want to do is be able to convert the SQL which does this into .net code to be used by an application. i.e. write in .net some code to put xml on a queue and then write some .net code to retrieve xml from another queue.
I wouldn't have thought this would be a difficult thing to do and would have been done hundreds of times, but unable to find anything to simply send and retrieve XML to service broker queues....
thanks for your help.. its really needed. I found some links, but they are really vague and often doing select statments in service broker or something like this. I don't want to call any sql, just send and recieve XML on the queues.
any example code that does this, would be really helpfull
kind regards,
David Weeden
Database Developer
hello
can sql server 2005 send messages to asp.net2.0 and to inform that a user hasn't (for example) the right to read a query
how?
thanks
Hi,
I neew a small sample code in VB.NET to create a custom data flow component for SSIS.
All the examples I was able to find it are in C#.
Answer very apreciated.
hi,
Does anyone have any sample code for using ntext data type, when inserting or reading big block of texts? How can you read a big huge block of text back into c#?
thx
-Philip
This is a sample code from an MSDN help site. I copied it and pasted into an open new query. I tried to execute it and got two errors:
USE AdventureWorks;
GO
DECLARE @tablename sysname
SET @tablename = N'Person.AddressType'
table_loop:
IF (@@FETCH_STATUS <> -2)
BEGIN
SELECT @tablename = RTRIM(UPPER(@tablename))
EXEC ('SELECT ''' + @tablename + ''' = COUNT(*) FROM '
+ @tablename )
PRINT ' '
END
FETCH NEXT FROM tnames_cursor INTO @tablename
IF (@@FETCH_STATUS <> -1) GOTO table_loop
GO
The errors are:
Msg 208, Level 16, State 1, Line 1
Invalid object name 'PERSON.ADDRESSTYPE'.
Msg 16916, Level 16, State 1, Line 9
A cursor with the name 'tnames_cursor' does not exist
The database is connected. Table Person.AddressType is a part of it.
What is wrong?
Thanks.
hi,
i learn by practice and i would like an asp code using c# that connects to sql database can retrives information from a table.
can you please copy paste me some code or give me exact link a?
thank you
I was thinking of using a CTE to return history, have you ever done this?
I know this can be done in other ways but I wanted to wrap this CTE in a view so people could join to it.
Here is an example table:
KEY ASOFDATE
A 1/1/2007
A 3/1/2007
B 1/1/2007
B 2/5/2007
B 3/6/2007
I want the results to look like:
KEY START END
A 1/1/2007 3/1/2007
A 3/1/2007 NULL
B 1/1/2007 2/5/2007
B 2/5/2007 3/6/2007
B 3/6/2007 NULL
This would be even better:
ID KEY ASOFDATE
1 A 1/1/2007
2 A 3/1/2007
3 B 1/1/2007
4 B 2/5/2007
5 B 3/6/2007
I want the results to look like:
ID KEY START END
1 A 1/1/2007 3/1/2007
2 A 3/1/2007 NULL
3 B 1/1/2007 2/5/2007
4 B 2/5/2007 3/6/2007
5 B 3/6/2007 NULL
Hi,
If somebody have worked on providing the code for the selection of delivery method and delivery schedule page in aspx . Then please share it with me. I am working on creating a Subscription page using aspx rather then reporting services page.
Please let me know if there are any controls provided by reporting services or some 3rd party for this.
Thanks & Regards,
Sam
Hi all!
Help, please!
I am trying to send messages between 2 different server instances. I am getting the following errors in Profiler:
"This message could not be delivered because the user with ID 0 in database ID 14 does not have permission to send to the service. Service name: 'TestService1'."
"The target service name could not be found. Ensure that the service name is specified correctly and/or the routing information has been supplied."
The scripts for object creation and messaging is following at the first server instance:
USE master
GO
CREATE ENDPOINT SBroker
STATE = STARTED
AS TCP ( LISTENER_PORT = 1212 )
FOR SERVICE_BROKER (
ENCRYPTION = DISABLED
);
GO
USE Test
GO
CREATE QUEUE TestQueue
WITH STATUS=ON
CREATE SERVICE TestService
AUTHORIZATION dbo
ON QUEUE TestQueue
CREATE ROUTE TestRoute
WITH
SERVICE_NAME = 'TestService1',
BROKER_INSTANCE ='2B7CE76A-9804-46F3-9AE8-0AE59313613A',
ADDRESS = 'TCP://10.17.11.17:4037' ;
// send message script:
DECLARE @dh UNIQUEIDENTIFIER;
BEGIN DIALOG CONVERSATION @dh
FROM SERVICE [TestService]
TO SERVICE 'TestService1','2B7CE76A-9804-46F3-9AE8-0AE59313613A'
ON CONTRACT [DEFAULT]
WITH ENCRYPTION = OFF;
SEND ON CONVERSATION @dh MESSAGE TYPE [DEFAULT] ('this is message1');
DECLARE @status nvarchar(1024);
SELECT status = GET_TRANSMISSION_STATUS(@dh);
END CONVERSATION @dh;
on second server instance:
use master
GO
CREATE ENDPOINT SBroker2
STATE = STARTED
AS TCP ( LISTENER_PORT = 1212 )
FOR SERVICE_BROKER (
ENCRYPTION = DISABLED
);
GO
in first server, in sys.transmission_queue transmission_status is empty
Both servers in the same domain, and databases on this server has the same owner.
on both servers, select @@version:
Microsoft SQL Server 2005 - 9.00.2047.00 (Intel X86)
Apr 14 2006 01:12:25 Copyright (c) 1988-2005 Microsoft Corporation
Developer Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
Thanks a lot for help and explanation!
Sveta.
Does anyone know where to find a simple sample showing you how to use asp.net 2.0 with the login control to direct you to another aspx page using SQL 2000 as the database on a hosted server? I have been beating my head against the wall trying to figure this out and would be very appreciative if anyone knows a place out there that shows this? I can find many sites that show you how to do this with SQL 2005, but not SQL 2000. Thanks - Chris
View 4 Replies View RelatedI am trying to write a ssis surrogate key data transform, my problem is I can't find an example how to add a column to the incoming columns and add some data to it. If anyone has a sample, can you please post it. I found a script option that works but I would like an actual transform.
Thanks
I am looking for some examples of code for an appliction which will let run RS reports from a custom application. The user will choose the reports from a menu structure
We have an exiting application which we use to run crystal reports which we are going to be migrating to RS. We use custom web controls to capture the required parameter values for the reports, and hope to reuse the same controls (with minimal rework) to capture the parameters values for the RS reports.
What I am after is some code which will
a) let me run the report and display it. I don't want to display the report parameters in the URL etc as we generate some report parameter values based upon the user's ID and do NOT want them displayed back to the users
b) export the report to PDF, CSV etc depending upon the option chosen by the user.
The application will be developed in VS 2005 - VB. Does any one know of some links to some sample code
Hi, there;
I try to import data from ODBC using C# programmatically. Is there any sample code we can have a look. Like how to read schema from ODBC source table and then create source column...
Thanks
Greetings,
I have been developing VBA apps in Access and Excel for sometime and am fairly proficient in VBA. Now we are moving all of our data to SQL Server 2005. I am in need of learning how to write code for the Script Component of a data flow task. And so I have a couple of questions.
First, are there any books you recommend for learning ???? (I'm not even sure what I need to learn: .NET? ADO.NET?)
And as a follow-up, any good websites that provide good reference documentation?
And my second question is more specific to my current problem. If I had a bit of code to get me started, I'm sure I could scream all the way to the bottom of the hill.
Data source is coming from a sort task where the data is sorted by STATUS and then MOD_DATE and the AUDIT_ID.
I need to read each row and compare it to the next row. If STATUS is the same, discard the second row.
When STATUS is different, send the first row to the output (to be used by the next task in the data flow).
Using the "different" row from step 3, go to step 2.
I know how to write if statements, case statements, for/next statements. I'm just not understanding how to read the rows in and then send them back out. I've been searching for some sample code but everything I find tends to be solving much bigger issues.
Any help you can provide would be much appreciated.
Rob
Hi All,
Are there samples for working with Inegration Service ?
As I see in shipped samples are C# and VB only.
Particulary I need a C++ code for "Enumerating Available Packages Programmatically"
Thanks a lot.
Sergiy
Hello,
I have a question..
I set-up my Service Broker comunication on the same SQL Server Instance beetween 2 different DBs.
One DB behave as Initiator and send messages to the SB service setup in the other DB.
I execute the SEND statement from the Initiator and if I count the messages on the sys.transmission_queue before to commit the transaction the count returns 0.
If I try to send a message not compliant with the message type, the count that runs after the SEND returns 1 - far enought.
I'm confused about the first behaviour because from what I understood the Acknolodgment and the removal of the message from the sys.trasmission queue should happen after the COMMIT.
Some expert can aswer to this direct quection?
Thank you
Marina B.
hi
How to upload excel data to sql server 2000 thorugh .net application.
I want like one upload button should be there,we have to browese corresponding excel file and then we need to upload to database.
before uploading it has to ask appending or replace everything in table.
both options appending and replacing shoulb be there.
How to upload ?
any sample code is there plz give me.
Please help me.
Thanks.
After much work and thanks to all of you who helped on this here is a code sample that can be adapted. From the dataflow task add an OLEDB source component, a row count component and finally a Script Destination Component.
On the Script Destination Component rename the Input node of the imports and outputs tree view to "ParsedInput"
The readonly User: variables that start with gs can be read in the PreExecute method
The readwrite User: variable giSuccessCount can only be used in the post execute task because it is populated by the Row Count Component which is the previous object in the Dataflow
The xml code is adapted from an idea in Donald Farmers book
enjoy
Dave
Now if someone can make a Script Source Component that can read a file with a header , data body and trailer that would b egreat!
' Microsoft SQL Server Integration Services user script component
' This is your new script component in Microsoft Visual Basic .NET
' ScriptMain is the entrypoint class for script components
Imports System
Imports System.Data
Imports System.Math
Imports System.IO
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper
Imports System.Xml
Public Class ScriptMain
Inherits UserComponent
Dim sw As StreamWriter
'In addition to using the Imports System.Xml statement a reference must be added to the
'System.Xml assembly (Select Project-Add Reference from IDE)
Dim xWriter As XmlTextWriter
Dim OutputFileType As String '.csv or .xml
Public Overrides Sub PreExecute()
'Read Only variables
Dim gsPickUp As String = Me.Variables.gsPickUp 'D:ftprootOutAvid'
Dim gsPickUpFilename As String = Me.Variables.gsPickUpFilename '1_AVID_'
Dim gsPickUpFileExtn As String = Me.Variables.gsPickUpFileExtn '.csv'
Dim gsMemoText As String = Me.Variables.gsMemoText 'Memo Text : credit adjustment'
Dim gsStatementText As String = Me.Variables.gsStatementText 'Statment Text : credit adjustment'
Dim gsRunMode As String = Me.Variables.gsRunMode 'UPDATE'
Dim fileName As String = gsPickUp & "" & gsPickUpFilename
fileName = fileName & (Format(Now(), "yyMMdd").ToString)
'MsgBox(fileName)
OutputFileType = gsPickUpFileExtn
If OutputFileType = ".csv" Then
fileName = fileName & gsPickUpFileExtn
sw = New StreamWriter(fileName) 'connection to dest file
'Header records
sw.Write(gsRunMode)
sw.Write(Environment.NewLine) ' end of line
sw.Write(gsMemoText)
sw.Write(Environment.NewLine)
sw.Write(gsStatementText)
sw.Write(Environment.NewLine)
sw.Write(Environment.NewLine) 'Spacer
End If
If OutputFileType = ".xml" Then
fileName = fileName & gsPickUpFileExtn
'xWriter = New XmlTextWriter(Me.Connections.XMLConnection.ConnectionString, Nothing)
'xWriter.WriteStartDocument()
'xWriter.WriteComment("Customer file parsed using script")
'xWriter.WriteStartElement("x", "customer", "http://some.org/name")
'xWriter.WriteAttributeString("FileName", Me.Connections.XMLConnection.ConnectionString)
xWriter = New XmlTextWriter(fileName, Nothing)
xWriter.WriteStartDocument()
xWriter.WriteComment("Customer file parsed using script")
xWriter.WriteStartElement("x", "customer", "http://some.org/name")
xWriter.WriteAttributeString("FileName", fileName)
End If
End Sub
Public Overrides Sub ParsedInput_ProcessInputRow(ByVal Row As ParsedInputBuffer)
If OutputFileType = ".csv" Then
Dim delim As String = ","
sw.Write(Row.ProjectID.ToString + delim)
sw.Write(Row.TransactionRefNum.ToString + delim)
sw.Write(Row.CustomerNum.ToString + delim)
sw.Write(Row.AccountNum.ToString + delim)
sw.Write(Environment.NewLine) ' end of line
sw.Flush() 'send the stream to file
End If
If OutputFileType = ".xml" Then
xWriter.WriteStartElement("CUSTOMER")
xWriter.WriteStartElement("ProjectID")
xWriter.WriteString(Row.ProjectID.ToString)
xWriter.WriteEndElement()
xWriter.WriteStartElement("TransactionRefNum")
xWriter.WriteString(Row.TransactionRefNum.ToString)
xWriter.WriteEndElement()
xWriter.WriteStartElement("CustomerNum")
xWriter.WriteString(Row.CustomerNum.ToString)
xWriter.WriteEndElement()
xWriter.WriteStartElement("AccountNum")
xWriter.WriteString(Row.AccountNum.ToString)
xWriter.WriteEndElement()
End If
End Sub
Public Overrides Sub PostExecute()
If OutputFileType = ".csv" Then
'Create the trailer
sw.Write(Environment.NewLine) ' blank line
sw.Write("RECORD_COUNT: " & Me.Variables.giSuccessCount.ToString) 'ReadWrite Varible
sw.Write(Environment.NewLine)
sw.Flush() 'send the stream to file
'Close file
sw.Close()
End If
If OutputFileType = ".xml" Then
xWriter.WriteStartElement("RecordCount")
xWriter.WriteString(Me.Variables.giSuccessCount.ToString)
xWriter.WriteEndElement()
xWriter.WriteEndElement()
xWriter.WriteEndDocument()
xWriter.Close()
End If
End Sub
End Class
I have some long running stored procedures that I invoke from ADO.NET (using Typed Datasets).
Inside the stored procedure, I like to keep track of the execution by using the PRINT command.
Is there a way of extracting and displaying this PRINT information in .NET during the stored procedure execution?
Hi everyone,
Primary platform is Framework 2.0
Our current service throws on-demand .DTSX and now we'd like that will throw old ETL 2000 too.
I'd like to know if success or not success when calling dtspkg.dll from my managed code (vb).
Is it possible or not? I only capture errors for the sake of TRY..END TRY but I don't know how the hell to know
if the dts execution was successful.
Execute method not returns anything.
Currently my schema is the following:
paquete = New DTS.Package2
paquete.LoadFromSQLServer("SRVDESA1", , , DTS.DTSSQLServerStorageFlags.DTSSQLStgFlag_UseTrustedConnection, _
, , , "pruebaenric")
paquete.FailOnError = True
Try
paquete.Execute()
Catch ex As Exception
' stuff
End Try
paquete = Nothing
I've tried to use WITHEVENTS but it's useless at all from there.
Events are not fired. I though that RCW created for this could be able to call them...
Private Withevents paquete as dts.package2
Private paquete_OnError()
stuff
Private paquete_OnProgress()
stuff
Thanks for any input related with,
Hi,
Does anybody have a working Java code sample that connects to an SQLServer 2005 database on a remote host, via the default named pipe, from a client using the SQLServer 2005 JDBC driver? Could you post it, or a pointer to it?
I've gotten java.sql DriverManager.getConnection() to work fine with TCP/IP connections before. But I'm a newbie with named pipes, and unclear on how the connection string/properties are different. I've tried to piece it together from multiple docs and threads, but haven't found sample code that quite fits my situation. I think a simple working example would best clarify the syntax.
The server is not using SQL Express. Most SQLServer configuration options are defaults; the named pipes protocol is enabled.
Thanks
I'm having a hard time locating a listing of potential error codes and the meanings for the Execute Process Task component...can someone assist?
Thanks
I want the server to send some auto email according to a column "expiry_date" in some table. How shall I do that? Use trigger?
Is it possible?
When I try to connect through SQL Server Management Studio,
the browser in the connect dialog can see the Reporting Services 2005 instance,
but when I try to connect, I get an error dialog with the message,
"An Unknown error has occured in WMI provider. error code 80040219."
it gives me no options for which instance to access
and connects to the default instance.
How do I connect to a 2nd named instance of SSRS via Management Studio?
i must use 2 different instance. (ex : 1) default instance 2) named instance) on the same physical server.
physical server os is cluster.
so i cant't remove one instance.
How can use 2 instance of SSRS via Management Studio?
Im trying to use VB.net 2005 to write a sample app to access a DB. Are there any samples for this and any samples of how I go about making the DB in the first place?
View 1 Replies View RelatedCan someone direct me where I can get my hands on SQL Server 7. I know it`s a beta but I would like to test it out.
Ted Tirado
The last 3 mornings when I get to my desk, there is a windows msgbox waiting for with the following information:
<<<<<<<<< Start of Message >>>>>>>>>>>>>>>>>>>>>>
'Demo: Sev. 24 Errors' on //MySQLServerBox
Description: 17550
DBCC TRACEON 208, server process ID (SPID) 52
<<<<<<<<< End of Message >>>>>>>>>>>>>>>>>>>>>>
It seems to be coming from the same machine, but I don't really understand the nature of the error and can't find and info in the Help files or MS Knowledge Base.
Curious if anybody on this forum can provide some insight or point me in the right direction.
Thanks,
Alex
Hello,
I am new to SQLserver and have installed the DEMO version of SQLserver 2000 on my system. I got it up and running and verified that it was working properly.
I then purchased two CPU's of the standard edition for my system. The software has arrived, but all that arrived are the CD's. I see no key, nor do I see a way to provide a key even if I had one.
I do not know how to proceeed and need help to figure out how to obtain a key or how to enter it if I ever obtain one.
Any help is greatly appreciated!!!!!
Thanks,
James
We're looking at several tools to do reporting and analysis of our SQL 2005 data.
We want to create canned web based reports
We want internal - non-developers to create canned reports for our customers
We want internal - non-developers to create ad hoc reports
We want to slice and dice the data in various way and produce graphs and trend reports - pretty stuff for management
Tools like Business Objects Edge satisfy many of these requirements but we already have SQL 2005 which I understand can do similar things. I can easily get a demo of a companys products to show me what can and can't be done.
Does anyone know how I can get a SQL demo using these services ? I tried contacting Microsoft but didn't get anywhere
I don't have the luxury of time to learn all the benefits and functionality to understand if SQL is the best way to go. Database wise - yes - but Analysis and Reporting ??
Thanks