Changing a Text To a Date

I have a field in a table that contains date data in the following format:
20040301
what is the best, low impact method for converting this to a useable date
field?
Sorry, wrong group...
"Atley" <atley_1@.homtmail.com> wrote in message
news:euhlpsnEEHA.3788@.TK2MSFTNGP10.phx.gbl...
> I have a field in a table that contains date data in the following
format:
> 20040301
> what is the best, low impact method for converting this to a useable date
> field?
>

Changing a SQL Statement as it's passed to the server.

Wondering if this is possible...

If have a program (a standalone .Exe) that passed the following into our SQL Server.

SELECT l.LabelType, l.PalletLabelID, l.DelSchedID, l.AsnID, d.BasicPartNo, d.LatestPartNo, d.Description, d.PartColour, d.ContainerCode, d.PackFactor FROM Label l INNER JOIN DeliverySchedule d ON l.DelSchedID = d.DelSchedID WHERE l.SupplierID = 1 AND l.WarehouseID = 1 AND l.LabelID = ?

The statement is always the same, the only thing that differs is the LabelID field, which can be any number between 000000 and 999999.

The problem is however is that the statement is wrong! (very wrong), and I was wondering if it was possible to do any of the following:

Create something which could be classed as a "Select Trigger" (that is, I can create a script to determine what values are returned back).

Create something that constantly looks for that sentance been passed and then run my script to return the correct data.

I'm at a loss of ideas on how to achieve what I'm after.

The .Exe can NOT be changed.

Would really appreciate some feedback.

Define "wrong". You could possibly use views to make the data look like the query, if some data is wrong. There are a few things you can do performancewise if it is performance issue.

I don't think there is anything that you can do to capture the statement and change it in stream (unless you wrote your own drivers and used them instead of the OleDb or ODBC drivers, which is probably harder than just rewriting your EXE from scratch.)

>>The .Exe can NOT be changed.<<

Is it that large of a program? Is it a third party thing?

Changing a record after comparing one database table to another

I'm using SQL Server 2005 Express.

In my main database table I have many fields but the two following fields are my main concern.

1) email_address

2) unsubscribe

In my secondary database table I have one record only.

1) email_address

