Binary Data On Multiple Servers

Feb 18, 2007

I'm using EncryptByKey to encrypt data in my SS2005 database. Since our server is really slow to access from home to work on, I used the Database Publishing Wizard and installed the db to work on at home. Then I created the certificate and symmetric key in my home db.

When I pull info using the DecryptByKey on our database at work on Windows 2003 Server, no problem, the data is decrypted. However, the same data does not decrypt at home on my Windows XP computer. I'm using TripleDes on both machines for the symmetric key (AES won't work on XP).

--To create my cert and key:

USE My_DB;
CREATE CERTIFICATE MyCert
ENCRYPTION BY PASSWORD = 'some password'
WITH SUBJECT = My Data',
START_DATE = '01/01/2007',
EXPIRY_DATE = '01/01/2099';
GO

CREATE SYMMETRIC KEY MyKey WITH ALGORITHM = TRIPLE_DES
ENCRYPTION BY CERTIFICATE MyCert;
GO


To encrypt:

OPEN SYMMETRIC KEY MyKey

DECRYPTION BY CERTIFICATE MyCert

WITH PASSWORD = 'same password as above';

Insert my record, use scope_identity to return primary key into @CustomerID.

INSERT INTO [Customers] (EncryptByKey(Key_GUID('MyKey'), @DataToEncrypt, 1, CONVERT( varbinary, @CustomerID)))

CLOSE SYMMETRIC KEY MyKey

To decrypt:

SELECT CONVERT(varchar(3925), DecryptByKey(EncryptedField, 1, CONVERT( varbinary, @CustomerID))) as PlainTextData

FROM Customers

WHERE (CustomerID= @CustomerID)

Everything works fine when I run the decrypt query on the database on our work server. But I'm not getting decrypted data at home. Is the symmetric key or certificate machine specific? If so, that will cause a huge problem when we deploy to a production server.

Thanks in advance for your help!

View 5 Replies


ADVERTISEMENT

Retrieving Data From Multiple SQL Servers In Parallel

Nov 9, 2001

I have multiple SQL 2000 servers that hold data which I retrieve using a single SQL stored procedure. Unfortunately, I don't know how to retrieve this data asychronously (in parallel).

For example, I retrieve data from two linked servers using the following:

select * into #temp from openquery(SomeLinkedServer,'exec BigQueryHere')
select * into #temp2 from openquery(OtherLinkedServer,'exec BigQueryThere')
.
.
lots of manipulation of the temporary tables to get what I want, etc.
.
.

The problem I have with this is that there is no reason why the first query should have to finish before the second query begins (serially) because these are on separate servers. Is there a way to execute these so that they run at the same time?

Bill

View 1 Replies View Related

Create View With Data From Multiple Servers

Jul 23, 2005

Hi everyone,I have 5 servers, all with identical databases just different data. Ihave a rather lengthy SQL statement (in a View) to hit one database andpull-in certain data, but I'd like to somehow run this same SQLstatement within the view but hit all 5 servers so we don't have 5different versions of this data to mess with.I'm not opposed to creating an update query in a stored procedure tohit all 5 databases and update a table or even do this within a DTS,but I'd prefer to keep it as simple as possible and as dynamic so theusers can simply run the view and get live data anytime based on all 5tables.Is this possible ???Thanks,rlangly

View 2 Replies View Related

Data Synchronization Accross Multiple Servers Running Replication

Apr 19, 2007

Hello,



We have an environment with 7 servers that are running replication with one another and I'm wondering if there are any tools or experiences that any of you might have that may assist in the auditing of these servers. The data should be in synch accross the boards for all tables, but sometimes problems can arise such as replication not being set up properly, stored procedure's being out of synch, or data gliches etc.



In dealing with these issues we have an in-house written program which analises each table on each server and takes a snapshot and does column by column compare. We also have another program that will synch the data up (basically a delete/insert statement on the publisher). This process can take up to 3 weeks for our quarterly update of every table. I'm wondering if anybody has used any tools such as in SSIS or a third party tool and has done or is doing something similar to what we are doing now. If so, are there any tips you wouldn't mind sharing on how our process might be sped up?





Thanks,

Phil

View 4 Replies View Related

T-SQL (SS2K8) :: Store Binary Data Rather Than Int Or Binary?

May 7, 2015

I'm using a bit-wise comparison to effectively store multiple values in one column. However once the number of values increases it starts to become too big for a int data type.you also cannot perform a bitwise & on two binary datatypes. Is there a better way to store the binary data rather than int or binary?

View 9 Replies View Related

Data Driven Subscriptions - Triggers Multiple Times On Prod Servers?

Mar 7, 2008



Hey!

DDS triggers 3 - 4 times on Report Servers with 15 mints apart..any ideas?

View 3 Replies View Related

