Hi all,
I am not familiar with the Store Procedure, so just want to know how to create the store procedure? For example, i want to write a store procedure for Login validation to check whether the username and password is correct. So what should i do?Okay, first off I am FAMOUS for writing the kludgiest code on the planet. This proc may or may not work for you (it does for me). It may or may not be very fast. It may or may not meet with best practices guidelines (it probably doesn't). But it should give you some things to work with (a stored procedure, parameters, working with results). You need to do a bit of research on your own. Google is your friend.
Supporting Tables
create table Users (Username varchar(255), [Password] varchar(255))
Supporting Data
insert Users (UserName, [Password]) values ('tscott','123')
go
insert Users (UserName, [Password]) values ('ascott','abc')
go
insert Users (UserName, [Password]) values ('bscott','Abc')
go
select * from users2
Stored Procedure
CREATE PROC spValidatePassword ( @.UserName varchar(255),
@.Password varchar(255)
)
/************************************************** *****
* spValidatePassword
* hmscott
*
* Validates a username/password against a table of known username/passwords
* Warning: data is not encrypted; these passwords are not secure. This
* sample is for demonstration only and is not intended for production
* use.
*
* Parameters:
* IN: UserName The usernmae provided by the user
* Password The password provided by the user
*
* Returns:
* Single-row recordset (ReturnCode):
* -1: UserName not found
* 0: Password incorrect for this user
* 1: Password correct for this user
************************************************** ****/
AS
DECLARE @.LocalPassword varchar(255), @.ReturnCode int
SELECT @.LocalPassword = [Password]
FROM dbo.Users2
WHERE UserName = @.UserName
IF @.LocalPassword IS NULL
BEGIN
SELECT @.ReturnCode = -1 -- User not valid
END
ELSE
BEGIN
IF @.LocalPassword = @.Password COLLATE Latin1_General_CS_AS
-- IF @.LocalPassword = @.Password -- Use this line instead of the above line if you want the passwords to be case insensitive
BEGIN
SELECT @.ReturnCode = 1 -- Password matches
END
ELSE
BEGIN
SELECT @.ReturnCode = 0 -- Password does not match
END
END
SELECT @.ReturnCode as ReturnCode
spValidatePassword 'nouser', 'Foo'
spValidatePassword 'bscott', 'Abc'
spValidatePassword 'bscott', 'abc'sql
Showing posts with label store. Show all posts
Showing posts with label store. Show all posts
Friday, March 30, 2012
Monday, March 26, 2012
How to create image field?
Hello,
I would like to store some images in an msde table. When I go into the
enterprise manager design table mode and pick image as a field type, it
defaults to 16 bytes in size, and doesn't allow me to change the size. I
can't create a varbinary field greater than 8000 bytes either. The msde
instance is a recent install downloaded fresh from Microsoft, so it is the
current version (but for the life of me I can't figure out how to determine
the version #).
I've also tried using a shareware package called MSDE manager, but with the
same results.
How do I create an image field of 50K, for example?
thanks
Marc Pelletier
Hi,
It is not an issue, By default it will take bytes. Image data type will
allow you store a maximum of 2 GB. No need to change any thing
VARBINARY will allow a maximum of bytes.
Thanks
Hari
SQL Server MVP
"Marc Pelletier" <no.email@.please.com> wrote in message
news:Xns959A92CA0DCBAmpdd445@.216.168.3.44...
> Hello,
> I would like to store some images in an msde table. When I go into the
> enterprise manager design table mode and pick image as a field type, it
> defaults to 16 bytes in size, and doesn't allow me to change the size. I
> can't create a varbinary field greater than 8000 bytes either. The msde
> instance is a recent install downloaded fresh from Microsoft, so it is the
> current version (but for the life of me I can't figure out how to
> determine
> the version #).
> I've also tried using a shareware package called MSDE manager, but with
> the
> same results.
> How do I create an image field of 50K, for example?
> thanks
> Marc Pelletier
|||"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in news:eFR6PmUxEHA.1264
@.TK2MSFTNGP12.phx.gbl:
> It is not an issue, By default it will take bytes. Image data type will
> allow you store a maximum of 2 GB. No need to change any thing
> VARBINARY will allow a maximum of bytes.
>
I don't mean to look a gift horse in the mouth, but you haven't told me
anything. Do you mean to say that even though it appears to be 16 bytes, I
can actually stuff whatever I want in there? That doesn't seem likely.
cheers
Marc Pelletier
|||> I don't mean to look a gift horse in the mouth, but you haven't told me
> anything. Do you mean to say that even though it appears to be 16 bytes, I
> can actually stuff whatever I want in there? That doesn't seem likely.
Stop looking at enterprise manager to "tell you anything." The 16 bytes
represents a pointer to the data, not the actual data. Hari was not lying
when he said that you can store ~2 GB of data in there...
|||Typically, all that is stored in the data row for image, text, or ntext
columns is a 16 byte pointer to where the actual data is stored. You can
store up to 2GB of data for each data value using these data types.
For more information about how the data for these data types are stored,
see:
http://msdn.microsoft.com/library/?u...asp?frame=true
http://msdn.microsoft.com/library/?u...asp?frame=true
http://msdn.microsoft.com/library/?u...asp?frame=true
Alan Brewer [MSFT]
Content Architect
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
|||"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in
news:#l6OeKbxEHA.2876@.TK2MSFTNGP12.phx.gbl:
> Stop looking at enterprise manager to "tell you anything." The 16
> bytes represents a pointer to the data, not the actual data. Hari was
> not lying when he said that you can store ~2 GB of data in there...
>
Ok, thanks, I didn't think he was lying ( and if I gave that impression,
sorry). That being the case, how come varbinary allows me to set the size
up to 8000, when it also has a limit of ~2G? How would I define my field
to store a large amount of data?
I'm guessing that under the hood they are essentially the same type,
since I could stream any binary data into an image field anyway, couldn't
I?
thanks
Marc Pelletier
|||> sorry). That being the case, how come varbinary allows me to set the size
> up to 8000, when it also has a limit of ~2G?
Who said varbinary has a limit of ~2 GB?
> How would I define my field
> to store a large amount of data?
Using the IMAGE datatype, not VARBINARY.
> I'm guessing that under the hood they are essentially the same type,
> since I could stream any binary data into an image field anyway, couldn't
> I?
I'm not going to presume to know exactly how the engine works with these
types. But I know that they are stored at least slightly differently,
similar to VARCHAR(8000) and TEXT (even though, deep down, they are both
just storing text).
|||"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in
news:O0zalzcxEHA.2568@.TK2MSFTNGP10.phx.gbl:
> Who said varbinary has a limit of ~2 GB?
>
Well... I did. But I shouldn't have. If it isn't clear already, I'm new to
sql server. I generally vigorously avoid database programming, but can't
avoid it for my current project.
Thanks for the help, everyone. I think I'm away for now.
Marc
|||Hello,
I am new to databases. But i dont understand how one could store a picture
in a cell in a database? Or do you mean that there is a link to the picture
in the cell?
Thanks
WStoreyII
"Marc Pelletier" wrote:
> Hello,
> I would like to store some images in an msde table. When I go into the
> enterprise manager design table mode and pick image as a field type, it
> defaults to 16 bytes in size, and doesn't allow me to change the size. I
> can't create a varbinary field greater than 8000 bytes either. The msde
> instance is a recent install downloaded fresh from Microsoft, so it is the
> current version (but for the life of me I can't figure out how to determine
> the version #).
> I've also tried using a shareware package called MSDE manager, but with the
> same results.
> How do I create an image field of 50K, for example?
> thanks
> Marc Pelletier
>
|||=?Utf-8?B?V1N0b3JleUlJ?= <WStoreyII@.discussions.microsoft.com> wrote in
news:54836FEC-DDCB-4F80-85F0-13F580D4C292@.microsoft.com:
> I am new to databases. But i dont understand how one could store a
> picture in a cell in a database? Or do you mean that there is a link
> to the picture in the cell?
>
WStorey,
The image is stored as a binary stream directly in a blob (binary large
object, I think ) field of the database. It adds a substantial overhead to
the process, and I think that in many cases it is better to store the image
on disk and store a reference to it in the db, as you have suggested.
However it is what I am going to do. In my case the number ( and size) of
images will be fairly small and dynamic, maybe a couple of thousand, and
will be accessed a lot, so I expext the sql server cacheing to help with
the performance hit.
The how is explained quite clearly in
http://support.microsoft.com/default...en-us;309158#1
for the csharp case.
Hope this helps.
Marc Pelletier
I would like to store some images in an msde table. When I go into the
enterprise manager design table mode and pick image as a field type, it
defaults to 16 bytes in size, and doesn't allow me to change the size. I
can't create a varbinary field greater than 8000 bytes either. The msde
instance is a recent install downloaded fresh from Microsoft, so it is the
current version (but for the life of me I can't figure out how to determine
the version #).
I've also tried using a shareware package called MSDE manager, but with the
same results.
How do I create an image field of 50K, for example?
thanks
Marc Pelletier
Hi,
It is not an issue, By default it will take bytes. Image data type will
allow you store a maximum of 2 GB. No need to change any thing
VARBINARY will allow a maximum of bytes.
Thanks
Hari
SQL Server MVP
"Marc Pelletier" <no.email@.please.com> wrote in message
news:Xns959A92CA0DCBAmpdd445@.216.168.3.44...
> Hello,
> I would like to store some images in an msde table. When I go into the
> enterprise manager design table mode and pick image as a field type, it
> defaults to 16 bytes in size, and doesn't allow me to change the size. I
> can't create a varbinary field greater than 8000 bytes either. The msde
> instance is a recent install downloaded fresh from Microsoft, so it is the
> current version (but for the life of me I can't figure out how to
> determine
> the version #).
> I've also tried using a shareware package called MSDE manager, but with
> the
> same results.
> How do I create an image field of 50K, for example?
> thanks
> Marc Pelletier
|||"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in news:eFR6PmUxEHA.1264
@.TK2MSFTNGP12.phx.gbl:
> It is not an issue, By default it will take bytes. Image data type will
> allow you store a maximum of 2 GB. No need to change any thing
> VARBINARY will allow a maximum of bytes.
>
I don't mean to look a gift horse in the mouth, but you haven't told me
anything. Do you mean to say that even though it appears to be 16 bytes, I
can actually stuff whatever I want in there? That doesn't seem likely.
cheers
Marc Pelletier
|||> I don't mean to look a gift horse in the mouth, but you haven't told me
> anything. Do you mean to say that even though it appears to be 16 bytes, I
> can actually stuff whatever I want in there? That doesn't seem likely.
Stop looking at enterprise manager to "tell you anything." The 16 bytes
represents a pointer to the data, not the actual data. Hari was not lying
when he said that you can store ~2 GB of data in there...
|||Typically, all that is stored in the data row for image, text, or ntext
columns is a 16 byte pointer to where the actual data is stored. You can
store up to 2GB of data for each data value using these data types.
For more information about how the data for these data types are stored,
see:
http://msdn.microsoft.com/library/?u...asp?frame=true
http://msdn.microsoft.com/library/?u...asp?frame=true
http://msdn.microsoft.com/library/?u...asp?frame=true
Alan Brewer [MSFT]
Content Architect
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
|||"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in
news:#l6OeKbxEHA.2876@.TK2MSFTNGP12.phx.gbl:
> Stop looking at enterprise manager to "tell you anything." The 16
> bytes represents a pointer to the data, not the actual data. Hari was
> not lying when he said that you can store ~2 GB of data in there...
>
Ok, thanks, I didn't think he was lying ( and if I gave that impression,
sorry). That being the case, how come varbinary allows me to set the size
up to 8000, when it also has a limit of ~2G? How would I define my field
to store a large amount of data?
I'm guessing that under the hood they are essentially the same type,
since I could stream any binary data into an image field anyway, couldn't
I?
thanks
Marc Pelletier
|||> sorry). That being the case, how come varbinary allows me to set the size
> up to 8000, when it also has a limit of ~2G?
Who said varbinary has a limit of ~2 GB?
> How would I define my field
> to store a large amount of data?
Using the IMAGE datatype, not VARBINARY.
> I'm guessing that under the hood they are essentially the same type,
> since I could stream any binary data into an image field anyway, couldn't
> I?
I'm not going to presume to know exactly how the engine works with these
types. But I know that they are stored at least slightly differently,
similar to VARCHAR(8000) and TEXT (even though, deep down, they are both
just storing text).
|||"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in
news:O0zalzcxEHA.2568@.TK2MSFTNGP10.phx.gbl:
> Who said varbinary has a limit of ~2 GB?
>
Well... I did. But I shouldn't have. If it isn't clear already, I'm new to
sql server. I generally vigorously avoid database programming, but can't
avoid it for my current project.
Thanks for the help, everyone. I think I'm away for now.
Marc
|||Hello,
I am new to databases. But i dont understand how one could store a picture
in a cell in a database? Or do you mean that there is a link to the picture
in the cell?
Thanks
WStoreyII
"Marc Pelletier" wrote:
> Hello,
> I would like to store some images in an msde table. When I go into the
> enterprise manager design table mode and pick image as a field type, it
> defaults to 16 bytes in size, and doesn't allow me to change the size. I
> can't create a varbinary field greater than 8000 bytes either. The msde
> instance is a recent install downloaded fresh from Microsoft, so it is the
> current version (but for the life of me I can't figure out how to determine
> the version #).
> I've also tried using a shareware package called MSDE manager, but with the
> same results.
> How do I create an image field of 50K, for example?
> thanks
> Marc Pelletier
>
|||=?Utf-8?B?V1N0b3JleUlJ?= <WStoreyII@.discussions.microsoft.com> wrote in
news:54836FEC-DDCB-4F80-85F0-13F580D4C292@.microsoft.com:
> I am new to databases. But i dont understand how one could store a
> picture in a cell in a database? Or do you mean that there is a link
> to the picture in the cell?
>
WStorey,
The image is stored as a binary stream directly in a blob (binary large
object, I think ) field of the database. It adds a substantial overhead to
the process, and I think that in many cases it is better to store the image
on disk and store a reference to it in the db, as you have suggested.
However it is what I am going to do. In my case the number ( and size) of
images will be fairly small and dynamic, maybe a couple of thousand, and
will be accessed a lot, so I expext the sql server cacheing to help with
the performance hit.
The how is explained quite clearly in
http://support.microsoft.com/default...en-us;309158#1
for the csharp case.
Hope this helps.
Marc Pelletier
Friday, March 23, 2012
How to create column in existing table
Hi i want to create a store procedure
i have table called ABC which has 10 column and have lot of data now i want
to design a store procedure to add another emplty column into that table
which i will fill with another store procedure that i already have thanksUse Alter Table..ADD
see example
CREATE TABLE ABC (id INT,SomeColumn VARCHAR(49))
SELECT * FROM ABC
ALTER TABLE ABC
ADD SomeOtherColumn int
SELECT * FROM ABC
Denis the SQL Menace
http://sqlservercode.blogspot.com/|||You're going to do this once, or all the time? If this is something you're
going to be doing repeatedly, I'd strongly recommend reviewing your
requirements and coming up with a more sane implementation plan.
"amjad" <amjad@.discussions.microsoft.com> wrote in message
news:DB18DF5B-16CF-4918-A42D-8406A1617675@.microsoft.com...
> Hi i want to create a store procedure
> i have table called ABC which has 10 column and have lot of data now i
> want
> to design a store procedure to add another emplty column into that table
> which i will fill with another store procedure that i already have thankssql
i have table called ABC which has 10 column and have lot of data now i want
to design a store procedure to add another emplty column into that table
which i will fill with another store procedure that i already have thanksUse Alter Table..ADD
see example
CREATE TABLE ABC (id INT,SomeColumn VARCHAR(49))
SELECT * FROM ABC
ALTER TABLE ABC
ADD SomeOtherColumn int
SELECT * FROM ABC
Denis the SQL Menace
http://sqlservercode.blogspot.com/|||You're going to do this once, or all the time? If this is something you're
going to be doing repeatedly, I'd strongly recommend reviewing your
requirements and coming up with a more sane implementation plan.
"amjad" <amjad@.discussions.microsoft.com> wrote in message
news:DB18DF5B-16CF-4918-A42D-8406A1617675@.microsoft.com...
> Hi i want to create a store procedure
> i have table called ABC which has 10 column and have lot of data now i
> want
> to design a store procedure to add another emplty column into that table
> which i will fill with another store procedure that i already have thankssql
how to create array column and how to retrive in sqlserver
hi
i am using database sqlserver,
i am searching for varray concept like in oracle to store multiple values in a single column(as array column) like that shell i do in sql server
1.how to create array column in a table using sqlserver
if possible how can i use select query for that
there is no array concept in sql server. you can however store the values as a concatenated string with a delimiter and use some custom function to parse through the string to split them up.|||Try the link below for samples using the string functions in SQL Server and Oracle PL/SQL is closer to C++ than T-SQL. I would also look at Ken Henderson books at my local bookstore run a search for String functions in the BOL(books online). Hope this helps.
http://vyaskn.tripod.com/passing_arrays_to_stored_procedures.htm
|||Why do you want to store multiple values in a single column? Thishas some pretty huge implications from a relational modelingstandpoint, not to mention the fact that it's going to kill performanceif you ever need to query the thing. If you can post someinformation about what business problem you're trying to solve, I'mcertian we can help you find a better solution.|||
Sorry I forgot I have a link with SQL Server Arrays. Try the link below. Hope this helps.
http://www.sommarskog.se/arrays-in-sql.html
Wednesday, March 21, 2012
How to create a storeprocedure
Hi,
I'm very very new in SQLServer.
I need to create a store procedure that execute a sql file located in a PATH
for example:
C:\Inetpub\Reserved\update.sql
HOw can I made this?
Thanks a lot!Hi
what version of sql server you using.
if you have sql server installed on your computer, then you can open .sql
file in query analyser or management studio to execute it
i still don't understand why you a need a SP to execute update.sql file
Regards
VT
Knowledge is power, share it...
http://oneplace4sql.blogspot.com/
"Andrea" <noreply@.nospam.net> wrote in message
news:5Fkni.2101$BM.412@.tornado.fastwebnet.it...
> Hi,
> I'm very very new in SQLServer.
> I need to create a store procedure that execute a sql file located in a
> PATH
> for example:
>
> C:\Inetpub\Reserved\update.sql
>
>
> HOw can I made this?
>
> Thanks a lot!
>
>|||Andrea
Sorry, I type it from the memory, please test it
EXEC master..xp_cmdshell 'osql.exe -S SVR -U sa -P pswd -i
"c:\Inetpub\Reserved\update.sql" -o "C:\output.txt"'
"Andrea" <noreply@.nospam.net> wrote in message
news:5Fkni.2101$BM.412@.tornado.fastwebnet.it...
> Hi,
> I'm very very new in SQLServer.
> I need to create a store procedure that execute a sql file located in a
> PATH
> for example:
>
> C:\Inetpub\Reserved\update.sql
>
>
> HOw can I made this?
>
> Thanks a lot!
>
>sql
I'm very very new in SQLServer.
I need to create a store procedure that execute a sql file located in a PATH
for example:
C:\Inetpub\Reserved\update.sql
HOw can I made this?
Thanks a lot!Hi
what version of sql server you using.
if you have sql server installed on your computer, then you can open .sql
file in query analyser or management studio to execute it
i still don't understand why you a need a SP to execute update.sql file
Regards
VT
Knowledge is power, share it...
http://oneplace4sql.blogspot.com/
"Andrea" <noreply@.nospam.net> wrote in message
news:5Fkni.2101$BM.412@.tornado.fastwebnet.it...
> Hi,
> I'm very very new in SQLServer.
> I need to create a store procedure that execute a sql file located in a
> PATH
> for example:
>
> C:\Inetpub\Reserved\update.sql
>
>
> HOw can I made this?
>
> Thanks a lot!
>
>|||Andrea
Sorry, I type it from the memory, please test it
EXEC master..xp_cmdshell 'osql.exe -S SVR -U sa -P pswd -i
"c:\Inetpub\Reserved\update.sql" -o "C:\output.txt"'
"Andrea" <noreply@.nospam.net> wrote in message
news:5Fkni.2101$BM.412@.tornado.fastwebnet.it...
> Hi,
> I'm very very new in SQLServer.
> I need to create a store procedure that execute a sql file located in a
> PATH
> for example:
>
> C:\Inetpub\Reserved\update.sql
>
>
> HOw can I made this?
>
> Thanks a lot!
>
>sql
How to create a storeprocedure
Hi,
I'm very very new in SQLServer.
I need to create a store procedure that execute a sql file located in a PATH
for example:
C:\Inetpub\Reserved\update.sql
HOw can I made this?
Thanks a lot!
Andrea
Sorry, I type it from the memory, please test it
EXEC master..xp_cmdshell 'osql.exe -S SVR -U sa -P pswd -i
"c:\Inetpub\Reserved\update.sql" -o "C:\output.txt"'
"Andrea" <noreply@.nospam.net> wrote in message
news:5Fkni.2101$BM.412@.tornado.fastwebnet.it...
> Hi,
> I'm very very new in SQLServer.
> I need to create a store procedure that execute a sql file located in a
> PATH
> for example:
>
> C:\Inetpub\Reserved\update.sql
>
>
> HOw can I made this?
>
> Thanks a lot!
>
>
I'm very very new in SQLServer.
I need to create a store procedure that execute a sql file located in a PATH
for example:
C:\Inetpub\Reserved\update.sql
HOw can I made this?
Thanks a lot!
Andrea
Sorry, I type it from the memory, please test it
EXEC master..xp_cmdshell 'osql.exe -S SVR -U sa -P pswd -i
"c:\Inetpub\Reserved\update.sql" -o "C:\output.txt"'
"Andrea" <noreply@.nospam.net> wrote in message
news:5Fkni.2101$BM.412@.tornado.fastwebnet.it...
> Hi,
> I'm very very new in SQLServer.
> I need to create a store procedure that execute a sql file located in a
> PATH
> for example:
>
> C:\Inetpub\Reserved\update.sql
>
>
> HOw can I made this?
>
> Thanks a lot!
>
>
How to create a storeprocedure
Hi,
I'm very very new in SQLServer.
I need to create a store procedure that execute a sql file located in a PATH
for example:
C:\Inetpub\Reserved\update.sql
HOw can I made this?
Thanks a lot!Hi
what version of sql server you using.
if you have sql server installed on your computer, then you can open .sql
file in query analyser or management studio to execute it
i still don't understand why you a need a SP to execute update.sql file
Regards
VT
Knowledge is power, share it...
http://oneplace4sql.blogspot.com/
"Andrea" <noreply@.nospam.net> wrote in message
news:5Fkni.2101$BM.412@.tornado.fastwebnet.it...
> Hi,
> I'm very very new in SQLServer.
> I need to create a store procedure that execute a sql file located in a
> PATH
> for example:
>
> C:\Inetpub\Reserved\update.sql
>
>
> HOw can I made this?
>
> Thanks a lot!
>
>|||Andrea
Sorry, I type it from the memory, please test it
EXEC master..xp_cmdshell 'osql.exe -S SVR -U sa -P pswd -i
"c:\Inetpub\Reserved\update.sql" -o "C:\output.txt"'
"Andrea" <noreply@.nospam.net> wrote in message
news:5Fkni.2101$BM.412@.tornado.fastwebnet.it...
> Hi,
> I'm very very new in SQLServer.
> I need to create a store procedure that execute a sql file located in a
> PATH
> for example:
>
> C:\Inetpub\Reserved\update.sql
>
>
> HOw can I made this?
>
> Thanks a lot!
>
>
I'm very very new in SQLServer.
I need to create a store procedure that execute a sql file located in a PATH
for example:
C:\Inetpub\Reserved\update.sql
HOw can I made this?
Thanks a lot!Hi
what version of sql server you using.
if you have sql server installed on your computer, then you can open .sql
file in query analyser or management studio to execute it
i still don't understand why you a need a SP to execute update.sql file
Regards
VT
Knowledge is power, share it...
http://oneplace4sql.blogspot.com/
"Andrea" <noreply@.nospam.net> wrote in message
news:5Fkni.2101$BM.412@.tornado.fastwebnet.it...
> Hi,
> I'm very very new in SQLServer.
> I need to create a store procedure that execute a sql file located in a
> PATH
> for example:
>
> C:\Inetpub\Reserved\update.sql
>
>
> HOw can I made this?
>
> Thanks a lot!
>
>|||Andrea
Sorry, I type it from the memory, please test it
EXEC master..xp_cmdshell 'osql.exe -S SVR -U sa -P pswd -i
"c:\Inetpub\Reserved\update.sql" -o "C:\output.txt"'
"Andrea" <noreply@.nospam.net> wrote in message
news:5Fkni.2101$BM.412@.tornado.fastwebnet.it...
> Hi,
> I'm very very new in SQLServer.
> I need to create a store procedure that execute a sql file located in a
> PATH
> for example:
>
> C:\Inetpub\Reserved\update.sql
>
>
> HOw can I made this?
>
> Thanks a lot!
>
>
Friday, March 9, 2012
how to create a DTS with parameters and call it fom a SP with the associeted paramete
Hi,
I've create a DTS package with 2 global parameters, I need to call this DTS
from a Store Procedure and pass the 2 parameters.
How Can I do that in T-SQL ?
If it's not possible, what I want to do is the following :
Copy data (with a date filter) from one table to another (on another SQL
server) in T-SQL. Is there a way to do that in TSQL ?
Thanks for your help,
H. MAILLARD.You can execute DTSRUN via xp_cmdshell or use the sp_OA* procs to execute a
DTS package from Transact-SQL. See http://www.sqldts.com/default.aspx?210.
Hope this helps.
Dan Guzman
SQL Server MVP
"Herve MAILLARD" <rvmaillard@.newsgroup.nospam> wrote in message
news:%237V6QFzHFHA.1860@.TK2MSFTNGP15.phx.gbl...
> Hi,
> I've create a DTS package with 2 global parameters, I need to call this
> DTS
> from a Store Procedure and pass the 2 parameters.
> How Can I do that in T-SQL ?
> If it's not possible, what I want to do is the following :
> Copy data (with a date filter) from one table to another (on another SQL
> server) in T-SQL. Is there a way to do that in TSQL ?
>
> Thanks for your help,
> H. MAILLARD.
>
>
I've create a DTS package with 2 global parameters, I need to call this DTS
from a Store Procedure and pass the 2 parameters.
How Can I do that in T-SQL ?
If it's not possible, what I want to do is the following :
Copy data (with a date filter) from one table to another (on another SQL
server) in T-SQL. Is there a way to do that in TSQL ?
Thanks for your help,
H. MAILLARD.You can execute DTSRUN via xp_cmdshell or use the sp_OA* procs to execute a
DTS package from Transact-SQL. See http://www.sqldts.com/default.aspx?210.
Hope this helps.
Dan Guzman
SQL Server MVP
"Herve MAILLARD" <rvmaillard@.newsgroup.nospam> wrote in message
news:%237V6QFzHFHA.1860@.TK2MSFTNGP15.phx.gbl...
> Hi,
> I've create a DTS package with 2 global parameters, I need to call this
> DTS
> from a Store Procedure and pass the 2 parameters.
> How Can I do that in T-SQL ?
> If it's not possible, what I want to do is the following :
> Copy data (with a date filter) from one table to another (on another SQL
> server) in T-SQL. Is there a way to do that in TSQL ?
>
> Thanks for your help,
> H. MAILLARD.
>
>
How to create a database that can support multiple language ?
Hi:
Is it possible to create a database and tables which can store
multiple language ? I need to create a table with a column that can
accept English, Chinese, Japan. Korea and Thai word.
Thanks
JCVoonyes.
--
Wei Xiao [MSFT]
SQL Server Storage Engine Development
http://weblogs.asp.net/weix
This posting is provided "AS IS" with no warranties, and confers no rights.
"JC Voon" <jcvoon_99@.yahoo.com> wrote in message
news:42228b35.7044749@.msnews.microsoft.com...
> Hi:
> Is it possible to create a database and tables which can store
> multiple language ? I need to create a table with a column that can
> accept English, Chinese, Japan. Korea and Thai word.
> Thanks
> JCVoon|||Wei Xiao :
Please tell me how...
Thanks
JCVoon
> Is it possible to create a database and tables which can store
> multiple language ? I need to create a table with a column that can
> accept English, Chinese, Japan. Korea and Thai word.|||Unicode is easist choice if it works for you.
If you just need store and retrieve these strings as opaque binary objects,
then you can use binary.
These articles might help:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/architec/8_ar_da_6ttf.asp
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/createdb/cm_8_des_04_6zp0.asp
Wei Xiao [MSFT]
SQL Server Storage Engine Development
http://weblogs.asp.net/weix
This posting is provided "AS IS" with no warranties, and confers no rights.
"JC Voon" <jcvoon_99@.yahoo.com> wrote in message
news:4225f10c.33116579@.msnews.microsoft.com...
> Wei Xiao :
> Please tell me how...
> Thanks
> JCVoon
>> Is it possible to create a database and tables which can store
>> multiple language ? I need to create a table with a column that can
>> accept English, Chinese, Japan. Korea and Thai word.
>|||wei xiao:
Thank for the reply.
I've declare the field to use NVarChar, and it work for me now.
Do i still need to consider the Collation setting in both field or
database level ?
Thanks
JCVoon
>Unicode is easist choice if it works for you.
>If you just need store and retrieve these strings as opaque binary objects,
>then you can use binary.
>These articles might help:
>http://msdn.microsoft.com/library/default.asp?url=/library/en-us/architec/8_ar_da_6ttf.asp
>http://msdn.microsoft.com/library/default.asp?url=/library/en-us/createdb/cm_8_des_04_6zp0.asp
Is it possible to create a database and tables which can store
multiple language ? I need to create a table with a column that can
accept English, Chinese, Japan. Korea and Thai word.
Thanks
JCVoonyes.
--
Wei Xiao [MSFT]
SQL Server Storage Engine Development
http://weblogs.asp.net/weix
This posting is provided "AS IS" with no warranties, and confers no rights.
"JC Voon" <jcvoon_99@.yahoo.com> wrote in message
news:42228b35.7044749@.msnews.microsoft.com...
> Hi:
> Is it possible to create a database and tables which can store
> multiple language ? I need to create a table with a column that can
> accept English, Chinese, Japan. Korea and Thai word.
> Thanks
> JCVoon|||Wei Xiao :
Please tell me how...
Thanks
JCVoon
> Is it possible to create a database and tables which can store
> multiple language ? I need to create a table with a column that can
> accept English, Chinese, Japan. Korea and Thai word.|||Unicode is easist choice if it works for you.
If you just need store and retrieve these strings as opaque binary objects,
then you can use binary.
These articles might help:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/architec/8_ar_da_6ttf.asp
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/createdb/cm_8_des_04_6zp0.asp
Wei Xiao [MSFT]
SQL Server Storage Engine Development
http://weblogs.asp.net/weix
This posting is provided "AS IS" with no warranties, and confers no rights.
"JC Voon" <jcvoon_99@.yahoo.com> wrote in message
news:4225f10c.33116579@.msnews.microsoft.com...
> Wei Xiao :
> Please tell me how...
> Thanks
> JCVoon
>> Is it possible to create a database and tables which can store
>> multiple language ? I need to create a table with a column that can
>> accept English, Chinese, Japan. Korea and Thai word.
>|||wei xiao:
Thank for the reply.
I've declare the field to use NVarChar, and it work for me now.
Do i still need to consider the Collation setting in both field or
database level ?
Thanks
JCVoon
>Unicode is easist choice if it works for you.
>If you just need store and retrieve these strings as opaque binary objects,
>then you can use binary.
>These articles might help:
>http://msdn.microsoft.com/library/default.asp?url=/library/en-us/architec/8_ar_da_6ttf.asp
>http://msdn.microsoft.com/library/default.asp?url=/library/en-us/createdb/cm_8_des_04_6zp0.asp
Wednesday, March 7, 2012
How to crack encrypted store procedure in SQL 2005?
Dear all,
Is it possible to crack encrypted store procedures in SQL 2005? I have an
application that uses a lot of stored procedures in SQL 2005 that I must
protect the source codes in store procedures. I am worry about users can
crack encrypted stored procedures so my application source codes will be
re-engineering completely.
Please share with me your thoughts how to protect source codes in stored
procedure in SQL server.
Thanks.
Maggie.Maggie,
Yes, they can be cracked.
If it is worth $79.95 to someone: http://www.devlib.net/decryptsql.htm
If it is worth $99 to someone: http://www.elitude.net/
And then there is this from the August 2007 SQL Server Magazine:
http://www.sqlmag.com/Article/Artic...rver_95728.html
Note that the code is downloadable, even if you are not a subscriber who can
read the whole article. It does require a significant number of rights to
be able to run this decryption procedure, but on another server not in your
control you have no control over what people do.
You can encrypt, of course, but legal protection comes through contracts and
other legal agreements.
RLF
"Maggie" <Maggie@.discussions.microsoft.com> wrote in message
news:DB70D424-0BE5-490C-9794-6676AFE47E2E@.microsoft.com...
> Dear all,
> Is it possible to crack encrypted store procedures in SQL 2005? I have an
> application that uses a lot of stored procedures in SQL 2005 that I must
> protect the source codes in store procedures. I am worry about users can
> crack encrypted stored procedures so my application source codes will be
> re-engineering completely.
> Please share with me your thoughts how to protect source codes in stored
> procedure in SQL server.
> Thanks.
> Maggie.
>|||Hi Russell,
Thanks a lot for the reply, it helps me a lot.
Maggie.
"Russell Fields" wrote:
> Maggie,
> Yes, they can be cracked.
> If it is worth $79.95 to someone: http://www.devlib.net/decryptsql.htm
> If it is worth $99 to someone: http://www.elitude.net/
> And then there is this from the August 2007 SQL Server Magazine:
> http://www.sqlmag.com/Article/Artic...rver_95728.html
> Note that the code is downloadable, even if you are not a subscriber who c
an
> read the whole article. It does require a significant number of rights to
> be able to run this decryption procedure, but on another server not in you
r
> control you have no control over what people do.
> You can encrypt, of course, but legal protection comes through contracts a
nd
> other legal agreements.
> RLF
> "Maggie" <Maggie@.discussions.microsoft.com> wrote in message
> news:DB70D424-0BE5-490C-9794-6676AFE47E2E@.microsoft.com...
>
>
Is it possible to crack encrypted store procedures in SQL 2005? I have an
application that uses a lot of stored procedures in SQL 2005 that I must
protect the source codes in store procedures. I am worry about users can
crack encrypted stored procedures so my application source codes will be
re-engineering completely.
Please share with me your thoughts how to protect source codes in stored
procedure in SQL server.
Thanks.
Maggie.Maggie,
Yes, they can be cracked.
If it is worth $79.95 to someone: http://www.devlib.net/decryptsql.htm
If it is worth $99 to someone: http://www.elitude.net/
And then there is this from the August 2007 SQL Server Magazine:
http://www.sqlmag.com/Article/Artic...rver_95728.html
Note that the code is downloadable, even if you are not a subscriber who can
read the whole article. It does require a significant number of rights to
be able to run this decryption procedure, but on another server not in your
control you have no control over what people do.
You can encrypt, of course, but legal protection comes through contracts and
other legal agreements.
RLF
"Maggie" <Maggie@.discussions.microsoft.com> wrote in message
news:DB70D424-0BE5-490C-9794-6676AFE47E2E@.microsoft.com...
> Dear all,
> Is it possible to crack encrypted store procedures in SQL 2005? I have an
> application that uses a lot of stored procedures in SQL 2005 that I must
> protect the source codes in store procedures. I am worry about users can
> crack encrypted stored procedures so my application source codes will be
> re-engineering completely.
> Please share with me your thoughts how to protect source codes in stored
> procedure in SQL server.
> Thanks.
> Maggie.
>|||Hi Russell,
Thanks a lot for the reply, it helps me a lot.
Maggie.
"Russell Fields" wrote:
> Maggie,
> Yes, they can be cracked.
> If it is worth $79.95 to someone: http://www.devlib.net/decryptsql.htm
> If it is worth $99 to someone: http://www.elitude.net/
> And then there is this from the August 2007 SQL Server Magazine:
> http://www.sqlmag.com/Article/Artic...rver_95728.html
> Note that the code is downloadable, even if you are not a subscriber who c
an
> read the whole article. It does require a significant number of rights to
> be able to run this decryption procedure, but on another server not in you
r
> control you have no control over what people do.
> You can encrypt, of course, but legal protection comes through contracts a
nd
> other legal agreements.
> RLF
> "Maggie" <Maggie@.discussions.microsoft.com> wrote in message
> news:DB70D424-0BE5-490C-9794-6676AFE47E2E@.microsoft.com...
>
>
How to covert Oracle Store Procedure to SQLServer
Is there any easy to do this, covert Oracle store procedure to SQL Server 2000.
Any help is thankful.
--iqueen.try using the oracle stored procedure directly in oracle.
I hope it will execute.
Again it depends on what version of oracle u have.
I have used sql SP on oracle with minor changes|||Originally posted by cyrus
I have used sql SP on oracle with minor changes
I doubt it...
Why not post some code to show us though...|||the problem is : I have change my Database Server, Oracle->SQLServer2000, the data have transfer to SQL Server, easy done with DTS, but Store Procedure won't, what shall I do ,wanna enable the same store procedure in SQL Server.
Any help is thankful.
--iqueen.|||There is really a lot of store procedure , more than 2000.|||I do not have ORACLE here however I think it can be done using DTS.
After you select the Target Source and Destination Source, you are provided with 3 options one of which says "Copy Objects and..."
If you opt for this option, you get options for all types of objects.
Please let me know if that does not work. Thanks.|||DTS can NOT transfer the store procedure... :(:(:(
Originally posted by tampu
I do not have ORACLE here however I think it can be done using DTS.
After you select the Target Source and Destination Source, you are provided with 3 options one of which says "Copy Objects and..."
If you opt for this option, you get options for all types of objects.
Please let me know if that does not work. Thanks.|||Oracle uses PL-SQL and SQL Server uses T-SQL.. . . .although they are much similar as regards ANSI standards but syntactical differences are definitely there. .. . .. ..functions... . processes and other things sre different
you can refer to microsoft site for they provide much info as to shift orcale code to SQL Server ... ..|||see if this attachment helps u|||the attachment contains basics of oracle Architecture :D
it will not help for Oracle to Sql 2k conversion.
Regards,
Selva Balaji B.|||Sorry ,Probably I gave a wrong one
There no concrete help but difference between oracle and sql
see if it helps u|||Hi cyrus
This is an useful document. :)
Regards,
Selva Balaji B.
Any help is thankful.
--iqueen.try using the oracle stored procedure directly in oracle.
I hope it will execute.
Again it depends on what version of oracle u have.
I have used sql SP on oracle with minor changes|||Originally posted by cyrus
I have used sql SP on oracle with minor changes
I doubt it...
Why not post some code to show us though...|||the problem is : I have change my Database Server, Oracle->SQLServer2000, the data have transfer to SQL Server, easy done with DTS, but Store Procedure won't, what shall I do ,wanna enable the same store procedure in SQL Server.
Any help is thankful.
--iqueen.|||There is really a lot of store procedure , more than 2000.|||I do not have ORACLE here however I think it can be done using DTS.
After you select the Target Source and Destination Source, you are provided with 3 options one of which says "Copy Objects and..."
If you opt for this option, you get options for all types of objects.
Please let me know if that does not work. Thanks.|||DTS can NOT transfer the store procedure... :(:(:(
Originally posted by tampu
I do not have ORACLE here however I think it can be done using DTS.
After you select the Target Source and Destination Source, you are provided with 3 options one of which says "Copy Objects and..."
If you opt for this option, you get options for all types of objects.
Please let me know if that does not work. Thanks.|||Oracle uses PL-SQL and SQL Server uses T-SQL.. . . .although they are much similar as regards ANSI standards but syntactical differences are definitely there. .. . .. ..functions... . processes and other things sre different
you can refer to microsoft site for they provide much info as to shift orcale code to SQL Server ... ..|||see if this attachment helps u|||the attachment contains basics of oracle Architecture :D
it will not help for Oracle to Sql 2k conversion.
Regards,
Selva Balaji B.|||Sorry ,Probably I gave a wrong one
There no concrete help but difference between oracle and sql
see if it helps u|||Hi cyrus
This is an useful document. :)
Regards,
Selva Balaji B.
How to covert data from visual foxpro database to SQL server
Hi,
I have my data all store in visual foxpro
database, if i want to change my database to SQL server,
how do i copy all the data in visual foxpro and save in
SQL Server Database? Thank you.We are currently taking a lot of our data from Foxpro to SQL Server. We are
only taking Data and Structure. We simply used the Foxpro drivers and
created a link to the Foxpro DB. Using DTS we sucked the Data in. The
usual things are worth looking out for like Dates but besides that we have
no real problems with it.
--
Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org
"florence" <florencelee@.visualsolutions.com.my> wrote in message
news:696501c3437c$d5f33f40$a401280a@.phx.gbl...
> Hi,
> I have my data all store in visual foxpro
> database, if i want to change my database to SQL server,
> how do i copy all the data in visual foxpro and save in
> SQL Server Database? Thank you.|||The converse of this method is to create an SQL Server table to receive the
data and then create a remote view to that table on the FoxPro side. After
that you only need to APPEND FoxPro data to the view and issue a
TABLEUPDATE().
--
Cindy Winegarden MCSD, Microsoft Visual FoxPro MVP
cindy.winegarden@.mvps.org, www.cindywinegarden.com
"Allan Mitchell" <allan@.no-spam.sqldts.com> wrote in message
news:e2klnp4QDHA.1564@.TK2MSFTNGP12.phx.gbl...
> We are currently taking a lot of our data from Foxpro to SQL Server. We
are
> only taking Data and Structure. We simply used the Foxpro drivers and
> created a link to the Foxpro DB. Using DTS we sucked the Data in. The
> usual things are worth looking out for like Dates but besides that we have
> no real problems with it.
> --
>
> Allan Mitchell (Microsoft SQL Server MVP)
> MCSE,MCDBA
> www.SQLDTS.com
> I support PASS - the definitive, global community
> for SQL Server professionals - http://www.sqlpass.org
> "florence" <florencelee@.visualsolutions.com.my> wrote in message
> news:696501c3437c$d5f33f40$a401280a@.phx.gbl...
> > Hi,
> >
> > I have my data all store in visual foxpro
> > database, if i want to change my database to SQL server,
> > how do i copy all the data in visual foxpro and save in
> > SQL Server Database? Thank you.
>
I have my data all store in visual foxpro
database, if i want to change my database to SQL server,
how do i copy all the data in visual foxpro and save in
SQL Server Database? Thank you.We are currently taking a lot of our data from Foxpro to SQL Server. We are
only taking Data and Structure. We simply used the Foxpro drivers and
created a link to the Foxpro DB. Using DTS we sucked the Data in. The
usual things are worth looking out for like Dates but besides that we have
no real problems with it.
--
Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org
"florence" <florencelee@.visualsolutions.com.my> wrote in message
news:696501c3437c$d5f33f40$a401280a@.phx.gbl...
> Hi,
> I have my data all store in visual foxpro
> database, if i want to change my database to SQL server,
> how do i copy all the data in visual foxpro and save in
> SQL Server Database? Thank you.|||The converse of this method is to create an SQL Server table to receive the
data and then create a remote view to that table on the FoxPro side. After
that you only need to APPEND FoxPro data to the view and issue a
TABLEUPDATE().
--
Cindy Winegarden MCSD, Microsoft Visual FoxPro MVP
cindy.winegarden@.mvps.org, www.cindywinegarden.com
"Allan Mitchell" <allan@.no-spam.sqldts.com> wrote in message
news:e2klnp4QDHA.1564@.TK2MSFTNGP12.phx.gbl...
> We are currently taking a lot of our data from Foxpro to SQL Server. We
are
> only taking Data and Structure. We simply used the Foxpro drivers and
> created a link to the Foxpro DB. Using DTS we sucked the Data in. The
> usual things are worth looking out for like Dates but besides that we have
> no real problems with it.
> --
>
> Allan Mitchell (Microsoft SQL Server MVP)
> MCSE,MCDBA
> www.SQLDTS.com
> I support PASS - the definitive, global community
> for SQL Server professionals - http://www.sqlpass.org
> "florence" <florencelee@.visualsolutions.com.my> wrote in message
> news:696501c3437c$d5f33f40$a401280a@.phx.gbl...
> > Hi,
> >
> > I have my data all store in visual foxpro
> > database, if i want to change my database to SQL server,
> > how do i copy all the data in visual foxpro and save in
> > SQL Server Database? Thank you.
>
Friday, February 24, 2012
How to copy store procedure from SQL 2000 to 2005?
I'm using MS SQL Server Management Studio. How do copy my store procedures that are on the SQL Server 2000 to SQL Server 2005? What are my options? I don't have admin rights...only rights to my database.you can script the procedures in sql 2000 and run the script in sql 2005 for the procs to be created..............but i believe you need to have sysadmin privilege...........|||
You can read the definition from
SELECT Routine_definition
FROM INFORMATION_SCHEMA.Routines
Where Routine_Name = 'YourProcedureName'
Jens K. Suessmeyer
http://www.sqlserver2005.de
|||Expanding on Jens' comments:
Since the INFORMATION_SCHEMA views are accessible by 'Public', you may read the procedure definitions, and then you can copy and paste into a new query window for the SQL 2005 server, and after you execute the code, the procedure will be saved in the SQL 2005 server.
|||Now, this is one procedure at a time, correct?
Thanks!
|||That's correct.
If you had admin rights, then there are much easier methods.
Subscribe to:
Comments (Atom)