How disable Named Pipes in SQL Server 2000 from VB.NET

Hello,

I have a big problem with SQL Server 2000 and Visual Basic .Net. I wont disable named Pipes from code write in Visual Basic (.Net). How make this ?

This problem is very important for me.

Help me! Please!!!

Try SMO/DMO forum:
http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=88&SiteID=1
(this forum is about SSIS, not sure if anyone knows the answer)

How can we write C# code in sql.

Hello

Anybody knows how to write c# in SQL?

All responses appreciated.

thanks.

There are many examples out there, one of them is http://www.codeproject.com/dotnet/CLR_in_Sql_Server_2005.asp. But anyway if you google for word like clr or c# in combonation with SQL Server 2005 you will find some good examples out there, depending of what you want to do (triggers, udts, udfs, procedures)

HTH, Jens Suessmeyer.|||

There's also the Database Engine .NET Framework Programming section of the Books Online, which has many C# examples.

http://msdn2.microsoft.com/en-us/library/ms131102(SQL.90).aspx

|||hello
thank u, i got what i wanted.
regards

How can I write this SQL statement when the string value is a variable

Hi. I was wondering how I might be able to write the following SQL statement | SET @.AlertSymbol = N'MSFT' | when I want to replace the MSFT with the variable @.StockSymbol?

I'd like to do something like | SET @.AlertSymbol = N'@.StockSymbol' | but that doesn't seem to work as SQL isn't evaluating @.StockSymbol but rather treating it as a string.

All of the following return errors:

SET @.AlertSymbol = N@.StockSymbol
SET @.AlertSymbol = N+@.StockSymbol
SET @.AlertSymbol = N&@.StockSymbol

Thanks, MattJust declare @.StockSymbol as nchar or nvarchar and you can simply use

SET @.AlertSymbol = @.StockSymbol

The N in N'foo' tells SQL Server that 'foo' is in unicode. The same applies to all n<bar> datatypes.|||Originally posted by mt404
Hi. I was wondering how I might be able to write the following SQL statement | SET @.AlertSymbol = N'MSFT' | when I want to replace the MSFT with the variable @.StockSymbol?

I'd like to do something like | SET @.AlertSymbol = N'@.StockSymbol' | but that doesn't seem to work as SQL isn't evaluating @.StockSymbol but rather treating it as a string.

All of the following return errors:

SET @.AlertSymbol = N@.StockSymbol
SET @.AlertSymbol = N+@.StockSymbol
SET @.AlertSymbol = N&@.StockSymbol

Thanks, Matt

Nchar/varchar has higher precedence than char/varchar. Hence, an implicit conversion should take care of this for you.

e.g.

declare @.AlertSymbol nvarchar(10),
@.StockSymbol varchar(10)

set @.StockSymbol='MSFT'

set @.AlertSymbol=@.StockSymbol

--sql2k
select sql_variant_property(@.AlertSymbol,'BaseType'), @.AlertSymbol|||Thaks to both of you for helping me out and teaching me what the N'foo' actually meant.

How Can I Write this SQL Query ?

Hi

I have 2 tables and I want to Get information from that tables by SQL Query but How Can I writ this SQL Query ? .. My target as Follow

Class Table

---------------

ClassID ClassName

1 AA

2 BB

Student Table

---------------

StudentID StudentName ClassID

1 Student 1 1

2 Student 2 1

3 Student 3 2

4 Student 4 1

5 Student 5 2

6 Student 6 1

How Can I Writ SQL Query to get result like the following ..

----------------

ClassID ClassName StudentCount

1 AA 4

2 BB 2

My SQL Query must get all Class table column plus column content the count of student in each class

And thanks with my regarding

Fraas

The simplest way to accomplish this would be a query like this:
SELECT
Class.ClassID,
Class.ClassName,
Count(Student.StudentID) AS StudentCount
FROM
Class
LEFT OUTER JOIN
Student ON Class.ClassID = Student.ClassID
GROUP BY
Class.ClassID,
Class.ClassName
ORDER BY
Class.ClassID