What I want to accomplish... I want to compare the email_address of the secondary database table to the email_address of the main database table and if it exist, change the value of the unsubscribe field. (or if I can't do that, then delete the record within the main database table completely.)

I'd really appreciate any help I can get.

Thanks,

Bill

Something like this should work for you.

Code Snippet

UPDATE MyMainTable
SET UnSubscribe = {Put Your Value Here}

FROM MyMainTable m
JOIN MySecondaryTable s
ON m.eMail_Address = s.eMail_Address

If there is a match, [UnSubscribe] is changed, if there is NO match, nothing happens. This will work with multiple rows in the secondary table.

|||

I'll try this tonight. Thanks a lot Arnie.

Bill

Changes to order by in sql 2005

Hi, can anyone tell me why the following fails in sql 2005 with the error,
invalid column name 'title2'. This works in all other versions including 200
5
Express.
Select title as title1, title as title2 from title order by [title].title2
I can fix the query to run under 2005 by removing the field aliases, but I
can't find anything to explain why it failed. There are over 1000 queries in
my application, and although this is the only reported failure so far, if
there is a fundamental change regarding aliases or order by clauses, I need
to know so I can fix all affected queries.
ThanksPaul (Paul@.discussions.microsoft.com) writes:
> Hi, can anyone tell me why the following fails in sql 2005 with the
> error, invalid column name 'title2'. This works in all other versions
> including 2005 Express.
> Select title as title1, title as title2 from title order by [title].title2
If fails, because it's incorrect SQL. There is no title2 in title.
It was accepted in SQL 2000, yes. That was a bug.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Specifying a table alias in ORDER BY doesn't make sense, logically. From a l
ogical standpoint the
ORDER BY is performed as one of the last steps of the query (FROM, WHERE, GR
OUP BY, HAVING, SELECT
<col1, col2>, ORDER BY, TOP). Since ORDER BY is after the SELECT, we don't "
see" the base tables any
more, so it doesn't make sense to refer to them. In fact, at this stage, the
re exist only one table,
the table to be returned. But even SQL Server 2005 has some strangeness left
behind, even though it
is stricter than 2000:
--OK in 2000 and 2005
SELECT au_lname AS lname FROM authors AS a ORDER BY a.au_lname
--Fails in 2005, OK in 2000
SELECT au_lname AS lname FROM authors AS a ORDER BY a.lname
--Fails in 2000 and 2005
SELECT au_lname AS lname FROM authors AS a ORDER BY authors.au_lname
--Fails in 2005, OK in 2000
SELECT au_lname AS lname FROM authors AS a ORDER BY authors.lname
Anyhow, search for ORDER BY in below BOL URL (make sure you have the updates
BOL):
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/instsql9/html/65eaafa1-9e06-4264-b547-
cbee8013c995.htm
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:FD1F5D8D-F11B-432C-A081-B75036B890A2@.microsoft.com...
> Hi, can anyone tell me why the following fails in sql 2005 with the error,
> invalid column name 'title2'. This works in all other versions including 2
005
> Express.
> Select title as title1, title as title2 from title order by [title].title2
> I can fix the query to run under 2005 by removing the field aliases, but I
> can't find anything to explain why it failed. There are over 1000 queries
in
> my application, and although this is the only reported failure so far, if
> there is a fundamental change regarding aliases or order by clauses, I nee
d
> to know so I can fix all affected queries.
> Thanks
>|||Paul wrote:
> Hi, can anyone tell me why the following fails in sql 2005 with the error,
> invalid column name 'title2'. This works in all other versions including 2
005
> Express.
> Select title as title1, title as title2 from title order by [title].title2
> I can fix the query to run under 2005 by removing the field aliases, but I
> can't find anything to explain why it failed. There are over 1000 queries
in
> my application, and although this is the only reported failure so far, if
> there is a fundamental change regarding aliases or order by clauses, I nee
d
> to know so I can fix all affected queries.
> Thanks
You need to read this section in Books Online:
http://msdn2.microsoft.com/en-us/library/ms143532(SQL.90).aspx
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
--|||Thanks for the replies, at least I know what to check for in my other querie
s
now.
Thanks
"Paul" wrote:

> Hi, can anyone tell me why the following fails in sql 2005 with the error,
> invalid column name 'title2'. This works in all other versions including 2
005
> Express.
> Select title as title1, title as title2 from title order by [title].title2
> I can fix the query to run under 2005 by removing the field aliases, but I
> can't find anything to explain why it failed. There are over 1000 queries
in
> my application, and although this is the only reported failure so far, if
> there is a fundamental change regarding aliases or order by clauses, I nee
d
> to know so I can fix all affected queries.
> Thanks
>

Changes related to PDF in SQL Server 2005 Reporting Services

Hi All,

I am working on SQL Server 2005 reporting services. In reports I am required to make changes related to exporting report to PDF. Following are those changes:

1) Change PDF export page format to letter from legal

2) Fix page break to ensure that all details related to a specific issue are viewable on the same page

In reports exporting is done through the control given by services itself. So can any one please guide me regarding how this changes can be done. Please help me ASAP...

Thanks....

For (1), it seems like you should be able to just change the page size of the report. Or do you need to change the size just for PDF? If you are using URL access or SOAP, you can specify page sizes via device info settings. See http://msdn2.microsoft.com/en-us/library/ms154682.aspx

For (2), you will need to modify the RDL; try looking into the KeepTogether and RepeatWith settings on report items.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSRDL/htm/rsp_ref_rdl_elements_ae_4ak3.asp|||

With regard to page breaks I can see a KeepTogether property that refers to the whole report but not to individual groups. There is no RepeatWith setting within my rdl (SQL2005).

I would like my report to group on a particular field but don't want it to break before or after the report header / footer. Any idea how I can do this?

Changes not committed in SQL Server Project

I am brand new to vb.net and Visual Studio. I am following a series of lessons where I am supposed to be able to connect to a SQL Server Database bind the db to a form and then edit, add and delete records from within the form. When I run the project everything appears to work correctly, I can change the table data, add or delete rows but once I close the project and look at the underlying data, none of the changes are reflected. When I re-run the project, the data is just as it was before I made the changes.

