DSN issue/question..

I have 2 instances of SQL on the same box. I am running Great Plains on both. When I change the properties of one system DSN, it automatically updates the other entry also. Have any of you seen this before? Any insights would help.

Thanks

Sundar

You are in the wrong forum.

Try this for starters: http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=93&SiteID=1|||following Phil's suggestion, thread moved to DB Engine forum

dropping a temp table

I've read here that a temp table is drop automatically when a store procedur
e
that created the temp table ends or when a session that uses a temp table
ends.
If I have a temp table that was created by an activex script inside a dts
and the dts is a scheduled job, would the temp table be dropped when the job
ended? Or should I explicitly drop the table in the script?
Thanks,
--
Dan D.IMHO, you should always drop what you create and never rely on the system to
clean up for you.
"Dan D." <DanD@.discussions.microsoft.com> wrote in message
news:19839D1E-FAEF-47CB-9047-987EEB3E0663@.microsoft.com...
> I've read here that a temp table is drop automatically when a store
> procedure
> that created the temp table ends or when a session that uses a temp table
> ends.
> If I have a temp table that was created by an activex script inside a dts
> and the dts is a scheduled job, would the temp table be dropped when the
> job
> ended? Or should I explicitly drop the table in the script?
> Thanks,
> --
> Dan D.|||Dan,
Better if we drop it in the sp / job.
if object_id('tempdb.dbo.#temp_table') is not null
drop table #temp_table
AMB
"Dan D." wrote:

> I've read here that a temp table is drop automatically when a store proced
ure
> that created the temp table ends or when a session that uses a temp table
> ends.
> If I have a temp table that was created by an activex script inside a dts
> and the dts is a scheduled job, would the temp table be dropped when the j
ob
> ended? Or should I explicitly drop the table in the script?
> Thanks,
> --
> Dan D.|||Is the stored procedure really going to see the #temp table if it is created
in the DTS package? I would guess they are separately scoped...
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:AFF0DC56-3B04-4E8B-87FF-BFFD3BC9A352@.microsoft.com...
> Dan,
> Better if we drop it in the sp / job.
> if object_id('tempdb.dbo.#temp_table') is not null
> drop table #temp_table|||Aaron,
What I meant was that it is better to drop it before the code finishs.

