Changing the DataSource

After changing the data soource for a working report, the Fields become
"unlinked"; what is the correct way to change a data source or relink fields
after the change'There is a refresh button to the right of the ... in the dataset tab. That
should do it for you.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Espo" <Espo@.discussions.microsoft.com> wrote in message
news:FE7D0C8B-9D7F-430E-9A37-10D4A73FBFA3@.microsoft.com...
> After changing the data soource for a working report, the Fields become
> "unlinked"; what is the correct way to change a data source or relink
> fields
> after the change'
>

Changing record number

Hi,
I have cancelled some records due to null fields,then the record number is not coming in order.how to code it to get it in orderwow, that's a short description.........
So I'm gonna have to guess the detail. You are probably, from the brief description, experiencing a problem because your report is having trouble with the nulls. More information, including database type, OS, CR version always helps us better assess the problem.
I think you need a formula to convert fields likely to be nulls, eg:
@.AccountBalance

if IsNull({tablename.AccountBalance}) then
formula = 0
else
formula = {tablename.AccountBalance}
end if

I've deliberatley kept this simple to demonstrate the principles.

Dave

changing one field updates 3 other fields, use a join?

i currently have a table like this..

user_id username app_id app2_id app3_id app4_id
1 john 3 4 5 6
2 mike 4 5 6 6
3 manager 4 5 6 6
4 vicepres 5 6 6 6
5 ceo 6 6 6 6
6 board 6 6 6 6

the basic pattern is... a user has approvers, and those approvers have approvers as well... i have 4 columns of approvers.. and if my first approver is the manager, then my second approver will be the managers approver and my third approver will be the managers approver's approver and so on..

on my actual page, i have select fields for the app, app2, app3, app4 and i need it so that when i change the very first app, it'll automatically update app2, app3, and app4

any ideas how i would do this? im pretty new to sql but im thinking i would use some type of join?

thanksLook into triggers. You'd make an UPDATE trigger that executes automatically when an update occurs on the table. You can then check to see if the update is to app_id, and make changes to the other fields accordingly.

There are two types of triggers, INSTEAD OF and AFTER. You need to determine if you want to replace the original UPDATE with some SQL you want the trigger to execute, or if you want the trigger to execute after the original UPDATE occurrs.|||Look into triggers. You'd make an UPDATE trigger that executes automatically when an update occurs on the table. You can then check to see if the update is to app_id, and make changes to the other fields accordingly.

There are two types of triggers, INSTEAD OF and AFTER. You need to determine if you want to replace the original UPDATE with some SQL you want the trigger to execute, or if you want the trigger to execute after the original UPDATE occurrs.

im pretty new to sql, im reading up on triggers and examples of them right now.. they show some code for the triggers.. would that whole code be the entire SQL query or would i code that in perl ( what im using that to access the SQL database)?

thanks|||You actually store triggers at the table level of the DB. In Enterprise Manager, drill down into your DB, and down to your table. Then right click on the table you wish to apply a trigger to and select "All Tasks" then "Manage Triggers".|||You actually store triggers at the table level of the DB. In Enterprise Manager, drill down into your DB, and down to your table. Then right click on the table you wish to apply a trigger to and select "All Tasks" then "Manage Triggers".
ahh ok, found it, thanks|||ok i got it working... so now, if i update an approver for a specific user, the trigger will run and go ahead and update the app2, app3, and app4 so it does what it's suppose to.

so i still have my original table as above, but now i added this trigger to the table:

CREATE TRIGGER approvers ON users FOR UPDATE AS
IF UPDATE(approver_id)
BEGIN
DECLARE @.thisUser VARCHAR(10)
DECLARE @.newApp VARCHAR(10)
DECLARE @.newApp2 VARCHAR(10)
DECLARE @.newApp3 VARCHAR(10)
DECLARE @.newApp4 VARCHAR(10)

