Thank you all very much for your suggestions. MakeItSo -- I tried setting Trusted Connection to False, but still received the same error. Borislav -- I verified that we're executing SQL Server in Mixed Mode. Olaf -- it fails on the Recordset.Open which follows directly after the Connect.Open...
It seems so often that security is what stumps me. My situation:
1. Running an Excel spreadsheet with VBA code that executes a stored procedure on our SQL server.
2. We have an SQL login called abcdefg that has been given Execute rights to the stored procedure.
3. The connection string for...
When you create the spreadsheet, do you force a recalculation prior to saving it? I'm wondering if the cells are marked as dirty, but since you're operating programmatically, nothing is forcing a recalc. If the formulae aren't recalculated, they may report a code (like 001) indicating they do...
Thanks for replying, PHV. Below is the calling section of the VBA code from the macro. I probably worded my post unclearly -- I do use a command to kick off the stored proc (see "adCmdText" in the Options parameter of the Open). Again, the calling seems to work fine -- without making any...
I have a stored procedure that runs on an SQL server (running SQL Server 2008 R2 SP2), updating an SQL table.
I also have an Excel 2007 spreadsheet with a macro that opens a recordset to the SQL stored proc, initiating the update of the SQL table.
Through initial development, there were no...
Thanks, George -- I believe that is going to work just fine. Never knew about the restorehistory table -- always good to learn something new! Thanks again.
Is it possible to determine the backup filename that was used to restore a database? We have a restored database, and there is a question as to which backup file was used for the restore. Is the backup filename kept anywhere in the metadata for the database?
Thanks in advance for your help!
Don't know if you've solved this, but I suggest a minor adjustment to SQLBill's reply...
IF EXISTS (SELECT * FROM tempdb.sys.objects WHERE object_id = OBJECT_ID(U'tempdb.dbo.#Result') AND type in ('U'))
DROP TABLE tempdb.dbo.#Result
Thanks for the response. I think you've pointed me to a viable solution.
Yes, a proc works on objects in its database; however, what I'm trying to do is execute a script located in a central database against ~25 other databases. Using fully-qualified names means, as new databases are added...
JohnDuncanTB,
Just realized that putting the proc in master doesn't solve the issue of changing databases within the proc. Yes, I could access the proc from anywhere else, but my issue is the inability within a single proc to change the active database programatically -- i.e., what I would...
Wow, fast responses -- I was replying to the first response when the second one came in!
I like the idea of the master database, presuming the "powers that be" will go along with it. I'll play with it in test and see what I can come up with.
Thanks again
Thanks for the suggestion. Btw, we have many similar databases because we provide business services to that many separate businesses. Giving each their own database sandbox (so to speak) makes them feel more secure, and makes individualized customizations easier. But the downside is, as I've...
We have a number of stored procedures that need to run against a set of similar databases (~25). Our desire is a single proc that can step through all the databases, probably via a cursor.
Unfortunately, we run into issues because the USE statement does not allow a variable. The only...
The SaveRecord method is passed the RTables Dataset. In the routine that calls SaveRecord, can you slip in some test code to print out the Tablenames in RTables prior to the call? If RTables thinks the table count's some value other than 3, perhaps it will help if you see a list of the tables...
Ugh. One more "oops" during copying the code to the post window:
In the next to the last line, the word "MyTbl" should be replaced by the @tbl variable, making the correct line be:
+ 'set @cmd = @cmd + '' from ' + @tbl + ''' '
Sorry for the two quick corrections -- no peer review in my office!
Here's how I'd do it without having the specifically code the field names. I tested it on a table created exactly as yours (lastname, firstname, gender). Here you would need to specify the Catalog/DB and tablename, but if desired, these also could come from a cursor that reads the...
This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
By continuing to use this site, you are consenting to our use of cookies.