Database

Re: Filter rows depending on case result

2-Apr-08 17:44

Hi, thanks for looking

I hace a case in my select statement (SoldOut), how could i fiilter rows depending on that field

I made it work by putting the case in my where

But how can i put the case in my select and apply use where to filter rows?

Thanks again

Alexei Rodriguez

gspiteri2-Apr-08 17:48

gspiteri

2-Apr-08 17:48

Using CASE Expressions

storing and retriving urdu data in sql server using c# window forms

Shuaib wasif khan2-Apr-08 10:25

Shuaib wasif khan

2-Apr-08 10:25

hai
can any one plz tell me how to use urdu language in c# forms and through
form insert data into sql server when i i insert data in sqlserver it
show ???????? instead of actual data .this problem also caries with
crystal report 9 Sigh | :sigh:

Re: storing and retriving urdu data in sql server using c# window forms

pmarfleet2-Apr-08 12:06

pmarfleet

2-Apr-08 12:06

Use nvarchar[^] columns to store your data. This allows you to take advantage of the extended character set provided by Unicode encoding.

Paul Marfleet

"No, his mind is not for rent
To any God or government"
Tom Sawyer - Rush

SQL string functions

Yevgeny Efter2-Apr-08 7:53

2-Apr-08 7:53

Hello!

I am writing a stored procedure, and in my stored procedure, I need to work with a string.

I found some functions in SQL: DATALENGTH, and SUBSTRING. But I can't find information

about how to work with strings in SQL. What I need is to walk through a string, exemine

every character and make the string smaller depending on the characters.

First, how can I compare ntext strings?

And, second, maybe there are more string functions in SQL?

Thanks in advance!

Have a nice day!

Mark J. Miller2-Apr-08 9:15

2-Apr-08 9:15

If you have SQL BOL installed here's the documetation for all string functions: ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/6940a83d-5374-4af3-bb27-5d89c8af83ac.htm

Otherwise, here's the online help docs: http://msdn2.microsoft.com/en-us/library/ms181984.aspx[^]

A Wong2-Apr-08 9:15

A Wong

2-Apr-08 9:15

You probably want to take a look at nvarchar and varchar types. You can also take a look at functions that might help you on msdn.

http://msdn2.microsoft.com/en-us/library/ms186939.aspx[^]">

Yevgeny Efter2-Apr-08 9:21

2-Apr-08 9:21

My problem now is getting the last character of a ntext variable.

I do it this way, but it doesn't seem to work:

SUBSTRING(@strvar, DATALENGTH(@strvar), 0)

Have a nice day!

Mark J. Miller2-Apr-08 9:35

2-Apr-08 9:35

<br />
DECLARE @strvar VARCHAR(50)<br />
SET @strvar = 'hello world'<br />
<br />
SELECT SUBSTRING(@strvar, LEN(@strvar), 1)<br />

Yevgeny Efter2-Apr-08 9:57

2-Apr-08 9:57

Alright, now I try to compare it to a '\' using LIKE, and it never works.
How can I check if this character is '\' or not?

Have a nice day!

Mark J. Miller2-Apr-08 10:16

2-Apr-08 10:16

DECLARE @strvar VARCHAR(50)
SET @strvar = 'hello world'

SELECT CASE SUBSTRING(@strvar, LEN(@strvar), 1) WHEN '/' THEN 1 ELSE 0 END

Yevgeny Efter2-Apr-08 18:52

2-Apr-08 18:52