> Is the stored procedure really going to see the #temp table if it is creat
ed
> in the DTS package? I would guess they are separately scoped...
Function Main()
sConnect="provider=sqloledb;server=weg-256;database=test_db;integrated
security=SSPI"
Set Conn = CreateObject("ADODB.Connection")
Conn.Open sConnect
Conn.Execute ("create table #tmp (c1 int)")
Conn.Execute ("insert into #tmp values(1)")
Conn.Execute ("create procedure #p1 as set nocount on select * from #tmp if
object_id('tempdb.dbo.#tmp') is not null drop table #tmp")
Set Rs = Conn.Execute ("exec #p1")
msgbox Rs.fields.item(0).value
Conn.close
Main = DTSTaskExecResult_Success
End Function
AMB
"Aaron Bertrand [SQL Server MVP]" wrote:

> Is the stored procedure really going to see the #temp table if it is creat
ed
> in the DTS package? I would guess they are separately scoped...
>
> "Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in messag
e
> news:AFF0DC56-3B04-4E8B-87FF-BFFD3BC9A352@.microsoft.com...
>
>|||Ah sorry, I saw "in the sp" but not " / job" (or didn't translate " / job"
to " / package/script")
A|||Thanks everyone. That's what I was always taught. Cleanup after yourself.
--
Dan D.
"Alejandro Mesa" wrote:
> Aaron,
> What I meant was that it is better to drop it before the code finishs.
>
> Function Main()
> sConnect="provider=sqloledb;server=weg-256;database=test_db;integrated
> security=SSPI"
> Set Conn = CreateObject("ADODB.Connection")
> Conn.Open sConnect
> Conn.Execute ("create table #tmp (c1 int)")
> Conn.Execute ("insert into #tmp values(1)")
> Conn.Execute ("create procedure #p1 as set nocount on select * from #tmp
if
> object_id('tempdb.dbo.#tmp') is not null drop table #tmp")
> Set Rs = Conn.Execute ("exec #p1")
> msgbox Rs.fields.item(0).value
> Conn.close
> Main = DTSTaskExecResult_Success
> End Function
>
> AMB
> "Aaron Bertrand [SQL Server MVP]" wrote:
>

Drop Table

Created a database which is going to import data from a Oracle database ever
y
w automatically. I need to know is there a way I can either get the impor
t
to override the existing data or a script to 'drop table' before importing.
I've done a test on importing the data and it just adds concurrently the new
data. If I can 'drop table' how would I script it to do it wly before the
import?You could use DROP and re-CREATE.
Or:
TRUNCATE TABLE YourTable.
Or:
DELETE FROM YourTable
Include these as part of whatever process performs the import. Or
schedule it automatically in a job using SQL Agent.
--
David Portas
SQL Server MVP
--|||Hi David,
Thanks for suggestions, I'm not to good with SQL, I found where you do the
job schedule. In the Edit Job Step this is the following info I put in:
Type: Transact-SQL Script
Database:name of database
Command:
DROP TABLE Result
GO
(It failed)
then I tried
TRUNCATE TABLE Result
(It Failed)
Am I using the right syntax for deleting the table. The error in Event
Viewer is Event ID 208 which when you look it up doesn't tell you much.|||"GO" is batch terminator for Query Analyzer. It has nothing to do with
T-SQL. So, create a T-SQL with the following for definition.
if object_id('Result','U') is not null
drop table Result
--
-oj
"Reg Coles" <RegColes@.discussions.microsoft.com> wrote in message
news:36BF20DA-504B-4C3A-AFB9-C2158B86A6F4@.microsoft.com...
> Hi David,
> Thanks for suggestions, I'm not to good with SQL, I found where you do the
> job schedule. In the Edit Job Step this is the following info I put in:
> Type: Transact-SQL Script
> Database:name of database
> Command:
> DROP TABLE Result
> GO
> (It failed)
> then I tried
> TRUNCATE TABLE Result
> (It Failed)
> Am I using the right syntax for deleting the table. The error in Event
> Viewer is Event ID 208 which when you look it up doesn't tell you much.|||Thanks OJ, working like a treat.

Drop index error

Dear
I would like to using script to drop index and columns automatically. When I
run the script , it occur a error :
Cannot drop the index 'ADDRESS._WA_Sys_SHORTNAME_5A2A0B13', because it does
not exist in the system catalog.
I don't know what is the problem here and the "XXX_WA_Sys_XXXX" work for,
Can anyone point out the problem and give me a solution. Thanks
And my script is :
Declare Live2StdCur Scroll Cursor For
select Table_Name, Column_Name from Live.Information_Schema.columns Live
where not Exists (select * from Std.Information_Schema.columns Std where
Live.Table_Name = Std.Table_Name and Live.Column_Name = Std.Column_Name)
Order by Table_Name
For Read Only
Open Live2StdCur
Fetch First From Live2StdCur Into @.TableName, @.ColumnName
While @.@.Fetch_Status = 0
Begin
Set @.TableId = Object_id(@.TableName)
Set @.ColumnId = (select colid from syscolumns where id =
Object_id(@.TableName) and name = @.ColumnName )
-- Drop default value
Set @.constraint_name = (select name from sysobjects where parent_obj =
@.TableId and info = @.ColumnId)
EXEC ('ALTER TABLE ' +@.TableName + ' DROP CONSTRAINT ' + @.constraint_name)
-- Drop index
Declare IndexCur Scroll Cursor For
select name from sysindexes where indid in (select indid from sysindexkeys
where id = @.TableId and colid = @.ColumnId) and id = @.TableId
For Read Only
Open IndexCur
Fetch First From IndexCur Into @.index_name
While @.@.Fetch_Status = 0
Begin
EXEC ('Drop Index ' + @.TableName + '.' + @.index_name)
Fetch Next From IndexCur Into @.index_name
End
Close IndexCur
-- Drop Column
EXEC ('ALTER TABLE ' + @.TableName + ' DROP COLUMN ' + @.ColumnName)
-- Show information
print 'Table = ' + @.TableName + ', Column = ' + @.ColumnName
Fetch Next From Live2StdCur Into @.TableName, @.ColumnName
End
Close Live2StdCur
Deallocate IndexCur
Deallocate Live2StdCurTypically the indexes beginning with "_WA_Sys_" are statistics that SQL
Server generates. You ought to exclude them from your script by adding
and INDEXPROPERTY([id], [name], N'IsStatistics') = 0
to your inner cursor where you deal with the indexes.
HTH
*mike hodgson* |/ database administrator/ | mallesons stephen jaques
*T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907
*E* mailto:mike.hodgson@.mallesons.nospam.com |* W* http://www.mallesons.com
Gary wrote:

>Dear
>I would like to using script to drop index and columns automatically. When
I
>run the script , it occur a error :
>Cannot drop the index 'ADDRESS._WA_Sys_SHORTNAME_5A2A0B13', because it does
>not exist in the system catalog.
>I don't know what is the problem here and the "XXX_WA_Sys_XXXX" work for,
>Can anyone point out the problem and give me a solution. Thanks
>And my script is :
>Declare Live2StdCur Scroll Cursor For
>select Table_Name, Column_Name from Live.Information_Schema.columns Live
>where not Exists (select * from Std.Information_Schema.columns Std where
>Live.Table_Name = Std.Table_Name and Live.Column_Name = Std.Column_Name)
>Order by Table_Name
>For Read Only
>Open Live2StdCur
>Fetch First From Live2StdCur Into @.TableName, @.ColumnName
>While @.@.Fetch_Status = 0
>Begin
>Set @.TableId = Object_id(@.TableName)
>Set @.ColumnId = (select colid from syscolumns where id =
>Object_id(@.TableName) and name = @.ColumnName )
>-- Drop default value
>Set @.constraint_name = (select name from sysobjects where parent_obj =
>@.TableId and info = @.ColumnId)
>EXEC ('ALTER TABLE ' +@.TableName + ' DROP CONSTRAINT ' + @.constraint_name)
>-- Drop index
> Declare IndexCur Scroll Cursor For
> select name from sysindexes where indid in (select indid from sysindexkey
s
>where id = @.TableId and colid = @.ColumnId) and id = @.TableId
> For Read Only
> Open IndexCur
> Fetch First From IndexCur Into @.index_name
> While @.@.Fetch_Status = 0
> Begin
> EXEC ('Drop Index ' + @.TableName + '.' + @.index_name)
> Fetch Next From IndexCur Into @.index_name
> End
> Close IndexCur
>-- Drop Column
>EXEC ('ALTER TABLE ' + @.TableName + ' DROP COLUMN ' + @.ColumnName)
>-- Show information
>print 'Table = ' + @.TableName + ', Column = ' + @.ColumnName
>Fetch Next From Live2StdCur Into @.TableName, @.ColumnName
>End
>Close Live2StdCur
>Deallocate IndexCur
>Deallocate Live2StdCur
>
>
>|||Dear Mike
Thanks for your reply. I have solved the problem
but there is another problem here. It seem like the index type cannot drop
again
The index 'I_698DIMIDX' is dependent on column 'INVENTPROJID'.
Should I add any criteria again on the select statment
Gary
"Mike Hodgson" wrote:

> Typically the indexes beginning with "_WA_Sys_" are statistics that SQL
> Server generates. You ought to exclude them from your script by adding
> and INDEXPROPERTY([id], [name], N'IsStatistics') = 0
> to your inner cursor where you deal with the indexes.
> HTH
> --
> *mike hodgson* |/ database administrator/ | mallesons stephen jaques
> *T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907
> *E* mailto:mike.hodgson@.mallesons.nospam.com |* W* [url]http://www.mallesons.com[/url
]
>
> Gary wrote:
>
>|||Sorry
The whole error message are:
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'CONSTRAINT'.
Server: Msg 5074, Level 16, State 8, Line 1
The index 'I_698DIMIDX' is dependent on column 'INVENTPROJID'.
Server: Msg 5074, Level 16, State 1, Line 1
The index 'I_698PROJIDIDX' is dependent on column 'INVENTPROJID'.
Server: Msg 5074, Level 16, State 1, Line 1
The index 'I_698DIMIDX' is dependent on column 'INVENTPROJID'.
Server: Msg 5074, Level 16, State 1, Line 1
The index 'I_698PROJIDIDX' is dependent on column 'INVENTPROJID'.
Server: Msg 4922, Level 16, State 1, Line 1
ALTER TABLE DROP COLUMN INVENTPROJID failed because one or more objects
access this column.
"Gary" wrote:
> Dear Mike
> Thanks for your reply. I have solved the problem
> but there is another problem here. It seem like the index type cannot drop
> again
> The index 'I_698DIMIDX' is dependent on column 'INVENTPROJID'.
> Should I add any criteria again on the select statment
> Gary
> "Mike Hodgson" wrote:
>|||Gary,
Cursor loops (especially nested loops) can be difficult to debug. What
I often do, which I find quite helpful especially when building up
complex dynamic strings to EXEC at runtime, is change the "EXEC (...)"
statements to "PRINT (...)" so I can see exactly what T-SQL commands the
SQL server is trying to execute. Then you can take the results of that
(with all the print statements) and, as that should be a valid T-SQL
script, execute the statements either one at a time (in order) or in
small batches to see where it's going wrong. It often becomes blatantly
obvious where the mistake is when you do this.
One thing I notice is you are declaring the inner cursor (IndexCur)
inside the outer WHILE loop but you're only deallocating it outside the
outer loop. The OPEN & CLOSE are fine and open & close the resultset
appropriately, but how you've got the DECLARE & DEALLOCATE I would think
would result in the same cursor being declared multiple times but the
resources used by the cursor would not get released each time. SQL
Server may be able to handle this odd case (not sure), but in most
programming languages that would result in a memory/resource leak.
I think the problem is in the fact that you're using variables in the
declaration of your inner cursor (IndexCur) but since you're not
deallocating the cursor before you declare it again (i.e. at the end of
the inner loop), the different variable values each time through the
outer loop will not be taken into account when the cursor is declared
again. This would mean that for each iteration of the outer loop, the
inner cursor would have the same declaration and so you'd be working on
the same resultset for IndexCur each time...I think. BOL describes this
on its "DECLARE CURSOR" page:
Variables may be used as part of the /select_statement/ that
declares a cursor. Cursor variable values do not change after a
cursor is declared. In SQL Server version 6.5 and earlier, variable
values are refreshed every time a cursor is reopened.
I may be off-base with this thought but you should be able to tell if
this is the case or not pretty quickly by changing your "EXEC (...)"
statements to "PRINT (...)" and looking at the resultant T-SQL statements.
It's always a good idea to have your DECLARE/DEALLOCATE and OPEN/CLOSE
statements at the same scope as each other so they're always a matching
pair in terms of scope.
Also, there's not much point in making the cursors SCROLL cursors since
the only operation you're doing on them is FETCH NEXT (the FETCH FIRST
statements in this context do the same as a FETCH NEXT).
*mike hodgson* |/ database administrator/ | mallesons stephen jaques
*T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907
*E* mailto:mike.hodgson@.mallesons.nospam.com |* W* http://www.mallesons.com
Gary wrote:
>Sorry
>The whole error message are:
>Server: Msg 170, Level 15, State 1, Line 1
>Line 1: Incorrect syntax near 'CONSTRAINT'.
>Server: Msg 5074, Level 16, State 8, Line 1
>The index 'I_698DIMIDX' is dependent on column 'INVENTPROJID'.
>Server: Msg 5074, Level 16, State 1, Line 1
>The index 'I_698PROJIDIDX' is dependent on column 'INVENTPROJID'.
>Server: Msg 5074, Level 16, State 1, Line 1
>The index 'I_698DIMIDX' is dependent on column 'INVENTPROJID'.
>Server: Msg 5074, Level 16, State 1, Line 1
>The index 'I_698PROJIDIDX' is dependent on column 'INVENTPROJID'.
>Server: Msg 4922, Level 16, State 1, Line 1
>ALTER TABLE DROP COLUMN INVENTPROJID failed because one or more objects
>access this column.
>
>"Gary" wrote:
>
>

