|
That's not bad, but still, requires me to update all my stored procedures... not cool! 
|
|
|
|
|
I'm not clear as to why.
If the synonym is on the test server and points to the code on the production server then any change to the prod server should immediately be available to the test server. You run the code on the test server but it is the code from production.
Or am I not seeing the problem?
_____________________________
Give a man a mug, he drinks for a day. Teach a man to mug...
|
|
|
|
|
Here's why:
CREATE SYNONYM [ schema_name_1. ] synonym_name FOR <object><br />
<br />
<object> :: =<br />
{<br />
[ server_name.[ database_name ] . [ schema_name_2 ].| database_name . [ schema_name_2 ].| schema_name_2. ] object_name<br />
}<br />
USE tempdb;<br />
GO<br />
-- Create a synonym for the Product table in AdventureWorks2008R2.<br />
CREATE SYNONYM MyProduct<br />
FOR AdventureWorks2008R2.Production.Product;<br />
GO<br />
<br />
-- Query the Product table by using the synonym.<br />
USE tempdb;<br />
GO<br />
SELECT ProductID, Name <br />
FROM MyProduct<br />
WHERE ProductID < 5;<br />
GO<br />
Maybe I'm not seeing it, but how would you manage to use the same procedure and call different databases depending on who called the procedure?
Let's say we have db1 and db2 and we're doing:
SELECT A, B, C FROM dbo.Table
What I need is to call the data from db1 if the application made the call, but go read the data on db2 if the call was made by the build server (the unit/integration tests).
|
|
|
|
|
Nevermind.
I'm trying it on my servers and can't seem to see anything but views and tables through my linked server. So maybe the other way around would work.
I'm curious enough to continue researching this but let us know if you decide on a solution.
_____________________________
Give a man a mug, he drinks for a day. Teach a man to mug...
|
|
|
|
|
smcnulty2000 wrote: I'm curious enough to continue researching this but let us know if you decide on
a solution.
I don't think there's one... but I will let you know if I find one, indeed.
|
|
|
|
|
Still does not make sense to me. Why not take a copy of the procedures and run them on the test server. We use SQL Compare to do this except we do it the other way around. Develop on the dev server, test on UAT and deploy to Production, SQL Compare does the synching of the code in both steps.
When we require another environment we take a backup of production onto yet another server. Then the only change is the server name and we don't use that in the qualification in the code. I admit this may become complicated if you are using distributed databases but we use Oracle for any really big data (multi TB)
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Mycroft Holmes wrote: but we use Oracle for any really big data (multi TB)
I'm jealous.
I've only gotten to big gig levels.
_____________________________
Give a man a mug, he drinks for a day. Teach a man to mug...
|
|
|
|
|
We recently sized an Oracle system at 6TB, we can identify 3tb as the initial data coming from part of the trading system.
Really big data is only useful if your structure is good and your data clean. Unfortunately our legacy systems were designed by incompetent, moronic, lazy, uneducated, idiots who only had a vague idea about the business and absolutely no idea about data structures.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Mycroft Holmes wrote: our legacy systems were designed by incompetent, moronic, lazy, uneducated,
idiots who only had a vague idea about the business and absolutely no idea about
data structures
This is in fact the standard methodology for database development in 99% of companies worldwide.
|
|
|
|
|
Mycroft Holmes wrote: Still does not make sense to me. Why not take a copy of the procedures and run
them on the test server.
Because we're using continuous integration with tfs 2010, and want to launch the test sets while the build server is... building. If we have to copy our stored procedures to the test database then this will require a manual action each time a sp is updated, which I think, is a bad idea. Less humans around, less problems.
Apart from checking bugs, we'd also like to guarantee that the deploy succeed regardless of the environment (dev, tst, post test, production).
I know this seems a bit strange since we should only test on one of those, but we have numerous errors due to deployment issues (large amount of files to merge, a lot of manual actions) and we'd like to prevent that, or at least, be warned as soon as possible.
Thanks
|
|
|
|
|
_Zorro_ wrote: continuous integration with tfs 2010
Never have been able to get the hang of automated testing/deployment I guess I'm old school, I want a human driving the deployment.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
It works fine for us, but we still have to do some stuff manually.
We start having huge problems when humans begin to interact. That's actually why we wanted to validate each deployment step, because there's always something wrong. A file not merged correctly, something forgotten on the database, etc. and it implies a big loss of time to go and repair everything... each time...
I suppose your humans are better than ours! 
|
|
|
|
|
_Zorro_ wrote: I suppose your humans are better than ours
Ow, crap, I just had to pick myself up off the floor, possibly we have simpler deployments and I'll take it that we have a very good team of developers but the main systems deployment not a chance!
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Mycroft Holmes wrote: possibly we have simpler deployments
Heh
We have quite complex deployment issues right now indeed. We're maintaining a lot of branches for our customers. And it's not always as easy as it's supposed to be when the time has come to merge all the stuff together after a long period of time.
|
|
|
|
|
How to set password on database in sql server 2005 ?
|
|
|
|
|
Which password?
I Love T-SQL
"VB.NET is developed with C#.NET"
If my post helps you kindly save my time by voting my post.
www.cacttus.com
|
|
|
|
|
This one: *****.
"The worst code you'll come across is code you wrote last year.", wizardzz[ ^]
|
|
|
|
|
d@nish wrote: This one: *****
 
