Showing posts with label write. Show all posts
Showing posts with label write. Show all posts

Monday, March 26, 2012

Need HELP

I have a string 'I Love India'

I want to write a query that will display o/p in following format--

I

LOVE

INDIA

all in seperate lines

Thanks,

Ankur

The code below will meet your very specific requirement.

Are you looking for a solution that you can utilise for any text string containing spaces?

Chris

SELECT t.[Text]

FROM

(SELECT 1 AS [ID], 'I' AS [TEXT] UNION

SELECT 2, 'LOVE' UNION

SELECT 3, 'INDIA') t

ORDER BY T.[ID]

|||

Use a variation of Jen Seussmeyer's SPLIT function, located here:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=326300&SiteID=1

Or, if you are really attempting to simulate an array, check out these resources:

Arrays and Lists in SQL Server
http://www.sommarskog.se/arrays-in-sql.html
http://www.realsqlguy.com/?p=9
http://www.aspfaq.com/2248
http://www.projectdmx.com/tsql/sqlarrays.aspx
http://realsqlguy.com/twiki/bin/view/RealSQLGuy/ParseDelimitedStringToTable
2005- http://omnibuzz-sql.blogspot.com/2006/06/interesting-queries-using-recursive.html
http://solidqualitylearning.com/Blogs/dejan/archive/2004/10/22/200.aspx

Friday, March 23, 2012

Need DTS/VBScript guru

I am trying to write a DTS package at work that uses a loop to fire a query off against a different server/database at each loop iteration. Prior to the execute SQL task , I use ActiveX (VBScript) to change the Catalog and DataSource of the connection (created by drag and drop if icon). My Execute SQL task selects @.@.servername and getdate() just as a dummy query to make sure I am actually pointed at a different server.

While testing the properties of the connection using global variables and msgbox shows that the database (catalog) and server (data source) ip address is being changed, the result returned is always from the first server. I feel like I need to disconnect the connection object, change the parameters and then reconnect to the new server but there doesn't appear to be anyway to do this.

Anyone out there able to successfully change a connection object inthis manner??I figured it out myself but just in case anyone else has this problem:

In order to use activeX to change a connection object DataSource/Catalog (server/database) from one SQL server to another one (different physical box) via ActiveX Script task you must set the RESUABLE property to 0 rather than the value -1 as mine had defaulted to. Now it all works like a charm...

Wednesday, March 21, 2012

need book reccommendation

I have just accepted a new position where a new role of mine will be
managing SQL servers. I don't need to know how to write code and what have
you, just set up databases and admin type stuff. Can someone please
reccommend a book that I can read to get me familiar with terms and
procedures. I just need to develope a good understanding as quick as
possible. Thanks
Brandon Baker
A+, Net+, MCSA, MCSE
Others will have some actual text recommendations, but I firmly believe the
best reference anywhere is Books Online, which is installed as part of the
client tools along with SQL Server.
Or, you can view online here:
http://msdn.microsoft.com/library/de...start_4fht.asp
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
"Brandon Baker" <mrmoneymatters@.nospam.postalias> wrote in message
news:OXXq5073FHA.3292@.tk2msftngp13.phx.gbl...
>I have just accepted a new position where a new role of mine will be
>managing SQL servers. I don't need to know how to write code and what have
>you, just set up databases and admin type stuff. Can someone please
>reccommend a book that I can read to get me familiar with terms and
>procedures. I just need to develope a good understanding as quick as
>possible. Thanks
> --
> Brandon Baker
> A+, Net+, MCSA, MCSE
>
|||"Brandon Baker" <mrmoneymatters@.nospam.postalias> wrote in
news:OXXq5073FHA.3292@.tk2msftngp13.phx.gbl:

> I have just accepted a new position where a new role of mine will be
> managing SQL servers. I don't need to know how to write code and what
> have you, just set up databases and admin type stuff. Can someone
> please reccommend a book that I can read to get me familiar with terms
> and procedures. I just need to develope a good understanding as quick
> as possible. Thanks
Perhaps SQL Server 2000 Administrators Companion is what you look for. At
least I'm glad to have it in my bookshelf.
Ole Kristian Bangs
MCT, MCDBA, MCDST, MCSE:Security, MCSE:Messaging
|||"Brandon Baker" <mrmoneymatters@.nospam.postalias> wrote in message
news:OXXq5073FHA.3292@.tk2msftngp13.phx.gbl...
> I have just accepted a new position where a new role of mine will be
> managing SQL servers. I don't need to know how to write code and what have
> you, just set up databases and admin type stuff. Can someone please
> reccommend a book that I can read to get me familiar with terms and
> procedures. I just need to develope a good understanding as quick as
> possible. Thanks
> --
> Brandon Baker
> A+, Net+, MCSA, MCSE
I've found this book invaluable: Microsoft SQL Server 2000 DBA Survival
Guide, Second Edition (Paperback)
by Mark Spenik, Orryn Sledge

need book reccommendation

I have just accepted a new position where a new role of mine will be
managing SQL servers. I don't need to know how to write code and what have
you, just set up databases and admin type stuff. Can someone please
reccommend a book that I can read to get me familiar with terms and
procedures. I just need to develope a good understanding as quick as
possible. Thanks
Brandon Baker
A+, Net+, MCSA, MCSEOthers will have some actual text recommendations, but I firmly believe the
best reference anywhere is Books Online, which is installed as part of the
client tools along with SQL Server.
Or, you can view online here:
http://msdn.microsoft.com/library/d...r />
_4fht.asp
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
"Brandon Baker" <mrmoneymatters@.nospam.postalias> wrote in message
news:OXXq5073FHA.3292@.tk2msftngp13.phx.gbl...
>I have just accepted a new position where a new role of mine will be
>managing SQL servers. I don't need to know how to write code and what have
>you, just set up databases and admin type stuff. Can someone please
>reccommend a book that I can read to get me familiar with terms and
>procedures. I just need to develope a good understanding as quick as
>possible. Thanks
> --
> Brandon Baker
> A+, Net+, MCSA, MCSE
>|||"Brandon Baker" <mrmoneymatters@.nospam.postalias> wrote in
news:OXXq5073FHA.3292@.tk2msftngp13.phx.gbl:

> I have just accepted a new position where a new role of mine will be
> managing SQL servers. I don't need to know how to write code and what
> have you, just set up databases and admin type stuff. Can someone
> please reccommend a book that I can read to get me familiar with terms
> and procedures. I just need to develope a good understanding as quick
> as possible. Thanks
Perhaps SQL Server 2000 Administrators Companion is what you look for. At
least I'm glad to have it in my bookshelf.
Ole Kristian Bangs
MCT, MCDBA, MCDST, MCSE:Security, MCSE:Messaging|||"Brandon Baker" <mrmoneymatters@.nospam.postalias> wrote in message
news:OXXq5073FHA.3292@.tk2msftngp13.phx.gbl...
> I have just accepted a new position where a new role of mine will be
> managing SQL servers. I don't need to know how to write code and what have
> you, just set up databases and admin type stuff. Can someone please
> reccommend a book that I can read to get me familiar with terms and
> procedures. I just need to develope a good understanding as quick as
> possible. Thanks
> --
> Brandon Baker
> A+, Net+, MCSA, MCSE
I've found this book invaluable: Microsoft SQL Server 2000 DBA Survival
Guide, Second Edition (Paperback)
by Mark Spenik, Orryn Sledge

need book reccommendation