This will return one row for each class, with the number of students ineach class. If no students are found for the class 0 will appearin the StudentCount column.

|||In a related question, how would someone run a similar query thatwouldn't count the number of students but rather concatenate the names,preferably with separators?
|||

Thanks For Your Answer .. it's work nice

How can I write results of a query to output file and save it to the disk

Hi All,
Is there a way to write the results of a query to a file and save it to the
disk? I know that the SQL Query Analyzer gives us the option of 'results to
file'. I want to know whether I can write a script which can query the
database and redirect the results to a file, so that I can open the script
file thru SQL Query Analyzer and run it or even I can use this script in Job
Scheduling when I want.
Thanks,
NN> Is there a way to write the results of a query to a file and save it to
the
> disk? I know that the SQL Query Analyzer gives us the option of 'results
to
> file'. I want to know whether I can write a script which can query the
> database and redirect the results to a file, so that I can open the script
> file thru SQL Query Analyzer and run it or even I can use this script in
Job
> Scheduling when I want.
Just create a regular script, only T-SQL commands; you can run this commands
in QA, job task, or with OSQL.EXE command prompt utility. OSQL can save the
results to an output file. Check the parameters (specifically -i and -o) for
osql in Books onLine.
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com|||Q: How can I read/write to a flat file from inside a SQL Server TSQL
script/stored-procedure/trigger?
A: http://www.mssqlserver.com/faq/general-flatfile.asp
HTH, Jens Smeyer.
http://www.sqlserver2005.de
--
"Nagaraju Nookala" <iranichai@.netscape.net> schrieb im Newsbeitrag
news:OU6DYFFRFHA.3296@.TK2MSFTNGP15.phx.gbl...
> Hi All,
> Is there a way to write the results of a query to a file and save it to
> the disk? I know that the SQL Query Analyzer gives us the option of
> 'results to file'. I want to know whether I can write a script which can
> query the database and redirect the results to a file, so that I can open
> the script file thru SQL Query Analyzer and run it or even I can use this
> script in Job Scheduling when I want.
> Thanks,
> NN
>

How can i write polling and processing queries

i dont want to fully update the molap cache and i also dont want to fully read the source database that is why i am using polling query to poll the changes and processing query to get the changed records from database. for information about polling and processing query you can check the link

http://msdn2.microsoft.com/en-us/library/ms188965.aspx

but i am unable to write these queries to get all changes i.e insert, update and delete records can any one tell me how can i use these queries OR SQL Server provide some other way to just read the changes in source database not the whole dabatase.

This is

about proactive caching, right? If the changes in the table happen to be

insert, update and delete then the processing will *most probably* need to be

done fully.

When you end up with full processing, if you need to use polling notification

mechanism then the processing query is not needed. In case of deletes or

updates the polling query should fetch something like timestamp of the last

change in the database. Why not to use automatic SQL notifications?

The link you provided discusses polling and processing queries for incremental

processing. It is possible when only new records appear in the table (only

inserts).

There is one case when incremental processing can be used with "inserts,

deletes and updates". Suppose at time t1 you have a state of records S1 of

your table. Suppose you manage to make proactive caching to be scheduled at

well known time t2. I suppose Client Initiated is the only reliable one for

deterministic start of the proactive caching. If between t1 an t2 the inserts,

updates and deletes in the table do not change the records from S1 but only

create and modify incremental records then it is possible to use incremental

processing with proactive caching.

How can i write a sproc for dynamic columns

Hi...

Is it possible to eliminate values from a select statements if they are NULL or Blank..

the reason i have use cast as decimals is because that value is a varchar in my database... and i want to eliminate those values from my Final select statement that dont is blank so that i will have a data set which may be only 1 - 10 long...

