Case sensitivity

Hello,
I'm writing a function witch from an string (nvarchar) gets the characters
and converts them. I have the problem with case sensitivity. I get the
results in small letters but I need them to be in the exact case as the inpu
t
values, and must not change the SQL Server's settings. I've tryed with
char(number), nchar(number), but results are small letters. Can somebody
please help me with my problem?
ThanksHi,
see the following code.. use this logic in your function
DECLARE @.c CHAR
DECLARE @.d CHAR
SET @.c = 'a'
SET @.d = 'B'
DECLARE @.a VARCHAR(126)
SET @.a = ''
SET @.a = @.a + @.c + @.d
PRINT @.a
I hope that this will help you
Regards
Sivakumar
"RioDD" wrote:

> Hello,
> I'm writing a function witch from an string (nvarchar) gets the characters
> and converts them. I have the problem with case sensitivity. I get the
> results in small letters but I need them to be in the exact case as the in
put
> values, and must not change the SQL Server's settings. I've tryed with
> char(number), nchar(number), but results are small letters. Can somebody
> please help me with my problem?
> Thanks|||Check out COLLATE in the BOL.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
.
"RioDD" <RioDD@.discussions.microsoft.com> wrote in message
news:D1E3AE60-365A-4B64-8408-E7C118D9F84C@.microsoft.com...
Hello,
I'm writing a function witch from an string (nvarchar) gets the characters
and converts them. I have the problem with case sensitivity. I get the
results in small letters but I need them to be in the exact case as the
input
values, and must not change the SQL Server's settings. I've tryed with
char(number), nchar(number), but results are small letters. Can somebody
please help me with my problem?
Thanks|||Sorry it didn't help me. The problem is when I use
if @.c='a'
it returns true for both 'a' and 'A'
"Subramaniam Sivakumar" wrote:
> Hi,
> see the following code.. use this logic in your function
> DECLARE @.c CHAR
> DECLARE @.d CHAR
> SET @.c = 'a'
> SET @.d = 'B'
> DECLARE @.a VARCHAR(126)
> SET @.a = ''
> SET @.a = @.a + @.c + @.d
> PRINT @.a
> I hope that this will help you
> Regards
> Sivakumar
> "RioDD" wrote:
>|||try this
IF CONVERT(varbinary(64), @.c) = CONVERT(varbinary(64), 'A')
"RioDD" wrote:

> Hello,
> I'm writing a function witch from an string (nvarchar) gets the characters
> and converts them. I have the problem with case sensitivity. I get the
> results in small letters but I need them to be in the exact case as the in
put
> values, and must not change the SQL Server's settings. I've tryed with
> char(number), nchar(number), but results are small letters. Can somebody
> please help me with my problem?
> Thanks|||Thanks, this helped me
"Subramaniam Sivakumar" wrote:
> try this
> IF CONVERT(varbinary(64), @.c) = CONVERT(varbinary(64), 'A')
>
> "RioDD" wrote:
>|||This will only work if the string is less than 64 bits. You really should
look up the various collations in books online, that is the correct way to d
o
this.|||Hi,
no... you can use varbinary upto 8000.
"Scott Simons" wrote:

> This will only work if the string is less than 64 bits. You really should
> look up the various collations in books online, that is the correct way to
do
> this.

Case sensitive query? Problems with Integration Services Query

Hi,
i am writing a IntegrationServices2005 job which copies the result of a
view to a table.
I found a very strange problem:
select * from [dbo].[warehouse_aufwandurlaub] -> runs in < 1 second.
select * from [dbo].[warehouse_AufwandUrlaub] -> runs in > 30 Second
s
(timeout).
the name of the view is "warehouse_AufwandUrlaub".
If i run the query in query analyzer, both querys exectute very fast.
What is causing this failure? how can i avoid this?
Thx
Best regards,
Patrick DingerSorry i forgot to mention, that i am using SQL Server 2005 with SP1.
Thx in advance,
Patrick Dinger|||Any activites on the server at this time?
Don't you really need WHERE condition?
"Patrick Dinger" <paxos2k@.gmail.com> wrote in message
news:1147690976.451955.271160@.j55g2000cwa.googlegroups.com...
> Sorry i forgot to mention, that i am using SQL Server 2005 with SP1.
> Thx in advance,
> Patrick Dinger
>|||Hi,
its not an ressource problem, there isn't really activity on the server
currently.
the error is reproducible. No, i do not need a where condition...
Best regards,
Patrick Dinger

