|
Matthias Steinbart wrote:
The only thing that comes into my mind is to restrict direct table access and allow the developer only to mess around with the view
The conditions that make up the view get precompiled so that the query operates marginally faster than sending the query direct.
You base the view on multiple tables which means you can access normalised data in a denormalised form as if it were one table.
Unioning many physical tables in to one large view. On one project I work on there is a physical table for each year because the quantity of data is so large. It can be accessed through a view which represents the whole set. IIRC, this is called horizontal partioning.
I don't use views much, so there may be other uses. I tend to use stored procedures for everything.
My: Blog | Photos | Next SQL Presentation
WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and More
|
|
|
|
|
Hi Colin,
thanks for your reply. I actually didn't know that views get compiled.
Colin Angus Mackay wrote:
IIRC, this is called horizontal partioning.
What does IIRC mean? I made up "If I remember correctly"... Is this correct?
I've just reviewed a pretty large and well functioning Web-App and I've seen that those people as well used stored procedures for just anything. Made sense to me.
BTW, I yesterday read your article about SQL injections. Very interesting topic and well explained. You got my 5 on that one
/matthias
I love deadlines. I like the whooshing sound they make as they fly by. [Douglas Adams]
|
|
|
|
|
Matthias Steinbart wrote:
What does IIRC mean? I made up "If I remember correctly"...
Yes, that's right - I shouldn't used these abbreviations, especially as I get on at others for using "U" and "R" and other forms of text/sms-speak.
Matthias Steinbart wrote:
I yesterday read your article about SQL injections. Very interesting topic and well explained. You got my 5 on that one
Thanks.
My: Blog | Photos | Next SQL Presentation
WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and More
|
|
|
|
|
I've done the following HTA to connect to MyDB.MDB and open all records in MyTable, the connection seems ok, but when I go to display it in a span (DBData), it get the following error:
Object required: 'DBData'
Any help with this would be appreciated.
<html>
<head>
<title>Database Connection</title>
<HTA:APPLICATION ID="objDBCreate" APPLICATIONNAME="DBCreate" SCROLL="no" SINGLEINSTANCE="yes" WINDOWSTATE="normal">
</head>
<style>
BODY
{
background-color: buttonface;
font-family: Helvetica;
font-size: 8pt;
margin-top: 2px;
margin-left: 8px;
margin-right: 3px;
margin-bottom: 3px;
}
</style>
<script language=VBScript>
set conn=CreateObject("ADODB.Connection")
conn.Provider="Microsoft.Jet.OLEDB.4.0"
conn.Open "MyDB.MDB"
set rs=CreateObject("ADODB.recordset")
rs.Open "SELECT * FROM MyTable", conn
rs.MoveFirst
strHTML = "<table border='1' " & _
"style='border-collapse: collapse' " & _
"bgcolor='white' bordercolor='black' " & _
"width='50%' id='Table1' >"
Do Until rs.EOF
strHTML = strHTML & "<tr>"
strHTML = strHTML & "<td width='50%'>" & _
rs.Fields.Item("Computername") & "</td>"
strHTML = strHTML & "<td width='50%'>" & _
rs.Fields.Item("IPAddress") & "</td>"
strHTML = strHTML & "</tr>"
rs.MoveNext
Loop
strHTML = strHTML & "</table>"
rs.Close
conn.Close
Set rs=Nothing
Set conn=Nothing
DBData.InnerHTML = strHTML
</script>
<body>
<span id="DBData"></span>
</body>
</html>
|
|
|
|
|
try to declare the body and span before the script
<pre><html>
<head>
<title>Database Connection</title>
<HTA:APPLICATION ID="objDBCreate" APPLICATIONNAME="DBCreate" SCROLL="no" SINGLEINSTANCE="yes" WINDOWSTATE="normal">
</head>
<style>
BODY
{
background-color: buttonface;
font-family: Helvetica;
font-size: 8pt;
margin-top: 2px;
margin-left: 8px;
margin-right: 3px;
margin-bottom: 3px;
}
</style>
<body>
<span id="DBData"></span>
<script language=VBScript>
set conn=CreateObject("ADODB.Connection")
conn.Provider="Microsoft.Jet.OLEDB.4.0"
conn.Open "MyDB.MDB"
set rs=CreateObject("ADODB.recordset")
rs.Open "SELECT * FROM MyTable", conn
rs.MoveFirst
strHTML = "<table border='1' " & _
"style='border-collapse: collapse' " & _
"bgcolor='white' bordercolor='black' " & _
"width='50%' id='Table1' >"
Do Until rs.EOF
strHTML = strHTML & "<tr>"
strHTML = strHTML & "<td width='50%'>" & _
rs.Fields.Item("Computername") & "</td>"
strHTML = strHTML & "<td width='50%'>" & _
rs.Fields.Item("IPAddress") & "</td>"
strHTML = strHTML & "</tr>"
rs.MoveNext
Loop
strHTML = strHTML & "</table>"
rs.Close
conn.Close
Set rs=Nothing
Set conn=Nothing
DBData.InnerHTML = strHTML
</script>
</body>
</html>
|
|
|
|
|
How i can insert a crypted string into a field of record(its type is Binary)using Query Analyzer?
|
|
|
|
|
Hi everyone,
My problem:
I have a DB where primary/foreign keys are of type Guid.
I was trying to filter in my report all records where foreign key is Guid X.
Somehow, no records are selected!
Observing the sql query reveals:
....WHERE `tbA`.`ParentID`='{guid {B44D446E-F50C-43DD-882C-66A6130D0EEF}}'
What am I doing wrong?
I'm using CR (Ver. 9) for Visual Stodio .Net 2003
|
|
|
|
|
I am using ODBC.Net and am having a problem executing some sql. Why is it that if my statment has a 'go' batch terminators i can not execute them thru ODBC.NET. I need to remove the go and it works. But some of my Sql does not like it if I remove the go. I have no control over the sql so I can not recode my sql.
Can someon tell me why this is and is there a way around the go.
Thanks
|
|
|
|
|
GO is not a TSQL command. SQL server does not recognize it; it is a SQL tool command which tells the tool to send the pending commands to the server for processing. If your statements do not work without the GO, then you will have to send them to the server in separate commands just like query analyzer.
|
|
|
|
|
After I posted this and had a brief discussion with a co-worker I realized this. My solution is to parse the files into a collection of code blocks and execute each block individually then go to the next file.
Thanks for the help
|
|
|
|
|
hi everyone, howz life?
I have a problem, working with list boxes.
My requirement is,
I want to move the contents of one list box to the other list box, on the button click event. In ASP.
Please, Can anyone help me out in this.
|
|
|
|
|
|
First, is it possible to connect to MSDE 2000 server from another computer with ado.net? I only managed to connect from the same computer that is running the server.
If it is possible, what aspects should i take into account (or simply how should i do it). And is it possible to use an IP adress in the connectionstring?
What about firewalls?
I have never done client/server applications and im trying to build one to learn something new.
If you need more info please ask, and PLEASE anserw.
R
Johan
|
|
|
|
|
Yes, it's possible. New MSDE installs, by default, have the network protocols disabled. This is a security mitigation - if the server cannot be reached over the network, it cannot be hacked remotely.
To enable network protocols, use the Server Network Utility, svrnetcn.exe in Microsoft SQL Server\80\Tools\Binn. You can also configure this at install time by setting the DISABLENETWORKPROTOCOLS property to 0.
See this article[^] for more.
Stability. What an interesting concept. -- Chris Maunder
|
|
|
|
|
Hi there,
I have a table with a column that I need to fill in with consecutive numbers (the field, currently, has the same number for all the rows).
I want to do this in the SQL Server Query Analyzer.
Could you please give me some hints about how can I do this?
Thanks a lot,
Cristina
|
|
|
|
|
Hi,
Try this ..........
<br />
DECLARE @Counter as int<br />
SET @Counter = 1<br />
<br />
DECLARE @ID as int<br />
<br />
DECLARE Table_Cursor CURSOR FOR<br />
SELECT TableID<br />
FROM TableName <br />
<br />
<br />
OPEN Table_Cursor<br />
<br />
FETCH NEXT FROM Table_Cursor INTO @ID<br />
WHILE @@FETCH_STATUS = 0<br />
BEGIN<br />
Update TableName<br />
SET<br />
SerialCol = @Counter<br />
WHERE TableID = @ID <br />
FETCH NEXT FROM Table_Cursor INTO @ID<br />
END<br />
<br />
CLOSE Table_Cursor<br />
DEALLOCATE Table_Cursor
I hope this will help u...............:->
Reagrds,
Ritesh
|
|
|
|
|
oops!!!! Forgot to increment @Counter i.e
FETCH NEXT FROM Table_Cursor INTO @ID
WHILE @@FETCH_STATUS = 0
BEGIN
Update TableName
SET
SerialCol = @Counter
WHERE TableID = @ID
SET @Counter = @Counter + 1
FETCH NEXT FROM Table_Cursor INTO @ID
END
Regards,
Ritesh
|
|
|
|
|
I want to execute a procedure in database2 from a procedure in database1. i want to pass a refcursor and get back the resultset. Is it possible to pass cursor variable in RPC? If not please let me know, how it can be done.
Thanks
|
|
|
|
|
how to create a custom data source in sql server 2000 reporting services
|
|
|
|
|
|
greetings
i did alot of work inb vb6 as frontent and sqlserver as backend, as with rapid change in technology, i've decided to swing to vb.net...and started some few job on it, but, i did find myself stuck in the area of using SQl, it seems it takes alot more than i thought in connecting database, viewing, editing, delete and update, unfortunately i cant find a good book that has samples and will realy give me the idea on how to make things easier.
hope u can direct me to anything that would be very usefull for me in understanding record connectivity in VB.net
thanks
cyrus
|
|
|
|
|
How do you make or set a DataSet to have LOCAL properties instead of GLOBAL?
Does it have to be in an exclusive Namespace?
For instance, if I create a new form, new adapter and dataset by design the wizard will give the default beginning name of the new dataset as dataset (which will be dataset1). All of my other forms with their dataset1 will have their properties changed to the last forms properties. All of them.
It is acting like the dataset name is global and adjusts all previous datasets with that particular name accordingly.
And if I create dataset11 or dataset111 or dataset112 it will see it as a subset(?) of dataset1 and all datasets that start with dataset1 or dataset1**** will be changed.
Nothing is changed inside of the SelectCommand just the DataSet.
Solutions?
Any comments would be grateful.
Rick
|
|
|
|
|
By hard coding it into your form.
If you create the DataSet by the wizard or using your toolbox, you create a globally named dataset for your project.
|
|
|
|
|
Hello all,
I'm populating a strongly typed dataset from a local XML file and I would like to establish a master-detail relationship between an element and a table of grandchild elements (and their attributes) which will be bound to a datagrid. Is it possible to do this without clicking through the child element on the datagrid? The XML file will be populated with multiple grandchild elements for each grandparent. Any help would be much appreciated (in C# if possible). Thanks.
-CS
|
|
|
|
|
This Stored Procedure YtdSales requires an input variable @title and the result of the query is returned in another variable @ytd_sales. Notice that the OUTPUT variable is clearly marked in the Stored Procedure. Also note the ParameterDirection attribute.
QES: How to run the Stored Procedure in Sql Query analizer,Pl give a
detail answer of how to get the output parameter value
Create Procedure YtdSales
@title varchar(50), @ytd_sales int OUTPUT
as
Select @ytd_sales=ytd_sales
from titles
where title=@title
return
|
|
|
|