|
Hi, This is a repost of mine from Lounge.
I would like to know the implications of using dynamic sql with in a stored procedure, how it affects execution plan, what is the pros and cons of EXECUTE IMMEDIATE, the oracle native dynamic sql execution command.
please give me pointers to relevant resources.
Cheers,
Venkatraman Kalyanam
Chennai - India
"Being Excellent is not a skill, it is an attitude"
|
|
|
|
|
I can't seem to figure out/ find a website that shows me how to setup my own sql server. I am using services provided by http://no-ip.com[^]. What port do I need to forward? (I'm using the linksys Cable/Dsl Router) Do I need to configure anyything on the server itself?
Steve
McLenithan
Is Bert Evil?
|
|
|
|
|
What problems are you having? Are you able to connect to your box from the outside at all? That should be your first step. The router you are using will allow you to access one of your computers internally, but that is disabled by default (thankfully so). A router is routing traffice between your local (private/internal) network and the outside world. By default, people should not be able to access your machine on the inside from the outside.
I believe that the linksys routers have a setting for a DMZ box. This machine would be available from the outside if you enable this setting on your router (look for DMZ in your router settings). I think to get to the settings of your router, you open a browser to 192.168.1.1. The default login is an empty username and "admin" as the password--unless you've changed this from the factory defaults.
Once you do get the router set up properly, you will probably have to enable the Sql Server port on your router as well. I wouldn't recommend this if you are deploying a website on it, though. It is less secure this way. If you only allow DB traffic from your web box, you'll have a more secure situation.
Now, as far as getting this all to work with your dynamic DNS service, I don't know anything about it. You should probably contact the service provider and see what they suggest. I'm sure you're not the first person who wanted to do this. In fact, once you do figure it out, you should post an article on CP with the steps you took to accomplish this task.
I hope this helps. Let me know if you have other questions.
-Matt
------------------------------------------
The 3 great virtues of a programmer:
Laziness, Impatience, and Hubris.
--Larry Wall
|
|
|
|
|
Thanks for the tips.
Steve
McLenithan
Is Bert Evil?
|
|
|
|
|
CREATE PROCEDURE UpdateCompanyProfile
(
@AccNo varchar(50),
@CompanyName varchar(50),
@Address1 varchar(50),
@Address2 varchar(50),
@City varchar(50),
@State varchar(50),
@ZipCode varchar(50)
)
AS
UPDATE tblCompany SET
CompanyName = @CompanyName,
Address1 = @Address1,
Address2 = @Address2,
City = @City,
State = @State,
ZipCode = @ZipCode
WHERE AccountNo = @AccNo
RETURN
my tables are:
tblCompany
---------
CompanyName (primary Key)
AccNo
Address1
Address2
City
State
ZipCode
tblCustomer
-----------
Name
Email
Phone
Job
CompanyName (foreign key)
when i try to update the CompanyName from the site, i get this error message:
UPDATE statement conflicted with COLUMN REFERENCE constraint 'FK_tblCustomer_tblCompany'. The conflict occurred in database 'test', table 'tblCustomer', column 'CompanyName'. The statement has been terminated.
how do i edit the sp above to update the CompanyName column in my customer table as well?
Laine
|
|
|
|
|
Change your constraint FK_tblCustomer_tblCompany to include:
on update cascade
General its considered good practice to use a primary key which isn't subject to change.
|
|
|
|
|
Ixpah wrote:
General its considered good practice to use a primary key which isn't subject to change.
Agreed... There should be some other type of identifier for the company name (even if it is a field that autoincrments as you add company names) that is used as the primary key and the forigen key in the other.
In the example given, once the company name is used somewhere, what happens if it ever changes?
Paul Watson wrote:
"At the end of the day it is what you produce that counts, not how many doctorates you have on the wall."
George Carlin wrote:
"Don't sweat the petty things, and don't pet the sweaty things."
Jörgen Sigvardsson wrote:
If the physicists find a universal theory describing the laws of universe, I'm sure the a**hole constant will be an integral part of that theory.
|
|
|
|
|
Hello,
I have a problem with ASP.net page to access an Access database over the network. I get following error:
Microsoft OLE dB Provider for ODBC Drivers (0x80004005) [Microsoft][ODBC Microsoft Access Driver] The Microsoft Jet database engine cannot open the file '(unknown)'. It is already opened exclusively by another user, or you need permission to view its data.
I tried to add user ASPNET to IIS computer but I had to add it though Active directory and it did not work. I can connect from the computer which is installed .net framework and has ASPNET by installing to this database over the network.
Is is same to add user through active directory and having ASPNET user by installing framework?
If you have any idea, it would be great!!
thanks
|
|
|
|
|
Is there a *.ldb file of the same name in that folder?
"Do unto others as you would have them do unto you." - Jesus
"An eye for an eye only makes the whole world blind." - Mahatma Gandhi
|
|
|
|
|
Hi,
Doas anyone use anr O/R mapping tool for .NET platform. I searched the Internet and found two tools only" ObjectZ (www.mooongoose.com) and PORT (www.potis.com.pl). Do you have any experience in using any of them ?
Ava
|
|
|
|
|
Anyone know any codegenerator that can do the following:
1) generate code from asp like templates (eg <% some code %>)
2) itterate through the RELATIONS of a database , most generators ive seen dont implement this.
(both features must exist otherwise im not interested :P)
any URLs would be good
//Roger
|
|
|
|
|
Not sure about your question (first one). I once wrote some ASP to generate ASP pages using the FSO which worked fairly well. Maybe a starting point if you have to roll your own?
Davy
Blog for Software Testing, Bugs, Quality, Security and Stability - www.latedecember.com
News From Angus, Scotland - The Angus Blog My Personal Blog - Homepage.
|
|
|
|
|
no , what i ment with asp like template was that i want to be able to script the output of the generator in the same way as you script an asp page , but instead of outputting the result to a browser , i want to output it to a file.
(and not make the asp'ish code itslef write to a file via fso)
ive seen lots of template based generators , but none that actually can handle relations in a database...
//Roger
|
|
|
|
|
llblGen will follow relations.
take a look at www.sd.nl
Take Care
|
|
|
|
|
One of these darn things I keep forgetting is how
to insert a SQL NULL value into a column using ADO
in VC++ code!
Grr... I do not want to use a SQL statement or
a stored procedure!! That is too easy. Here is what
I am looking for, something like:
pRst->Fields->Item[colAdminSvrPwd]->Value = (LPCSTR)NULL;
pRst->Fields->Item[colAdminSvrPwd]->Value = (LPCSTR)0;
pRst->Fields->Item[colAdminSvrPwd]->Value = (LPCSTR)'\0';
Keep in mind that these are VARIANT types so
conversions are applied implicitly and the above
code does not work.. so what does?
Thanks in advance!
|
|
|
|
|
Try
pRst->Fields->Item[colAdminSvrPwd]->Value = _variant_t();
|
|
|
|
|
Do you have to declare a variant variable first
then it's type? I mean is that what _variant_t()
is doing? I will try this out! Currently I am
using the code below and it works:
_variant_t vtNull;
vtNull.ChangeType(VT_NULL);
pRst->Fields->Item[colAdminSvrPwd]->Value = vtNull;
|
|
|
|
|
ADO treats VT_EMPTY and VT_NULL as the same (SQL NULL).
|
|
|
|
|
hi all ,
i am not a newbie (i've been programming for 9 years) , but i am a newbie to .NET.
i have 2 questions a bout the dataset...
1)briefly , i have 3 simple queries to execute and insert the 3 tables to the dataset . can i execute all 3 of them at the same time (or one after the other) , but sending the SQLs to the server at a single time and get the result of all 3 queries in the same time (instead of executing 3 Queries...)? (i only read the data , so do i have to use the DataAdapter or can i just use the Reader?)
2)i want to update the table with the changes maded to the dataset's Table , but i have 21 keys (its not actually 21 keys , but its 21 fields that are critical to determing which rows to update) , so how can i make the DAtaTable update my rows (its a lot of rows , i dont want to execute so much UPDATE commands...
thanks.
|
|
|
|
|
Have you tried the DataAdapter.Update(DataSet) method?
|
|
|
|
|
Hi..
I want to compact and repair an Access 2000 file
i used DAO ( DBEngine.CompactDatabase )
but i get an error about an unrecognized format or corrupted file.
I think that compact and repair could be done by ADOX.
can any one tell how ???
|
|
|
|
|
I am writing a program which runs just fine in the computer where I am developing it. The OS is Windows XP. Since I wanted someone else to see my program I tried to run it on an old laptop that I have which runs on Windows 95. Unfortunately I got an error generated with _com_error which says:
Error Number: 0x80040154
Error Message: Class not registered.
This is part of the code
// Initialize the COM environment
if( FAILED( hr = ::CoInitialize( NULL ) ) )
{
_com_issue_error( hr );
return FALSE;
}
// Create a connection with the database
// THE PROGRAM FAILS RIGHT HERE
if( FAILED( hr = m_pConn.CreateInstance( __uuidof( Connection ) ) ) )
{
_com_issue_error( hr );
return FALSE;
} // end if
m_pConn->Open(L"Provider=Microsoft.Jet.OLEDB.3.0;\
Data Source=SomeDbFile.mdb;",
L"", L"", adOpenUnspecified );
I tried to register msado15.dll with regsvr32 in the computer which runs on windows 95 but that didn't work. Is there anything that I can do to to make the program run in Windows 95?
none
|
|
|
|
|
|
Thank you for your reply. I have a dial-up connection so it will take me a while to download the required files and see if they will work, but this is a very good start thanks to you.
none
|
|
|
|
|
hello @all,
i would like to tie up an access data base to a java program. i would like to make this gladly over JDBC and ODBC (is so desired). can someone explain to me as simply as possible, how i can make that?
thank you!
sunny
|
|
|
|