Case sensitive query? Problems with Integration Services Query

Hi,
i am writing a IntegrationServices2005 job which copies the result of a
view to a table.
I found a very strange problem:
select * from [dbo].[warehouse_aufwandurlaub] -> runs in < 1 second.
select * from [dbo].[warehouse_AufwandUrlaub] -> runs in > 30 Seconds
(timeout).
the name of the view is "warehouse_AufwandUrlaub".
If i run the query in query analyzer, both querys exectute very fast.
What is causing this failure? how can i avoid this?
Thx
Best regards,
Patrick DingerSorry i forgot to mention, that i am using SQL Server 2005 with SP1.
Thx in advance,
Patrick Dinger|||Any activites on the server at this time?
Don't you really need WHERE condition?
"Patrick Dinger" <paxos2k@.gmail.com> wrote in message
news:1147690976.451955.271160@.j55g2000cwa.googlegroups.com...
> Sorry i forgot to mention, that i am using SQL Server 2005 with SP1.
> Thx in advance,
> Patrick Dinger
>|||Hi,
its not an ressource problem, there isn't really activity on the server
currently.
the error is reproducible. No, i do not need a where condition...
Best regards,
Patrick Dinger

Capturing the results from exec command

Hi,

I'm writing a small query where I have a dynamic table name and dynamic condition for the criteria. In order to execute this, I need Exec command.

exec(select count(*) from @.dynamictable where condition = @.dynamiccond)

But here I want to capture the count from the select statement. Could any of you help me capture the results from exec command?

Thanks

2 ways

USE pubs
GO

--sp_executesql
DECLARE @.chvTableName VARCHAR(100),
@.intTableCount INT,
@.chvSQL NVARCHAR(100)

SELECT @.chvTableName = 'Authors'
SELECT @.chvSQL = N'SELECT @.intTableCount = COUNT(*) FROM ' + @.chvTableName

EXEC sp_executesql @.chvSQL, N'@.intTableCount INT OUTPUT', @.intTableCount OUTPUT

SELECT @.intTableCount
GO

--EXEC (SQL)
DECLARE @.chvTableName VARCHAR(100),
@.intTableCount INT,
@.chvSQL NVARCHAR(100)

CREATE TABLE #temp (Totalcount INT)
SELECT @.chvTableName = 'Authors'
SELECT @.chvSQL = 'Insert into #temp Select Count(*) from ' + @.chvTableName

EXEC( @.chvSQL)

SELECT @.intTableCount = Totalcount from #temp

SELECT @.intTableCount

DROP TABLE #temp

Denis the SQL Menace

http://sqlservercode.blogspot.com/

|||

Or:

DECLARE @.chvTableName VARCHAR(100)

CREATE TABLE #temp (Totalcount INT)
SELECT @.chvTableName = 'sysobjects'

insert into #temp(totalCount)
EXEC( 'Select Count(*) from ' + @.chvTableName)

SELECT * from #temp

DROP TABLE #temp

Note: it is generally considered a bad practice to do this sort of thing unless you are building some sort of tool. If this is a production application, it would be better to build a procedure per table:

create procedures count_accounts
as
select count(*) from account
go

Yes, it sounds like a lot of maintenance, but unless you build a large quantity of tables, it shouldn't be a big deal.

Capturing Server Name?

Hi y'all...long time, I know...

I have what OUGHT to be a simple question...or so I think (which really hurts, mind you...)

I am writing a stored proc that will reside on several different databases and be used to write a row to a "wait table" that is used to control processing in the various databases...

Essentially, the stored proc already exists, and writes what is essentially a note (or process semaphore) that says "Hey, Process XYZ is waiting on the completion of process 123"

Problem is...process XYZ has the same name on a number of different servers, so I have to come up with a way to differentiate the process name that's waiting on a job on a single server (in other words, 5 or more XYZ's can be waiting on a single job on a single server in the network, and the wait table resides on that single server).

So...my thought (again, think pain) is that I will put a process name of "SERVER.XYZ" into the wait table.

The SP I will use to write the "waiting on" semaphore is a common one, so - long story short(er) I need a way to capture the name of the current server (like db_name(), only server_name() - or something like it).