I have just accepted a new position where a new role of mine will be
managing SQL servers. I don't need to know how to write code and what have
you, just set up databases and admin type stuff. Can someone please
reccommend a book that I can read to get me familiar with terms and
procedures. I just need to develope a good understanding as quick as
possible. Thanks
--
Brandon Baker
A+, Net+, MCSA, MCSEOthers will have some actual text recommendations, but I firmly believe the
best reference anywhere is Books Online, which is installed as part of the
client tools along with SQL Server.
Or, you can view online here:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/startsql/getstart_4fht.asp
--
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
"Brandon Baker" <mrmoneymatters@.nospam.postalias> wrote in message
news:OXXq5073FHA.3292@.tk2msftngp13.phx.gbl...
>I have just accepted a new position where a new role of mine will be
>managing SQL servers. I don't need to know how to write code and what have
>you, just set up databases and admin type stuff. Can someone please
>reccommend a book that I can read to get me familiar with terms and
>procedures. I just need to develope a good understanding as quick as
>possible. Thanks
> --
> Brandon Baker
> A+, Net+, MCSA, MCSE
>|||"Brandon Baker" <mrmoneymatters@.nospam.postalias> wrote in
news:OXXq5073FHA.3292@.tk2msftngp13.phx.gbl:
> I have just accepted a new position where a new role of mine will be
> managing SQL servers. I don't need to know how to write code and what
> have you, just set up databases and admin type stuff. Can someone
> please reccommend a book that I can read to get me familiar with terms
> and procedures. I just need to develope a good understanding as quick
> as possible. Thanks
Perhaps SQL Server 2000 Administrators Companion is what you look for. At
least I'm glad to have it in my bookshelf.
--
Ole Kristian Bangås
MCT, MCDBA, MCDST, MCSE:Security, MCSE:Messaging|||"Brandon Baker" <mrmoneymatters@.nospam.postalias> wrote in message
news:OXXq5073FHA.3292@.tk2msftngp13.phx.gbl...
> I have just accepted a new position where a new role of mine will be
> managing SQL servers. I don't need to know how to write code and what have
> you, just set up databases and admin type stuff. Can someone please
> reccommend a book that I can read to get me familiar with terms and
> procedures. I just need to develope a good understanding as quick as
> possible. Thanks
> --
> Brandon Baker
> A+, Net+, MCSA, MCSE
I've found this book invaluable: Microsoft SQL Server 2000 DBA Survival
Guide, Second Edition (Paperback)
by Mark Spenik, Orryn Sledge

Monday, March 12, 2012

Need advice on IF...THEN...ELSE type T-SQL syntax

I don't write SQL that often, so...

What is the most "efficient" way to write an SQL statement that accomplishes:

If a record for this key exists in this table, just update it with the following information, but if a record does not exist, create one with the information.

Thanks in advance.

Look into If Exists.

|||

try this

ifexists(SELECT key_fieldfrom yourtablewhere key_field='key')BEGINUPDATE yourtableset [thefield]='your new value'WHERE key_field='key'END
|||

My understanding is that with the 2008 release of sql server, it will add the MERGE command, which is designed precisely for this purpose.

Otherwise, you'll have to use the prior example (but remember to add an "else" branch to do the insert.

Alternatively, there is a way to do it as two statements (one update, one insert) that will handle all the rows you need updated/inserted.

It just depends upon the nature of the update. If all the records to be updated need to be updated with the same info (i.e., add 5% to the salary), one update statement will update all of them.

Then, do an insert statement in the form of:

insert into destination_table (col1,col2) select colA, colB from source_table where source_table.key_column not in (select key_column from destination_table)

That will insert all the missing records.

|||

Thanks for the push, all. I also came upon the slightly different approach of

Do the Update, if the @.@.ROWCOUNT equals 0, do the insert.

|||

DisturbedBuddha:

Thanks for the push, all. I also came upon the slightly different approach of

Do the Update, if the @.@.ROWCOUNT equals 0, do the insert.


Duh! Much easier and probably more efficient than the exists check.

|||

http://disturbedbuddha.wordpress.com/2007/11/29/easy-sql-if-record-exists-update-it-if-not-insert-it/

Friday, March 9, 2012

Need advice how to write a program to receive an image via email and put it into the SQL S