ALTER PROCEDURE [dbo].[rpt_ParticipantPlanPeriodInvActivity]@.PlanIdint,@.ParticipantIdint,@.PeriodIdintASDECLARE @.tbl table (tblId smallint IDENTITY(1,1),ParticipantIdint,LoanIdint, Name1char(2), NDesc1char(30),TotAct1decimal(19,4),Name2char(2), NDesc2char(30), TotAct2decimal(19,4),Name3char(2), NDesc3char(30),TotAct3decimal(19,4),Name4 Char(2), NDesc4char(30),TotAct4decimal(19,4),Name5char(2),NDesc5char(30),TotAct5decimal(19,4),Name6char(2),NDesc6char(30), TotAct6decimal(19,4),Name7char(2),NDesc7char(30),TotAct7decimal(19,4), Name8char(2),NDesc8char(30),TotAct8decimal(19,4),Name9char(2),NDesc9char(30),TotAct9decimal(19,4), Name10char(2),NDesc10char(30),TotAct10decimal(19,4),Name11char(2),NDesc11char(30),TotAct11decimal(19,4),Name12char(2),NDesc12char(30),TotAct12decimal(19,4),Name13char(2),NDesc13char(30),TotAct13decimal(19,4), Name14char(2),NDesc14char(30), TotAct14decimal(19,4),Name15char(2),NDesc15char(30),TotAct15decimal(19,4),Name16char(2),NDesc16char(30),TotAct16decimal(19,4),Name17char(2),NDesc17char(30),TotAct17decimal(19,4),Name18char(2),NDesc18char(30),TotAct18decimal(19,4), Name19char(2),NDesc19char(30),TotAct19decimal(19,4),Name20char(2),NDesc20char(30),TotAct20decimal(19,4) )Insert Into @.tbl SELECTpf.ParticipantId,pf.FundIdas LoanId, --CASE When FundName Is Null Then ShortName ELSE FundName ENDas FundNames, --pf.PortfolioId,--PortfolioName, Act1as Name1, a.Descriptionas NDesc1, cast(TotAct1as decimal(19,4)) ,Act2as Name2, b.Descriptionas NDesc2,Cast(TotAct2as decimal(19,4)),Act3as Name3, c.Descriptionas NDesc3,Cast(TotAct3as decimal(19,4)),Act4as Name4, d.Descriptionas NDesc4,Cast(TotAct4as decimal(19,4)),Act5as Name5,e.Descriptionas NDesc5,Cast(TotAct5as decimal(19,4)),Act6as Name6, fi.Descriptionas NDesc6,Cast(TotAct6as decimal(19,4)),Act7as Name7,g.Descriptionas NDesc7,Cast(TotAct7as decimal(19,4)),Act8as Name8,h.Descriptionas NDesc8,Cast(TotAct8as decimal(19,4)),Act9as Name9, i.Descriptionas NDesc9,Cast(TotAct9as decimal(19,4)),Act10as Name10, j.Descriptionas NDesc10,Cast(TotAct10as decimal(19,4)),Act11as Name11,k.Descriptionas NDesc11,Cast(TotAct11as decimal(19,4)),Act12as Name12,l.Descriptionas NDesc12,Cast(TotAct12as decimal(19,4)),Act13as Name13, m.Descriptionas NDesc13,Cast(TotAct13as decimal(19,4)),Act14as Name14,n.Descriptionas NDesc14,Cast(TotAct14as decimal(19,4)),Act15as Name15,o.Descriptionas NDesc15,Cast(TotAct15as decimal(19,4)),Act16as Name16,p1.Descriptionas NDesc16,Cast(TotAct16as decimal(19,4)),Act17as Name17,q.Descriptionas NDesc17,Cast(TotAct17as decimal(19,4)),Act18as Name18,r.Descriptionas NDesc18,Cast(TotAct18as decimal(19,4)),Act19as Name19, s.Descriptionas NDesc19,Cast(TotAct19as decimal(19,4)),Act20as Name20,t.Descriptionas NDesc20,Cast(TotAct20as decimal(19,4))FROM ParticipantPlanFundBalances1 pfLeft Outer JOIN Fund f On f.FundId = pf.FundIdLEFT Join PlanPortfolio pOn pf.PortfolioId = p.PortfolioId Left outer Join AscActCodes a on pf.Act1 = a.Name left outer Join AscActCodes b on pf.Act2 = b.Name left outer Join AscActCodes c on pf.Act3 = c.Name left outer Join AscActCodes d on pf.Act4 = d.Nameleft outer Join AscActCodes e on pf.Act5 = e.Nameleft outer Join AscActCodes fi on pf.Act6 = fi.Nameleft outer Join AscActCodes g on pf.Act7 = g.Nameleft outer Join AscActCodes h on pf.Act8 = h.Nameleft Outer Join AscActCodes i on pf.Act9 = i.Nameleft Outer Join AscActCodes j on pf.Act10 = j.Name left outer Join AscActCodes k on pf.Act11 = k.Nameleft outer Join AscActCodes l on pf.Act12 = l.Nameleft outer Join AscActCodes m on pf.Act13 = m.Nameleft outer Join AscActCodes n on pf.Act14 = n.Nameleft outer Join AscActCodes o on pf.Act15 = o.Nameleft outer Join AscActCodes p1 on pf.Act16 = p1.Nameleft outer Join AscActCodes q on pf.Act17 = q.Nameleft outer Join AscActCodes r on pf.Act18 = r.Nameleft outer Join AscActCodes s on pf.Act19 = s.Nameleft outer Join AscActCodes t on pf.Act20 = t.Name WHERE pf.FundId = 0 ANDPeriodId = @.PeriodIdANDpf.PlanId = @.PlanIdANDpf.ParticipantId = @.ParticipantId--Get the Fund informationfor the report and combine it with the Loan information--in the table variable...SELECTpf.ParticipantId,pf.PortfolioId,PortfolioName,pf.FundId LoanFundId, CASE When FundName Is Null Then ShortName ELSE FundName ENDas FundNames,Act1as Name1, a.Descriptionas NDesc1, Cast(TotAct1as decimal(19,4)),Act2as Name2, b.Descriptionas NDesc2,Cast(TotAct2as decimal(19,4)),Act3as Name3, c.Descriptionas NDesc3,Cast(TotAct3as decimal(19,4)),Act4as Name4, d.Descriptionas NDesc4,Cast(TotAct4as decimal(19,4)),Act5as Name5,e.Descriptionas NDesc5,Cast(TotAct5as decimal(19,4)),Act6as Name6, fi.Descriptionas NDesc6,Cast(TotAct6as decimal(19,4)),Act7as Name7,g.Descriptionas NDesc7,Cast(TotAct7as decimal(19,4)),Act8as Name8,h.Descriptionas NDesc8,Cast(TotAct8as decimal(19,4)),Act9as Name9, i.Descriptionas NDesc9,Cast(TotAct9as decimal(19,4)),Act10as Name10, j.Descriptionas NDesc10,Cast(TotAct10as decimal(19,4)),Act11as Name11,k.Descriptionas NDesc11,Cast(TotAct11as decimal(19,4)),Act12as Name12,l.Descriptionas NDesc12,Cast(TotAct12as decimal(19,4)),Act13as Name13, m.Descriptionas NDesc13,Cast(TotAct13as decimal(19,4)),Act14as Name14,n.Descriptionas NDesc14,Cast(TotAct14as decimal(19,4)),Act15as Name15,o.Descriptionas NDesc15,Cast(TotAct15as decimal(19,4)),Act16as Name16,p1.Descriptionas NDesc16,Cast(TotAct16as decimal(19,4)),Act17as Name17,q.Descriptionas NDesc17,Cast(TotAct17as decimal(19,4)),Act18as Name18,r.Descriptionas NDesc18,Cast(TotAct18as decimal(19,4)),Act19as Name19, s.Descriptionas NDesc19,Cast(TotAct19as decimal(19,4)),Act20as Name20,t.Descriptionas NDesc20,Cast(TotAct20as decimal(19,4))FROM ParticipantPlanFundBalances1 pfLeft Outer JOIN Fund f On f.FundId = pf.FundIdLEFT Join PlanPortfolio pOn pf.PortfolioId = p.PortfolioId Left outer Join AscActCodes a on pf.Act1 = a.Name left outer Join AscActCodes b on pf.Act2 = b.Name left outer Join AscActCodes c on pf.Act3 = c.Name left outer Join AscActCodes d on pf.Act4 = d.Nameleft outer Join AscActCodes e on pf.Act5 = e.Nameleft outer Join AscActCodes fi on pf.Act6 = fi.Nameleft outer Join AscActCodes g on pf.Act7 = g.Nameleft outer Join AscActCodes h on pf.Act8 = h.Nameleft Outer Join AscActCodes i on pf.Act9 = i.Nameleft Outer Join AscActCodes j on pf.Act10 = j.Name left outer Join AscActCodes k on pf.Act11 = k.Nameleft outer Join AscActCodes l on pf.Act12 = l.Nameleft outer Join AscActCodes m on pf.Act13 = m.Nameleft outer Join AscActCodes n on pf.Act14 = n.Nameleft outer Join AscActCodes o on pf.Act15 = o.Nameleft outer Join AscActCodes p1 on pf.Act16 = p1.Nameleft outer Join AscActCodes q on pf.Act17 = q.Nameleft outer Join AscActCodes r on pf.Act18 = r.Nameleft outer Join AscActCodes s on pf.Act19 = s.Nameleft outer Join AscActCodes t on pf.Act20 = t.Name WHEREpf.FundId <> 0 ANDPeriodId = @.PeriodIdANDpf.PlanId = @.PlanIdANDParticipantId = @.ParticipantIdUnionSELECTParticipantId,0,'NA',LoanId,'Loan ' + cast(tblIdas char(1)),Name1,NDesc1,Cast(TotAct1as decimal(19,4)),Name2,NDesc2,Cast(TotAct2as decimal(19,4)),Name3,NDesc3,Cast(TotAct3as decimal(19,4)), Name4,NDesc4,Cast(TotAct4as decimal(19,4)),Name5,NDesc5,Cast(TotAct5as decimal(19,4)),Name6,NDesc6,Cast(TotAct6as decimal(19,4)),Name7,NDesc7,Cast(TotAct7as decimal(19,4)),Name8,NDesc8,Cast(TotAct8as decimal(19,4)),Name9,NDesc9,Cast(TotAct9as decimal(19,4)),Name10,NDesc10,Cast(TotAct10as decimal(19,4)),Name11,NDesc11,Cast(TotAct11as decimal(19,4)),Name12,NDesc12,Cast(TotAct12as decimal(19,4)),Name13,NDesc13,Cast(TotAct13as decimal(19,4)),Name14,NDesc14,Cast(TotAct14as decimal(19,4)),Name15,NDesc15,Cast(TotAct15as decimal(19,4)),Name16,NDesc16,Cast(TotAct16as decimal(19,4)),Name17,NDesc17,Cast(TotAct17as decimal(19,4)),Name18,NDesc18,Cast(TotAct18as decimal(19,4)),Name19,NDesc19,Cast(TotAct19as decimal(19,4)),Name20,NDesc20,Cast(TotAct20as decimal(19,4)) FROM @.tbl