Any suggestions? Thanks in advance...
Paulselect SERVERPROPERTY ('ServerName')

that'll do it...

Thanks for reading though!|||SELECT @.@.SERVERNAME may also work for you...|||Do you mean the name of the server that the database is on? If so, then you have your solution. If you wanted the client server's name, you could use:

select hostname
from master..sysprocesses
where spid = @.@.spid

If you go this route, you will have to make sure that all of your connection strings supply a hostname, though. Not all applications do. Also, the hostname can be spoofed, so be careful about hardcoding that. Good luck.|||...or simply HOST_NAME()|||I'll be darned. Microsoft actually came up with something that might be useful. I may have to try that with an application that spoofs a hostname, and see what happens. Thanks, rdjabarov.

Capturing record count for a table in Oracle and saving it in a table in SQL Server

I would like to find out how to capture record count for a table in oracle using SSIS and then writing that value in a SQL Server table.

I understand that I can use a variable to accomplish this task. Well first issue I run into is that what import statement do I need to use in the design script section of Script Task. I see that in many examples following statement is used for SQL Server databases:

Imports System.Data.SqlClient

Which Import statement I need to use to for Oracle database. I am using a OLE DB Connection.

any idea?

thanks

Why are you trying to use a Script Task? This can be achieved very easily using an Execute SQL Task.

-Jamie

|||

I'd concur with Jamie, use an Execute SQL task.

If you have to use the script task, then you need to reference the System.Data.OleDb namespace for OLEDB connections, or the System.Data.OracleClient for the Oracle client.

|||well how do I use sql task to accomplish this?|||select count(*) from table

Store the contents of that SQL statement into a single result set. Variable mapped to 0 in the Result tab.|||the result tab is frozen. how do I enable it? do I need to define an expression?|||

Set ResultSet='Single Row'

-Jamie

|||is that an expression?|||

No. ResultSet is a property of the Execute SQL Task.

-Jamie

|||gotcha.. i did set it single row. Now how do I get the value from that resultset and use it to update a column.|||On the Result Set tab, add a result, make the name 0 and specify the variable to put the count in. Then use another Execute SQL task, with an Insert statement, to write the variable to your table.|||

just wondering why would you name result set in result name tab to zero? is that initial value of the variable?

also what would the query look like in the second sql task to get value from the variable?

is there any example I can follow?