Hello,
I am trying to find a way to write a program to do the following:
Receive an image via email, retrieve that image and put it into a database.
Could you please give me an advice where to start or point me into the right
direction? Is there an ASP component somewhere that would do the trick?
P.S. I have my own Windows 2003 server and SQL Server 2000, and can install
anything on my server if I need to.
Thank you,
Robert ZolnarHi Vanessa
If you can send the image as an attachment xp_readmail can save this to disc
where it can then be loaded in to the database. For SQL Mail you will need a
MAPI client e.g. Outlook 2000 installed on the server. Check out
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnovba01/html/SQLServerE-mail.asp
and Books online for more. Once the image has been materialised you than
then use something like the Bii sample program to load it
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/samples/samples_2isp.asp
If you want to do away with the Email you could use upload the image file
into a location (share) that is accessable by your SQL Server see
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/samples/samples_2isp.asp
This link also discusses the pros and cons of storing images in the database
John
"Vanessa Lee" <van77788@.yahoo.com> wrote in message
news:roKdnfLGWt-zymXeRVn-sw@.comcast.com...
> Hello,
> I am trying to find a way to write a program to do the following:
> Receive an image via email, retrieve that image and put it into a
> database.
> Could you please give me an advice where to start or point me into the
> right
> direction? Is there an ASP component somewhere that would do the trick?
> P.S. I have my own Windows 2003 server and SQL Server 2000, and can
> install
> anything on my server if I need to.
> Thank you,
> Robert Zolnar
>|||Hello John,
Thank you for your reply. Now that I know about MAPI as you mentioned, I
wonder if there is a way to avoid using SQL server at all and saving the
image to a file with a specific filename for a later retrieval.
Here is how I think it would work:
(1) Receive an image in email attachment.
(2) Retrieve the image from that attachment.
(3) Change the name of the image file into what was written in the Subject
of that email.
(4) Save the renamed image file into a specific folder.
(5) Delete the received email
Do you know about any other components or programs somewhere on the net
already written preferably in VBScript that it might be helpful for me to
look at?
Thank you.
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:O6LWOqVNGHA.720@.TK2MSFTNGP14.phx.gbl...
> Hi Vanessa
> If you can send the image as an attachment xp_readmail can save this to
disc
> where it can then be loaded in to the database. For SQL Mail you will need
a
> MAPI client e.g. Outlook 2000 installed on the server. Check out
>
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnovba01/html/SQLServerE-mail.asp
> and Books online for more. Once the image has been materialised you than
> then use something like the Bii sample program to load it
>
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/samples/samples_2isp.asp
> If you want to do away with the Email you could use upload the image file
> into a location (share) that is accessable by your SQL Server see
>
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/samples/samples_2isp.asp
> This link also discusses the pros and cons of storing images in the
database
> John
>
> "Vanessa Lee" <van77788@.yahoo.com> wrote in message
> news:roKdnfLGWt-zymXeRVn-sw@.comcast.com...
> > Hello,
> >
> > I am trying to find a way to write a program to do the following:
> >
> > Receive an image via email, retrieve that image and put it into a
> > database.
> >
> > Could you please give me an advice where to start or point me into the
> > right
> > direction? Is there an ASP component somewhere that would do the trick?
> >
> > P.S. I have my own Windows 2003 server and SQL Server 2000, and can
> > install
> > anything on my server if I need to.
> >
> > Thank you,
> > Robert Zolnar
> >
> >
>|||Hi
What you are wanting to do should certainly be possible, although I have
never used it with VBScript. See
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/exchanchor/htms/msexchsvr_mapi.asp for the SDK although the example are mainly in C/C++
John
"Vanessa Lee" wrote:
> Hello John,
> Thank you for your reply. Now that I know about MAPI as you mentioned, I
> wonder if there is a way to avoid using SQL server at all and saving the
> image to a file with a specific filename for a later retrieval.
> Here is how I think it would work:
> (1) Receive an image in email attachment.
> (2) Retrieve the image from that attachment.
> (3) Change the name of the image file into what was written in the Subject
> of that email.
> (4) Save the renamed image file into a specific folder.
> (5) Delete the received email
> Do you know about any other components or programs somewhere on the net
> already written preferably in VBScript that it might be helpful for me to
> look at?
> Thank you.
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:O6LWOqVNGHA.720@.TK2MSFTNGP14.phx.gbl...
> > Hi Vanessa
> >
> > If you can send the image as an attachment xp_readmail can save this to
> disc
> > where it can then be loaded in to the database. For SQL Mail you will need
> a
> > MAPI client e.g. Outlook 2000 installed on the server. Check out
> >
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnovba01/html/SQLServerE-mail.asp
> > and Books online for more. Once the image has been materialised you than
> > then use something like the Bii sample program to load it
> >
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/samples/samples_2isp.asp
> >
> > If you want to do away with the Email you could use upload the image file
> > into a location (share) that is accessable by your SQL Server see
> >
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/samples/samples_2isp.asp
> > This link also discusses the pros and cons of storing images in the
> database
> >
> > John
> >
> >
> > "Vanessa Lee" <van77788@.yahoo.com> wrote in message
> > news:roKdnfLGWt-zymXeRVn-sw@.comcast.com...
> > > Hello,
> > >
> > > I am trying to find a way to write a program to do the following:
> > >
> > > Receive an image via email, retrieve that image and put it into a
> > > database.
> > >
> > > Could you please give me an advice where to start or point me into the
> > > right
> > > direction? Is there an ASP component somewhere that would do the trick?
> > >
> > > P.S. I have my own Windows 2003 server and SQL Server 2000, and can
> > > install
> > > anything on my server if I need to.
> > >
> > > Thank you,
> > > Robert Zolnar
> > >
> > >
> >
> >
>
>

