modified connection in package cionfiguration - sql server agent job refuses to run

Hello,

Here is the following mind-numbing problem I have (and wished I did not have to experience)

A set of 2 SSIS packages is scheduled to run in a sql server agent job on the same server. Both packages use an environment variable that point to a package configuration file. In this file there are 2 connections, one to a sql server with a sql server user id and passwordn, another to an AS400 DB2. Both packages are deployed on the same server in SSIS server under MSDB sql storage, with package protection level set to 'rely on server storage and roles for access control'.

Today the connection to the As400 needed to change, it is now connected to another AS400 server. The packages have been modified to use the new connection. In the configuration file the old connection has been commented out and the new connection string was added, the connection itself was given a new more meaningfull name in the packages.

Running the packages from visual studio 2005 works. After testing I have deployed the packages to SSIS server in MSDB storage.

Now when I start the sql server agent job that runs these packages, the job quits with an error, in the history I see an error message that it failed to connect to the sql server with the given sql server user account.

When I the step in the sql server agent job properties for both packages, under the Tab 'Data sources' I see that it is using the new AS400 connection. I can also see the connectionstring for the sql server with the user id (but no password).

To make it possible for my packages to run (the users are waiting for the data) I have solved it like this:

- under the 'configurations' tab I have added the name of my package configuration file.

- i did this for both packages in both job steps

when I run the job , it works without problems.

Now , my question is: I have hardcoded the pathname for the package configuration file in my job. Instead of the package using the environment variable to find th epackage configuration file. I would prefer my packages , when in a sql server agent job to also use the environment variable. What can I do to make this happen?

Flabbergasted as always,

Hello,

I have found the cause of the problem and the answer to my question.

I forgot to mention one of the steps of the modification: the package configuration file was moved to another drive, to centralize all configuration files on the same drive and folder. The environment variable fior the config file had been changed to point to the new and correct location, the config file had been deleted from the old location. But the SSIS server had NOT been restarted.

therefore the probable reason of the problem was: when the job was run, the package was given the in memory environment variable value from SSIS server and did not find the package configuration file, it then used the connections string information kept in the package. but in a package the password is never stored, so it could not connect to the sql server. It may also have received the connection from the sql server agent job (in the 'Data sources' tab under the job step properties) but in there the password is not kept either.

I have copied the package configuration file back to the old drive and folder and removed the package configuration file name from the 'Connections' tab in the job step properties. After that the job ran without problems.

Conclusion: I have to restart the SSIS service to read the environment variable again , so that it reads from the config files in the new folder.

question : is there some other way to force SSIS to read environment variables?

It is hard to think of a sentence that contains the words 'developer-friendly' and 'SSIS' (without negation). Sentences that contain the words 'mind-boggling', 'blistering barnacles' or 'cumbersome' together with 'SSIS' are easier to think of.

|||

I would agree with Jan Dhondt.SSIS package execution is not consistent under different environment and may function abnormally. Very fundamental issues have not been dealt with, the result is sleepless nights for developers like me.

This is what I am stuck with:

I have a SSIS package, which dynamically connects to a flat file data source and loads the data to an OLE DB destination.

The package when executed from the command prompt, with the 'dtExec' utility, and executed from structured file storage works fine and loads the data from any folder location without any error. It also executes fine, when executed from Query designer or SQL Agent Job, if the flat file data source resides in the same directory as the package or any of sub directories of the saved package; but when the package is executed from the Query designer or SQL Agent Job, and when the data source file, is not saved in the directory or sub directories same as the package source the package gives throws a strange error:

Code: 0xC001401E

Source: SSISPackage_Data_Import Connection manager "FlatFileConnectionManager"

Description: The file name "\\filepath\Training_090909.txt" specified in the connection was not valid.

Also the variable(varFilename), which dynamically stores the location of the flat file data source, has a valid default value, so it shouldn't give any design time validation error. I don't have a package configuration file.

Any help on this would be appreciated.

Hope that MS would deal with such issues before it is too late.

Thanks

|||

If the package is started from a sql agent job, it may have different credentials than normal, and therefore may not have read access to the txt file. Is this the case?

|||I am trying to run the package in a T-SQL statement.

modified connection in package cionfiguration - sql server agent job refuses to run

Hello,

Here is the following mind-numbing problem I have (and wished I did not have to experience)

A set of 2 SSIS packages is scheduled to run in a sql server agent job on the same server. Both packages use an environment variable that point to a package configuration file. In this file there are 2 connections, one to a sql server with a sql server user id and passwordn, another to an AS400 DB2. Both packages are deployed on the same server in SSIS server under MSDB sql storage, with package protection level set to 'rely on server storage and roles for access control'.

Today the connection to the As400 needed to change, it is now connected to another AS400 server. The packages have been modified to use the new connection. In the configuration file the old connection has been commented out and the new connection string was added, the connection itself was given a new more meaningfull name in the packages.

Running the packages from visual studio 2005 works. After testing I have deployed the packages to SSIS server in MSDB storage.

Now when I start the sql server agent job that runs these packages, the job quits with an error, in the history I see an error message that it failed to connect to the sql server with the given sql server user account.

When I the step in the sql server agent job properties for both packages, under the Tab 'Data sources' I see that it is using the new AS400 connection. I can also see the connectionstring for the sql server with the user id (but no password).

To make it possible for my packages to run (the users are waiting for the data) I have solved it like this:

- under the 'configurations' tab I have added the name of my package configuration file.

- i did this for both packages in both job steps

when I run the job , it works without problems.

Now , my question is: I have hardcoded the pathname for the package configuration file in my job. Instead of the package using the environment variable to find th epackage configuration file. I would prefer my packages , when in a sql server agent job to also use the environment variable. What can I do to make this happen?

Flabbergasted as always,

Hello,

I have found the cause of the problem and the answer to my question.

I forgot to mention one of the steps of the modification: the package configuration file was moved to another drive, to centralize all configuration files on the same drive and folder. The environment variable fior the config file had been changed to point to the new and correct location, the config file had been deleted from the old location. But the SSIS server had NOT been restarted.

therefore the probable reason of the problem was: when the job was run, the package was given the in memory environment variable value from SSIS server and did not find the package configuration file, it then used the connections string information kept in the package. but in a package the password is never stored, so it could not connect to the sql server. It may also have received the connection from the sql server agent job (in the 'Data sources' tab under the job step properties) but in there the password is not kept either.

I have copied the package configuration file back to the old drive and folder and removed the package configuration file name from the 'Connections' tab in the job step properties. After that the job ran without problems.

Conclusion: I have to restart the SSIS service to read the environment variable again , so that it reads from the config files in the new folder.

question : is there some other way to force SSIS to read environment variables?

It is hard to think of a sentence that contains the words 'developer-friendly' and 'SSIS' (without negation). Sentences that contain the words 'mind-boggling', 'blistering barnacles' or 'cumbersome' together with 'SSIS' are easier to think of.

|||

I would agree with Jan Dhondt.SSIS package execution is not consistent under different environment and may function abnormally. Very fundamental issues have not been dealt with, the result is sleepless nights for developers like me.

This is what I am stuck with:

I have a SSIS package, which dynamically connects to a flat file data source and loads the data to an OLE DB destination.

The package when executed from the command prompt, with the 'dtExec' utility, and executed from structured file storage works fine and loads the data from any folder location without any error. It also executes fine, when executed from Query designer or SQL Agent Job, if the flat file data source resides in the same directory as the package or any of sub directories of the saved package; but when the package is executed from the Query designer or SQL Agent Job, and when the data source file, is not saved in the directory or sub directories same as the package source the package gives throws a strange error:

Code: 0xC001401E

Source: SSISPackage_Data_Import Connection manager "FlatFileConnectionManager"

Description: The file name "\\filepath\Training_090909.txt" specified in the connection was not valid.

Also the variable(varFilename), which dynamically stores the location of the flat file data source, has a valid default value, so it shouldn't give any design time validation error. I don't have a package configuration file.

Any help on this would be appreciated.

Hope that MS would deal with such issues before it is too late.

Thanks

|||

If the package is started from a sql agent job, it may have different credentials than normal, and therefore may not have read access to the txt file. Is this the case?

|||I am trying to run the package in a T-SQL statement.sql

Model database has unknown owner