Reporting Services :: Report Builder 3 Using Data Sources From Multiple Servers?

Sep 5, 2013

I have a report designed in RB3 that uses a data source from a SQL database that is on the report server. I want to add data to the report from an access database an a network drive.

I can add the second data source and create a data set to add data to the report. The dataset query returns data from the Access database but when I run the report I get the following error.

An error has occurred during report processing. (rsProcessingAborted)

Cannot create a connection to data source 'Feasibility'. (rsError OpeningConnection)

Also when I test the connection to the Access data base I get error ERROR (IM002) (Microsoft)(ODBC Driver Manger) Data source name not found and no default driver specified.

I noticed that Report Builder is connecting to the report server. If I disconnect from the report server I can a can connect to the Access data base but not the SQL database.

How can I get the report to run against both data sources?

View 2 Replies View Related

Creating A Database From Multiple Databases Accross Multiple Servers

Sep 13, 2007

Hi,

I'm trying to create a database that takes specific information from a number of databases on different servers to make some reporting that we have much easier.

I'm pretty new to SQL so I'm not sure of the best way to proceed. I read an article that suggested I use the OPENROWSET command. The problem is, the version of SQL that came with one of the programmes we use is limited and will not allow you to turn on the allow "Ad Hoc distributed Queries" so the SLQ statement will not execute.

I'm confused why it won't let me to connect through ODBC as I've created a web page that selects data from this database with no problems!

Here is the SQL statement that I've written to make sure it is the correct one (on the msdn library page it said that this was the ODBC connection):

SELECT a.*
FROM OPENROWSET('MSDASQL','DRIVER=(SQL Server);SERVER=APPOLOACT7;UID=sa;PWD=***************',
'SELECT * FROM MDCTestAndDev.dbo.TBL_CONTACT') AS a



I've also created the ODBC connection using the tool on Administration Tools>Data Sources ODBC

Any help would be greatly appreciated (also any ways of selecting from one database and inserting it into another will be helpfull)

Thanks

View 8 Replies View Related

How Can I Process Multiple Binary Files Into My Sql Database?

Aug 1, 2006

i have a table with rows of file names and paths. what i'm trying to do is process each file and store it in my sql database. i want to store the files as binary files (they are word and excel and pdf files) anyone know a way to do this? it would especially be useful if i could do this with a console application so i can schedule it

View 2 Replies View Related

Ntext Over 4000 Chars Causes 'Data In Row (n) Was Not Update... String Or Binary Data Would Be Truncated...'

Oct 18, 2006

When I enter over 4000 chars in any ntext field in my SQL Server 2005 database (directly in the database and through the application) I get an error saying that the data could not be updated because string or binary data would be truncated.Has anyone ever seen this? I cannot figure out what is causing it, ntext should be able to hold a lot more data that this...

View 7 Replies View Related

String Or Binary Data Would Be Truncated. I Get This Error When Entering Data Using Sql Server Management Studio Express.

May 21, 2008

String or binary data would be truncated. I get this error when entering data using sql server management studio express.

I am not running a sql to insert or update the table. This is through the EDI.

The data type is varchar(100). I enter one character and it errors on me. So this isn't a string being too long problem.

Any ideas?



View 2 Replies View Related

SqlServer 2005 String Or Binary Data Would Be Truncated When Data Is OK

Feb 21, 2006

When using AquaData or JDBC (inet tds driver), when doing an insert using SqlServer 2005, I get error "String or binary data would be truncated" when the data is actually OK. There are no triggers, etc. that would confuse the situation. It works fine in SqlServer 2000.

The scenario is as follows:

Create table:
create table test3 (
name varchar (18) ,
tbname varchar (18)
)

Create and populate table:
create table maxtable (
tablename varchar (18) not null,
[...]
)

Try to insert into test3:
insert into test3 (name, tbname)
select i.name, o.name
from dbo.sysindexes i, sysobjects o, maxtable m
where i.indid > 0 and i.indid < 255
and i.id = o.id and i.indid = 1
and o.name = lower(m.tablename)

And I get the error "String or binary data would be truncated." The values being selected for i.name and o.name have maximum length of 18. There are other rows in sysindexes and sysobjects with longer values, but they are not being selected.

The error does not occur with SQL Server Management Studio, and does not occur using SqlServer 2000.

View 6 Replies View Related

Multiple Servers

Nov 15, 2007

Hi All,I have recently published a website to our webserver and i get a sql error. We have a webserver that does not have sqlserver on it and and our database server which does. i have used the configuration utility to to setup my users and roles which created the ASPNETDB in my local App_Data folder. Is there a way to copy this database to our database server and change the references so the site refers to the new instance on the database server as apposed to the local instance when a user logs in?ThanksBryan 

View 3 Replies View Related

