xp cmdshell: March 2012

Friday, March 30, 2012

Problem scheduling processing

When I manually run the IS packages to load the data warehouse and then process the dimensions and cubes, everything works fine.

When I schedule the packages as jobs, eventually the data goes wrong. The jobs are scheduled in the correct order.

Any ideas?

Thanks.

If I get your description right, the SSIS packages are not executed in the correct order. Is that so?

For one you can use serveral other ways to process your dimensions and cubes: You can use ascmd command line utility to process exactly the cubes and dimensions. Then wou can schedule a SQL agent job to run ascmd.

You can also use SQL Agent jobs directly to send processing commands to Analysis Serivces.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||

eventually the data goes wrong.

What do you mean by "goes wrong" is it possible that it is just not updated, or not fully updated?

One common trap when scheduling jobs is that usually the scheduled instance runs under a different user account. You should double check that this account has all the appropriate priviledges. Sometimes logging on to your workstation with the same account that the SQL Agent is running under and running the packages interactively can help to highlight any issues. You might find things like network drive mappings are not the same, or privileges to access other databases are not the same your own account, or even that the account in question does not have rights to process the SSAS database.

Problem scheduling job

I have a script that I wrote (please pardon my less than beautiful code) to
dump some aggregate data into a table for reporting purposes. It populates
a
few temp tables, then inserts into the destination table.
This script works fine in QA, but when I try to run it from a SA Job it
fails with the following error:
Line 12: Incorrect syntax near '20050128'. [SQLSTATE 42000] (Error 170).
NOTE: The step was retried the requested number of times (1) without
succeeding. The step failed.
Here's my script:
--begin script
USE NGEPMProd
GO
SELECT TTM.desc_30 AS TaskType, TU.user_id AS Owner, TU.createdate,T.status
INTO #NotStarted
FROM tasks T
INNER JOIN task_type_mstr TTM ON
TTM.task_type_id = T.task_type_id
INNER JOIN task_users_assigned_to TU ON
TU.task_id = T.task_id
WHERE t.status = 1
ORDER BY TU.user_id ASC
SELECT Owner, COUNT(OWNER) AS NumberNotStarted, CONVERT(char(10), GETDATE(),
101)AS [DATE]
INTO #NSTotals
FROM #NotStarted
GROUP BY owner
ORDER BY OWNER ASC
----
--
USE NGEPMProd
GO
SELECT TTM.desc_30 AS TaskType, TU.user_id AS Owner, TU.createdate,T.status
INTO #InProgress
FROM tasks T
INNER JOIN task_type_mstr TTM ON
TTM.task_type_id = T.task_type_id
INNER JOIN task_users_assigned_to TU ON
TU.task_id = T.task_id
WHERE t.status = 3
ORDER BY TU.user_id ASC
SELECT Owner, COUNT(OWNER) AS NumberInProgress,CONVERT(char(10), GETDATE(),
101)AS [DATE]
INTO #ProgTotals
FROM #InProgress
GROUP BY owner
ORDER BY OWNER ASC
----
-
USE NGEPMProd
GO
SELECT TTM.desc_30 AS TaskType, TU.user_id AS Owner, TU.createdate,T.status,
CONVERT(char(10), T.followup_date, 101)AS FUDate
INTO #Overdue
FROM tasks T
INNER JOIN task_type_mstr TTM ON
TTM.task_type_id = T.task_type_id
INNER JOIN task_users_assigned_to TU ON
TU.task_id = T.task_id
WHERE T.status = '3'
ORDER BY TU.user_id ASC
SELECT Owner, COUNT(OWNER) AS NumberOverdue, CONVERT(char(10), GETDATE(),
101)AS [DATE]
INTO #ODTotals
FROM #Overdue
WHERE FUDate < GETDATE()
GROUP BY owner
ORDER BY OWNER ASC
----
-
USE NGEPMProd
GO
SELECT TTM.desc_30 AS TaskType, TU.user_id AS Owner, TU.createdate,T.status,
CONVERT(char(10), T.completion_date, 101) AS CompletedDate
INTO #Completed
FROM tasks T
INNER JOIN task_type_mstr TTM ON
TTM.task_type_id = T.task_type_id
INNER JOIN task_users_assigned_to TU ON
TU.task_id = T.task_id
WHERE t.status = 2
--att tasks completed in the last 24 hours
AND T.completion_date >= DATEADD(hh, -24,GETDATE())-- or enter a specific
date like this '20050127'
ORDER BY TU.user_id ASC
SELECT Owner, COUNT(OWNER) AS NumberCompleted, CONVERT(char(10), GETDATE(),
101)AS [DATE]
INTO #CompTotals
FROM #Completed
GROUP BY owner
ORDER BY OWNER ASC
----
---
--insert records into permanent table for reports
INSERT INTO task_totals_collector
SELECT UM.user_id AS Employee, NS.NumberNotStarted AS NotStarted,
PT.NumberInProgress AS InProgress,CT.NumberCompleted AS Completed,
CONVERT(char(10), GETDATE(), 101)AS CalcDate, OT.NumberOverdue AS Overdue
FROM user_mstr UM
LEFT JOIN #NSTotals NS
ON UM.user_id=NS.owner
LEFT JOIN #ProgTotals PT ON
UM.user_id=PT.owner
LEFT JOIN #CompTotals CT ON
UM.user_id=CT.owner
LEFT JOIN #ODTotals OT ON
UM.user_id=OT.owner
ORDER BY UM.user_id ASC
--end of script
Patrick Rouse
Microsoft MVP - Terminal Server
http://www.workthin.com"Patrick Rouse" <PatrickRouse@.discussions.microsoft.com> wrote in message
news:F723A708-5FF4-4E8A-9F41-38A49BB08BE4@.microsoft.com...
> --att tasks completed in the last 24 hours
> AND T.completion_date >= DATEADD(hh, -24,GETDATE())-- or enter a specific
> date like this '20050127'
Patrick,
The only thing I can see that might be causing that error is this part; I'm
not sure if this is just wrapped because of the news client, though... You
should double-check that to make sure. I can't find the string '20050128'
in your code anywhere... Do you have some dynamic SQL that's not posted?
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--|||No, I think it's getting this from GETDATE(). The database I'm working with
has a lot of dates stored as CHAR(8), which drives me nuts, as I can ORDER B
Y
on these columns. In my query I CONVERT them to DATETIME, but they're still
formatted as 'YYYYMMDD'.
"Adam Machanic" wrote:

> "Patrick Rouse" <PatrickRouse@.discussions.microsoft.com> wrote in message
> news:F723A708-5FF4-4E8A-9F41-38A49BB08BE4@.microsoft.com...
> Patrick,
> The only thing I can see that might be causing that error is this part; I'
m
> not sure if this is just wrapped because of the news client, though... You
> should double-check that to make sure. I can't find the string '20050128'
> in your code anywhere... Do you have some dynamic SQL that's not posted?
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>
>|||One thing I can suggest is to put brackets ( [ ] ) around all your table and
column names as you have a lot of reserved words as object names.
Andrew J. Kelly SQL MVP
"Patrick Rouse" <PatrickRouse@.discussions.microsoft.com> wrote in message
news:F723A708-5FF4-4E8A-9F41-38A49BB08BE4@.microsoft.com...
>I have a script that I wrote (please pardon my less than beautiful code) to
> dump some aggregate data into a table for reporting purposes. It
> populates a
> few temp tables, then inserts into the destination table.
> This script works fine in QA, but when I try to run it from a SA Job it
> fails with the following error:
> Line 12: Incorrect syntax near '20050128'. [SQLSTATE 42000] (Error 170).
> NOTE: The step was retried the requested number of times (1) without
> succeeding. The step failed.
>
> Here's my script:
> --begin script
> USE NGEPMProd
> GO
> SELECT TTM.desc_30 AS TaskType, TU.user_id AS Owner,
> TU.createdate,T.status
> INTO #NotStarted
> FROM tasks T
> INNER JOIN task_type_mstr TTM ON
> TTM.task_type_id = T.task_type_id
> INNER JOIN task_users_assigned_to TU ON
> TU.task_id = T.task_id
> WHERE t.status = 1
> ORDER BY TU.user_id ASC
> SELECT Owner, COUNT(OWNER) AS NumberNotStarted, CONVERT(char(10),
> GETDATE(),
> 101)AS [DATE]
> INTO #NSTotals
> FROM #NotStarted
> GROUP BY owner
> ORDER BY OWNER ASC
> ----
--
> USE NGEPMProd
> GO
> SELECT TTM.desc_30 AS TaskType, TU.user_id AS Owner,
> TU.createdate,T.status
> INTO #InProgress
> FROM tasks T
> INNER JOIN task_type_mstr TTM ON
> TTM.task_type_id = T.task_type_id
> INNER JOIN task_users_assigned_to TU ON
> TU.task_id = T.task_id
> WHERE t.status = 3
> ORDER BY TU.user_id ASC
> SELECT Owner, COUNT(OWNER) AS NumberInProgress,CONVERT(char(10),
> GETDATE(),
> 101)AS [DATE]
> INTO #ProgTotals
> FROM #InProgress
> GROUP BY owner
> ORDER BY OWNER ASC
> ----
--
> USE NGEPMProd
> GO
> SELECT TTM.desc_30 AS TaskType, TU.user_id AS Owner,
> TU.createdate,T.status,
> CONVERT(char(10), T.followup_date, 101)AS FUDate
> INTO #Overdue
> FROM tasks T
> INNER JOIN task_type_mstr TTM ON
> TTM.task_type_id = T.task_type_id
> INNER JOIN task_users_assigned_to TU ON
> TU.task_id = T.task_id
> WHERE T.status = '3'
> ORDER BY TU.user_id ASC
> SELECT Owner, COUNT(OWNER) AS NumberOverdue, CONVERT(char(10), GETDATE(),
> 101)AS [DATE]
> INTO #ODTotals
> FROM #Overdue
> WHERE FUDate < GETDATE()
> GROUP BY owner
> ORDER BY OWNER ASC
> ----
--
> USE NGEPMProd
> GO
> SELECT TTM.desc_30 AS TaskType, TU.user_id AS Owner,
> TU.createdate,T.status,
> CONVERT(char(10), T.completion_date, 101) AS CompletedDate
> INTO #Completed
> FROM tasks T
> INNER JOIN task_type_mstr TTM ON
> TTM.task_type_id = T.task_type_id
> INNER JOIN task_users_assigned_to TU ON
> TU.task_id = T.task_id
> WHERE t.status = 2
> --att tasks completed in the last 24 hours
> AND T.completion_date >= DATEADD(hh, -24,GETDATE())-- or enter a specific
> date like this '20050127'
> ORDER BY TU.user_id ASC
> SELECT Owner, COUNT(OWNER) AS NumberCompleted, CONVERT(char(10),
> GETDATE(),
> 101)AS [DATE]
> INTO #CompTotals
> FROM #Completed
> GROUP BY owner
> ORDER BY OWNER ASC
> ----
---
> --insert records into permanent table for reports
> INSERT INTO task_totals_collector
> SELECT UM.user_id AS Employee, NS.NumberNotStarted AS NotStarted,
> PT.NumberInProgress AS InProgress,CT.NumberCompleted AS Completed,
> CONVERT(char(10), GETDATE(), 101)AS CalcDate, OT.NumberOverdue AS Overdue
> FROM user_mstr UM
> LEFT JOIN #NSTotals NS
> ON UM.user_id=NS.owner
> LEFT JOIN #ProgTotals PT ON
> UM.user_id=PT.owner
> LEFT JOIN #CompTotals CT ON
> UM.user_id=CT.owner
> LEFT JOIN #ODTotals OT ON
> UM.user_id=OT.owner
> ORDER BY UM.user_id ASC
> --end of script
>
>
> Patrick Rouse
> Microsoft MVP - Terminal Server
> http://www.workthin.com|||I put brackets around the tables named user_id, and then ran just the first
section (up to the first --), which fails at the first GETDATE with
this error:
Incorrect syntax near '20050128'. [SQLSTATE 42000] (Error 170). The step
failed.
Like I said, it works fine in QA, just not in a job. :(
"Andrew J. Kelly" wrote:

> One thing I can suggest is to put brackets ( [ ] ) around all your table a
nd
> column names as you have a lot of reserved words as object names.
> --
> Andrew J. Kelly SQL MVP
>
> "Patrick Rouse" <PatrickRouse@.discussions.microsoft.com> wrote in message
> news:F723A708-5FF4-4E8A-9F41-38A49BB08BE4@.microsoft.com...
>
>|||Patrick,
I haven't followed the whole thread but do you actually have "GO" in your
SQL step. "GO" is a batch terminator used by QA and is not part of T-SQL.
So, you would want to take it out.
-oj
"Patrick Rouse" <PatrickRouse@.discussions.microsoft.com> wrote in message
news:5B11E86A-08E5-4027-926E-84AE2A3AFBEA@.microsoft.com...
>I put brackets around the tables named user_id, and then ran just the first
> section (up to the first --), which fails at the first GETDATE with
> this error:
> Incorrect syntax near '20050128'. [SQLSTATE 42000] (Error 170). The step
> failed.
> Like I said, it works fine in QA, just not in a job. :(
> "Andrew J. Kelly" wrote:
>|||Good to know, but I removed the USE DatabaseName & GO statements and still
get the same error.
"Patrick Rouse" wrote:
> No, I think it's getting this from GETDATE(). The database I'm working wi
th
> has a lot of dates stored as CHAR(8), which drives me nuts, as I can ORDER
BY
> on these columns. In my query I CONVERT them to DATETIME, but they're stil
l
> formatted as 'YYYYMMDD'.
> "Adam Machanic" wrote:
>|||First off as OJ mentions , do you have the GO's in the actual code? How you
know it is the GETDATE() that is failing or are you assuming because it is a
date type value in the error message? Here is the first block from your
original post:
<<<<<
--begin script
USE NGEPMProd
GO
SELECT TTM.desc_30 AS TaskType, TU.user_id AS Owner, TU.createdate,T.status
INTO #NotStarted
FROM tasks T
INNER JOIN task_type_mstr TTM ON
TTM.task_type_id = T.task_type_id
INNER JOIN task_users_assigned_to TU ON
TU.task_id = T.task_id
WHERE t.status = 1
ORDER BY TU.user_id ASC
SELECT Owner, COUNT(OWNER) AS NumberNotStarted, CONVERT(char(10), GETDATE(),
101)AS [DATE]
INTO #NSTotals
FROM #NotStarted
GROUP BY owner
ORDER BY OWNER ASC
I can't tell from this post but it looks like you don't have a space between
the end of the CONVERT and the AS. What happens if you set the date to a
variable and use the variable in the select instead?
Andrew J. Kelly SQL MVP
"Patrick Rouse" <PatrickRouse@.discussions.microsoft.com> wrote in message
news:5B11E86A-08E5-4027-926E-84AE2A3AFBEA@.microsoft.com...
>I put brackets around the tables named user_id, and then ran just the first
> section (up to the first --), which fails at the first GETDATE with
> this error:
> Incorrect syntax near '20050128'. [SQLSTATE 42000] (Error 170). The step
> failed.
> Like I said, it works fine in QA, just not in a job. :(
> "Andrew J. Kelly" wrote:
>|||Patrick,
Make it easier on us if you would script out the job and post it here. ;-)
(you can use EM to do that).
There's probably a silly syntax error.
-oj
"Patrick Rouse" <PatrickRouse@.discussions.microsoft.com> wrote in message
news:58F52F80-E014-4CF7-8868-9A919AA4EBEA@.microsoft.com...
> Good to know, but I removed the USE DatabaseName & GO statements and still
> get the same error.
>|||I assume it's the get date, because that returns the date listed in the erro
r
I posted, and the error states on "line 12" which is where GETDATE is
specified.
"Andrew J. Kelly" wrote:

> First off as OJ mentions , do you have the GO's in the actual code? How y
ou
> know it is the GETDATE() that is failing or are you assuming because it is
a
> date type value in the error message? Here is the first block from your
> original post:
> <<<<<
> --begin script
> USE NGEPMProd
> GO
> SELECT TTM.desc_30 AS TaskType, TU.user_id AS Owner, TU.createdate,T.statu
s
> INTO #NotStarted
> FROM tasks T
> INNER JOIN task_type_mstr TTM ON
> TTM.task_type_id = T.task_type_id
> INNER JOIN task_users_assigned_to TU ON
> TU.task_id = T.task_id
> WHERE t.status = 1
> ORDER BY TU.user_id ASC
> SELECT Owner, COUNT(OWNER) AS NumberNotStarted, CONVERT(char(10), GETDATE(
),
> 101)AS [DATE]
> INTO #NSTotals
> FROM #NotStarted
> GROUP BY owner
> ORDER BY OWNER ASC
> I can't tell from this post but it looks like you don't have a space betwe
en
> the end of the CONVERT and the AS. What happens if you set the date to a
> variable and use the variable in the select instead?
> --
> Andrew J. Kelly SQL MVP
>
> "Patrick Rouse" <PatrickRouse@.discussions.microsoft.com> wrote in message
> news:5B11E86A-08E5-4027-926E-84AE2A3AFBEA@.microsoft.com...
>
>sql

Problem scheduling DTS packages

I am getting the following error whenever I schedule a new DTS package:
SQL Server does not exist or access denied
This is new, all of my older scheduled packages run without any problem.
This just started occuring during the past week and is occuring on all of my
SQL Servers (7 and 2000).
Any ideas?John
Well , Perhaps you are created the DTS package on your computer and when
you execute the package it runs under account of your computer (SQL Agent
service)
By default, the owner of that service is LocalSystem (with has many
Administrator-style permission, but *NO* access to any network resources).
So, change the account of that service to a Domain Admin account (or other
suitable account with permissions on both computers (server and yours)
"John Hamilton" <jhamil@.nowhere.com> wrote in message
news:uxNQlFVnDHA.2628@.TK2MSFTNGP10.phx.gbl...
> I am getting the following error whenever I schedule a new DTS package:
> SQL Server does not exist or access denied
> This is new, all of my older scheduled packages run without any problem.
> This just started occuring during the past week and is occuring on all of
my
> SQL Servers (7 and 2000).
> Any ideas?
>|||I'm just curious why it was working, I haven't changed anything and now it
does not. The network guys applied some security patches about the time
this started.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:eERZIZVnDHA.3612@.TK2MSFTNGP11.phx.gbl...
> John
> Well , Perhaps you are created the DTS package on your computer and when
> you execute the package it runs under account of your computer (SQL Agent
> service)
> By default, the owner of that service is LocalSystem (with has many
> Administrator-style permission, but *NO* access to any network resources).
> So, change the account of that service to a Domain Admin account (or other
> suitable account with permissions on both computers (server and yours)
> "John Hamilton" <jhamil@.nowhere.com> wrote in message
> news:uxNQlFVnDHA.2628@.TK2MSFTNGP10.phx.gbl...
> > I am getting the following error whenever I schedule a new DTS package:
> >
> > SQL Server does not exist or access denied
> >
> > This is new, all of my older scheduled packages run without any problem.
> > This just started occuring during the past week and is occuring on all
of
> my
> > SQL Servers (7 and 2000).
> >
> > Any ideas?
> >
> >
>

Problem scheduling a package

Hi,
I have a package saved in the msdb database. When I right click on it and execute, it works fine through the dts utility program.

When I schedule it, it always errors out with the message "The command line parameters are invalid".

The package is password protected. The command line in the dts utility reads: /DTS "\MSDB\<package name>" /SERVER aawork2/DECRYPT /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING V
I have to enter my password to view this.

In the job step, if I click the command line, it reads: /DTS "/MSDB/<package name>" /SERVER aawork2/DECRYPT <password> /MAXCONCURRENT " -1 " /CHECKPOINTING OFF

I suspect its something to do with the password protection and the fact the job runs it under the service account.

Thanks in advance for any help.

Asim.
No, the problem (most likely) is that the package path has forward slashes instead of backward slashes. Try changing them to back slashses. Also put a space between the server name and the /DECRYPT option. These were bugs that should be fixed by RTM.

Thanks,
Matt|||Matt, it works. Thanks! This was very helpful.

Asim.

Problem saving/retrieving unicode characters NVARCHAR with unicode collation (java jdbc)

I'm connecting to a SQL Server 2005 database using the latest (beta) sql server driver (Microsoft SQL Server 2005 JDBC Driver 1.1 CTP June 2006) from within Java (Rational Application Developer).

The table in SQL Server database has collation Latin1_General_CI_AS and one of the columns is a NVARCHAR with collation Indic_General_90_CI_AS. This should be a Unicode only collation. However when storing for instance the following String:

€_￡_ùúü?ùúü?_???????￡????_???_??????_прстуф_????
... it is saved with ? for all unicode characters as follows (when looking in the database):
€_￡_ùúü?ùúü?_???????￡????_???_?_?_?

The above is not correct, since all unicode characters should still be visible. When inserting the same string directly into the sql server database (without using Java) the result is ok.

Also when trying to retrieve the results again it complains about the following error within Java:

Codepage 0 is not supported by the Java environment.

Hopefully somebody has an answer for this problem. When I alter the collation of the NVARCHAR column to be Latin1_General_CI_AS as well, the data can be stored and retrieved however then of course the unicode specific characters are lost and results into ? So in that case the output is as described above (ie €_￡_ùúü?ùúü?_???????￡????_???_?_?_?)

We would like to be able to persist and retrieve unicode characters in a SQL Server database using the correct JDBC Driver. We achieved this result already with an Oracle UTF8 database. But we need to be compliant with a SQL Server database as well. Please help.

Thanks in advance for your help.So far nobody replied on my question. unfortunately.
Please help me to sort out this problem.

Bottom line we just want to be able to save and retrieve Unicode characters in SQL server within our Java application. We want to do this by setting the NVARCHAR columns in all tables to a Unicode collation.

Please help.
Thanks.|||

Hugo,

Can you tell me whether you have sendStringParametersAsUnicode set to "false" in your connection properties?

Thank you,

--David Olix

JDBC Development

|||

Hi David,

Thanks for your reply. I can ensure that I didn't set the sendStringParametersAsUnicode to "false". This parameter is default set to "true" so I didn't change it in the connection properties.

I found out that the unicode Collation I used "Indic_General_90_CI_AS" had a Codepage 0 in my SQL Server 2005 installation. In fact all unicode only collations do have a codepage 0 if I check them all using the following command:

SELECT COLLATIONPROPERTY('Indic_General_90_CI_AS', 'CodePage') as CodePage

Is this perhaps related to the problem I have in Java? Because in there the exception explains about the fact that the codepage is 0.

I really hope you can help me to sort this out.

Thanks in advance for your help David.

Hugo

|||Hi David,

I found the solution. When updating a SQL Server 2005 NVARCHAR column, you have to use the following syntax:

UPDATE <table> SET <column> = N'<value>'

So for example:
UPDATE customer SET id = '€_???????￡????_???_??????_прстуф_????'

Has to be this for a SQL Server connection:

UPDATE customer SET id = N'€_???????￡????_???_??????_прстуф_????'

The prefix N before the value is required to make sure to save the string in Unicode format. I did not do this so far. Now I'm able to store and retrieve data into a SQL Server database and my collatin is now Latin1_General_CI_AS since this supports Unicode as well.

Hugo|||

hi hugo

this is gr8 thread

i have facing a problem

i want to insert indic values in a table in SQL server

the update statement u mentioned is useful

UPDATE <table> SET <column> = N'<value>'

but when i use INSERT INTO <table> VALUES(N'<value>')

can u tell me y doesnt this work

tahnx in advance.

Problem saving/retrieving unicode characters NVARCHAR with unicode collation (java jdbc)

I'm connecting to a SQL Server 2005 database using the latest (beta) sql server driver (Microsoft SQL Server 2005 JDBC Driver 1.1 CTP June 2006) from within Java (Rational Application Developer).

The above is not correct, since all unicode characters should still be visible. When inserting the same string directly into the sql server database (without using Java) the result is ok.

Also when trying to retrieve the results again it complains about the following error within Java:

Codepage 0 is not supported by the Java environment.

Thanks in advance for your help.
So far nobody replied on my question. unfortunately.
Please help me to sort out this problem.

Please help.
Thanks.
|||

Hugo,

Can you tell me whether you have sendStringParametersAsUnicode set to "false" in your connection properties?

Thank you,

--David Olix

JDBC Development

|||

Hi David,

Thanks for your reply. I can ensure that I didn't set the sendStringParametersAsUnicode to "false". This parameter is default set to "true" so I didn't change it in the connection properties.

SELECT COLLATIONPROPERTY('Indic_General_90_CI_AS', 'CodePage') as CodePage

Is this perhaps related to the problem I have in Java? Because in there the exception explains about the fact that the codepage is 0.

I really hope you can help me to sort this out.

Thanks in advance for your help David.

Hugo

|||Hi David,

I found the solution. When updating a SQL Server 2005 NVARCHAR column, you have to use the following syntax:

UPDATE <table> SET <column> = N'<value>'

So for example:
UPDATE customer SET id = '€_???????￡????_???_??????_прстуф_????'

Has to be this for a SQL Server connection:

UPDATE customer SET id = N'€_???????￡????_???_??????_прстуф_????'

Hugo
|||

hi hugo

this is gr8 thread

i have facing a problem

i want to insert indic values in a table in SQL server

the update statement u mentioned is useful

UPDATE <table> SET <column> = N'<value>'

but when i use INSERT INTO <table> VALUES(N'<value>')

can u tell me y doesnt this work

tahnx in advance.

problem running where table2.column1 in (table1.column2)

I have 2 tables
table1 contains data on a product, and has a column2 with a list of
values separated by a comma (ie. 1577,256,436). column2 is of type
varchar
table2 contains a column1 which has a list of ids (which are the ids
referenced in table1.column2) and another column with descriptions for
those ids. column1 is of type int
if i run a sql statement with a where clause
WHERE table2.column1 in (table1.column2)
i get an error saying its unable to convert varchar to int
the clause with the data in it should look like
WHERE table2.column1 in (1577,256,436)
is it not possible to pass a list of values from another table and
check against it, as if it was a haldfull of int values separated by a
comma?
ive also tried casting both columns to type varchar, and
table1.column2 adding single quotes using a replace() command so that
the values are varchar values, but end up with 0 results.
at the same time if i just run
WHERE table2.column1 in (1577,256,436)
meaning the data is not being passed im just typing it in, than the
query is ran just fine and produces the results im looking for.
any help on this is appreciated
You didn't provide table definition scripts nor sample data, but I am
guessing that you will need dynamic sql here.
create table #tmp1 (id int identity, a char(1))
insert #tmp1 values ('a')
insert #tmp1 values ('2')
insert #tmp1 values ('d')
create table #tmp2 (b varchar(10))
insert #tmp2 values ('1,2')
declare @.sql varchar(100)
select @.sql = 'select * from #tmp1 where id in (' + b + ')'
from #tmp2
print @.sql
exec (@.sql)
drop table #tmp1
drop table #tmp2
TheSQLGuru
President
Indicium Resources, Inc.
<igornik@.gmail.com> wrote in message
news:1187194472.590943.165600@.d55g2000hsg.googlegr oups.com...
>I have 2 tables
> table1 contains data on a product, and has a column2 with a list of
> values separated by a comma (ie. 1577,256,436). column2 is of type
> varchar
> table2 contains a column1 which has a list of ids (which are the ids
> referenced in table1.column2) and another column with descriptions for
> those ids. column1 is of type int
> if i run a sql statement with a where clause
> WHERE table2.column1 in (table1.column2)
> i get an error saying its unable to convert varchar to int
> the clause with the data in it should look like
> WHERE table2.column1 in (1577,256,436)
> is it not possible to pass a list of values from another table and
> check against it, as if it was a haldfull of int values separated by a
> comma?
> ive also tried casting both columns to type varchar, and
> table1.column2 adding single quotes using a replace() command so that
> the values are varchar values, but end up with 0 results.
> at the same time if i just run
> WHERE table2.column1 in (1577,256,436)
> meaning the data is not being passed im just typing it in, than the
> query is ran just fine and produces the results im looking for.
> any help on this is appreciated
>
|||/*
Value lists are very bad design and do not belong in Relational
Databases.
I suspect that you currently have something that looks something like
this:
*/
create table #t1 (t1_id int)
create table #t2 (t2_id int, t1_ids varchar(16))
insert into #t1 (t1_id) values (1)
insert into #t1 (t1_id) values (2)
insert into #t1 (t1_id) values (3)
insert into #t1 (t1_id) values (4)
insert into #t2 (t2_id, t1_ids) values (1, '1,3,4')
insert into #t2 (t2_id, t1_ids) values (2, '1,2,3')
select * from #t1
select * from #t2
drop table #t2
drop table #t1
/*
I suspect you would try something like:
select
t1_id,
t2_id
from
t1,t2
where
t1_id in (t1_ids)
But that won't work, this value list should be stored in the proper
case below:
*/
create table #t1 (t1_id int)
create table #t2 (t2_id int, t1_id int)
insert into #t1 (t1_id) values (1)
insert into #t1 (t1_id) values (2)
insert into #t1 (t1_id) values (3)
insert into #t1 (t1_id) values (4)
insert into #t2 (t2_id, t1_id) values (1, 1)
insert into #t2 (t2_id, t1_id) values (1, 3)
insert into #t2 (t2_id, t1_id) values (1, 4)
insert into #t2 (t2_id, t1_ids) values (2, 1)
insert into #t2 (t2_id, t1_ids) values (2, 2)
insert into #t2 (t2_id, t1_ids) values (2, 3)
select * from #t1
select * from #t2
select
t1_id,
t2_id
from
#t1 t1
inner join #t2 t2 on t1.t1_id = t2.t1_id
drop table #t2
drop table #t1
On Aug 15, 12:14 pm, igor...@.gmail.com wrote:
> I have 2 tables
> table1 contains data on a product, and has a column2 with a list of
> values separated by a comma (ie. 1577,256,436). column2 is of type
> varchar
> table2 contains a column1 which has a list of ids (which are the ids
> referenced in table1.column2) and another column with descriptions for
> those ids. column1 is of type int
> if i run a sql statement with a where clause
> WHERE table2.column1 in (table1.column2)
> i get an error saying its unable to convert varchar to int
> the clause with the data in it should look like
> WHERE table2.column1 in (1577,256,436)
> is it not possible to pass a list of values from another table and
> check against it, as if it was a haldfull of int values separated by a
> comma?
> ive also tried casting both columns to type varchar, and
> table1.column2 adding single quotes using a replace() command so that
> the values are varchar values, but end up with 0 results.
> at the same time if i just run
> WHERE table2.column1 in (1577,256,436)
> meaning the data is not being passed im just typing it in, than the
> query is ran just fine and produces the results im looking for.
> any help on this is appreciated
|||thanks for all of your help, this got me going into the right
direction again.
sql

problem running where table2.column1 in (table1.column2)

I have 2 tables
table1 contains data on a product, and has a column2 with a list of
values separated by a comma (ie. 1577,256,436). column2 is of type
varchar
table2 contains a column1 which has a list of ids (which are the ids
referenced in table1.column2) and another column with descriptions for
those ids. column1 is of type int
if i run a sql statement with a where clause
WHERE table2.column1 in (table1.column2)
i get an error saying its unable to convert varchar to int
the clause with the data in it should look like
WHERE table2.column1 in (1577,256,436)
is it not possible to pass a list of values from another table and
check against it, as if it was a haldfull of int values separated by a
comma?
ive also tried casting both columns to type varchar, and
table1.column2 adding single quotes using a replace() command so that
the values are varchar values, but end up with 0 results.
at the same time if i just run
WHERE table2.column1 in (1577,256,436)
meaning the data is not being passed im just typing it in, than the
query is ran just fine and produces the results im looking for.
any help on this is appreciatedYou didn't provide table definition scripts nor sample data, but I am
guessing that you will need dynamic sql here.
create table #tmp1 (id int identity, a char(1))
insert #tmp1 values ('a')
insert #tmp1 values ('2')
insert #tmp1 values ('d')
create table #tmp2 (b varchar(10))
insert #tmp2 values ('1,2')
declare @.sql varchar(100)
select @.sql = 'select * from #tmp1 where id in (' + b + ')'
from #tmp2
print @.sql
exec (@.sql)
drop table #tmp1
drop table #tmp2
TheSQLGuru
President
Indicium Resources, Inc.
<igornik@.gmail.com> wrote in message
news:1187194472.590943.165600@.d55g2000hsg.googlegroups.com...
>I have 2 tables
> table1 contains data on a product, and has a column2 with a list of
> values separated by a comma (ie. 1577,256,436). column2 is of type
> varchar
> table2 contains a column1 which has a list of ids (which are the ids
> referenced in table1.column2) and another column with descriptions for
> those ids. column1 is of type int
> if i run a sql statement with a where clause
> WHERE table2.column1 in (table1.column2)
> i get an error saying its unable to convert varchar to int
> the clause with the data in it should look like
> WHERE table2.column1 in (1577,256,436)
> is it not possible to pass a list of values from another table and
> check against it, as if it was a haldfull of int values separated by a
> comma?
> ive also tried casting both columns to type varchar, and
> table1.column2 adding single quotes using a replace() command so that
> the values are varchar values, but end up with 0 results.
> at the same time if i just run
> WHERE table2.column1 in (1577,256,436)
> meaning the data is not being passed im just typing it in, than the
> query is ran just fine and produces the results im looking for.
> any help on this is appreciated
>|||/*
Value lists are very bad design and do not belong in Relational
Databases.
I suspect that you currently have something that looks something like
this:
*/
create table #t1 (t1_id int)
create table #t2 (t2_id int, t1_ids varchar(16))
insert into #t1 (t1_id) values (1)
insert into #t1 (t1_id) values (2)
insert into #t1 (t1_id) values (3)
insert into #t1 (t1_id) values (4)
insert into #t2 (t2_id, t1_ids) values (1, '1,3,4')
insert into #t2 (t2_id, t1_ids) values (2, '1,2,3')
select * from #t1
select * from #t2
drop table #t2
drop table #t1
/*
I suspect you would try something like:
select
t1_id,
t2_id
from
t1,t2
where
t1_id in (t1_ids)
But that won't work, this value list should be stored in the proper
case below:
*/
create table #t1 (t1_id int)
create table #t2 (t2_id int, t1_id int)
insert into #t1 (t1_id) values (1)
insert into #t1 (t1_id) values (2)
insert into #t1 (t1_id) values (3)
insert into #t1 (t1_id) values (4)
insert into #t2 (t2_id, t1_id) values (1, 1)
insert into #t2 (t2_id, t1_id) values (1, 3)
insert into #t2 (t2_id, t1_id) values (1, 4)
insert into #t2 (t2_id, t1_ids) values (2, 1)
insert into #t2 (t2_id, t1_ids) values (2, 2)
insert into #t2 (t2_id, t1_ids) values (2, 3)
select * from #t1
select * from #t2
select
t1_id,
t2_id
from
#t1 t1
inner join #t2 t2 on t1.t1_id = t2.t1_id
drop table #t2
drop table #t1
On Aug 15, 12:14 pm, igor...@.gmail.com wrote:
> I have 2 tables
> table1 contains data on a product, and has a column2 with a list of
> values separated by a comma (ie. 1577,256,436). column2 is of type
> varchar
> table2 contains a column1 which has a list of ids (which are the ids
> referenced in table1.column2) and another column with descriptions for
> those ids. column1 is of type int
> if i run a sql statement with a where clause
> WHERE table2.column1 in (table1.column2)
> i get an error saying its unable to convert varchar to int
> the clause with the data in it should look like
> WHERE table2.column1 in (1577,256,436)
> is it not possible to pass a list of values from another table and
> check against it, as if it was a haldfull of int values separated by a
> comma?
> ive also tried casting both columns to type varchar, and
> table1.column2 adding single quotes using a replace() command so that
> the values are varchar values, but end up with 0 results.
> at the same time if i just run
> WHERE table2.column1 in (1577,256,436)
> meaning the data is not being passed im just typing it in, than the
> query is ran just fine and produces the results im looking for.
> any help on this is appreciated|||thanks for all of your help, this got me going into the right
direction again.

problem running where table2.column1 in (table1.column2)

I have 2 tables
table1 contains data on a product, and has a column2 with a list of
values separated by a comma (ie. 1577,256,436). column2 is of type
varchar
table2 contains a column1 which has a list of ids (which are the ids
referenced in table1.column2) and another column with descriptions for
those ids. column1 is of type int
if i run a sql statement with a where clause
WHERE table2.column1 in (table1.column2)
i get an error saying its unable to convert varchar to int
the clause with the data in it should look like
WHERE table2.column1 in (1577,256,436)
is it not possible to pass a list of values from another table and
check against it, as if it was a haldfull of int values separated by a
comma?
ive also tried casting both columns to type varchar, and
table1.column2 adding single quotes using a replace() command so that
the values are varchar values, but end up with 0 results.
at the same time if i just run
WHERE table2.column1 in (1577,256,436)
meaning the data is not being passed im just typing it in, than the
query is ran just fine and produces the results im looking for.
any help on this is appreciatedYou didn't provide table definition scripts nor sample data, but I am
guessing that you will need dynamic sql here.
create table #tmp1 (id int identity, a char(1))
insert #tmp1 values ('a')
insert #tmp1 values ('2')
insert #tmp1 values ('d')
create table #tmp2 (b varchar(10))
insert #tmp2 values ('1,2')
declare @.sql varchar(100)
select @.sql = 'select * from #tmp1 where id in (' + b + ')'
from #tmp2
print @.sql
exec (@.sql)
drop table #tmp1
drop table #tmp2
TheSQLGuru
President
Indicium Resources, Inc.
<igornik@.gmail.com> wrote in message
news:1187194472.590943.165600@.d55g2000hsg.googlegroups.com...
>I have 2 tables
> table1 contains data on a product, and has a column2 with a list of
> values separated by a comma (ie. 1577,256,436). column2 is of type
> varchar
> table2 contains a column1 which has a list of ids (which are the ids
> referenced in table1.column2) and another column with descriptions for
> those ids. column1 is of type int
> if i run a sql statement with a where clause
> WHERE table2.column1 in (table1.column2)
> i get an error saying its unable to convert varchar to int
> the clause with the data in it should look like
> WHERE table2.column1 in (1577,256,436)
> is it not possible to pass a list of values from another table and
> check against it, as if it was a haldfull of int values separated by a
> comma?
> ive also tried casting both columns to type varchar, and
> table1.column2 adding single quotes using a replace() command so that
> the values are varchar values, but end up with 0 results.
> at the same time if i just run
> WHERE table2.column1 in (1577,256,436)
> meaning the data is not being passed im just typing it in, than the
> query is ran just fine and produces the results im looking for.
> any help on this is appreciated
>|||/*
Value lists are very bad design and do not belong in Relational
Databases.
I suspect that you currently have something that looks something like
this:
*/
create table #t1 (t1_id int)
create table #t2 (t2_id int, t1_ids varchar(16))
insert into #t1 (t1_id) values (1)
insert into #t1 (t1_id) values (2)
insert into #t1 (t1_id) values (3)
insert into #t1 (t1_id) values (4)
insert into #t2 (t2_id, t1_ids) values (1, '1,3,4')
insert into #t2 (t2_id, t1_ids) values (2, '1,2,3')
select * from #t1
select * from #t2
drop table #t2
drop table #t1
/*
I suspect you would try something like:
select
t1_id,
t2_id
from
t1,t2
where
t1_id in (t1_ids)
But that won't work, this value list should be stored in the proper
case below:
*/
create table #t1 (t1_id int)
create table #t2 (t2_id int, t1_id int)
insert into #t1 (t1_id) values (1)
insert into #t1 (t1_id) values (2)
insert into #t1 (t1_id) values (3)
insert into #t1 (t1_id) values (4)
insert into #t2 (t2_id, t1_id) values (1, 1)
insert into #t2 (t2_id, t1_id) values (1, 3)
insert into #t2 (t2_id, t1_id) values (1, 4)
insert into #t2 (t2_id, t1_ids) values (2, 1)
insert into #t2 (t2_id, t1_ids) values (2, 2)
insert into #t2 (t2_id, t1_ids) values (2, 3)
select * from #t1
select * from #t2
select
t1_id,
t2_id
from
#t1 t1
inner join #t2 t2 on t1.t1_id = t2.t1_id
drop table #t2
drop table #t1
On Aug 15, 12:14 pm, igor...@.gmail.com wrote:
> I have 2 tables
> table1 contains data on a product, and has a column2 with a list of
> values separated by a comma (ie. 1577,256,436). column2 is of type
> varchar
> table2 contains a column1 which has a list of ids (which are the ids
> referenced in table1.column2) and another column with descriptions for
> those ids. column1 is of type int
> if i run a sql statement with a where clause
> WHERE table2.column1 in (table1.column2)
> i get an error saying its unable to convert varchar to int
> the clause with the data in it should look like
> WHERE table2.column1 in (1577,256,436)
> is it not possible to pass a list of values from another table and
> check against it, as if it was a haldfull of int values separated by a
> comma?
> ive also tried casting both columns to type varchar, and
> table1.column2 adding single quotes using a replace() command so that
> the values are varchar values, but end up with 0 results.
> at the same time if i just run
> WHERE table2.column1 in (1577,256,436)
> meaning the data is not being passed im just typing it in, than the
> query is ran just fine and produces the results im looking for.
> any help on this is appreciated|||thanks for all of your help, this got me going into the right
direction again.

problem running vb.net application with sqlexpress in system with only .NET FRAMEWORK

Hi,

I have developed a stand alone application that get data from excel and dumps it in local database (sql server database primary data file) in visual studio .net 2005. I had no issues while developing the application. When i am trying to install this application in the standalone system with .net framework 2.0 and no backend database it giving the following error

provider: sql network interfaces, error 26 - Error locating Server/ Instance Specified

connection string i am using is

Dim objLocalDB As System.Data.SqlClient.SqlConnection

objLocalDB = New System.Data.SqlClient.SqlConnection("Data Source=.\SQLEXPRESS;AttachDbFilename=" & System.AppDomain.CurrentDomain.BaseDirectory & "LFDB.mdf;Integrated Security=True;User Instance=True")

I dont want to use any backend database. I only want to use the database that comes with .net (i.e sqlexpress)

Please help me how can i get through this problem.

hi,

Manyam wrote:

When i am trying to install this application in the standalone system with .net framework 2.0 and no backend database it giving the following error
provider: sql network interfaces, error 26 - Error locating Server/ Instance Specified

I can't understand if you installed SQLExpress as well, on the target computer... did you?

regards

|||SQL Server Express is a backend database although the GUI tools are more lightweight than the ones of the more bigger editions. If you want to use user instances as mentioned in the connection string, you will need to have a SQL Server Express Service in place to attach the database to the Service. SQL Server Express can be deployed within your application (even through clickonce deplyoment but at the end need to be installed).

HTH, Jens K. Suessmeyer.

http://www.sqlserver0205.de
|||

Manyam wrote:
I dont want to use any backend database. I only want to use the database that comes with .net (i.e sqlexpress)
Please help me how can i get through this problem.

SQL Express is not part of the .NET Framework, it is a completely separate product that must be installed if you are going to use it. You can add SQL Express as a prerequisite to you application installation automatically using either ClickOnce or standard Setup Projects in VS 2005, in your projects properties, click the Prerequisite button and make sure SQL Express is checked in the list. This will automatically include the SQL Express installer in your deployment and install it on the target computer if it is needed.

Mike

|||

Hi,

Thanks for your suggestion. So we cannot run vb.net application that is developed using sql server management studio without SQL EXPRESS and only .net framework.

I tried to open the link provided by you. Its not opening. I am getting page cannot be displayed.

|||

Hi,

Thanks for your valueable suggestion. So i cannot run a vb application developed using sql server management studio without SQL EXPRESS.I tried installing SQL EXPRESS in standalone system, but its giving issues.

Process followed -->

Uninstalled existing framework.

Installed .NET framework 2.0Dowloaded SQLEXPRESS and istalled in system.

Its throwing a error that .net should require some updates... What updates do we need to install to get this working.

Thanks in advance.

|||What's your OS? If WIndows XP Pro, this needs SP2 and updated security patches

Problem running stored procedure inside another store procedure

hi,

I want to use a stored procedure inside a stored procedure simulteanously changing the database.
this is my base store procedure

alter PROCEDURE create_file @.dbname sysname
AS
declare @.fname varchar(30)
declare @.fsizes nvarchar
BEGIN
DECLARE @.cmd varchar(1000)
set @.cmd = 'osql -E -d ' + @.dbname + ' -Q "exec ret_sizes @.fname OUTPUT,@.fsizes OUTPUT"'
exec master..xp_cmdshell @.cmd
END

if i execute the his "exec create_file ertis"
i get error as

Msg 137, Level 15, State 2, Server HCC-BPVHD1, Line 1
Must declare the variable '@.fname'.
NULL

the procedure called inside the mail procedure is

alter procedure ret_sizes
@.fname varchar(30) OUTPUT,
@.fsizes nvarchar OUTPUT
as
begin
select @.fname=name, @.fsizes=size from sysfiles
order by fileid
end

Please help meThe @.Cmd string doesn't seem to include the second exec.

set @.cmd = 'osql -E -d ' + @.dbname + ' + '-Q' + ' "exec ret_sizes @.fname OUTPUT,@.fsizes OUTPUT"' '

exec @.CMD

__________________________________________________ ___________

Quote:

Originally Posted by eRTIS SQL

hi,

I want to use a stored procedure inside a stored procedure simulteanously changing the database.
this is my base store procedure

if i execute the his "exec create_file ertis"
i get error as

Msg 137, Level 15, State 2, Server HCC-BPVHD1, Line 1
Must declare the variable '@.fname'.
NULL

the procedure called inside the mail procedure is

alter procedure ret_sizes
@.fname varchar(30) OUTPUT,
@.fsizes nvarchar OUTPUT
as
begin
select @.fname=name, @.fsizes=size from sysfiles
order by fileid
end

Please help me

sql

Problem running stored Procedure

Hi Guys & Gals

I'm having problems running a stored procedure, I'm getting an error that I don't understand. My procedure is this:


ALTER PROC sp_get_allowed_growers
@.GrowerList varchar(500)
AS
BEGIN
SET NOCOUNT ONDECLARE @.SQL varchar(600)
SET @.SQL =
'SELECT nu_code, nu_description, nu_master
FROM nursery WHERE nu_master IN (' + @.GrowerList + ') ORDER BY nu_code ASC'
EXEC(@.SQL)
END
GO

and the code I'm using to execute the procedure is this:


public DataSet GetGrowers(string Username)
{
System.Text.StringBuilder UserRoles = new System.Text.StringBuilder();
UsersDB ps = new UsersDB();
SqlDataReader dr = ps.GetRolesByUser(Username);
while(dr.Read())
{
UserRoles.Append(dr["RoleName"]+",");
}
UserRoles.Remove(UserRoles.Length-1,1);
//Create instance of Connection and Command objects
SqlConnection transloadConnection = new SqlConnection(ConfigurationSettings.AppSettings["connectionStringTARPS"]);
SqlDataAdapter transloadCommand = new SqlDataAdapter("sp_get_allowed_growers",transloadConnection);
 //Create and fill the DataSet
SqlParameter paramList = new SqlParameter("@.GrowerList",SqlDbType.VarChar);
paramList.Value = UserRoles.ToString();
transloadCommand.SelectCommand.Parameters.Add(paramList);
DataSet dsGrowers = new DataSet();
transloadCommand.Fill(dsGrowers);
return dsGrowers;}

The UserRoles stringbuilder has an appropriate value when it is passed to the stored procedure. When I run the stored procedure in query analyser it runs just fine. However, when I step through the code above, I get the following error:

Line 1: Incorrect syntax near 'sp_get_allowed_growers'.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.SqlClient.SqlException: Line 1: Incorrect syntax near 'sp_get_allowed_growers'.

Anyone with any ideas would be very helpful...Try surrounding your parameter list (just the one parameter) in your stored procedure in parentheses. Also, I've never used Alter proc, I use Create Procedure (not to say that Alter proc doesn't work, I don't know - just an observation)|||CREATE PROCEDURE dbo.sp_get_allowed_growers
(
@.GrowerList varchar(500)
)
AS

I always use dbo.sp"NAME" because when you push to production your account may or may not be the dbo.|||Although, technically, you should never preface your stored procedure names with sp_. sp_ is used by sql server to designate system stored procedures. When you preface your own sprocs with sp_, you cause sql server to first search through all system sprocs, then through the local sprocs to find your procedure. In other words, you're adding in a bit of inefficiency to each and every procedure call.|||Thanks for the suggestions guys, I tried that and I still get the same error. It's a bit of an odd one because the stored procedure seems fine. I compiled it in query analyser and I can run it in query analyser by right-clicking and selecting "Open", I'm then prompted for the paramter and it returns the expected result so, as far as I know, it's functioning correctly. When I step through the code calling the procedure, the Autos window shows the value of the parameter as "'Admins','NH'" which is exactly what I'd expect.

I'm pretty sure it's something to do with the way that the value of the parameter is being assigned. In query analyser, when I run the stored proc and I am prompted for the value of the @.GrowerList parameter, I have to type the values in exactly like this 'Admins','NH' and it works.

The odd thing is that the database is raising the error and saying that there's a syntax error in the stored proc when there definitely isn't. It would be different if it was raising a type conversion error but it isn't.

Keep thinking folks, I'll send a small prize to whoever can help me crack it (don't get too excited, it will probably be a company pen or something!)

Cheers,|||Add

transloadCommand.SelectCommand.CommandType=CommandType.StoredProcedure;

before you call .Fill()

And I would also concur that naming an sp with an sp_ prefix will slow things down...|||Thanks doug, that cracked it straight away, I'm very grateful. If you email me your address to imacleverbloke@.mcowan.info I'll pop the freebies in the post. Don't get too excited but they're worth having!

As for the sp_ naming convention, I hear about that performance issue too late and I'm kinda stuck with it until the next comprehensive overhaul.

Cheers guys.

Problem running SSIS from C# program.

I am able to run it from the file system but when I try running it from SQL Server I get an error message that it cannot load package. I am using the following code.

Package package = app.LoadFromSqlServer("trans_hist", Properties.Resources.dbServerName, Properties.Resources.userId, Properties.Resources.password, null);Nobody is doing this?|||What is the error message exactly? Can you see the package in the MSDB database when looking in Management Studio?

Have you read through this? http://msdn2.microsoft.com/en-us/library/microsoft.sqlserver.dts.runtime.application.loadfromsqlserver.aspx|||The specified package could not be loaded from the SQL Server Database /r/n

I tried both this
Package package = app.LoadFromSqlServer("trans_hist", Properties.Resources.dbServerName, Properties.Resources.userId, Properties.Resources.password, null);

and this
Package package = app.LoadFromSqlServer("\\MSDB\\Maintenance Plans\\trans_hist", "ppntt240", "id", "password", null);

I also tries adding .dtsx to the SSIS name.|||I think you may want:

"\\Maintenance Plans\\trans_hist"|||That was it, thank you.

Problem running SSIS from C# program.

I am able to run it from the file system but when I try running it from SQL Server I get an error message that it cannot load package. I am using the following code.

Package package = app.LoadFromSqlServer("trans_hist", Properties.Resources.dbServerName, Properties.Resources.userId, Properties.Resources.password, null);

Nobody is doing this?
|||What is the error message exactly? Can you see the package in the MSDB database when looking in Management Studio?

I tried both this
Package package = app.LoadFromSqlServer("trans_hist", Properties.Resources.dbServerName, Properties.Resources.userId, Properties.Resources.password, null);

and this
Package package = app.LoadFromSqlServer("\\MSDB\\Maintenance Plans\\trans_hist", "ppntt240", "id", "password", null);

I also tries adding .dtsx to the SSIS name.
|||I think you may want:

"\\Maintenance Plans\\trans_hist"|||That was it, thank you.

Problem running SSIS from C# executable when kicked off by another executable.

If I kick off my C# executable from the server everything works fine but if I have another C# executable kick off the C# executable that runs the SSIS I get an error message saying it cannot run SSIS package. I am thinking it sounds like a permissions problem but not sure. Any suggestions/guidance would be greatly appreciated.

Errors I am getting.

2007-09-26 15:02:30,187 [1] ERROR reporting.Processor [(null)] - Problem with Direct Script

2007-09-26 15:02:30,843 [1] ERROR reporting.Processor [(null)] - {Microsoft.SqlServer.Dts.Runtime.DtsError, Microsoft.SqlServer.Dts.Runtime.DtsError, Microsoft.SqlServer.Dts.Runtime.DtsError, Microsoft.SqlServer.Dts.Runtime.DtsError, Microsoft.SqlServer.Dts.Runtime.DtsError}

Thanks in advance.It appears to hqve a different ID running the executable when kicked off from the first executable which is kicked off from JCL. When I run the Exe it has my id and runs fine. What is a better approach to fixing this, changing the roles on the SSIS scripts or adding the id to SQL Server? Assuming this is the problem.|||You might have to better show the whole picture. Where packages are stored, how many packages, what your "C# executables" do, and then you mention JCL, a mainframe concept. Seems like quite a few pieces here.

Do you have package logging turned on?|||

Can you add a message box in your app to display the current credentials? I'd guess that your user credentials aren't being passed from the first app to the second.

Problem running SSIS from C#

have one script that works fine. I am doing the exact same thging with this new script and it runs fine from C# on my desktop and runs fine from SS on the server but comes back with a failure when trying to run from C# on the server. Is there any additional info I can retrieve about the problem? All I am getting right now is "Failure" from the result field.

if (result == DTSExecResult.Failure)

{

Console.WriteLine("Task failed or abended");

log.Error("Problem with DTS Script");

log.Error(result);

}

else

I would first try looking at the error info in Package.Errors, especially the error description stored in each error object in the collection.|||package.errors did not really give me any useful information. Is there anything else I can look at or canm anybody think of any reason why the SSIS would not work from a C# program but works everyplace else? I have others that work and cannot find any difference between the two.|||If you have other packages that work from your C# program, then it's possible something in that particular package is causing a problem (e.g. missing dependency for a task). I would try to narrow this down by reducing that package is something simpler that works (e.g. by disabling or removing tasks), then start adding back functionality until you get an error.|||

Ted Lee - MSFT wrote:
If you have other packages that work from your C# program, then it's possible something in that particular package is causing a problem (e.g. missing dependency for a task). I would try to narrow this down by reducing that package is something simpler that works (e.g. by disabling or removing tasks), then start adding back functionality until you get an error.

Package runs from the C# on my desktop and runs from SQL Server from the server. It is just after moving the EXE to the server and running it on the server that the SSIS will come back as a "Failure" and bomb.

|||What is the actual error message reported from the package?sql

Problem running SSIS from C#

I am having a problem running a SSIS Script from within a C# program. Script was running fine and then one day it stopped running and is giving me the following errors and nothing has changed.

2007-07-18 14:27:52,098 [1] ERROR reporting.Processor [(null)] - Problem with DTS Script

2007-07-18 14:27:52,895 [1] ERROR reporting.Processor [(null)] - {Microsoft.SqlServer.Dts.Runtime.DtsError, Microsoft.SqlServer.Dts.Runtime.DtsError}

The SSIS runs fine from the process on my machine, from SQL Server on my machine and from SQL Server on the server. But when I run it from the C# executable I get the above errors. This process also runs other SSIS scripts and they all work fine. I am using the following code to execute the script. Can anybody give me some ideas on how to troubleshoot this problem.

Package package = app.LoadFromSqlServer("\\Maintenance Plans\\SCRA2", "ppntt240", "load_abc", "bcp123", null);

DTSExecResult result = package.Execute();

Variables vars = package.Variables;
int rowcount = Convert.ToInt32(vars["count"].Value);
String rowcount2 = "0000000000" + rowcount.ToString();

string fullcnt = rowcount2.ToString().Substring(rowcount2.Length - 10, 10);

if (result == DTSExecResult.Failure)
{
Console.WriteLine("Task failed or abended");
log.Error("Problem with DTS Script");
log.Error(package.Errors);

}
else
{
Console.WriteLine("Task ran successfully");

Can you enable logging in the package so that you can get more detail about the error?|||How do I do this?|||Right-click on the package in the designer, choose Logging, add a log provider (Text is simple to set up, but database is good too), and check the checkbox beside the package name. In the details tab, make sure you check OnError and OnWarning,|||Logging is not an option if I right click. If I select run I see logging and it ask for log provider e.g.(text file, event log or SQL Server) and Configuration String. Is this where I turn it on? What are the best options? Thanks.
|||If you open the package in BIDS, go to the control flow for the package, and right-click in an empty area (one not occupied by a task), you should see the logging option.|||Is there anything unique to the server that would cause the following errors?

PackageStart,PPNTT240,PNCNT\FF22882,SCRA2,{0D01AF9F-5C9C-4B54-942E-2B97FCF12826},{AF032472-7840-4C2B-99D6-692FA7A2AD22},7/19/2007 12:35:21 PM,7/19/2007 12:35:21 PM,0,0x,Beginning of package execution.

OnError,PPNTT240,PNCNT\FF22882,Data Flow Task Direct_Prod,{F36A5050-8FC5-4A2F-A457-42AB134844CF},{AF032472-7840-4C2B-99D6-692FA7A2AD22},7/19/2007 12:35:27 PM,7/19/2007 12:35:27 PM,-1071636471,0x,An OLE DB error has occurred. Error code: 0x80040E07.
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E07 Description: "The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.".

OnError,PPNTT240,PNCNT\FF22882,SCRA2,{0D01AF9F-5C9C-4B54-942E-2B97FCF12826},{AF032472-7840-4C2B-99D6-692FA7A2AD22},7/19/2007 12:35:27 PM,7/19/2007 12:35:27 PM,-1071636471,0x,An OLE DB error has occurred. Error code: 0x80040E07.
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E07 Description: "The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.".

OnTaskFailed,PPNTT240,PNCNT\FF22882,Data Flow Task Direct_Prod,{F36A5050-8FC5-4A2F-A457-42AB134844CF},{AF032472-7840-4C2B-99D6-692FA7A2AD22},7/19/2007 12:35:27 PM,7/19/2007 12:35:27 PM,0,0x,(null)
OnWarning,PPNTT240,PNCNT\FF22882,SCRA2,{0D01AF9F-5C9C-4B54-942E-2B97FCF12826},{AF032472-7840-4C2B-99D6-692FA7A2AD22},7/19/2007 12:35:27 PM,7/19/2007 12:35:27 PM,-2147381246,0x,The Execution method succeeded, but the number of errors raised (2) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.

PackageEnd,PPNTT240,PNCNT\FF22882,SCRA2,{0D01AF9F-5C9C-4B54-942E-2B97FCF12826},{AF032472-7840-4C2B-99D6-692FA7A2AD22},7/19/2007 12:35:27 PM,7/19/2007 12:35:27 PM,1,0x,End of package execution.

|||

agentf1 wrote:
Is there anything unique to the server that would cause the following errors?
OnError,PPNTT240,PNCNT\FF22882,Data Flow Task Direct_Prod,{F36A5050-8FC5-4A2F-A457-42AB134844CF},{AF032472-7840-4C2B-99D6-692FA7A2AD22},7/19/2007 12:35:27 PM,7/19/2007 12:35:27 PM,-1071636471,0x,An OLE DB error has occurred. Error code: 0x80040E07.
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E07 Description: "The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.".

OnError,PPNTT240,PNCNT\FF22882,SCRA2,{0D01AF9F-5C9C-4B54-942E-2B97FCF12826},{AF032472-7840-4C2B-99D6-692FA7A2AD22},7/19/2007 12:35:27 PM,7/19/2007 12:35:27 PM,-1071636471,0x,An OLE DB error has occurred. Error code: 0x80040E07.
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E07 Description: "The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.".

Looks like you have problem with you source data. Since you get out-of-range problem, my bet is that the source date format is messed up. Probably server is expecting mm/dd/yyyy and your source date is dd/mm/yyyy

Thanks.

|||Data is fine. Script runs on server outside of C#.|||Are you absolutely positive that the package is connecting to the same data sources when you run it from C#? I have a hard time believing that an error message on a data conversion error isn't caused by the data

|||As John said, Check for the connections in the data flow task (Direct_Prod) when you run from the C# and in Server.

Thanks|||It is the same. It was running fine and then one day just started bombing. It runs from the server ok and runs from the C# ok when it is on my desktop. Strangest thing I have ever seen.

I recently added the logging so I am sure I have the same version.
|||I figured out my problem. The date comes in like this in most cases 07/09/2007 but comes in like this when run via C# on the server 7/9/2007. I am doing this when I build my query in the string + HolidayEndDt.ToString.Substring(0, 10) +

It is also apparently giving me problems in another spot where I compare month to see if it is EOM and it fails because it is comparing 07 to 7/ since I am using substring for that as well.

What do most people do in these instances? How do you build a query in a string that contains a date? Thanks.
|||

agentf1 wrote:

HolidayEndDt.ToString.Substring(0, 10)
What do most people do in these instances? How do you build a query in a string that contains a date? Thanks.

Assuming you HolidayEndDt is datetime type,

HolidayEndDt.ToString("yyyyMMdd") should solve your problem, and as far as i know thats one of the best way to deal with dates.

Thanks

|||

Yes, that is what I did. Thanks.

Actually I did HolidayEndDt.ToString("MM/dd/yyyy") but I guess there is 6 in one and half dozen in the other. More or less the same thing.

Problem Running Sql Server 2005 Express on Vista

I have been trying to get Sql Server 2005 Express Advanced with SP2 installed and operating on my Vista machine. It seems to install ok but when I open Management Studio Express and try to create a new db or attach an existing one, I get error messages. So although it seems to be installed, I can't really use it.

When I try to create a new database I get:

TITLE: Microsoft SQL ServerManagement Studio Express

----------

Create failed for Database'practice'. (Microsoft.SqlServer.Express.Smo)

For help, click:http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.3042.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Create+Database&LinkId=20476

----------

ADDITIONAL INFORMATION:

An exception occurred whileexecuting a Transact-SQL statement or batch.(Microsoft.SqlServer.Express.ConnectionInfo)

----------

CREATE DATABASE permission denied indatabase 'master'. (Microsoft SQL Server, Error: 262)

For help, click:http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.3042&EvtSrc=MSSQLServer&EvtID=262&LinkId=20476

----------

BUTTONS:

----------

When I try to attach an existing database I get:

TITLE: Microsoft SQL ServerManagement Studio Express

----------

Failed to retrieve data for thisrequest. (Microsoft.SqlServer.Express.SmoEnum)

For help, click:http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&LinkId=20476

----------

ADDITIONAL INFORMATION:

An exception occurred whileexecuting a Transact-SQL statement or batch.(Microsoft.SqlServer.Express.ConnectionInfo)

----------

The server principal"Dave-PC\Dave" is not able to access the database "model"under the current security context. (Microsoft SQL Server, Error: 916)

For help, click:http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.3042&EvtSrc=MSSQLServer&EvtID=916&LinkId=20476

----------

BUTTONS:

----------

I have tried following the links in the error messages but the result is: "We're sorryThereis no additional information about this issue in the Error and EventLog Messages or Knowledge Base databases at this time. You can use thelinks in the Support area to determine whether any additionalinformation might be available elsewhere."

I am successfully using both programs on my XP SP2 machines, so I know how to install and run them on XP, just not on Vista (yet). Any suggestions on how to proceed?

I had alot of VS issues when I switched to Vista. Are you running the management studio as an admin? As a test you may want to turn off UAC and see if that fixes your issue. If it does then you just need to change the permissions that you are running with.

|||

Thanks for the suggestion. It worked and I am able to run Sql Server 2005 Express via Management Studio to create and attached databases.

Question: Do I have to toggle the UAC on and off every time I want to use SQL Server Express (or leave it off always if I am willing to accept the security issues)? Or do I have other options?

|||

I personally turned mine off just because I am the only user of the PC and I'm fairly certain that if I was going to do something harmful to my own PC I'd just turn UAC off at the time I was doing it anyway. There is however a way to set the program to always run in admin mode. I'm on an XP machine right now so I can't post instructions myself but step 3 in the below article should be what you need.

http://4sysops.com/archives/vista%E2%80%99s-uac-8-ways-how-to-elevate-an-application-to-run-it-with-administrator-rights/

|||

Thanks for help. I hope I get equally good advice as I sort out some of my other Vista problems!

problem running sp_addpublication

When I am running sp_addpublication, I am getting the following error. Whats
wrong?
I was able to run the same script before and this stored procedure was
running fine.
Server: Msg 14294, Level 16, State 1, Procedure sp_verify_job_identifiers,
Line 24
Supply either @.job_id or @.job_name to identify the job.
Job 'Server_Name\InstanceNanme-DBName-1' started successfully.
Adam,
I suggest running profiler to see what are the parameters being sent to this
procedure and to debug where the process is going wrong. The most likely
cause is a changed servername, as the error is raised in several system
procedures and the code is usually of the form...
select @.distribution_jobid = job_id from msdb..sysjobs_view where
name = @.name and
UPPER(originating_server) = UPPER(CONVERT(sysname,
SERVERPROPERTY('ServerName')))
if @.distribution_jobid IS NULL
begin
-- Message from msdb.dbo.sp_verify_job_identifiers
RAISERROR(14262, -1, -1, 'Job', @.name)
GOTO UNDO
end
So, if your servername has changed, this could be the cause of the problem.
In this case:
Use Master
go
Select @.@.Servername
This should return your current server name but if it
returns NULL then try:
Use Master
go
Sp_DropServer 'OldName'
GO
Use Master
go
Sp_Addserver 'NewName', 'local'
GO
Stop and Start SQL Services
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Thank you Paul for your response. Your solution does make sense, however in
my case the server name has not changed. I had forgotten to run the script to
create jobs, running it seems to have solve the problem. I am pasting a part
of that script to give you the idea.
if (select count(*) from msdb.dbo.syscategories where name =
N'REPL-LogReader') < 1
execute msdb.dbo.sp_add_category N'REPL-LogReader'
Thanks.
-A
"Paul Ibison" wrote:

> Adam,
> I suggest running profiler to see what are the parameters being sent to this
> procedure and to debug where the process is going wrong. The most likely
> cause is a changed servername, as the error is raised in several system
> procedures and the code is usually of the form...
> select @.distribution_jobid = job_id from msdb..sysjobs_view where
> name = @.name and
> UPPER(originating_server) = UPPER(CONVERT(sysname,
> SERVERPROPERTY('ServerName')))
> if @.distribution_jobid IS NULL
> begin
> -- Message from msdb.dbo.sp_verify_job_identifiers
> RAISERROR(14262, -1, -1, 'Job', @.name)
> GOTO UNDO
> end
> So, if your servername has changed, this could be the cause of the problem.
> In this case:
> Use Master
> go
> Select @.@.Servername
> This should return your current server name but if it
> returns NULL then try:
> Use Master
> go
> Sp_DropServer 'OldName'
> GO
> Use Master
> go
> Sp_Addserver 'NewName', 'local'
> GO
> Stop and Start SQL Services
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>
|||Hi Adam,
I do have the same issue like while buidling my replication using
scripts it is giving the following error
Server: Msg 14294, Level 16, State 1, Procedure sp_verify_job_identifiers,
Line 24
Supply either @.job_id or @.job_name to identify the job
even if I have created the job for 'REPL-LogReader', before creating
publication it is throwing the same error, is there any way that I can solve
this error.
Please help.
Thanks
Ramesh
"Adam" wrote:
[vbcol=seagreen]
> Thank you Paul for your response. Your solution does make sense, however in
> my case the server name has not changed. I had forgotten to run the script to
> create jobs, running it seems to have solve the problem. I am pasting a part
> of that script to give you the idea.
> if (select count(*) from msdb.dbo.syscategories where name =
> N'REPL-LogReader') < 1
> execute msdb.dbo.sp_add_category N'REPL-LogReader'
> Thanks.
> -A
> "Paul Ibison" wrote:

Problem running Proc from Job

I have a stored procedure that runs fine using the Query Analyzer:

exec sp_ProcessRecords

However, when I create a job to run the stored proc once an hour, the job fails, with the following message:

Executed as user: sa. String or binary data would be truncated. [SQLSTATE 22001] (Error 8152) The statement has been terminated. [SQLSTATE 01000] (Error 3621). The step failed.

I don't think it's a permission problem, since the job runs as sa.

I don't understand why it would work if I run it manually, but not when it runs as a job.

Any help would be greatly appreciated.Check your parameters that are being passed to the stored procedure. This means that some parameter that is being passed it too long for the datatype and will be truncated.|||Originally posted by rnealejr
Check your parameters that are being passed to the stored procedure. This means that some parameter that is being passed it too long for the datatype and will be truncated.

Unfortunately, the stored proc called from the job does not take any parameters.

The odd thing is that the stored proc will work fine if it is run manually from the Query Analyzer. The error only occurs when the proc is run from the job, using the exact syntax!

I'm at a loss...|||Can you post the stored proc code - or describe what it is doing ? If you are doing inserts/updates then the same problem can occur.sql

Problem running package with 'larger' amount of data

Dear,

I created a package getting data from files and database sources, doing some transformations, retrieving dimension id's and then inserting it into a fact table.

Running this package with a limited amount of data (about a couple of 100.000 records) does not result in any errors and everything goes fine.

Now running the same package (still in debug mode) with more data (about 2.000.000 rows) doesn't result in any errors as well, but it just stops running. In fact, it doesn't really stop, but it doesn't continue as well. If I've only been waiting for some minutes or hours, I could think it's still processing, but I waited for about a day and it still is 'processing' the same step.

Any ideas on how to dig further into this in order to find the problem? Or is this a known problem?

Thanks for your ideas,

Jievie

Look at the BuffersSpooled perf counter. If it's really up there, you're thrashing the disk.

Also, just look task manager under the performance tab. Look at Physical Memory available. Chances are, you're running up against memory limitations.

Depending on what your pipeline contains, you should be able to optimize out the bottlenecks as well as eliminate any applications running that may be competing for resources.

This white paper is helpful:

http://www.microsoft.com/technet/prodtechnol/sql/2005/ssisperf.mspx

|||

Dear Jievie,

Could it be possible that the package tries to update a row locked by an other application?

Succes,

Paul

|||

Dear KirkHaselden and Paul,

In the meanwhile, I did some checks you both proposed me to do and can tell you the following:

I can't imagine another application is locking any rows or tables, as my sources are some flat files and two tables that are only used by my SSIS package. I also checked the system views (for the tables), no locks are listed...|||

There are three things you're not telling us ... how much memory you have, what transformations you are performing, and how big your rows are.

You are indicating you are memory constrained, but we don't know how much is involved.

The transformations are important - some transformations such as sort, or lookup, or aggregate, can require a lot of memory. Others, such as data conversion are very lightweight.

You can see the size of your rows by double clicking on the paths between transformations and looking at the metadata tab there - it will tell you what data types you are using for each row, and from there you can start to get the size of the row. (Remember folks, "row" is not a useful metric - we've seen rows from a few bytes to several megabytes being passed through SSIS.)

Once you get us this additional information, we should be able to help more. And do read the performance whitepaper - it will give you many useful suggestions.

thanks

Donald

Problem running package

Hi,

I am trying to run simple import export package and if fails saying that pacage execution failed.

then i try using File system and the error say that it is not 32 bit driver and execution failed.

I get this error also

The return value was unknown. The process exit code was -1073741795. The step failed.

thanks

What does your package import/export? If you use 32-bit data providers, you will need to run your package in 32-bit mode.

Thanks.

Problem running multiple SP's

Hello,
I have two SP that are similar to the one below. When trying to run both of
them through Query analyzer the first one will execute properly but the
second one will say that it executed but doesn't really update any fields.
The only difference between the two is the name of the table that gets
updated and the number of tests. If I run either one independently through
there own query windows they both work.
TIA for any help.
CREATE PROCEDURE [dbo].[sp_UpdateRF] AS
-- Update RF Grades
DECLARE @.TestName VARCHAR(30)
DECLARE @.TestGrade CHAR(10)
DECLARE @.Employee_ID char(7)
DECLARE curRF CURSOR FOR SELECT TestName, TestGrade, Import.Employee_ID
FROM Import, RF
WHERE Import.Employee_ID = RF.Employee_ID
AND SUBSTRING(TestName, 1, 1)= '5'
OPEN curRF
WHILE @.@.FETCH_STATUS = 0
BEGIN
FETCH NEXT FROM curRF INTO @.TestName, @.TestGrade, @.Employee_ID
IF SUBSTRING(@.TestName, 3, 1) = '1'
BEGIN
UPDATE RF
SET RF.[1] = @.TestGrade
WHERE @.Employee_ID = RF.Employee_ID
AND RF.[1] IS NULL
END
IF SUBSTRING(@.TestName, 3, 1) = '2'
BEGIN
UPDATE RF
SET RF.[2] = @.TestGrade
WHERE @.Employee_ID = RF.Employee_ID
AND RF.[2] IS NULL
END
IF SUBSTRING(@.TestName, 3, 1) = '3'
BEGIN
UPDATE RF
SET RF.[3] = @.TestGrade
WHERE @.Employee_ID = RF.Employee_ID
AND RF.[3] IS NULL
END
IF SUBSTRING(@.TestName, 3, 1) = '4'
BEGIN
UPDATE RF
SET RF.[4] = @.TestGrade
WHERE @.Employee_ID = RF.Employee_ID
AND RF.[4] IS NULL
END
IF SUBSTRING(@.TestName, 3, 1) = '5'
BEGIN
UPDATE RF
SET RF.[5] = @.TestGrade
WHERE @.Employee_ID = RF.Employee_ID
AND RF.[5] IS NULL
END
IF SUBSTRING(@.TestName, 3, 1) = '6'
BEGIN
UPDATE RF
SET RF.[6] = @.TestGrade
WHERE @.Employee_ID = RF.Employee_ID
AND RF.[6] IS NULL
END
IF SUBSTRING(@.TestName, 3, 1) = 'F'
BEGIN
UPDATE RF
SET RF.F = @.TestGrade
WHERE @.Employee_ID = RF.Employee_ID
END
END
CLOSE curRF
DEALLOCATE curRF
GOOn Thu, 25 Aug 2005 15:14:47 -0700, XImhotep wrote:

>Hello,
>I have two SP that are similar to the one below. When trying to run both of
>them through Query analyzer the first one will execute properly but the
>second one will say that it executed but doesn't really update any fields.
>The only difference between the two is the name of the table that gets
>updated and the number of tests. If I run either one independently through
>there own query windows they both work.
>TIA for any help.
Hi XImhotep,
The problem is that you have some statements in the wrong order. You
should always have a FETCH _before_ testing @.@.FETCH_STATUS. If you don't
have a fetch before that, you'll end up testing the last fetch status of
the previously executed cursor.
The proper order of events is
DECLARE CURSOR
OPEN CURSOR
FETCH FIRST
WHILE @.@.FETCH_STATUS = 0
BEGIN
-- do something
FETCH NEXT
END
CLOSE CURSOR
DEALLOCATE CURSOR
However, most cursors are not needed at all. In 99% of the situations, a
set-based alternative will be faster, shorter, easier to read and hence
easier to maintain.
Your post doesn't reveal enough of your tables to make it worth an
attempt at rewriting it. However, if you post more information about
this, I'll be happy to have a look (and many others will too). See
www.aspfaq.com/5006 for an explanation of the information you should
provide if you s

help.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||XImhotep wrote:
> Hello,
> I have two SP that are similar to the one below. When trying to run
> both of them through Query analyzer the first one will execute
> properly but the second one will say that it executed but doesn't
> <SNIP>
Try adding some debug code to the procedures and see if they are both
running the updates. Also, how are you executing the two procedures? Do
you have two EXEC statements in the same QA window that are being
executed as a batch?
David Gugick
Quest Software
www.imceda.com
www.quest.com|||Thanks for the help. I missed the fetch statement at the bottom. Everyhting
is working now.
Thanks again.
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:08isg1lm0qc9s5f8001gd2relr27k9s568@.
4ax.com...
> On Thu, 25 Aug 2005 15:14:47 -0700, XImhotep wrote:
>
> Hi XImhotep,
> The problem is that you have some statements in the wrong order. You
> should always have a FETCH _before_ testing @.@.FETCH_STATUS. If you don't
> have a fetch before that, you'll end up testing the last fetch status of
> the previously executed cursor.
> The proper order of events is
> DECLARE CURSOR
> OPEN CURSOR
> FETCH FIRST
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> -- do something
> FETCH NEXT
> END
> CLOSE CURSOR
> DEALLOCATE CURSOR
> However, most cursors are not needed at all. In 99% of the situations, a
> set-based alternative will be faster, shorter, easier to read and hence
> easier to maintain.
> Your post doesn't reveal enough of your tables to make it worth an
> attempt at rewriting it. However, if you post more information about
> this, I'll be happy to have a look (and many others will too). See
> www.aspfaq.com/5006 for an explanation of the information you should
> provide if you s

help.
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)

problem running MS SQL Web Data Admin

Hi:
My client machine is XP Pro with SP1 with IIS 5.1 My server machine is
Windows 2000 server SP4 with IIS 5.0. Server has the latest MSDE 2000(SP3a,
in mixed mode) as well as MS SQL Web Data Admin (webadmin) installed on it.
I can use webadmin on the server to access data in MSDE using windows auth
as well as SQL login.
I then installed webadmin on the Client machine but can't get it to connect
to the MSDE on the server machine. I looked at KB article: 319930 and
ensured that test.udl on client XP machine can connect fine using TCP/IP and
sa userid to the MSDE on the server machine. But webadmin won't connect.
Here's the error message:
Server Error in '/webadmin' Application.
COM object with CLSID {10020200-E260-11CF-AE68-00AA004A34D5} is either not
valid or not registered.
Description: An unhandled exception occurred during the execution of the
current web request. Please review the stack trace for more information
about the error and where it originated in the code.
Exception Details: System.Runtime.InteropServices.COMException: COM object
with CLSID {10020200-E260-11CF-AE68-00AA004A34D5} is either not valid or not
registered.
Source Error:
An unhandled exception was generated during the execution of the current web
request. Information regarding the origin and location of the exception can
be identified using the exception stack trace below.
Stack Trace:
[COMException (0x80040154): COM object with CLSID
{10020200-E260-11CF-AE68-00AA004A34D5} is either not valid or not
registered.]
SqlAdmin.SqlServer.Connect()
SqlWebAdmin.databases.Page_Load(Object sender, EventArgs e) +28
System.Web.UI.Control.OnLoad(EventArgs e) +67
System.Web.UI.Control.LoadRecursive() +35
System.Web.UI.Page.ProcessRequestMain() +731
Version Information: Microsoft .NET Framework Version:1.1.4322.573; ASP.NET
Version:1.1.4322.573
Other ASP.NET starter kit apps (like Commerce and Portal) work fine from
Client machine using MSDE on the server. The problem seems to be with
webadmin. Any suggestions would be appreciated.
Vamsee Lakamsani
lakamsani AT gmail.com
The web data admin package needs a dll called SQLDMO.dll that is a com object that is only installed from the full version of SQL server, not with MSDE. That is the com object error message you are receiving.
"vl" wrote:

> Hi:
> My client machine is XP Pro with SP1 with IIS 5.1 My server machine is
> Windows 2000 server SP4 with IIS 5.0. Server has the latest MSDE 2000(SP3a,
> in mixed mode) as well as MS SQL Web Data Admin (webadmin) installed on it.
> I can use webadmin on the server to access data in MSDE using windows auth
> as well as SQL login.
> I then installed webadmin on the Client machine but can't get it to connect
> to the MSDE on the server machine. I looked at KB article: 319930 and
> ensured that test.udl on client XP machine can connect fine using TCP/IP and
> sa userid to the MSDE on the server machine. But webadmin won't connect.
> Here's the error message:
> --
> Server Error in '/webadmin' Application.
> ----
> --
> COM object with CLSID {10020200-E260-11CF-AE68-00AA004A34D5} is either not
> valid or not registered.
> Description: An unhandled exception occurred during the execution of the
> current web request. Please review the stack trace for more information
> about the error and where it originated in the code.
> Exception Details: System.Runtime.InteropServices.COMException: COM object
> with CLSID {10020200-E260-11CF-AE68-00AA004A34D5} is either not valid or not
> registered.
> Source Error:
> An unhandled exception was generated during the execution of the current web
> request. Information regarding the origin and location of the exception can
> be identified using the exception stack trace below.
> Stack Trace:
>
> [COMException (0x80040154): COM object with CLSID
> {10020200-E260-11CF-AE68-00AA004A34D5} is either not valid or not
> registered.]
> SqlAdmin.SqlServer.Connect()
> SqlWebAdmin.databases.Page_Load(Object sender, EventArgs e) +28
> System.Web.UI.Control.OnLoad(EventArgs e) +67
> System.Web.UI.Control.LoadRecursive() +35
> System.Web.UI.Page.ProcessRequestMain() +731
>
>
> ----
> --
> Version Information: Microsoft .NET Framework Version:1.1.4322.573; ASP.NET
> Version:1.1.4322.573
> Other ASP.NET starter kit apps (like Commerce and Portal) work fine from
> Client machine using MSDE on the server. The problem seems to be with
> webadmin. Any suggestions would be appreciated.
> --
> Vamsee Lakamsani
> lakamsani AT gmail.com
>
>

Friday, March 30, 2012

Problem scheduling processing

Problem scheduling job

Problem scheduling DTS packages

Problem scheduling a package

Problem saving/retrieving unicode characters NVARCHAR with unicode collation (java jdbc)

Problem saving/retrieving unicode characters NVARCHAR with unicode collation (java jdbc)

problem running where table2.column1 in (table1.column2)

problem running where table2.column1 in (table1.column2)

problem running where table2.column1 in (table1.column2)

problem running vb.net application with sqlexpress in system with only .NET FRAMEWORK

Problem running stored procedure inside another store procedure

Problem running stored Procedure

Problem running SSIS from C# program.

Problem running SSIS from C# program.

Problem running SSIS from C# executable when kicked off by another executable.

Problem running SSIS from C#

Problem running SSIS from C#

Problem Running Sql Server 2005 Express on Vista

problem running sp_addpublication

Problem running Proc from Job

Problem running package with 'larger' amount of data

Problem running package

Problem running multiple SP's

problem running MS SQL Web Data Admin

xp cmdshell

Blog Archive

About Me