database errors: databases

Showing posts with label databases. Show all posts

Thursday, March 22, 2012

Crystal reports problem

I also posted this to the CR newsgroup, but I think it might be an MSDE
problem also.
We recently moved several Access databases to an instance of MSDE. We use
VB .Net for program development. Upon running one of the programs a dialog
box now pops up alerting us to enter a user name and password. I searched
the Business Solutions help and found that the database server needs to be
set up with a trusted connection. I thought I already did this through the
data connection of the VS IDE.
Is there something more than I have to do on the MSDE side?
Thanks for any useful information.
Brad
hi Brad,
"Brad" <ballison@.ukcdogs.com> ha scritto nel messaggio
news:eRE7fiSkEHA.3612@.TK2MSFTNGP12.phx.gbl...
> I also posted this to the CR newsgroup, but I think it might be an MSDE
> problem also.
> We recently moved several Access databases to an instance of MSDE. We
use
> VB .Net for program development. Upon running one of the programs a
dialog
> box now pops up alerting us to enter a user name and password. I searched
> the Business Solutions help and found that the database server needs to be
> set up with a trusted connection. I thought I already did this through
the
> data connection of the VS IDE.
> Is there something more than I have to do on the MSDE side?
> Thanks for any useful information.
MSDE is usually installed allowing *only* trusted connections, and you have
to force the parameter SECURITYMODE=SQL at install time in order to allow
SQL Server authenticated connection..
chek your registry at
HKLM\SOFTWARE\Microsoft\MSSQLServer\MSSQLServer
or
HKLM\SOFTWARE\Microsoft\Microsoft SQL Server\InstanceName\MSSQLServer
at the
LoginMode key
a value of 2 will allow both, where 1 only allows trusted connections
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.9.1 - DbaMgr ver 0.55.1
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||Andrea,
Thank you for the response, however in researching this problem I did see a
knowleddge base article about changing the key to 2 which I already did and
I still am having the same problem.
Thanks,
Brad
"Andrea Montanari" <andrea.sqlDMO@.virgilio.it> wrote in message
news:2prjfcFo4pukU1@.uni-berlin.de...
> hi Brad,
> "Brad" <ballison@.ukcdogs.com> ha scritto nel messaggio
> news:eRE7fiSkEHA.3612@.TK2MSFTNGP12.phx.gbl...
> use
> dialog
> the
> MSDE is usually installed allowing *only* trusted connections, and you
> have
> to force the parameter SECURITYMODE=SQL at install time in order to allow
> SQL Server authenticated connection..
> chek your registry at
> HKLM\SOFTWARE\Microsoft\MSSQLServer\MSSQLServer
> or
> HKLM\SOFTWARE\Microsoft\Microsoft SQL Server\InstanceName\MSSQLServer
> at the
> LoginMode key
> a value of 2 will allow both, where 1 only allows trusted connections
> --
> Andrea Montanari (Microsoft MVP - SQL Server)
> http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
> DbaMgr2k ver 0.9.1 - DbaMgr ver 0.55.1
> (my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
> interface)
> -- remove DMO to reply
>
sql

Friday, February 24, 2012

CROSSTAB QUERY FROM ACCESS TO SQL

Hello,
I'm quite basically new to working with SQL databases, coming from a
Microsoft Access background. As we've got the SQL server sitting here,
I've been asked to get the MS Access db's moved across onto it. I've
had no problem with the tables, but as the majority of the reporting
comes from cross tab queries I'm having trouble getting started with
the design of a cross tab/pivot view. I've tried all the examples the
search on groups provided but I'm having a hard time making it work for
me.
I'm reporting off one table tblMONTHCOSTS, the data at the moment is
displayed as
txtMOBILE NO txtBILLMONTH curCALLCOST
078******** 200501 15.25
077******* 200501 12.54
078******** 200502 9.54
077******* 200502 10.55
And so on...... For 12 months, for 39 mobile phones.
What I want it to do is display like this :
txtMOBILE NO 200501 200502 200503......
078******** 15.25 9.54
077******* 12.54 10.55
Does anyone have any advice on how this can be done? I know I'm
pretty new to this so if you could even just point me in the direction
of a good book I would be very grateful.
ThanksRefer this
http://weblogs.sqlteam.com/jeffs/archive/2005/05.aspx
Madhivanan

