|
tan873 wrote: how to zip file with xp_cmdshell in sql server 2005
I suppose that depends on exactly what you mean.
First step how would you do it in a command shell?
Windows has the 'compact' command built in.
Comand line options are documented in the help for that as well as at the following.
http://www.microsoft.com/resources/documentation/windows/xp/all/proddocs/en-us/compact.mspx?mfr=true[^]
If you want to use something besides 'compact' then you must explore the command line options for that tool.
Once you know how to do it from a regular shell then you explore the options with the shell in sql server.
|
|
|
|
|
Hi!
I've to check the database for existency. i.e. My table has a field called name. I've to check whether a particular string is present in the field. e.g I've to check whether "ratha" is prsent in name. I'm using SQLite. How to do this? Which Query should I use?
|
|
|
|
|
Look at the LIKE keyword...
here...[^]
I don't speak Idiot - please talk slowly and clearly
'This space for rent'
Driven to the arms of Heineken by the wife
|
|
|
|
|
If I use LIKE key word,it doesn't fully serve my purpose. This is my query:
irr::core::stringc test = "Select name from profile WHERE name LIKE '%";
test += str.trim();
test += "%'";
result.clear();
result = pManager->SQLdb.Query(test);
With this query, I can't add a new name "sri" to the name field if "srinivasan" is already present in the name field. I don't want this. If "srinivasan" is present, I could not add names like "srinivasan","SRINIVASAN" or "SrInIvAsAn". i.e I've to check the exact name without regard to case. How to do this?
Another question is:
I've to restrict the number of records in a table. i.e I don't want to update the table if it already has 10 records in it. How to do this?
modified on Thursday, October 28, 2010 6:29 AM
|
|
|
|
|
T.RATHA KRISHNAN wrote: If I use LIKE key word,it doesn't fully serve my purpose.
It does however answer your first question.
With the additional detail that you have added then something like the following would be want you want. (Unless there are other yet unstated requirements.)
select lower(name) from profile WHERE name LIKE 'sri%"
T.RATHA KRISHNAN wrote: I've to restrict the number of records in a table. i.e I don't want to update the table if it already has 10 records in it. How to do this?
Odd requirement.
But do you know what 'count' does?
You might want to get a book or use the online documentation and read through all of the functions that are available as part of the database.
And if this system is part of a commercial/publicly available system you should look up sql injection attacks and ways to prevent that. Sql injection attacks are still a significant security problem.
|
|
|
|
|
hi
can we user parameters with the In function ??
example:
select * from tbl_Name where id in(par)
// par is a parameter pass to a stored procedure
i want to do this because i dont know how many ids i will get.
thank you
When you get mad...THINK twice that the only advice
Tamimi - Code
|
|
|
|
|
Example:
select * from tbl_Name where id in(1,2,3,4)
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
If my post helps you kindly save my time by voting my post.
www.aktualiteti.com
|
|
|
|
|
thank you..
i know this 
my question was how to do that with a parameter
When you get mad...THINK twice that the only advice
Tamimi - Code
|
|
|
|
|
Then you have to generate that numbers from select query.
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
If my post helps you kindly save my time by voting my post.
www.aktualiteti.com
|
|
|
|
|
mmmmmm
suppose you have a check box list that contains 50 items(id)
and the user can select as many as he wish from the list..
then how you can pass the selected ids to a stored procedure ??
giving that you don't know how many ids you will pass??
got me??
When you get mad...THINK twice that the only advice
Tamimi - Code
|
|
|
|
|
Tamimi - Code wrote: then how you can pass the selected ids to a stored procedure ??
Presuming that you really did mean stored procedure then...
First step, determine based on to create the stored procedure in the language supported by the database. Options that I have used for variable argument lists.
- Arrays
- Varchar with values as a comma separated values in that list.
- Proc with up to X args and of which can be null.
- Several procs each one with an increasing number of parameters: first has 5, second has 10, etc.
- dynamic SQL, run in a proc (only suitable for situations where input is known to be secure.)
Once you do in fact have a proc then you write code which populates the parameters dependent on the type of proc that actually exists.
Conversely without a proc, and just using SQL, one creates the SQL from scratch using code (for loops, string concatenation, etc) with the appropriate number of bind variables. Then one populates the bind variables. Then you run it.
To my mind the last option is easier than any solution with procs.
|
|
|
|
|
I guess your parameter might be comprising of a delimited string, say, "1,2,4,8" or even "{Guid1},{Guid2},...". If such is your requirement, you can always use some little tweaks.
Like
Declare a temporary table to store the Ids, like declare @Ids table(Id int)
Then, use some string functions to split the string and insert the ids into the table. (Like, CHARINDEX, SUBSTRING, etc)
After that, you can then use the temporary table on your WHERE ... IN ... clause
Like
select * from tbl_Name where id in (select Id from @Ids)
|
|
|
|
|
thank you
this is nice. i will give it a try.
or simply 
string strIds = "1,2,3,4";
SqlCommand com="select * from tbl_name where id in(" + strIds + ")";
When you get mad...THINK twice that the only advice
Tamimi - Code
|
|
|
|
|
Note: I'm not sure about MS-SQL, but in Oracle there is a limit to the number of values you can have in an "IN" clause. Back in Oracle 9, I believe the max was 1000.
for example: select * from myTable where ID IN (1,2,3,....,1000,1001) would fail because there was more than 1000 values listed.
I'm not sure if this is still the case, but you might want to consider it in your design.
Good luck. 
|
|
|
|
|
|
|
this is new to me
thank you... will read about it
When you get mad...THINK twice that the only advice
Tamimi - Code
|
|
|
|
|
Hi,
I am pretty new to database section. I tried porting a table created in Oracle to SQL Server 2005. In Oracle, i could see a column defined holding the user details. The column is defines as given below.
CREATE_USER VARCHAR2(255 BYTE) DEFAULT USER NOT NULL
My doubt is regarding default value USER.
I would like to know
1) What will be the value keyword "USER" will be returning in ORACLE. Is it an in built property or function which returns the default user?
2) And the equivalent value to be used for "USER" in SQL Server 2005?
Please help
Thanks,
Arudya
modified on Wednesday, October 27, 2010 12:01 AM
|
|
|
|
|
The value returned for USER will be the userid that was supplied to establish the connection. If SQL*Plus is available, login to an oracle database and try the command select user from dual. It should return the same value as you supplied as the userid when you logged in. As for the equivalent in SQL Server, I'd suggest try googling for an equivalent.
Chris Meech
I am Canadian. [heard in a local bar]
In theory there is no difference between theory and practice. In practice there is. [Yogi Berra]
|
|
|
|
|
Hi,
I have a form which including different sort of information. for instance, some information is regarding to personal data which related to Table1 and some are for Table2 and the some are for Table3. all this information are in 1 form.
Now, I just wondering to know, when I wanna insert data via Store procedure do I have to write SP for each of them?
is there any approach to insert data with writing just one SP.
how can I write SP, inserting data in Multi table. is it possible
|
|
|
|
|
If you insist on using stored procedures then there should be one that performs all the required statements in a transaction that can be rolled back.
Many practitioners seem to forget that by definition a procedure should contain more than one statement.
|
|
|
|
|
future3839 wrote: is there any approach to insert data with writing just one SP
This is a basic aspect of an SP
Receive values via variables in the procedure declaration
CREATE PROC StoreDumy
-- Declare
@Var1 VARCHAR(100),@Var2 VARCHAR(100),@Var3 VARCHAR(100),@Var4 VARCHAR(100),
@Var5 VARCHAR(100),@Var6 VARCHAR(100),@Var7 VARCHAR(100)
Insert the variables in the tables required. You may want to wrap the inserts in a transaction so that if 1 insert fails all inserts are rolled back and you are not left with orphaned data
As
DECLARE @ID int
begin TRAN
INSERT dbo.Country (Fld1, Fld2)
VALUES(@Var1, @Var2)
SET @ID = SCOPE_IDENTITY()
INSERT dbo.State (CtryID, Fld1, Fld2)
VALUES(@ID, @Var3, @Var4)
SET @ID = SCOPE_IDENTITY()
INSERT dbo.Town (StateID, Fld1, Fld2)
VALUES(@ID, @Var5, @Var6)
COMMIT TRAN
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
anyone has the URL for the english official forum of PostgreSQL NOT the Postgre Advanced Server..
|
|
|
|
|
jrahma wrote: anyone has the URL
And you want to use a database product that you cannot even find the URL for. What level of support do you think you are going to get for this DB. Oh wait, you are looking here, a MS centric site, for support 
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Mycroft Holmes wrote: Oh wait, you are looking here, a MS centric site, for support
Thanks, good point, I could not fathom why the question got down voted. I guess I look at things too narrowly.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