Need A writen Help about writen application with RS.

I need to write VB.NET or C# App. that have an input
boxes that tansfer their content to RS parameters
and then renders the report.
thanks.You can take a look at the ReportViewer sample provided
with the RS installation.
>--Original Message--
> I need to write VB.NET or C# App. that have an input
>boxes that tansfer their content to RS parameters
>and then renders the report.
>
>thanks.
>
>.
>

Wednesday, March 7, 2012

need a small help on SQL Query.

guys i need a small help on SQL Query.

I want to write a SELECT query which gives me CASE SENSITIVE search result. for ex: if a users original password in db is 'TOM' and user while logging in enters his password as 'tom', he should not be able to login ..But if he enters the password as 'TOM' he is permitted to log in .. How do i check this using a SQL Select Query ?
Any idea on this would be greatly appreciated..

NimeshUsing the PUBS database as an example, I have SQL Server installed as case insensitive. I can write a query to return the Author 'Green' from the AUTHORS table as:

select *
from authors
where au_lname = 'green'

Which would return 1 record even if the lastname is 'Green' with a capital letter. However if I want to enforce case sensitive I would convert the values to VARBINARY. The following would not return a record:

select *
from authors
where convert(varbinary,au_lname) = convert(varbinary,'green')

However this would return 1 record:

select *
from authors
where convert(varbinary,au_lname) = convert(varbinary,'Green')

Need a query to get path for last DB backup

Help needed. I need a query (or location of that data so I can write
one) to get the file path information for the last backup taken.
Many thanks.> Help needed. I need a query (or location of that data so I can write
> one) to get the file path information for the last backup taken.
SELECT TOP 1 mf.physical_device_name
FROM msdb.dbo.backupset AS b
LEFT JOIN msdb.dbo.backupmediafamily AS mf ON b.media_set_id=mf.media_set_id
WHERE b.database_name='HeliosDB'
ORDER BY b.backup_start_date DESC
--
Tom
http://kbupdate.info/ | http://suppline.com/|||>> Help needed. I need a query (or location of that data so I can write
>> one) to get the file path information for the last backup taken.
> SELECT TOP 1 mf.physical_device_name
> FROM msdb.dbo.backupset AS b
> LEFT JOIN msdb.dbo.backupmediafamily AS mf ON
> b.media_set_id=mf.media_set_id
> WHERE b.database_name='HeliosDB'
> ORDER BY b.backup_start_date DESC
Oops, typo. Right version:
.
.
WHERE b.database_name=N'YourDatabaseName'
.
.
--
Tom
http://kbupdate.info/ | http://suppline.com/