Cross-server SELECT query

Hi,

Recently the powers-that-be migrated the largest databases from one server to another, more powerful server while keeping some support data on the original server. My problem: I need to run queries on tables spanning both database servers. Unfortunately, I can't find any documentation on how to do this. Does anyone have any ideas?

Thanks!lookup linked servers and sp_addlinkedserver.

Sunday, February 19, 2012

cross-database relationship

How can I create a relationship between tables from two
different databases in SQL 2000?
MadhuriThis is not possible with declarative referential integrity (foreign keys).
You need to use triggers to enforce the relationship.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Madhuri Mittal" <madhurimittal@.yahoo.com> wrote in message
news:03c001c3d709$9c920a80$a401280a@.phx.gbl...
> How can I create a relationship between tables from two
> different databases in SQL 2000?
> Madhuri

cross-database relationship

How can I create a relationship between tables from two
different databases in SQL 2000?
MadhuriThis is not possible with declarative referential integrity (foreign keys).
You need to use triggers to enforce the relationship.
Hope this helps.
Dan Guzman
SQL Server MVP
"Madhuri Mittal" <madhurimittal@.yahoo.com> wrote in message
news:03c001c3d709$9c920a80$a401280a@.phx.gbl...

quote:

> How can I create a relationship between tables from two
> different databases in SQL 2000?
> Madhuri

|||Dan,
Thanks a lot- could you point me to some good documentation and books
that explain how to create such triggers? Thanks again
Madhuri
*** Sent via Developersdex http://www.examnotes.net ***
Don't just participate in USENET...get rewarded for it!|||Here's a link to a list of many of the more popular SQL Server books.
http://www.sql-server-performance.com/reviews.asp
Itzik Ben-Gan and Thomas Moreau have some examples of cascading RI triggers
in their white paper:
<http://www.msdn.microsoft.com/libra...ry/en-us/dnsql2
k/html/sql_refintegrity.asp?frame=true>.
Hope this helps.
Dan Guzman
SQL Server MVP
"Madhuri Mittal" <madhurimittal@.yahoo.com> wrote in message
news:uxTyEB51DHA.4064@.tk2msftngp13.phx.gbl...

quote:

> Dan,
> Thanks a lot- could you point me to some good documentation and books
> that explain how to create such triggers? Thanks again
> Madhuri
>
> *** Sent via Developersdex http://www.examnotes.net ***
> Don't just participate in USENET...get rewarded for it!
>

Cross-Database Ownership Chaining

I have two databases A and B. Need to see some data on B.
I know I can use Cross-Database Ownership Chaining in order to get
access on B. Are there any alternatives?
In other database systems I did this by setting up an Alias to
database B and specifing the account to use for the connection to B.
thanks,Hi,
Thanks for your post.
To let me better understand this issue, I would like to confirm a
conception from you:
Is the Cross-database Ownership Chaining as you referenced in the form of
"server.database.schema.table"?
SQL Server doesn't support custom database alias to reference the original.
If you use linked server, you can use sp_addlinkedserver to add a linked
server to the current SQL Server and specify the login account in the
remote server.
In addition to the chaining expression "server.database.schema.table", you
can use OPENQUERY or OPENROWSET to access the remote server.
For the usage of OPENQUERY and OPENROWSET, you may refer to SQL Server Book
Online.
If you have any other questions or concerns, please feel free to let me
know. It's my pleasure to be of assistance.
++++++++++++++++++++++++++++++++
Charles Wang
Microsoft Online Partner Support
++++++++++++++++++++++++++++++++
PLEASE NOTE: The partner managed newsgroups are provided
to assist with break/fix issues and simple how to questions.
We also love to hear your product feedback!
Let us know what you think by posting
- from the web interface: Partner Feedback
- from your newsreader:
microsoft.private.directaccess.partnerfeedback.
We look forward to hearing from you!
======================================================When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
======================================================This posting is provided "AS IS" with no warranties, a
nd confers no rights.
======================================================

Cross-Database Ownership Chaining