I can edit the data directly in the tables and it works as you would expect. I'm sure that this is something bone-headed that I'm missing but I can't for the life of me find it.

Any guidance?

Thanks in advance for your help.

mpj

For .NET related questions, please see the Data Access forum in the .NET group.

Thank you.

|||

Will do...

Thanks

change tracking for SQL Server

Has anyone come across a change tracking tool for SQL Server. Specifically
the scenario I want is the following :

A Production DB needs some modifications to its content.
This tool will copy the DB to a dev environment.
The Dev environment will be 2 copies of the DB, 1 as a control set and the
other the change DB.
The developer makes their changes in the Change DB. They test them out and
then when they decide the changes are ready to pushto production they hit
the "go" button on the tool.
The tool calculates the delta between the Control and the Change DB and then
pushes the changes to produciton

Any ideas

Thanks

s"Steve Mew" <steve_mew@.hotmail.com> wrote in message news:<wWnib.8720$843.5779@.nwrddc03.gnilink.net>...
> Has anyone come across a change tracking tool for SQL Server. Specifically
> the scenario I want is the following :
> A Production DB needs some modifications to its content.
> This tool will copy the DB to a dev environment.
> The Dev environment will be 2 copies of the DB, 1 as a control set and the
> other the change DB.
> The developer makes their changes in the Change DB. They test them out and
> then when they decide the changes are ready to pushto production they hit
> the "go" button on the tool.
> The tool calculates the delta between the Control and the Change DB and then
> pushes the changes to produciton
> Any ideas
> Thanks
> s