SELECT @.thisUser = (SELECT user_id FROM Inserted )
SELECT @.newApp = (SELECT approver_id FROM Inserted)
SELECT @.newApp2 = (SELECT approver_id from users WHERE user_id = @.newApp)
SELECT @.newApp3 = (SELECT approver_id from users WHERE user_id = @.newApp2)
SELECT @.newApp4 = (SELECT approver_id from users WHERE user_id = @.newApp3)

UPDATE users SET app2_id = @.newApp2 WHERE user_id = @.thisUser
UPDATE users SET app3_id = @.newApp3 WHERE user_id = @.thisUser
UPDATE users SET app4_id = @.newApp4 WHERE user_id = @.thisUser
END

but i just thought of a problem, say i update the approver of an approver, i.e. i update app_id of manager and change it from vice pres to the ceo, so i change app_id for the manager from 4 to 5.. then all the rows that had the manager as an app, app_2, app_3, or app_4 would need to be updated to since the managers approve is no longer the same person, the other peoples app2, wouldnt be the same and therefore their app3, and app4 wont be the same either .. i hope someone understand what im saying... how should i go about fixing this? like if i change the approver of manager, would i have to return all the rows that had manager as an app, app_2, app_3, or app_4 and then update all of those rows somehow? like looping through each of the rows returned?

im really new to sql, i hope someone can guide me through this

thanks|||Look into "Cascading Triggers". That may help you out.|||got it working now, thanks :)

Changing Number to Text in Query

I have two fields that have either a 0 or 1 in them. When I do a SELECT to
bring the data back, I would like to change the 0 to an "N" and a 1 to a
"Y".
I read up on the CASE statement and had something like but kept getting
syntax errors.
Select Name, Address, City,
CASE field_in_question
WHEN 0 THEN 'N'
WHEN 1 THEN 'Y'
END,
State, Zip from Notes
Can someone help a feebled old brain figure this one out?
Thanks,
Gary>> I read up on the CASE statement and had something like but kept getting
Can you post the error messages here?
Anith|||The error message says "Syntax error (missing operator) in query expression.
"Anith Sen" <anith@.bizdatasolutions.com> wrote in message
news:OCdGspmAFHA.3236@.TK2MSFTNGP15.phx.gbl...
> Can you post the error messages here?
> --
> Anith
>|||Since your original post has no attributions, let us start from simple
issues. Are you use SQL Server or Access? CASE is not supported in Access.
If you are using SQL Server, are you using Query Analyzer? Some other
interfaces have limitations on certain constructs they allow
If you are using Query Analyzer, can you post your CREATE TABLE statement
for the table Notes? For details refer to www.aspfaq.com/5006
Anith|||That's the problem. I am using Access with a Visual Basic front end.
Is there a way to do what I need to do in Access SQL?
Thanks,
Gary
"Anith Sen" <anith@.bizdatasolutions.com> wrote in message
news:eS5ywFnAFHA.3416@.TK2MSFTNGP09.phx.gbl...
> Since your original post has no attributions, let us start from simple
> issues. Are you use SQL Server or Access? CASE is not supported in Access.
> If you are using SQL Server, are you using Query Analyzer? Some other
> interfaces have limitations on certain constructs they allow
> If you are using Query Analyzer, can you post your CREATE TABLE statement
> for the table Notes? For details refer to www.aspfaq.com/5006
> --
> Anith
>|||Create a stored procedure or a view, and select from that within Access
instead of accessing the table directly.
http://www.aspfaq.com/
(Reverse address to reply.)
"Gary Paris" <garyparis@.yada.com> wrote in message
news:ePQKtTnAFHA.2712@.TK2MSFTNGP15.phx.gbl...
> That's the problem. I am using Access with a Visual Basic front end.
> Is there a way to do what I need to do in Access SQL?
> Thanks,
> Gary
> "Anith Sen" <anith@.bizdatasolutions.com> wrote in message
> news:eS5ywFnAFHA.3416@.TK2MSFTNGP09.phx.gbl...
Access.
statement
>|||Oh, and don't use the view designer, because it doesn't support case! Use
Query Analyzer...
http://www.aspfaq.com/
(Reverse address to reply.)
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:uNFbUYnAFHA.2196@.TK2MSFTNGP14.phx.gbl...
> Create a stored procedure or a view, and select from that within Access
> instead of accessing the table directly.
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "Gary Paris" <garyparis@.yada.com> wrote in message
> news:ePQKtTnAFHA.2712@.TK2MSFTNGP15.phx.gbl...
> Access.
> statement
>|||I am using Access as the back end and not using SQL server. I don't believe
Query Analyzer comes with Access (although I could be wrong). Would it be
better to post my question in the Access group or is there a way to do what
I want with Access SQL?
Thanks,
Gary
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:e4cZzanAFHA.3264@.TK2MSFTNGP12.phx.gbl...
> Oh, and don't use the view designer, because it doesn't support case! Use
> Query Analyzer...
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
> news:uNFbUYnAFHA.2196@.TK2MSFTNGP14.phx.gbl...
>|||Sorry, I thought you were using Access to connect to SQL Server. Yes, you
can do this in Access using the IIF function. See the Access documentation
or post to an Access newsgroup. This group is dedicated to programming
in/for SQL Server.
"Gary Paris" <garyparis@.yada.com> wrote in message
news:O3$5VinAFHA.3988@.TK2MSFTNGP11.phx.gbl...
> I am using Access as the back end and not using SQL server. I don't
believe
> Query Analyzer comes with Access (although I could be wrong). Would it
be
> better to post my question in the Access group or is there a way to do
what
> I want with Access SQL?
> Thanks,
> Gary
> "Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
> news:e4cZzanAFHA.3264@.TK2MSFTNGP12.phx.gbl...
Use
other
>