I have two databases A and B. Need to see some data on B.
I know I can use Cross-Database Ownership Chaining in order to get
access on B. Are there any alternatives?
In other database systems I did this by setting up an Alias to
database B and specifing the account to use for the connection to B.
thanks,Hi,
Thanks for your post.
To let me better understand this issue, I would like to confirm a
conception from you:
Is the Cross-database Ownership Chaining as you referenced in the form of
"server.database.schema.table"?
SQL Server doesn't support custom database alias to reference the original.
If you use linked server, you can use sp_addlinkedserver to add a linked
server to the current SQL Server and specify the login account in the
remote server.
In addition to the chaining expression "server.database.schema.table", you
can use OPENQUERY or OPENROWSET to access the remote server.
For the usage of OPENQUERY and OPENROWSET, you may refer to SQL Server Book
Online.
If you have any other questions or concerns, please feel free to let me
know. It's my pleasure to be of assistance.
++++++++++++++++++++++++++++++++
Charles Wang
Microsoft Online Partner Support
++++++++++++++++++++++++++++++++
PLEASE NOTE: The partner managed newsgroups are provided
to assist with break/fix issues and simple how to questions.
We also love to hear your product feedback!
Let us know what you think by posting
- from the web interface: Partner Feedback
- from your newsreader:
microsoft.private.directaccess.partnerfeedback.
We look forward to hearing from you!
========================================
==============
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
========================================
==============
This posting is provided "AS IS" with no warranties, a
nd confers no rights.
========================================
==============

Thursday, February 16, 2012

Cross referncing databases

Does Referencing databases in queries make queries any
slower
For ex
use DB2
go
select col1 from DB1..Table1
will above be any slower than
use DB1
go
select col1 from DB1..Table1
sanjayI don't think it would make much difference in speed.. But having cross
database references does introduce a level of complexity that you might not
otherwise have... Since each database can be restored independently of the
others, they could get 'out of sync' ... leaving you with big referential
problems... So , I *try* to keep all references local whenever possible... I
have seen some major applications which have 20-30 databases for a single
app and tons of cross database references, these companies are successful in
their products, but it makes my skin crawl...
--
Wayne Snyder MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
(Please respond only to the newsgroups.)
I support the Professional Association for SQL Server
(www.sqlpass.org)
"Sanjay" <sanjayg@.hotmail.com> wrote in message
news:9a6b01c34636$605f4320$a401280a@.phx.gbl...
> Does Referencing databases in queries make queries any
> slower
> For ex
> use DB2
> go
> select col1 from DB1..Table1
> will above be any slower than
> use DB1
> go
> select col1 from DB1..Table1
> sanjay
>
>

Tuesday, February 14, 2012

Cross database integrity

