how to change the instance name back to default of SQL2k5

hi,

While installing SQL SERVER 2005, I had opted for providing custom name for the SQL Server and named it as for eg. as 'xyz' . Now I would prefer to change it back to default instance so that I can use server=localhost in my connection string of my ASP.NET page. With the custom instance name everytime I have to give
server="Machinename\xyz" which is annoying as I will have to change the connection strings in so many places for my exisiting ASP.NET page.

For e.g.

strConnection="server="Machinename\xyz";database=test;Integrated Security=SSPI;";

I tried using server=(local). It did not work...:(

Also on my another machine which has SQL2k5 installed with default instance I am able to use this string:
strConnection="server=localhost;database=test;Integrated Security=SSPI;";

while the same string I cannot use on the one in which I provided the instance name.

Guess uninstall is the only way.

anyone knows how can I change it back to default instance?

I'm not sure if there is a way to change an instance name. If you must do this, install another copy of SQL Server with the default instance name. Then backup your databases in the xyz instance. Restore them in the default instance. Then uninstall xyz instanace. As for the programs with changing connections strings in web apps, I would recommend pointing them to datasources instead of putting connections string in there. Then, if you modify your sql server configuration you simply have to change the datasouce and not all of your web.config files.|||

Thanks JonM. I know how to create datasources but I don;t know what needs to be put in the ASP.NET file or web.config files to establish the connection.

Suppose I have database called 'test' with DSN name as 'test' in the 'xyz' instance of SQL Server on my machine named 'Development'

How do I achieve this?

Thank you once again for your help.

|||

Basically you change your connection string to 'Datasource=xyx', and remove all of the other stuff, you may still have to specify a username and password if you didnt specify it in the datasouce.

sql

How to change display format of my meaure?

Hi,

Is it possile to definea custom format for the 'display format' property of a measure so that the results are shown in Thousands or Millions or Billions e.g

100,000 can be shown either as 100T
OR
1,000,000 can beshown as 10M
OR
10,000,000 1B

Sorry if I got my numbers wrong but I hope you get the idea?

Looking forward to your help. ThanksLook, you can write code or a function using conversion and text manipulation functions to display the value however you want, but a general principle of database application design is that data formatting is the job of the interface, not the database server. I strongly suggest that you use your interface or reporting tool to format your results (and Query Analyzer is not a user interface!).

how to capture custom made error message into table

Dear all,
I want to know how to custom made error message into table, as I illustrated
T-SQL below :
if objectproperty(object_id('DateTable'),'I
sUserTable')=1
drop table DateTable
if objectproperty(object_id('InputFromFlatF
ile1'),'IsUserTable')=1
drop table InputFromFlatFile1
if objectproperty(object_id('errorLog'),'Is
UserTable')=1
drop table errorLog
if objectproperty(object_id('usp_testData')
,'IsProcedure')=1
drop proc usp_testData
create table DateTable
(datetimestamp datetime)
go
create table errorLog
(
data varchar(200),
errmsg varchar(2000)
)
go
create proc usp_testData
(@.dateinfo datetime)
as
begin
if convert(varchar(8),@.dateinfo,112)<'20060328'
begin
raiserror('Date input invalid because it''s entered before 28 Mar
2006',16,1)
return
end
else
insert into DateTable (datetimestamp) values (@.dateinfo)
end
go
create table InputFromFlatFile1
(
id int identity (1,1) not null,
linestring varchar(100)
)
go
insert into InputFromFlatFile1 (linestring) values ('20060328');
insert into InputFromFlatFile1 (linestring) values ('20060212');
insert into InputFromFlatFile1 (linestring) values ('20060115');
declare @.linestring varchar(200), @.errmsg varchar(2000)
declare @.cnt int, @.recnum int, @.error int
set @.cnt = 1
select @.recnum = count(*) from InputFromFlatFile1
while @.cnt <= @.recnum
begin
select @.linestring = linestring from InputFromFlatFile1 where id = @.cnt;
print @.linestring
exec @.error = usp_testData @.linestring;
if @.error<>0 or @.@.error <> 0
begin
if @.@.error <> 0
begin
set @.error = @.@.error
select @.errmsg = description from master.dbo.sysmessages where error =
@.error
end
/*
How can I capture user-made error in stored procedure usp_testData to put
into ErrorLog table
'
*/
insert into errorLog values (@.linestring, @.errmsg)
end
set @.cnt = @.cnt + 1;
end
select * from DateTable
select * from errorLog
from query analyzer I get error message below :
Date input invalid because it is entered before 28 Mar 2006
but I cannot get the error message as above in errorLog table
How can I capture error message like in query analyzer in errorLog table?
Regards,
Koronx
SQL HobbistHi
Check out http://www.sommarskog.se/error-handling-II.html and
http://www.sommarskog.se/error-handling-I.html You will need to add the
insert statement into your code or possibly do it through the client
application by trapping the error message returned.
John
"Kornx Koronx" wrote:

> Dear all,
> I want to know how to custom made error message into table, as I illustrat
ed
> T-SQL below :
> if objectproperty(object_id('DateTable'),'I
sUserTable')=1
> drop table DateTable
> if objectproperty(object_id('InputFromFlatF
ile1'),'IsUserTable')=1
> drop table InputFromFlatFile1
> if objectproperty(object_id('errorLog'),'Is
UserTable')=1
> drop table errorLog
> if objectproperty(object_id('usp_testData')
,'IsProcedure')=1
> drop proc usp_testData
> create table DateTable
> (datetimestamp datetime)
> go
> create table errorLog
> (
> data varchar(200),
> errmsg varchar(2000)
> )
> go
> create proc usp_testData
> (@.dateinfo datetime)
> as
> begin
> if convert(varchar(8),@.dateinfo,112)<'20060328'
> begin
> raiserror('Date input invalid because it''s entered before 28 Mar
> 2006',16,1)
> return
> end
> else
> insert into DateTable (datetimestamp) values (@.dateinfo)
> end
> go
> create table InputFromFlatFile1
> (
> id int identity (1,1) not null,
> linestring varchar(100)
> )
> go
> insert into InputFromFlatFile1 (linestring) values ('20060328');
> insert into InputFromFlatFile1 (linestring) values ('20060212');
> insert into InputFromFlatFile1 (linestring) values ('20060115');
> declare @.linestring varchar(200), @.errmsg varchar(2000)
> declare @.cnt int, @.recnum int, @.error int
> set @.cnt = 1
> select @.recnum = count(*) from InputFromFlatFile1
> while @.cnt <= @.recnum
> begin
> select @.linestring = linestring from InputFromFlatFile1 where id = @.cnt;
> print @.linestring
> exec @.error = usp_testData @.linestring;
> if @.error<>0 or @.@.error <> 0
> begin
> if @.@.error <> 0
> begin
> set @.error = @.@.error
> select @.errmsg = description from master.dbo.sysmessages where error =
> @.error
> end
> /*
> How can I capture user-made error in stored procedure usp_testData to put
> into ErrorLog table
> '
> */
> insert into errorLog values (@.linestring, @.errmsg)
> end
> set @.cnt = @.cnt + 1;
> end
> select * from DateTable
> select * from errorLog
> from query analyzer I get error message below :
> Date input invalid because it is entered before 28 Mar 2006
>
> but I cannot get the error message as above in errorLog table
> How can I capture error message like in query analyzer in errorLog table?
> Regards,
> Koronx
> SQL Hobbist

How to call a stored proc?

What is the syntax to call a SQL server stored procedure from a report?
Say if I wanted to create a custom field that would be based on an
expression that calls the stored proc (which would return a single
value from a single field - a sum in this case).
What would be the syntax for doing this within a UDF?
Thanks for any help!Hi
it very similar with sending an sql statment.
from the new dataset window choose command type=storsd procedure, and press
OK.
now you will get the stored procedures list. select the wanted SP and run
the query.
now, go back to the dataset window, you will find there your SP parameters.
you can give some values to those parameters.
hope it was helpful
Shlomi
"megabyte" wrote:
> What is the syntax to call a SQL server stored procedure from a report?
> Say if I wanted to create a custom field that would be based on an
> expression that calls the stored proc (which would return a single
> value from a single field - a sum in this case).
> What would be the syntax for doing this within a UDF?
> Thanks for any help!
>|||Thanks for your reply - I think maybe I did not explain this well
enough: The place where I need to call the stored proc is not while
building the original datasets for the report - this stored proc needs
to run during the execution of the report, for each record returned by
one of the datasets - so really the best place for me to call it would
be in the custom "code" section of the report, in a
User-Defined-Function - do you know what the syntax would be for that?
(this stored proc would return one single value, a sum).
Shlomi wrote:
> Hi
> it very similar with sending an sql statment.
> from the new dataset window choose command type=storsd procedure, and press
> OK.
> now you will get the stored procedures list. select the wanted SP and run
> the query.
> now, go back to the dataset window, you will find there your SP parameters.
> you can give some values to those parameters.
>
> hope it was helpful
> Shlomi
>
> "megabyte" wrote:
> > What is the syntax to call a SQL server stored procedure from a report?
> > Say if I wanted to create a custom field that would be based on an
> > expression that calls the stored proc (which would return a single
> > value from a single field - a sum in this case).
> >
> > What would be the syntax for doing this within a UDF?
> >
> > Thanks for any help!
> >
> >|||Hi
I can think about two ways to do this:
1. you can write a custom code for reading from DB, and call it from the
development envionment.
2. you can retrive all data and write client function for dealing the
aggregations. (i did dit once by retrive an XML and write some XML function
to deal with the aggregation).
if you need the exact solution, i'll try to hel you.
Shlomi
"megabyte" wrote:
> Thanks for your reply - I think maybe I did not explain this well
> enough: The place where I need to call the stored proc is not while
> building the original datasets for the report - this stored proc needs
> to run during the execution of the report, for each record returned by
> one of the datasets - so really the best place for me to call it would
> be in the custom "code" section of the report, in a
> User-Defined-Function - do you know what the syntax would be for that?
> (this stored proc would return one single value, a sum).
>
> Shlomi wrote:
> > Hi
> > it very similar with sending an sql statment.
> > from the new dataset window choose command type=storsd procedure, and press
> > OK.
> > now you will get the stored procedures list. select the wanted SP and run
> > the query.
> > now, go back to the dataset window, you will find there your SP parameters.
> > you can give some values to those parameters.
> >
> >
> > hope it was helpful
> >
> > Shlomi
> >
> >
> > "megabyte" wrote:
> >
> > > What is the syntax to call a SQL server stored procedure from a report?
> > > Say if I wanted to create a custom field that would be based on an
> > > expression that calls the stored proc (which would return a single
> > > value from a single field - a sum in this case).
> > >
> > > What would be the syntax for doing this within a UDF?
> > >
> > > Thanks for any help!
> > >
> > >
>|||Yes, 1 is what I'm after - writing custom code for reading from the DB
at runtime and calling it from the dev. environment - that's exactly
what I don't know how to do - it's VB.Net language, right? I have no
idea how to do that - do you? Do you know of sample code out there?
Shlomi wrote:
> Hi
> I can think about two ways to do this:
> 1. you can write a custom code for reading from DB, and call it from the
> development envionment.
> 2. you can retrive all data and write client function for dealing the
> aggregations. (i did dit once by retrive an XML and write some XML function
> to deal with the aggregation).
> if you need the exact solution, i'll try to hel you.
> Shlomi
> "megabyte" wrote:
> > Thanks for your reply - I think maybe I did not explain this well
> > enough: The place where I need to call the stored proc is not while
> > building the original datasets for the report - this stored proc needs
> > to run during the execution of the report, for each record returned by
> > one of the datasets - so really the best place for me to call it would
> > be in the custom "code" section of the report, in a
> > User-Defined-Function - do you know what the syntax would be for that?
> > (this stored proc would return one single value, a sum).
> >
> >
> > Shlomi wrote:
> > > Hi
> > > it very similar with sending an sql statment.
> > > from the new dataset window choose command type=storsd procedure, and press
> > > OK.
> > > now you will get the stored procedures list. select the wanted SP and run
> > > the query.
> > > now, go back to the dataset window, you will find there your SP parameters.
> > > you can give some values to those parameters.
> > >
> > >
> > > hope it was helpful
> > >
> > > Shlomi
> > >
> > >
> > > "megabyte" wrote:
> > >
> > > > What is the syntax to call a SQL server stored procedure from a report?
> > > > Say if I wanted to create a custom field that would be based on an
> > > > expression that calls the stored proc (which would return a single
> > > > value from a single field - a sum in this case).
> > > >
> > > > What would be the syntax for doing this within a UDF?
> > > >
> > > > Thanks for any help!
> > > >
> > > >
> >
> >|||Hi
This MSDN article will tell you everything you want to know about it:
http://msdn2.microsoft.com/en-us/library/ms153561.aspx
if you have more questions, you will be more than welcome.
P.S.
Use the Microsoft enterprise library to read your data.
"megabyte" wrote:
> Yes, 1 is what I'm after - writing custom code for reading from the DB
> at runtime and calling it from the dev. environment - that's exactly
> what I don't know how to do - it's VB.Net language, right? I have no
> idea how to do that - do you? Do you know of sample code out there?
>
> Shlomi wrote:
> > Hi
> > I can think about two ways to do this:
> > 1. you can write a custom code for reading from DB, and call it from the
> > development envionment.
> >
> > 2. you can retrive all data and write client function for dealing the
> > aggregations. (i did dit once by retrive an XML and write some XML function
> > to deal with the aggregation).
> >
> > if you need the exact solution, i'll try to hel you.
> >
> > Shlomi
> >
> > "megabyte" wrote:
> >
> > > Thanks for your reply - I think maybe I did not explain this well
> > > enough: The place where I need to call the stored proc is not while
> > > building the original datasets for the report - this stored proc needs
> > > to run during the execution of the report, for each record returned by
> > > one of the datasets - so really the best place for me to call it would
> > > be in the custom "code" section of the report, in a
> > > User-Defined-Function - do you know what the syntax would be for that?
> > > (this stored proc would return one single value, a sum).
> > >
> > >
> > > Shlomi wrote:
> > > > Hi
> > > > it very similar with sending an sql statment.
> > > > from the new dataset window choose command type=storsd procedure, and press
> > > > OK.
> > > > now you will get the stored procedures list. select the wanted SP and run
> > > > the query.
> > > > now, go back to the dataset window, you will find there your SP parameters.
> > > > you can give some values to those parameters.
> > > >
> > > >
> > > > hope it was helpful
> > > >
> > > > Shlomi
> > > >
> > > >
> > > > "megabyte" wrote:
> > > >
> > > > > What is the syntax to call a SQL server stored procedure from a report?
> > > > > Say if I wanted to create a custom field that would be based on an
> > > > > expression that calls the stored proc (which would return a single
> > > > > value from a single field - a sum in this case).
> > > > >
> > > > > What would be the syntax for doing this within a UDF?
> > > > >
> > > > > Thanks for any help!
> > > > >
> > > > >
> > >
> > >
>

How to build a Custom Delivery Protocol of SMS

Appreciate for any comment about how to build a custom delivery protocol of SMS (short message service). I am working on a Notification Service project but I have no idea how to write a custom delivery protocol for SMS user.

thank you,

Hi Nemo -

Some links that may help...

http://groups.google.com/group/microsoft.public.sqlserver.notificationsvcs/browse_thread/thread/669d84f2ff70e872/cbca58445c41735e?q=sms+&rnum=2#cbca58445c41735e

http://groups.google.com/group/microsoft.public.sqlserver.notificationsvcs/browse_thread/thread/6efc2f9083a1e848/1901ade55f731a4e?q=sms+&rnum=7#1901ade55f731a4e

http://groups.google.com/group/microsoft.public.sqlserver.notificationsvcs/browse_thread/thread/64881889a372fdf6/2e859f334105a583?q=sms+&rnum=5#2e859f334105a583

http://groups.google.com/group/microsoft.public.sqlserver.notificationsvcs/browse_thread/thread/e85aab616313e031/5f054efc9af85ae3?q=sms+&rnum=4#5f054efc9af85ae3

HTH...

Joe

|||

Hi Joe,

I have similar requirements to Nemo, i.e. need to develop a custom delivery protocol for SMS. However, I cannot use the external providers as one of the requirements is that the application should be able to send notifications via an SMPP (Short Message Peer to Peer) Telstra (Aus) server.

Any ideas on how to go about this?

Thanks,

Kevin.

|||

You cannot send SMS messages directly from you application without special hardware (the simplest and the cheapest solution is just a cell phone plugged into your server, but it's not as simple as it sounds... and not very scalable either).

I am now in Phase 1 of my project, and for now our management is happy to have my app send emails to subscribers. However, when we go to Phase 2 (more core functionality + SMS and AIM), I'll just do my best to convince them to buy an out-of-the-box SMS-related solution from a third party.

...or, maybe, we'll buy a GSM device (or, whatever it's called...), and I'll play with programming it as much as I want. ;-)

By the way, many modern cell phones (let alone more sophisticated and expensive devices) have AIM installed by default (my cell phone that I got for free from T-Mobile has AIM), and you can relatively easily develop a custom delivery channel using AIM SDK. -> http://developer.aim.com/aimccMain.jsp

|||

Hi Kevin,

When working with with a 'SMPP server' you'll need to create a TCP connection to their server and communicate using the SMPP protocol. One of the first questions they'll need to answer is what version(s) of SMPP they support. Most network providers support 3.4 (with backward compatibility to 3.3).

Regards

How to build a Custom Delivery Protocol of SMS

Appreciate for any comment about how to build a custom delivery protocol of SMS (short message service). I am working on a Notification Service project but I have no idea how to write a custom delivery protocol for SMS user.

thank you,

Hi Nemo -

Some links that may help...

http://groups.google.com/group/microsoft.public.sqlserver.notificationsvcs/browse_thread/thread/669d84f2ff70e872/cbca58445c41735e?q=sms+&rnum=2#cbca58445c41735e

http://groups.google.com/group/microsoft.public.sqlserver.notificationsvcs/browse_thread/thread/6efc2f9083a1e848/1901ade55f731a4e?q=sms+&rnum=7#1901ade55f731a4e

http://groups.google.com/group/microsoft.public.sqlserver.notificationsvcs/browse_thread/thread/64881889a372fdf6/2e859f334105a583?q=sms+&rnum=5#2e859f334105a583

http://groups.google.com/group/microsoft.public.sqlserver.notificationsvcs/browse_thread/thread/e85aab616313e031/5f054efc9af85ae3?q=sms+&rnum=4#5f054efc9af85ae3

HTH...

Joe

|||

Hi Joe,

I have similar requirements to Nemo, i.e. need to develop a custom delivery protocol for SMS. However, I cannot use the external providers as one of the requirements is that the application should be able to send notifications via an SMPP (Short Message Peer to Peer) Telstra (Aus) server.

Any ideas on how to go about this?

Thanks,

Kevin.

|||

You cannot send SMS messages directly from you application without special hardware (the simplest and the cheapest solution is just a cell phone plugged into your server, but it's not as simple as it sounds... and not very scalable either).

I am now in Phase 1 of my project, and for now our management is happy to have my app send emails to subscribers. However, when we go to Phase 2 (more core functionality + SMS and AIM), I'll just do my best to convince them to buy an out-of-the-box SMS-related solution from a third party.

...or, maybe, we'll buy a GSM device (or, whatever it's called...), and I'll play with programming it as much as I want. ;-)

By the way, many modern cell phones (let alone more sophisticated and expensive devices) have AIM installed by default (my cell phone that I got for free from T-Mobile has AIM), and you can relatively easily develop a custom delivery channel using AIM SDK. -> http://developer.aim.com/aimccMain.jsp

|||

Hi Kevin,

When working with with a 'SMPP server' you'll need to create a TCP connection to their server and communicate using the SMPP protocol. One of the first questions they'll need to answer is what version(s) of SMPP they support. Most network providers support 3.4 (with backward compatibility to 3.3).

Regards

How to build a Custom Delivery Protocol of SMS

Appreciate for any comment about how to build a custom delivery protocol of SMS (short message service). I am working on a Notification Service project but I have no idea how to write a custom delivery protocol for SMS user.

thank you,

Hi Nemo -

Some links that may help...

http://groups.google.com/group/microsoft.public.sqlserver.notificationsvcs/browse_thread/thread/669d84f2ff70e872/cbca58445c41735e?q=sms+&rnum=2#cbca58445c41735e

http://groups.google.com/group/microsoft.public.sqlserver.notificationsvcs/browse_thread/thread/6efc2f9083a1e848/1901ade55f731a4e?q=sms+&rnum=7#1901ade55f731a4e

http://groups.google.com/group/microsoft.public.sqlserver.notificationsvcs/browse_thread/thread/64881889a372fdf6/2e859f334105a583?q=sms+&rnum=5#2e859f334105a583

http://groups.google.com/group/microsoft.public.sqlserver.notificationsvcs/browse_thread/thread/e85aab616313e031/5f054efc9af85ae3?q=sms+&rnum=4#5f054efc9af85ae3

HTH...

Joe

|||

Hi Joe,

I have similar requirements to Nemo, i.e. need to develop a custom delivery protocol for SMS. However, I cannot use the external providers as one of the requirements is that the application should be able to send notifications via an SMPP (Short Message Peer to Peer) Telstra (Aus) server.

Any ideas on how to go about this?

Thanks,

Kevin.

|||

You cannot send SMS messages directly from you application without special hardware (the simplest and the cheapest solution is just a cell phone plugged into your server, but it's not as simple as it sounds... and not very scalable either).

I am now in Phase 1 of my project, and for now our management is happy to have my app send emails to subscribers. However, when we go to Phase 2 (more core functionality + SMS and AIM), I'll just do my best to convince them to buy an out-of-the-box SMS-related solution from a third party.

...or, maybe, we'll buy a GSM device (or, whatever it's called...), and I'll play with programming it as much as I want. ;-)

By the way, many modern cell phones (let alone more sophisticated and expensive devices) have AIM installed by default (my cell phone that I got for free from T-Mobile has AIM), and you can relatively easily develop a custom delivery channel using AIM SDK. -> http://developer.aim.com/aimccMain.jsp

|||

Hi Kevin,

When working with with a 'SMPP server' you'll need to create a TCP connection to their server and communicate using the SMPP protocol. One of the first questions they'll need to answer is what version(s) of SMPP they support. Most network providers support 3.4 (with backward compatibility to 3.3).

Regards