Any help will be appreciated.

Regards

Karen

You can either limit rows in the WHERE clause by specifying the NOT NULL option or you can cast at the front end...

|||

huh?

Please post the minimum amount of code necessary to make your question clear. We don't want to read a book! :)

Your queries are bringing back values from many different columns in one resulting row.

Do you want to skip an entire row because one of the columns has a null or blank value? Or something else? I really don't understand.

|||

I want to skip an entire column because everything is NULL

|||

Never ask a query you don't want the answer to! :)

The short answer is no. If you query a value as a distinct column in the result set, you get that column in the result set.

The longer answer is maybe. If you don't mind concatenating multiple values together into one column in the result set, you can effectively skip the column. Just be sure to use isnull(columnname,'') to replace null values with an empty string, otherwise the whole string will end up null. That's a useful technique for writing lines of text that need to be printed out (as in queries that write sql code), but useless if you need the other column values back as discrete values.

|||

Thanks for your answer... can u tell a good place where i paste a screen shot of my report and show it to others..

Regards

Karen

|||

Karenros:

Thanks for your answer... can u tell a good place where i paste a screen shot of my report and show it to others..

Sorry, no. I'm sure you can get a free website with yahoo or geocities.

|||

David and Dinakar

Thanks for your answers..

Suppose if i have declared a table in my sproc.. like