I'm not aware of a single tool that would do exactly what you want,
however you could start by looking at the Red Gate SQL tools
(http://www.red-gate.com), which can compare two databases and
generate scripts to synchronize them. There are similar tools from
other vendors.

Copying the databases is easily automated (backup/restore, SQLDMO,
DTS), but you might want to consider the permissions involved. Many
sites do not allow developers to backup/restore databases or push code
directly to production (although to be fair, there are certainly many
that do).

Simon|||Steve Mew (steve_mew@.hotmail.com) writes:
> Has anyone come across a change tracking tool for SQL Server. Specifically
> the scenario I want is the following :
> A Production DB needs some modifications to its content.
> This tool will copy the DB to a dev environment.
> The Dev environment will be 2 copies of the DB, 1 as a control set and the
> other the change DB.
> The developer makes their changes in the Change DB. They test them out and
> then when they decide the changes are ready to pushto production they hit
> the "go" button on the tool.
> The tool calculates the delta between the Control and the Change DB and
> then pushes the changes to produciton

For the scenario you describe it sounds like SQL Compare from Red Gate
is the tool to use. However, I have never used this tool myself.

I cannot escape to take the occassion to point out that the best strategy
would be have all your source in a version control system, and then have
controlled procedures from building update scrtips from the version-
control system.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Change the table structure

Hi...

I execute select statement on three tables and i get the following table:

ID

Value

Name

1

10

color

2

20

color

3

30

color

4

40

color

and, from this table i want to create the following table:

ID

color

1

10

2

20

3

30

4

40

mean, the value color become the header title of the column.

How can i do that?

thank you...

But you use a GridView or what to show the table ?

If you use a GridView you can set the Visible attribute of a BoundField at runtime.

|||

Hi...

I am not using gridView, or any other control, i just need to do this in sql.

thanks.

|||

may215:

Hi...

I am not using gridView, or any other control, i just need to do this in sql.

thanks.

Are you going to insert values in the second table, or its value will be same as the first table but columns' names are diffrents?

If you are not going to insert values in the second table, then I suggest to create a view that get its data from the first table.

1CREATE VIEW MyViewForTheSecondTable23AS45SELECT [ID], [VALUE]AS'Color'67FROM MyFirstTable8910

Now, you can do the SELECT on the view like:

1SELECT *FROM MyViewForTheSecondTable

The returned result will be same as in the first table but columns name will be ID and Color insted of ID and Value.

Good luck.

|||

Hi...

When i try to create the view i am getting error: invalid column name ID, VALUE....in the following example: invalid column name PhonePropertyValue , PhonePropertyName.

here is the full example i trying to do:

SELECT [dbo.TblPropertyForPhoneType.PhonePropertyValue], [dbo.TblPhoneProperty.PhonePropertyName] AS
'color'

FROM dbo.TblPhoneType INNER JOIN
dbo.TblPropertyForPhoneType ON dbo.TblPhoneType.PhoneTypeID = dbo.TblPropertyForPhoneType.PhoneTypeID INNER JOIN
dbo.TblPhoneProperty ON dbo.TblPropertyForPhoneType.PhonePropertyID = dbo.TblPhoneProperty.PhonePropertyID
WHERE (dbo.TblPhoneProperty.PhonePropertyID = '34')

Why is that?

thanks...

|||

may215:

Hi...

When i try to create the view i am getting error: invalid column name ID, VALUE....i

I cheked the example I wrote .. it is working fine.

Here is a complete senario that worked on my machine and it should work on your machine as well:

1-- create sample database2create database MyTestDB3go45-- use the sample database6use MyTestDB7go89-- create new table for testing10CREATE TABLE MyTable11(12[ID]INT,13[VALUE]CHAR(10)14)15GO1617-- insert two records in the testing table18INSERT INTO MyTableSELECT 4,'red'19INSERT INTO MyTableSELECT 5,'black'202122-- create a view23CREATE VIEW MyViewForTheSecondTable2425AS2627 SELECT [ID], [VALUE]AS'COLOR'2829FROM MyTable303132-- select data from the created view33SELECT *FROM MyViewForTheSecondTable

Please let me know if it did not work with you for any reason.

may215:

in the following example: invalid column name PhonePropertyValue , PhonePropertyName.

here is the full example i trying to do:

SELECT [dbo.TblPropertyForPhoneType.PhonePropertyValue], [dbo.TblPhoneProperty.PhonePropertyName] AS
'color'

FROM dbo.TblPhoneType INNER JOIN
dbo.TblPropertyForPhoneType ON dbo.TblPhoneType.PhoneTypeID = dbo.TblPropertyForPhoneType.PhoneTypeID INNER JOIN
dbo.TblPhoneProperty ON dbo.TblPropertyForPhoneType.PhonePropertyID = dbo.TblPhoneProperty.PhonePropertyID
WHERE (dbo.TblPhoneProperty.PhonePropertyID = '34')

Why is that?

thanks...

First of all, please make sure those columns are exists in the table and they were typed correctly.

Then try this:

1SELECT prop.PhonePropertyValue, prop.PhonePropertyNameAS'color'23FROM dbo.TblPhoneType pt4INNERJOIN5 dbo.TblPropertyForPhoneType prop6ON pt.PhoneTypeID = prop.PhoneTypeID7INNERJOIN8 dbo.TblPhoneProperty pp9ON pp.PhonePropertyID = prop.PhonePropertyID10WHERE pp.PhonePropertyID ='34'11

Good luck.

Change servername

Hi,
I moved jobs from a server to a new one with dts. However I cannot delete,
edit, enable, ... these jobs. If I try so I get the following error:
14274 Cannot add, update, or delete a job (or its steps or schedules) that
originated from an MSX server.
We changed the name of the server after it was installed from 'EDIFRAME-' to
'EDIFRAME'.
If I do SELECT @.@.SERVERNAME the result is 'EDIFRAME-' (This is the old
temporary name)
How can I change the servername because I think this is the problem?
Thanks in advance for any help.
Jo Segers.http://www.karaszi.com/SQLServer/info_change_server_name.asp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Jo Segers" <segers_jo@.hotmail.com> wrote in message news:e$AuIrvpEHA.1712@.tk2msftngp13.phx.gbl...
> Hi,
> I moved jobs from a server to a new one with dts. However I cannot delete,
> edit, enable, ... these jobs. If I try so I get the following error:
> 14274 Cannot add, update, or delete a job (or its steps or schedules) that
> originated from an MSX server.
> We changed the name of the server after it was installed from 'EDIFRAME-' to
> 'EDIFRAME'.
> If I do SELECT @.@.SERVERNAME the result is 'EDIFRAME-' (This is the old
> temporary name)
> How can I change the servername because I think this is the problem?
> Thanks in advance for any help.
> Jo Segers.
>|||Thanks for the information, this solved my problem.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> schreef
in bericht news:eT7K37vpEHA.2900@.TK2MSFTNGP12.phx.gbl...
> http://www.karaszi.com/SQLServer/info_change_server_name.asp
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Jo Segers" <segers_jo@.hotmail.com> wrote in message
news:e$AuIrvpEHA.1712@.tk2msftngp13.phx.gbl...
> > Hi,
> >
> > I moved jobs from a server to a new one with dts. However I cannot
delete,
> > edit, enable, ... these jobs. If I try so I get the following error:
> >
> > 14274 Cannot add, update, or delete a job (or its steps or schedules)
that
> > originated from an MSX server.
> >
> > We changed the name of the server after it was installed from
'EDIFRAME-' to
> > 'EDIFRAME'.
> >
> > If I do SELECT @.@.SERVERNAME the result is 'EDIFRAME-' (This is the old
> > temporary name)
> >
> > How can I change the servername because I think this is the problem?
> >
> > Thanks in advance for any help.
> >
> > Jo Segers.
> >
> >
>

Change servername

Hi,
I moved jobs from a server to a new one with dts. However I cannot delete,
edit, enable, ... these jobs. If I try so I get the following error:
14274 Cannot add, update, or delete a job (or its steps or schedules) that
originated from an MSX server.
We changed the name of the server after it was installed from 'EDIFRAME-' to
'EDIFRAME'.
If I do SELECT @.@.SERVERNAME the result is 'EDIFRAME-' (This is the old
temporary name)
How can I change the servername because I think this is the problem?
Thanks in advance for any help.
Jo Segers.
http://www.karaszi.com/SQLServer/inf...erver_name.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Jo Segers" <segers_jo@.hotmail.com> wrote in message news:e$AuIrvpEHA.1712@.tk2msftngp13.phx.gbl...
> Hi,
> I moved jobs from a server to a new one with dts. However I cannot delete,
> edit, enable, ... these jobs. If I try so I get the following error:
> 14274 Cannot add, update, or delete a job (or its steps or schedules) that
> originated from an MSX server.
> We changed the name of the server after it was installed from 'EDIFRAME-' to
> 'EDIFRAME'.
> If I do SELECT @.@.SERVERNAME the result is 'EDIFRAME-' (This is the old
> temporary name)
> How can I change the servername because I think this is the problem?
> Thanks in advance for any help.
> Jo Segers.
>
|||Thanks for the information, this solved my problem.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> schreef
in bericht news:eT7K37vpEHA.2900@.TK2MSFTNGP12.phx.gbl...
> http://www.karaszi.com/SQLServer/inf...erver_name.asp
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Jo Segers" <segers_jo@.hotmail.com> wrote in message
news:e$AuIrvpEHA.1712@.tk2msftngp13.phx.gbl...[vbcol=seagreen]
delete,[vbcol=seagreen]
that[vbcol=seagreen]
'EDIFRAME-' to
>

change save settings directory on SSMS 2005

when i exit sql server management studio i get the following error:

The automatically saved settings file '\\SERVER\DATA\Sql Server Management Studio\Settings\CurrentSettings-2006-09-21.vssettings' is not available for write. You can change this file on the 'Import and Export Settings' Tools Options page.

i'm guessing this might be because of the corporate policy of pointing everyone's "My Documents" at a read-only folder.

i'd like to follow the advice on the dialog but unfortunately my Tools, Options dialog does not have an 'Import and Export Settings' page.

any ideas?

This problem relates to SSMS being an offshoot of the Visual Studio Shell. I would raise a bug in Connect http://connect.microsoft.com/sqlserver|||

You could try changing the registry I found this key that might be worth looking at

HKEY_CURRENT_USER\Software\Microsoft\Microsoft SQL Server\90\Tools\Shell

|||

Probably the folder in which the vssettings file is present is a read-only location and that's why it's not allowing you to over-write it.

But you can very well re-direct your My Documents folder to any location(for which you have write access) and save the settings file will be saved when you close the SSMS.

For redirecting the My Documents folder, right click on it and select Properties and specify the new path in the Target folder location.

Hope that Helps.

Regards,

Rajesh

|||bug raised as https://connect.microsoft.com/VisualStudio/feedback/ViewFeedback.aspx?FeedbackID=208955

Change results in column to display different

I have a column in the following select startement called displayvalue. Displayvalue is a varchar column. Primary everything entered into this column is numeric, but there are times when NR is entered. In the select query if it sees NR, I would like to have NR changed in the resultset to NULL or blank. NR doesn't come up all of the time, but I have not idea how to do this in the select statment.

Here goes..

SELECT SAMPLE.SAMPLEID, SAMPLE.U_WORKORDERNUMBER, SAMPLE.U_SAMPLEDATETIME, SDI.PARAMID, SDI.DISPLAYVALUE
FROM SAMPLE SAMPLE, SDIDATAITEM SDI
WHERE ( (SAMPLE.SAMPLEID = SDI.KEYID1) AND
(SAMPLE.U_WORKORDERNUMBER = '0060') AND (SAMPLE.U_SAMPLEDATETIME > '31-DEC-2003') AND (SDI.PARAMID = 'BOD') )
ORDER BY SAMPLE.SAMPLEID ASC, SDI.PARAMID ASCSELECT SAMPLE.SAMPLEID, SAMPLE.U_WORKORDERNUMBER, SAMPLE.U_SAMPLEDATETIME, SDI.PARAMID,
(CASE WHEN ISNUMERIC(SDI.DISPLAYVALUE) = 0 THEN Null
ELSE SDI.DISPLAYVALUE END) SDI_DISPLAYVALUE
FROM SAMPLE SAMPLE, SDIDATAITEM SDI
WHERE ( (SAMPLE.SAMPLEID = SDI.KEYID1) AND
(SAMPLE.U_WORKORDERNUMBER = '0060') AND (SAMPLE.U_SAMPLEDATETIME > '31-DEC-2003') AND (SDI.PARAMID = 'BOD') )
ORDER BY SAMPLE.SAMPLEID ASC, SDI.PARAMID ASC

Change ownership

Hello I'm trying to change the ownership using this command :

sp_changeobjectowner'govern.NA_WEB_SUBS_HITS','dbo'

and then I received this following error message :

Msg 15001, Level 16, State 1, Procedure sp_changeobjectowner, Line 75
Object 'govern.NA_WEB_SUBS_HITS' does not exist or is not a valid object for this operation.

Can anyone help please?

Thanks,

Stanley

You need to speicify the current owner on this object to change:

sp_changeobjectowner 'Dbobject', 'dbo'

|||

NOTE: sp_changeobjectowner has been deprecated in SQL Server 2005. I strongly recommend using the new DDL designed for changing ownership: ALTER AUTHORIZATION.

For more information please refer to BOL:

· ALTER AUTHORIZATION (Transact-SQL) http://msdn2.microsoft.com/en-us/library/ms187359.aspx

· sp_changeobjectowner (Transact-SQL) http://msdn2.microsoft.com/en-us/library/ms177519.aspx

Thanks a lot,

-Raul Garcia

SDE/T

SQL Server Engine

|||

What if the "current owner" does not exist? After a total system crash we made a db restore. All the objects belong to a sql user that does not exist anymore (different sid). What can we do now? http://support.microsoft.com/kb/275312/en-us has a good script generator but we got stuck "oldOwner.dbObject" does not exist!!!

Change OracleDataAdapter property

I am getting the following error when trying to run a query against an Oracle DB.

"TITLE: Microsoft Report Designer

An error occurred while reading data from the query result set.
OCI-22053: overflow error

ADDITIONAL INFORMATION:

OCI-22053: overflow error
(System.Data.OracleClient)"

The fix is...

set the ReturnProviderSpecificTypes property of the OracleDataAdapter to true and have it read OracleNumber's instead of System.Decimal, which can't handle the value

I need to know How I can do this.

Using BI tools in sql server 2005

Resolved this by creating the report without previewing then opened the .rdl file in my xml editor and changed the typename value for each System.Decimal

' <rd:TypeName>System.Decimal</rd:TypeName>' to ' <rd:TypeName>OracleNumbers</rd:TypeName>'

then saved as .rdl and opened in reporting services and previewed and works just fine.

|||I'm having a similar issue but I'm processing the cube in a Visual Studio project. Can I make the data type change within the Analysis Services project somewhere?

Change OracleDataAdapter property

I am getting the following error when trying to run a query against an Oracle DB.

"TITLE: Microsoft Report Designer

An error occurred while reading data from the query result set.
OCI-22053: overflow error

ADDITIONAL INFORMATION:

OCI-22053: overflow error
(System.Data.OracleClient)"

The fix is...

set the ReturnProviderSpecificTypes property of the OracleDataAdapter to true and have it read OracleNumber's instead of System.Decimal, which can't handle the value

I need to know How I can do this.

Using BI tools in sql server 2005

Resolved this by creating the report without previewing then opened the .rdl file in my xml editor and changed the typename value for each System.Decimal

' <rd:TypeName>System.Decimal</rd:TypeName>' to ' <rd:TypeName>OracleNumbers</rd:TypeName>'

then saved as .rdl and opened in reporting services and previewed and works just fine.

|||I'm having a similar issue but I'm processing the cube in a Visual Studio project. Can I make the data type change within the Analysis Services project somewhere?

Change my query please

I have two tables, one with basic info about employee and the other one
with hierarchical data (employee ID, supervisor ID)
Following query returns the name, id and child count. Problem is that
it returns the entire table. I am only interested in records whose
supervisor id is 55 (or whatever). So, it should return the names of
employees, count of their sub employees only if their supervisor is 55.
Does this make sense?
SELECT P.First_Name + ' ' + P.Last_Name as 'Name', P.Employee_ID,
COUNT(C.Parent_Employee_ID) AS child_count
FROM Employee AS P LEFT JOIN Employee_Hierarchy AS C
ON P.Employee_ID = C.Parent_Employee_ID
GROUP BY P.Employee_ID, P.First_Name , P.Last_NameYou haven't posted any DDL, but I still think this might be of help:
http://milambda.blogspot.com/2005/0...or-monkeys.html
ML|||hi sehboo,
Just add the criteria for the supervior id:
SELECT P.First_Name + ' ' + P.Last_Name as 'Name', P.Employee_ID,
COUNT(C.Parent_Employee_ID) AS child_count
FROM Employee AS P LEFT JOIN Employee_Hierarchy AS C
ON P.Employee_ID = C.Parent_Employee_ID
where C.SupervisorID=55
GROUP BY P.Employee_ID, P.First_Name , P.Last_Name
HTH
das
"Sehboo" wrote:

> I have two tables, one with basic info about employee and the other one
> with hierarchical data (employee ID, supervisor ID)
>
> Following query returns the name, id and child count. Problem is that
> it returns the entire table. I am only interested in records whose
> supervisor id is 55 (or whatever). So, it should return the names of
> employees, count of their sub employees only if their supervisor is 55.
> Does this make sense?
> SELECT P.First_Name + ' ' + P.Last_Name as 'Name', P.Employee_ID,
> COUNT(C.Parent_Employee_ID) AS child_count
> FROM Employee AS P LEFT JOIN Employee_Hierarchy AS C
> ON P.Employee_ID = C.Parent_Employee_ID
> GROUP BY P.Employee_ID, P.First_Name , P.Last_Name
>|||Get a copy of TREES & HIERARCHIES IN SQL for several better ways to
model a heirarchy.|||If I add
where C.SupervisorID=55
then it shows just one record, I want to see all the children of
supervisor.|||Check this thread:
25e265122fe9c2e7" target="_blank">http://groups.google.com/group/micr...5e265122fe9c2e7
It has an example of how to deal with this using a simple breadth first
loop.
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Arguments are to be avoided: they are always vulgar and often convincing."
(Oscar Wilde)
<MasoodAdnan@.gmail.com> wrote in message
news:1126149763.658813.99930@.z14g2000cwz.googlegroups.com...
> If I add
> where C.SupervisorID=55
> then it shows just one record, I want to see all the children of
> supervisor.
>|||On 7 Sep 2005 20:22:43 -0700, MasoodAdnan@.gmail.com wrote:

>If I add
>where C.SupervisorID=55
>then it shows just one record, I want to see all the children of
>supervisor.
Hi MasoodAdnan,
Try adding
WHERE P.SupervisorID = 55
instead.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)