|||No, 0 refers to the first column of the resultset (it's a zero-based index).|||

Shahab03 wrote:

just wondering why would you name result set in result name tab to zero? is that initial value of the variable?

also what would the query look like in the second sql task to get value from the variable?

is there any example I can follow?

INSERT INTO MyTable (RecordCountCol)

VALUES (?)

On the Parameter Mapping tab, add a parameter, set the variable to the same one you populated earlier, make the name 0, and set the data type appropriately.

See this for a really good overview of using the Execute SQL task. http://www.sqlis.com/58.aspx

|||

well I have a sqltask now with following properties:

query: select count(*) as EmpCompRC from empcomp

I have also defined a variable EmpCompRC. Result name in result set tab is set to 0 and variable is EmpCompRC.

Now as I understand I have setup another sql task after the defined above. Correct?

and what would that task look like? I dont see how would I pass the variable value from previous task to this one. I tried following statement but it failed:

update detail

set sourcerecordcount = @.EmpCompRC

Go

does anyone know how to setup this sqltask? query above still leaves the variable name out.

Can't we use variables in OPENQUERY, FREETEXT("@searchstring")?

I'm writing a stored procedure for a keyword search in a Word or PDF
doc which i've done through Index Server and linked the results to SQL
Server.
Part of my stored proc is shown below in which for a FREETEXT keyword
search i'm using a variable "@.searchstring", which i have to, is not
working.
I know it works with hard text but
Is there any way i can use a Variable in OPENQUERIES or is this my DEAD
END?
Can anyone please guide me how to use a variable in FREETEXT
Thanks in Advance
DECLARE @.searchstring varchar(22)
SET @.searchstring = 'aspnet'
SELECT * FROM OPENQUERY(FileSystem,'SELECT Directory, FileName,
DocAuthor, Size, Create, Write, Path FROM SCOPE(''
"c:\inetpub\wwwroot\sap-resources\Uploads" '') WHERE
FREETEXT(''@.searchstring'')')Hi,
You need to resort to dynamic SQL, i.e....
SET @.sql = 'SELECT TOP 50 *
FROM (
SELECT DISTINCT
kba.idKBArticle,
[Rank],
Characterization
FROM ( SELECT DISTINCT TOP 50 [FileName],
[Rank],
Characterization
FROM OPENQUERY( lsIndexServer,
''SELECT FileName, Rank, Characterization
FROM TORVERSRVH3.SQLServerUG2..SCOPE() WHERE ' + CASE
WHEN @.OpType='C' THEN 'CONTAINS' ELSE 'FREETEXT' END +
'( '' +
@.SearchKeywords + '' )'' )
WHERE LEFT( Characterization, 12 ) <>
''vti_encoding''
) AS qry
INNER JOIN KBArticle kba ON kba.ArticleFileName =
qry.[FileName]'
REMEMBER!!!!!! ====>>>>>>>
To prevent injection make absolutely sure you replace any single quotes with
2 single quotes...
-- this one fails and is subject to injection...
declare @.searchtext varchar(100)
set @.searchtext = 'tony''s injection'
exec( 'print ''' + @.searchtext + '''' )
go
-- this one works because prevent injection...
declare @.searchtext varchar(100)
set @.searchtext = 'tony''s injection'
set @.searchtext = REPLACE( @.searchtext, '''', ''' )
exec( 'print ''' + @.searchtext + '''' )
go
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"savvy" <johngera@.gmail.com> wrote in message
news:1137755654.791956.239920@.z14g2000cwz.googlegroups.com...
> I'm writing a stored procedure for a keyword search in a Word or PDF
> doc which i've done through Index Server and linked the results to SQL
> Server.
> Part of my stored proc is shown below in which for a FREETEXT keyword
> search i'm using a variable "@.searchstring", which i have to, is not
> working.
> I know it works with hard text but
> Is there any way i can use a Variable in OPENQUERIES or is this my DEAD
> END?
> Can anyone please guide me how to use a variable in FREETEXT
> Thanks in Advance
> DECLARE @.searchstring varchar(22)
> SET @.searchstring = 'aspnet'
> SELECT * FROM OPENQUERY(FileSystem,'SELECT Directory, FileName,
> DocAuthor, Size, Create, Write, Path FROM SCOPE(''
> "c:\inetpub\wwwroot\sap-resources\Uploads" '') WHERE
> FREETEXT(''@.searchstring'')')
>|||Thanks for your help
i tried using above idea and some other examples.
The code shown below is working perfectly in the analyzer. I want to
create a view with the results
Is it possible ?
Thanks in Advance
DECLARE @.searchstring varchar(22)
SET @.searchstring = 'aspnet'
declare @.strSQL varchar(244)
select @.strSQL='select FileName,Path from scope(''''
"c:\inetpub\wwwroot\sap-resources\Uploads" '''') where contains ('
select @.strSQL=@.strSQL +char(39)+ char(39)+ @.searchstring +char(39)+
char(39)+')'
select @.strSQL='select * from openquery(FileSystem,'+ char(39)+
@.strSQL+ char(39)+ ')'
exec (@.strSQL)
Something like
CREATE VIEW FileSearchResults AS (@.strSQL)
which is not working|||Hi Savvy,
Sorry - you won't be able to create a view for that unless your search
string is hard-coded and never changes.
You could write a stored procedure that accepts the search string as a
parameter.
Tony.
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"savvy" <johngera@.gmail.com> wrote in message
news:1137764139.455350.325970@.o13g2000cwo.googlegroups.com...
> Thanks for your help
> i tried using above idea and some other examples.
> The code shown below is working perfectly in the analyzer. I want to
> create a view with the results
> Is it possible ?
> Thanks in Advance
>
> DECLARE @.searchstring varchar(22)
> SET @.searchstring = 'aspnet'
> declare @.strSQL varchar(244)
> select @.strSQL='select FileName,Path from scope(''''
> "c:\inetpub\wwwroot\sap-resources\Uploads" '''') where contains ('
> select @.strSQL=@.strSQL +char(39)+ char(39)+ @.searchstring +char(39)+
> char(39)+')'
> select @.strSQL='select * from openquery(FileSystem,'+ char(39)+
> @.strSQL+ char(39)+ ')'
> exec (@.strSQL)
>
> Something like
> CREATE VIEW FileSearchResults AS (@.strSQL)
> which is not working
>|||Thank you very much for your help and time Tony Rogerson
This is my complete stored procedure which is perfectly working when i
hardcore the @.searchstring with the word which doesn't change.
I just want to use a variable working over there. Can u please help me
in this
Thanks in Advance
CREATE PROCEDURE SelectIndexServerCVpaths
(
@.searchstring varchar(100)
)
AS
IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS
WHERE TABLE_NAME = 'FileSearchResults')
DROP VIEW FileSearchResults
EXEC ('CREATE VIEW FileSearchResults AS SELECT * FROM
OPENQUERY(FileSystem,''SELECT Directory, FileName,
DocAuthor, Size, Create, Write, Path FROM
SCOPE('''' "c:\inetpub\wwwroot\sap-resources\Uploads" '''') WHERE
FREETEXT(''''@.searchstring'''')'')')
SELECT * FROM CVdetails C, FileSearchResults F WHERE C.CV_Path =
F.PATH AND C.DefaultID=1
GO|||CREATE PROCEDURE SelectIndexServerCVpaths
(
@.searchstring varchar(100)
)
AS
SET @.searchstring = REPLACE( @.searchstring, '''', ''' )
IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS
WHERE TABLE_NAME = 'FileSearchResults')
DROP VIEW FileSearchResults
EXEC ('CREATE VIEW FileSearchResults AS SELECT * FROM
OPENQUERY(FileSystem,''SELECT Directory, FileName,
DocAuthor, Size, Create, Write, Path FROM
SCOPE('''' "c:\inetpub\wwwroot\sap-resources\Uploads" '''') WHERE
FREETEXT('' + @.searchstring + '')'')')
SELECT * FROM CVdetails C, FileSearchResults F WHERE C.CV_Path =
F.PATH AND C.DefaultID=1
GO
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"savvy" <johngera@.gmail.com> wrote in message
news:1137766282.280852.185740@.g14g2000cwa.googlegroups.com...
> Thank you very much for your help and time Tony Rogerson
> This is my complete stored procedure which is perfectly working when i
> hardcore the @.searchstring with the word which doesn't change.
> I just want to use a variable working over there. Can u please help me
> in this
> Thanks in Advance
> CREATE PROCEDURE SelectIndexServerCVpaths
> (
> @.searchstring varchar(100)
> )
> AS
> IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS
> WHERE TABLE_NAME = 'FileSearchResults')
> DROP VIEW FileSearchResults
> EXEC ('CREATE VIEW FileSearchResults AS SELECT * FROM
> OPENQUERY(FileSystem,''SELECT Directory, FileName,
> DocAuthor, Size, Create, Write, Path FROM
> SCOPE('''' "c:\inetpub\wwwroot\sap-resources\Uploads" '''') WHERE
> FREETEXT(''''@.searchstring'''')'')')
> SELECT * FROM CVdetails C, FileSearchResults F WHERE C.CV_Path =
> F.PATH AND C.DefaultID=1
> GO
>|||Thanks for your Great help Tony
I have a strange problem its above code is working in the Query
Analyzer but not working if execute the stored procedure as shown below
i tried but i'm not able to figure out where the problem is
Thanks in Advance
Exec SelectIndexServerCVpaths
@.searchstring = 'aspnet'|||whats the error?
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"savvy" <johngera@.gmail.com> wrote in message
news:1137770531.573885.223780@.f14g2000cwb.googlegroups.com...
> Thanks for your Great help Tony
> I have a strange problem its above code is working in the Query
> Analyzer but not working if execute the stored procedure as shown below
> i tried but i'm not able to figure out where the problem is
> Thanks in Advance
> Exec SelectIndexServerCVpaths
> @.searchstring = 'aspnet'
>|||I'm sorry Tony
i didn't copy the code properly in my stored procedure this part
exactly FREETEXT('' + @.searchstring + '')'')')
when i copied again
its working perfectly Tony
You dont know how much your help is worth to me
I cant just express in words
I needed to complete project today which i did with your help
Thank you very very very much Tony Rogerson|||I'm really grateful to you Tony
Thanks onceagain