I am trying to run the sp_helpdb stored procedure and am getting the
following results:
Server: Msg 515, Level 16, State 2, Procedure sp_helpdb, Line 53
Cannot insert the value NULL into column '', table ''; column does not
allow nulls. INSERT fails.
The statement has been terminated.
I think I have narrowed it down. The model database has an owner of
unknown. I have tried running this:
ALTER DATABASE model SET SINGLE_USER
DBCC CHECKDB ('model', Repair_Rebuild)
ALTER DATABASE model SET Multi_USER
But the server seems to hang trying to set the db to single user. I
can not detach the model database either. Any suggestions on how to
fix this without losing all of my other database info in the master?
TIA
when you run this
select schema_owner,* from information_schema.schemata
where catalog_name ='model'
what's the schema_owner?
Denis the SQL Menace
http://sqlservercode.blogspot.com/
|||Have you tried just changing the owner to 'sa', which is what it should be?
USE model
EXEC sp_changedbowner 'sa'
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
<jhmosow@.gmail.com> wrote in message
news:1143221417.974191.196120@.i40g2000cwc.googlegr oups.com...
>I am trying to run the sp_helpdb stored procedure and am getting the
> following results:
> Server: Msg 515, Level 16, State 2, Procedure sp_helpdb, Line 53
> Cannot insert the value NULL into column '', table ''; column does not
> allow nulls. INSERT fails.
> The statement has been terminated.
> I think I have narrowed it down. The model database has an owner of
> unknown. I have tried running this:
> ALTER DATABASE model SET SINGLE_USER
> DBCC CHECKDB ('model', Repair_Rebuild)
> ALTER DATABASE model SET Multi_USER
> But the server seems to hang trying to set the db to single user. I
> can not detach the model database either. Any suggestions on how to
> fix this without losing all of my other database info in the master?
> TIA
>
|||When I tried this query:
select schema_owner,* from information_schema.schemata
where catalog_name ='model'
I get:
Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'information_schema.schemata'.
When I try changing the owner using:
use model
EXEC sp_changedbowner 'sa'
I get:
Server: Msg 15109, Level 16, State 1, Procedure sp_changedbowner, Line
22
Cannot change the owner of the master database.
I am logged in as sa.
|||The sp_changedbowner procedure affects the database you are currently in,
and the message indicates you did not USE model before running the stored
procedure.
First:
USE model
GO
Make sure you are in model:
SELECT db_name()
Once you are in model:
EXEC sp_changedbowner 'sa'
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
<jhmosow@.gmail.com> wrote in message
news:1143292556.116759.304750@.e56g2000cwe.googlegr oups.com...
> When I tried this query:
> select schema_owner,* from information_schema.schemata
> where catalog_name ='model'
> I get:
> Server: Msg 208, Level 16, State 1, Line 1
> Invalid object name 'information_schema.schemata'.
> When I try changing the owner using:
> use model
> EXEC sp_changedbowner 'sa'
> I get:
> Server: Msg 15109, Level 16, State 1, Procedure sp_changedbowner, Line
> 22
> Cannot change the owner of the master database.
> I am logged in as sa.
>
|||I made sure I was in the model database. I set up the following query:
use model
go
SELECT db_name()
go
EXEC sp_changedbowner 'sa'
go
The responses I received was:
(1 row(s) affected)
Server: Msg 15109, Level 16, State 1, Procedure sp_changedbowner, Line
22
Cannot change the owner of the master database.
Query Analyzer does show I am in the Model database.
|||What messages do you get if you do below?
EXEC model..sp_changedbowner 'sa'
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<jhmosow@.gmail.com> wrote in message news:1143466613.203455.96190@.i40g2000cwc.googlegro ups.com...
>I made sure I was in the model database. I set up the following query:
> use model
> go
> SELECT db_name()
> go
> EXEC sp_changedbowner 'sa'
> go
> The responses I received was:
> (1 row(s) affected)
> Server: Msg 15109, Level 16, State 1, Procedure sp_changedbowner, Line
> 22
> Cannot change the owner of the master database.
> Query Analyzer does show I am in the Model database.
>
|||Running EXEC model..sp_changedbowner 'sa' returns:
Server: Msg 15109, Level 16, State 1, Procedure sp_changedbowner, Line
22
Cannot change the owner of the master database.
|||OK, it seems like SQL Server doesn't allow you to change the owner of the model database, and that
the error message is slightly misleading. Since sp_changedbowner doesn't allow you to change the
owner of model to anything else but "sa", you have to try to find out how and why this was changed
from sa in the first place. How to fix this is then up to you:
* Rebuild the system databases (rebuildm.exe). You will lose all information in the system
databases.
* Hack the system tables. If you don't know how, don't do it. And, it is not supported.- Warning,
warning!!!
* Open a case with MS Support and let them hand-hold you through the process.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
<jhmosow@.gmail.com> wrote in message news:1143471215.461886.94170@.v46g2000cwv.googlegro ups.com...
> Running EXEC model..sp_changedbowner 'sa' returns:
> Server: Msg 15109, Level 16, State 1, Procedure sp_changedbowner, Line
> 22
> Cannot change the owner of the master database.
>

Model database has unknown owner

I am trying to run the sp_helpdb stored procedure and am getting the
following results:
Server: Msg 515, Level 16, State 2, Procedure sp_helpdb, Line 53
Cannot insert the value NULL into column '', table ''; column does not
allow nulls. INSERT fails.
The statement has been terminated.
I think I have narrowed it down. The model database has an owner of
unknown. I have tried running this:
ALTER DATABASE model SET SINGLE_USER
DBCC CHECKDB ('model', Repair_Rebuild)
ALTER DATABASE model SET Multi_USER
But the server seems to hang trying to set the db to single user. I
can not detach the model database either. Any suggestions on how to
fix this without losing all of my other database info in the master?
TIAwhen you run this
select schema_owner,* from information_schema.schemata
where catalog_name ='model'
what's the schema_owner?
Denis the SQL Menace
http://sqlservercode.blogspot.com/|||Have you tried just changing the owner to 'sa', which is what it should be?
USE model
EXEC sp_changedbowner 'sa'
--
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
<jhmosow@.gmail.com> wrote in message
news:1143221417.974191.196120@.i40g2000cwc.googlegroups.com...
>I am trying to run the sp_helpdb stored procedure and am getting the
> following results:
> Server: Msg 515, Level 16, State 2, Procedure sp_helpdb, Line 53
> Cannot insert the value NULL into column '', table ''; column does not
> allow nulls. INSERT fails.
> The statement has been terminated.
> I think I have narrowed it down. The model database has an owner of
> unknown. I have tried running this:
> ALTER DATABASE model SET SINGLE_USER
> DBCC CHECKDB ('model', Repair_Rebuild)
> ALTER DATABASE model SET Multi_USER
> But the server seems to hang trying to set the db to single user. I
> can not detach the model database either. Any suggestions on how to
> fix this without losing all of my other database info in the master?
> TIA
>|||When I tried this query:
select schema_owner,* from information_schema.schemata
where catalog_name ='model'
I get:
Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'information_schema.schemata'.
When I try changing the owner using:
use model
EXEC sp_changedbowner 'sa'
I get:
Server: Msg 15109, Level 16, State 1, Procedure sp_changedbowner, Line
22
Cannot change the owner of the master database.
I am logged in as sa.|||The sp_changedbowner procedure affects the database you are currently in,
and the message indicates you did not USE model before running the stored
procedure.
First:
USE model
GO
Make sure you are in model:
SELECT db_name()
Once you are in model:
EXEC sp_changedbowner 'sa'
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
<jhmosow@.gmail.com> wrote in message
news:1143292556.116759.304750@.e56g2000cwe.googlegroups.com...
> When I tried this query:
> select schema_owner,* from information_schema.schemata
> where catalog_name ='model'
> I get:
> Server: Msg 208, Level 16, State 1, Line 1
> Invalid object name 'information_schema.schemata'.
> When I try changing the owner using:
> use model
> EXEC sp_changedbowner 'sa'
> I get:
> Server: Msg 15109, Level 16, State 1, Procedure sp_changedbowner, Line
> 22
> Cannot change the owner of the master database.
> I am logged in as sa.
>|||I made sure I was in the model database. I set up the following query:
use model
go
SELECT db_name()
go
EXEC sp_changedbowner 'sa'
go
The responses I received was:
(1 row(s) affected)
Server: Msg 15109, Level 16, State 1, Procedure sp_changedbowner, Line
22
Cannot change the owner of the master database.
Query Analyzer does show I am in the Model database.|||What messages do you get if you do below?
EXEC model..sp_changedbowner 'sa'
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<jhmosow@.gmail.com> wrote in message news:1143466613.203455.96190@.i40g2000cwc.googlegroups.com...
>I made sure I was in the model database. I set up the following query:
> use model
> go
> SELECT db_name()
> go
> EXEC sp_changedbowner 'sa'
> go
> The responses I received was:
> (1 row(s) affected)
> Server: Msg 15109, Level 16, State 1, Procedure sp_changedbowner, Line
> 22
> Cannot change the owner of the master database.
> Query Analyzer does show I am in the Model database.
>|||Running EXEC model..sp_changedbowner 'sa' returns:
Server: Msg 15109, Level 16, State 1, Procedure sp_changedbowner, Line
22
Cannot change the owner of the master database.|||OK, it seems like SQL Server doesn't allow you to change the owner of the model database, and that
the error message is slightly misleading. Since sp_changedbowner doesn't allow you to change the
owner of model to anything else but "sa", you have to try to find out how and why this was changed
from sa in the first place. How to fix this is then up to you:
* Rebuild the system databases (rebuildm.exe). You will lose all information in the system
databases.
* Hack the system tables. If you don't know how, don't do it. And, it is not supported.- Warning,
warning!!!
* Open a case with MS Support and let them hand-hold you through the process.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
<jhmosow@.gmail.com> wrote in message news:1143471215.461886.94170@.v46g2000cwv.googlegroups.com...
> Running EXEC model..sp_changedbowner 'sa' returns:
> Server: Msg 15109, Level 16, State 1, Procedure sp_changedbowner, Line
> 22
> Cannot change the owner of the master database.
>sql

Model database has unknown owner