I Love T-SQL
"VB.NET is developed with C#.NET"
If my post helps you kindly save my time by voting my post.
www.cacttus.com
|
|
|
|
|
Set a password that other people can't open my mdf file
|
|
|
|
|
I guess, this link [^]can help you
I Love T-SQL
"VB.NET is developed with C#.NET"
If my post helps you kindly save my time by voting my post.
www.cacttus.com
|
|
|
|
|
You cannot set password on the SQL Server mdf file as you do with an Access mdb file. Use SQL Server Authentication to restrict what users can see.
|
|
|
|
|
I setup a simple sample program to expose a problem I have with the EntityFramework.
A simple table contains 3 fields
contactguid UNIQUEIDENTIFIER Default: NEWID()
surname VARCHAR(255)
forename VARCHAR(255)
I have created an EntityFramework model for it and wrote this code
policy = dbContext.CreateObject<tblContact>();
policyHolder.Surname = "Blog";
policyHolder.Forename = "Joe";
dbContext.SaveChanges();
The issue is that whatever I do the query set to the database will look like this:
exec sp_executesql N'insert [dbo].[tblContact]([ContactGUID], [Surname], [Forename])
values ('00000000-0000-0000-0000-000000000000', 'Blog', 'Joe')
I was not expecting the ContactGUID to be set with zeros but the default NEWID() to be returned to the client.
This is not happening.
I have tried to change about every property of the contactguid field (particularly StoreGeneratedPattern) but nothing seems to have any action.
Did someone hit this problem?
modified on Friday, July 22, 2011 4:39 AM
|
|
|
|
|
Replying to my own message.
My good colleague Adam found an excellent blog post about this exact problem.
The solution to this problem is simple is to edit the your model edmx file.
In the property element which originaly was:
<Property Name="ContactGUID" Type="uniqueidentifier">
I need to add the StoreGeneratedPattern="Identity" attribute.
<Property Name="SectionGUID" Type="uniqueidentifier" Nullable="false" StoreGeneratedPattern="Identity">
The generated SQL then seems very good, as not only the default value are not overwritten but they are returned to the client in a single query.
declare @generated_keys table(ContactGUID uniqueidentifier)
insert [dbo].[dtblContact](surname, forname)
output inserted.ContactGUID into @generated_keys
values ('Blog', 'Joe')
select t.ContactGUID
from @generated_keys as g
join [dbo].dtblContact as t on g.ContactGUID = t.ContactGUID
where @@ROWCOUNT > 0
For
more details visit: http://leedumond.com/blog/using-a-guid-as-an-entitykey-in-entity-framework-4/[^]
modified on Friday, July 22, 2011 4:39 AM
|
|
|
|
|
Hi,
What I have is table Student and Poke
Student
------------------------------------
Student_Id(PK) | Name
------------------------------------
1 Alex
2 Bob
3 Caveman
Poke
----------------------------------------------------------
Poke_Id(PK) | Poke_Giver_Id(FK) | Poke_Receiver_Id(FK)
----------------------------------------------------------
1 1 2
2 1 2
3 2 1
What I'm trying to get is something like this.
----------------------------------------------------
Poke_Id | Poke_Giver_Name | Poke_Receiver_Name
----------------------------------------------------
1 Alex Bob
2 Alex Bob
3 Bob Alex
Please help.
|
|
|
|
|
With simple inner join here is query which you need:
select p.poke_id, s.[Name] as Poke_Giver_Name,s2.[Name] as Poke_Receiver_Name
from students s
inner join Poke p on p.Poke_Giver_Id = s.Student_Id
inner join students s2 on s2.Student_Id = p.Poke_Receiver_Id
I Love T-SQL
"VB.NET is developed with C#.NET"
If my post helps you kindly save my time by voting my post.
www.cacttus.com
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
