print icon on toolbar

Is there defo no way of altering what happens when user clicks the print icon? I want to return msgbox before printing starts?!There is no way to change the behavior of the print button to popup a msgbox first.sql

print function?

One of our user requirement is to include "Print" option to the Reporting
Manager toolbar (either inlcude in the Export to .. list or a separate
option to send the report to the printer).
Is this possible.? If possible, how? if not, what are the alternatives?
ThanksClient side printing is expected to be included in a future release. In the
current release, you can print reports from a custom app. Please see
http://www.csharphelp.com/archives3/archive545.html for a code sample.
--
Ravi Mumulla (Microsoft)
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"newmem" <""> wrote in message
news:uQgLw%23TiEHA.3548@.TK2MSFTNGP09.phx.gbl...
> One of our user requirement is to include "Print" option to the Reporting
> Manager toolbar (either inlcude in the Export to .. list or a separate
> option to send the report to the printer).
> Is this possible.? If possible, how? if not, what are the alternatives?
> Thanks
>

Print Date and Time

I am currently using the global execution time in my reports, but I would
also like to show the user what time they view the report. I guess in
Crystal they distinguish between the two as "Data Date" which would be the
execution time and then "Print Date" which is the date and time the report
is viewed. Does any one know if there a way to show this is RS?
Thanks,
LisaPlease review .Net's DateTime functionality (Search on DateTime in MSDN).
In your case your could place, in a textbox, an expression like
=DateTime.Now.
You could also use the Now() function.
--
Bruce Johnson [MSFT]
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Lisa" <Lisa.Lambert@._nospam_etalk.com> wrote in message
news:OnOBSRDZEHA.3716@.TK2MSFTNGP11.phx.gbl...
> I am currently using the global execution time in my reports, but I would
> also like to show the user what time they view the report. I guess in
> Crystal they distinguish between the two as "Data Date" which would be the
> execution time and then "Print Date" which is the date and time the report
> is viewed. Does any one know if there a way to show this is RS?
> Thanks,
> Lisa
>|||Thank you!
"Bruce Johnson [MSFT]" <brucejoh@.online.microsoft.com> wrote in message
news:O%23oBowEZEHA.712@.TK2MSFTNGP11.phx.gbl...
> Please review .Net's DateTime functionality (Search on DateTime in MSDN).
> In your case your could place, in a textbox, an expression like
> =DateTime.Now.
> You could also use the Now() function.
> --
> Bruce Johnson [MSFT]
> Microsoft SQL Server Reporting Services
> This posting is provided "AS IS" with no warranties, and confers no
rights.
>
> "Lisa" <Lisa.Lambert@._nospam_etalk.com> wrote in message
> news:OnOBSRDZEHA.3716@.TK2MSFTNGP11.phx.gbl...
> > I am currently using the global execution time in my reports, but I
would
> > also like to show the user what time they view the report. I guess in
> > Crystal they distinguish between the two as "Data Date" which would be
the
> > execution time and then "Print Date" which is the date and time the
report
> > is viewed. Does any one know if there a way to show this is RS?
> >
> > Thanks,
> >
> > Lisa
> >
> >
>|||You can try =now().
--
Ravi Mumulla (Microsoft)
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Lisa" <Lisa.Lambert@._nospam_etalk.com> wrote in message
news:OnOBSRDZEHA.3716@.TK2MSFTNGP11.phx.gbl...
> I am currently using the global execution time in my reports, but I would
> also like to show the user what time they view the report. I guess in
> Crystal they distinguish between the two as "Data Date" which would be the
> execution time and then "Print Date" which is the date and time the report
> is viewed. Does any one know if there a way to show this is RS?
> Thanks,
> Lisa
>

Print and Raiserror