Why do I need the SELECT?
Won't
WHILE(SUBSTRING(@strvar, LEN(@strvar), 1) NOT LIKE '\')
work?

Have a nice day!

Mark J. Miller3-Apr-08 3:28

3-Apr-08 3:28

You don't need SELECT, I'm just using it like a Debug.Print to see the value returned. Why are you using LIKE? You aren't making any kind of pattern comparison. You are comparing the result with a constant expression. You should be doing it this way:

SUBSTRING(@strvar, LEN(@strvar), 1) <> '\'

Yevgeny Efter3-Apr-08 8:01

Weird problem with some DB objects (SPs and Views)

3-Apr-08 8:01

Thank you!

Have a nice day!

AlexeiXX32-Apr-08 7:10

Re: Weird problem with some DB objects (SPs and Views)

2-Apr-08 7:10

Thanks for looking

I have a weird situation here

I have some views and SPs
They have a name (Example: spCars), but when i generate the script for that object the create view script has another name (spCarsByBrand)
I know this happens beacause i changed the name after i created them

How can i preevent this??
Whats the correct way to rename SPs and views?
Do i have to delete and recreate with new name?

Thanks again

Alexei Rodriguez

Mark J. Miller2-Apr-08 9:25

2-Apr-08 9:25

AlexeiXX3 wrote:
Do i have to delete and recreate with new name?

That's the best way to do it in my view, but you should be able to use sp_rename if you don't like the idea of DROP/CREATE. Here's an example of both:

sp_rename

<br />
CREATE PROCEDURE myTestProc<br />
@Id int<br />
as<br />
<br />
SELECT @@SERVERNAME<br />
<br />
GO<br />
<br />
EXEC sp_rename 'myTestProc', 'myRenamedTestProc', 'OBJECT'<br />
<br />
IF OBJECT_ID(N'myTestProc') IS NOT NULL<br />
BEGIN<br />
	SELECT 'Rename failed'<br />
	DROP PROCEDURE myTestProc<br />
END<br />
IF OBJECT_ID(N'myRenamedTestProc') IS NOT NULL<br />
BEGIN<br />
	SELECT 'Rename worked'<br />
	DROP PROCEDURE myRenamedTestProc<br />
END<br />

DROP/CREATE

<br />
CREATE PROCEDURE myTestProc<br />
@Id int<br />
as<br />
<br />
SELECT @@SERVERNAME<br />
<br />
GO<br />
<br />
IF EXISTS(SELECT * FROM sys.objects WHERE [object_id] = OBJECT_ID(N'myTestProc')) BEGIN<br />
	DROP PROCEDURE myTestProc<br />
END <br />
GO<br />
<br />
CREATE PROCEDURE myRenamedTestProc<br />
@Id int<br />
as<br />
<br />
SELECT @@SERVERNAME<br />
<br />
GO<br />
<br />
IF EXISTS(SELECT * FROM sys.objects WHERE [object_id] = OBJECT_ID(N'myTestProc')) BEGIN<br />
	DROP PROCEDURE myRenamedTestProc<br />
END <br />
GO<br />
<br />

Re: Weird problem with some DB objects (SPs and Views)

AlexeiXX32-Apr-08 9:49

Re: Weird problem with some DB objects (SPs and Views)

2-Apr-08 9:49

Thanks for your answer

I cant believe all that one has to do to make sql work correctly Frown | :-(

I hadnt noticed that behavior until now that im exporting my DB using the generate sql script

By the way, sql script generator also has a lot of problems, doesnt it??
When dropping tables and other objects it doesnt care about dependencies and neither does it when creating them

Im having a hard time with generating the scripts for my DB
sql 2000 enterprise manager (Doesnt care about the deletion or creation order)
sql 2005 express ed (Doesnt care about the deletion or creation order)
Database Publishing Wizard (This one fails when trying to generate the script for a function that return a table, and i dont think that it generates scripts in the right order)

Do you have any sugestions for this?

Thanks again

Alexei Rodriguez

Mark J. Miller2-Apr-08 10:14

2-Apr-08 10:14

Renaming objects will always cause problems. If you need to find dependencies here are a couple scripts to allow you to search the source of views and stored procs. Just enter the name of the object you're looking for and you'll get a list of objects which contain your search phrase.

Search stored procedure:

DECLARE @find VARCHAR(1000)SET @find = 'enter your search phrase here'<br />
<br />
SELECT sp.name,<br />
ISNULL(smsp.definition, ssmsp.definition) AS [Definition]<br />
FROM sys.all_objects AS sp<br />
LEFT OUTER JOIN sys.sql_modules AS smsp ON smsp.object_id = sp.object_id<br />
LEFT OUTER JOIN sys.system_sql_modules AS ssmsp ON ssmsp.object_id = sp.object_id<br />
WHERE(sp.type = N'P' OR sp.type = N'RF' OR sp.type='PC')<br />
AND ISNULL(smsp.definition, ssmsp.definition) LIKE '%' + @find + '%'

Search view:

<br />
DECLARE @search VARCHAR(1000)SET @search = 'enter your search phrase here'<br />
<br />
SELECT c.[Text]<br />
FROM dbo.sysobjects AS vINNER JOIN dbo.syscomments c ON c.id = v.id<br />
	AND CASE WHEN c.Number > 1 THEN c.Number			ELSE 0 END = 0<br />
WHERE v.type = 'V' AND c.[Text] LIKE '%' + @search + '%'<br />

As far as dropping and recreating objects as long as your script creates tables before your other objects. What I always do when writing my scripts is follow the IF EXISTS DROP then CREATE pattern. That way when I have a large script which creates a large number of objects if I get errors due to dependencies the first time I run it then I just run it again. The first time creates all the objects, the second time will create any that failed on the first attempt.

If you don't like this, then when you generate your scripts run them on a test database. Create 2 script files - one with DROP statements and one with CREATE statements. Run your CREATE script and look for dependency errors. If you get any, run your DROP script then rearrange your CREATE statements to fix the dependency errors and run the CREATE script again. Rinse, wash, repeat.

Re: Weird problem with some DB objects (SPs and Views)

AlexeiXX32-Apr-08 10:02

how to encrypt and decrypt stored procedure in sql server 2000

2-Apr-08 10:02

I just saw your answer to the other post about the tool to generate the scripts
Ill give it atry

Thanks

Alexei Rodriguez

M.Ramesh2-Apr-08 2:11

M.Ramesh

2-Apr-08 2:11

I want to encrypt my stored procedure. How can i do this and revert back also.

anybody help..

Re: how to encrypt and decrypt stored procedure in sql server 2000

Mark J. Miller2-Apr-08 9:27

2-Apr-08 9:27

Here's how to encrypt the procedure:

<br />
CREATE PROCEDURE myTestProc<br />
@Id INT<br />
WITH ENCRYPTION<br />
AS<br />
<br />
   SELECT @@SERVERNAME<br />
<br />
GO<br />

But you have to save the source code in a script file somewhere. There is no way to decrypt the source. Not even for sysadmin or dbowner.