Declare @.tbl table

(

Column1 - Column N

)

Is it possible to add columns dynamically to it.. like for eg..

my select statement has around 10 columns and in that 4 columns are returning no value... so can i adjust the number of columns in that table.. based on the select statement results...

Regards

Karen

|||

Karenros:

Declare @.tbl table

(

Column1 - Column N

)

I don't understand the syntax you are using in your example.

You could do this as a two step process.

Issue the query with all the columns.

Construct a query statement in a string that only includes the columns that got results and exec that query string.

Honestly, why not just set the Visible or Hidden property of the UI component to false for those columns that don't have values. I think that would be a lot simpler. :)

|||

The reason i am not doing it in the UI compenent,, cause in SSRS 2005 if i hide a column the width of the table would shrink.

Declare @.Tbl Table

(

tbld int identity,

col 1,

Col2,

.

.

Col N

)

|||

To add columns you need to ALTER TABLE. I dont think you can ALTER a table variable. you might either need to use an actual table or a temporary table... I havent looked at your entire post.. I've been quite busy last week and will be so next week too.. am just posting based on your most recent post...

how can i use the " use " statement with database-name contains spaces in sql2000

example :

i cann't write :

use [my db]

what can i do with this problem ?

That works for me:

use [master]

CREATE DATABASE [Some DB]

