i'm trying to make private messages like the one in this site, i didnt start programming yet, the problem is in the DB schema, i have 3 tables (Users, Messages, MessageDetails) i think the problem that the UserID is related to the other two Tables, so to know the sender and reciever, when i try to view all messages for specific user and show all users who sent it, it give nothing back, so any help in the DB, thanx 4 help.
View 13 RepliesHello, 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
Hi
I am trying to setup service broker by following the article on http://support.microsoft.com/default.aspx/kb/915852
The script ran without error, but when I try to send a message, I get the following in the trace Audit Broker Conversation and the message is not delivered.
The certificate's private key cannot be found
This message could not be delivered because the security context could not be retrieved.
Does anyone know what I did wrong, is it the Certficate in the Master Database or the Target Database
thanks
Paul
One of our clients controls data for about 150 companies. Each company has the same schema and is running SQL Server 7. The maintenence task is horrible. He would like to put all the data in one large database, but needs to control the access, so a user at one company selecting data in a table cannot have access to another company's data in the same table. I thought about using views for each company but that is 150 companies times 100 tables. With Oracle8i you can make virtual private databases. Can this be done in SQL Server 7? Thanks in advance
View 1 Replies View RelatedI set up Service broker (2008R2 -> 2012) across different servers and domains using certificates.I set up 2 queues (one for sending, one for receiving).I set up 6 services (3 for send, 3 for receive), all 3 sharing the same queue type. (srv_send_1, srv_send_2, srv_send_3), (srv_receive_1, srv_receive_2, srv_receive_3).I set up a route for each receiving service on the source, and for each sending service on the target.I set up a SB binding to the remote for each sender service type.I granted send rights to the sending services on the source server.
I then tested my first service, and it worked perfectly (still works).My second service however is failing with the error "The certificate's private key cannot be found".I am stumped, as all the setup code was a lot of copy/paste which I have checked, rechecked and even re-coded.Basically, here is what Im doing:
begin dialog @ConversationHandle
FROM Service srv_send_1
TO Service 'srv_receive_1'
ON CONTRACT myContract
WITH ENCRYPTION = OFF, LIFETIME = 60*60*24*7;
SEND ON CONVERSATION @conversationHandle
MESSAGE TYPE [my_msgtype] (@xml);
The above code works 100% with service1, but not with service2.Note that on profiler, the target server doesnt receive any communication. The error shows on the sender profiler.Also, The conversation shows up as CONVERSING in sys.conversation_endpoints and the entry exists in sys.transmission_queue.
Web server: win2003 server with iis6DB: sql server 2000I have 50+ remote offices running an Access 2002 app which connectsdirectly to sql server at a 3rd party hosting company, in part using anodbc connection on the workstation.We recently moved our database (and our web site) in-house. The newdatabase is inside the firewall but outside the dmz, where it livesalong side the company's most sensitive databases. IT's policy won'tallow us to connect directly from the internet. I'm trying to avoidbuying another copy of sql server for the Access app, which I one dayhope to drop anyway in favor ofThe web server uses port 1433 to talk to the database. I was wonderingif there is a simple way to route requests from the Access app throughthe web server.Thanks,Bob
View 9 Replies View RelatedI have a couple of questions on the nature of shared and private deployment of SQL Server CE 2005 on mobile devices:
1. I've read that with a shared install, SQL Server 2005 Compact Edition files are automatically updated and serviced by Microsoft Update. I take it that this only applies to installations on desktop computers -- if you're installing on mobile devices, there IS no automatic update. Right?
2. I've also read that in order to do a shared install of SQL Server 2005 CE, the user must have administrative rights. How does this apply to installs on a mobile device? There is no such thing as an administrative user on a mobile device. However, mobile devices CAN require different levels of privilege to install certain programs -- do you need something like an application signed for privileged execution (via a M2M certificate) in order to do a shared install of SQL Server 2005 CE on a mobile device?
Or is the shared versus private deployment only an issue when deploying to a desktop?
Larry
MSDN has examples of deploying SQLCE privately for C# and VB.NET, but that doesn't seem to translate to C++. A .NET application just loads the assembly it needs from the local directory, but from what I can tell I need to use the OLE DB driver to use SQL CE in C++. The problem is that the OLE DB driver is a COM object that (presumably) needs to be registered.
Has anyone successfully used SQLCE from C++ without registering any COM objects? I'm thinking the only way to do that would be to call DllGetObject() on the DLL and get the object manually, but I've never done this so I'd hate to waste the time if there's an easier way, or if this won't work.
We are looking to put column level encryption on a table. Great! Wonderful! Look in books online and see that you must give "control" perm to the Certificate for any user that needs to use the symmetric key.
Now my question. I have backed up the Certificate to file. What I have found playing around with the commands is it is possible to run the following command:
Alter Certificate MyCert
Remove private key
BOL says their is no "Restore Certificate" command and to just use "Create Certificate". I have tried this and I keep getting an error saying the certificate already exist in the database.
What have I missed? How can I restore this certificate without restoring the ENTIRE database? Is there a permission set that can be applied to the Certificate to stop thsi kind of command from being run while still having the symmetric key usable? Any help would be of great use!
Thanks,
*note* this is using the MasterKey->Cert->SymmetricKey
Is there any harm in defining a SqlConnection as a private class variable for a web page?Private SqlConnection cn = new SqlConnectection(ConfigurationManager.ConnectionStrings["MyString"].ConectionString);The connection is only opened in certain event handlers, and is closed right after use. It was always my understanding that creating the connection is not a big deal but opening the connection is. Is that correct?
View 1 Replies View RelatedI'm working on a VB.Net 2005 application that uses SQLCE as a backend database.
Following the instructions on how to do a private installation (http://msdn2.microsoft.com/en-us/library/bb219482.aspx) so that admin rights were not needed, I successfully got it working as a ClickOnce deployment. But today during a demo for a client, they ran into this error while installing the latest version from our publishing URL:
"Unable to install or run the application. Application requires that assembly System.Data.SQLServerCe version 9.0.42 .... must be installed in the Global Assembly Cache."
Basically it's saying the user isn't an admin so they can't install. This is the problem I went to great lengths to try to avoid, and is the reason we are using SQL CE in the first place: admin rights are not required to install our application's database. To our knowledge, SQL CE is the only Microsoft product that fits this scenario.
Now, I haven't changed anything in the publishing of the application to my knowledge, and when I check the project prerequisites, the SQLCE engine still isn't in there (as summarized at the above URL). Again following the instructions at the URL above for private installation, the required assemblies are still part of the project's files.
It must be something I did to cause this, but I have no memory of changing anything in regards to this part of the application. Our deadline is in 4 days and I cannot continue development until I get past this issue.
Where do I look to fix this problem? A little help would be greatly appreciated.
Here is what I am trying to do. I want to create a private / public key pair (either a certificate or asymmetric key) and be able to give the public key to my business contacts. They would then use the public key to encrypt data to be sent to me and then I could import that data into my SQL 2005 database and use the private key to decrypt it.
It seems to me that this should be a pretty simple process, but it is not. The reason I want to do this is that the data they send to me on a regular basis is sensitive. Currently we use PGP, but then I have to decrypt the data and then import it and then reencrypt it. It would greatly simplify my process if I could just import the data and use it.
So how can I do this. Most of the posts I read say that asymmetric keys are avaiable in SQL2005 but not recommended for use. I don't want my business contacts to be able to decrypt any of my data (if they got access to the database) just encrypt.
Any recommendations???
Thanks!
Jim Youmans
St Louis, Missouri
Folks- By reading all the results I got from doing a search on "private install" in this forum, I thought this must be as easy as:
1. Copying the seven DLL's comprising the engine to my application's install dir on the deployment machine. If my app is installed at C:MyApp then CE dll's would go into C:MyApp, thats where my WinMain'd exe is residing.
2. RegSvr32 on sqlceca35.dll, sqlceoledb35.dll
Since I'm using native C++/OLE DB, I don't think I'd need to care about System.Data.SqlServerCe.dll
However my app which is basically the NorthWnd sample is failing on CoCreateInstance(CLSID_SQLSERVERCE_3_0............)
Which means oledb provider not registered on the machine? am I correct?
I watched Steve Lasker's video, even though the private install covers managed code, but I thought I should be doing something very similar, apparently I'm missing something.
I'll appreciate any help.
P.S., The 7 dll's above I mentioned are:
sqlceca35.dll
sqlcecompact35.dll
sqlceer35EN.dll
sqlceme35.dll
sqlceoledb35.dll
sqlceqp35.dll
sqlcese35.dll
Hi Guys, I've got a bit stuck trying to decide on a table structure for a private message like part of a site I'm building (I'm using SqlServer 2000, not that it really matters).at first I was going to use a single table with the following schema: MessageId intReplyToMessageId int (nullable)FromUserId uniqueidentifier (I'm using .net membership)ToUserId uniqueidentifier IsRead bitIsDeleted bitSubject nchar(50)Body nvarchar(1000) DateCreated datetimeDateUpdated datetimeCreatedBy uniqueidentifierModifiedBy uniqueidentifier What strikes me here is that I'm using alot of guids (which are huge) and if I'm going to be threading the messages (which I am) there's no easy way to return an ordered list of messages that are replies to each other. So then I thought about introducting a messageThreads table, but I can't think of anything to store there apart from a threadid (which seems to defeat the purpose)Any ideas? I can clarify more if necessary. Thanks
View 6 Replies View RelatedSQL 2005 Standard x64 Service Pack 2
Windows 2003 R2 X64 service pack 2
The principle, partner and witnesss have two NICs each (NIC1 and NIC2). I want them to communicate in NIC2 for sending logs and establishing quorum. This will happen in their own private network (say 192.168.1.0/24). The NIC1 in each server will be available for client communication. The domain and clients are in the network (say 10.1.1.0/24).
I am using the same domain account as SQL server service account in all three servers.
How can I do this?
Thanks
I'm having a great deal of difficultyconnecting to SQL 2005 sp1 during the running of the deployment tool on the FE.. The error message indicates that I have to install the Backward compatible tool on the FE... Did that and upgraded SQL to SP1... Still a no-workie..Exactly what SQLs are compatible with the Public version of OCS... It worked fine with the Private version and SQL2005...
View 2 Replies View RelatedI've just bought a Virtual Private Server Account because I want to host a number of different ASP.NET sites, each backed by their own SQL database. I've downloaded SQLEXPRE.EXE and SQLServer2005_SSMEE-x64.msi to the server and installed the database engine and Management Studio Express on the VPS.
My problem is that I cannot access the SQLEXPRESS on my server from my local machine. The Management Studio Express cannot get logged in. I've used "Surface Area Configuration Manager" to allow "Local and remote connections" using TCP/IP only. I've used SQL Server Configuration Manager" to enable the TCP/IP protocol. After making these changes I've stopped and started the SQL server - and even rebooted the machine. I've created a login "Remote User" for this access. I've added sqlservr.exe as an exception to the firewall.
What else do I need to do? Is there a simpleton's guide to completing this task?
Hi, I am using express database and tools. I have an aspnetdb express database because I am using the login controls, personalisation...Can I use this database for my private tables also? Or do I need a separate express database for my private tables? Will using aspnetdb database, which contains special asp.net tables, for my private website tables create any kinds of problems in using my website? I want to use a single express database for my website, not two different express databases.
Regards, Sandy
We are planning to encrypt few fields using asymmetric encryption. Tyring share public key with users and retain private key with us. How to generate keys? Haven't found any solid document on how to generate these keys.
View 0 Replies View RelatedI am trying to create an assembly object when a report is initialized and I am getting the error:
[rsCompilerErrorInExpression] The BackgroundColor expression for the textbox 'textbox2' contains an error: [BC30390] 'ReportExprHostImpl.CustomCodeProxy.X' is not accessible in this context because it is 'Private'
In the Code tab, I have:
Dim X As namespace.classname
Protected Overrides Sub OnInit()
X = new namespace.classname()
End Sub
In my color field, I have "=code.X.color"
If I replace the color field with "=namespace.classname.color" and have color be a shared property, then it works. However, I ultimately need the color to be based on individual user settings, so I cannot use the shared property approach.
Any ideas on how to get around this "because it is private" error?
FYI, I am trying to replicate the concept of themes using report formatting information pulled from a database.
I am trying to convert a certificate that was exported from our database server to be used by SQL Server for database encryption. When I run the PVKConverter, not Private Key File (PVK) is generated.The certificate has Server and Client Authentication as the purposes of the certificate.
What purpose or purposes does the certificate need in order to be able to be used by SQL Server 2012 SP2?Why doesn't the PVKConverter generate a private key file?I can use the command makecerts to generate a self signed certificate and have it work with SQL Server database encryption.
Hi all,
I have some doubt about the IP Required for configuring cluster(both Window and SQL server)
Details :
----------
How Many IP(Public & Private) Required for 2 Node Active/Passive both window and SQL server Cluster ?
IP(Public & Private) Required :
---------------------------------------
1 : For Window Clustering : 2 node Active/Passive Cluster ? How many ip ?
2 : For Sql Server Clustering : 2 node Active/Passive Cluster ?how many ip ?
3 : Is there any mathematics for that ? How to analyse the IP requirement ?
Thanks
I'm trying to implement a SSAS Project in a Virtual Machine using a private network. When I try to deploy the solution the program gives me the following error:
I already have a Windows Authentication but still gives me that error.
Hi
I am looking at documentation of CREATE CERTIFICATE statement. I am having hard time in understanding if I want to create CERTIFICATE with above mentioned options, how I am supposed to create either PRIVATE KEY file or EXECUTABLE file. any example would be really helpful for what I am doing here.
thanks
Satya
I am sure I'm being dumb here but I am trying to deploy an assembly with external_access.
I have signed the assembly using the <new> option in the project properties.
When I then try and create the Key I get the above error using the code below.
CREATE ASYMMETRIC KEY SQLExtensionUDTKey
FROM EXECUTABLE FILE = 'C:Documents and SettingsSimon SabinMy DocumentsVisual Studio 2005ProjectsSQLBitsCoreSQLExtensionsSQLExtensions.UDTinDebugSQLExtensions.UDT.dll'
What could be the problem?
hey,
i have a problem, to import a self signed openssl certificate into the sql server 2005.
my final idea is to get encrypted columns from the database over an jdbc connection in a java client.
when i use a certificate generated by the sql server 2005, i can encryt columns of a table. then i catch the
the result in my java client. but in java, i need a keystore with the private key of the certificate.
ok. i have export the the certificate and the private key of the sql server 2005.
problem: in a keystore i can only import the certificate (signed public key) but not the private key.
my new idea is to import an openssl certificate or an certificate generated by the keytool (java) into the
sql server 2005 and encypt the data with the imported certificate. Problem: The SQL Server give me an
Exception: (Sorry i drag & drop the exception, is written in german)
Msg 15208, Level 16, State 1, Line 2
Die Datei für das Zertifikat, den asymmetrischen Schlüssel oder den privaten Schlüssel ist nicht vorhanden oder weist ein ungültiges Format auf.
My Import Statement is:
CREATE CERTIFICATE InsuranceCertOpenSSL
FROM
FILE = 'E:masterkeysinsuranceservice_tomcat_apr_x509_certificate_with_cygwin_openssl_20060630insuranceservice_tomcat_x509_certificate_with_openssl.crt'
WITH PRIVATE KEY (
FILE = 'E:masterkeysinsuranceservice_tomcat_apr_x509_certificate_with_cygwin_openssl_20060630insuranceservice_tomcat_x509_certificate_with_openssl_private.key',
DECRYPTION BY PASSWORD = 'testit2_',
ENCRYPTION BY PASSWORD = 'testit2_'
)
I use password encryption, and not the internal master key (or service master key)
Hope for help :)
nils
I'm green on this networking stuff so any help is greatly appreciated.
I have setup my home computer as a server and assigned a static IP to it. I installed SQL Server 2005 Developer and allowed remote connections to it through the Surface Area Config tool, added port 1433 TCP and 1434 UDP to firewall's exception list, and started the SQL Server Browser service.
Now I can't seem to remote connect to this SQL Server from my office network. Is there something I'm doing wrong here?
Thanks!!
Hello,I have a doubt. I've just bought a private server and now I can decide between having my applications under a private SQL Server Express database or under a Shared SQL Server database.The application is not professional, it's just a hobby, but it could have more than 100.000 entries and be used by 200 users at the same time.I know the limitations of SQL Server Express, but I want to know the opinion of someone who have experienced about this.Thanks!
View 4 Replies View RelatedHi,
A long time ago I posted this: http://blogs.conchango.com/jamiethomson/archive/2005/06/09/1583.aspx explaining all the different buffer types in the pipeline.
I have to admit I'm still not clear on the difference between a private buffer and a flat buffer though.
Are flat buffers a subset of private buffers.
If so, if a private buffer is not a flat buffer - what is it?
If not, can a buffer be a private buffer AND a flat buffer?
Some descriptions from BOL are:
Private buffers: A private buffer is a buffer that a transformation uses for temporary work only
Flat buffers: Flat buffers are blocks of memory that a component uses to store data
That sounds like two ways of saying the same thing to me! It certainly doesn't distinguish them anyway!
Just seeking some clarification here. If you could whip of a demo package that explains the difference between the two (with reference to the Performance Counters) then that would be great.
Thanks
Jamie
Trying to obtain a full list of SQL 6.5 error messages into a file. There are
2,215 messages present in master.sysmessages, but there is a total of 2,733
messages listed in the dialog box within enterprise manager. Message numbers
17026 thru 19020 don't seem to be stored in the sysmessages table.
Anyone know how I could obtain an extract to obtain a full list of messages ?
After sending a message the message is just gone.
Our transmissionand and out our transactionqueue is empty.
This is our CLIENT:
CREATE MESSAGE TYPE HelloWorldMessage
VALIDATION = WELL_FORMED_XML ;
GO
CREATE CONTRACT HelloWorldContract
( HelloWorldMessage SENT BY INITIATOR);
GO
CREATE QUEUE [dbo].[InitiatorQueue] ;
GO
CREATE SERVICE InitiatorService
ON QUEUE [dbo].[InitiatorQueue];
GO
drop endpoint SqlEndpoint
CREATE ENDPOINT SqlEndpoint
STATE = STARTED
AS TCP (LISTENER_PORT = 1235, LISTENER_IP =ALL)
FOR service_broker(AUTHENTICATION = WINDOWS)
GO
use master
GRANT CONNECT ON ENDPOINT:qlEndpoint to public
GO
use test1
DROP ROUTE SqlRoute
GO
CREATE ROUTE SqlRoute
AUTHORIZATION [dbo]
WITH
SERVICE_NAME = 'TargetService',
BROKER_INSTANCE = '80EFDE56-4088-4015-B6C0-D12285C60F66',
ADDRESS = 'TCP://localhost:5900';
GO
GRANT SEND ON SERVICE::[TargetService] to public
create master key
encryption by password = 'azertyuiopqsdfghjklm'
go
SERVER
CREATE MESSAGE TYPE HelloWorldMessage
VALIDATION = WELL_FORMED_XML ;
GO
CREATE CONTRACT HelloWorldContract
( HelloWorldMessage SENT BY INITIATOR);
GO
CREATE QUEUE [dbo].[InitiatorQueue] ;
GO
CREATE SERVICE InitiatorService
ON QUEUE [dbo].[InitiatorQueue];
GO
drop endpoint SqlEndpoint
CREATE ENDPOINT SqlEndpoint
STATE = STARTED
AS TCP (LISTENER_PORT = 1235, LISTENER_IP =ALL)
FOR service_broker(AUTHENTICATION = WINDOWS)
GO
use master
GRANT CONNECT ON ENDPOINT:qlEndpoint to public
GO
use test1
DROP ROUTE SqlRoute
GO
CREATE ROUTE SqlRoute
AUTHORIZATION [dbo]
WITH
SERVICE_NAME = 'TargetService',
BROKER_INSTANCE = '80EFDE56-4088-4015-B6C0-D12285C60F66',
ADDRESS = 'TCP://localhost:5900';
GO
GRANT SEND ON SERVICE::[TargetService] to public
create master key
encryption by password = 'azertyuiopqsdfghjklm'
go
Hopefull you have an idea?
I am writing a tracking system. There is a table in the Sql Server 2000 database that contains a column for the user's ntid, the page they visited, the date of the last visit, a column each to track hits for the current year and a previous year column (basically for archiveing and reporting purposes), and 12 columns for hits per month (obviously, one per column). To record a hit, my unit determined we would only track one hit per day, so basically, there are 3 possible outcomes I needed to account for :
1) A user had never hit the page before, so I need to record the user's ID, the page they hit for the first time (since it won't exist yet), increment the year counter for that user on that page, and then determine what month column counter should be incremented as well.
2) A user had hit the page before, but not on this same day, so I need to update the row for that user on that page, changing the last visit field to reflect the current date, and icnrementing the appropriate counters.
3) A user had hit the page already on the same day, so basically, nothing should be changed whatsoever. No action should be taken.
I wrote a stored procedure to attempt to accomplish that logic, and though it's probably not very pretty, I was surprised at how few errors I got on my first Syntax check. Here's the stored procedure :
CREATE PROCEDURE sp_hitMe@ntid varchar(10),@page varchar(50),@thisHit datetimeASSET NOCOUNT ON
DECLARE @tempDate datetimeDECLARE @yearCount intDECLARE @monthCount intDECLARE @inMonth varchar(20)DECLARE @monthColumn varchar(10)SET @inMonth = DATENAME(mm, @thisHit)SET @monthColumn = CASE WHEN @inMonth = 'January' THEN 'hitsInJan' WHEN @inMonth = 'February' THEN 'hitsInFeb' WHEN @inMonth = 'March' THEN 'hitsInMar' WHEN @inMonth = 'April' THEN 'hitsInApr' WHEN @inMonth = 'May' THEN 'hitsInMay' WHEN @inMonth = 'June' THEN 'hitsInJun' WHEN @inMonth = 'July' THEN 'hitsInJul' WHEN @inMonth = 'August' THEN 'hitsInAug' WHEN @inMonth = 'September' THEN 'hitsInSep' WHEN @inMonth = 'October' THEN 'hitsInOct' WHEN @inMonth = 'November' THEN 'hitsInNov' WHEN @inMonth = 'December' THEN 'hitsInDec' END DECLARE @insString varchar(500)DECLARE @updString varchar(500)SET @insString = 'INSERT INTO tblTracking (ntid, page, lastVisit, hitsThisYear, ' + @monthColumn + ') VALUES (' + @ntid + ', ' + @page + ', ' + @thisHit + ', 1, 1)'
if exists(select * from tblTracking where ntid = @ntid and @page = page) begin if exists(select * from tblTracking where lastVisit = @thisHit) begin -- DO NOTHING! end else begin DECLARE @theColumn varchar (100) SET @theColumn = 'SELECT ' + @monthColumn + ' FROM tblTracking WHERE ntid = @ntid AND @page = page' SET @yearCount = (SELECT hitsThisYear FROM tblTracking WHERE ntid = @ntid AND @page = page) + 1 SET @monthCount = (Exec @theColumn) SET @monthCount = @monthCount + 1 SET @updString = 'UPDATE tblTracking SET lastVisit = ' + @thisHit + ', hitsThisYear = ' + @yearCount + ', ' + @monthColumn + ' = ' + @monthCount + ' WHERE ntid = @ntid AND @page = page' Exec @updString end endelse begin Exec @insString endGO
And to my surprise, the only 3 errors I got were :
Server: Msg 156, Level 15, State 1, Procedure sp_hitMe, Line 39Incorrect syntax near the keyword 'end'.Server: Msg 156, Level 15, State 1, Procedure sp_hitMe, Line 45Incorrect syntax near the keyword 'Exec'.Server: Msg 156, Level 15, State 1, Procedure sp_hitMe, Line 50Incorrect syntax near the keyword 'end'.
However, these are of course so vague as to be useless to me. What's wrong with the procedure? What have I missed?
Is there a way to return the SQL Native error to a Visual Basic program?
View 3 Replies View Related