I create a new Role "TestRole"under Security/ Roles in the Report Server.
I i want to drop this Role, i get the Error that the Role does not exist ?
But the Role exist !!
Anyone can help ?
For UI operations, please post to SQL Server Tools General.
Thanks
Laurentiu
due to unavoidable reasons i had to use a ## temp table in a SP,
ie i had to dynamically create a table whose (number of)columns i come to know at runtime..
if i do thi ::set @.sql = 'create #table....select some columns _ append varchar(10)'
then insert into #temp....temp is not valid here..so i used ##temp
now i need to explicitly drop it...also in catch block , i need to make a provision for droping it incase of an error in runnin proc...some kind os IF EXISTS drop ##temp.... as i dont know if it'll be created by that time or not..how do i do it..there is ofcource no entry in sysobjects....where is the entry for temp tables...tempdb dosent have system tables..!!
Can you provide SQl of SP...
~Mandip
|||Nitin:
Since in this case it is a global temp table -- that is, it starts with ##, it will explicitly appear in sysobjects in tempdb:
|||create table dbo.##temp
( what varchar (5)
)select uid, id, left (name, 20) as name from tempdb.dbo.sysobjects where name = '##temp'
if exists
( select 0 from tempdb.dbo.sysobjects
where name = '##temp'
and type = 'U'
)
begin
print 'Dropping the table.'
print ' '
drop table ##temp
endselect id, left (name, 20) as name from tempdb.dbo.sysobjects where name = '##temp'
-- -
-- O U T P U T :
-- --- uid id name
-- -- --
-- 1 853242293 ##temp
--
-- (1 row(s) affected)-- Dropping the table.
--
-- id name
-- -- ---- (0 row(s) affected)
Dave
Thanks Dave.....
actually i was tryin this...
select * from sysobjects where name like '##temp'
hence the question.....now as u told it wors fine while i do this :: select * from tempdb.dbo.sysobjects where name like '##temp'
tell me if 2 ppl r running this sp simultaneously , will 1 of them get an error (##temp already exist..) or he'll have to wait for a tempdb lock.. ?
|||
Nitin:
Yes, because you are using a GLOBAL temp table this is a potential problem. However, since you are in a stored procedure, the procedure will automatically drop the temp table when it goes out of context should you use a non-global temp table -- one that starts with # instead of ##. I don't see why this would not be acceptable. Are you doing something in which you potentially need the temp table to have persistence beyond the scope of the procedure?
|||
Dave
dave :
i am creating my temp table by creating a sql string for it and then executing it..as num of columns is decided on the runtime..below is the query..
declare @.mytable varchar(500)
set @.mytable = 'CREATE TABLE ##temp (UserId int,'
select @.mytable = @.mytable + 'Plan_' + cast(PlanCode AS nvarchar(10)) + ' varchar(5),'
FROM (SELECT distinct PaymentPlanPlanTypeCode FROM #sometable) p
SET @.mytable = LEFT(@.mytable, LEN(@.mytable) - 1)
SET @.mytable = @.mytable + ')'
print @.mytable
exec (@.mytable)
i need this temp table in this 1 SP only but when i replace the ##temp with , #temp , the table is not getting created... i dont know why..probably some scope problem... also i ran out of the option of using table datatype as my proc further refers this table and i cant declare it inside a string..
see this as well :
--1
declare @.sql varchar(100)
set @.sql='create table ##tem (a int)'
exec (@.sql)
select * from #tem
error : invalid object #tem
--2
create table #tem (a int)
select * from #tem
gives the result.
|||
Nitin:
You are right, you do have a scope problem. I have a rather"dirty" idea, but I will have to test it out and unfortunately I won't have any time for at least an hour or so. Hopefully, you can get a better idea from somebody else can get you a better idea than what I have. I will check back in a while and if nobody else has come up with something I will test out my "dirty trick."
|||Dave
Nitin:
|||One more question: I am assuming that this is not a "performance critical" procedure and that even though it is possible for multiple users to execute this procedure at the same time it is not likely. Is that assumption correct or is it rather likely that multiple users will execute your procedure concurrently?
Dave
hi ,
this proc is for some mis report...though data will be large, its unlikely that more then a few users will use it... but agn..can be more then 1 at a time...
so i guess ur assumption can hold..
|||Nitin:
This is my "dirty" suggestion. I tried it out and it seems to work. If you get another idea it will probably be better. Good luck.
|||Dave
-- --
-- First, I think I might have used the TABLOCK optimizer hint less than a handful
-- of times over my entire carreer. I don't think I've ever used TABLOCKX other
-- than in demo code. So to begin with I am iffy on the code that follows.
--
-- With that said, understand that what this code tries to use the execute string.
-- to build the format of the target table into a global temp table. Once that is
-- done a SELECT INTO is used to grab that format and use it to create the
-- intended local temp table. Once that is done the global temp table can be
-- dropped. This TABLOCKX optimizer hint means that this portion of the code
-- is single-threaded and is definite bottleneck. If this is not an intensely
-- used query this might be sufficient.
--
-- Unfortunately, there are additional bottlenecking problems. As I suspected,
-- The "select into" portion of the query puts exclusive locks on keys (1)
-- SYSOBJECTS, (2) SYSINDEXES and (3) SYSCOLUMNS of the tempdb database. Also,
-- exclusive intent locks are put on all three of these tables plus
-- (1) SYSCOMMENTS, (2) SYSDEPENDS, and (3) SYSPERMISSIONS and (4) SYSPROPERTIES.
--
-- If you wish to test this out, just comment out the COMMIT TRAN command,
-- run the procedure and then run SP_LOCK. To release the locks exeucte the
-- COMMIT TRAN statement.
--
-- Therefore, it is critical that if this kind of code is used in production that
-- the construction of the TEMP table take place OUTSIDE of the actual processing
-- transaction so that the code below executes in as few microseconds as possible
-- and reduces the profile of the bottleneck.
--
-- I also tested this with no transaction enclosures and exeucted the WAITFOR so
-- that I could see how it locked outside of any transaction enclosures. I
-- commented out my BEGIN TRAN and END TRAN satements, ran with the WAITFOR in
-- affect and did an SP_LOCK with an outside connection. No locks were retained.
-- What to understand with all of this is that (1) the TABLOCKX hint will single
-- thread this and that is not particular good; however, (2) SQL Server issues
-- locks that might otherwise single-thread you briefly anyway; so this might not
-- be TOO bad. (3) Always be careful how you use SELECT INTO syntax; it can
-- also bite you.
--
-- I really don't like this code; but if you don't get any other suggestion, it
-- might be worth a try.
--
-- Dave
--
-- --
--begin tran doWhatexec ( 'create table ##what (what varchar (20) ) ' )
select * into #what from ##what (TABLOCKX) where 1=0
if exists
( select 0 from tempdb.dbo.sysobjects
where type = 'U'
and name = '##what'
)
drop table ##whatselect * from #what
--waitfor delay '0:01:00.000'
drop table #what
--commit tran
go
-- -
--
-- --- spid dbid ObjId IndId Type Resource Mode Status
-- -- - - --
-- 55 2 6 0 TAB IX GRANT
-- 55 2 1 0 TAB IX GRANT
-- 55 2 2 0 TAB IX GRANT
-- 55 2 12 0 TAB IX GRANT
-- 55 2 9 0 TAB IX GRANT
-- 55 2 11 0 TAB IX GRANT
-- 55 2 1529959413 0 TAB Sch-M GRANT
-- 55 2 3 2 KEY (9401125ee398) X GRANT
-- 55 2 1 3 KEY (f50024d97b7f) X GRANT
-- 55 2 1 2 KEY (fc00a92d9bd9) X GRANT
-- 55 2 2 1 KEY (bc009dbece03) X GRANT
-- 55 2 1 1 KEY (bc004820585b) X GRANT
-- 55 2 3 1 KEY (bd00770e8a50) X GRANT
-- 55 2 1513959356 0 TAB Sch-M GRANT
-- 55 2 2 1 KEY (f500783daf5c) X GRANT
-- 55 2 3 1 KEY (f60025843207) X GRANT
-- 55 2 1 3 KEY (bc003d203e1f) X GRANT
-- 55 2 1 1 KEY (f50051d91d3b) X GRANT
-- 55 2 1 2 KEY (9b16f50455d2) X GRANT
-- 55 2 3 2 KEY (cd019d01c693) X GRANT
-- 55 33 0 0 DB S GRANT
-- 55 2 3 0 TAB IX GRANT
thanks a lot for ur time dave....im tempted to use this..i'll do some testing and go for it... ya i cant run away from select into...infact that was the thing i was trying earlier while generating the query string...so im kinda ready for that...
cheers
nitin
|||You can do below:
create table #tbl( /* put fixed columns here. those that are not decided dynamically )
-- use ALTER TABLE to add the columns dynamically
exec('alter table "#tbl" add c1 int, c2 int...')
select ...
Note, however that this has performance implications due to the excessive amount of recompilations triggered due to the schema changes. In above case, pretty much every line will trigger recompile of the entire SP (SQL2000) or statement (SQL2005).
I have developed an application that utilize SQL server 2005. The application is a clickonce application and launched from the IIS. The problem is that when im trying to connect from outside the company i get the Error that the server does not accept remote connections.
I have used the surface manager and setup the protocol to accept remote connections. Nothing happens.
I have changed the IIS to use the configuration screens under the .NET configuration in IIS. Nothing helps there either.
I have open the ports 445 ,1433, 1434
Anyone knows what i could do to get the connections working on the %%%%%%
I'm moving your question to the data access forum where you should get an answer.|||Can you post the exact error msg here?
Is your SQL instance the default instance? Can you check whether it's listening on NP and/or TCP? and TCP port number? You need to run SQL Browser. I assume you opened port UDP 1434, not TCP 1434.
or at best, collect information according to
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=362498&SiteID=1
Thanks,
|||Have a look at:I'm trying to attach to a database that's stored in my D: partition on my disk. The disk has partitions C: D: E: F:
But when I choose "Attach", and open the requester to select the database-file, only drives C: and E: are shown.
All other programs lists the partitions right.
Where have D: and F: gone to ?
YOu will only see fixed drives of the server, so if you are remote administering the server, you won′t see the local drives in the folder dialog.HTH, Jens K. Sü?meyer.
http://www.sqlserver2005.de|||
All partitions are on the same physical disk. Why isn't E: also hidden then ?
And how can I be able to use drive D: ? The drives are still hidden either if I administer the server by Remote Desktop, or if I work directly on the computer.
|||My guess is that this is a permissions issue. SQL Express runs in the context of Network Service, which is a limited user account. Check the NTFS permissions on your other drives and determine if the Network Service account has appropriate permissions.
Mike
Hi,
I have a SAN and configuring a cluster on SQL 2005. I initially created a Quorum drive when setting up the cluster and now added 4 more drives to the physical node but when I try to install SQL that drive cannot be located.
Do we need to create all the drives when installing the cluster or what is the way to add the drives later on.
Thanks
Anup
I remember that adding a drive for sql requires the drive to be added to the resource group, I assume you have to create a resource group prior to install?|||It's one thing to add them locally - did you add them to the cluster via Cluster Admin as well? There's a procedure for adding drives after the cluster is already configured. Are they seen in CluAdmin? If not, you did not add them properly.
You also didn't mention MS DTC. That needs its own drive now as well in its own group with an IP, name, and DTC resource.
|||Thanks All.
With some troubleshooting and tips from this forums the cluster is up and running with SQL 2005. However I do not understand whi we need a seperate drive for MSDTC cant we share the quorum drive to do this. Can you explain how to size this like perf issues, disks needed etc.
Thanks
|||
For failover clusters, you always need to think in terms of "units of failover". If you put the MSDTC data on the same drive as the quorum, you would need to tie MSDTC to the cluster service. You then create dependancies between MSDTC and the cluster service that shouldn't be there.
In general, you want to think in terms of a service and its associated resources. That bundle needs to be independant from all other bundles on the cluster so that it can move from node to node independant of other bundles (resource groups).
For disk storage, the unit of availability is the physical disk (or LUN in the case of a SAN array). You can't have one partition of a disk mounted to one node and another partition mounted to another node.
So, it's not a matter of capacity or throughput or perf issues, it is an availability issue.
|||Kevin Farlee wrote:
For failover clusters, you always need to think in terms of "units of failover". If you put the MSDTC data on the same drive as the quorum, you would need to tie MSDTC to the cluster service. You then create dependancies between MSDTC and the cluster service that shouldn't be there.
Just to reinforce - this is not a recommended configuration. It only existed with Windows 2000 because of comclust and that has (thankfully) been taken away in W2K3. Always put MS DTC in a separate group.
In the cases where MS DTC is heavily used (such as BTS), it could potentially affect the availability of the quorum disk (i.e. if it gets filled up with DTC log). You don't want to go there.
|||
Assuming you added the new drives as a disk resource in cluster manager, did you also make those new drives a Dependency of SQL Server? If not, SQL Server will not be able to see them.
|||
Thanks. Now I am getting to understand the concepts. However one last question if I have a active/active cluster then do i need to setup 2 MSDTC groups one in each node?
I appreciate all the help throughout .
|||
No, one DTC per cluster. It is shared with everything else in the cluster.
You will need separate dedicated disks and such for your other SQL instance(s) though.
Hi
I am currently trying to get a script component to write data into a openedge database.
I am stumped by the below message.
Validation error. Bulk Load ProjPeriod Bplan: Script Component [9378]: System.Data.Odbc.OdbcException:
ERROR [HYC00] [DataDirect][ODBC Progress OpenEdge Wire Protocol driver]Optional feature not implemented. ERROR [HY000] [DataDirect][ODBC Progress OpenEdge Wire Protocol driver][OPENEDGE]Server rejects connection on attach.
ERROR [IM006] [DataDirect][ODBC Progress OpenEdge Wire Protocol driver]Driver's SQLSetConnectAttr failed. ERROR [HYC00] [DataDirect][ODBC Progress OpenEdge Wire Protocol driver]Optional feature not implemented. ERROR [HY000] [DataDirect][ODBC Progress OpenEdge Wire Protocol driver][OPENEDGE]Server rejects connection on attach.
ERROR [IM006] [DataDirect][ODBC Progress OpenEdge Wire Protocol driver]Driver's SQLSetConnectAttr failed. at Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost.HandleUserException(Exception e) at Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost.AcquireConnections(Object transaction) at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostAcquireConnections(IDTSManagedComponentWrapper90 wrapper, Object transaction) Package.dtsx 0 0
The two areas’ that I have found that might be causing this is transaction and codepage, however I have tried setting these appropriately with no success.
Any help would be much appreciated.
Cheers,
Ken C
Does the connection work correctly if you use the connection manager from a DataReader source?
|||
Hi jwelch
As far as I can tell, using this connection from connection manager with Datareader does work.
I created the connection and used in a datareader and it displayed the field names from the supplied sql.
Cheers,
Ken
|||
Hi Again
A bit wierd.... I changed the Fetch Array Size, in the odbc connection to 0 .....this seemed to remove the error from ssis. (This seemed to work for somone else connecting to Oracle db)
Since then i have put the value back to 50 and the error messege has not returned ?
Will develop futher and post back
Cheers
hi
I want to know file's list of SQLSERVER2000 Driver
I write a program , it connect to sqlserver2000 in Eternet and
client side of software install in every computer but i have to install sqlserver2000 in every client pc
how can i connect to SQLServer2000 Server (in a network) with out installing Sqlserver2000 ?
Tanks
Hi,
you just have to install the MDAC Components to access SQL Server.
HTH, Jens K. Suessmeyer.
http://www.sqlserver2005.de|||
Depending on your exact needs in addition to MDAC you may also want to try SQL Server Native Client, JDBC, or ADO.NET.
Check out http://msdn.microsoft.com/data for more info.
I need some help and feedbacks from other users that are currently running Reporting Services using DB2 as a database. I have been testing OLE DB and ODBC from Microsoft and IBM, and recently DB2 .Net Data provider from IBM.
We are currently running DB2 on z/os v 7, gateway v.8 fixpack 10. We will mainly be calling Stored Procedures on the DB2 and passing recordsets back to the client.
First I installed IBM DB2 ODBC DRIVER v. 7.01.00.88, but I did not get this too work with reporting Services calling Stored Procedures, so I had too upgrade the driver to v8.01.10.812 to get it to work.
I have been in contact with Microsoft and they recommend using Microsoft OLE DB Provider for DB2 (30.sept 2005). I have tried too get this driver too work but I have problems configuring it. I think that it has something too do with some packages that the driver tries too create on DB2. I get error messages both on the DB2 and in the Data access Tool that runs a wizard setting up the driver. '—The package creation process has failed on resource "mytest", Data description mismatch SQLSTATE:HY000, SQLCODE: -292'
IBM have also developed a IBM DB2 Data provider for .Net Framework 2.0. This is the only driver that I can use too browse the database in the Server Explorer in VS. If you edit the RSReportDesigner.config file you can also use this driver in Report Server projects. Just add <Extension Name="DB2" Type="IBM.Data.DB2.DB2Connection,IBM.Data.DB2, Version=9.0.0.2, Culture=neutral, PublicKeyToken=7c307b91aa13d208" /> too the data section of the xml file.
So too wrap it all up. Which provider should I use? Are there any recommendations out there?
Thanks
B
well i've a question.
how to get IBM DB2 Data Provider for .Net Framework 2.0
I need some help and feedbacks from other users that are currently running Reporting Services using DB2 as a database. I have been testing OLE DB and ODBC from Microsoft and IBM, and recently DB2 .Net Data provider from IBM.
We are currently running DB2 on z/os v 7, gateway v.8 fixpack 10. We will mainly be calling Stored Procedures on the DB2 and passing recordsets back to the client.
First I installed IBM DB2 ODBC DRIVER v. 7.01.00.88, but I did not get this too work with reporting Services calling Stored Procedures, so I had too upgrade the driver to v8.01.10.812 to get it to work.
I have been in contact with Microsoft and they recommend using Microsoft OLE DB Provider for DB2 (30.sept 2005). I have tried too get this driver too work but I have problems configuring it. I think that it has something too do with some packages that the driver tries too create on DB2. I get error messages both on the DB2 and in the Data access Tool that runs a wizard setting up the driver. '—The package creation process has failed on resource "mytest", Data description mismatch SQLSTATE:HY000, SQLCODE: -292'
IBM have also developed a IBM DB2 Data provider for .Net Framework 2.0. This is the only driver that I can use too browse the database in the Server Explorer in VS. If you edit the RSReportDesigner.config file you can also use this driver in Report Server projects. Just add <Extension Name="DB2" Type="IBM.Data.DB2.DB2Connection,IBM.Data.DB2, Version=9.0.0.2, Culture=neutral, PublicKeyToken=7c307b91aa13d208" /> too the data section of the xml file.
So too wrap it all up. Which provider should I use? Are there any recommendations out there?
Thanks
B
well i've a question.
how to get IBM DB2 Data Provider for .Net Framework 2.0