USE [Some DB]

SELECT DB_NAME()

-

Some DB

(1 row(s) affected)

USE MASTER

DROP DATABASE [Some DB]

What error are you getting back ? Regardless of the possibiliy to put a space in the name, this is NOT recommended. It might work in the most cases but you will run in many cases where this isn′t supported (liek third party vendors)

HTH, Jens Suessmeyer.

How can I use getdate() to be an input parameter in a sproc?

I am trying to write a sproc that automatically uses the system date (i.e. -
getdate()) as an input parameter. Even though it displays it does not beha
ve like an input parameter.Casey
Do you need the entire date down the minutes and seconds, in other words, an
exact snapshot
of the date?
If not, you can just refer to GETDATE() right within your procedure and
bypass the
parameter part.
"Casey" <cevans2@.edd.ca.gov> wrote in message
news:57B71663-C451-45D0-BB67-FE2B21BCCA25@.microsoft.com...
> I am trying to write a sproc that automatically uses the system date
(i.e. - getdate()) as an input parameter. Even though it displays it does
not behave like an input parameter.|||Hi,
Use the below sample,
alter proc test_proc2
as
begin
declare @.to_day smalldatetime
set @.to_day = getdate()
select @.to_day
end
Incase if it is a must to have date as input parameter then,
alter proc test_proc2 @.to_day datetime = '01/01/1900'
as
begin
set @.to_day = getdate()
select @.to_day
end
Thanks
Hari
MCDBA
"Casey" <cevans2@.edd.ca.gov> wrote in message
news:57B71663-C451-45D0-BB67-FE2B21BCCA25@.microsoft.com...
> I am trying to write a sproc that automatically uses the system date
(i.e. - getdate()) as an input parameter. Even though it displays it does
not behave like an input parameter.