Changing field props in SQL Management Studio

Hi,
I have average database with something like 15M records. I have to change
some fields property of tables with average 4M records. On smaller tables I
can change one field at time and save design would be successful but on
larger tables I got timeout something like 25 seconds after pressing OK.
Where I can change timeout period or disable it?
Tried to put 0 on Connection window, changed to 0 remote query timeout, but
it does not change anything.
Thanks
RobertI strongly encourage you to do these changes using ALTER TABLE instead of the GUI since the GUI can
be quite clumsy regarding the SQL it generates.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Piasecznik" <WYpiasecznikWAL@.op.pl> wrote in message news:frdfec$1gmi$1@.news2.ipartners.pl...
> Hi,
> I have average database with something like 15M records. I have to change some fields property of
> tables with average 4M records. On smaller tables I can change one field at time and save design
> would be successful but on larger tables I got timeout something like 25 seconds after pressing
> OK.
> Where I can change timeout period or disable it?
> Tried to put 0 on Connection window, changed to 0 remote query timeout, but it does not change
> anything.
> Thanks
> Robert
>

Changing field length whilst replicating

Hi
I have two databases that are merged using replication, and I want to change the length of one of the fields. Can anyone think of a way of doing it that doesn't require dropping the whole publication and rebuilding it? Thanks EdYou don't have to drop the entire publication. You have two options, one is to drop the subscription and the article to change the DDL and add the subscription then intialize it. The other is to use sp_repldropcolumn and sp_repladdcolumn to drop the old column and add it back with the length you want without dropping the subscription and the article. Of course, you need to save the data somewhere to add it backup in when the new column got recreated.|||Brilliant, Thanks Ed

Changing data type to the fields of my tables.

Hello.

i have a database with 300 tables. All the data types of the fields of my tables are custom. Ex. IFGint:int and stuff like that.

I want to know how can i through a stored procedure, change in batch mode all the fields of my tables. I don't want to modify by hand everytable. It's a lot of work and i think that maybe there might be a way for this programatically.

thanks in advance

You can use SQL queries to generate the DDL statements that you need, something like this (use the other columns in the columns schema table to generate other things into the ALTER TABLE statements)

