Monday, March 26, 2012
gatting at my data!
Problem accessing tables in SQL Query Analyser:-
? why can't I access the database connected as Jim ?
Created Database [SolutionsNet] - Owner Jim
Created Table [Jim.Customers] Owner Jim
Data base access Jim 'permit'
Table - Companies - permissions Jim > Select . . . [the lot]
Jim Permissions - Table Companies > Select . . . [the lot]
Jim - permit in database role > public, db_owners . . [the lot]
BUT
SQL Query Analyser
Connect as Jim
use SolutionsNet
go
select * from jim.companies
go
Server: Msg 229, Level 14, State 5, Line 1
SELECT permission denied on object 'Companies', database 'SolutionsNet', owner 'Jim'.
[same if table is just 'companies' withot the prefix]
HOWEVER
Connect as sa
use SolutionsNet
go
select * from jim.companies
go
Fine:- displays the empty table!
? why can't I access the data connected as Jim ?
Jim Bunton
Hi,
Multiple post.
You have created the table as [JIM.Companies]. This created the table with name JIM.COMPANIES. To avaoid this you have to create table
as [JIM].[Companies]
Thanks
Hari
SQL Server MVP
"Jim Bunton" <jBunton@.BlueYonder.co.uk> wrote in message news:jFt1f.75837$iW5.12732@.fe3.news.blueyonder.co. uk...
Sql Server 2000, Win 2000
Problem accessing tables in SQL Query Analyser:-
? why can't I access the database connected as Jim ?
Created Database [SolutionsNet] - Owner Jim
Created Table [Jim.Customers] Owner Jim
Data base access Jim 'permit'
Table - Companies - permissions Jim > Select . . . [the lot]
Jim Permissions - Table Companies > Select . . . [the lot]
Jim - permit in database role > public, db_owners . . [the lot]
BUT
SQL Query Analyser
Connect as Jim
use SolutionsNet
go
select * from jim.companies
go
Server: Msg 229, Level 14, State 5, Line 1
SELECT permission denied on object 'Companies', database 'SolutionsNet', owner 'Jim'.
[same if table is just 'companies' withot the prefix]
HOWEVER
Connect as sa
use SolutionsNet
go
select * from jim.companies
go
Fine:- displays the empty table!
? why can't I access the data connected as Jim ?
Jim Bunton
|||Hi Hari - thanks for your clear response to my posting
enterprise manager reports that I have created a tabale 'Companies' whose owner is Jim
sqlQueryAnalyser reports that SolutionsNet includes the table Jim.Companies - I an assuming that this means there is a table called companies whose owner is Jim.
When connected as Jim(SqlQueryAnalyser) both
select * from companies
or
select * from jim.companies
return the error:-
Server: Msg 229, Level 14, State 5, Line 1
SELECT permission denied on object 'Companies', database 'SolutionsNet', owner 'Jim'.
After using dbo to dropt the table
CREATE TABLE
[Jim].[Companies]
(CompanyId INT PRIMARY KEY IDENTITY(1,1),
Companyname VarChar(50)NOT NULL,
.. . . .
Nothing has changed! same results as before
Drop the table
connect as Jim and run:-
CREATE TABLE
[Jim].[Companies]
(CompanyId INT PRIMARY KEY IDENTITY(1,1),
Companyname
. . . .
The command(s) completed successfully.
Run SqlEnterprise manager - reports the table Companies owner Jim
as 'Jim' I still can't access the table
Server: Msg 229, Level 14, State 5, Line 1
SELECT permission denied on object 'Companies', database 'SolutionsNet', owner 'Jim'.
But I can as sa/dbo
BUT if I create a Windows Authentification user [Study/Jim Bunton] and connect then I can access the table!!! - Select user_name informs me that I'm dbo [I can still access the table whether or not Select . . permissions are set tick or cross.
I'm totally mystified!
NOW? - Might it be because I am running SQL Server Personal edition?
[I am not running win 2k server - just win 2k]
NB
[The irony is that on an .asp page I have totally failed to get a Trusted Connection (windows authentification] to work for Northwind and resorted to using the datase authentification!
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message news:ONwZyb2yFHA.2348@.TK2MSFTNGP15.phx.gbl...
Hi,
Multiple post.
You have created the table as [JIM.Companies]. This created the table with name JIM.COMPANIES. To avaoid this you have to create table
as [JIM].[Companies]
Thanks
Hari
SQL Server MVP
"Jim Bunton" <jBunton@.BlueYonder.co.uk> wrote in message news:jFt1f.75837$iW5.12732@.fe3.news.blueyonder.co. uk...
Sql Server 2000, Win 2000
Problem accessing tables in SQL Query Analyser:-
? why can't I access the database connected as Jim ?
Created Database [SolutionsNet] - Owner Jim
Created Table [Jim.Customers] Owner Jim
Data base access Jim 'permit'
Table - Companies - permissions Jim > Select . . . [the lot]
Jim Permissions - Table Companies > Select . . . [the lot]
Jim - permit in database role > public, db_owners . . [the lot]
BUT
SQL Query Analyser
Connect as Jim
use SolutionsNet
go
select * from jim.companies
go
Server: Msg 229, Level 14, State 5, Line 1
SELECT permission denied on object 'Companies', database 'SolutionsNet', owner 'Jim'.
[same if table is just 'companies' withot the prefix]
HOWEVER
Connect as sa
use SolutionsNet
go
select * from jim.companies
go
Fine:- displays the empty table!
? why can't I access the data connected as Jim ?
Jim Bunton
Friday, March 9, 2012
Function Reference
Regards
Frik
It's listed under Using Expressions in Reporting Services.
http://msdn2.microsoft.com/en-us/library/ms159673(en-US,SQL.90).aspx
-Lukasz
This posting is provided "AS IS" with no warranties, and confers no rights.
In addition to the built-in RDL functions, you can use any object / function from the .NET framework which is available within the following namespaces: System, System.Convert, System.Math, and Microsoft.VisualBasic.
A reference of available expression functions in the VB runtime library is available at:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vblr7/html/vaoriVBRuntimeLibraryKeywords.asp
-- Robert
Function Reference
Regards
Frik
It's listed under Using Expressions in Reporting Services.
http://msdn2.microsoft.com/en-us/library/ms159673(en-US,SQL.90).aspx
-Lukasz
This posting is provided "AS IS" with no warranties, and confers no rights.
In addition to the built-in RDL functions, you can use any object / function from the .NET framework which is available within the following namespaces: System, System.Convert, System.Math, and Microsoft.VisualBasic.
A reference of available expression functions in the VB runtime library is available at:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vblr7/html/vaoriVBRuntimeLibraryKeywords.asp
-- Robert
Wednesday, March 7, 2012
Function default parameter
Since GetDate() can't be used inside a UDF I thought
I would use a date parameter with a default value of GetDate()
i.e.:
Create function dbo.ufn_GetWeekAreaAvailability(
@.WeekId int,
@.AreaId char(1),
@.Now DateTime = GetDate()
) Returns
or
Create function dbo.ufn_GetWeekAreaAvailability(
@.WeekId int,
@.AreaId char(1),
@.Now DateTime = (Select GetDate())
) Returns
These do not even compile
However
Create function dbo.ufn_GetWeekAreaAvailability(
@.WeekId int,
@.AreaId char(1),
@.Now DateTime = GetDate
) Returns
Does but when I call it it
Select * from dbo.ufn_GetWeekAreaAvailability(1,'1',Default)
It tells me that string cannot be converted to a date
Server: Msg 241, Level 16, State 1, Procedure ufn_Test, Line 0
Syntax error converting datetime from character string.
Any Idea...
Thanks
Fred
Hi Fred,
You can't use a function as the default value for a parameter, only
literals. You can use a view that just returns the value of GETDATE() for
example:
CREATE VIEW vw_getdate
AS
SELECT GETDATE() AS getdate
but the view will be accessed each time the function is executed, which
means that you can end up with having different values for each execution.
The safest is not to use a default value and just pass in the value for
GETDATE().
Jacco Schalkwijk
SQL Server MVP
"Fred" <Fred@.discussions.microsoft.com> wrote in message
news:7315A12D-37ED-4F5F-8855-2DFE1F2153E6@.microsoft.com...
>I am creating a udh function that requires to know the current date.
> Since GetDate() can't be used inside a UDF I thought
> I would use a date parameter with a default value of GetDate()
> i.e.:
> Create function dbo.ufn_GetWeekAreaAvailability(
> @.WeekId int,
> @.AreaId char(1),
> @.Now DateTime = GetDate()
> ) Returns
> or
> Create function dbo.ufn_GetWeekAreaAvailability(
> @.WeekId int,
> @.AreaId char(1),
> @.Now DateTime = (Select GetDate())
> ) Returns
> These do not even compile
> However
> Create function dbo.ufn_GetWeekAreaAvailability(
> @.WeekId int,
> @.AreaId char(1),
> @.Now DateTime = GetDate
> ) Returns
> Does but when I call it it
> Select * from dbo.ufn_GetWeekAreaAvailability(1,'1',Default)
> It tells me that string cannot be converted to a date
> Server: Msg 241, Level 16, State 1, Procedure ufn_Test, Line 0
> Syntax error converting datetime from character string.
> Any Idea...
>
> Thanks
>
>
> --
> Fred
Sunday, February 26, 2012
Full-Text Search stopped for confict in service accounts
In me organizattion are one sql 2000 server with the dbs.
When remote users connect to this server can't see the Full-Text Search
running.
The Microsoft Search service in windows are running.
I was read that the problem concern to change the user of microsoft sql
services after install the server. This is the poit. This server have this
problem. I change the user of sql services after install the sql.
Have any form of resolve this confinct without reinstall the sql server?
Thanks a lot
cyber
MSSearch must run under the local system account. One of the reasons SQL FTS
might not work is because you change the account that MSSQLServer runs under
using control panel. If so you should locate your server in Enterprise
Manager, right click on it, and select properties, then click the security
tab, and change it back to system, SQL Server will prompt you to stop and
start it. Then change it back to the local admin account.
I suspect you are actually running into this problem -
http://www.indexserverfaq.com/tablenotenabled.htm
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"cyb165er" <cyb165er@.discussions.microsoft.com> wrote in message
news:CBD24B8D-9D09-4EC0-84C0-84D48F7344CC@.microsoft.com...
> Hi:
> In me organizattion are one sql 2000 server with the dbs.
> When remote users connect to this server can't see the Full-Text Search
> running.
> The Microsoft Search service in windows are running.
> I was read that the problem concern to change the user of microsoft sql
> services after install the server. This is the poit. This server have this
> problem. I change the user of sql services after install the sql.
> Have any form of resolve this confinct without reinstall the sql server?
> Thanks a lot
> cyber