SQL Server 2012 :: Return Multiple Messages To Client Application?
Jun 9, 2014
I have a procedure where after processing, i am required to send multiple message to calling application.
For ex:
create procedure test as
(@a as int,
@b as int
@c as int )
/*
some transformations */
print 'variable a is' + @a;
print 'variable b is' + @b;
print 'variable c is' + @c;
I am only providing a sample of return messages, but in reality there a lot more messages where a string and a parameter value need to concatenated.Unfortunately print is not allowing to concatenate parameter value.I can use RaiseError, but these messages are not really any error messages.
i tried to concatenate all message and output it using OUT type parameter, but the length of all messages combined exceeds 10000 characters.
Is there any other alternate to send these messages out to application?
View 1 Replies
ADVERTISEMENT
May 9, 2014
Very basically, I need to return a result set based on another value and only if there are multiples of that other value.
Example. select * from mytable
Returns (--column separater)
John---1---1
John---1---1
John---2---2
John---3---3
John---3---3
John---4---4
So I want a query that would return only
John---1---1
John---1---1
John---3---3
John---3---3
The query cannot be something as simple as this:
Select * from mytable where John=1 or John=3
I have to many possible results to plan like that. I am thinking some kind of aggregate with a group.
View 9 Replies
View Related
Mar 31, 2014
I have been investigating the number of connections activeinactive to a certain database server and I have stumbled across an application which seems to not be clearing its database connections.For one instance of a client there was >70 sql connections which eventuated from the closing and reopening one 1 screen in the culprut app. Once the application was closed all of the connections are recycled but its evident that within the application itself it is not correctly reusing already existing open connections.
I have raised a point with the main programmer that we need to investigate more into how the application is managingot managing its ADO .NET connections to SQL.
I am starting with doing some reading here URL... and I was hoping to get some more information about the possible impact of excessive sql connections on the SQL Server itself. Our organization is quite lucky in that our SQl Servers are Overspecced given their workload, bearing that in mind I would like to dig a bit deeper to get some stats if I can to highlight the scope of the issue to the managementprogrammers.Our SQL server peaks at 6500 processes and a good 70% of those are due to this applications mis-management of its sql connections.
View 6 Replies
View Related
Apr 16, 2007
Hi folks,
I have an application designed in vb.net and it needs to Communicate with MS SQL 2005 database which resids on SERVER and The applicaton needs to run from Client PCs... When i install the Application on Server itself it works fine.... But when i Install it on any of the client Pcs it gives me the following error....
Unable to install or run the application. The application requires that assembly Microsoft.sqlserver.ConnectionInfo version 9.0.242.0 be installed in Global Assembly Cache (GAC) First.
Any help guys ???
View 3 Replies
View Related
Sep 12, 2007
hie,
have a question. what tool is available to develop client/server application with db as ms sql server? is it only visual studio.
for example: i was using oracle 9i db with 9i developer as a total package to build applications. very easy with creating update, delete or new forms.
is it possible with visual studio or am i missing something?
i need to feel comfortable since i know sql server but dont know what to use for developing application.
many thanks,
farouque
View 2 Replies
View Related
Jun 5, 2014
We have a Silverlight based application which currently supports only one production version. Idea is to support three concurrent versions of the same application and user will switch to the newer versions based on their interest or they can still continue with the older version.
We still have to use the existing database for all these three versions.
What is the best way to architect this so that we can differentiate the code between the versions and still keep the data in sync and run all the versions in parallel.
View 7 Replies
View Related
Feb 19, 2006
I have a view that retrieves the details for sys.messages in SQL Server Express and noticed that the language ID is 1033 (U.S. English). I will need French and Portuguese, and understand that these are standard. Is there a set of DDL Scripts that will install the sys.messages for these languages?
thanks,
Flavelle
View 1 Replies
View Related
Jan 29, 2007
Hi guys,I'm not sure if I'm just bad at googling but I can't seem to find a way to set an ASP.NET 2.0 web application to connect to SQL Server 2005 using the current client's user credentials. My web application is using Integrated Windows Authentication so its Page.User.Identity is set to a DOMAINusername value... I want to pass that to my connectionstring or have my connections pick up the identity automatically and use that Identity when accessing the db server.Oh and another thing, my IIS Application Pool is using a specific Identity itself, so I don't know if that might affect the above.Hope someone could help.
View 2 Replies
View Related
Jul 20, 2005
SServer PC: Win SBS 2003 with 2.6 GHz processor and 1GB RAMSQL Server 2000 v 2000.8.00.76 (sp3)MS Office 2k3MSJet ms04-014 (latest ost sp8)MDAC v2.8 RTMADO 2.1vb6.exe / ADO 2.0I think this is a SQL Server/ADO problem as I have 2 applications withsame problem.My access database uses a timer based function to insert records intoSQL Server using ADO and stored procedures. Access also uses DAO ,Jet/ odbc to linked tables on SQL Server for many other tasks/forms.All is well when Access 1st run but after a few hours or so the Accessapp grinds to a halt.Upon checking the task manager the mem usuage upto 160MB and handlecount upto 86,000 ! (cpu process % is low).After the "Access Fail" if I stop/start access only, performance isnot returned, I have to stop/start SQL Server.It would seem that allconnections from this PC to SQL server are badly affected, it is nottied to the client application that had the problem.As I could not work out where the problem was I took the Accessfunctionality into a VB6 app, using ADO 2.0, thinking this shouldsimplify matters with Jet and ODBC out of the way.I now have the same problem with the number of handles increasing withevery new timer based function.* code snippet example *If Not OpenConnection Then 'we have not been able to open aconnection to SQL serverCall procLog("Connection failed to SQL server")Exit FunctionEnd If'gVar.cnnSQL is my public ADODB.ConnectionSet cmdSQL = New ADODB.CommandWith cmdSQL.ActiveConnection = gVar.cnnSQL.CommandText = "MyDB.dbo.insert_tblMyData".CommandType = adCmdStoredProc.Execute RecordsAffected:=lngRecs, _Parameters:=Array(lngID, dtDate,intCategory,strNationality,strNotes,strName)End With* code snippet *** After the "Access Fail" if I look at one of my clients, running thesame Access app on another PC, it seems normally responsive when usingone my bound forms to browse the data from same SQL Server **Any ideas anyone ?
View 3 Replies
View Related
May 5, 2006
Hi All,
I've developed an application that connects to a SQL Server 2005 Express database. I created a DSN to connect to the database through ODBC. Currently, I am testing locally and everything works fine.
I would now like to install my application on another workstation and connect remotely to the database located on my development machine.
The client workstation does not have SQL Server 2005 Express installed on it because I would just like my application to connect remotely by creating the DSN and using ODBC. What I'm missing here are the database drivers. The "SQL Natice Client" is not available on this client workstation. How can I deploy the necessary drivers with my installation file so that I may create the required DSN name using the SQL Native Client driver?
Thanks!
View 6 Replies
View Related
Apr 27, 2007
Dear all,
I have set up service broker to work between two instances of SQL server with Dialog Security (implemented using certificates). The initiator queue has a activation procedure attached to process the return messages.
I'm receiving the messages from inside a SSIS package using Receive statement. Once I recive the message, I store the conversation handle, message type and message body in variables and execute the remaining ETL package based on the input.
Towards the end of the package, I send a message back to the Initiator for the same conversation to indicate sucess or failure.
Code Snippet
declare @conversation_handle UNIQUEIDENTIFIER
select @conversation_handle = <<SSIS User Variable>>;
SEND ON CONVERSATION @conversation_handle MESSAGE TYPE [/OLAP/Error] (N'<Error>My custom error</Error>');
END CONVERSATION @conversation_handle ;
The problem now is that, the initiator queue receives only the "http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog" message. I would expect it to recieve "/OLAP/Error" first and then the "EndDialog" message.
Any idea on what's happening here? Any help is appreciated.
Cheers,
Arun
View 5 Replies
View Related
Feb 13, 2006
Hello All,
After hitting limitations in the SQL CLR world that bar us from invoking COM objects we are forced to use windows services to read the messages off the Service Broker Queues.
Unfortunately we loose the auto activation feature in the Queues, but we can still read messages and perform the SQL work under one transaction.
We are going to attempt to take N messages simultaneously from the Queue, though N instances of a windows service. If the messages send to the queue are one message per conversation, will we be able to achieve having N readers take messages off simultaneounsly?
Thank you very much,
Lubomir
P.S. if anyone has a better approach to obtaining the message in "out of sql code" or invoking external (not assemblies stores in SQL server) code libraries, that would be etremely nice to hear. I have thought about invoking a web service through CLR, but that is probably too much overhead - MSMQ seems much more appealing than a web service;
View 5 Replies
View Related
Jul 2, 2001
Hi, I am trying to find a way to capture all the status (Start time, execution time, Status messages etc) from executing a DTS package in to a table I will create in a database, does anyone know, where those information being kept?
When I excute the DTS package manually, a window will come up and show the status of each step within the DTS package. I am hoping to capture these information and load it to my log table.
Thanks in advance.
View 1 Replies
View Related
Dec 13, 2006
Hi all,
I have one application through which i m installing SQL Server 2005 Express Edition.
If some failure occures, all I get is one exitCode which is a return code of SQL Express installer. Is there any way by which I can find out the exact reason of failure.
One example is error code 28062.
This error code is for "sql is unable to install on compressed folder / drive".
Please help
Thanks and regards,
-Anup
View 1 Replies
View Related
Jul 5, 2006
I am trying to install the client tools for management purposes only on a IBM T42 laptop which meets all the hardware and software requirements listed on MS support including the HCL list, the Readme, the forums and MSDN but the install still shows the following log messages when the install fails.
AdminMessage = Setup requires user to be in the administrator group in order to continue the installation process. Setup is aborting as the current user is not in the administrator group.
(I am an adminsistrator on this machine)
Property(S): SupportedOSMessage = Installation of this product failed because it is not supported on this operating system. For information on supported configurations, see the product documentation.
Property(S): SupportedProcTypeMessage = This Microsoft SQL Server Native Client package is not supported on the current processor type.
Any help would be appreciated.
View 3 Replies
View Related
Nov 24, 2006
One of the big problems with the old MDAC was different versions on different client machines. You would test your app with the latest version say, but when you deployed it, it might fail as the client has a different version.
My company develops software for Municipal Government clients. These clients use other SQL Server applications as well as ours, but they can only use one version of the client software (MDAC) on a given desktop. That means if we require a particular MDAC version, but the clients other applications from other vendors don't officially support that MDAC version, the client is in a real jam.
Our software also supports Oracle, which allows our software to specify a particular Oracle Home which points to a directory with a particular version of the Oracle client dlls (along with corresponding registry entries for that Oracle Home), such that we control the exact client version of the Oracle software that the client uses with our applications. This will not interfere with, and is completely seperate, from the default Oracle home installed when you install the Oracle client software.
What I would like to see for the Native Client is the ability to have our applications use the version of the Native Client that we wish to support and deploy without interfering with the Native Client version used by other applications. Have a default Native Client, but allow applications to somehow specify a different Native Client version/set of DLLs.
Is there any such functionality with the Native Client? (I didn't see any mention of such in the documentation, but I thought I'd ask)
If not, are there any future plans to support multiple Native Client versions on the same desktop?
View 1 Replies
View Related
May 28, 2008
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
View 1 Replies
View Related
Aug 5, 2014
I concatenate multiple rows from one table in multiple columns like this:
--Create Table
CREATE TABLE [Person].[Person_1](
[BusinessEntityID] [int] NOT NULL,
[PersonType] [nchar](2) NOT NULL,
[FirstName] [varchar](100) NOT NULL,
CONSTRAINT [PK_Person_BusinessEntityID_1] PRIMARY KEY CLUSTERED
[Code] ....
This works very well, but I want to concatenate more rows with different [PersonType]-Values in different columns and I don't like the overhead, of using the same table in every subquery ([Person_1]). Is there a more elegant way to do this, without using a temp table or something else?
View 1 Replies
View Related
Mar 2, 2015
I have the following results:
ID, Office1
1, Testing
1, Hello World
What i am trying to do is to get this result:
ID, Office1, Office2
1, Testing, Hello World
how i can accomplish this task.
View 3 Replies
View Related
Apr 28, 2014
We have request where client want to host both application server(IIS...etc) and MSSQL DB server on same machine. (DB is used for Time & Attendance tracking only, so there are not many transactions per day and No HA) .........
Do we have any disadvantages if we host both on same Machine.?
View 1 Replies
View Related
May 14, 2014
I have table named TEMPLATE_ACTIVITY. This is template table I have 27 this kind of tables.
I want to create stored procedure to change name MICHELIN_US_ instead of TEMPLATE_ all remaining name should be same. For that I am using 'Create Table As Select' to keep same structure as Template tables.
I want to create sp as like execute this way Exec @MICHELIN_US_
So that in future if Client change to MICHELIN_US_ to UNITED_ I can just change Exec @UNITED_
And it will change all table names to UNITED_ACTIVITY
I want to create this SP for different client.
View 3 Replies
View Related
Apr 23, 2015
A heavily-selected database will be in an inconsistent state for several hours during a batch process. For that time, a database snapshot is created and accessed instead. To allow constant client read access to the database, a database that only contains synonyms exists. Those synonyms point to the main database except during the batch process, at which time they point to the database snapshot.
To switch the synonyms, each synonym is dropped and then created pointing to the database snapshot (after its creation, of course). The drop/create occurs inside a transaction. Roughly, the SQL looks like this:
SET XACT_ABORT ON;
BEGIN TRANSACTION;
DROP SYNONYM [dbo].[some_proc];
CREATE SYNONYM [dbo].[some_proc] FOR [snapshot_db].[dbo].[some_proc];
GRANT EXECUTE, SELECT ON [dbo].[some_proc] TO public;
COMMIT TRANSACTION;
When the batch update is completed, the process is reversed with "snapshot_db" replaced with "regular_db". The SQL snippet above is dynamic SQL. What I've pasted is the dynamic SQL that is executed as a single batch.
While this switch is happening, clients are accessing the procedures through the synonyms, potentially at a high request rate. Testing reveals that clients can get the error:
Error=-2147217900, Id=0, Meaning=IDispatch error #3092,
Source=Microsoft OLE DB Provider for ODBC Drivers,
Description=[Microsoft][ODBC SQL Server Driver][SQL Server]Could not find stored procedure 'dbo.some_proc'.
This error only occurs once. If the same SPID retries its request and the transaction has not completed (for testing, a delay was added), then it blocks until the transaction completes.
Any way to prevent it besides a client-side retry?
View 2 Replies
View Related
Jul 27, 2006
Hi Everyone,
I have a software solution that consists of two separate applications that share a SQL Server Express database on a single computer. Everything is working great, except when both of the applications want to access the database at the same time. All I can find is information on configuring multi-user access in a distributed system. I'd appreciate any information on configuring multi-application access on a single computer.
Thanks,
Mike
View 4 Replies
View Related
Jul 23, 2014
I am trying to setup a client alias to connect to a named instance on another server.
TITLE: Connect to Server
------------------------------
Cannot connect to fred.
------------------------------
ADDITIONAL INFORMATION:
A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server) (Microsoft SQL Server, Error: 53)
[URL]
The network path was not found
------------------------------
BUTTONS:
OK
------------------------------
I have set up the alias on the server and as expected it works. On the client side I have used the SQL Server Client Network Utility to configure the client side alias but no luck
I am using TCP/IP with a specific port ..
By the way the SQL is SQL Server 2012 Ent and the O/S is Windows 2012 R2 minshell
View 9 Replies
View Related
Apr 7, 2015
We have a database residing on a SQL Server 2012 Express system running under Windows Server 2012. I have installed the SQL 2012 Native Client on the user's workstation. I created the System DSN with the required settings and when I test the connection all tests pass. I am able to ping the server as well as connect to it via SQL Server Management Studio.
Now when the user launches the application he is presented with the dialog window as in Screenshot1 (see attached). After clicking on Ok he is next presented with the dialog window where he has to select the SQL server. See attached Screenshot2. The user selects the SQL server from the drop down list and he is then able to access the application and work normally. I cannot understand why this is happening because I have included the correct server instance in the DSN settings i.e. sqlserversqlexpress
Telling the user to select the server himself is not an option for us.
I have checked the settings on the SQL Server. TCP/IP is enabled, remote connections are allowed and there is no firewall blocking. Port 1433 has been set as the default port to use.
View 6 Replies
View Related
May 28, 2015
I have a script that resolve's data into xml like this, ex:
<root>
<title>A</title>
<id>1</id>
<nodes>
<node>
<id>2</id>
<title>A.1</title>
</node>
</nodes>
</root>
And works perfectly, but ... how to make sure every item has an element "nodes" ? The case here is for the child leafs obviously. This, because on the client i have to inject this element "nodes" on a json version of this xml, and just wanted to avoid normalizing the structure on the client.
For the root I am using
FOR XML PATH('root'),TYPE; and for the hierarchy that follows
FOR XML RAW ('node'), root('nodes'), ELEMENTS
View 0 Replies
View Related
Oct 18, 2006
Hi all...
I am writing a Windows Application which will be used by about 100 clients. (NT Authenticated)
The application is going to be used in a LAN enviroment. User who have access will download a VB.NET application from a web site.
Of course, I want to prevent any of the 100 users from using a tool such as Query Analysis or ODBC to connect directly to the database and modify data.
So, it appears the way to go is to the "Application Roles". For this to work, the application roll password has to be saved in the application to execute the sp_setapprole procedure.
Is there a way to secure this password? I am worried about a user somehow extracting the password from the application and connecting directly.
Am I on the right track here? Any ideas appreciated!
Thanks,
Forch
View 4 Replies
View Related
Dec 11, 2007
Dear all,
I need to develop fro my customer a WinForm application which will display a list of report name available fro my customer, who will be free to select the desire report to print.
For that I was previously using Crystal report but as now I am in an environment where report services is included and can be used I have decide to test it and see if it cover my customer need.
I have use reporting service in one of my customer project but by dircetly accessing it through the ReportServer default interface but never from a WinFrom application.
How can I implemented the connection to reporting service, accessing report template and have access to print function as well as export to different format in my application ?
Is there any step by step procedure somewhere ? could not found
thnaks for your help
regards
serge
View 6 Replies
View Related
Oct 19, 2007
I am currently modifying an existing application (which uses OLE DB Consumer Templates to access a remote SQL db) to utilize SQL Native Client instead. Does anyone have any experience making this change?
Am I correct in my assumption that all I have to do is:
change my included headers from 'sqloledb.h' to 'sqlncli.h'
and
change CLSID_SQLOLEDB to CLSID_SQLNCLI?
I am still including the 'atldbcli.h' header and therefore still using the OLE DB Consumer Templates.
Can I continue to use the Consumer Templates along with SQL Native Client?
View 4 Replies
View Related
Aug 3, 2006
Hi,
I am trying to install multiple application services on 1 sql server database. Is this possible? I do not know how to install anything but the default application services settings for the membership, roles, etc.
Please help if you can.
Thanks,
Ezeibe
View 1 Replies
View Related
Jan 3, 2008
I have a SS2K5 stored procedure that executes 2 others stored procedures
sp_zero1 and sp_zero2
sp_zero1 and sp_zero2 do the same thing ... raises an Divide by zero error
I need to (print / select into a database) both error messages using just one
try catch block instead of 2 blocks like in the next example:
-- THIS IS THE WORKING CODE THAT I DONT WANT
BEGIN TRY
exec sp_zero1
END TRY
BEGIN CATCH
PRINT ERROR_MESSAGE()
PRINT ERROR_PROCEDURE()
END CATCH
BEGIN TRY
exec sp_zero2
END TRY
BEGIN CATCH
print ERROR_MESSAGE()
print ERROR_PROCEDURE()
END CATCH
if I try the next code
--THIS IS THE NON WORKING CODE
BEGIN TRY
exec sp_zero1
exec sp_zero2
END TRY
BEGIN CATCH
print ERROR_MESSAGE()
print ERROR_PROCEDURE()
END CATCH
only the first error message is printed and the execution is stopped
This is a generic example ... in reality I have a stored procedure that executes tens and hundreds of other stored procedures ... so thats the reason I need just one block of try catch instead of hundreds of blocks
thank you
View 6 Replies
View Related
Mar 24, 2015
I have to create a query that will be returning the users and the application modules they have access for. Along with the list of users and modules the query should also return if any module is not accessible for that user.
The 'ApplicationUsers' table is like this:
CREATE TABLE #ApplicationUsers
(
userId INT,
UserName VARCHAR(50)
)
INSERT INTO #ApplicationUsers VALUES
(1, 'Daniel'), (2,'Martin'), (3, 'Brandon'), (4, 'Doug')The 'ApplicationModule' table is like this:
[Code] .....
I used pivot query but there are a couple of limitations in it(may be I am making mistakes in it). First, the pivot columns are static whereas I need dynamic column names(Application Modules). Second, I need to include all the users with all the modules with access or without access that I am unable to achieve.
View 5 Replies
View Related
Sep 24, 2014
I am building a bunch of packages on our new server and all was going well until I edited the project using the client tools on my PC. I now receive the below error if I try to execute any of the packages on the server (all is still fine on the client). I have scoured the net but I don't seem to be able to come up with a solution. I have tried altering the folder & object permissions for my login (that created the project on the server and edited using the client) but I still get the error.
ERROR:
TITLE: Microsoft Visual Studio
------------------------------
Failed to start project
------------------------------
ADDITIONAL INFORMATION:
Exception deserializing the package "Access to the path 'G:VisualStudioTestTestbinDevelopmentTest.ispac' is denied.". (Microsoft.DataTransformationServices.VsIntegration)
------------------------------
Access to the path 'G:VisualStudioTestTestbinDevelopmentTest.ispac' is denied. (mscorlib)
------------------------------
BUTTONS:
OK
------------------------------
View 4 Replies
View Related