select 'alter table [' + table_schema + '].[' + table_name + '] alter column [' + column_name + '] int'
+ CASE WHEN upper(IS_NULLABLE) = 'YES' THEN ' NULL' ELSE ' NOT NULL' END
from information_schema.columns
where data_type = 'tinyint'
and table_name in (select table_name from information_schema.tables where table_type = 'BASE TABLE')

Make sure you backup before you start running these scripts and test them all!

|||That looks pretty good. I was curious what this would look like with the new 2005 syntax using the DMVs. Here is what I came up with... Please note: This needs strenuous testing and a backup like the previous post suggested!!

USE tempdb
GO
IF EXISTS(SELECT * FROM sys.types where name = 'SSN')
DROP TYPE SSN
GO
CREATE TYPE SSN
FROM varchar(11) NOT NULL ;

GO
IF OBJECT_ID('TestSSN') IS NOT NULL
DROP TABLE TestSSN
GO
CREATE TABLE TestSSN ( MySSn SSN)
GO

-- Show the tables with the UDF fields...
SELECT s.name SchemaName, t.name TableName, sc.name ColumnName
FROM sys.schemas s
JOIN sys.tables t
ON s.schema_id = t.schema_id
JOIN sys.columns sc
ON t.object_id = sc.object_id
JOIN sys.types st
ON sc.user_type_id = st.user_type_id
WHERE st.name = 'SSN'
GO

DECLARE @.ErrMsg varchar(255),
@.ErrNum int,
@.SQL nvarchar(max),
@.TableName sysname,
@.ColumnName sysname,
@.SchemaName sysname

DECLARE @.c CURSOR

-- create a cursor to loop through all the tables
SET @.c = CURSOR FOR
SELECT s.name SchemaName, t.name TableName, sc.name ColumnName
FROM sys.schemas s
JOIN sys.tables t
ON s.schema_id = t.schema_id
JOIN sys.columns sc
ON t.object_id = sc.object_id
JOIN sys.types st
ON sc.user_type_id = st.user_type_id
WHERE st.name = 'SSN'
FOR READ ONLY

OPEN @.c
FETCH @.c INTO @.SchemaName, @.TableName, @.ColumnName
WHILE @.@.FETCH_STATUS = 0
BEGIN -- loop
SET @.SQL = 'ALTER TABLE ' + @.SchemaName + '.' + @.TableName + ' ALTER COLUMN ' + @.ColumnName + ' varchar(32) NOT NULL'
PRINT 'Executing: ' + @.SQL
EXEC sp_executesql @.SQL

SELECT @.ErrNum = @.@.Error--, @.RowCnt = @.@.ROWCOUNT
IF (@.ErrNum <> 0)
BEGIN
RAISERROR('Error updating %s.%s table to have %s a varchar(32). Error Numer: %d', 16, 1, @.SchemaName, @.TableName, @.ColumnName, @.ErrNum)
GOTO ErrorHandler
END

FETCH @.c INTO @.SchemaName, @.TableName, @.ColumnName
END -- loop
CLOSE @.c
DEALLOCATE @.c

GOTO ExitScript

ErrorHandler:
RAISERROR (@.ErrMsg, 16, 127)
GOTO ExitScript

ExitScript:
GO

-- See if we have any tables with that UDF anymore...
SELECT s.name SchemaName, t.name TableName, sc.name ColumnName
FROM sys.schemas s
JOIN sys.tables t
ON s.schema_id = t.schema_id
JOIN sys.columns sc
ON t.object_id = sc.object_id
JOIN sys.types st
ON sc.user_type_id = st.user_type_id
WHERE st.name = 'SSN'

Changing Data Sources

If I have a dataset that uses a certain data source, if I
change the data source do I have to redefine my dataset
and replace the fields from the dataset in my designer...?
DanNope . I never had to do that
"alien251" wrote:
> If I have a dataset that uses a certain data source, if I
> change the data source do I have to redefine my dataset
> and replace the fields from the dataset in my designer...?
> Dan
>