Way To Run Same Db Across Multiple Servers?

Mar 2, 2015

What's the easiest way to run the same db across multiple servers?

View 4 Replies View Related

Concatenate All Binary Columns Into Single Binary Column?

May 22, 2014

Server is SQL 2000

I have a table with 10 rows with a varbinary column

I wish to concatenate all the binary column into a single binary column and then write that to another table within the database. This application splits a binary file (Word or PDF document) into multiple segments (this is Column2 as below)

example as follows

TableA

Column1 Column2 Column3
aaa 001 <some binary value>
aaa 002 <some binary value>
aaa 003 <some binary value>
aaa 004 <some binary value>
aaa 005 <some binary value>

desired results in TableB

Column1 Column2
aaa <concatenated value of above binary columns>

View 9 Replies View Related

Query From Multiple Servers

Jun 3, 2008

hi
Is there a way to have a select query on multiple servers ? (2 databases , each one located on a sql server)

View 2 Replies View Related

Replication To Multiple Servers

Jul 12, 2000

suppose i am repliacting(Transactional Replication) to two servers using the same publications can i stop replicating to one server without disturbing the other server?
If i resume the replication to the server after two days what will happen to the two day's data?will they be replicated when i resume the replication?
Thank you

View 2 Replies View Related

SQL Security - Multiple SQL Servers

Feb 6, 2001

Question:

How to prevent a user/admin on one SQL 7 server, who knows the system admin password that is common to both servers, from accessing a second SQL 7 server via the SQL Server Group setup process.

Is there a way from shutting off remote access to a SQL server without limiting RPC?

All reading points towards 1) Remote Access Options, 2) Store User Independent, 3) Control Store as options to locking down a server. Would like to hear from someone that ran into this type of issue. Third party software available?

I've tried the following without any success: 1) I have deleted the second SQL server registration from the first SQL server group - but this is too easy to delete and recreate, 2) edit the SQL server registration for the remote server and force authentication - almost there if SQL prevented deletion of the registration without verifying the logon/password of the person who created it (again able to delete and recreate), 3) played around with the client utility to remove the TCP/IP pipe entry.

Anyone else pulling their hair out on this?

TIA!

View 1 Replies View Related

Multiple SQL Servers On Same Network

Nov 12, 1999

Please excuse my niavety on this subject, But I have a simple question. When you have more than one SQl server 7 installation on the same network, each using different machines, does one automatically have the ability/authority to be able to stop the other server at will??

Thanks in advance for any help

Simon

View 2 Replies View Related

Multiple SQL 2005 Servers

Mar 12, 2007

Joe writes "I am trying to find whether I can have multiple SQL 2005 servers but having same security profiles and maintain them grom one place only. I prefer not to have windows authentication.


Thanks a lot"

View 1 Replies View Related

Quering Multiple Servers

Apr 13, 2007

I'm sort of new at MS SQL Server - I'm trying to gather system table information from multiple servers about backups on our site and insert the rows the query returns into a table on one central server where I'm running the stored procedure from. I have the server names stored into a table but when I attempt to connect to servers, I get an error which tells me that I need to create a linked server by using sp_addlinkedserver stored procedure. People that I work with tell me they want to do this without creating linked servers - Is there a way to accomplish? When I run the query, it only retrieves data from the local server I'm running the stored procedure on, but like I mentioned, when I attempt to connect to other servers, I get the error message that it couldn't find the server name in "syservers" and that I need to run sp_addlinkedserver. This also happens if I run the same query in OSQL from Windows command line.

Any help on this would certainly be appreciated.

Thanks in advance.

View 2 Replies View Related

Acces Multiple Servers

Nov 15, 2006

Hi,

I need to access a table from another server in my procedure...I am now connected to say SERVERA...I need to access another table SERVERB.TABLE2 in the same procedure...

Is there a way to do that...

I need to connect to two servers from the same procedure to get data..

Please get back

Thanks,

View 3 Replies View Related

Querying Multiple DB Servers

Nov 27, 2007

Hi,

I want to perform a join on two tables from two separate database servers.




Code Block
SELECT *
FROM tbl1 AS t1
INNER JOIN
"SERVERASQL2000".Production.tbl2 AS t2
ON t2.UserID = t1.UserID






Why do I get this error...

Invalid object name 'SERVERASQL2000.Production.tbl2'

thanks.

View 10 Replies View Related

Manage SQL Servers On Multiple Domains

Aug 24, 2000

I have to maintain several SQL Server 7 DBs across multiple NT Domains (same network). How do I access the SQL Server on the second domain from enterprise manager?

Thanks!

Lia

View 1 Replies View Related

Copying Sprocs, DTS To Multiple DBs And Servers

May 3, 2007

