Backing Up sql server

Hey, please forgive me if my infomation source was incorrect. But a friend of mine mentioned to me he had hurd of a program, or code that will make an "install script" of your database. Basically it`ll back up all the tables on your server and if the worst case senario happens then you can run the file like an install script. Is this true? It sounds invalueable! If it isnt true, can someone tell me if it is possible to use code to create a "install script" of your own database? I am using "Microsoft SQL Server".

Thanks in advance John

It's called Enterprise Manager and the documentation is in Books Online. You installed both when you installed Microsoft SQL Server.

Jeff

|||Your firend is right. This is possible to make database backup by scripts. :)|||

so its already installed with Microsofts SQL server?

It is possible to make a page on your site, (that only Admin can access) to create a backup using "Enterprise Manager"

Im finding it hard to find any infomation on Enterprise Manager on MSDN, or am I looking in the wrong place? thanks si!

backgroundcolor in groups

Hello,
There are 3 groups in my table. The second and third group are hidden and
can be drilled down. I have a code to set alternate color for backgroundcolor
of each rows (the first group). The code is "= iif(RunningValue(
Fields!uniqueID.Value, CountDistinct, Nothing) mod 2, "white", "yellow")".
However, some rows can not return alternate color. The report looks like that:
row1 (backgroundcolor is white)
row2 (backgroundcolor is yellow)
row3 (backgroundcolor is white)
row4 (backgroundcolor is white)
row5 (backgroundcolor is yellow)
Does anybody know how to set alternate color for groups?
Thanks!The RunningValue runs based on the details, but you are using the value to
determine the color of groups. If one group contains an even number of
details, the next group will have the same backgroundcolor as the previous
group.
At this point, there is no predefined aggregate function available that
would just count the groups. However, you may want to read this article
which explains the steps necessary to get this working:
http://blogs.msdn.com/chrishays/archive/2004/08/30/223068.aspx
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Jean" <Jean@.discussions.microsoft.com> wrote in message
news:C44CDF74-4505-47C7-B7EB-F53F788698EF@.microsoft.com...
> Hello,
> There are 3 groups in my table. The second and third group are hidden and
> can be drilled down. I have a code to set alternate color for
backgroundcolor
> of each rows (the first group). The code is "= iif(RunningValue(
> Fields!uniqueID.Value, CountDistinct, Nothing) mod 2, "white", "yellow")".
> However, some rows can not return alternate color. The report looks like
that:
> row1 (backgroundcolor is white)
> row2 (backgroundcolor is yellow)
> row3 (backgroundcolor is white)
> row4 (backgroundcolor is white)
> row5 (backgroundcolor is yellow)
> Does anybody know how to set alternate color for groups?
> Thanks!
>
>|||Thanks for your help! It works after I change my code to
"= iif(RunningValue(Fields!uniqueID.Value & Fields!group1.Value,
CountDistinct, Nothing) mod 2, "white", "yellow")".
Jean
"Robert Bruckner [MSFT]" wrote:
> The RunningValue runs based on the details, but you are using the value to
> determine the color of groups. If one group contains an even number of
> details, the next group will have the same backgroundcolor as the previous
> group.
> At this point, there is no predefined aggregate function available that
> would just count the groups. However, you may want to read this article
> which explains the steps necessary to get this working:
> http://blogs.msdn.com/chrishays/archive/2004/08/30/223068.aspx
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "Jean" <Jean@.discussions.microsoft.com> wrote in message
> news:C44CDF74-4505-47C7-B7EB-F53F788698EF@.microsoft.com...
> > Hello,
> >
> > There are 3 groups in my table. The second and third group are hidden and
> > can be drilled down. I have a code to set alternate color for
> backgroundcolor
> > of each rows (the first group). The code is "= iif(RunningValue(
> > Fields!uniqueID.Value, CountDistinct, Nothing) mod 2, "white", "yellow")".
> > However, some rows can not return alternate color. The report looks like
> that:
> >
> > row1 (backgroundcolor is white)
> > row2 (backgroundcolor is yellow)
> > row3 (backgroundcolor is white)
> > row4 (backgroundcolor is white)
> > row5 (backgroundcolor is yellow)
> >
> > Does anybody know how to set alternate color for groups?
> >
> > Thanks!
> >
> >
> >
> >
>
>sql

Awkward question

We have a program built around MS SQL Server, front end is Access project with a lot of VBA code (I think). My programmer moved on and it's unlikely that my superiors can afford a replacement, so I'm trying to learn enough to complete our project. (I work for the USMC, but I'm a manager/Family Therapist.)

Here's my problem/question:

I have a report which can provide details based upon a Command/Battalion selection. I want the user to be able to choose the the Command and a filtered list of associated Battalions for that Command. The choices then populate the report with appropriate data.

I've used a stored procedure to acquire the data for the report and it works beautifully . . . if you have memorized the Commands and Battalions so that you get the right data. I do not understand how to present the Command and Battalion information to user and then pass the user selection to my procedure to get the right data.

Any assistance is very gratefully received!

Peter MorrisThere is more than one way to skin this cat, but none of them are very difficult. Here is a general solution:

I assume (hope) you have a table of Commands with a unique key, and then a table of Battalions with a field linked to the primary key of the Commands table. The battalions table should also include a unique index, since I suppose it is possible for two battalions to have the same name if they are in different commands?

Create a form, (it does not have to have a datasource) and add a combo box do it that uses the Commands table as its source and includes the primary key field.

Create a second dropdown on the form for battalions, but leave it's rowsource blank and leave it disabled by default.

Next, create an ON CHANGE event for the Command dropdown that performs the following actions
1) Set the value of the battalion dropdown to null, clearing any existing value.
2) Set the datasource of the battalion query to an SQL query string similar to this: "select battalion_id, battalion from battalion_table where command_id = " + [command_dropdown].
3) Requiries the battalion dropdown

Add a button to your form that opens the report.

Modify the report to filter on the battalion_ID field from your form.

...yes, the details of this are going to depend on your table structure, but this should give you an idea of the direction you need to go.

Now MOVE IT, MAGGOT!

blindman|||Thanks so much for the reply! I'll get on it as soon as events allow!

Sir, YES SIR!|||Blindman:

OK, took a look at your directions and the structural elements are already in place. Where I get stuck is little things like: "1) Set the value of the battalion dropdown to null, clearing any existing value." I'm a novice, and while I have examples of what the orignal programmer did, when I copied and suitably modified (I thought) his code, it didn't work.

Would you be willing to communicate directly via email so I could show the code I have and perhaps you could point out what I'm missing? [I will not be offended with a no. 8-)]

I certainly appreciate you assistance!

Peter

Awkward phrase

We have code from a manufacturer that we have not changed since the code was
purchased. In the code is some code that appears awkward. (See below) The
suggestion is to use a clause if exists (select blah blah -see below) then.
Given that the manufacturer wrote it this way (three to four years ago), is
there any reason why it may be a bad idea to change this proc... If so would
you use "If Exists..." or something else.
SELECT TOP 1
@.PickTicketID = AMU.PICK_TICKET_ID,
@.PTLine = AMU.LINE_NUMBER, @.SOI = TUID.SOI, @.INVKey = tUID.INV_Key,
@.ItemKey = tUID.ITEM_KEY
FROM
tIU WITH(NOLOCK)
INNER JOIN
tUID WITH(NOLOCK) ON tIU.UID_Key = tUID.UID_Key
INNER JOIN
ASSIGNED_MU AMU WITH(NOLOCK) ON AMU.MU_Key = tIU.MU_Key
WHERE
tIU.MU_Key = @.MUKey
IF ISNULL(@.PickTicketID,'') <> ''
--do stuff
Regards,
Jamie
Ready for this one? Here's your answer, and you're sure to hate it: ;-)
"It depends."
It all depends on how often this procedure is created, and how the
underlying talbes are indexed. The question is this: What problem are
you trying to solve here? Performance? If so, it would be really
strange (and probably a mark of bad indexes) if this procedure performed
poorly. If it's cleanliness and data integrity issues, clearly NOLOCK
would have to go. But doing that could kill the concurrency of the
database -- a lot of developers use NOLOCK as a crutch to avoid dealing
with locks at the expense of potentially munging your data.
Other than that, the code wouldn't be that bad, if there were an ORDER
BY. The strange thing here is the "TOP 1". Do you expect there to be
more than one row? If so, which row should it be? You'd need an ORDER
BY there to tell SQL Server, or you'll get something random. You could
GROUP BY all of the columns, but if you *knew* that there would only be
one row coming off the query, the TOP 1 *could* get you better performance.
HTH
-Dave
thejamie wrote:
> We have code from a manufacturer that we have not changed since the code was
> purchased. In the code is some code that appears awkward. (See below) The
> suggestion is to use a clause if exists (select blah blah -see below) then.
> Given that the manufacturer wrote it this way (three to four years ago), is
> there any reason why it may be a bad idea to change this proc... If so would
> you use "If Exists..." or something else.
> SELECT TOP 1
> @.PickTicketID = AMU.PICK_TICKET_ID,
> @.PTLine = AMU.LINE_NUMBER, @.SOI = TUID.SOI, @.INVKey = tUID.INV_Key,
> @.ItemKey = tUID.ITEM_KEY
> FROM
> tIU WITH(NOLOCK)
> INNER JOIN
> tUID WITH(NOLOCK) ON tIU.UID_Key = tUID.UID_Key
> INNER JOIN
> ASSIGNED_MU AMU WITH(NOLOCK) ON AMU.MU_Key = tIU.MU_Key
> WHERE
> tIU.MU_Key = @.MUKey
> IF ISNULL(@.PickTicketID,'') <> ''
> --do stuff
>
|||Top 1 is just to show the item exists. Thanks for the reply David. There is
a problem with this particular database - it was inherited and the nolock is
a crutch. It would be nice to know exactly where to put in different locking
hints and where to eliminate them completely, but my mere four months here
don't allow me that perspective just yet. In my mind, establishing any
pipelined process and fitting the locking hints to perform based on the
pipelines would be an improvement. Pipeline example might be 1- picking an
item in the warehouse, 2- moving it into production, 3- preparing the
material and 4- packing and shipping it. This is too wide a scope however;
maybe just picking is the single pipeline and maybe just packing and shipping
is another one.
Regards,
Jamie
"David Markle" wrote:

> Ready for this one? Here's your answer, and you're sure to hate it: ;-)
> "It depends."
> It all depends on how often this procedure is created, and how the
> underlying talbes are indexed. The question is this: What problem are
> you trying to solve here? Performance? If so, it would be really
> strange (and probably a mark of bad indexes) if this procedure performed
> poorly. If it's cleanliness and data integrity issues, clearly NOLOCK
> would have to go. But doing that could kill the concurrency of the
> database -- a lot of developers use NOLOCK as a crutch to avoid dealing
> with locks at the expense of potentially munging your data.
> Other than that, the code wouldn't be that bad, if there were an ORDER
> BY. The strange thing here is the "TOP 1". Do you expect there to be
> more than one row? If so, which row should it be? You'd need an ORDER
> BY there to tell SQL Server, or you'll get something random. You could
> GROUP BY all of the columns, but if you *knew* that there would only be
> one row coming off the query, the TOP 1 *could* get you better performance.
> HTH
> -Dave
> thejamie wrote:
>

Awkward phrase

We have code from a manufacturer that we have not changed since the code was
purchased. In the code is some code that appears awkward. (See below) The
suggestion is to use a clause if exists (select blah blah -see below) then.
Given that the manufacturer wrote it this way (three to four years ago), is
there any reason why it may be a bad idea to change this proc... If so would
you use "If Exists..." or something else.
SELECT TOP 1
@.PickTicketID = AMU.PICK_TICKET_ID,
@.PTLine = AMU.LINE_NUMBER, @.SOI = TUID.SOI, @.INVKey = tUID.INV_Key,
@.ItemKey = tUID.ITEM_KEY
FROM
tIU WITH(NOLOCK)
INNER JOIN
tUID WITH(NOLOCK) ON tIU.UID_Key = tUID.UID_Key
INNER JOIN
ASSIGNED_MU AMU WITH(NOLOCK) ON AMU.MU_Key = tIU.MU_Key
WHERE
tIU.MU_Key = @.MUKey
IF ISNULL(@.PickTicketID,'') <> ''
--do stuff
--
Regards,
JamieReady for this one? Here's your answer, and you're sure to hate it: ;-)
"It depends."
It all depends on how often this procedure is created, and how the
underlying talbes are indexed. The question is this: What problem are
you trying to solve here? Performance? If so, it would be really
strange (and probably a mark of bad indexes) if this procedure performed
poorly. If it's cleanliness and data integrity issues, clearly NOLOCK
would have to go. But doing that could kill the concurrency of the
database -- a lot of developers use NOLOCK as a crutch to avoid dealing
with locks at the expense of potentially munging your data.
Other than that, the code wouldn't be that bad, if there were an ORDER
BY. The strange thing here is the "TOP 1". Do you expect there to be
more than one row? If so, which row should it be? You'd need an ORDER
BY there to tell SQL Server, or you'll get something random. You could
GROUP BY all of the columns, but if you *knew* that there would only be
one row coming off the query, the TOP 1 *could* get you better performance.
HTH
-Dave
thejamie wrote:
> We have code from a manufacturer that we have not changed since the code was
> purchased. In the code is some code that appears awkward. (See below) The
> suggestion is to use a clause if exists (select blah blah -see below) then.
> Given that the manufacturer wrote it this way (three to four years ago), is
> there any reason why it may be a bad idea to change this proc... If so would
> you use "If Exists..." or something else.
> SELECT TOP 1
> @.PickTicketID = AMU.PICK_TICKET_ID,
> @.PTLine = AMU.LINE_NUMBER, @.SOI = TUID.SOI, @.INVKey = tUID.INV_Key,
> @.ItemKey = tUID.ITEM_KEY
> FROM
> tIU WITH(NOLOCK)
> INNER JOIN
> tUID WITH(NOLOCK) ON tIU.UID_Key = tUID.UID_Key
> INNER JOIN
> ASSIGNED_MU AMU WITH(NOLOCK) ON AMU.MU_Key = tIU.MU_Key
> WHERE
> tIU.MU_Key = @.MUKey
> IF ISNULL(@.PickTicketID,'') <> ''
> --do stuff
>|||Top 1 is just to show the item exists. Thanks for the reply David. There is
a problem with this particular database - it was inherited and the nolock is
a crutch. It would be nice to know exactly where to put in different locking
hints and where to eliminate them completely, but my mere four months here
don't allow me that perspective just yet. In my mind, establishing any
pipelined process and fitting the locking hints to perform based on the
pipelines would be an improvement. Pipeline example might be 1- picking an
item in the warehouse, 2- moving it into production, 3- preparing the
material and 4- packing and shipping it. This is too wide a scope however;
maybe just picking is the single pipeline and maybe just packing and shipping
is another one.
--
Regards,
Jamie
"David Markle" wrote:
> Ready for this one? Here's your answer, and you're sure to hate it: ;-)
> "It depends."
> It all depends on how often this procedure is created, and how the
> underlying talbes are indexed. The question is this: What problem are
> you trying to solve here? Performance? If so, it would be really
> strange (and probably a mark of bad indexes) if this procedure performed
> poorly. If it's cleanliness and data integrity issues, clearly NOLOCK
> would have to go. But doing that could kill the concurrency of the
> database -- a lot of developers use NOLOCK as a crutch to avoid dealing
> with locks at the expense of potentially munging your data.
> Other than that, the code wouldn't be that bad, if there were an ORDER
> BY. The strange thing here is the "TOP 1". Do you expect there to be
> more than one row? If so, which row should it be? You'd need an ORDER
> BY there to tell SQL Server, or you'll get something random. You could
> GROUP BY all of the columns, but if you *knew* that there would only be
> one row coming off the query, the TOP 1 *could* get you better performance.
> HTH
> -Dave
> thejamie wrote:
> > We have code from a manufacturer that we have not changed since the code was
> > purchased. In the code is some code that appears awkward. (See below) The
> > suggestion is to use a clause if exists (select blah blah -see below) then.
> > Given that the manufacturer wrote it this way (three to four years ago), is
> > there any reason why it may be a bad idea to change this proc... If so would
> > you use "If Exists..." or something else.
> >
> > SELECT TOP 1
> > @.PickTicketID = AMU.PICK_TICKET_ID,
> > @.PTLine = AMU.LINE_NUMBER, @.SOI = TUID.SOI, @.INVKey = tUID.INV_Key,
> > @.ItemKey = tUID.ITEM_KEY
> > FROM
> > tIU WITH(NOLOCK)
> > INNER JOIN
> > tUID WITH(NOLOCK) ON tIU.UID_Key = tUID.UID_Key
> > INNER JOIN
> > ASSIGNED_MU AMU WITH(NOLOCK) ON AMU.MU_Key = tIU.MU_Key
> > WHERE
> > tIU.MU_Key = @.MUKey
> >
> > IF ISNULL(@.PickTicketID,'') <> ''
> > --do stuff
> >
>

Awkward phrase

We have code from a manufacturer that we have not changed since the code was
purchased. In the code is some code that appears awkward. (See below) The
suggestion is to use a clause if exists (select blah blah -see below) then.
Given that the manufacturer wrote it this way (three to four years ago), is
there any reason why it may be a bad idea to change this proc... If so woul
d
you use "If Exists..." or something else.
SELECT TOP 1
@.PickTicketID = AMU.PICK_TICKET_ID,
@.PTLine = AMU.LINE_NUMBER, @.SOI = TUID.SOI, @.INVKey = tUID.INV_Key,
@.ItemKey = tUID.ITEM_KEY
FROM
tIU WITH(NOLOCK)
INNER JOIN
tUID WITH(NOLOCK) ON tIU.UID_Key = tUID.UID_Key
INNER JOIN
ASSIGNED_MU AMU WITH(NOLOCK) ON AMU.MU_Key = tIU.MU_Key
WHERE
tIU.MU_Key = @.MUKey
IF ISNULL(@.PickTicketID,'') <> ''
--do stuff
Regards,
JamieReady for this one? Here's your answer, and you're sure to hate it: ;-)
"It depends."
It all depends on how often this procedure is created, and how the
underlying talbes are indexed. The question is this: What problem are
you trying to solve here? Performance? If so, it would be really
strange (and probably a mark of bad indexes) if this procedure performed
poorly. If it's cleanliness and data integrity issues, clearly NOLOCK
would have to go. But doing that could kill the concurrency of the
database -- a lot of developers use NOLOCK as a crutch to avoid dealing
with locks at the expense of potentially munging your data.
Other than that, the code wouldn't be that bad, if there were an ORDER
BY. The strange thing here is the "TOP 1". Do you expect there to be
more than one row? If so, which row should it be? You'd need an ORDER
BY there to tell SQL Server, or you'll get something random. You could
GROUP BY all of the columns, but if you *knew* that there would only be
one row coming off the query, the TOP 1 *could* get you better performance.
HTH
-Dave
thejamie wrote:
> We have code from a manufacturer that we have not changed since the code w
as
> purchased. In the code is some code that appears awkward. (See below) T
he
> suggestion is to use a clause if exists (select blah blah -see below) then
.
> Given that the manufacturer wrote it this way (three to four years ago), i
s
> there any reason why it may be a bad idea to change this proc... If so wo
uld
> you use "If Exists..." or something else.
> SELECT TOP 1
> @.PickTicketID = AMU.PICK_TICKET_ID,
> @.PTLine = AMU.LINE_NUMBER, @.SOI = TUID.SOI, @.INVKey = tUID.INV_Key,
> @.ItemKey = tUID.ITEM_KEY
> FROM
> tIU WITH(NOLOCK)
> INNER JOIN
> tUID WITH(NOLOCK) ON tIU.UID_Key = tUID.UID_Key
> INNER JOIN
> ASSIGNED_MU AMU WITH(NOLOCK) ON AMU.MU_Key = tIU.MU_Key
> WHERE
> tIU.MU_Key = @.MUKey
> IF ISNULL(@.PickTicketID,'') <> ''
> --do stuff
>|||Top 1 is just to show the item exists. Thanks for the reply David. There i
s
a problem with this particular database - it was inherited and the nolock is
a crutch. It would be nice to know exactly where to put in different lockin
g
hints and where to eliminate them completely, but my mere four months here
don't allow me that perspective just yet. In my mind, establishing any
pipelined process and fitting the locking hints to perform based on the
pipelines would be an improvement. Pipeline example might be 1- picking an
item in the warehouse, 2- moving it into production, 3- preparing the
material and 4- packing and shipping it. This is too wide a scope however;
maybe just picking is the single pipeline and maybe just packing and shippin
g
is another one.
--
Regards,
Jamie
"David Markle" wrote:

> Ready for this one? Here's your answer, and you're sure to hate it: ;-)
> "It depends."
> It all depends on how often this procedure is created, and how the
> underlying talbes are indexed. The question is this: What problem are
> you trying to solve here? Performance? If so, it would be really
> strange (and probably a mark of bad indexes) if this procedure performed
> poorly. If it's cleanliness and data integrity issues, clearly NOLOCK
> would have to go. But doing that could kill the concurrency of the
> database -- a lot of developers use NOLOCK as a crutch to avoid dealing
> with locks at the expense of potentially munging your data.
> Other than that, the code wouldn't be that bad, if there were an ORDER
> BY. The strange thing here is the "TOP 1". Do you expect there to be
> more than one row? If so, which row should it be? You'd need an ORDER
> BY there to tell SQL Server, or you'll get something random. You could
> GROUP BY all of the columns, but if you *knew* that there would only be
> one row coming off the query, the TOP 1 *could* get you better performance
.
> HTH
> -Dave
> thejamie wrote:
>

Avoiding SQL Injection with Dynamic SQL

I am exclusively using Stored Procedures to access the database, i.e. there are no Ad-Hoc SQL statements anywhere in the C# code. However, one thing I need to be able to do is to allow filtering for data grids on my ASP.NET page. I want to do the filtering in the Stored Procedure using Dynamic SQL to set the WHERE clause. However, one fear of mine is SQL injection from the client. How can I avoid arbitrary SQL injection, yet still allow for a dynamic WHERE clause to be passed into the stored procedure?

Jason PachecoFrom herehttp://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sp3sec03.mspx

Preventing SQL Injection

So long as injected SQL code is syntactically correct, it will be impossible to programmatically detect tampering on the server side. You must therefore validate all user input on the client side, and force server-side type checking by calling parameterized stored procedures. Always validate user input by testing type, length, format, and range. Untested input can cause program errors, and may be used by hackers as a point of entry into your system. When implementing precautions against malicious input, consider the architecture and deployment scenarios of your application. Remember that programs designed to run in a secure environment can be copied to an insecure environment.

Validate All Input

The following suggestions should be considered best practices:
- Make no assumptions about the size, type, or content of the data received by your application. For example, evaluate:
- How will your application behave if an errant, or malicious, user enters a 10-megabyte MPEG file where your application expects a postal code?
- How will your application behave if a DROP TABLE statement is embedded in a text field?
- Test the size and data type of input, and enforce appropriate limits. This can help prevent deliberate buffer overruns.
- Test the content of string variables and accept only expected values. Reject entries containing binary data, escape sequences, and comment characters. This can help prevent script injection and can protect against some buffer overrun exploits.
- When working with XML documents, validate all data against its schema as it is entered.
- Never build Transact-SQL statements directly from user input.
- Use stored procedures to validate user input.
- In multi-tiered environments, all data should be validated before admission to the trusted zone. Data that does not pass the validation process should be rejected, and an error returned to the previous tier.
- Implement multiple layers of validation. Precautions you take against casually malicious users may be ineffective against expert hackers. The best practice is to validate input in the user interface, and then at all subsequent points at which it crosses a trust boundary.

For example, data validation in a client-side application may prevent simple script injection; however, if the next tier assumes that its input has already been validated, any hacker capable of bypassing your client can have unrestricted access to your system.
- Never concatenate user input that is not validated. String concatenation is the primary point of entry for script injection.
- Do not accept the following strings in fields from which file names may be constructed: AUX, CLOCK$, COM1 through COM8, CON, CONFIG$, LPT1 through LPT8, NUL, and PRN.

When possible, reject input that contains the following potentially dangerous characters.
Input characterMeaning in Transact-SQL

; Query delimiter

' Character data string delimiter

-- Comment delimiter

/* ... */ Comment delimiters. Text between /* and */ is not evaluated by the server.

Xp_ Begins the name of catalog extended stored procedures such as xp_cmdshell.

Use Type-Safe SQL Parameters

The Parameters collection in SQL Server provides type checking and length validation. If you use the Parameters collection, input is treated as a literal value rather than executable code. An additional benefit of using the Parameters collection is that you can enforce type and length checks. Values outside of the range will trigger an exception. The following code fragment illustrates using the Parameters collection:

SqlDataAdapter myCommand = new SqlDataAdapter("AuthorLogin", conn);
myCommand.SelectCommand.CommandType = CommandType.StoredProcedure;
SqlParameter parm = myCommand.SelectCommand.Parameters.Add(
"@.au_id", SqlDbType.VarChar, 11);
parm.Value = Login.Text;

In this example, the @.au_id parameter is treated as a literal value rather than executable code. This value is checked for type and length. If the value of @.au_id does not conform to the specified type and length constraints, an exception will be thrown.

Use Parameterized Input with Stored Procedures

Stored procedures may be susceptible to SQL injection if they use unfiltered input. For example, the following code is vulnerable:

SqlDataAdapter myCommand =
new SqlDataAdapter("LoginStoredProcedure '" +
Login.Text + "'", conn);

If you use stored procedures, you should use parameters as their input.

Use the Parameters Collection with Dynamic SQL

If you cannot use stored procedures, you can still use parameters, as shown below.

SqlDataAdapter myCommand = new SqlDataAdapter(
"SELECT au_lname, au_fname FROM Authors WHERE au_id = @.au_id", conn);
SQLParameter parm = myCommand.SelectCommand.Parameters.Add("@.au_id",
SqlDbType.VarChar, 11);
Parm.Value = Login.Text;

Filtering Input

Filtering input may also be helpful in protecting against SQL injection by removing escape characters, but due to the large number of characters that may pose problems it is not a reliable defense. The following snippet searches for the character string delimiter.

private string SafeSqlLiteral(string inputSQL)
{
return inputSQL.Replace("'", "''");
}

LIKE Clauses

Note that if you are using a LIKE clause, wildcard characters still need to be escaped:

s = s.Replace("[", "[[]");
s = s.Replace("%", "[%]");
s = s.Replace("_", "[_]");|||

Many thanks to you DarrellNorton,

Very helpfull information.
this was my posthttp://forums.asp.net/926297/ShowPost.aspx

BR

Avoid to Write the Schema Name in a Function Call in SQL 2005

Avoid Writing the Schema Name in a Function Call in SQL 2005

Hi, everybody, already I try to certificate my code in Oracle PL/SQL to T-SQL to SQL Server 2005, but I have a little trouble, When I Translate the code with SQL Migration Assistant for Oracle (SSMA), this tool, put the schema name before the function name, that is a good practice, but I can avoid to do that, because, The company have a lot of calls in hard-code, and when I execute the call without the schema name, SQL2005 send me a error message, like say, “is not recognize as built-in function name”.

Somebody knows what I can do to do that?

Thanks

KC:

If the function is a scalar function you must precede the function name with the schema; it is a requirement for the way Transact SQL Works.

Avareges for period

Hi,

I am trying to get average for sales for last 30 days.

Aggregation function for [Measures].[Avg Sales] is AverageOfChildren

Next code returns correct result

SELECT { [Measures].[Avg Sales] } ON columns

FROM [Sales]

where ([Dim Date].[Year - Day].[Day].&[2/14/2007]&[2]:[Dim Date].[Year - Day].[Day].&[3/15/2007]&[3])

But this query is not flexible. Should be selected day and returned value for 30 days back.

For example, I am trying to use next code

select

([Dim Date].[Year - Day].[Day].CurrentMember.lead(29) : [Dim Date].[Year - Day].[Day].CurrentMember

, [Measures].[Avg Sales]) // average for 30 days back

ON columns

FROM [SAles]

where ([Dim Date].[Year - Day].[Day].&[3/15/2007]&[3])

But recieved error "The Tuple function expects a tuple expression for the argument. A tuple set expression was used."

What the query should be to resolve this problem?

Thanks for help.

Try creating a query calculated measure for this,like:

With

Member [Measures].[Trailing30Sales] as

Aggregate({[Dim Date].[Year - Day].Lag(29) : [Dim Date].[Year - Day].CurrentMember},

[Measures].[Avg Sales])

select

{[Measures].[Trailing30Sales]} // average for 30 days back

ON columns

FROM [Sales]

where ([Dim Date].[Year - Day].[Day].&[3/15/2007]&[3])

|||

Thank you, very much!

It is very simple

Available platforms...?

HI,
Can someone please tell me if I can install the SQL Server Client code on and AIX 4.3.3 O/S? When I say client, what I need to do is to install the client code locally on an IBM Infomration Integrator server to enable connectivity to a remote/disparate SQLServer dB.
Your help in this matter will be hugely appreciated.
Regards
Alex.No. You may have to compile your own client with libraries such as freeTDS.
MS was supposedly going to release some SQL client tools for Unix (the rumor
I heard anyway, not meant to create any rumor). But I don't know whether
that is right now.
--
Linchi Shea
linchi_shea@.NOSPAMml.com
"Alex Crockett" <anonymous@.discussions.microsoft.com> wrote in message
news:4521C7EF-9B2E-4CCF-9F6E-D4797C956D15@.microsoft.com...
> HI,
> Can someone please tell me if I can install the SQL Server Client code on
and AIX 4.3.3 O/S? When I say client, what I need to do is to install the
client code locally on an IBM Infomration Integrator server to enable
connectivity to a remote/disparate SQLServer dB.
> Your help in this matter will be hugely appreciated.
> Regards
> Alex.