Post a reply
1 posts • Page 1 of 1
- Windows XP
- Visual Studio .Net
- MS OFFICE OUTLOOK
- 1. Identify inserted rows
Hi,I'd like to insert several rows into a table in a multi-user environment andafterwards uniquely identify the new rows without having unique keys definedin the table (except "ID INT IDENTITY PRIMARY KEY").I know this is not possible in SQL Server 2000. Does anybody know if SQLServer 2005 will return some unique row identifier after INSERT has beenexecuted enabling me to safely identify the created ID column values?TIA,Axel Dahmen
- 2. Good Names???
Hi All,I would like of some options about names:Which are good names:For tables:tblCustomer or only Customer or other.For Store procedure:spAdd only SPAdd or StoreProcedureAdd or Add or other.For Data Base:dbCustomer or only Customer or other.and else...Thanks
- 3. Listing all fields where most are duplicate data
I need to do a query that will return records where all data is the sameexcept one field.For instance, a client table has a name, address, city, state, zip,comments. Because the database wasn't really set up correctly, many recordshave the same name, addres, city, state, zip, but only the comment isdifferent. I know I can do a distinct:select distinct name, address, city, state, zip from ClientTablebut I need to return all fields with the different comments so I can give alist to the users for them to determine which records should be kept. Itried:select * from ClientTable where name in (select distinct name, address,city, state, zip from ClientTable)but I get an error: Only one expression can be specified in the select listwhen the subquery is not introduced with EXISTS.So I tried Exists:select * from ClientTable where exists (select distinct name, address, city,state, zip from ClientTable)and I get all records, not just the ones where the comment field is the onlydifferent data.Thanks for your help.
- 4. Character Function for?
Hello,Is there a function to tell me how many instances of a specified character there are in a given batch of characters. For instance, how many of the character [a] are found in the word [Alabama]?Thanks!Any help would be greatly appreciated.
Can't invoke object from sql sp_oacreate
by Rich Norgaard » Sat, 29 Nov 2003 06:15:10 GMT
Hello all! I've been troubleshooting this problem for quite some time now with no success. I am experiencing some sort of security issue trying to use the sp_oacreate family of stored procedures. I am testing this on a managed hosting box on which I have an Admin account and sa priviledges, but am restricted via security policies on certain system configs. I'm pretty sure they've done something funny and misconfigured something. Here's the sql code first of all:DECLARE @pid int, @hr int, @path varchar(500), @src varchar(1000), @desc varchar(1000)SET @path = 'd:\myscript\test.wsf'EXEC @hr = sp_OACreate 'WScript.Shell', @pid OUTIF @hr <> 0BEGIN EXEC sp_OAGetErrorInfo @pid, @src OUT, @desc OUT SELECT 'oacreate',hr=convert(varbinary(4),@hr), Source=@src, Description=@desc RETURNENDEXEC @hr = sp_OAMethod @pid, 'Run', NULL, @pathIF @hr <> 0BEGIN EXEC sp_OAGetErrorInfo @pid, @src OUT, @desc OUT SELECT 'oamethod',hr=convert(varbinary(4),@hr), Source=@src, Description=@desc RETURNENDEXEC @hr = sp_OADestroy @pid It works on two of three servers from this same provider, all of which are 'supposed' to be configured exactly the same. It also works on all of our local servers here. I am for the time being executing this in Query Analyzer with sa. Here's the result:oamethod 0x80070002 NULL NULL At first I thought is was restrictions at the DB level so I set all related system stored procedures to everyone and it still failed. Next we looked at local security policies and noticed that 'Create global objects' and 'Impersonate a user after authentication' were not set, so we got our provider to set the effective settings to Service, System and Administrators. Still no luck. The error number results on the call to the method Run but searches on this revolve around licensing and I couldn't find anything specific enough to help. The script itself is simply an ADO call to the DB with a comment so I can see it in Profiler, here's the code for my .wsf:<job id="myjob"><script language="VBScript">Set db_conn = Createobject("ADODB.Connection")db_conn.Open MY_CONN_STRINGdb_conn.Execute("-- TEST - IT WORKED IF WE SEE THIS")</script></job> Any help would be super appreciated; this is definitely a tricky one! Thanks and have a super day!
1.Error creating object SQL 7.0 using sp_oaCreate
Hi,I am trying to troubleshoot an issue that came up suddenly on a SQL Server 7 that I support.There is a sp that runs every five minutes and one of the functions in the sp uses the sp_oaCreate to create and object which returns some free disk space information. This sp has been working fine for over six years now and all of a sudden, last week, it started failing. After some debugging, I found that the following call is failing with the attached error; exec @hr = sp_oaCreate 'CqDiskSpace.clsDiskSpace', @ds OUTPUTif @hr <> 0beginset @ErrMsg = 'Could not create CqDiskSpace.clsDiskSpace -- error ' + cast(@hr as varchar(15))goto EHendAnd the error is:Could not create CqDiskSpace.clsDiskSpace -- error -2147221164.I've been searching google and other things and found some information on this forum with a similar error but the odd thing is that it it was working fine and there was nothing done to the system at that time.I should mention that I can create the object from outside the stored procedure with no problems. It is SQL Server 7.0 7.00.961.Can anyone possibly point me in a direction of what to look for?Thanks a bunch
2.Handling Script Timeout when invoking ActiveX Object involving ADODB from ASP2.0 pages- SQL Client setting
Sorted at last!For educational purposes, I would like to advise the root cause of theproblem!The SQL Client Network Utility on the remote SQL Server 2000 SP3a on Windows2003 server (same network segment and domain as the WinXP) was set to useTCP/IP followed by Named PipesI had to remove Named Pipes from the protocol enabled (it having a lowerpriority than TCP/IP doesn't make a single bit of difference. I amfowarding this info to the SQL newsgroup, just in case anyone is interestedand/or if it is a SQL Client BUG? I had similar problems before withMicrosoft Content Management Server(MCMS) 2002 SP1a, where by I had to useTCP/IP (instead of Named Pipes) to get MCMS 2002 Scripted Site Deploymentusing SDO working. Changing the prioirty makes no difference and had toresolve using an alias that is TCP/IP or removing Named Pipes from theenabled protocol"Yan-Hong Huang[MSFT]" < XXXX@XXXXX.COM > wrote in messagenews: XXXX@XXXXX.COM ...> Hi Patrick,>> Thanks very much for the detailed information. I have also got your 2> emails. :)>> Yes, after following the steps, I did reproduce the same error. The error> message in the asp page on XP box is:>> Transactional Web Page>> --------------------------------------------------------------------------
3.sp_OACreate on .NET COM object
I am trying to create an instance of a .NET pooled COM object from a storedprocedure using the sp_OACreate procedure. I am getting an "Invalidpointer" error. I have registered the COM object using the regsvcs, and Iknow I have the name of the component correct because I get a "notregistered" error if I change it to something non-existent.I have successfully used sp_OACreate to create an instance of a COM objectbuild with VS6, and called methods on it without problems.Anyone have any idea what might be the problem?Thanks
4.Remote Object Instantiation from TSQL proc using OLE (sp_OACreate)
How to ? pleasethanks
5.-- Class not registered -- sp_OACreate 'MSScriptControl.ScriptControl', @object OUTPUT,4
I'm successfully using the 'MSScriptControl.ScriptControl' from within a sqlstored procedure.[ I use this object to evaluate dynamically generated mathematicalexpressions (stored in db tables):A=5B=3C = 3 * (A+ B)-- so far so good ]To enhance security and prevent that potential memory leaks could affect thesql server process I'm looking for a solution to run the object outside thememory space of sql server.According the books this should be possible by adding context level 4parameter to sp_OACreate. Unfortunately for one or another reason this seemsnot to work with this object? Can anybody confirm this is normal behaviorand/or if there is a workaround for this? Any other object with an evalmethod could also suite (it does not have to be a full blown scriptinglanguage)-- These three lines work fineEXEC @hr = sp_OACreate 'MSScriptControl.ScriptControl', @object OUTPUTEXEC @hr = sp_OACreate 'MSScriptControl.ScriptControl', @object OUTPUT,5EXEC @hr = sp_OACreate 'MSScriptControl.ScriptControl', @object OUTPUT,1-- Creating the object in context level 4 does not work however-- Get following error msg back----- Class not registeredEXEC @hr = sp_OACreate 'MSScriptControl.ScriptControl', @object OUTPUT,4Thanks for any help,Bart
6. Cant create object using sp_OACreate
7. Execute Persmission denied on object 'sp_OACreate'
8. The object invoked has been disconnected from its clients
Post a reply
1 posts • Page 1 of 1
Return to MS SQL SERVER
Who is online
Users browsing this forum: No registered users and 30 guest