We have a growing number of servers and databases on each server that all share the same (sub)set of sprocs and UDFs. DTS packages, which we use for data import, frequently need to be copied between the servers. What is the best way to maintain this? Ideally, I would like to be able to click a button and have a script creating or altering one or more sprocs automatically run aginst all DBs on all servers. Likewise, I'd like to be able to copy DTS packages to all servers.

We use SS2000 SP4 and plan to migrate to SS2005. We also use ASP.net 2.0 and VS 2005 SP1.

View 4 Replies View Related

Running Multiple Db Servers For One Db (import/exp

Feb 29, 2008

Hi,

I have zero experience running any databases that spread further than 1 machine, so I have a few theory questions here that hopefully someone can help with. Hopefully this is the right forum, I'm not sure if it classifies as 'clustering'.

Anyways, we are launching a web app that is going to start with just 1 webserver/db server. For speed reasons, after some growth we might have to have a load balanced setup with a webserver in europe and one in north america. Basically the webservers are going to be serving 100,000's of files and each time a file is served it needs to be recorded in the database.

I think that if I'm connecting my european webserver across the internet to my db server, thats killing the purpose of having a webserver in europe to make for faster responses.

I am thinking that this european web server/db serving is only going to be logging the files served. Is there a way to import them into north american database everynight ?

I'm not sure what the best approach would be for something like this, but any suggestions are greatly appreciated.


Thanks very much,
mike123

View 2 Replies View Related

List Of Databases From Multiple Servers Without SMO

Nov 14, 2007



Is it possible to get an aggregate list of databases from multiple servers, without using SMO?

All target servers are running SQL Server 2005.

Ben Aminnia

View 2 Replies View Related

Licensing Multiple Passive Servers

Oct 17, 2007



Hi, I was wondering what the licensing requirement would be for following scenario:

1 active sql server installation installed on a windows failover cluster.

1 passive mirror sql server (single server).

1 passive log-shipped sql server (single server).

In the microsoft white paper it says:

When doing failover support, a server is designated as the passive server. The purpose of the passive server is to absorb the data and information held in another server that fails. A passive server does not need a license, provided that the number of processors in the passive server is equal or less than those of the active server. The passive server can take the duties of the active server for 30 days. Afterward, it must be licensed accordingly.

So do I need to buy an enterprise license for each of the additional passive servers...in the above example meaning I would need 3 licenses in total?

Thanks,
Paul

View 3 Replies View Related

Accessing One Database From Multiple SQL Servers

Dec 1, 2006

Apologies if this question has been asked before. I am about to upgrade a multiuser application based on Access97 and VB6 to C# and possibly SQL Server Express.

My customer does not want to use a server based database solution ie a single instance of SQL Server, but prefers to have a common networked dataset which is accessed via user's own individual database engines. This is because of restrictions on the installation of server based apps.

Does this scenario completely rule out the use of SQL Server Express?

Thanks

View 3 Replies View Related

Sending The Same Message To Multiple Servers

Jul 19, 2007

Can anyone outline some best practices for sending the same message out to multiple servers? What is the easiest way to do this?

Currently, I have a message sent from Server A to Server B, which is fired by a table insert, update, delete trigger.

The goal is to keep this table synchronized on many remote servers. It sounds like a tedious exercise to deploy the SSB messages, queues, services, etc to 20+ more sites! I am hoping there is a shortcut of some kind.

Any advice is appreciated.

View 7 Replies View Related

Distributed Partitions (Across Multiple Servers)

Jul 4, 2007

I have an existing table that I want to partition across multiple servers. I am having a hard time finding an article that explains how to do it.



I am in the process of designing applying a scale out architecture to our database...but have hit this brick wall.



Any help would be great.



Thanks!



Eric Elliston

SWFLParent.com

http://www.swflparent.com

View 1 Replies View Related

Binary Data

Dec 28, 2007

Hello,
I have a table which uses binary data to store passwords.  How do I view the contents of the "binary data" column, ie. the passwords?  It just shows it as <binary data>?

View 5 Replies View Related

Binary Data

Feb 8, 2001

We have a dll that sends a hexadecimal data (const. length) to MS SQL Server database. It's declared as String in VB, the db column data type is binary.

Here is the SQL String that has been executed successfully in Query Analyzer:

"declare @MyHAX varchar(32)
select @MyHAX='0x3236374535454337363145313430463742394545 413443473230343544320000'
insert MyTABLE (MyCOLUMN)
values (convert (binary(32),@MyHAX))"

When I am trying to do the same thing in the insert stored procedure, I get an error message: "Disallowed implicit conversion from data type varchar to data type binary, table 'MyDB.dbo.MyTABLE', column 'MyCOLUMN'. Use the convert function to run this query."

Does anyone know how can I insert my binary data?

View 5 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved