|
so if you specify that to server name, what happens? Do you get an error and if you do, what kind of error.
|
|
|
|
|
Error is:
This version of MSSMSE can only be used to connect to sql server 2000 & 2005
N!dh! 
|
|
|
|
|
|
I just started a new project and I need to work on the thing at home.
I have permission to take what I need but I'm not sure what I'm looking for?
I asked (and received) the Schema for the tables but whenever I try and run the SQL queries in the SQLServer query window I get all sorts of errors about missing LDF/MDF files:
Msg 5133, Level 16, State 1, Line 2
Directory lookup for the file "D:\_SQL_LOG\AXXX_CLQ_Log.LDF" failed with the operating system error 2(The system cannot find the file specified.).
Msg 1802, Level 16, State 1, Line 2
CREATE DATABASE failed. Some file names listed could not be created. Check related errors.
Msg 15010, Level 16, State 1, Procedure sp_dboption, Line 64
The database 'AXXX_CLQ' does not exist. Supply a valid database name. To see available databases, use sys.databases.
I'm wondering if it wouldn't be easier to just copy the actual files and move them into position? Unfortunately it looks as though the LOG files and data files are stored on different partitions and at home I only have one.
How can I be sure to get a full copy of everything so it's easy to setup at home and get working on this in the evening?
Cheers,
Alex
|
|
|
|
|
Hi,
there are a couple of possibilities:
1. change your fixed paths to something that exists on every relevant system, so if one doesn't have a D: then
don't use D: anywhere
2. make the paths configurable somehow, so you can run the app with different paths on different machines;
3. add a D: partition to the machine that lacks one (Vista does not need a tool to do this, i.e. Computer Management
can solve this;XP and earlier get changed most easily using Partition Magic or something similar)
4. on the machine(s) lacking D: use "Map network drive" to map D: to some existing folder on C: (see Explorer,
Tools menu).
In the short term I recommend 4; in the long term I would go for 2.
Luc Pattyn [Forum Guidelines] [My Articles]
- before you ask a question here, search CodeProject, then Google
- the quality and detail of your question reflects on the effectiveness of the help you are likely to get
- use the code block button (PRE tags) to preserve formatting when showing multi-line code snippets
|
|
|
|
|
Luc Pattyn wrote: change your fixed paths to something that exists on every relevant system, so if one doesn't have a D: then
don't use D: anywhere
Thing is all I have is the schema SQL file not the files which the schema seems to refer. Can I create empty files and assume that would work?
|
|
|
|
|
hat do you actually mean by schema? Do you refer to the MDF-file? If yes, then how have you taken it. Is it detached, just copied etc.
|
|
|
|
|
Hi all. Is there a way to automatically grant execute permissions on CLR stored procedures upon deployment from a Visual Studio SqlServer project?
I have a solution with a project for all my Sql Server stored procs, and a second project for a web site that tests them. When I rebuild/run the web site, the stored procedure assembly is rebuilt and re-deployed also (which is good) but because the stored procedures are recreated, the GRANT permissions previously on them are lost (which is not so good). I'd like to have the GRANT EXECUTE permissions added as an automatic part of the deployment process, if that is possible.
Help?
|
|
|
|
|
Instead of rebuilding (dropping and creating) the procedures, could you use ALTER PROCEDURE syntax? If that's not an option, perhaps you could include a plain script to the deployment (depending how you do the deployment).
|
|
|
|
|
Mika Wendelius wrote: Instead of rebuilding (dropping and creating) the procedures, could you use ALTER PROCEDURE syntax? If that's not an option, perhaps you could include a plain script to the deployment (depending how you do the deployment).
If I were writing the deployment script, then sure, but I was hoping to stay within Visual Studio - it's just a lot more convenient to click the "run" button, have it go through the build and deployment, and run the web app. I was hoping that I've missed an option along the way that supports the execution of custom SQL scripts upon deployment... or some other means of addressing the stored procedure permissions. I haven't found anything like that in VS.
You know, something like "Deployment Events..." similar to "Build Events..." would be exactly the thing.
|
|
|
|
|
Have you tried post build events? I'm not sure, but I think it's run as the last operation in building sequence.
I did a little test and it seems that the post build command line (or actually I used a script) was run after the full build was done, but I think I may have a different situation since the target directory was actually the web folder.
|
|
|
|
|
Mika Wendelius wrote: Have you tried post build events? I'm not sure, but I think it's run as the last operation in building sequence.
I did a little test and it seems that the post build command line (or actually I used a script) was run after the full build was done, but I think I may have a different situation since the target directory was actually the web folder.
Hi Mika. Thanks for the replies. I guess that might work - a post-build event on the web project that issues the GRANT EXECUTE statements to the server... I can try that.
It just seems like there should be a way to do this when compiling and deploying the stored procedures themselves, but the above might be a workaround.
Thanks again.
|
|
|
|
|
Mike Ellison wrote: the above might be a workaround.
If you have time, let us know if it worked. I think that there could be other workarounds also, but nothing comes in mind that would be any better than post event build.
One thing I was thinking is that if the grant statements are fixed, could you actually do this in the database. What I mean is that if you would create a DDL trigger using CREATE_PROCEDURE event type, you could catch the procedure creation at the database and then execute the necessary grants...
Mike Ellison wrote: Thanks again
You're welcome
|
|
|
|
|
Hi Mika. I ended up using a DDL trigger like the following:
CREATE TRIGGER GrantExecuteTrigger on DATABASE
FOR CREATE_PROCEDURE
AS
declare @user varchar(64)
set @user = 'whateverUserWeWant'
declare @data xml
set @data = EVENTDATA()
declare @sql varchar(max)
set @sql = 'GRANT EXECUTE ON '
+ @data.value('(/EVENT_INSTANCE/ObjectName)[1]', 'varchar(256)')
+ ' TO ' + @user
execute(@sql)
GO
This did work... every time the assembly/stored procs are deployed from Visual Studio, the trigger is applying the GRANT EXECUTE permission accordingly. I will just have to remember to adjust permissions on any stored procs that I would work with outside the VS context.
Much of what I read regarding this issue indicated there was a Script.PostDeployment.sql file that could be modified for this purpose, but I wonder if that is limited to the Database edition of Team Studio... we have the Architecture edition, and it doesn't seem like the same facility exists there (unless I'm completely missing it).
|
|
|
|
|
Mike Ellison wrote: I wonder if that is limited to the Database edition of Team Studio
Seems that i's only in Database Edition, http://msdn.microsoft.com/en-us/library/aa833410.aspx[^]
Mike Ellison wrote: I will just have to remember to adjust permissions on any stored procs that I would work with outside the VS context.
If you want to put some time to this, one way could be that you:
- create a table in your db where you will store the privileges that you want to grant to procedures (also grantees)
- include a file containing the data for the grants to your VS project
- using pre build events, first delete the grants from the table in the database
- add new rows to the table based on the previous file (you could use sqlcmd or osql for this)
Now when the procedure is created, your trigger could read the grant "profiles" from that table so you could control what privileges are granted using the file in VS project.
|
|
|
|
|
Thanks for the suggestion, Mika, and I appreciate the conversation you and I have been having on this. It was very helpful.
|
|
|
|
|
Mike Ellison wrote: It was very helpful.
Glad to hear that You're welcome.
|
|
|
|
|
Hi,
I'm trying to use the query below in MSSQL.
<br />
SELECT [mxDelete],[mxContactID] <br />
FROM mxContact.dbo.Contacts <br />
WHERE mxContactID IN <br />
<br />
(SELECT [mxContactID], [mxzCopies]<br />
FROM <br />
(SELECT [mxContactID], [mxzCopies]<br />
FROM mxContact.dbo.Associations_Groups_to_Contacts <br />
WHERE mxGroupID='35EA110C-FE44-4A85-8D9A-AE973E2C5F85' <br />
OR mxGroupID='3CF66F58-F8E4-44CD-84A0-CB62F85E129B') <br />
AS COL<br />
GROUP BY mxContactID HAVING (COUNT(mxContactID) > 1))<br />
My question is, how can I make it so that the code in bold will give me the mxzCopies column as well.
If I select just the contactID, then it works fine, but with the addition of mxzCopies, it gives me:
Msg 8120, Level 16, State 1, Line 1
Column 'COL.mxzCopies' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Msg 116, Level 16, State 1, Line 1
Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.
Cheers,
Mark.
Mark Brock
"We're definitely not going to make a G or a PG version of this. It's not PillowfightCraft." -- Chris Metzen
Click here to view my blog
|
|
|
|
|
Nevermind figured it out.
<br />
SELECT mxContactID, mxzCopies<br />
FROM <br />
(<br />
SELECT [mxContactID], [mxzCopies]<br />
FROM mxContact.dbo.Associations_Groups_to_Contacts <br />
WHERE mxGroupID='35EA110C-FE44-4A85-8D9A-AE973E2C5F85' <br />
OR mxGroupID='3CF66F58-F8E4-44CD-84A0-CB62F85E129B'<br />
)<br />
AS COL<br />
GROUP BY mxContactID, mxzCopies HAVING (COUNT(mxContactID) > 1)<br />
Mark Brock
"We're definitely not going to make a G or a PG version of this. It's not PillowfightCraft." -- Chris Metzen
Click here to view my blog
|
|
|
|
|
whenever i was trying to do this .. exception occurred.
Please help me out.
Thanks in Advance.
|
|
|
|
|
In short, no.
However, you can export the database in 2005 (scripts, csv etc) and import the data in 2000 as long as you don't use 2005 specific features or datatypes.
|
|
|
|
|
XQuery - its a tricky beast.
Have a look at the script below, I'm creating a simple xml document in a single row in a temporary table. What I want to do is update EVERY value element and set it to D. There's three, A, B and C, and I want all three to be updated to D.
The script complains that I'm attempting to update more than one node. Does this mean I have to use some iterative approach to do this? Any ideas anyone?
CREATE TABLE #temp ( myXml xml )
INSERT INTO #temp VALUES ('<document><value>A</value><value>B</value><value>C</value></document>')
select * from #temp
update #temp set myXml.modify('replace value of (//value/text()) with "D"')
drop table #temp
Regards,
Rob Philpott.
|
|
|
|
|
Rob Philpott wrote: Does this mean I have to use some iterative approach to do this?
Yes, you'll have to iterate through the nodes. SQL Server doesn't support multiple node updates at once.
One way you could do this is something like this:
declare @counter int
set @counter = 1
while (@counter <= 3)
begin
update #temp
set myXml.modify('replace value of (/document/value[sql:variable("@counter")]/text())[1] with "somevalue"')
set @counter = @counter + 1
end
|
|
|
|
|
Perfect. Thanks Mika.
Regards,
Rob Philpott.
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