I am trying to run the sp_helpdb stored procedure and am getting the
following results:
Server: Msg 515, Level 16, State 2, Procedure sp_helpdb, Line 53
Cannot insert the value NULL into column '', table ''; column does not
allow nulls. INSERT fails.
The statement has been terminated.
I think I have narrowed it down. The model database has an owner of
unknown. I have tried running this:
ALTER DATABASE model SET SINGLE_USER
DBCC CHECKDB ('model', Repair_Rebuild)
ALTER DATABASE model SET Multi_USER
But the server seems to hang trying to set the db to single user. I
can not detach the model database either. Any suggestions on how to
fix this without losing all of my other database info in the master?
TIAwhen you run this
select schema_owner,* from information_schema.schemata
where catalog_name ='model'
what's the schema_owner?
Denis the SQL Menace
http://sqlservercode.blogspot.com/|||Have you tried just changing the owner to 'sa', which is what it should be?
USE model
EXEC sp_changedbowner 'sa'
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
<jhmosow@.gmail.com> wrote in message
news:1143221417.974191.196120@.i40g2000cwc.googlegroups.com...
>I am trying to run the sp_helpdb stored procedure and am getting the
> following results:
> Server: Msg 515, Level 16, State 2, Procedure sp_helpdb, Line 53
> Cannot insert the value NULL into column '', table ''; column does not
> allow nulls. INSERT fails.
> The statement has been terminated.
> I think I have narrowed it down. The model database has an owner of
> unknown. I have tried running this:
> ALTER DATABASE model SET SINGLE_USER
> DBCC CHECKDB ('model', Repair_Rebuild)
> ALTER DATABASE model SET Multi_USER
> But the server seems to hang trying to set the db to single user. I
> can not detach the model database either. Any suggestions on how to
> fix this without losing all of my other database info in the master?
> TIA
>|||When I tried this query:
select schema_owner,* from information_schema.schemata
where catalog_name ='model'
I get:
Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'information_schema.schemata'.
When I try changing the owner using:
use model
EXEC sp_changedbowner 'sa'
I get:
Server: Msg 15109, Level 16, State 1, Procedure sp_changedbowner, Line
22
Cannot change the owner of the master database.
I am logged in as sa.|||The sp_changedbowner procedure affects the database you are currently in,
and the message indicates you did not USE model before running the stored
procedure.
First:
USE model
GO
Make sure you are in model:
SELECT db_name()
Once you are in model:
EXEC sp_changedbowner 'sa'
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
<jhmosow@.gmail.com> wrote in message
news:1143292556.116759.304750@.e56g2000cwe.googlegroups.com...
> When I tried this query:
> select schema_owner,* from information_schema.schemata
> where catalog_name ='model'
> I get:
> Server: Msg 208, Level 16, State 1, Line 1
> Invalid object name 'information_schema.schemata'.
> When I try changing the owner using:
> use model
> EXEC sp_changedbowner 'sa'
> I get:
> Server: Msg 15109, Level 16, State 1, Procedure sp_changedbowner, Line
> 22
> Cannot change the owner of the master database.
> I am logged in as sa.
>|||I made sure I was in the model database. I set up the following query:
use model
go
SELECT db_name()
go
EXEC sp_changedbowner 'sa'
go
The responses I received was:
(1 row(s) affected)
Server: Msg 15109, Level 16, State 1, Procedure sp_changedbowner, Line
22
Cannot change the owner of the master database.
Query Analyzer does show I am in the Model database.|||What messages do you get if you do below?
EXEC model..sp_changedbowner 'sa'
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<jhmosow@.gmail.com> wrote in message news:1143466613.203455.96190@.i40g2000cwc.googlegroups.c
om...
>I made sure I was in the model database. I set up the following query:
> use model
> go
> SELECT db_name()
> go
> EXEC sp_changedbowner 'sa'
> go
> The responses I received was:
> (1 row(s) affected)
> Server: Msg 15109, Level 16, State 1, Procedure sp_changedbowner, Line
> 22
> Cannot change the owner of the master database.
> Query Analyzer does show I am in the Model database.
>|||Running EXEC model..sp_changedbowner 'sa' returns:
Server: Msg 15109, Level 16, State 1, Procedure sp_changedbowner, Line
22
Cannot change the owner of the master database.|||OK, it seems like SQL Server doesn't allow you to change the owner of the mo
del database, and that
the error message is slightly misleading. Since sp_changedbowner doesn't all
ow you to change the
owner of model to anything else but "sa", you have to try to find out how an
d why this was changed
from sa in the first place. How to fix this is then up to you:
* Rebuild the system databases (rebuildm.exe). You will lose all information
in the system
databases.
* Hack the system tables. If you don't know how, don't do it. And, it is not
supported.- Warning,
warning!!!
* Open a case with MS Support and let them hand-hold you through the process
.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
<jhmosow@.gmail.com> wrote in message news:1143471215.461886.94170@.v46g2000cwv.googlegroups.c
om...
> Running EXEC model..sp_changedbowner 'sa' returns:
> Server: Msg 15109, Level 16, State 1, Procedure sp_changedbowner, Line
> 22
> Cannot change the owner of the master database.
>

MOD Equivalent in SQl Server?

I'm trying to build a query that I'd like only run on records with an odd
number in a specific field.
Using the "MOD" function, I'd simply throw a criteria in that says where
"Field Mod 2 <> 0"
Is there a mod function in SQL Server 2K? I can't find it... If not,
what's my alternative?
Thanks in advance...
gThe "%" character is the modulo function, so try
Field % 2 <> 0
"Greg Toronto" wrote:

> I'm trying to build a query that I'd like only run on records with an odd
> number in a specific field.
> Using the "MOD" function, I'd simply throw a criteria in that says where
> "Field Mod 2 <> 0"
> Is there a mod function in SQL Server 2K? I can't find it... If not,
> what's my alternative?
> Thanks in advance...
> g|||>> Using the "MOD" function, I'd simply throw a criteria in that says where
"Field Mod 2 <> 0" <<
They stole the infixed % from C; the ANSI/ISO syntax is MOD(). But be
careful and try your MOD to see how you expect negative numbers to
work. This was a big problem in Standardizing Pascal years ago.

Mobile vs Compact - What changed?

What really changed from SQL Mobile to SQL Compact besides allowing the database to run on any desktop? Is there a document somewhere explaining the differences, I did not see anything?

I just changed my development machine over to SP1 and wondered what code I should be changing.

I am filling the database from my C++ Desktop app using ADO using the OLEDB provider Microsoft.SQLSERVER.MOBILE.OLEDB.3.0 and using System.Data.SqlServerCe in my C# mobile app. Is that OLE provider valid with the new version or should it be *.COMPACT.3.1?

I thought there was now suppose to be some sort of automatic compaction, but I did not notice any new properties.

I would appreciate any insight into the changes.

Thanks,

Juan Foegen

From my understanding, SQL Server Compact edition is an updated version to SQL Server Mobile Edition. You can use the compact edition not only on the mobile devices, but also on various devices like Tablet PC's, desktop computers etc. Also they are trying to include lot of sync features in the future releases, as you can see now they released Sync Services for ADO.NET CTP, the next new sync model like Merge Replication and RDA and in the next version release of SQL Server Compact Framework 3.5 they are trying to include all these features.

You can visit http://blogs.msdn.com/stevelasker/ for more information on these changes.

However, the namespace System.Data.SqlServerCE is going to be same for all the releases as it was same in both SQL Server CE and SQL Server Mobile.

Thanks

Ravi.

|||The OLEDB provider has not changed name, as the 3.1 release mainly concerns lifting the restrictions on supported platforms. For detailed information on the new features and fixes in 3.1 (or 3.0.5300.0) see this kb article: http://support.microsoft.com/Default.aspx?kbid=920700|||

Thanks for all your help and other replies on this forum.

Mobile vs Compact - What changed?

What really changed from SQL Mobile to SQL Compact besides allowing the database to run on any desktop? Is there a document somewhere explaining the differences, I did not see anything?

I just changed my development machine over to SP1 and wondered what code I should be changing.

I am filling the database from my C++ Desktop app using ADO using the OLEDB provider Microsoft.SQLSERVER.MOBILE.OLEDB.3.0 and using System.Data.SqlServerCe in my C# mobile app. Is that OLE provider valid with the new version or should it be *.COMPACT.3.1?

I thought there was now suppose to be some sort of automatic compaction, but I did not notice any new properties.

I would appreciate any insight into the changes.

Thanks,

Juan Foegen

From my understanding, SQL Server Compact edition is an updated version to SQL Server Mobile Edition. You can use the compact edition not only on the mobile devices, but also on various devices like Tablet PC's, desktop computers etc. Also they are trying to include lot of sync features in the future releases, as you can see now they released Sync Services for ADO.NET CTP, the next new sync model like Merge Replication and RDA and in the next version release of SQL Server Compact Framework 3.5 they are trying to include all these features.

You can visit http://blogs.msdn.com/stevelasker/ for more information on these changes.

However, the namespace System.Data.SqlServerCE is going to be same for all the releases as it was same in both SQL Server CE and SQL Server Mobile.

Thanks

Ravi.

|||The OLEDB provider has not changed name, as the 3.1 release mainly concerns lifting the restrictions on supported platforms. For detailed information on the new features and fixes in 3.1 (or 3.0.5300.0) see this kb article: http://support.microsoft.com/Default.aspx?kbid=920700|||

Thanks for all your help and other replies on this forum.

sql

Mobile sdf File created on Server

Hi there,

Background: I've created an application to run on windows mobile 5.0 devices. It replicates to a publication on a server. To do this it goes through ASP.net. This creates the database on the Mobile Devices.

Issue: Problem is the initial download of the database is taking 2hours plus to replicate (create) for the first time. So I'm trying to create the .sdf file on the server, zip it, and send it across to the handheld. All subsequent replications take about 5 minutes.

Question: Does anyone know how, or have examples for creating a Mobile CE database on the server. I need to create it using ASP.net and go through a pre-existing publication where a hostname used for filtering.

http://msdn2.microsoft.com/en-us/library/ms173009.aspx explains how to create this in SQL Server Management Stuidio. How do you do the same thing over ASP?

Can this be done using SMO? How do you create a SQLServerCompactEdition database using normal .NET Framework? Thanks for your help!!

Is it possible to access System.Data.SqlServerCe namespace without using the .NET Compact Framework?

So if I wanted to access the assembly in a VB.NET Windows App inside the .NET Framework, is this possible?

|||

Yes, SQL CE 3.1 allows you to do that. There’s a special version of SQL CE provider for desktop framework.