Is it possible to create foreign key between to distinct SQL databases ?
Database A contains table [customers] (primary key : customer_id)
Database B contains table [invoices] (each invoice should be linked to
a customer. Foreign key : customer_id)
I would like to create an integrity constraint between
[invoices].[customer_id] and [customers].[customer_id], despite the fact
that [invoices] et [customers] tables are in SEPARATE databases.
Is it possible to do that ?
Thanks in advance
Tom
The short answer is no. The more complex answer is: Yes, you would have to
enforce cross database constraint integrity using a Trigger. There are
security and other issues that will also have to be addressed.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Tom McLeesh" <tom.mcleesh@.gmail.com> wrote in message
news:u7%23a7148GHA.4572@.TK2MSFTNGP02.phx.gbl...
> Is it possible to create foreign key between to distinct SQL databases ?
> Database A contains table [customers] (primary key : customer_id)
> Database B contains table [invoices] (each invoice should be linked to a
> customer. Foreign key : customer_id)
> I would like to create an integrity constraint between
> [invoices].[customer_id] and [customers].[customer_id], despite the fact
> that [invoices] et [customers] tables are in SEPARATE databases.
> Is it possible to do that ?
> Thanks in advance
> Tom
|||Often, the quality of the responses received is related to our ability to
'bounce' ideas off of each other. In the future, to make it easier for us to
give you ideas, and to prevent folks from wasting time on already answered
questions, please:
Don't post to multiple newsgroups. Choose the one that best fits your
question and post there. Only post to another newsgroup if you get no answer
in a day or two (or if you accidentally posted to the wrong newsgroup -and
you indicate that you've already posted elsewhere).
If you really think that a question belongs into more than one newsgroup,
then use your newsreader's capability of multi-posting, i.e., posting one
occurrence of a message into several newsgroups at once. If you multi-post
appropriately, answers 'should' appear in all the newsgroups. Folks
responding in different newsgroups will see responses from each other, even
if the responses were posted in a different newsgroup.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Tom McLeesh" <tom.mcleesh@.gmail.com> wrote in message
news:u7%23a7148GHA.4572@.TK2MSFTNGP02.phx.gbl...
> Is it possible to create foreign key between to distinct SQL databases ?
> Database A contains table [customers] (primary key : customer_id)
> Database B contains table [invoices] (each invoice should be linked to a
> customer. Foreign key : customer_id)
> I would like to create an integrity constraint between
> [invoices].[customer_id] and [customers].[customer_id], despite the fact
> that [invoices] et [customers] tables are in SEPARATE databases.
> Is it possible to do that ?
> Thanks in advance
> Tom

Cross database integrity

Cross Database Activation Security

I have two databases Basket_ODS and Intelligence_ODS.

I created a user in the Basket_ODS and Intelligence_ODS databases as follows:

USE Basket_ods

CREATE MASTER KEY ENCRYPTION BY PASSWORD = '*******'

CREATE USER BasketServiceUser WITHOUT LOGIN

ALTER AUTHORIZATION ON SERVICE::[Order Send] TO BasketServiceUser

GRANT CONTROL ON SERVICE::[Order Send]

TO BasketServiceUser

CREATE CERTIFICATE BasketServiceCertPriv

AUTHORIZATION BasketServiceUser

WITH SUBJECT = 'ForBasketService'

BACKUP CERTIFICATE BasketServiceCertPriv

TO FILE = 'BasketServiceCertPub'

In the other database...

I created the following:

USE Intelligence_ODS

CREATE MASTER KEY ENCRYPTION BY PASSWORD = '************

USE Intelligence_ODS

CREATE USER BasketServiceUser WITHOUT LOGIN

CREATE CERTIFICATE BasketServiceCertPub

AUTHORIZATION BasketServiceUser

FROM FILE = 'BasketServiceCertPub'

My Queue is in BASKET_ODS and is set up as:

ALTER QUEUE ODS.[Order Process Queue] WITH

ACTIVATION (

STATUS = ON,

PROCEDURE_NAME = ODS.ProcessOrderQueue,

MAX_QUEUE_READERS = 4,

EXECUTE AS 'BasketServiceUser'

)

I have performed the following grants in Basket_ODS

grant execute on ODS.ProcessOrderQueue to BasketServiceUser

ProcessOrderQueue calls [ODS].[MoveOrderTotals_Core] in the Intelligence_ODS database.

grant execute on [ODS].[MoveOrderTotals_Core] to BasketServiceUser

ProcessOrderQueue proc is set as follows:

ALTER procedure [ODS].[ProcessOrderQueue]

WITH EXECUTE AS 'BasketServiceUser'

[ODS].[MoveOrderTotals_Core] is set up as follows:

when I run ProcessOrderQueue I get an error message:

ALTER procedure [ODS].[MoveOrderTotals_Core](@.Orderid uniqueidentifier)

with execute as 'BasketServiceUser'

I just don't understand when I run ProcessOrderQueue I get the following error message (when database trust is turned off)

The server principal "sa" is not able to access the database "Intelligence_ODS" under the current security context.

Can you help me figure out what I'm doing wrong. I've spent so much time on this security stuff. Is there another way to do this that is more straight forward without using database trust?

Something key that I left out...

ProcessOrderQueue calls [Basket_ODS].[ODS].GenericProcessor which is defined as follows:

ALTER Procedure [ODS].[GenericProcessor](@.procName nvarchar(128), @.orderID uniqueidentifier)

WITH EXECUTE AS 'BasketServiceUser'

DECLARE @.sql nvarchar(4000)

Select @.sql = 'exec ' + @.procName + ' @.orderID'

print @.sql

EXEC sp_executesql @.sql,

-- PARAMETER DECLARATIONS

N'@.orderID uniqueidentifier',

-- PARAMETER VALUES

@.orderID

This procedure is called based on the message read from the queue by ProcessOrderQueue. It then looks up what stored procedure needs to be called as per the message and then calls GenericProcessor with the name of the stored procedure to call along with the associated OrderID. When I changed this proc from 'Execute as OWNER' to Execute as 'BasketServiceUser' I now get the following error message :

The server principal "S-1-9-3-3317550566-1317506277-4110005931-648375857." is not able to access the database "Intelligence_ODS" under the current security context.

The stored procedure [Intelligence_ODS].[ODS].[MoveOrderTotals_Core] does an Insert into [Intelligence_ODS].... Select from [Basket_ODS]. That is the cross database portion.

|||

In order to get the cross database EXECUTE AS context to work, the key ingredient is the signing of the procedure. I see this is missing from you example. In code signing, is really irelevant who owns the certificate in [Basket_ods]. The important thing is for this certificate to sign the procedure that attempts to call another procedure cross database (I think is the [ODS].[ProcessOrderQueue]):

ADD SIGNATURE TO OBJECT::[ODS].[ProcessOrderQueue] BY CERTIFICATE [BasketServiceCertPriv]

Note that once the signature is added, the procedure should not be altered in any way, or the signature will be lost.

In database Intelligence_ODS you have to create a user derived from the [BasketServiceCertPub] certificate and grant him the appropiate permissions, including AUTHENTICATE:

CREATE USER [BasketServiceCertPubUser] FROM CERTIFICATE [BasketServiceCertPub];

GRANT AUTHENTICATE TO [BasketServiceCertPubUser];

GRANT EXECUTE ON [MoveOrderTotals_Core] TO [BasketServiceCertPubUser];

This should be it. The EXECUTE AS clauses on the queue activation, as well as on the [ProcessOrderQueue] procedure, should be probably OWNER. The procedure [MoveOrderTotals_Core] does not need an EXECUTE AS.

I think you are mixing some concepts related to the process of setting up dialog security (users w/o login, certificate ownership) with the concepts related to EXECUTE AS and code signing procedures. The two concepts are very distinct. The EXECUTE AS relies on signing the procedures and users derived from certificates to achieve it's goals.

HTH,
~ Remus

CROSS APPLY doesn't work in some databases

I've run across a weird error trying to use CROSS APPLY. Running SQL2005SP2 developer edition on XP2 (but same error occurs on Enterprise running on W2K3).

A simple CROSS APPLY always works when run from master, but doesn't work when run from most (but not all) of my other user databases. I can't figure out what could be causing the error, or is there some reason it would only work when the current database is master?

Here is the simplest testcase I can come up with:
select a.spid,b.text from master..sysprocesses a cross apply ::fn_get_sql(a.sql_handle) b

If this is run while the current database is master, it returns spid and SQL without a problem. If it's run in the other SQL-delivered databases (model, msdb, tempb), it also works fine. However, if it's run in all but one of the user databases, I get back an error:
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'a'.

I'm stumped on how to troubleshoot this. There is no difference in the query; the only difference is the current database. The databases are all owned by sa. I get the same results with sys.sysprocesses instead of master..sysprocesses.

As another datapoint, the employee/department example in the BOL for CROSS APPLY exhibits the same behavior; it works fine if run with master as the current database (regardless of where the tables are created), it fails with
Msg 102, Level 15, State 1, Line 3
Incorrect syntax near '.'.
if run from one of the other user databases.

Any ideas on what could be wrong?

Thanks,

Vince
Sorry to reply in my own thread, but I found the problem. I took drastic measures and created a new, empty database, then deleted all objects out of one of the ones that didn't work. The new one worked, the old one didn't.

Then I started looking at every dialog in SMSS for each database. The difference (which everyone else has probably figured out already) was the compatability level. The old databases were created with 80, the new ones had 90.

My apologies for the noise.
|||Actually, it wasn't necessary to drop these databases - there is a special stored proc to change their compatibility level:

exec sp_dbcmptlevel MyOldDB, 90

Thursday, March 22, 2012

Crystal reports problem

Friday, February 24, 2012

CROSSTAB QUERY FROM ACCESS TO SQL

Cross-server SELECT query

Sunday, February 19, 2012

cross-database relationship

cross-database relationship

Cross-Database Ownership Chaining

Cross-Database Ownership Chaining

Thursday, February 16, 2012

Cross referncing databases

Tuesday, February 14, 2012

Cross database integrity

Cross database integrity

Cross database integrity

Cross database integrity

Cross Database Activation Security

CROSS APPLY doesn't work in some databases

database errors

Blog Archive

About Me