|
Hi,
I have a problem using synonyms in stored procedure.
In my procedure I create a synonym based on a specific business logic.
For example:
create procedure test_sp_create_synonyms
as
if object_id('synTest')is not null
drop synonym synTest
exec('create synonym synTest for Counters')
select top 10 * from synTest
go
The problem appears when before executing this procedure, the synonym synTest
points to table T1 that does not exists, and the table T1 is placed on Linked Server.
For example:
synTest -> [test_ls].[DB1].[dbo].[T1]
When I execute the procedure an error is raised:
The OLE DB provider "SQLNCLI" for linked server "test_ls" does not contain the table ""DB1"."dbo"."T1"". The table either does not exist or the current user does not have permissions on that table.
The problem is only when T1 is situated on linked server. If is on a local server(example: synTest -> [dbo].[T1]) here is no problem and the execution of test_sp_create_synonyms passes OK.
I will be grateful if someone can help me.
|
|
|
|
|
This doesn't quite make sense.
The synonym creation should be ok even if the table doesn't exist, but your select statement (select top 10...) should fail if T1 does not exist in target database. But then again, this is supposed to happen if the table does not exist.
Synonyms can be created against non-existing objects and that shouldn't be any problem as long as when using the synonym the object exists.
Or did I miss something?
The need to optimize rises from a bad design.
My articles[ ^]
|
|
|
|
|
Hello all,
I'm wondering how to get the schema of the result set returned by a stored procedure without calling it. I know that some code generation tools do this, but I've been unable to figure out how to go about it. Is there some INFORMATION_SCHEMA view that does this that I don't know about?
Thanks,
Will
|
|
|
|
|
I have the impression they discovery tool actually executes the procedure to get the resultset. The only other way I can think of would be to parse the sysobjects data but that would be a nightmare.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
I ran a SQL trace. Looks like this kind of thing is what they are doing. I tested it and it is non-destructive.
SET FMTONLY ON
EXEC Test_Proc
SET FMTONLY OFF
I've never seen that keyword before. Ever day is a learning experience.
|
|
|
|
|
Hi,
I have a problem with attaching databases to Microsoft SQL Server Express 2005, databases are attached as Read-Only even I'm using Management Studio or sp_attach_db stored procedure.
Can you help me??
|
|
|
|
|
One possibility is that the database is marked read-only before detach. If the database isn't in restoring state (everything is fine after attach), could you simply set it to read-write state:
ALTER DATABASE DatabaseName SET READ_WRITE;
The need to optimize rises from a bad design.
My articles[ ^]
|
|
|
|
|
Hi Mika, thanx for reply
I cannot change database to be read write when I tried your code I received the following errors:
Msg 5120, Level 16, State 101, Line 1
Unable to open the physical file "D:\>> DatabaseName.mdf". Operating system error 5: "5(error not found)".
Msg 5120, Level 16, State 101, Line 1
Unable to open the physical file "D:\>> DatabaseName.ldf". Operating system error 5: "5(error not found)".
File activation failure. The physical file name ">> DatabaseName.ldf" may be incorrect.
Msg 945, Level 14, State 2, Line 1
Database 'DatabaseName' cannot be opened due to inaccessible files or insufficient memory or disk space. See the SQL Server errorlog for details.
Msg 5069, Level 16, State 1, Line 1
ALTER DATABASE statement failed.
My database is a valid database copied from another machine on witch it works fine.
|
|
|
|
|
Ahmad Safwat wrote: Database 'DatabaseName' cannot be opened due to inaccessible files or insufficient memory or disk space. See the SQL Server errorlog for details.
In the ALTER command, replace the DatabaseName with the actual database name and try again. Now you tried to set options for database named DatabaseName. For example if your database is named Test then the command would be:
ALTER DATABASE <code>Test </code>SET READ_WRITE;
The need to optimize rises from a bad design.
My articles[ ^]
|
|
|
|
|
I'm sorry it was the weak end, and I was unable to follow up the thread
Mika Wendelius wrote: replace the DatabaseName with the actual database name
I understood you first time and this is what I exactly did, and received the previous errors.
And my database is a valid database copied from another machine on witch it works fine.
|
|
|
|
|
Ok,
The error message you got when trying to set read_write is quite odd. It clearly states that your physical files are not in good condition or not found.
- are there any suspicious messages in the error log when you attach the database?
- also check that the file attributes are correct on both physical files (not read-only and not system)
The need to optimize rises from a bad design.
My articles[ ^]
|
|
|
|
|
!!! S U R P R I S E !!!
The problem has been solved by changing "Log On" type of SQL Server Service from "Network Service" to "Local System".
Now, I changed "Log On" type back to "Network Service" and still no problem !!!
Can anybody explain that to me??
|
|
|
|
|
Local system has all the privileges for the computer whereas privileges for Network Service are limited.
There are several reasons to this. Your datafiles in the db you attached may have had restrictions at file level which are now corrected, the directory where the files reside may have had a privilege problem etc.
Didn't the errorlog say anything when you attached the database for the first time?
The need to optimize rises from a bad design.
My articles[ ^]
|
|
|
|
|
Is the database file's attribute set to readonly?
"The clue train passed his station without stopping." - John Simmons / outlaw programmer
"Real programmers just throw a bunch of 1s and 0s at the computer to see what sticks" - Pete O'Hanlon
"Not only do you continue to babble nonsense, you can't even correctly remember the nonsense you babbled just minutes ago." - Rob Graham
|
|
|
|
|
i would like to insert a file into a table corresponding to a varbinary column. is there a way to specify the complete filepath in the local directory in the sql script? all within sql not c#.
thanks in advance.
----------------------------------------------------------
"unzip; strip; touch; finger; mount; fsck; more; yes; unmount; sleep" - my daily unix command list
|
|
|
|
|
Perhaps somehthing like:
UPDATE SomeTableName
SET SomeColumnName = (SELECT *
FROM OPENROWSET(BULK
'C:\AnyPath\AnyFile.Extension',
SINGLE_BLOB) TableAlias )
The need to optimize rises from a bad design.
My articles[ ^]
|
|
|
|
|
Hi,
I have created a table and in that i have inserted some records. then i want to display the records between specified dates then how to do that.
create table rough(checkeddate nvarchar(100))
These are the inserted values..
insert into rough values('1/01/2002')
insert into rough values('3/01/2002')
insert into rough values('12/01/2003')
insert into rough values('11/09/2006')
insert into rough values('8/01/2008')
insert into rough values('1/01/2004')
insert into rough values('1/01/2006')
insert into rough values('1/21/2003')
insert into rough values('1/31/2005')
insert into rough values('1/11/2006')
insert into rough values('1/01/2006')
insert into rough values('1/01/2002')
insert into rough values('1/01/2004')
insert into rough values('1/01/2006')
The output as follows
mm/dd/yyyy
----------
12/01/2003
11/09/2006
1/01/2006
1/21/2003
1/31/2005
1/11/2006
1/01/2006
1/01/2006
so my requirement is now i want to diplay particular records between two dates like as follows
select * from rough where checkeddate between '1/01/2006' and '12/31/2006'
Can anyone help me..
|
|
|
|
|
Samiullah wrote: create table rough(checkeddate nvarchar(100))
Why are you storing date as an nvarchar? Try using a datetime type instead and you can easily filter records using the query you mentioned. But if you still want to achieve it using an nvarchar, you can simply cast the value to a datetime. Here's a small change to your query:
select * from rough where cast(checkeddate as datetime) between '1/01/2006' and '12/31/2006'
Regards,
Syed Mehroz Alam
My Blog
My Articles
Computers are incredibly fast, accurate, and stupid; humans are incredibly slow, inaccurate and brilliant; together they are powerful beyond imagination. - Albert Einstein
modified on Thursday, November 13, 2008 8:22 AM
|
|
|
|
|
Thanks for your immediate response. As iam new to sql server and its also my requirement to use the nvarchar thats why!
|
|
|
|
|
as Syed said - STORE YOUR DATES AS DATETIME. This is a fundamental error in design and you should be smacked for using nvarchar. You are now going to forever need to cast/convert that column to do anything with it.
More emphasis - USE THE CORRECT DATA TYPE
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Yeah, what they said... or at least use an ISO 8601 compliant format YYYY-MM-DD.
|
|
|
|
|
using C# and SQL Server 2005
Instantiate a data adapter on startup and re-use.
No problems when 1 station running.
When a number of stations running at the same time, data adapter goes null in the middle of the operation. i.e., It deletes some records from the database as it is supposed to do, but then goes null.
Is there some way to query the database to get more information on why this is happening? Any debugging ideas? I don't have much experience with SQL Server, so if you have an idea please give me the specifics. Thanks.
|
|
|
|
|
First, I didn't quite understand what you mean with 'station'. However, I believe that it's hard to observe this situation from database since the reason most likely isn't in the database.
Instead, use debugger in Visual Studio, catch the exception immediately when it happens (use menu Debug/Exceptions... to catch all handled/unhandled exceptions) and then use breakpoints to narrow the area.
The most likely cause for this is that if you are reusing same data adapter, when the first operation ends and you perhaps dispose the object, you use the same variable somewhere else (or same code in another thread) and the value is set to null.
One way to get more information is to add diagnostics messages (for example using Trace.WriteLine) to every place where you instantiate the data adapter, set it to null (or dispose it, i.e. using blocks) or use it. This will give you info how things are going and in which order (especially if you're using threads).
Hope this helps,
Mika
The need to optimize rises from a bad design.
My articles[ ^]
|
|
|
|
|
Thanks for the suggestions, Mika.
By station I meant client.
Via Debug/Exceptions is how null data adapter was determined.
Adapter is instantiated on startup and disposed of when application quits - isolated in one class. Pretty simple in that respect.
Agree it might be caused in some other area - since it happens sporadically and has been hard to pinpoint using the debugger, was just wanting to try another avenue. I was hoping the database might keep something like a 'last error' the way it tracks number of deadlocks.
Have found a way around it for the time being.
Thanks again ...
|
|
|
|
|
Okay,
Just for explanation: If the command would fail while in the database or connection gets lost for some reason, you end up in an error at the database. As a result of an error you would get an exception at calling side (in C# in this case). So, if you're not receiving any exceptions in your code, but the data adapter just suddenly goes to null, it's quite certain that the problem is in the code.
If you're using singleton or static class, perhaps one way to find out the problem is to define a property that returns your data adapter and keep the data adapter private in your class (if not already done so). Now in the property setter, first test if new value for the property is null and act based on that (throw an exception, write log etc). This could help you to isolate the cause.
Also you could double-check that you are not ignoring any exceptions anywhere. One reason could be that the statement actually fails in the database, but you don't get information about the exception (for example an empty catch block).
If you would end up in a deadlock you would get an exception in C# and have a message similar to:
"Transaction (Process ID 52) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction."
However, if you suspect that deadlock might occur, you can set trace flag 1204 in startup parameters. After restarting the SQL Server, deadlock info if is written into errorlog at server side if a deadlock occurs.
Hope this helps you forward,
Mika
The need to optimize rises from a bad design.
My articles[ ^]
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