|||You can run code like in this sample (http://msdn2.microsoft.com/en-us/library/system.data.sqlserverce.sqlcereplication.synchronize.aspx

on your web server. create the file to the local file system and zip it. Then give the device the URL to the .zip file and your .NET Compact Framework app can the download the .zip file using HttpWebRequest class and unzip.

|||

So the problem i was having was in using an assembly that came with the compact framework. The System.Data.SqlServerCE.dll file is located in at least two places on my machine. One in the CE and one in the Common folder...both have the same name and version number.

I changed the reference from pointing at the CE version to the normal version and it cleared up the problem. Thanks for the responses, I'm still not sure what the differences in the assemblies are.

Mobile sdf File created on Server

Hi there,

Background: I've created an application to run on windows mobile 5.0 devices. It replicates to a publication on a server. To do this it goes through ASP.net. This creates the database on the Mobile Devices.

Issue: Problem is the initial download of the database is taking 2hours plus to replicate (create) for the first time. So I'm trying to create the .sdf file on the server, zip it, and send it across to the handheld. All subsequent replications take about 5 minutes.

Question: Does anyone know how, or have examples for creating a Mobile CE database on the server. I need to create it using ASP.net and go through a pre-existing publication where a hostname used for filtering.

http://msdn2.microsoft.com/en-us/library/ms173009.aspx explains how to create this in SQL Server Management Stuidio. How do you do the same thing over ASP?

Can this be done using SMO? How do you create a SQLServerCompactEdition database using normal .NET Framework? Thanks for your help!!

Is it possible to access System.Data.SqlServerCe namespace without using the .NET Compact Framework?

So if I wanted to access the assembly in a VB.NET Windows App inside the .NET Framework, is this possible?

|||

Yes, SQL CE 3.1 allows you to do that. There’s a special version of SQL CE provider for desktop framework.

|||You can run code like in this sample (http://msdn2.microsoft.com/en-us/library/system.data.sqlserverce.sqlcereplication.synchronize.aspx

on your web server. create the file to the local file system and zip it. Then give the device the URL to the .zip file and your .NET Compact Framework app can the download the .zip file using HttpWebRequest class and unzip.

|||

So the problem i was having was in using an assembly that came with the compact framework. The System.Data.SqlServerCE.dll file is located in at least two places on my machine. One in the CE and one in the Common folder...both have the same name and version number.

I changed the reference from pointing at the CE version to the normal version and it cleared up the problem. Thanks for the responses, I'm still not sure what the differences in the assemblies are.

sql

Mixed Mode vs. Windows Authentication

I am trying to create a query that can determine if a user id is using mixed mode/windows/both authentication. I need to do this so that it can run on both sql server 2000 and 2005, meaning I can't use any of the sys.* views. Is there a single query could use for both systems?
-Kyle

Hi kschlap,

there is no method for your issue.

but maybe you can try to build a view to select cross these 2 servers.

create view v_loginid_info

as

select 'servname'='mssql2k', loginid

from mssql2k.master.dbo.sysprocesses

union all

select 'servname'='mssql2k05', loginid

from mssql2k05.master.sys.sysprocess

try to think about.

hoping this can help you.

Best Regrads,

Hunt.

|||I have found the query...

select name, is_policy_checked
from sys.sql_logins

When I use this query, it doesn't pick up the windows authenticated users. Is there a way to get it to pick up all users?
-Kyle|||

How about something like this:

select name, isntuser from syslogins

isntuser=1 means Windows authentication

isntuser=0 means SQL Server authentication

Ben

sql

Missing Transactions - REPOSTED.

Larry,
How about if you add a row manually to S2, does it work
then? If it does, then delete this row, run the merge
agent then add the same row on S1 and see if it
propagates through. Also, check that when the
transactional replication puts the record onto S2 is
there a resulting corresponding record in
msmerge_contents on s2? Finally please check the conflict
tables to confirm there's nothing there.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
Paul,
Thanks for you response. To answer your questions....
I reinitialized the data to make sure everything was in sync. Then I
inserted a row into S2 and ran the merge agent and the row appeared on the
remote server. I delete the record from S2 and it was deleted off the remote
server.
Next I inserted a row, for the same remote location in to S1, ran the agents
(trans on S1 & merge on the remote location and the row appeared. The delete
worked fine also.
Next I ran my app that inserts data in to S1. A total of 263 rows were
inserted into 2 of the tables. I ran the trans agent on S1 and all the new
records were replicated to S2. Next I ran the merge agent on the remote
location and nothing.
I checked msmerge_contents before and after my app ran and it had the same
number of rows.
Finally, I repeated the insert as I did in the first paragraph and nothing.
As I was typing this message, I checked something. The remote locations did
not have merge replication enabled for the dB that was the subscriber. I
enabled them then delete the data from paragraph 3 (from above) and reran my
application and nothing.
I checked the conflict tables on the publisher (S2) and they were empty. Not
sure if they are suppose to exist, but there are no conflict tables on the
subscriber.
To make things even stranger, the changes being made on the remote
location/tables are making their way back to S2
TIA,
Larry...
|||Larry,
somehow, your app is able to force the replication stored procedures to run
on the subscriber without firing the triggers. This shouldn't be possible!
I'd run profiler on the transactional subscriber to see how this is
occurring and to confirm that the merge triggers aren't firing.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Paul,
I am at such a loss. The manual insertions are not working now either.
This is what is strange...the updates that are being done at remote
locations are making it back to S2, but nothing is making it from S2 to the
remote locations. In fact, I have having to reinitialize the data daily and
the inserts into S2 are not being sent to the remote locations unless the
snapshot agent is run.
I know in trans repl, there is a log reader that gets the pending
transactions. What does this in merge? Could this be the problem?
I have disabled replication for this dB and deleted all the publications. I
recreated one and tried it as a push and a pull subscription and no luck.
HELP!!!!
Larry...
|||Larry,
please script out your publications/subscriptions and
post them up here. Also, please confirm that you are
using the same service pack (sp3/a) on all 3 computers
involved.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||The scrips below are from servers all running 8.00.534 (SP2)
~~~~
Transactional Publication & Subscription from S1 to S2
~~~~
-- Enabling the replication database
use master
GO
exec sp_replicationdboption @.dbname = N'EDI_On_RouseMain', @.optname =
N'publish', @.value = N'true'
GO
use [EDI_On_RouseMain]
GO
-- Adding the transactional publication
exec sp_addpublication @.publication = N'EDI_On_RouseMain', @.restricted =
N'false', @.sync_method = N'native', @.repl_freq = N'continuous', @.description
= N'Transactional publication of EDI_On_RouseMain database from Publisher
ROUSEMAIN.', @.status = N'active', @.allow_push = N'true', @.allow_pull =
N'true', @.allow_anonymous = N'true', @.enabled_for_internet = N'false',
@.independent_agent = N'true', @.immediate_sync = N'true', @.allow_sync_tran =
N'false', @.autogen_sync_procs = N'false', @.retention = 336,
@.allow_queued_tran = N'false', @.snapshot_in_defaultfolder = N'true',
@.compress_snapshot = N'false', @.ftp_port = 21, @.ftp_login = N'anonymous',
@.allow_dts = N'false', @.allow_subscription_copy = N'false',
@.add_to_active_directory = N'false', @.logreader_job_name =
N'ROUSEMAIN-EDI_On_RouseMain-2'
exec sp_addpublication_snapshot @.publication =
N'EDI_On_RouseMain',@.frequency_type = 4, @.frequency_interval = 1,
@.frequency_relative_interval = 1, @.frequency_recurrence_factor = 0,
@.frequency_subday = 8, @.frequency_subday_interval = 1, @.active_start_date =
0, @.active_end_date = 0, @.active_start_time_of_day = 0,
@.active_end_time_of_day = 235959, @.snapshot_job_name =
N'ROUSEMAIN-EDI_On_RouseMain-EDI_On_RouseMain-2'
GO
exec sp_grant_publication_access @.publication = N'EDI_On_RouseMain', @.login
= N'BUILTIN\Administrators'
GO
exec sp_grant_publication_access @.publication = N'EDI_On_RouseMain', @.login
= N'distributor_admin'
GO
exec sp_grant_publication_access @.publication = N'EDI_On_RouseMain', @.login
= N'EDI'
GO
exec sp_grant_publication_access @.publication = N'EDI_On_RouseMain', @.login
= N'sa'
GO
-- Adding the transactional articles
exec sp_addarticle @.publication = N'EDI_On_RouseMain', @.article =
N'tbl880Detail', @.source_owner = N'dbo', @.source_object = N'tbl880Detail',
@.destination_table = N'tbl880Detail', @.type = N'logbased', @.creation_script =
null, @.description = null, @.pre_creation_cmd = N'drop', @.schema_option =
0x00000000000000F3, @.status = 16, @.vertical_partition = N'false', @.ins_cmd =
N'CALL sp_MSins_tbl880Detail', @.del_cmd = N'CALL sp_MSdel_tbl880Detail',
@.upd_cmd = N'MCALL sp_MSupd_tbl880Detail', @.filter = null, @.sync_object =
null, @.auto_identity_range = N'false'
GO
exec sp_addarticle @.publication = N'EDI_On_RouseMain', @.article =
N'tbl880DetailAllow', @.source_owner = N'dbo', @.source_object =
N'tbl880DetailAllow', @.destination_table = N'tbl880DetailAllow', @.type =
N'logbased', @.creation_script = null, @.description = null, @.pre_creation_cmd
= N'drop', @.schema_option = 0x00000000000000F3, @.status = 16,
@.vertical_partition = N'false', @.ins_cmd = N'CALL
sp_MSins_tbl880DetailAllow', @.del_cmd = N'CALL sp_MSdel_tbl880DetailAllow',
@.upd_cmd = N'MCALL sp_MSupd_tbl880DetailAllow', @.filter = null, @.sync_object
= null, @.auto_identity_range = N'false'
GO
exec sp_addarticle @.publication = N'EDI_On_RouseMain', @.article =
N'tbl880Header', @.source_owner = N'dbo', @.source_object = N'tbl880Header',
@.destination_table = N'tbl880Header', @.type = N'logbased', @.creation_script =
null, @.description = null, @.pre_creation_cmd = N'drop', @.schema_option =
0x00000000000000F3, @.status = 16, @.vertical_partition = N'false', @.ins_cmd =
N'CALL sp_MSins_tbl880Header', @.del_cmd = N'CALL sp_MSdel_tbl880Header',
@.upd_cmd = N'MCALL sp_MSupd_tbl880Header', @.filter = null, @.sync_object =
null, @.auto_identity_range = N'false'
GO
exec sp_addarticle @.publication = N'EDI_On_RouseMain', @.article =
N'tbl880HeaderAllow', @.source_owner = N'dbo', @.source_object =
N'tbl880HeaderAllow', @.destination_table = N'tbl880HeaderAllow', @.type =
N'logbased', @.creation_script = null, @.description = null, @.pre_creation_cmd
= N'drop', @.schema_option = 0x00000000000000F3, @.status = 16,
@.vertical_partition = N'false', @.ins_cmd = N'CALL
sp_MSins_tbl880HeaderAllow', @.del_cmd = N'CALL sp_MSdel_tbl880HeaderAllow',
@.upd_cmd = N'MCALL sp_MSupd_tbl880HeaderAllow', @.filter = null, @.sync_object
= null, @.auto_identity_range = N'false'
GO
-- Adding the transactional subscription
exec sp_addsubscription @.publication = N'EDI_On_RouseMain', @.article =
N'all', @.subscriber = N'ROUSEONE', @.destination_db = N'EDI', @.sync_type =
N'automatic', @.update_mode = N'read only', @.offloadagent = 0,
@.dts_package_location = N'distributor'
GO
~~~~
Merge publication and push subscription from S2 to Remote location #18
~~~~
-- Enabling the replication database
use master
GO
exec sp_replicationdboption @.dbname = N'EDI', @.optname = N'merge publish',
@.value = N'true'
GO
use [EDI]
GO
-- Adding the merge publication
exec sp_addmergepublication @.publication = N'EDI - 18', @.description =
N'Merge publication of EDI database from Publisher ROUSEONE.', @.retention =
14, @.sync_mode = N'native', @.allow_push = N'true', @.allow_pull = N'true',
@.allow_anonymous = N'true', @.enabled_for_internet = N'false',
@.centralized_conflicts = N'true', @.dynamic_filters = N'false',
@.snapshot_in_defaultfolder = N'true', @.compress_snapshot = N'false',
@.ftp_port = 21, @.ftp_login = N'anonymous', @.conflict_retention = 14,
@.keep_partition_changes = N'true', @.allow_subscription_copy = N'false',
@.allow_synctoalternate = N'false', @.add_to_active_directory = N'false',
@.max_concurrent_merge = 0, @.max_concurrent_dynamic_snapshots = 0
exec sp_addpublication_snapshot @.publication = N'EDI - 18',@.frequency_type =
4, @.frequency_interval = 1, @.frequency_relative_interval = 1,
@.frequency_recurrence_factor = 0, @.frequency_subday = 1,
@.frequency_subday_interval = 5, @.active_start_date = 0, @.active_end_date = 0,
@.active_start_time_of_day = 500, @.active_end_time_of_day = 235959,
@.snapshot_job_name = N'ROUSEONE-EDI-EDI - 18-95'
GO
exec sp_grant_publication_access @.publication = N'EDI - 18', @.login =
N'BUILTIN\Administrators'
GO
exec sp_grant_publication_access @.publication = N'EDI - 18', @.login =
N'distributor_admin'
GO
exec sp_grant_publication_access @.publication = N'EDI - 18', @.login = N'sa'
GO
-- Adding the merge articles
exec sp_addmergearticle @.publication = N'EDI - 18', @.article =
N'tbl880Detail', @.source_owner = N'dbo', @.source_object = N'tbl880Detail',
@.type = N'table', @.description = null, @.column_tracking = N'true',
@.pre_creation_cmd = N'drop', @.creation_script = null, @.schema_option =
0x000000000000CFF1, @.article_resolver = null, @.subset_filterclause = N'loc =
18', @.vertical_partition = N'false', @.destination_owner = N'dbo',
@.auto_identity_range = N'false', @.verify_resolver_signature = 0,
@.allow_interactive_resolver = N'false', @.fast_multicol_updateproc = N'true',
@.check_permissions = 0
GO
exec sp_addmergearticle @.publication = N'EDI - 18', @.article =
N'tbl880DetailAllow', @.source_owner = N'dbo', @.source_object =
N'tbl880DetailAllow', @.type = N'table', @.description = null, @.column_tracking
= N'true', @.pre_creation_cmd = N'drop', @.creation_script = null,
@.schema_option = 0x000000000000CFF1, @.article_resolver = null,
@.subset_filterclause = N'loc = 18', @.vertical_partition = N'false',
@.destination_owner = N'dbo', @.auto_identity_range = N'false',
@.verify_resolver_signature = 0, @.allow_interactive_resolver = N'false',
@.fast_multicol_updateproc = N'true', @.check_permissions = 0
GO
exec sp_addmergearticle @.publication = N'EDI - 18', @.article =
N'tbl880Header', @.source_owner = N'dbo', @.source_object = N'tbl880Header',
@.type = N'table', @.description = null, @.column_tracking = N'true',
@.pre_creation_cmd = N'drop', @.creation_script = null, @.schema_option =
0x000000000000CFF1, @.article_resolver = null, @.subset_filterclause = N'loc =
18', @.vertical_partition = N'false', @.destination_owner = N'dbo',
@.auto_identity_range = N'false', @.verify_resolver_signature = 0,
@.allow_interactive_resolver = N'false', @.fast_multicol_updateproc = N'true',
@.check_permissions = 0
GO
exec sp_addmergearticle @.publication = N'EDI - 18', @.article =
N'tbl880HeaderAllow', @.source_owner = N'dbo', @.source_object =
N'tbl880HeaderAllow', @.type = N'table', @.description = null, @.column_tracking
= N'true', @.pre_creation_cmd = N'drop', @.creation_script = null,
@.schema_option = 0x000000000000CFF1, @.article_resolver = null,
@.subset_filterclause = N'loc = 18', @.vertical_partition = N'false',
@.destination_owner = N'dbo', @.auto_identity_range = N'false',
@.verify_resolver_signature = 0, @.allow_interactive_resolver = N'false',
@.fast_multicol_updateproc = N'true', @.check_permissions = 0
GO
-- Adding the merge subscription
exec sp_addmergesubscription @.publication = N'EDI - 18', @.subscriber =
N'ROUSE18SERVER', @.subscriber_db = N'EDI', @.subscription_type = N'push',
@.subscriber_type = N'local', @.subscription_priority = 0.000000, @.sync_type =
N'automatic', @.frequency_type = 4, @.frequency_interval = 1,
@.frequency_relative_interval = 1, @.frequency_recurrence_factor = 0,
@.frequency_subday = 8, @.frequency_subday_interval = 1, @.active_start_date =
0, @.active_end_date = 0, @.active_start_time_of_day = 0,
@.active_end_time_of_day = 235959, @.enabled_for_syncmgr = N'false',
@.offloadagent = 0, @.use_interactive_resolver = N'false'
GO

Missing the zero (0)

Hello all,

I hope someone can help me; if got a value in a cell (040 1234567),
when I run a query
in the Analyser I got as respons only 40 1234567, so missing the zero
(0) not the whole number is displayed. When I run a query on a cell
with value 1234567 I received the number 1234567 and that's oke. The
Data Type of the Column is Char.

Thanks,

MarkoTry it with VARCHAR , which accepts numbers and string

--

Jack
___________________________________
Post IT Jobs for FREE - www.ciquery.com
Over 14,000 registered IT focused individuals

"Marko" <nortel@.planet.nlwrote in message
news:1166012891.128359.186250@.16g2000cwy.googlegro ups.com...

Quote:

Originally Posted by

Hello all,
>
I hope someone can help me; if got a value in a cell (040 1234567),
when I run a query
in the Analyser I got as respons only 40 1234567, so missing the zero
(0) not the whole number is displayed. When I run a query on a cell
with value 1234567 I received the number 1234567 and that's oke. The
Data Type of the Column is Char.
>
Thanks,
>
Marko
>

|||Marko (nortel@.planet.nl) writes:

Quote:

Originally Posted by

I hope someone can help me; if got a value in a cell (040 1234567),
when I run a query
in the Analyser I got as respons only 40 1234567, so missing the zero
(0) not the whole number is displayed. When I run a query on a cell
with value 1234567 I received the number 1234567 and that's oke. The
Data Type of the Column is Char.

Cell? Do you mean column value in a row? Or are you running a query
against Excel?

What exact query do you run?

All I can say is that the leading zero should normally not be dropped
for a character value, which this obviously is, there is a space in it.

--
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|||Erland Sommarskog schreef:

Quote:

Originally Posted by

Marko (nortel@.planet.nl) writes:

Quote:

Originally Posted by

I hope someone can help me; if got a value in a cell (040 1234567),
when I run a query
in the Analyser I got as respons only 40 1234567, so missing the zero
(0) not the whole number is displayed. When I run a query on a cell
with value 1234567 I received the number 1234567 and that's oke. The
Data Type of the Column is Char.

>
Cell? Do you mean column value in a row? Or are you running a query
against Excel?
>
What exact query do you run?
>
All I can say is that the leading zero should normally not be dropped
for a character value, which this obviously is, there is a space in it.
>
>
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
>

Hello Erland,

It's a column value in a row, the query I run:

SELECT @.Registernum=(
SELECT regnr FROM dbo.planningview
WHERE convert(CHAR(11),date ,106) = convert(CHAR(11),GETDATE(),106)
AND
convert(CHAR(8),Starttime ,108) <= convert(CHAR(8),GETDATE(),108)
AND
convert(CHAR(8),Stoptime ,108) >= convert(CHAR(8),GETDATE(),108) AND

groupName = 'Application' AND shortname = 'Helpdesk' AND deleted =0
)

if (@.Registernum is null) Select @.Registernum=0
select @.Registernum
GO

What I get is 401234567 instaed of 0401234567, so I am missing the 0
(zero).|||Marko (nortel@.planet.nl) writes:

Quote:

Originally Posted by

It's a column value in a row, the query I run:
>
SELECT @.Registernum=(
SELECT regnr FROM dbo.planningview
WHERE convert(CHAR(11),date ,106) =
convert(CHAR(11),GETDATE(),106)
AND
convert(CHAR(8),Starttime ,108) <=
convert(CHAR(8),GETDATE(),108)
AND
convert(CHAR(8),Stoptime ,108) >=
convert(CHAR(8),GETDATE(),108) AND
>
groupName = 'Application' AND shortname = 'Helpdesk' AND
deleted =0
)
>
if (@.Registernum is null) Select @.Registernum=0
select @.Registernum
GO
>
>
What I get is 401234567 instaed of 0401234567, so I am missing the 0
(zero).

You failed to include the declaration of @.Registernum. I would guess
you have declared it as integer or decimal.

Also, write conditions like

WHERE convert(CHAR(11),date ,106) = convert(CHAR(11),GETDATE(),106)

as

WHERE date >= convert(char(8), getdate(), 112)
AND date < convert(char(8), dateadd(DAY, getdate() + 1), 112)

This can be essential for performance. If the date column is indexed,
the index is no of use if you put the column into an expression.

--
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|||Erland Sommarskog schreef:

Quote:

Originally Posted by

Marko (nortel@.planet.nl) writes:

Quote:

Originally Posted by

It's a column value in a row, the query I run:

SELECT @.Registernum=(
SELECT regnr FROM dbo.planningview
WHERE convert(CHAR(11),date ,106) =
convert(CHAR(11),GETDATE(),106)
AND
convert(CHAR(8),Starttime ,108) <=
convert(CHAR(8),GETDATE(),108)
AND
convert(CHAR(8),Stoptime ,108) >=
convert(CHAR(8),GETDATE(),108) AND

groupName = 'Application' AND shortname = 'Helpdesk' AND
deleted =0
)

if (@.Registernum is null) Select @.Registernum=0
select @.Registernum
GO

What I get is 401234567 instaed of 0401234567, so I am missing the 0
(zero).

>
You failed to include the declaration of @.Registernum. I would guess
you have declared it as integer or decimal.
>
Also, write conditions like
>
WHERE convert(CHAR(11),date ,106) = convert(CHAR(11),GETDATE(),106)
>
as
>
WHERE date >= convert(char(8), getdate(), 112)
AND date < convert(char(8), dateadd(DAY, getdate() + 1), 112)
>
This can be essential for performance. If the date column is indexed,
the index is no of use if you put the column into an expression.
>
>
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
>
>

Hello Erland,

I put the DECLARE above the Query:

DECLARE @.Registernum integer

but no results, I got still the value without the zero (0).
The Data Type of the Column is CHAR, is this maybe wrong?|||Marko (nortel@.planet.nl) writes:

Quote:

Originally Posted by

I put the DECLARE above the Query:
>
DECLARE @.Registernum integer
>
but no results, I got still the value without the zero (0).
The Data Type of the Column is CHAR, is this maybe wrong?

Your result set consists of a single integer variable. QA presents integer
values without leading zeroes. How could it now that the integer value
comes from a char column?

If you want to see a leading zero, you should declare the variable as
char or varchar of suitable length.

--
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

Missing SELECT statement, but where?

Hi
I have created a question in Access and then copied the SQL question from it.
When I try to run it in eg SQL plus I get an error. I can't see what's wrong.
SQL> SELECT MC_POSUM.ID, MC_POLINE.ID, MC_INVTRANS.TQTY_AMT, MC_PCITEM.ITID, MC_INVTRANS.PRSD_DTTM,
MC_EMPLOYEE.AENM
2 FROM ((((((MC_POSUM INNER JOIN MC_POLINE ON MC_POSUM.POSUMOI = MC_POLINE.PO_OI) INNER JOIN MC_P
ODEL ON MC_POLINE.POLNOI = MC_PODEL.POLINE_OI) INNER JOIN MC_INVTRANS ON MC_PODEL.PODELOI = MC_INVTR
ANS.PODEL_OI) INNER JOIN MC_EMPLOYEE ON MC_INVTRANS.AUDT_USER_OI = MC_EMPLOYEE.EMPOI) INNER JOIN MC_
PCITEM ON MC_POLINE.PCITEM_OI = MC_PCITEM.PCITOI) LEFT JOIN MC_STOREROOM ON MC_INVTRANS.STOR_OI = MC
_STOREROOM.STOROI) INNER JOIN MC_EMPLOYEE AS MC_EMPLOYEE_1 ON MC_POSUM.BUY_OI = MC_EMPLOYEE_1.EMPOI
3 WHERE (((MC_EMPLOYEE_1.AENM) Not Like [MC_EMPLOYEE.AENM]) AND ((MC_STOREROOM.STOROI) Not Like 3
2050) AND ((MC_INVTRANS.TRNTYP) Like 5));
FROM ((((((MC_POSUM INNER JOIN MC_POLINE ON MC_POSUM.POSUMOI = MC_POLINE.PO_OI) INNER JOIN MC_PODEL
*
ERROR at line 2:
ORA-00928: missing SELECT keyword
hi christina,
First off this is a SQL Server newsgroup. So try putting your question in some of the oracle
newgroup/forum. However, with some oracle knowledge i've here are little annotations on my
part.
As far as i know ansi sql syntaxes are only available in oracle from oracle9i so if you are
using oralce8i this is not supported.
Also, enclosing identifiers in square brackets is syntactically correct in SQL Server and not
in Oracle. See following corrected query(should work in oracle9i).
SELECT MC_POSUM.POSUMOI
FROM
--one bracket missing below
(((((((MC_POSUM INNER JOIN MC_POLINE ON MC_POSUM.POSUMOI = MC_POLINE.PO_OI) INNER JOIN
MC_PODEL ON MC_POLINE.POLNOI = MC_PODEL.POLINE_OI) INNER JOIN MC_INVTRANS ON MC_PODEL.PODELOI
= MC_INVTRANS.PODEL_OI) INNER JOIN MC_EMPLOYEE ON MC_INVTRANS.AUDT_USER_OI =
MC_EMPLOYEE.EMPOI) INNER JOIN MC_PCITEM ON MC_POLINE.PCITEM_OI = MC_PCITEM.PCITOI) LEFT JOIN
MC_STOREROOM ON MC_INVTRANS.STOR_OI = MC_STOREROOM.STOROI) INNER JOIN MC_EMPLOYEE
MC_EMPLOYEE_1 ON MC_POSUM.BUY_OI = MC_EMPLOYEE_1.EMPOI) --remove as
WHERE (((MC_EMPLOYEE_1.AENM) Not Like MC_EMPLOYEE.AENM) AND ((MC_STOREROOM.STOROI) Not Like
32050) AND ((MC_INVTRANS.TRNTYP) Like 5)) --no square bracket required.
--End of query
MC_PODEL
Also once where clause is over it is syntactically incorrect to use FROM clause again.
Vishal Parkar
vgparkar@.yahoo.co.in

Missing Record - Phantom Record

Hi All,

Have come across something weird and am after some help.

Say i run this query where rec_id is a column of table arlhrl,

select * from arlhrl where rec_id >= 14260

This returns to me 2 records with rec_id's of 14260 and 14261

Then I run this query

select * from arlhrl where rec_id >= 14263

This returns 7 records with rec_ids of 14263 up.

How come the first query doesn't return the records returned by the
2nd query also?

If I select for 14262 no records are returned. It is like this is a
phantom record or has an end of file character in it.

I tried re-creating the indexes but to no avail. If anyone has any
ideas about what could be causing it or how to fix it it would be much
appreciated.

Thanks in advance,

AndrewAndrew wrote:
> Hi All,
> Have come across something weird and am after some help.
> Say i run this query where rec_id is a column of table arlhrl,
> select * from arlhrl where rec_id >= 14260
> This returns to me 2 records with rec_id's of 14260 and 14261
> Then I run this query
> select * from arlhrl where rec_id >= 14263
> This returns 7 records with rec_ids of 14263 up.
> How come the first query doesn't return the records returned by the
> 2nd query also?
> If I select for 14262 no records are returned. It is like this is a
> phantom record or has an end of file character in it.
> I tried re-creating the indexes but to no avail. If anyone has any
> ideas about what could be causing it or how to fix it it would be much
> appreciated.
> Thanks in advance,
> Andrew

Hi,

First, stupid question - is the field 'rec_id' of integer type?
Why i am asking is because i had a similar example myself when i started with my new job - i was
quering an id field and got weird results as you do. Then i found that some 'smart ass' made this
comlumn a varchar for no reason - just because she was doing like that in Access all the time before :)

Second, what i'd do when i get into an unexplainable glitch:

SELECT * INTO <new table> FROM <your table
And try to query the records from the new table without setting any indexes - just as is - as you
know SELECT INTO just copies raw data without any underlying stuff.
See what you'll get.
From my experience there are a of of people who are allowed to mess with SQL databases but don't
have a clue what they are doing, and when you start using their 'smart ideas' sometimes it's just
hard to follow their logic :) So maybe some setting were set a wrong way somewhere, you can never
imagine what another person could do - believe me, i just got quite a few awsome examples within the
last month since i got this job :)

Let me know how it works!

Andrey|||Andrey <leyandrew@.yahoo.com> wrote in message news:<7wt3d.78769$D%.11878@.attbi_s51>...
> Andrew wrote:
> > Hi All,
> > Have come across something weird and am after some help.
> > Say i run this query where rec_id is a column of table arlhrl,
> > select * from arlhrl where rec_id >= 14260
> > This returns to me 2 records with rec_id's of 14260 and 14261
> > Then I run this query
> > select * from arlhrl where rec_id >= 14263
> > This returns 7 records with rec_ids of 14263 up.
> > How come the first query doesn't return the records returned by the
> > 2nd query also?
> > If I select for 14262 no records are returned. It is like this is a
> > phantom record or has an end of file character in it.
> > I tried re-creating the indexes but to no avail. If anyone has any
> > ideas about what could be causing it or how to fix it it would be much
> > appreciated.
> > Thanks in advance,
> > Andrew
>
> Hi,
> First, stupid question - is the field 'rec_id' of integer type?
> Why i am asking is because i had a similar example myself when i started with my new job - i was
> quering an id field and got weird results as you do. Then i found that some 'smart ass' made this
> comlumn a varchar for no reason - just because she was doing like that in Access all the time before :)
>
> Second, what i'd do when i get into an unexplainable glitch:
> SELECT * INTO <new table> FROM <your table>
> And try to query the records from the new table without setting any indexes - just as is - as you
> know SELECT INTO just copies raw data without any underlying stuff.
> See what you'll get.
> From my experience there are a of of people who are allowed to mess with SQL databases but don't
> have a clue what they are doing, and when you start using their 'smart ideas' sometimes it's just
> hard to follow their logic :) So maybe some setting were set a wrong way somewhere, you can never
> imagine what another person could do - believe me, i just got quite a few awsome examples within the
> last month since i got this job :)
> Let me know how it works!
> Andrey

Hi Andrey,

Thanks for your reply. I tried as you mentioned, inserting into new
table etc but to no avail. I did figure out what the problem was
though.

This particular table had been upsized from a foxpro table. One of the
columns in the foxpro table had a maximum value of numeric 9999.
Somehow, someone had tried to insert a value large than this so foxpro
put in ****. On the upsize, and I can only assume here, sql must have
thought 'hang on, you must mean infinity here' and put a bit-wise
pattern (1.#INF) for infinity into this particular column for the
record.

This only became evident when using Enterprise Manager and returning
all rows on the given table, it did display the record with the value
1.#INF in the column for the 'missing' record. As to why it displayed
in EM and not Query Analyser is anyone's guess, but surely the queries
that led me to this initial discovery shouldn't have behaved like
this!!??

posting

http://groups.google.com/groups?q=%...le .com&rnum=1

gives some ideas.

Thanks anyway,

Andrew|||Andrew wrote:

> Andrey <leyandrew@.yahoo.com> wrote in message news:<7wt3d.78769$D%.11878@.attbi_s51>...
>>Andrew wrote:
>>
>>>Hi All,
>>>
>>>Have come across something weird and am after some help.
>>>
>>>Say i run this query where rec_id is a column of table arlhrl,
>>>
>>>select * from arlhrl where rec_id >= 14260
>>>
>>>This returns to me 2 records with rec_id's of 14260 and 14261
>>>
>>>Then I run this query
>>>
>>>select * from arlhrl where rec_id >= 14263
>>>
>>>This returns 7 records with rec_ids of 14263 up.
>>>
>>>How come the first query doesn't return the records returned by the
>>>2nd query also?
>>>
>>>If I select for 14262 no records are returned. It is like this is a
>>>phantom record or has an end of file character in it.
>>>
>>>I tried re-creating the indexes but to no avail. If anyone has any
>>>ideas about what could be causing it or how to fix it it would be much
>>>appreciated.
>>>
>>>Thanks in advance,
>>>
>>>Andrew
>>
>>
>>Hi,
>>
>>First, stupid question - is the field 'rec_id' of integer type?
>>Why i am asking is because i had a similar example myself when i started with my new job - i was
>>quering an id field and got weird results as you do. Then i found that some 'smart ass' made this
>>comlumn a varchar for no reason - just because she was doing like that in Access all the time before :)
>>
>>
>>Second, what i'd do when i get into an unexplainable glitch:
>>
>>SELECT * INTO <new table> FROM <your table>
>>
>>And try to query the records from the new table without setting any indexes - just as is - as you
>>know SELECT INTO just copies raw data without any underlying stuff.
>>See what you'll get.
>> From my experience there are a of of people who are allowed to mess with SQL databases but don't
>>have a clue what they are doing, and when you start using their 'smart ideas' sometimes it's just
>>hard to follow their logic :) So maybe some setting were set a wrong way somewhere, you can never
>>imagine what another person could do - believe me, i just got quite a few awsome examples within the
>>last month since i got this job :)
>>
>>Let me know how it works!
>>
>>Andrey
>
> Hi Andrey,
> Thanks for your reply. I tried as you mentioned, inserting into new
> table etc but to no avail. I did figure out what the problem was
> though.
> This particular table had been upsized from a foxpro table. One of the
> columns in the foxpro table had a maximum value of numeric 9999.
> Somehow, someone had tried to insert a value large than this so foxpro
> put in ****. On the upsize, and I can only assume here, sql must have
> thought 'hang on, you must mean infinity here' and put a bit-wise
> pattern (1.#INF) for infinity into this particular column for the
> record.
> This only became evident when using Enterprise Manager and returning
> all rows on the given table, it did display the record with the value
> 1.#INF in the column for the 'missing' record. As to why it displayed
> in EM and not Query Analyser is anyone's guess, but surely the queries
> that led me to this initial discovery shouldn't have behaved like
> this!!??
> posting
> http://groups.google.com/groups?q=%...le .com&rnum=1
> gives some ideas.
> Thanks anyway,
> Andrew

Well, EM and QA might show you diferent results because they are using diferent methods of 'talking'
to sql server.
QA is using isql.com, precisely it's isqlw.com version, which is an old DB lib based way of connection.
EM, i guess, is using ODBC or OLEDB connection.

I also had a headache not long time ago, when i used sql console tools to make Python work with sql
server. I had a table with varcha fields which had around couple thousand characters of text each.

When i used isql.com to retreive those text records, text returned truncated, around 300 to 600
characters left.. SO i started using osql.com instead, and no headache.

So resume is - every time you're in doubt, use both EM and QA

PS. BTW, I didn't know sql server can store 'infinity' values. Thanks for the info!

WYGL,
Andrey|||Andrew wrote:

> Andrey <leyandrew@.yahoo.com> wrote in message news:<7wt3d.78769$D%.11878@.attbi_s51>...
>>Andrew wrote:
>>
>>>Hi All,
>>>
>>>Have come across something weird and am after some help.
>>>
>>>Say i run this query where rec_id is a column of table arlhrl,
>>>
>>>select * from arlhrl where rec_id >= 14260
>>>
>>>This returns to me 2 records with rec_id's of 14260 and 14261
>>>
>>>Then I run this query
>>>
>>>select * from arlhrl where rec_id >= 14263
>>>
>>>This returns 7 records with rec_ids of 14263 up.
>>>
>>>How come the first query doesn't return the records returned by the
>>>2nd query also?
>>>
>>>If I select for 14262 no records are returned. It is like this is a
>>>phantom record or has an end of file character in it.
>>>
>>>I tried re-creating the indexes but to no avail. If anyone has any
>>>ideas about what could be causing it or how to fix it it would be much
>>>appreciated.
>>>
>>>Thanks in advance,
>>>
>>>Andrew
>>
>>
>>Hi,
>>
>>First, stupid question - is the field 'rec_id' of integer type?
>>Why i am asking is because i had a similar example myself when i started with my new job - i was
>>quering an id field and got weird results as you do. Then i found that some 'smart ass' made this
>>comlumn a varchar for no reason - just because she was doing like that in Access all the time before :)
>>
>>
>>Second, what i'd do when i get into an unexplainable glitch:
>>
>>SELECT * INTO <new table> FROM <your table>
>>
>>And try to query the records from the new table without setting any indexes - just as is - as you
>>know SELECT INTO just copies raw data without any underlying stuff.
>>See what you'll get.
>> From my experience there are a of of people who are allowed to mess with SQL databases but don't
>>have a clue what they are doing, and when you start using their 'smart ideas' sometimes it's just
>>hard to follow their logic :) So maybe some setting were set a wrong way somewhere, you can never
>>imagine what another person could do - believe me, i just got quite a few awsome examples within the
>>last month since i got this job :)
>>
>>Let me know how it works!
>>
>>Andrey
>
> Hi Andrey,
> Thanks for your reply. I tried as you mentioned, inserting into new
> table etc but to no avail. I did figure out what the problem was
> though.
> This particular table had been upsized from a foxpro table. One of the
> columns in the foxpro table had a maximum value of numeric 9999.
> Somehow, someone had tried to insert a value large than this so foxpro
> put in ****. On the upsize, and I can only assume here, sql must have
> thought 'hang on, you must mean infinity here' and put a bit-wise
> pattern (1.#INF) for infinity into this particular column for the
> record.
> This only became evident when using Enterprise Manager and returning
> all rows on the given table, it did display the record with the value
> 1.#INF in the column for the 'missing' record. As to why it displayed
> in EM and not Query Analyser is anyone's guess, but surely the queries
> that led me to this initial discovery shouldn't have behaved like
> this!!??
> posting
> http://groups.google.com/groups?q=%...le .com&rnum=1
> gives some ideas.
> Thanks anyway,
> Andrew

And how did you get rid of that infinity value in the in field?|||I got rid of the infinity value using EM open table then typed in the
value I wanted.

Andrey <leyandrew@.yahoo.com> wrote in message news:<pir4d.28770$wV.19066@.attbi_s54>...
> Andrew wrote:
> > Andrey <leyandrew@.yahoo.com> wrote in message news:<7wt3d.78769$D%.11878@.attbi_s51>...
> >>Andrew wrote:
> >>
> >>>Hi All,
> >>>
> >>>Have come across something weird and am after some help.
> >>>
> >>>Say i run this query where rec_id is a column of table arlhrl,
> >>>
> >>>select * from arlhrl where rec_id >= 14260
> >>>
> >>>This returns to me 2 records with rec_id's of 14260 and 14261
> >>>
> >>>Then I run this query
> >>>
> >>>select * from arlhrl where rec_id >= 14263
> >>>
> >>>This returns 7 records with rec_ids of 14263 up.
> >>>
> >>>How come the first query doesn't return the records returned by the
> >>>2nd query also?
> >>>
> >>>If I select for 14262 no records are returned. It is like this is a
> >>>phantom record or has an end of file character in it.
> >>>
> >>>I tried re-creating the indexes but to no avail. If anyone has any
> >>>ideas about what could be causing it or how to fix it it would be much
> >>>appreciated.
> >>>
> >>>Thanks in advance,
> >>>
> >>>Andrew
> >>
> >>
> >>Hi,
> >>
> >>First, stupid question - is the field 'rec_id' of integer type?
> >>Why i am asking is because i had a similar example myself when i started with my new job - i was
> >>quering an id field and got weird results as you do. Then i found that some 'smart ass' made this
> >>comlumn a varchar for no reason - just because she was doing like that in Access all the time before :)
> >>
> >>
> >>Second, what i'd do when i get into an unexplainable glitch:
> >>
> >>SELECT * INTO <new table> FROM <your table>
> >>
> >>And try to query the records from the new table without setting any indexes - just as is - as you
> >>know SELECT INTO just copies raw data without any underlying stuff.
> >>See what you'll get.
> >> From my experience there are a of of people who are allowed to mess with SQL databases but don't
> >>have a clue what they are doing, and when you start using their 'smart ideas' sometimes it's just
> >>hard to follow their logic :) So maybe some setting were set a wrong way somewhere, you can never
> >>imagine what another person could do - believe me, i just got quite a few awsome examples within the
> >>last month since i got this job :)
> >>
> >>Let me know how it works!
> >>
> >>Andrey
> > Hi Andrey,
> > Thanks for your reply. I tried as you mentioned, inserting into new
> > table etc but to no avail. I did figure out what the problem was
> > though.
> > This particular table had been upsized from a foxpro table. One of the
> > columns in the foxpro table had a maximum value of numeric 9999.
> > Somehow, someone had tried to insert a value large than this so foxpro
> > put in ****. On the upsize, and I can only assume here, sql must have
> > thought 'hang on, you must mean infinity here' and put a bit-wise
> > pattern (1.#INF) for infinity into this particular column for the
> > record.
> > This only became evident when using Enterprise Manager and returning
> > all rows on the given table, it did display the record with the value
> > 1.#INF in the column for the 'missing' record. As to why it displayed
> > in EM and not Query Analyser is anyone's guess, but surely the queries
> > that led me to this initial discovery shouldn't have behaved like
> > this!!??
> > posting
> > http://groups.google.com/groups?q=%...le .com&rnum=1
> > gives some ideas.
> > Thanks anyway,
> > Andrew
>
> And how did you get rid of that infinity value in the in field?

missing password

Hi there,
I have run up a WSUS server and used msde for the sql database and
sods law I thought I'd be clever and use a different password to
normal which at the time I thought I'd remember and I've completely
forgot it.
By any chance does anyone know of a way I can now retrieve this as I
would like to link up the server in enterprise manager.
Any help would be appreciated, even if its too say I've got no chance.
Many thanks.Login to MSDE using windows authentication and change the password.
Steps:-
1. Login into MSDE machine using a Admin account
2. go to command prompt
3. Type OSQL -SSQL_Servername -E (enter - This will got to a SQL prompt)
4. Execute SP_Password NULL,'New_sa_password','SA'
This will change the SA password to the password you provide in step 4.
Thanks
Hari
"CJC" wrote:

> Hi there,
> I have run up a WSUS server and used msde for the sql database and
> sods law I thought I'd be clever and use a different password to
> normal which at the time I thought I'd remember and I've completely
> forgot it.
> By any chance does anyone know of a way I can now retrieve this as I
> would like to link up the server in enterprise manager.
> Any help would be appreciated, even if its too say I've got no chance.
> Many thanks.
>|||Thanks for your reply, I have only had chance to look at it.
This command for changing the password, will it matter what username I
initially used when setting it up, as I probably didnt use SA? Or is
this command saying I am creating this password for this username, so
in theory could I put 'joe.bloggs' instead of 'sa'
Also I'm terrible for being over cautious, is this likely to screw
anything else up, as currently the server is running fine, its just I
cannot link WSUS into enterprise manager.
Thanks again.
On 2 Feb, 20:54, Hari Prasad <HariPra...@.discussions.microsoft.com>
wrote:[vbcol=seagreen]
> Login to MSDE using windows authentication and change the password.
> Steps:-
> 1. Login into MSDE machine using a Admin account
> 2. go to command prompt
> 3. Type OSQL -SSQL_Servername -E (enter - This will got to a SQL prompt)
> 4. Execute SP_Password NULL,'New_sa_password','SA'
> This will change the SA password to the password you provide in step 4.
> Thanks
> Hari
> "CJC" wrote:
>
>
>
>

missing password

Hi there,
I have run up a WSUS server and used msde for the sql database and
sods law I thought I'd be clever and use a different password to
normal which at the time I thought I'd remember and I've completely
forgot it.
By any chance does anyone know of a way I can now retrieve this as I
would like to link up the server in enterprise manager.
Any help would be appreciated, even if its too say I've got no chance.
Many thanks.
Thanks for your reply, I have only had chance to look at it.
This command for changing the password, will it matter what username I
initially used when setting it up, as I probably didnt use SA? Or is
this command saying I am creating this password for this username, so
in theory could I put 'joe.bloggs' instead of 'sa'
Also I'm terrible for being over cautious, is this likely to screw
anything else up, as currently the server is running fine, its just I
cannot link WSUS into enterprise manager.
Thanks again.
On 2 Feb, 20:54, Hari Prasad <HariPra...@.discussions.microsoft.com>
wrote:[vbcol=seagreen]
> Login to MSDE using windows authentication and change the password.
> Steps:-
> 1. Login into MSDE machine using a Admin account
> 2. go to command prompt
> 3. Type OSQL -SSQL_Servername -E (enter - This will got to a SQL prompt)
> 4. Execute SP_Password NULL,'New_sa_password','SA'
> This will change the SA password to the password you provide in step 4.
> Thanks
> Hari
> "CJC" wrote:
>
>

missing password

Hi there,
I have run up a WSUS server and used msde for the sql database and
sods law I thought I'd be clever and use a different password to
normal which at the time I thought I'd remember and I've completely
forgot it.
By any chance does anyone know of a way I can now retrieve this as I
would like to link up the server in enterprise manager.
Any help would be appreciated, even if its too say I've got no chance.
Many thanks.Login to MSDE using windows authentication and change the password.
Steps:-
1. Login into MSDE machine using a Admin account
2. go to command prompt
3. Type OSQL -SSQL_Servername -E (enter - This will got to a SQL prompt)
4. Execute SP_Password NULL,'New_sa_password','SA'
This will change the SA password to the password you provide in step 4.
Thanks
Hari
"CJC" wrote:
> Hi there,
> I have run up a WSUS server and used msde for the sql database and
> sods law I thought I'd be clever and use a different password to
> normal which at the time I thought I'd remember and I've completely
> forgot it.
> By any chance does anyone know of a way I can now retrieve this as I
> would like to link up the server in enterprise manager.
> Any help would be appreciated, even if its too say I've got no chance.
> Many thanks.
>|||Thanks for your reply, I have only had chance to look at it.
This command for changing the password, will it matter what username I
initially used when setting it up, as I probably didnt use SA? Or is
this command saying I am creating this password for this username, so
in theory could I put 'joe.bloggs' instead of 'sa'
Also I'm terrible for being over cautious, is this likely to screw
anything else up, as currently the server is running fine, its just I
cannot link WSUS into enterprise manager.
Thanks again.
On 2 Feb, 20:54, Hari Prasad <HariPra...@.discussions.microsoft.com>
wrote:
> Login to MSDE using windows authentication and change the password.
> Steps:-
> 1. Login into MSDE machine using a Admin account
> 2. go to command prompt
> 3. Type OSQL -SSQL_Servername -E (enter - This will got to a SQL prompt)
> 4. Execute SP_Password NULL,'New_sa_password','SA'
> This will change the SA password to the password you provide in step 4.
> Thanks
> Hari
> "CJC" wrote:
> > Hi there,
> > I have run up a WSUS server and used msde for the sql database and
> > sods law I thought I'd be clever and use a different password to
> > normal which at the time I thought I'd remember and I've completely
> > forgot it.
> > By any chance does anyone know of a way I can now retrieve this as I
> > would like to link up the server in enterprise manager.
> > Any help would be appreciated, even if its too say I've got no chance.
> > Many thanks.

missing Microsoft.SqlServer.ManagedDTS

Hi

I am trying to run a SSIS package first time through vb.net console application. As the first step, i was trying to add the reference for Microsoft.SqlServer.ManagedDTS assembly but it was not in the available assemblies list box in visual studio 2003!

Please guide what i am supposed to do to get it, or if there any other alternative.

Thanks in advance

Utsav

In the Add Reference dialog window, click on the browse tab. Then navigate your way to:

C:\Program Files\Microsoft SQL Server\90\SDK\Assemblies

There you will find the Microsoft.SQLServer.ManagedDTS.dll, select it and hit okay...

|||ManagedDTS runs in process. If the console app is running on a machine that does not have SSIS loaded on it you will not be able to reference the dll. The application will also crash at the point that you attempt to access DTS objects. This also causes problems if you are executing packages that contain static paths. Because the package runs on the client machine, the paths will be assumed to be local. My solution to this was to create a web service that executes the packages on the server. Unfortunately, this aproach seems to be a huge chore, as I am having problems passing the credentials to the package app object. If anyone has any experience with this, some guidance would be greatly appreciated.|||See Michael Entin's recent post on this (http://blogs.msdn.com/michen/default.aspx).

missing Microsoft.SqlServer.ManagedDTS

Hi

I am trying to run a SSIS package first time through vb.net console application. As the first step, i was trying to add the reference for Microsoft.SqlServer.ManagedDTS assembly but it was not in the available assemblies list box in visual studio 2003!

Please guide what i am supposed to do to get it, or if there any other alternative.

Thanks in advance

Utsav

In the Add Reference dialog window, click on the browse tab. Then navigate your way to:

C:\Program Files\Microsoft SQL Server\90\SDK\Assemblies

There you will find the Microsoft.SQLServer.ManagedDTS.dll, select it and hit okay...

|||ManagedDTS runs in process. If the console app is running on a machine that does not have SSIS loaded on it you will not be able to reference the dll. The application will also crash at the point that you attempt to access DTS objects. This also causes problems if you are executing packages that contain static paths. Because the package runs on the client machine, the paths will be assumed to be local. My solution to this was to create a web service that executes the packages on the server. Unfortunately, this aproach seems to be a huge chore, as I am having problems passing the credentials to the package app object. If anyone has any experience with this, some guidance would be greatly appreciated.|||See Michael Entin's recent post on this (http://blogs.msdn.com/michen/default.aspx).