Check all SPs from a script

It's known that the SQL Server doesn't check the SPs and views when we
modify the tables and if we try to use these old SPs after we applied any
modifications like column names to the tables we'll get very serious
problems.
Is it possible to check all SPs and View from one script, like open each
one, check if it's correct (maybe try to save?) and if it's a problem then
save the name of this wrong SP to some table or just provide a list with
these names?
Dmitri.Oh, thats a good one, the only thing that come in mind would be to figure
out all Procedures (in all sysobjects) and to identify for every single
procedure the parameters in syscolumns. Then you have to create a
SQLStatement with the paramters and some dummy values within an automatic
Rollback transaction and query after execution for the @.@.error to identify
wheter it was executed valid or not.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Just D." <no@.spam.please> schrieb im Newsbeitrag
news:zwthe.10252$D91.4971@.fed1read01...
> It's known that the SQL Server doesn't check the SPs and views when we
> modify the tables and if we try to use these old SPs after we applied any
> modifications like column names to the tables we'll get very serious
> problems.
> Is it possible to check all SPs and View from one script, like open each
> one, check if it's correct (maybe try to save?) and if it's a problem then
> save the name of this wrong SP to some table or just provide a list with
> these names?
> Dmitri.
>|||Hi
That is by design, it is called Deferred Name Resolution.
You can overcome the problem in the views with using WITH SCHEMABINDING when
creating the view.
For validation, here is some code that checks if the objects referenced
exist.
--Credit to Dan Guzman, SQL Server MVP:
SELECT
N'SET FMTONLY ON EXEC ' +
QUOTENAME(ROUTINE_SCHEMA) +
N'.' +
QUOTENAME(ROUTINE_NAME) AS ProcedureName,
REPLICATE(N'NULL,',
ISNULL(
(SELECT COUNT(*) AS Parameters
FROM INFORMATION_SCHEMA.PARAMETERS p
WHERE p.SPECIFIC_SCHEMA = r.ROUTINE_SCHEMA AND
p.SPECIFIC_NAME = r.ROUTINE_NAME), 0)
) AS Parameters
INTO #FmtOnlyExecutes
FROM INFORMATION_SCHEMA.ROUTINES r
WHERE r.ROUTINE_TYPE = 'PROCEDURE' AND
OBJECTPROPERTY(OBJECT_ID(QUOTENAME(ROUTI
NE_SCHEMA) +
N'.' +
QUOTENAME(ROUTINE_NAME)), 'IsMSShipped') = 0
--remove trailing comma from parameter list
UPDATE #FmtOnlyExecutes
SET Parameters = LEFT(Parameters, LEN(Parameters) - 1)
WHERE RIGHT(Parameters, 1) = N','
--execute procs
DECLARE @.ExecuteStatement nvarchar(4000)
DECLARE ExecuteStatements CURSOR
LOCAL FAST_FORWARD READ_ONLY FOR
SELECT RTRIM(ProcedureName) +
N' ' +
Parameters
FROM #FmtOnlyExecutes
ORDER BY ProcedureName
OPEN ExecuteStatements
WHILE 1 = 1
BEGIN
FETCH NEXT FROM ExecuteStatements INTO @.ExecuteStatement
IF @.@.FETCH_STATUS = -1 BREAK
RAISERROR (@.ExecuteStatement, 0, 1) WITH NOWAIT
EXEC(@.ExecuteStatement)
END
CLOSE ExecuteStatements
DEALLOCATE ExecuteStatements
DROP TABLE #FmtOnlyExecutes
GO
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Just D." <no@.spam.please> wrote in message
news:zwthe.10252$D91.4971@.fed1read01...
> It's known that the SQL Server doesn't check the SPs and views when we
> modify the tables and if we try to use these old SPs after we applied any
> modifications like column names to the tables we'll get very serious
> problems.
> Is it possible to check all SPs and View from one script, like open each
> one, check if it's correct (maybe try to save?) and if it's a problem then
> save the name of this wrong SP to some table or just provide a list with
> these names?
> Dmitri.
>|||Jens,
My idea was to open the SP for modification but actually do not do anything
bad, but to let the Sql Server know that it was modified we can add a space
in the very end for example and then try to save it. If it fails then
report/add a name to some table, that this SP is wrong. That's easier that
you're suggesting.
I'm just wondering if anybody is already having this script?
Dmitri.
"Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote in
message news:OdeAyxMWFHA.3636@.TK2MSFTNGP14.phx.gbl...
> Oh, thats a good one, the only thing that come in mind would be to figure
> out all Procedures (in all sysobjects) and to identify for every single
> procedure the parameters in syscolumns. Then you have to create a
> SQLStatement with the paramters and some dummy values within an automatic
> Rollback transaction and query after execution for the @.@.error to identify
> wheter it was executed valid or not.
>
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
>
> "Just D." <no@.spam.please> schrieb im Newsbeitrag
> news:zwthe.10252$D91.4971@.fed1read01...
>