Drop Down Parameters Not Working

If you select an option from the dropdown list, the secondary dropdown list
does
not populate automatically. It appears that the focus is not being made on
the frame that allows for the user to enter parameters.
Is there a way to make the focus on a certain frame of the Report parameters
form?
I found that once the page loads, as long as I don't select anything in the
primary dropdown list, I can select the frame that the drop down is in, and
then select an option and it works correctly. If I select the option first,
then select the frame, it does not work. I'm not an expert, but to me, this
means that the focus needs to be put on the drop down list frame by Reporting
Service.I had this exact same problem, took a while to figure it out. Some IE
browsers (maybe others too) do this, not really the reporting services. I
believe if you update your browser it can solve this. Otherwise you can add
"&rc:Stylesheet=fix" to the end of you link to the report and it should work.
Hope this helps.
"A.J." wrote:
> If you select an option from the dropdown list, the secondary dropdown list
> does
> not populate automatically. It appears that the focus is not being made on
> the frame that allows for the user to enter parameters.
> Is there a way to make the focus on a certain frame of the Report parameters
> form?
> I found that once the page loads, as long as I don't select anything in the
> primary dropdown list, I can select the frame that the drop down is in, and
> then select an option and it works correctly. If I select the option first,
> then select the frame, it does not work. I'm not an expert, but to me, this
> means that the focus needs to be put on the drop down list frame by Reporting
> Service.|||I tried to update my browser and that didn't work. I already have the latest
from Microsoft. As far as the &rc goes, I didn't development the report, so
I'm not sure how to do this, but I'll pass it on to the owner.
"scraejtp" wrote:
> I had this exact same problem, took a while to figure it out. Some IE
> browsers (maybe others too) do this, not really the reporting services. I
> believe if you update your browser it can solve this. Otherwise you can add
> "&rc:Stylesheet=fix" to the end of you link to the report and it should work.
> Hope this helps.
> "A.J." wrote:
> > If you select an option from the dropdown list, the secondary dropdown list
> > does
> > not populate automatically. It appears that the focus is not being made on
> > the frame that allows for the user to enter parameters.
> >
> > Is there a way to make the focus on a certain frame of the Report parameters
> > form?
> >
> > I found that once the page loads, as long as I don't select anything in the
> > primary dropdown list, I can select the frame that the drop down is in, and
> > then select an option and it works correctly. If I select the option first,
> > then select the frame, it does not work. I'm not an expert, but to me, this
> > means that the focus needs to be put on the drop down list frame by Reporting
> > Service.|||Looks like I got it to work, but I had to revert to a previous version of
I.E. browser. I went from Update Version
"SP1;Q832894;Q867801;Q871260;Q823353;" to simply "SP1", which brings us back
to our original problem occurring after we pushed out Microsoft patch 888113.
This should be a Microsoft issue, if someone knows how to report it.
"scraejtp" wrote:
> I had this exact same problem, took a while to figure it out. Some IE
> browsers (maybe others too) do this, not really the reporting services. I
> believe if you update your browser it can solve this. Otherwise you can add
> "&rc:Stylesheet=fix" to the end of you link to the report and it should work.
> Hope this helps.
> "A.J." wrote:
> > If you select an option from the dropdown list, the secondary dropdown list
> > does
> > not populate automatically. It appears that the focus is not being made on
> > the frame that allows for the user to enter parameters.
> >
> > Is there a way to make the focus on a certain frame of the Report parameters
> > form?
> >
> > I found that once the page loads, as long as I don't select anything in the
> > primary dropdown list, I can select the frame that the drop down is in, and
> > then select an option and it works correctly. If I select the option first,
> > then select the frame, it does not work. I'm not an expert, but to me, this
> > means that the focus needs to be put on the drop down list frame by Reporting
> > Service.