how to make the contets of tables, case sensitive?

hi

the contents of sql server tables, aren't case sensitive.
and datas in them , are the same , whether they contains normal or caps letter.
for example Test = test=teST , and sql can't recogize the difference between them.

how can i make it sensitive?

thank u for your attention

Case-sensitivity is controlled by the collation that's being used by the server in the context of the query you're executing. I believe you can set the collation at the server, database or column level in SQL Server 2005. For more information, see the "Working with Collations" topic in Books Online.

Also, please post relational database engine questions to the SQL Database Engine forum (http://forums.microsoft.com/msdn/ShowForum.aspx?ForumID=93) where you're likely to get more prompt and accurate replies.

Raman Iyer
SQL Server Data Mining

sql

how to make the contents of tables, case sensitive?

hi

the contents of sql server tables, aren't case sensitive.
and datas in them , are the same , whether they contains normal or caps letter.
for example Test = test=teST , and sql can't recogize the difference between them.

how can i make it sensitive?

thank u for your attention

Case-sensitivity is controlled by the collation that's being used by the server in the context of the query you're executing. I believe you can set the collation at the server, database or column level in SQL Server 2005. For more information, see the "Working with Collations" topic in Books Online.

Also, please post relational database engine questions to the SQL Database Engine forum (http://forums.microsoft.com/msdn/ShowForum.aspx?ForumID=93) where you're likely to get more prompt and accurate replies.

Raman Iyer
SQL Server Data Mining

how to make the contents of sqlserver tables, case sensitive?

hi

the contents of sql server tables, aren't case sensitive.
and datas in them , are the same , whether they contains normal or caps letter.
for example Test = test=teST , and sql can't recogize the difference between them.

how can i make it sensitive?

thank u for your attention

Hi,
you may create a database with a case sesitive Collation and this will make the whole database case sestive including table names i.e.
select * from SYSOBJECTS will give you an error

how to make column lowercase

I have a table that contains names that are all in upper case, this column is called in many different areas of my web app. I wanted to make the names all lowercase, or with the leading character only capitalized.
How can I make a column within a SQL table lowercase at the SQL server end and not the programming side?

thanks,
Frank

UPDATE
 TableName
SET
 FieldName = LCASE(FieldName)

For all future entries, you create a trigger that will execute on insert.|||If you are talking about the data to be converted in lowercase, then following is the syntax:

select lower(column_name) from table_name

To convert first letter in Capital, you could write your own function, using UPPER, LOWER, SUBSTRING and CHARINDEX.
You can use CHARINDEX to find the spaces, then UPPER the character following each space.|||How can I do this automatically in the Formula field of the Table Design.

thanks

How to make an if in the dataflow ?

I have a dataflow where i import 2 files. The one is the file containing a couple of million records. The other file contains rows with summed values on a specific key.

The file with the millions of records is aggregated on the key, sorted, so that the 2 collums from the files can be compared. I then do a mergejoin on the key and now i have temptable with the (key,sum1,sum2). Now there must not be a difference between sum1 and sum2.
I can make a conditional split where i say ([sum1] - [sum2]) > 0.1 so that i get an output with rows where the diffence is more than 0.1.

My question is now, how do i make an action on that. If that task put out a row or more then do something (send mail task, stop further processing) ?

CgplJust add a "RecordCount" transform to your pipeline. So you count "Error Records". In the Control Flow you can change the "Link" between the task and change the "constraint options" to "Expression". There you can check if the value of the variable you used for the RecordCount is greater then 0. If so you can link to a send mail task or whatever you want...

HTH
Thomas|||Thanks but can you point that out in detail ?|||Ahhh Found out! Thanks|||This may help: http://blogs.conchango.com/jamiethomson/archive/2005/07/25/1843.aspx

-Jamie
EDIT: Ahh, except that you already worked it out while I was posting this. Never mind :)

how to locate a table that contains a specific trigger?

Hello,
the following tsql
select DISTINCT OBJECT_NAME([id]) FROM sysdepends
WHERE OBJECT_NAME([depid]) = 'tbl_CompanyChangeHistory'
displays a list of UDFs and a trigger that tbl_CompanyChangeHistory is
dependent upon. I need to locate the table that contains this specific
trigger.
I tried
select DISTINCT OBJECT_NAME([id]) FROM sysdepends
WHERE OBJECT_NAME([depid]) like 't_ChgHistory_Write%'
but this did not yield anything (empty).
How can I locate the table which contains this trigger?
Thanks,
RichReverse id and depid:
select DISTINCT OBJECT_NAME([depid]) FROM sysdepends
WHERE OBJECT_NAME([id]) like 't_ChgHistory_Write%' --trigger name?
"Rich" wrote:

> Hello,
> the following tsql
> select DISTINCT OBJECT_NAME([id]) FROM sysdepends
> WHERE OBJECT_NAME([depid]) = 'tbl_CompanyChangeHistory'
> displays a list of UDFs and a trigger that tbl_CompanyChangeHistory is
> dependent upon. I need to locate the table that contains this specific
> trigger.
> I tried
> select DISTINCT OBJECT_NAME([id]) FROM sysdepends
> WHERE OBJECT_NAME([depid]) like 't_ChgHistory_Write%'
> but this did not yield anything (empty).
> How can I locate the table which contains this trigger?
> Thanks,
> Rich
>|||Thanks for your reply. I had the [id] and [depid] columns switched around
which was incorrect. But your solution retrieved the dependent table and a
dependent UDF (still good stuff).
Here is something else I tried that actually retrieve the table I was
looking for
select OBJECT_NAME([parent_obj]) FROM sysobjects
WHERE [id] = 1467920351
The [id] here is the [id] of the trigger which I retrieved like this:
select * from sysobjects where xtype ='tr'
and name = 't_CompaniesWrite'
"Mark Williams" wrote:
> Reverse id and depid:
> select DISTINCT OBJECT_NAME([depid]) FROM sysdepends
> WHERE OBJECT_NAME([id]) like 't_ChgHistory_Write%' --trigger name?
>
> --
>
> "Rich" wrote:
>|||Rich
I'd not rely on sysdepends table ,instead take a look at Vyas's examle
CREATE PROCEDURE sp_FindObject
@.SearchString varchar (255)
AS
SET nocount ON
DECLARE @.Name varchar(255)
DECLARE @.Text nvarchar(4000)
CREATE TABLE #Objs
( ObjName varchar (255))
DECLARE Obj CURSOR
FOR
SELECT [NAME]
FROM
(
SELECT [NAME],[TEXT] FROM sysobjects so, syscomments sc
WHERE (so.xtype ='P' )
AND so.id = sc.id
UNION ALL
SELECT [NAME],[TEXT] FROM sysobjects so, syscomments sc
WHERE (so.xtype ='V' )
AND so.id = sc.id
UNION ALL
SELECT [NAME],[TEXT] FROM sysobjects so, syscomments sc
WHERE (so.xtype ='TR' )
AND so.id = sc.id
) AS Der WHERE [TEXT] LIKE @.SearchString
OPEN Obj
FETCH Next FROM Obj INTO @.Name
WHILE @.@.FETCH_STATUS=0
BEGIN
INSERT INTO #Objs VALUES (@.Name)
FETCH Next FROM Obj INTO @.Name
END
CLOSE Obj
DEALLOCATE Obj
SELECT objname FROM #Objs GROUP BY objname
DROP TABLE #Objs
GO
EXEC sp_FindObject '%HOST_ID()%'
"Rich" <Rich@.discussions.microsoft.com> wrote in message
news:BF61EDA5-4130-40E8-8FA5-2DF3938B0E9C@.microsoft.com...
> Thanks for your reply. I had the [id] and [depid] columns switched around
> which was incorrect. But your solution retrieved the dependent table and
> a
> dependent UDF (still good stuff).
> Here is something else I tried that actually retrieve the table I was
> looking for
> select OBJECT_NAME([parent_obj]) FROM sysobjects
> WHERE [id] = 1467920351
> The [id] here is the [id] of the trigger which I retrieved like this:
> select * from sysobjects where xtype ='tr'
> and name = 't_CompaniesWrite'
> "Mark Williams" wrote:
>

How to load XML from filename and path

Hi,

I have inherited a database that has a table called "Resources" that contains a field called "FilePath". FilePath is a VarChar(100) and contains the server path and filename of an XML file on the server hard disk.

I need to join the data in the XML with rows in the "Resources" table (and other tables).

Is it possible to get a Stored Procedure to load the XML file into a temporary table?

Is loading it into a table the best way to do it - or can I join directly to the XML file somehow?

What is the best approach? (I know nothing about XML in SQL Server yet)

Thanks in advance,

Chiz.

Try the link below to use OPENXML in SQL Server, I am not sure about Temp tables I would try it to see if it is possible, just remember to drop the temp table explictly. Hope this helps.
http://msdn.microsoft.com/msdnmag/issues/05/06/DataPoints/default.aspx|||Thanks Caddre,
At the moment the .NET code calls a Stored Procedure which JOINs the Resources table with some other tables and returns the result which goes into a DataSet.
In order to build a DataSet that can be displayed in a DataGrid the DataSet has to be looked at (one row at a time) and the FilePath 'field' used to load the XML file from disk into an XmlDocument. Then the right data in the right node has to be found and then finally when that is found it can be added to the DataSet.
Anyway, to cut a long story short, I reckon that if I can somehow get T-SQL to load an XML file from disk into a temporary table that it will be neater and more efficient.
Any other help appreciated,
Chiz.|||

The text below is from the BOL(books online) sp_xml_preparedocument System stored Procedure can be used, run a search for sp_xml_preparedocument in the BOL (books online). Hope this helps

(sp_xml_preparedocument
Reads the Extensible Markup Language (XML) text provided as input, then parses the text using the MSXML parser (Msxml2.dll), and provides the parsed document in a state ready for consumption. This parsed document is a tree representation of the various nodes (elements, attributes, text, comments, and so on) in the XML document.)

How to link details tables to list fields

Hi,
I am trying to create a report with a list for each row, which also contains
tables of related data (1 to many relationship).
There are various drill-down techniques described in the documentation, but
I have been unable to determine how to pass a field from the primary record
as a parameter to the queries for the sub data.
Eg. Have tables Employees and Sales. Sales is related to employees by
employeeID field in the Sales table. I want to show a single employee details
in list, then multiplae sals records in a table embedded in the list.
I need to fgigure out how to pass the current employee.ID field to the table
query as a paremeter.
Any help on this would be appreciated (even if I have to get this working
using sub reports)
Thanks,
...Derek
--
DerekYou have two options for doing this, either use nested data regions or use
subreports:
First here's how to use nested data regions. Write one dataset query to
return employee and sales information. This will likely be a join sql query.
Place a list data region on your screen in Layout view (list or table will do
- these data regions can contain other data regions). >Inside< the list data
region place a table data region. Both list and table must be bound to the
same dataset query you created. Put employee fields in the list data region
outside the table. Put sales fields inside the table. That's it.
A second option is to use subreports. First create a report with sales
information. The report should take employeeid as a parameter. save the
report. Now create the main report with a data region that displays employee
information. This report lists all employees and should have in its dataset
query an employeeid field. There is no parameter used. Inside the data region
with employee information, place a subreport control. Go to the properties of
the subreport. On the General tab of the Properties dialog there is a textbox
where you can specify your subreport. This is the report you first created
and saved. In the same dialog box there is a tab where you specify
parameters. select your employeeid parameter and specify the employeeid field
from the main report dataset. That's it.
Both solutions will give you what you want.
HTH
Charles Kangai, MCT, MCDBA
"DerekJMiller1" wrote:
> Hi,
> I am trying to create a report with a list for each row, which also contains
> tables of related data (1 to many relationship).
> There are various drill-down techniques described in the documentation, but
> I have been unable to determine how to pass a field from the primary record
> as a parameter to the queries for the sub data.
> Eg. Have tables Employees and Sales. Sales is related to employees by
> employeeID field in the Sales table. I want to show a single employee details
> in list, then multiplae sals records in a table embedded in the list.
> I need to fgigure out how to pass the current employee.ID field to the table
> query as a paremeter.
> Any help on this would be appreciated (even if I have to get this working
> using sub reports)
> Thanks,
> ...Derek
> --
> Derek|||Hi,
For me first option is working well. Thanks...
I want to display header on each page.
How to put header titles on each page. If I put table header on top in the
list, it is repeating for all records of the list.
If I put header table outside list, it is not visible on next page.
I hope, my question is clear.
Please help
"Charles Kangai" wrote:
> You have two options for doing this, either use nested data regions or use
> subreports:
> First here's how to use nested data regions. Write one dataset query to
> return employee and sales information. This will likely be a join sql query.
> Place a list data region on your screen in Layout view (list or table will do
> - these data regions can contain other data regions). >Inside< the list data
> region place a table data region. Both list and table must be bound to the
> same dataset query you created. Put employee fields in the list data region
> outside the table. Put sales fields inside the table. That's it.
> A second option is to use subreports. First create a report with sales
> information. The report should take employeeid as a parameter. save the
> report. Now create the main report with a data region that displays employee
> information. This report lists all employees and should have in its dataset
> query an employeeid field. There is no parameter used. Inside the data region
> with employee information, place a subreport control. Go to the properties of
> the subreport. On the General tab of the Properties dialog there is a textbox
> where you can specify your subreport. This is the report you first created
> and saved. In the same dialog box there is a tab where you specify
> parameters. select your employeeid parameter and specify the employeeid field
> from the main report dataset. That's it.
> Both solutions will give you what you want.
> HTH
> Charles Kangai, MCT, MCDBA
>
> "DerekJMiller1" wrote:
> > Hi,
> >
> > I am trying to create a report with a list for each row, which also contains
> > tables of related data (1 to many relationship).
> >
> > There are various drill-down techniques described in the documentation, but
> > I have been unable to determine how to pass a field from the primary record
> > as a parameter to the queries for the sub data.
> >
> > Eg. Have tables Employees and Sales. Sales is related to employees by
> > employeeID field in the Sales table. I want to show a single employee details
> > in list, then multiplae sals records in a table embedded in the list.
> >
> > I need to fgigure out how to pass the current employee.ID field to the table
> > query as a paremeter.
> >
> > Any help on this would be appreciated (even if I have to get this working
> > using sub reports)
> >
> > Thanks,
> > ...Derek
> > --
> > Derek

How to know if my varchar is only alphanumeric

Hi all
I would like to know in a select if my varchar field contains only
alphabetic caracter '
Is there a Function that can tell me this '
Thank
NicWell..you can use the IsNumeric() function like so
Select F1...Fn
From TableName
Where IsNumeric(F1) = 0
However, IsNumeric can sometimes return funky results. For example,
IsNumeric('.') returns 1.
Another way would be:
Select F1...Fn
From TableName
Where F1 Like '[^0-9]'
This expression finds results that do not contain the characters 0...9. The
downside to this approach is that a value of '02A' for example would also be
excluded even though it is not a number per se.
HTH
Thomas|||You could write one...
Create FUNCTION dbo.IsAlpha (@.Value VarChar(7000) )
RETURNS TinyInt
AS
BEGIN
Declare @.HasNonAlpha TinyInt
Declare @.A TinyInt
While Len(@.Value) > 0 Begin
Set @.A = Ascii(@.Value)
Set @.Value = Substring(@.Value, 2, Len(@.Value))
If @.A Not Between Ascii('a') And Ascii('z')
And @.A Not Between Ascii('A') And Ascii('Z')
And @.A <> Ascii(' ') Return 0
End
Return 1
RETURN @.HasNonAlpha
END
"Nicolas Veilleux" wrote:

> Hi all
> I would like to know in a select if my varchar field contains only
> alphabetic caracter '
> Is there a Function that can tell me this '
> Thank
> Nic
>
>|||Refactored...
CREATE FUNCTION dbo.IsAlpha (@.Value VarChar(7000) )
RETURNS TinyInt
AS
BEGIN
Declare @.A TinyInt
While Len(@.Value) > 0 Begin
Set @.A = Ascii(@.Value)
Set @.Value = Substring(@.Value, 2, Len(@.Value))
If @.A Not Between Ascii('a') And Ascii('z')
And @.A Not Between Ascii('A') And Ascii('Z')
And @.A <> Ascii(' ') Return 0
End
Return 1
End
"CBretana" wrote:
> You could write one...
> Create FUNCTION dbo.IsAlpha (@.Value VarChar(7000) )
> RETURNS TinyInt
> AS
> BEGIN
> Declare @.HasNonAlpha TinyInt
> Declare @.A TinyInt
> While Len(@.Value) > 0 Begin
> Set @.A = Ascii(@.Value)
> Set @.Value = Substring(@.Value, 2, Len(@.Value))
> If @.A Not Between Ascii('a') And Ascii('z')
> And @.A Not Between Ascii('A') And Ascii('Z')
> And @.A <> Ascii(' ') Return 0
> End
> Return 1
>
> RETURN @.HasNonAlpha
> END
> "Nicolas Veilleux" wrote:
>|||"Nicolas Veilleux" <nveilleux@.nbautomation.com> wrote in message
news:u9$bVx3RFHA.3120@.TK2MSFTNGP10.phx.gbl...
> Hi all
> I would like to know in a select if my varchar field contains only
> alphabetic caracter '
> Is there a Function that can tell me this '
> Thank
> Nic
>
SELECT
CASE PATINDEX('%[^a-z ]%','This string contains the number 1')
WHEN 0 THEN 'Alphabetic'
ELSE 'Non-alphabetic'
END|||How about :
SELECT PATINDEX('%[^A-Z]%', 'ab1c'), PATINDEX('%[^A-Z]%', 'abc')
If PATINDEX returns 0 then the string contains a non-alpha character.
Might need to be modified for case-sensitive collations.
- KH
"Nicolas Veilleux" wrote:

> Hi all
> I would like to know in a select if my varchar field contains only
> alphabetic caracter '
> Is there a Function that can tell me this '
> Thank
> Nic
>
>|||Sorry - if PATINDEX returns *NON-ZERO* then the string contains a non-alpha
character.
"KH" wrote:
> How about :
> SELECT PATINDEX('%[^A-Z]%', 'ab1c'), PATINDEX('%[^A-Z]%', 'abc')
> If PATINDEX returns 0 then the string contains a non-alpha character.
> Might need to be modified for case-sensitive collations.
> - KH
>
> "Nicolas Veilleux" wrote:
>|||First of all, columns are not fields; learn to think correctly.
CHECK
( LEN
(REPLACE (
REPLACE (
. REPLACE (UPPER(foobar), 'A', '')
.
'Z', '') = LEN(foobar))
Basically, uppercase the string, use nested REPLACE() functions to turn
the alphas into striings and check the length. Do not create a
function with a WHILE loop or other procedural stuff in SQL. That
misses the whole point of a declarative language.
SQL Server has a PATINDEX() which can also be used; I am not sure which
is faster. The REPLACE () is a scan in main storage, but PATINDEX()
has to build a finite state machine.