I wanted to give user message from SQL Stored Procedure.
I tried to use PRINT, but it does not go to my MS Access front end
application
I tried to use 'RAISERROR' to send out message, but the message has SQL
server message to my MS Access.
Are there any way to sent message to my front end application user form
stored procedure?
Any information is great appreciated.
Souris,souris wrote:
> I wanted to give user message from SQL Stored Procedure.
> I tried to use PRINT, but it does not go to my MS Access front end
> application
> I tried to use 'RAISERROR' to send out message, but the message has
> SQL server message to my MS Access.
> Are there any way to sent message to my front end application user
> form stored procedure?
> Any information is great appreciated.
> Souris,
You can use RAISERROR. Please post you code to generate the error and
exactly the problem you are seeing with using it. I assume you want to
notify the client app about an error condition and not just a normal
message. Is that correct?
For example, you could use:
RAISERROR ('Job id 1 expects the default level of 10.', 16, 1)
or
RAISERROR ('The level for job_id:%d should be between %d and %d.', 16,
1, @.@.JOB_ID, @.@.MIN_LVL, @.@.MAX_LVL)
David Gugick
Imceda Software
www.imceda.com|||Thanks for the message.
Here is my SQL
BEGIN
RAISERROR('YOU HAVE UPDATE DATA TODAY, IF YOU THINK YOU NEED UPLOAD ONE
MORE TIME PLEASE SEE YOUR ADMINISTRATOR, THANKS', 16, -1)
RETURN
END
The message comes following form my Access front end
Microsoft Access
ODBC - call failer
[Microsoft][ODBC SQL server Driver][SQL Driver]YOU AHVE UPDATE DATA TODAY,
OF YOU THINK YOU NEED UPLOAD ONE MORE TIME PLEASE SEE YOUR ADMINISTARTOR,
THANKS(#50000) [Microsoft][ODBC driver SQL Server][SQL server] Violation of
PRIMARY KEY constaint 'PK_TSC_CMPLD_CALLOUTS', Cannot insert duplicate key
in object 'TSC_CMPLD_CALLOUTS'. (#2627)Microsoft][ODBC SQL server
Driver][SQL Driver]The statement has been terminated.(#3621)[ODBC driver SQL
Server][SQL server] Violation of PRIMARY KEY constaint
'PK_TSC_CMPLD_CALLOUTS', Cannot insert duplicate key in object
'TSC_CMPLD_CALLOUTS'. (#2627)Microsoft][ODBC SQL server Driver][SQL
Driver]The statement has been terminated.(#3621)
How can I give the user message from my stored procedure?
Why my Access front give so much message to user?
Any information is great appreciated.
Thanks again,
Souris,
"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:%23OSTKNfJFHA.1308@.TK2MSFTNGP15.phx.gbl...
> souris wrote:
> You can use RAISERROR. Please post you code to generate the error and
> exactly the problem you are seeing with using it. I assume you want to
> notify the client app about an error condition and not just a normal
> message. Is that correct?
>
> For example, you could use:
> RAISERROR ('Job id 1 expects the default level of 10.', 16, 1)
> or
> RAISERROR ('The level for job_id:%d should be between %d and %d.', 16, 1,
> @.@.JOB_ID, @.@.MIN_LVL, @.@.MAX_LVL)
>
>
> --
> David Gugick
> Imceda Software
> www.imceda.com|||souris wrote:
> Thanks for the message.
> Here is my SQL
> BEGIN
> RAISERROR('YOU HAVE UPDATE DATA TODAY, IF YOU THINK YOU NEED UPLOAD
> ONE MORE TIME PLEASE SEE YOUR ADMINISTRATOR, THANKS', 16, -1)
> RETURN
> END
Sounds like you are generating a lot of errors in whatever SQL you are
running. Try running the SQL with the same parameter values from Query
Analyzer or some other query tool (probably can use Access as well) and
make sure it doesn't generate any errors.
It looks like you are using RAISERROR to return a message. RAISERROR is
for raising errors only, not for messages. I would have the application
display the message on it's own, and not have it generated from the
database.
David Gugick
Imceda Software
www.imceda.com|||Thanks for the message.
My App needs download once and only once per day
The date filed is a part of my primary key.
I wanted to use RAISERROR to raise error and also let user know what
happens.
1. If I use RAISERROR the SQL statement should not continue executing the
rest of SQL, but it seems continues running to violate primary key rule.
2. If I need to write message on front end application then there must be a
way to get rid the [Microsoft ODBC][SQL Server] message from RAISERROR.
Your information is great appreciated
Souris,
"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:OTuFBvyJFHA.3552@.TK2MSFTNGP12.phx.gbl...
> souris wrote:
> Sounds like you are generating a lot of errors in whatever SQL you are
> running. Try running the SQL with the same parameter values from Query
> Analyzer or some other query tool (probably can use Access as well) and
> make sure it doesn't generate any errors.
> It looks like you are using RAISERROR to return a message. RAISERROR is
> for raising errors only, not for messages. I would have the application
> display the message on it's own, and not have it generated from the
> database.
> --
> David Gugick
> Imceda Software
> www.imceda.com|||souris wrote:
> Thanks for the message.
> My App needs download once and only once per day
> The date filed is a part of my primary key.
> I wanted to use RAISERROR to raise error and also let user know what
> happens.
> 1. If I use RAISERROR the SQL statement should not continue executing
> the rest of SQL, but it seems continues running to violate primary
> key rule.
> 2. If I need to write message on front end application then there
> must be a way to get rid the [Microsoft ODBC][SQL Server] message
> from RAISERROR.
It looks like the snippet of code you posted should work. You are using
raiserror in a block with a return statement (you should add a return
code). I don't see how the rest of the SQL is being executed unless it
occurred before the raiserror. Can you post the entire procedure?
David Gugick
Imceda Software
www.imceda.com

Primarykey Fields

I'm try to get table columns name & primary key to generat Dlete/Insert
script to all my database tables..
I was able to get list of all user tables from sysobject and columns list
from syscolumns..
Now i need to know the primary key column (For delete Statments)..
How could i detrmine which column is primarykey or composite key'
thanxA couple of suggestions:
First, you may be trying to reinvent the wheel; have you looked at
using SQL-DMO or SCPTXFR to script out your database? May save you a
lot of time and energy.
Second, wherever possible, use the INFORMATION_SCHEMA views rather than
the system tables to query this type of information; look at the
following queries i nthe pubs database as an example:
SELECT *
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
WHERE TABLE_NAME = 'authors'
SELECT *
FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE
WHERE TABLE_NAME = 'authors'
Note that the identification of a primary key is a constraint on the
table, not a property of a column.
Of course, this only rings true for SQL Server 2000; no clue about
SS2005.
HTH,
Stu|||Islamegy (NULL_Islamegy_NULL@.yahoo.com) writes:
> I'm try to get table columns name & primary key to generat Dlete/Insert
> script to all my database tables..
> I was able to get list of all user tables from sysobject and columns list
> from syscolumns..
> Now i need to know the primary key column (For delete Statments)..
> How could i detrmine which column is primarykey or composite key'
> thanx
Here is a query that lists the PK columns for all tables in a database.
There is a restriction that the query as written will not cover keys
with more than 10 columns, but this is easy to address.
select o.name,
MAX(CASE ik.keyno WHEN 1 THEN c.name END) +
coalesce(MAX(CASE ik.keyno WHEN 2 THEN ', ' + c.name END), '') +
coalesce(MAX(CASE ik.keyno WHEN 3 THEN ', ' + c.name END), '') +
coalesce(MAX(CASE ik.keyno WHEN 4 THEN ', ' + c.name END), '') +
coalesce(MAX(CASE ik.keyno WHEN 5 THEN ', ' + c.name END), '') +
coalesce(MAX(CASE ik.keyno WHEN 6 THEN ', ' + c.name END), '') +
coalesce(MAX(CASE ik.keyno WHEN 7 THEN ', ' + c.name END), '') +
coalesce(MAX(CASE ik.keyno WHEN 8 THEN ', ' + c.name END), '') +
coalesce(MAX(CASE ik.keyno WHEN 9 THEN ', ' + c.name END), '') +
coalesce(MAX(CASE ik.keyno WHEN 10 THEN ', ' + c.name END), '')
from sysobjects o
join sysindexes i on i.id = o.id
join sysindexkeys ik on i.id = ik.id
and i.indid = ik.indid
join syscolumns c on ik.id = c.id
and ik.colid = c.colid
join sysobjects pk ON i.name = pk.name
AND o.id = pk.parent_obj
group by o.name
order by o.name
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Thanx so much for this query..
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns976FD9D836B2FYazorman@.127.0.0.1...
> Islamegy (NULL_Islamegy_NULL@.yahoo.com) writes:
> Here is a query that lists the PK columns for all tables in a database.
> There is a restriction that the query as written will not cover keys
> with more than 10 columns, but this is easy to address.
> select o.name,
> MAX(CASE ik.keyno WHEN 1 THEN c.name END) +
> coalesce(MAX(CASE ik.keyno WHEN 2 THEN ', ' + c.name END), '') +
> coalesce(MAX(CASE ik.keyno WHEN 3 THEN ', ' + c.name END), '') +
> coalesce(MAX(CASE ik.keyno WHEN 4 THEN ', ' + c.name END), '') +
> coalesce(MAX(CASE ik.keyno WHEN 5 THEN ', ' + c.name END), '') +
> coalesce(MAX(CASE ik.keyno WHEN 6 THEN ', ' + c.name END), '') +
> coalesce(MAX(CASE ik.keyno WHEN 7 THEN ', ' + c.name END), '') +
> coalesce(MAX(CASE ik.keyno WHEN 8 THEN ', ' + c.name END), '') +
> coalesce(MAX(CASE ik.keyno WHEN 9 THEN ', ' + c.name END), '') +
> coalesce(MAX(CASE ik.keyno WHEN 10 THEN ', ' + c.name END), '')
> from sysobjects o
> join sysindexes i on i.id = o.id
> join sysindexkeys ik on i.id = ik.id
> and i.indid = ik.indid
> join syscolumns c on ik.id = c.id
> and ik.colid = c.colid
> join sysobjects pk ON i.name = pk.name
> AND o.id = pk.parent_obj
> group by o.name
> order by o.name
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx

PrimaryKey and unique

how can I design a table to get unique values in a column within the PK
columns?
Example:
Client (int, PK)
User (int, PK
Userid(char...)
userid's shoud be unique for a client. Other clients are allowed to have
same userid's as other clients. A second user of a client cannot have the
same userid as other user of this client.
thanks for any help.
Looks like you have it almost right:
alter table MyTable
add
constraint PK_MyTable primary key (ClientID, UserID)
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"Michael Haberichter" <Haberichter@.community.nospam> wrote in message
news:ED15E772-0327-4C98-8FBD-92AE3717D1FB@.microsoft.com...
how can I design a table to get unique values in a column within the PK
columns?
Example:
Client (int, PK)
User (int, PK
Userid(char...)
userid's shoud be unique for a client. Other clients are allowed to have
same userid's as other clients. A second user of a client cannot have the
same userid as other user of this client.
thanks for any help.
sql

primary key fields in the database

Hello,
i need to query a database and get the list of all primary keys wrt all
the user tables in the database. I dont know as to what property field
of syscolumns mark a field as a primary key.
How do i know that the field in the table is a primary key '
thanksHere you go.
select c.column_name, object_name(o.parent_obj) as tableName
from information_schema.constraint_column_usage c
inner join sysobjects o on o.name = c.constraint_name
where o.xtype = 'PK'
Roji. P. Thomas
Net Asset Management
http://toponewithties.blogspot.com
"ch8an" <chethan.shetty@.gmail.com> wrote in message
news:1124874929.246497.117660@.o13g2000cwo.googlegroups.com...
> Hello,
> i need to query a database and get the list of all primary keys wrt all
> the user tables in the database. I dont know as to what property field
> of syscolumns mark a field as a primary key.
> How do i know that the field in the table is a primary key '
> thanks
>|||A couple of partial solutions:
1)sp_help <table>
2)generating full script and then to identify PK and its associates fields
3)
select * from sysobjects inner join sysobjects s1
on sysobjects.id = s1.parent_obj
where s1.xtype = 'PK'
"ch8an" wrote:

> Hello,
> i need to query a database and get the list of all primary keys wrt all
> the user tables in the database. I dont know as to what property field
> of syscolumns mark a field as a primary key.
> How do i know that the field in the table is a primary key '
> thanks
>|||http://www.aspfaq.com/search.asp?q=schema%3A
http://www.aspfaq.com/2104
"ch8an" <chethan.shetty@.gmail.com> wrote in message
news:1124874929.246497.117660@.o13g2000cwo.googlegroups.com...
> Hello,
> i need to query a database and get the list of all primary keys wrt all
> the user tables in the database. I dont know as to what property field
> of syscolumns mark a field as a primary key.
> How do i know that the field in the table is a primary key '
> thanks
>

Primary file group full

I have inherited a SQL server 2000 DB where the system and user tables
are separated into different mdf files (on separate drives). The
primary file group is the system data. I have a query that is
returning data from a remote DB table which is not in the local DB
table. I am running to the problem that as the query is running the
primary file group is now full. I have rebuilt the indexes and run a
shrink of the db, but still the file group is full. I have auto
growth enabled to unrestricted, but am getting an ERROR 1827. Any
help is greatly appreciated!
Daniel
Hi
And do you have enough space on the disk? I have seen such errors , it
could happen what the database tried to grow and while it is growing (writes
zeroes to the pages) users get the error.
"danielp" <danielsmith611@.gmail.com> wrote in message
news:1172157667.706350.173350@.q2g2000cwa.googlegro ups.com...
>I have inherited a SQL server 2000 DB where the system and user tables
> are separated into different mdf files (on separate drives). The
> primary file group is the system data. I have a query that is
> returning data from a remote DB table which is not in the local DB
> table. I am running to the problem that as the query is running the
> primary file group is now full. I have rebuilt the indexes and run a
> shrink of the db, but still the file group is full. I have auto
> growth enabled to unrestricted, but am getting an ERROR 1827. Any
> help is greatly appreciated!
> Daniel
>

Primary file group full

I have inherited a SQL server 2000 DB where the system and user tables
are separated into different mdf files (on separate drives). The
primary file group is the system data. I have a query that is
returning data from a remote DB table which is not in the local DB
table. I am running to the problem that as the query is running the
primary file group is now full. I have rebuilt the indexes and run a
shrink of the db, but still the file group is full. I have auto
growth enabled to unrestricted, but am getting an ERROR 1827. Any
help is greatly appreciated!
DanielHi
And do you have enough space on the disk? I have seen such errors , it
could happen what the database tried to grow and while it is growing (writes
zeroes to the pages) users get the error.
"danielp" <danielsmith611@.gmail.com> wrote in message
news:1172157667.706350.173350@.q2g2000cwa.googlegroups.com...
>I have inherited a SQL server 2000 DB where the system and user tables
> are separated into different mdf files (on separate drives). The
> primary file group is the system data. I have a query that is
> returning data from a remote DB table which is not in the local DB
> table. I am running to the problem that as the query is running the
> primary file group is now full. I have rebuilt the indexes and run a
> shrink of the db, but still the file group is full. I have auto
> growth enabled to unrestricted, but am getting an ERROR 1827. Any
> help is greatly appreciated!
> Daniel
>

Primary file group full

I have inherited a SQL server 2000 DB where the system and user tables
are separated into different mdf files (on separate drives). The
primary file group is the system data. I have a query that is
returning data from a remote DB table which is not in the local DB
table. I am running to the problem that as the query is running the
primary file group is now full. I have rebuilt the indexes and run a
shrink of the db, but still the file group is full. I have auto
growth enabled to unrestricted, but am getting an ERROR 1827. Any
help is greatly appreciated!
DanielHi
And do you have enough space on the disk? I have seen such errors , it
could happen what the database tried to grow and while it is growing (writes
zeroes to the pages) users get the error.
"danielp" <danielsmith611@.gmail.com> wrote in message
news:1172157667.706350.173350@.q2g2000cwa.googlegroups.com...
>I have inherited a SQL server 2000 DB where the system and user tables
> are separated into different mdf files (on separate drives). The
> primary file group is the system data. I have a query that is
> returning data from a remote DB table which is not in the local DB
> table. I am running to the problem that as the query is running the
> primary file group is now full. I have rebuilt the indexes and run a
> shrink of the db, but still the file group is full. I have auto
> growth enabled to unrestricted, but am getting an ERROR 1827. Any
> help is greatly appreciated!
> Daniel
>

Preventing overlapping data entry

Hello Everyone,

I have a web form that allows the user to select a time to reserve. I am trying to make it so that when a user selects a date to schedule something (which i have working) a drop down list will fill with times that have not been reserved.

The data is stored in two tables: tblCalendar and tblTime. tblTime contains pkTimeID and times (which are all possible times to select from in half hour intervals). tblCalendar contains a few fields but timeID and date (which is in the format M/d/yyyy) are what I need to test against. timeID is the foreign key of pkTimeID.

Basically when the user selects the date, a function gets called that will run a SELECT statement to get the times available. All of this works, I am able to fill the ddl with all times available no matter what the date is or what has already been reserved. I want to check if a time has been already selected based on the date selected by the user and only allow times not selected to be listed.

After acheiving this I would like to prevent the immediate time before and immediate time after from being displayed because each reserved time will last for one hour but the data is stored in half hour increments.

Any help/suggestions/links will be greatly appreciated. If I need to provide anything else please let me know.

Thanks in advance,

Brian

This query should give you the timeIDs that have not been scheduled:

SELECT t.pkTimeIDFROM tblTime tWHERE t.pkTimeIDNOT IN (SELECT c.timeIDFROM tblCalendar cWHERE c.date ='4/20/2007' )

Replace the date with the date the user has selected.|||

This query leaves out the prior and next time slots to avoid overlapping a one hour appointment.

SELECT t.pkTimeIDFROM tblTime tWHERE t.pkTimeIDNOT IN (SELECT unavailableTimes.timeIDFROM (SELECT c.timeID, c.dateFROM tblCalendar cLEFTJOIN tblTime tON t.pkTimeID = c.timeIDUNION SELECT t1.pkTimeID, c.dateFROM tblCalendar cLEFTJOIN tblTime tON t.pkTimeID = c.timeIDLEFTJOIN tblTime t1ON t1.time =SUBSTRING(CONVERT(VARCHAR(20),DATEADD(n, -30, c.date +' ' + t.time)), 14, 7)UNION SELECT t2.pkTimeID, c.dateFROM tblCalendar cLEFTJOIN tblTime tON t.pkTimeID = c.timeIDLEFTJOIN tblTime t2ON t2.time =SUBSTRING(CONVERT(VARCHAR(20),DATEADD(n, 30, c.date +' ' + t.time)), 14, 7) ) unavailableTimesWHERE unavailableTimes.date ='4/20/2007' )

This query is not optimal but you get the idea. Again, replace the date with the date the user has selected. You may also need to tweak the SUBSTRING params based on the format of your DATETIMEs.|||

Thank you so much for your help. I think I was a little to vague on my description but this solution helped me get a solution that worked for me. I needed to check for the previous and next pkTimeID value to eliminate not the actual datetime values. Just to keep this short my statement is as follows:

SELECT t.pkTimeID, t.timeFROM tblTime tWHERE t.pkTimeIDNOT IN (SELECT c.timeID - 1FROM tblCalendar cUNIONSELECT c1.timeIDFROM tblCalendar c1UNIONSELECT c2.timeID + 1FROM tblCalendar c2WHERE (c2.[date] ='4/20/2007'))

Thank you again for your help, and if you have any suggestions on how I might improve what I did come up with I would appreciate it. This is my first "real" project in the work place and I would like to learn the best way to approach a problem where ever I can, so that I will not have to learn these things later.

Brian

Preventing ALL text SQL Injection by removing single-quotes ?

I need to ask the user for some string (not INT) input... to
build a T-SQL "find" query in SQL Server 2000.
I do *NOT* need the user to ever search for "'"... so I'm removing the
single-quote character entirely.

> s = Replace(s, "'", "")
Can someone give a working example where SQL INJECTION would still be
possible?Hi Susan
You may want to read:
http://www.sqlsecurity.com/DesktopDefault.aspx?tabid=23
http://www.sommarskog.se/dynamic_sql.html#Security2
John
"Susan S via droptable.com" <forum@.droptable.com> wrote in message
news:50B826701CBE0@.droptable.com...
>I need to ask the user for some string (not INT) input... to
> build a T-SQL "find" query in SQL Server 2000.
> I do *NOT* need the user to ever search for "'"... so I'm removing the
> single-quote character entirely.
>
> Can someone give a working example where SQL INJECTION would still be
> possible?|||I can't find a single line of that article that *SHOWS* how a
string, fully stripped of all single-quotes... can be used for
Injection.
I'm not saying that it can't.
I just need someone to post the actual code.
Ever wonder why no one has the code?
Message posted via http://www.droptable.com|||Hi
You are assuming a certain business rule is applied which is probably
quite rare. You may be able to provide dropdowns if your values are so
specific and remove the need to type in anything!!
The parameterised query option is generic and can cater for all
sutuations, it should also be fast.
John|||John... that wasn't the question at all.
I *DO* have a need to search for text without containing any single-quotes.
(I couldn't possible have a dropdown box with 1000 different searches in it.
)
Why not simple say "I don't know" instead of answering a totally different
question?
John Bell wrote:
>Hi
>You are assuming a certain business rule is applied which is probably
>quite rare. You may be able to provide dropdowns if your values are so
>specific and remove the need to type in anything!!
>The parameterised query option is generic and can cater for all
>sutuations, it should also be fast.
>John
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...curity/200507/1|||"Susan S via droptable.com" <forum@.droptable.com> wrote in message
news:510817BAE9A30@.droptable.com...
> John... that wasn't the question at all.
> I *DO* have a need to search for text without containing any
> single-quotes.
> (I couldn't possible have a dropdown box with 1000 different searches in
> it.)
> Why not simple say "I don't know" instead of answering a totally different
> question?
You don't provide us with an example of your code that builds dynamic SQL so
we can only guess as to what might or might not work. Have you considered
char(39) being mixed in somehow?
But the larger question is, what is your aversion to parameterizing your
queries? If you were truly concerned about SQL injection, that's the route
you would go. Will you feel safe if nobody here can come up with an
example? Will that mean that it can't be done? You'd learn more hanging
out in hacker's circles, if that is your approach..
So, sorry if it offends you, and I'll admit that "I don't know" of a
specific example, but the real answer to your rhetorical question is:
parameterize, then you won't need to worry about it, there are no reasons it
can't be done, only excuses.
Good Luck,
Mark
[vbcol=seagreen]
> John Bell wrote:|||Well I started out the other day writing down a list from the sql server
perspective, but they all seem to start with the programmer making a
blunder. There are so many ways you can facilitate your site getting hacked
that it is really quite absurd. This is where a lot of the challenge exists.
If ever in the design of the system someone has said "It will never do such
and such" so you haven't coded defensively for the day you will have say
multiple sites on the one server (as an EG) then you have introduced time
bombs.
The most important thing about security is like so many things - expend your
effort where it will have most benefit.
I suggest you check your program code and ensure that no strings can
overflow buffers, that you use parameterised queries, that you check every
string not just for single quotes but also script and other HTML tags, for
code that can be evaluated to quotes, script, or html, that wherever
possible you remove as much need for free text entry as you can, do not
reflect errors verbatum back to the user - IE avoid replay attacks - do not
store data ever without sanitising it, do not show data with it being
santised (again), make everything as type safe as possible, normalise your
database properly, use maximum error and warning reporting when compiling,
use proper error handling, test the system thoroughly, try to break it, do
not accept warnings during compiles, and so on.
Add to that, use a DMZ, secure your web server, secure your database server,
enforce strong security... strong passwords,... I think you get the idea.
Now, look at your original question and ask yourself "was it responsible"?
Or do you think you can now check off the single quote character in
isolation? It is never a done task.
I suggest you re-read John's answer and references as a starter.
"Mark J. McGinty" <mmcginty@.spamfromyou.com> wrote in message
news:uX1WrSUhFHA.3256@.TK2MSFTNGP12.phx.gbl...
> "Susan S via droptable.com" <forum@.droptable.com> wrote in message
> news:510817BAE9A30@.droptable.com...
> You don't provide us with an example of your code that builds dynamic SQL
> so we can only guess as to what might or might not work. Have you
> considered char(39) being mixed in somehow?
> But the larger question is, what is your aversion to parameterizing your
> queries? If you were truly concerned about SQL injection, that's the
> route you would go. Will you feel safe if nobody here can come up with an
> example? Will that mean that it can't be done? You'd learn more hanging
> out in hacker's circles, if that is your approach..
> So, sorry if it offends you, and I'll admit that "I don't know" of a
> specific example, but the real answer to your rhetorical question is:
> parameterize, then you won't need to worry about it, there are no reasons
> it can't be done, only excuses.
>
> Good Luck,
> Mark
>
>
>
>|||Oh, of course, the biggest sin of them all - as Mark points out. Never use
dynamic SQL. You never need to for a correctly designed database.
"Mark J. McGinty" <mmcginty@.spamfromyou.com> wrote in message
news:uX1WrSUhFHA.3256@.TK2MSFTNGP12.phx.gbl...
> "Susan S via droptable.com" <forum@.droptable.com> wrote in message
> news:510817BAE9A30@.droptable.com...
> You don't provide us with an example of your code that builds dynamic SQL
> so we can only guess as to what might or might not work. Have you
> considered char(39) being mixed in somehow?
> But the larger question is, what is your aversion to parameterizing your
> queries? If you were truly concerned about SQL injection, that's the
> route you would go. Will you feel safe if nobody here can come up with an
> example? Will that mean that it can't be done? You'd learn more hanging
> out in hacker's circles, if that is your approach..
> So, sorry if it offends you, and I'll admit that "I don't know" of a
> specific example, but the real answer to your rhetorical question is:
> parameterize, then you won't need to worry about it, there are no reasons
> it can't be done, only excuses.
>
> Good Luck,
> Mark
>
>
>
>|||Wow.. it was just *ONE* simple request:
Post some *ACTUAL* code... that can cause SQL injection... where *ALL* singl
e
quote
characters have been removed from the string input by the user.
Seems like everyone is talking about eveything *OTHER* than that 1 simple
request.:

> Why not use another method instead?
> Why not fully secure your servers instead?
> Why not post your code?
> What are you using this for?
> You might needs users to enter single quotes some day.
> Why not write/use long parameterized methods instead?
> What if it's an INT, not a VarChar field?
> Why don't you go back and change 1000s of lines of code that you already w
rote?
> Let's talk about some totally different problems, instead of this one.
Sheezesss. That wasn't the question here. Here it is again:
s = GetUsersString() ' Get the input
s = Replace(s, "'", "") ' Remove all single-quotes
sql = "SELECT * FROM MyTable WHERE MyField='" & s & "'" ' Execute this
The Question:
In *ONE* line... without *ANY* explaination... what text would the user ente
r
as a value for "s",
that would break the above code sample?
So simple. No one can answer that? Or prove it can't be done?
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...curity/200507/1|||"Susan S via droptable.com" <forum@.droptable.com> wrote in message
news:5131626AFB930@.droptable.com...
> Wow.. it was just *ONE* simple request:
Considering all the good and relevant (despite your views) advice you've
received, it's obvious that you just don't get it; so go ahead and feel as
though you've written safe, secure code. Clearly, your mind is made up. I
think you've insulted the NG with your narrow-minded replies quite enough
for one thread.
What you refuse to accept is that it doesn't matter whether or not anyone in
this or any NG can provide you with an example. It simply doesn't matter.
It proves/disproves nothing. It does not mean it can't be done. You want
someone to prove for you that it *can* be done, otherwise you'll assume it
can't? That is a reckless approach at best.
So you choose to believe what you want to believe -- ok, fine. You chose to
ignore widely accepted best practices and now you want to justify that
choice -- that's all up to you. But don't expect the NG to validate those
choices, because -- and you know this as well as we do -- there is a body of
knowledge that says your chosen methodology is not the most secure, PERIOD,
bottom line, end of story.
And given today's Internet climate, why anyone would choose to do something
any way other than the most secure defies all reason. That's what we're on
about. That's why we can't get behind your over-simplification of the
issues.
And now I suppose you'll post another rude, frustrated, closed-minded reply
because I didn't stick to a one line answer... whatever... best of luck to
you too.
-Mark

prevent users from modifying tables or data but allow creating and modifying store procedu

Hello
Is there a way in Sql Server to prevent a user from modifying tables or data
but allow creating and modifying store procedures?
thanks!!
Hmmm, that would be a strange one. I guess you would want them to be able
to create a procedure, but they wouldn't be able to execute it (even if only
to test it), because they could easily put ALTER TABLE or UPDATE in there?
You can prevent writing of data by:
USE <database>
GO
EXEC sp_addrolemember 'db_denydatawriter', '<user name>'
GO
As for allowing CREATE PROCEDURE but not ALTER TABLE, hmm, not sure about
that one. With SQL Server 2005, you could roll back unauthorized table mods
using DDL triggers...
http://www.aspfaq.com/
(Reverse address to reply.)
"Zeng" <zzy@.nonospam.com> wrote in message
news:uKtrn3tjEHA.1644@.tk2msftngp13.phx.gbl...
> Hello
> Is there a way in Sql Server to prevent a user from modifying tables or
data
> but allow creating and modifying store procedures?
> thanks!!
>
|||right click database, properties, permissions tab.
there are check boxes you can use to deny users to create table but allow
them to create sp.
steve
"Zeng" <zzy@.nonospam.com> wrote in message
news:uKtrn3tjEHA.1644@.tk2msftngp13.phx.gbl...
> Hello
> Is there a way in Sql Server to prevent a user from modifying tables or
data
> but allow creating and modifying store procedures?
> thanks!!
>

prevent users from modifying tables or data but allow creating and modifying store procedu

Hello
Is there a way in Sql Server to prevent a user from modifying tables or data
but allow creating and modifying store procedures?
thanks!!Hmmm, that would be a strange one. I guess you would want them to be able
to create a procedure, but they wouldn't be able to execute it (even if only
to test it), because they could easily put ALTER TABLE or UPDATE in there?
You can prevent writing of data by:
USE <database>
GO
EXEC sp_addrolemember 'db_denydatawriter', '<user name>'
GO
As for allowing CREATE PROCEDURE but not ALTER TABLE, hmm, not sure about
that one. With SQL Server 2005, you could roll back unauthorized table mods
using DDL triggers...
--
http://www.aspfaq.com/
(Reverse address to reply.)
"Zeng" <zzy@.nonospam.com> wrote in message
news:uKtrn3tjEHA.1644@.tk2msftngp13.phx.gbl...
> Hello
> Is there a way in Sql Server to prevent a user from modifying tables or
data
> but allow creating and modifying store procedures?
> thanks!!
>|||right click database, properties, permissions tab.
there are check boxes you can use to deny users to create table but allow
them to create sp.
steve
"Zeng" <zzy@.nonospam.com> wrote in message
news:uKtrn3tjEHA.1644@.tk2msftngp13.phx.gbl...
> Hello
> Is there a way in Sql Server to prevent a user from modifying tables or
data
> but allow creating and modifying store procedures?
> thanks!!
>

prevent users from modifying tables or data but allow creating and modifying store pro

Hello
Is there a way in Sql Server to prevent a user from modifying tables or data
but allow creating and modifying store procedures?
thanks!!Hmmm, that would be a strange one. I guess you would want them to be able
to create a procedure, but they wouldn't be able to execute it (even if only
to test it), because they could easily put ALTER TABLE or UPDATE in there?
You can prevent writing of data by:
USE <database>
GO
EXEC sp_addrolemember 'db_denydatawriter', '<user name>'
GO
As for allowing CREATE PROCEDURE but not ALTER TABLE, hmm, not sure about
that one. With SQL Server 2005, you could roll back unauthorized table mods
using DDL triggers...
http://www.aspfaq.com/
(Reverse address to reply.)
"Zeng" <zzy@.nonospam.com> wrote in message
news:uKtrn3tjEHA.1644@.tk2msftngp13.phx.gbl...
> Hello
> Is there a way in Sql Server to prevent a user from modifying tables or
data
> but allow creating and modifying store procedures?
> thanks!!
>|||right click database, properties, permissions tab.
there are check boxes you can use to deny users to create table but allow
them to create sp.
steve
"Zeng" <zzy@.nonospam.com> wrote in message
news:uKtrn3tjEHA.1644@.tk2msftngp13.phx.gbl...
> Hello
> Is there a way in Sql Server to prevent a user from modifying tables or
data
> but allow creating and modifying store procedures?
> thanks!!
>