hi,
a field with all capital letters for last name and frist name -- DAVID
JONES
I need a funcation taht I could retrive it from sql query analyzer
select propercase(field1) from names. it returns "David Jones"
THnaksHi
A google search would turn up many hits such as
http://vyaskn.tripod.com/code/propercase.txt but it doesn't cater for all
names e.g MacNeil, McNeil, O'Neill etc..
John
"mecn" wrote:
> hi,
> a field with all capital letters for last name and frist name -- DAVID
> JONES
> I need a funcation taht I could retrive it from sql query analyzer
> select propercase(field1) from names. it returns "David Jones"
> THnaks
>
>
Showing posts with label analyzer. Show all posts
Showing posts with label analyzer. Show all posts
Friday, March 23, 2012
Propagating results of Alter Table to its views
When I alter a table to add columns or change their properties, either in
Enterprise Manager or with T-SQL statements in Query Analyzer, the
alterations are not automatically noticed by the views that use the table.
The only thing I have been able to figure out to do is to find each
potentially affected view and then:
1. Open it in design mode
2. Check or uncheck a field in the Altered table
3. Uncheck or check the same field to restore the view to its original field
selection
4. Close the view and Save it.
Two problems:
1. That's tedious and time-consuming
2. It is easy to miss an affected view.
Can someone advise me as to a better way? (I am mostly using SQL Server 2000
)
Thanks,
Doug MacLeanDoug MacLean wrote:
> When I alter a table to add columns or change their properties, either in
> Enterprise Manager or with T-SQL statements in Query Analyzer, the
> alterations are not automatically noticed by the views that use the table.
> The only thing I have been able to figure out to do is to find each
> potentially affected view and then:
> 1. Open it in design mode
> 2. Check or uncheck a field in the Altered table
> 3. Uncheck or check the same field to restore the view to its original fie
ld
> selection
> 4. Close the view and Save it.
> Two problems:
> 1. That's tedious and time-consuming
> 2. It is easy to miss an affected view.
> Can someone advise me as to a better way? (I am mostly using SQL Server 20
00)
> --
> Thanks,
> Doug MacLean
Firstly, do not use SELECT * in views. It's generally a bad idea to use
SELECT * anywhere in production code. It's a particularly bad idea in
views because of the way views handle changes to the columns.
So assuming you have named columns in all your views, use
sp_refreshview to ensure the view is up to date with changes to its
columns. If you want to add a column you'll have to edit the view
definition of course, which is good practice and not difficult if you
have adequate source control and change control procedures. Table
changes should never be executed by Enterprise Manager in a production
environment.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||David,
Thanks. That's very helpful. It eliminates the need for my 4-step process in
Enterprise Manager.
I wonder if there is a tool to list all of the views that depend on a
specified table, because the other challenge is actually finding all of them
when altering a table.
Best Regards,
--
Doug MacLean
"David Portas" wrote:
> Doug MacLean wrote:
> Firstly, do not use SELECT * in views. It's generally a bad idea to use
> SELECT * anywhere in production code. It's a particularly bad idea in
> views because of the way views handle changes to the columns.
> So assuming you have named columns in all your views, use
> sp_refreshview to ensure the view is up to date with changes to its
> columns. If you want to add a column you'll have to edit the view
> definition of course, which is good practice and not difficult if you
> have adequate source control and change control procedures. Table
> changes should never be executed by Enterprise Manager in a production
> environment.
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>|||Greetings,
"Doug MacLean" <DougMacLean@.discussions.microsoft.com> wrote in message
news:C701B020-919E-44B3-A61E-1FCF7AC6AA8D@.microsoft.com...
> David,
> Thanks. That's very helpful. It eliminates the need for my 4-step process
> in
> Enterprise Manager.
> I wonder if there is a tool to list all of the views that depend on a
> specified table, because the other challenge is actually finding all of
> them
> when altering a table.
> Best Regards,
> --
> Doug MacLean
Create the views WITH SCHEMABINDING and you won't be able to change the
table without first dropping the view. This is a good reminder to fix the
view ;-)
Regards,
Neale NOON|||You may want to use this.
select * from INFORMATION_SCHEMA.VIEW_TABLE_USAGE
where table_name = '<table_name>'
-Omnibuzz (The SQL GC)
http://omnibuzz-sql.blogspot.com/|||> I wonder if there is a tool to list all of the views that depend on a
> specified table, because the other challenge is actually finding all of
> them
> when altering a table.
You might find it easier to refresh all views since you cannot rely on
dependency information unless the views were created WITH SCHEMABINDING.
The script below will generate a script to refresh all views in the current
database. You can wrap it in a cursor and execute according to your
preference. BTW, even without 'SELECT *', you can run into issues with
changed datatypes in the referenced tables.
SELECT
'EXEC sp_refreshview ''' +
QUOTENAME(TABLE_SCHEMA) +
N'.' +
QUOTENAME(TABLE_NAME) +
''''
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'VIEW'
AND OBJECTPROPERTY(OBJECT_ID(
QUOTENAME(TABLE_SCHEMA) +
N'.' +
QUOTENAME(TABLE_NAME)
), 'IsMSShipped') = 0
Hope this helps.
Dan Guzman
SQL Server MVP
"Doug MacLean" <DougMacLean@.discussions.microsoft.com> wrote in message
news:C701B020-919E-44B3-A61E-1FCF7AC6AA8D@.microsoft.com...
> David,
> Thanks. That's very helpful. It eliminates the need for my 4-step process
> in
> Enterprise Manager.
> I wonder if there is a tool to list all of the views that depend on a
> specified table, because the other challenge is actually finding all of
> them
> when altering a table.
> Best Regards,
> --
> Doug MacLean
>
> "David Portas" wrote:
>|||Thanks, Neale.
That's an interesting idea to know when I change a table that has one or
more dependent views. So it is a good reminder.
But I KNOW there are views. And what I want is a clean way of finding and
refreshing them. Adding a process of dropping (and then re-adding) them seem
s
tedious.
Thanks Best Regards,
--
Doug MacLean
"Neale NOON" wrote:
> Greetings,
> "Doug MacLean" <DougMacLean@.discussions.microsoft.com> wrote in message
> news:C701B020-919E-44B3-A61E-1FCF7AC6AA8D@.microsoft.com...
>
> Create the views WITH SCHEMABINDING and you won't be able to change the
> table without first dropping the view. This is a good reminder to fix the
> view ;-)
> --
> Regards,
> Neale NOON
>
>|||Dan,
Ahhh!. That's a great idea. Thanks for both the idea and the sample code.
And it's practical because I only do such updates on the production system a
t
times when no one is using it.
You're right. I've already seen the problem that -- even without adding
columns -- the view needs to be refreshed.
Thanks much and Best regards,
--
Doug MacLean
"Dan Guzman" wrote:
> You might find it easier to refresh all views since you cannot rely on
> dependency information unless the views were created WITH SCHEMABINDING.
> The script below will generate a script to refresh all views in the curren
t
> database. You can wrap it in a cursor and execute according to your
> preference. BTW, even without 'SELECT *', you can run into issues with
> changed datatypes in the referenced tables.
> SELECT
> 'EXEC sp_refreshview ''' +
> QUOTENAME(TABLE_SCHEMA) +
> N'.' +
> QUOTENAME(TABLE_NAME) +
> ''''
> FROM INFORMATION_SCHEMA.TABLES
> WHERE TABLE_TYPE = 'VIEW'
> AND OBJECTPROPERTY(OBJECT_ID(
> QUOTENAME(TABLE_SCHEMA) +
> N'.' +
> QUOTENAME(TABLE_NAME)
> ), 'IsMSShipped') = 0
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Doug MacLean" <DougMacLean@.discussions.microsoft.com> wrote in message
> news:C701B020-919E-44B3-A61E-1FCF7AC6AA8D@.microsoft.com...
>
>
Enterprise Manager or with T-SQL statements in Query Analyzer, the
alterations are not automatically noticed by the views that use the table.
The only thing I have been able to figure out to do is to find each
potentially affected view and then:
1. Open it in design mode
2. Check or uncheck a field in the Altered table
3. Uncheck or check the same field to restore the view to its original field
selection
4. Close the view and Save it.
Two problems:
1. That's tedious and time-consuming
2. It is easy to miss an affected view.
Can someone advise me as to a better way? (I am mostly using SQL Server 2000
)
Thanks,
Doug MacLeanDoug MacLean wrote:
> When I alter a table to add columns or change their properties, either in
> Enterprise Manager or with T-SQL statements in Query Analyzer, the
> alterations are not automatically noticed by the views that use the table.
> The only thing I have been able to figure out to do is to find each
> potentially affected view and then:
> 1. Open it in design mode
> 2. Check or uncheck a field in the Altered table
> 3. Uncheck or check the same field to restore the view to its original fie
ld
> selection
> 4. Close the view and Save it.
> Two problems:
> 1. That's tedious and time-consuming
> 2. It is easy to miss an affected view.
> Can someone advise me as to a better way? (I am mostly using SQL Server 20
00)
> --
> Thanks,
> Doug MacLean
Firstly, do not use SELECT * in views. It's generally a bad idea to use
SELECT * anywhere in production code. It's a particularly bad idea in
views because of the way views handle changes to the columns.
So assuming you have named columns in all your views, use
sp_refreshview to ensure the view is up to date with changes to its
columns. If you want to add a column you'll have to edit the view
definition of course, which is good practice and not difficult if you
have adequate source control and change control procedures. Table
changes should never be executed by Enterprise Manager in a production
environment.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||David,
Thanks. That's very helpful. It eliminates the need for my 4-step process in
Enterprise Manager.
I wonder if there is a tool to list all of the views that depend on a
specified table, because the other challenge is actually finding all of them
when altering a table.
Best Regards,
--
Doug MacLean
"David Portas" wrote:
> Doug MacLean wrote:
> Firstly, do not use SELECT * in views. It's generally a bad idea to use
> SELECT * anywhere in production code. It's a particularly bad idea in
> views because of the way views handle changes to the columns.
> So assuming you have named columns in all your views, use
> sp_refreshview to ensure the view is up to date with changes to its
> columns. If you want to add a column you'll have to edit the view
> definition of course, which is good practice and not difficult if you
> have adequate source control and change control procedures. Table
> changes should never be executed by Enterprise Manager in a production
> environment.
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>|||Greetings,
"Doug MacLean" <DougMacLean@.discussions.microsoft.com> wrote in message
news:C701B020-919E-44B3-A61E-1FCF7AC6AA8D@.microsoft.com...
> David,
> Thanks. That's very helpful. It eliminates the need for my 4-step process
> in
> Enterprise Manager.
> I wonder if there is a tool to list all of the views that depend on a
> specified table, because the other challenge is actually finding all of
> them
> when altering a table.
> Best Regards,
> --
> Doug MacLean
Create the views WITH SCHEMABINDING and you won't be able to change the
table without first dropping the view. This is a good reminder to fix the
view ;-)
Regards,
Neale NOON|||You may want to use this.
select * from INFORMATION_SCHEMA.VIEW_TABLE_USAGE
where table_name = '<table_name>'
-Omnibuzz (The SQL GC)
http://omnibuzz-sql.blogspot.com/|||> I wonder if there is a tool to list all of the views that depend on a
> specified table, because the other challenge is actually finding all of
> them
> when altering a table.
You might find it easier to refresh all views since you cannot rely on
dependency information unless the views were created WITH SCHEMABINDING.
The script below will generate a script to refresh all views in the current
database. You can wrap it in a cursor and execute according to your
preference. BTW, even without 'SELECT *', you can run into issues with
changed datatypes in the referenced tables.
SELECT
'EXEC sp_refreshview ''' +
QUOTENAME(TABLE_SCHEMA) +
N'.' +
QUOTENAME(TABLE_NAME) +
''''
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'VIEW'
AND OBJECTPROPERTY(OBJECT_ID(
QUOTENAME(TABLE_SCHEMA) +
N'.' +
QUOTENAME(TABLE_NAME)
), 'IsMSShipped') = 0
Hope this helps.
Dan Guzman
SQL Server MVP
"Doug MacLean" <DougMacLean@.discussions.microsoft.com> wrote in message
news:C701B020-919E-44B3-A61E-1FCF7AC6AA8D@.microsoft.com...
> David,
> Thanks. That's very helpful. It eliminates the need for my 4-step process
> in
> Enterprise Manager.
> I wonder if there is a tool to list all of the views that depend on a
> specified table, because the other challenge is actually finding all of
> them
> when altering a table.
> Best Regards,
> --
> Doug MacLean
>
> "David Portas" wrote:
>|||Thanks, Neale.
That's an interesting idea to know when I change a table that has one or
more dependent views. So it is a good reminder.
But I KNOW there are views. And what I want is a clean way of finding and
refreshing them. Adding a process of dropping (and then re-adding) them seem
s
tedious.
Thanks Best Regards,
--
Doug MacLean
"Neale NOON" wrote:
> Greetings,
> "Doug MacLean" <DougMacLean@.discussions.microsoft.com> wrote in message
> news:C701B020-919E-44B3-A61E-1FCF7AC6AA8D@.microsoft.com...
>
> Create the views WITH SCHEMABINDING and you won't be able to change the
> table without first dropping the view. This is a good reminder to fix the
> view ;-)
> --
> Regards,
> Neale NOON
>
>|||Dan,
Ahhh!. That's a great idea. Thanks for both the idea and the sample code.
And it's practical because I only do such updates on the production system a
t
times when no one is using it.
You're right. I've already seen the problem that -- even without adding
columns -- the view needs to be refreshed.
Thanks much and Best regards,
--
Doug MacLean
"Dan Guzman" wrote:
> You might find it easier to refresh all views since you cannot rely on
> dependency information unless the views were created WITH SCHEMABINDING.
> The script below will generate a script to refresh all views in the curren
t
> database. You can wrap it in a cursor and execute according to your
> preference. BTW, even without 'SELECT *', you can run into issues with
> changed datatypes in the referenced tables.
> SELECT
> 'EXEC sp_refreshview ''' +
> QUOTENAME(TABLE_SCHEMA) +
> N'.' +
> QUOTENAME(TABLE_NAME) +
> ''''
> FROM INFORMATION_SCHEMA.TABLES
> WHERE TABLE_TYPE = 'VIEW'
> AND OBJECTPROPERTY(OBJECT_ID(
> QUOTENAME(TABLE_SCHEMA) +
> N'.' +
> QUOTENAME(TABLE_NAME)
> ), 'IsMSShipped') = 0
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Doug MacLean" <DougMacLean@.discussions.microsoft.com> wrote in message
> news:C701B020-919E-44B3-A61E-1FCF7AC6AA8D@.microsoft.com...
>
>
Prompt for variable values in a SQL script
I am writing a SQL script and it needs some parameters to be entered by the user. Users will run this script from the SQL Query Analyzer. I trying to see if there is a way the script will prompt the user to enter values for the parameters. This is possible in Oracle. Is there any equivalent in SQL Server?
Thanks in advance for your timeNo, you can't prompt for variables in the SQL script.
You can create your script as a stored procedure with parameters, or you can define and set your variables at the top of the script so the user can easily modify them (good programming practice anyway!).
That said...
bad programming practice is letting users run scripts from Query Analyzer! I hope the "users" have knowledge of databases, and I hope their server and database permissions are well defined, or they could inadvertently (or even maliciously) mess things up.
Why not build a simple interface, such as an Access Data Project, that calls the procedure after prompting the User for parameters?
blindman|||I realize that it is a bad and dangerous practice to let the users access the database from the query analyzer. We are redesigning the system from the scratch but till then, we have to support the existing system. Prevoius DBA let this hole into the system and we have to live with this till we finish our re-design. I was almost positive what I was looking for is not possible but I just wanted to make sure. Thanks a lot for your time blindman.
Originally posted by blindman
No, you can't prompt for variables in the SQL script.
You can create your script as a stored procedure with parameters, or you can define and set your variables at the top of the script so the user can easily modify them (good programming practice anyway!).
That said...
bad programming practice is letting users run scripts from Query Analyzer! I hope the "users" have knowledge of databases, and I hope their server and database permissions are well defined, or they could inadvertently (or even maliciously) mess things up.
Why not build a simple interface, such as an Access Data Project, that calls the procedure after prompting the User for parameters?
blindman
Thanks in advance for your timeNo, you can't prompt for variables in the SQL script.
You can create your script as a stored procedure with parameters, or you can define and set your variables at the top of the script so the user can easily modify them (good programming practice anyway!).
That said...
bad programming practice is letting users run scripts from Query Analyzer! I hope the "users" have knowledge of databases, and I hope their server and database permissions are well defined, or they could inadvertently (or even maliciously) mess things up.
Why not build a simple interface, such as an Access Data Project, that calls the procedure after prompting the User for parameters?
blindman|||I realize that it is a bad and dangerous practice to let the users access the database from the query analyzer. We are redesigning the system from the scratch but till then, we have to support the existing system. Prevoius DBA let this hole into the system and we have to live with this till we finish our re-design. I was almost positive what I was looking for is not possible but I just wanted to make sure. Thanks a lot for your time blindman.
Originally posted by blindman
No, you can't prompt for variables in the SQL script.
You can create your script as a stored procedure with parameters, or you can define and set your variables at the top of the script so the user can easily modify them (good programming practice anyway!).
That said...
bad programming practice is letting users run scripts from Query Analyzer! I hope the "users" have knowledge of databases, and I hope their server and database permissions are well defined, or they could inadvertently (or even maliciously) mess things up.
Why not build a simple interface, such as an Access Data Project, that calls the procedure after prompting the User for parameters?
blindman
Prompt for value
What is the sql code to prompt for a value?
Can I do this in query analyzer or do I have to send the value from some other application?the query analyser cannot prompt the user for a value.|||Prompting for values is the responsibility of the application interface. Query Analyzer is a tool, but it is by no means an application interface.
Can I do this in query analyzer or do I have to send the value from some other application?the query analyser cannot prompt the user for a value.|||Prompting for values is the responsibility of the application interface. Query Analyzer is a tool, but it is by no means an application interface.
Monday, March 12, 2012
Programming databases
I need to create a database with one table and its column names.
I got this script from the query analyzer templates as a database basic template:
IF EXISTS (SELECT *
FROM master..sysdatabases
WHERE name = N'<database_name, sysname, test_db>')
DROP DATABASE <database_name, sysname, test_db>
GO
I got this script from the query analyzer templates as a database basic template:
IF EXISTS (SELECT *
FROM master..sysdatabases
WHERE name = N'<database_name, sysname, test_db>')
DROP DATABASE <database_name, sysname, test_db>
GO
CREATE DATABASE <database_name, sysname, test_db>
GO
I am not sure what it all means, what I need to do it name the database a certain name each time. and then create one table with 6 column names.
can someone give me guidance please...the script is just trying to make sure there is no other db with that name and drop the db one if there is one..
hth|||Not really, heres what I got...
Declare @.name Char(10)
Set @.name = 'test'
CREATE DATABASE [@.name] ON (NAME = N'' & @.name & '_Data', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL\data\' & @.name & '_Data.MDF' , SIZE = 2, FILEGROWTH = 10%) LOG ON (NAME = N'' & @.name & '_Log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL\data\' & @.name & '_Log.LDF' , SIZE = 1, FILEGROWTH = 10%)
COLLATE SQL_Latin1_General_CP1_CI_AS
GO
exec sp_dboption N'@.name', N'autoclose', N'false'
GOexec sp_dboption N'@.name', N'bulkcopy', N'false'
GO
and so on...
Getting the variable '@.name' to go where I want it is where my problem occurs|||its more like
CREATE DATABASE+ @.name+ ON (NAME = N'' ......
hth|||exec sp_dboption N'hol', N'read only', N'false'
GO
What I need to do is pass, from the web page, a variable called @.name
So where it shows N'hol', the variable passed from the web form textbox whould go where 'hol' is.
N'' + @.name + '', does not work. what is the proper sytax for this?
E|||I suspect you don't understand what the 'N' bit means? (sorry if I'm wrong) You could split the batch up and use proper params. Although I'm a tad concerned that a web users is allowed to issue such commands, but hey I'm sure you've taken care of the security aspect.|||absolutely, what I am trying to do is automate as much as possible. this will be a hidden command in which the user can maintain an image database just by naming it where the form pulls the name from a textbox. Administration only should have access to this sort of command, so its all good with security.
SO tell me more about this spliting the batch up and the use of proper param names you speak of.. No, I am not sure of what the 'N' bit means either. Can you help?|||Params are good because you can literally use
exec spbla @.myParam1, @.myParam2
and you won't have to worry about all the horrible string concat problems.
Lookup SQLParameter.
Plus if your command type is stored procedure you can call spblaa directly!
I think you'll have to split up the batch cause normally client exec'd code doesn't like the "GO"s, although I've not actually tried it with the ADO.net.
I'll whisper the following, you don't really need the 'N' bit. Its just saying that the text is going to be unicode. You rarely have to worry about that, certainly not for params (i.e you don't prefix a param with 'N'). I didn't say that ok ;)
Subscribe to:
Posts (Atom)