chart x-axis y-axis

I'm trying to convert an old chart to Reporting Services.
We have used the same dataset for years, it is very very simple. But it seems to me that Reporting Services can't deal with this simple dataset.

My question is: How can I get create a line chart using this dataset.

The data set consists of 1 row with 96 columns (a day divided into 96 sets of 15 minutes). Each column records how many events occurs within the 15 minutes. Along the x axis I would like to display a vertical line every 4 hours. The y axis should be numbered 0 to the highest value in the results.

I could not make this dataset any more simple than it is. But Reporting Services just chokes on this.

Here is a sample of the dataset :
1 2 0 4 5 6 7 8 9 10... 96It looks as thought what I may need is to reset the dataset to look like this:
time events
00-14 1
15-29 2
30-44 0
...

The only way I could do that with the existing tables would be 96 unions.

96 Unions!
Microsoft I must say that I am not so impressed with reporting services. Dragging fields to a myesterous chart "category" or "series" was dumb to begin with.

char vs varchar - reclaiming free space

One of our customers is using MSDE2000 and has reached 2 gb limit. After
erasing some old data and shrinking database he still had 1938 mb
allocated. After that, I have noticed that some of the larger tables
(around 1,5 - 2 million rows spread across a few tables) had column
defined as char(256) and char(1280), and a lot of fields were just space
filled or filled with around 40-100 chars only. I have changed those
columns to varchar(256) and varchar(1280) and rtrimmed those columns using:
alter table sometable alter column somefield varchar(256) not null
update sometable set somefield = rtrim(somefield)
and after another database shrink it seems that it hasn't reclaimed any
space - what's even worse - the database seems to have grown to 2100 mb.
I expected to gain at least 200 mb, but it didn't happen. Is there a way
to reclaim that space?
Tnx in advance
Dragan Matic
Hi
Check if ANSI_PADDING is ON or OFF.
From BOL:
When set to ON, trailing blanks in character values inserted into varchar
columns and trailing zeros in binary values inserted into varbinary columns
are not trimmed. Values are not padded to the length of the column. When set
to OFF, the trailing blanks (for varchar) and zeros (for varbinary) are
trimmed. This setting affects only the definition of new columns.
You LOG file may have also grown and it may require shrinking see
http://msdn.microsoft.com/library/de...r_da2_1uzr.asp
John
"DRagan Matic" wrote:

> One of our customers is using MSDE2000 and has reached 2 gb limit. After
> erasing some old data and shrinking database he still had 1938 mb
> allocated. After that, I have noticed that some of the larger tables
> (around 1,5 - 2 million rows spread across a few tables) had column
> defined as char(256) and char(1280), and a lot of fields were just space
> filled or filled with around 40-100 chars only. I have changed those
> columns to varchar(256) and varchar(1280) and rtrimmed those columns using:
> alter table sometable alter column somefield varchar(256) not null
> update sometable set somefield = rtrim(somefield)
> and after another database shrink it seems that it hasn't reclaimed any
> space - what's even worse - the database seems to have grown to 2100 mb.
> I expected to gain at least 200 mb, but it didn't happen. Is there a way
> to reclaim that space?
> Tnx in advance
> Dragan Matic
>
|||Also be sure to run DBCC UPDATEUSAGE(0)
Roy

char vs varchar - reclaiming free space

One of our customers is using MSDE2000 and has reached 2 gb limit. After
erasing some old data and shrinking database he still had 1938 mb
allocated. After that, I have noticed that some of the larger tables
(around 1,5 - 2 million rows spread across a few tables) had column
defined as char(256) and char(1280), and a lot of fields were just space
filled or filled with around 40-100 chars only. I have changed those
columns to varchar(256) and varchar(1280) and rtrimmed those columns using:
alter table sometable alter column somefield varchar(256) not null
update sometable set somefield = rtrim(somefield)
and after another database shrink it seems that it hasn't reclaimed any
space - what's even worse - the database seems to have grown to 2100 mb.
I expected to gain at least 200 mb, but it didn't happen. Is there a way
to reclaim that space?
Tnx in advance
Dragan MaticHi
Check if ANSI_PADDING is ON or OFF.
From BOL:
When set to ON, trailing blanks in character values inserted into varchar
columns and trailing zeros in binary values inserted into varbinary columns
are not trimmed. Values are not padded to the length of the column. When set
to OFF, the trailing blanks (for varchar) and zeros (for varbinary) are
trimmed. This setting affects only the definition of new columns.
You LOG file may have also grown and it may require shrinking see
http://msdn.microsoft.com/library/d...r />
_1uzr.asp
John
"DRagan Matic" wrote:

> One of our customers is using MSDE2000 and has reached 2 gb limit. After
> erasing some old data and shrinking database he still had 1938 mb
> allocated. After that, I have noticed that some of the larger tables
> (around 1,5 - 2 million rows spread across a few tables) had column
> defined as char(256) and char(1280), and a lot of fields were just space
> filled or filled with around 40-100 chars only. I have changed those
> columns to varchar(256) and varchar(1280) and rtrimmed those columns using
:
> alter table sometable alter column somefield varchar(256) not null
> update sometable set somefield = rtrim(somefield)
> and after another database shrink it seems that it hasn't reclaimed any
> space - what's even worse - the database seems to have grown to 2100 mb.
> I expected to gain at least 200 mb, but it didn't happen. Is there a way
> to reclaim that space?
> Tnx in advance
> Dragan Matic
>|||Also be sure to run DBCC UPDATEUSAGE(0)
Roy

char vs varchar - reclaiming free space

One of our customers is using MSDE2000 and has reached 2 gb limit. After
erasing some old data and shrinking database he still had 1938 mb
allocated. After that, I have noticed that some of the larger tables
(around 1,5 - 2 million rows spread across a few tables) had column
defined as char(256) and char(1280), and a lot of fields were just space
filled or filled with around 40-100 chars only. I have changed those
columns to varchar(256) and varchar(1280) and rtrimmed those columns using:
alter table sometable alter column somefield varchar(256) not null
update sometable set somefield = rtrim(somefield)
and after another database shrink it seems that it hasn't reclaimed any
space - what's even worse - the database seems to have grown to 2100 mb.
I expected to gain at least 200 mb, but it didn't happen. Is there a way
to reclaim that space?
Tnx in advance
Dragan MaticHi
Check if ANSI_PADDING is ON or OFF.
From BOL:
When set to ON, trailing blanks in character values inserted into varchar
columns and trailing zeros in binary values inserted into varbinary columns
are not trimmed. Values are not padded to the length of the column. When set
to OFF, the trailing blanks (for varchar) and zeros (for varbinary) are
trimmed. This setting affects only the definition of new columns.
You LOG file may have also grown and it may require shrinking see
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/architec/8_ar_da2_1uzr.asp
John
"DRagan Matic" wrote:
> One of our customers is using MSDE2000 and has reached 2 gb limit. After
> erasing some old data and shrinking database he still had 1938 mb
> allocated. After that, I have noticed that some of the larger tables
> (around 1,5 - 2 million rows spread across a few tables) had column
> defined as char(256) and char(1280), and a lot of fields were just space
> filled or filled with around 40-100 chars only. I have changed those
> columns to varchar(256) and varchar(1280) and rtrimmed those columns using:
> alter table sometable alter column somefield varchar(256) not null
> update sometable set somefield = rtrim(somefield)
> and after another database shrink it seems that it hasn't reclaimed any
> space - what's even worse - the database seems to have grown to 2100 mb.
> I expected to gain at least 200 mb, but it didn't happen. Is there a way
> to reclaim that space?
> Tnx in advance
> Dragan Matic
>|||Also be sure to run DBCC UPDATEUSAGE(0)
Roy

Changing Windows Domain

I have a SQL server that is in an old NT4 domain. We need to move it to a Windows 2000 domain. We tried changing the domain of the server but now the SQL services will not start. We set the sql service to use an account in the new domain and still get an access denied error when trying to start the service.

Any ideas?

Thanks,

JeffOriginally posted by jpoling
I have a SQL server that is in an old NT4 domain. We need to move it to a Windows 2000 domain. We tried changing the domain of the server but now the SQL services will not start. We set the sql service to use an account in the new domain and still get an access denied error when trying to start the service.

Any ideas?

Thanks,

Jeff

Did you change both the MSSQLServer and SQLServerAgent user ids?
Are you sure the former accounts were domain accounts and not local accounts?
Did you change the accounts in the EM?
Or did you do it in the Service Manager on the Control Panel?
Did the server name change as well?
Did the sever create an account in the new Domain?

Just throwing some of the more obvious places to look.|||I think I solved the problem. We changed the account the sql service runs as through control panel | services and not through EM. When we went back and changed it through EM first, it worked.

Thanks for the suggestions!

Jeff