COLLATION

Hi, what's COLLATION for, can someone give a sample to me to begin?
SELECT COLLATIONPROPERTY('Traditional_Spanish_C
S_AS_KS_WS', 'CodePage')In BOL, click GO - URL... and paste this into "Jump to this URL:" text box.
mk:@.MSITStore:C:\Program%20Files\Microso
ft%20SQL%20Server\80\Tools\Books\arc
hitec.chm::/8_ar_da_6ttf.htm
AMB
"js" wrote:

> Hi, what's COLLATION for, can someone give a sample to me to begin?
> SELECT COLLATIONPROPERTY('Traditional_Spanish_C
S_AS_KS_WS', 'CodePage')
>
>|||Thanks Alejandro,
I also leaned how to use GO C URL.. LOL

Code Snippets

I am looking for a sample code to write a tool or a 3rd party tool that will enable me to get the same functionality as visual studio 2005 code snippets in SSMS.

We do not expose the necessary interfaces to have 3rd parties plug-in their components into SQL Server Management Studio in SQL Server 2005. We are looking at this for a future release. We do have a Template Explorer, which may be of use in the meantime.

Using SQL Server Management Studio Templates
How to: Use Templates in SQL Server Management Studio

Paul A. Mestemaker II
Program Manager
Microsoft SQL Server Manageability
http://blogs.msdn.com/sqlrem/

|||

Maybe you are looking for something like this (with sourcecode)

http://www.codeproject.com/useritems/enisey.asp

|||

Paul I was able to use the templates, its just that I was hoping for similar style of code snippets that Visual Studio has, where I could right click and have some of common tsql scripts available while using a query window. But for now the templates look better than nothing. I am not sure where to put in a suggestions, but that would be my first to have code snippets like visual studio 2005 uses in ssms.

Jurgen thanks for sending the like, but I am looking for more custom snippet storage not neccessarily code generation tools. But Thank you for the suggestion.

Thanks Troutbum!

|||

We have a customer portal where you can file suggestions. It's called Microsoft Connect. You can submit your suggestion here: http://connect.microsoft.com/SQLServer/.

Paul A. Mestemaker II
Program Manager
Microsoft SQL Server Manageability
http://blogs.msdn.com/sqlrem/

Code Samples

I am hoping that someone can direct me to a VB.Net code sample (preferably
ASP.Net) which is a good example of programatically setting ADO.Net
connection properties, calling a stored procedure, supplying user defined
parameters for the stored procedure and outputing the report to a PDF format.
I really need a sample that does all this programatically.
Any examples appreciated.
David Johnsonif you look through msdn, i'm sure you'll find enough examples.
almost for every method / function they have a code snippet
"David" wrote:
> I am hoping that someone can direct me to a VB.Net code sample (preferably
> ASP.Net) which is a good example of programatically setting ADO.Net
> connection properties, calling a stored procedure, supplying user defined
> parameters for the stored procedure and outputing the report to a PDF format.
> I really need a sample that does all this programatically.
> Any examples appreciated.
> David Johnson

Code Sample for SSIS Script Component

Greetings,

I have been developing VBA apps in Access and Excel for sometime and am fairly proficient in VBA. Now we are moving all of our data to SQL Server 2005. I am in need of learning how to write code for the Script Component of a data flow task. And so I have a couple of questions.

First, are there any books you recommend for learning ? (I'm not even sure what I need to learn: .NET? ADO.NET?)

And as a follow-up, any good websites that provide good reference documentation?

And my second question is more specific to my current problem. If I had a bit of code to get me started, I'm sure I could scream all the way to the bottom of the hill.

Data source is coming from a sort task where the data is sorted by STATUS and then MOD_DATE and the AUDIT_ID. I need to read each row and compare it to the next row. If STATUS is the same, discard the second row. When STATUS is different, send the first row to the output (to be used by the next task in the data flow). Using the "different" row from step 3, go to step 2.

I know how to write if statements, case statements, for/next statements. I'm just not understanding how to read the rows in and then send them back out. I've been searching for some sample code but everything I find tends to be solving much bigger issues.

Any help you can provide would be much appreciated.

Rob

1.
Which kind of book do you need ? T-SQL or SQL-CLR integration ?
For T-SQL I suggest you to buy a good basic book from amazon (that ha a good rating) just to learn the basics and then the buy the following two monuments by Itzik Ben-gan (MS Press)
INSIDE SQL SERVER 2005: T-SQL QUERYING
INSIDE SQL SERVER 2005: T-SQL PROGRAMMING
They are really hard... I don't know anything better.
For the second I suggest PRO SQL SERVER 2005 ASSEMBLY by Dewson (Apress)

2.
http://www.sql-server-performance.com
http://www.sqlservercentral.com
Google...

3.
can you explain better ? are you trying to code it in T-SQL or are you trying with a CLR stored procedure ? In the second case to send back a row you may use Pipe.Send or Pipe.ExecuteAndSend methods.

__

? www.carlop.com × carlop-dev.blogspot.com

|||

carlop wrote:

3.
can you explain better ? are you trying to code it in T-SQL or are you trying with a CLR stored procedure ? In the second case to send back a row you may use Pipe.Send or Pipe.ExecuteAndSend methods.

carlop - thank you for the book suggestions. I'll check them out.

Here is a sample of data. I am after capturing each row when the STATUS changes (highlighted in yellow). A total of six rows: one each of Not Ready, Hold and Complete and three of Active. All of the other rows will be discarded.

I'm thinking this is best done with a CLR procedure, however, I'm open to suggestions.

Rob

AUDIT_ID OBJECTID MODIFIED_DATE STATUS 2753029 124248079 4/26/2007 11:30 Active 2753035 124248079 4/26/2007 11:30 Active 2753076 124248079 4/26/2007 11:33 Active 2754823 124248079 4/26/2007 14:31 Not Ready 2794102 124248079 4/27/2007 7:29 Not Ready 2817836 124248079 4/27/2007 17:06 Active 2817859 124248079 4/27/2007 17:09 Active 2818286 124248079 4/27/2007 18:00 Hold 2818473 124248079 4/27/2007 18:38 Active 2818483 124248079 4/27/2007 18:44 Active 2989403 124248079 5/1/2007 7:14 Active 3022521 124248079 5/1/2007 7:19 Active 3042121 124248079 5/1/2007 10:45 Complete 3082880 124248079 5/2/2007 7:07 Complete

code Sample

Hello,
Can someone help me with a SQL code sample to insert a Picture / Documentin
image data field?
Regards
SathianSathian,
One or more of the following KB articles, should help you!
258038 (Q258038) HOWTO: Access and Modify SQL Server BLOB Data by Using the
ADO Stream Object
http://support.microsoft.com/?kbid=258038
201785 (Q201785) HOWTO: Import FileSystem Data Using DTS and Index Server
http://support.microsoft.com/defaul...kb;en-us;201785
309158 (Q309158) HOW TO: Read and Write BLOB Data by Using ADO.NET with C#
http://support.microsoft.com/defaul...kb;EN-US;309158
308042 (Q308042) HOW TO: Read and Write BLOB Data by Using ADO.NET with
VB.NET
http://support.microsoft.com/defaul...kb;EN-US;308042
326502 (Q326502) HOW TO: Read and Write BLOB Data by Using ADO.NET Through
ASP.NET
http://support.microsoft.com/?id=326502
Hope that helps!
John
--
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"Sathian T" <sathian.t@.in.Bosch.com> wrote in message
news:cta232$23a$1@.ns2.fe.internet.bosch.com...
> Hello,
> Can someone help me with a SQL code sample to insert a Picture /
Documentin
> image data field?
> Regards
> Sathian
>|||Hi
The following article and links to KB articles should give you everything
you need!
http://www.aspfaq.com/show.asp?id=2149
John
"Sathian T" wrote:

> Hello,
> Can someone help me with a SQL code sample to insert a Picture / Documenti
n
> image data field?
> Regards
> Sathian
>
>|||or this ;0)
-- OJ: TEXTCOPY example
-- Loading files into db &
-- exporting files out to folder
--
--TEXTCOPY IN
--
--create tb to hold data
create table tmp(fname varchar(100),img image default '0x0')
go
declare @.sql varchar(255),
@.fname varchar(100),
@.path varchar(50),
@.user sysname,
@.pass sysname
set @.user='myuser'
set @.pass='mypass'
--specify desired folder
set @.path='c:\winnt'
set @.sql='dir ' + @.path + '*.bmp /c /b'
--insert filenames into tb
insert tmp(fname)
exec master..xp_cmdshell @.sql
--loop through and insert file contents into tb
declare cc cursor
for select fname from tmp
open cc
fetch next from cc into @.fname
while @.@.fetch_status=0
begin
set @.sql='textcopy /s"'+@.@.servername+'" /u"'+@.user+'" /p"'+@.pass+'"
/d"'+db_name()+'" /t"tmp" /c"img" /w"where fname=''' + @.fname + '''"'
set @.sql=@.sql + ' /f"' + @.path + @.fname + '" /i' + ' /z'
print @.sql
exec master..xp_cmdshell @.sql ,no_output
fetch next from cc into @.fname
end
close cc
deallocate cc
go
select * from tmp
go
--
--TEXTCOPY OUT
--
declare @.sql varchar(255),
@.fname varchar(100),
@.path varchar(50),
@.user sysname,
@.pass sysname
set @.user='myuser'
set @.pass='mypass,'
--specify desired output folder
set @.path='c:\tmp'
set @.sql='md ' + @.path
--create output folder
exec master..xp_cmdshell @.sql
--loop through and insert file contents into tb
declare cc cursor
for select fname from tmp
open cc
fetch next from cc into @.fname
while @.@.fetch_status=0
begin
set @.sql='textcopy /s"'+@.@.servername+'" /u"'+@.user+'" /p"'+@.pass+'"
/d"'+db_name()+'" /t"tmp" /c"img" /w"where fname=''' + @.fname + '''"'
set @.sql=@.sql + ' /f"' + @.path + @.fname + '" /o' + ' /z'
print @.sql
exec master..xp_cmdshell @.sql ,no_output
fetch next from cc into @.fname
end
close cc
deallocate cc
set @.sql='dir ' + @.path + '*.bmp /c /b'
exec master..xp_cmdshell @.sql
go
drop table tmp
go
-oj
"Sathian T" <sathian.t@.in.Bosch.com> wrote in message
news:cta232$23a$1@.ns2.fe.internet.bosch.com...
> Hello,
> Can someone help me with a SQL code sample to insert a Picture /
> Documentin
> image data field?
> Regards
> Sathian
>|||John,
The MAJOR thing wrong with the below link is that it makes NO reference to
the advantages of storing documents (MS Word, Excel, Adobe's PDF, etc.) and
only references the storing of images, such as *.jpg files (which can also
be FT Indexed with existing JPEG IFilters). While, I don't want to start a
flame war, and I've not reviewed all of the KB articles (I will at a later
time, as I have to go to work ;-), I strongly disagree with the premise of
this links recommendation as it makes NO reference to advantage of storing
files for the purpose of accessing the content of these files via Full text
Search that is fully support by Microsoft in SQL Server 7.0, SQL Server 2000
and vastly improved in SQL Server 2005.
Respectively,
John
--
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"John Bell" <JohnBell@.discussions.microsoft.com> wrote in message
news:EFED2C44-641B-4AD8-87D2-4DFB9E45214C@.microsoft.com...
> Hi
> The following article and links to KB articles should give you everything
> you need!
> http://www.aspfaq.com/show.asp?id=2149
> John
>
> "Sathian T" wrote:
>
Documentin

coalesce does not seem to work

Hi,

I have the following table with some sample values, I want to return the first non null value in that order. COALESCE does not seem to work for me, it does not return the 3rd record. I need to include this in my select statement. Any urgent help please.

Mobile Business Private

NULL 345 NULL
4646 65464 65765
NULL 564
654654 564 6546

I want the following as my results:

Number

345
4646
564
654654

Select COALESCE(Mobile,Business,Private) as Number from Table returns:

345
4646

654654

(this is a test to see if private returns & it did with is not null but then how do i include in my select statement to show any one of the 3 fields)

select mobile,business,private where private is not null returns:

65765
564
6546

thanks

As you mentioned, COALESCE returns the first Non NULL value. You result is not what you want but the COALESCE is correct. You have a blank cell in your table. It is not NULL. You can use a CASE statement to check either NULL or blank to get the result you want. Or you can make sure your missing value cells are NULL.

HTH.