Error Installing SQL Server 2008 (July CTP)

When I install July CTP (do not have any other version of SQL on my laptop)

I am consistently getting an error

Error Code: 18456
MSI (s) (C0!4C) [01:01:33:703]: Product: Microsoft SQL Server 2008 -- Error 29515. SQL Server Setup could not connect to the database service for server configuration. The error was: [Microsoft][SQL Server Native Client 10.0][SQL Server]Login failed for user 'sa'. Refer to server error logs and setup logs for more information. For details on how to view setup logs, see "How to View Setup Log Files" in SQL Server Books Online.

Error 29515. SQL Server Setup could not connect to the database service for server configuration. The error was: [Microsoft][SQL Server Native Client 10.0][SQL Server]Login failed for user 'sa'. Refer to server error logs and setup logs for more information. For details on how to view setup logs, see "How to View Setup Log Files" in SQL Server Books Online.
<Func Name='GetCAContext'>
<EndFunc Name='GetCAContext' Return='T' GetLastError='203'>
Doing Action: Do_sqlScript
PerfTime Start: Do_sqlScript : Tue Aug 28 01:01:33 2007
Service MSSQLSERVER with parameters '-m SqlSetup -Q -qSQL_Latin1_General_CP1_CI_AS -T4022 -T3659 -T3610 -T4010' is being started at Tue Aug 28 01:01:33 2007
Attempt to start service when it is already running
SQL service MSSQLSERVER started successfully waiting for SQL service to accept client connections
Service MSSQLSERVER started at Tue Aug 28 01:01:33 2007
Loaded DLL:
C:\WINDOWS\system32\Odbc32.dll
Version:
3.525.1117.0

SQL_ERROR (-1) in OdbcConnection::connect
sqlstate=28000, level=-1, state=-1, native_error=18456, msg=[Microsoft][SQL Server Native Client 10.0][SQL Server]Login failed for user 'sa'.

Error Code: 0x80074818 (18456)
Windows Error Text: Source File Name: lib\odbc_connection.cpp
Compiler Timestamp: Tue Jul 24 04:09:22 2007
Function Name: OdbcConnection::connect@.connect
Source Line Number: 148

I had the same problem when i installed MS Sql 2008.

I find the firewall deny the port (135) for SQL connecting.

After open the port of the firewall, i re-install sql successfully.

sql

Error Installing SQL Server 2008 (July CTP)

When I install July CTP (do not have any other version of SQL on my laptop)

I am consistently getting an error

Error Code: 18456
MSI (s) (C0!4C) [01:01:33:703]: Product: Microsoft SQL Server 2008 -- Error 29515. SQL Server Setup could not connect to the database service for server configuration. The error was: [Microsoft][SQL Server Native Client 10.0][SQL Server]Login failed for user 'sa'. Refer to server error logs and setup logs for more information. For details on how to view setup logs, see "How to View Setup Log Files" in SQL Server Books Online.

Error 29515. SQL Server Setup could not connect to the database service for server configuration. The error was: [Microsoft][SQL Server Native Client 10.0][SQL Server]Login failed for user 'sa'. Refer to server error logs and setup logs for more information. For details on how to view setup logs, see "How to View Setup Log Files" in SQL Server Books Online.
<Func Name='GetCAContext'>
<EndFunc Name='GetCAContext' Return='T' GetLastError='203'>
Doing Action: Do_sqlScript
PerfTime Start: Do_sqlScript : Tue Aug 28 01:01:33 2007
Service MSSQLSERVER with parameters '-m SqlSetup -Q -qSQL_Latin1_General_CP1_CI_AS -T4022 -T3659 -T3610 -T4010' is being started at Tue Aug 28 01:01:33 2007
Attempt to start service when it is already running
SQL service MSSQLSERVER started successfully waiting for SQL service to accept client connections
Service MSSQLSERVER started at Tue Aug 28 01:01:33 2007
Loaded DLL:
C:\WINDOWS\system32\Odbc32.dll
Version:
3.525.1117.0

SQL_ERROR (-1) in OdbcConnection::connect
sqlstate=28000, level=-1, state=-1, native_error=18456, msg=[Microsoft][SQL Server Native Client 10.0][SQL Server]Login failed for user 'sa'.

Error Code: 0x80074818 (18456)
Windows Error Text: Source File Name: lib\odbc_connection.cpp
Compiler Timestamp: Tue Jul 24 04:09:22 2007
Function Name: OdbcConnection::connect@.connect
Source Line Number: 148

I had the same problem when i installed MS Sql 2008.

I find the firewall deny the port (135) for SQL connecting.

After open the port of the firewall, i re-install sql successfully.

Error inserting into Table Datatype with Identity Column

Hi
We are using the code below to simulate a cursor using the Table Datatype
but we are getting an error when inserting the records.
Server: Msg 8101, Level 16, State 1, Line 19
An explicit value for the identity column in table '@.tblImports' can only be
specified when a column list is used and IDENTITY_INSERT is ON.
Server: Msg 8101, Level 16, State 1, Line 28
An explicit value for the identity column in table '@.tblImports' can only be
specified when a column list is used and IDENTITY_INSERT is ON.
Any help would be much appreciated
Thanks
B
CREATE FUNCTION dbo.fcn_ImportDocs (@.Client VARCHAR(15), @.OrderNo INT, @.Type
VARCHAR(55))
RETURNS VARCHAR(8000) AS
BEGIN
DECLARE @.Output VARCHAR(8000)
DECLARE @.Imports VARCHAR(355)
DECLARE @.Description VARCHAR(355)
DECLARE @.tblImports TABLE(dm_ImportDesc VARCHAR(355), [Description]
VARCHAR(355), RowId INT IDENTITY(1, 1))
DECLARE @.count INT
DECLARE @.iRow INT
SET @.Output = ''
IF @.Type = 'All Items'
BEGIN
INSERT @.tblImports
SELECT dm_ImportDesc, [Description]
FROM Usr_Imports
INNER JOIN
TaskDB.dbo.DM_imports_friendlyName
ON
Usr_Imports.Import_Document collate database_default =
TaskDB.dbo.DM_imports_friendlyName.dm_importdesc collate database_default
ORDER BY Usr_Imports.ID
END
ELSE
BEGIN
INSERT @.tblImports
SELECT dm_ImportDesc, [Description]
FROM Usr_Imports
INNER JOIN
TaskDB.dbo.DM_imports_friendlyName
ON
Usr_Imports.Import_Document collate database_default =
TaskDB.dbo.DM_imports_friendlyName.dm_importdesc collate database_default
WHERE EntityRef = @.Client AND MatterNo =
@.OrderNo
ORDER BY Usr_Imports.ID
END
/** Simulate Cursor
****************************************
****************************/
SET @.count = @.@.ROWCOUNT
SET @.iRow = 1
WHILE @.iRow <= @.count
BEGIN
SELECT @.Imports = dm_ImportDesc, @.Description = [Description]
FROM @.tblImports
WHERE RowId = @.iRow
SELECT @.Output = @.Output + CHAR(11) + CASE WHEN @.Type = 'All
Items' THEN @.Description + CHAR(11) + CHAR(11) END + CHAR(11) + CHAR(11)
SET @.iRow = @.iRow + 1
END
RETURN @.Output
ENDAlways specify the column list in an INSERT statement:
INSERT @.tblImports (dm_ImportDesc, [Description])
SELECT dm_ImportDesc, [Description]
FROM ...
I recommend that you don't use ORDER BY in the INSERT statement. It is
not necessarily guaranteed that the IDENTITY column will be populated
in the order you specify.
If you explain your requirement I'm sure someone can suggest something
better. Why do concatentation and formatting in the database anyway?
David Portas
SQL Server MVP
--|||Ben
error clearly states that you are trying to update an identity column in the
function( not in cucrsor)
INSERT @.tblImports
SELECT dm_ImportDesc, [Description]
you can't update identity column explicitly unless SET INSERT_IDENTITY ON.
Post DDL
--
Regards
R.D
--Knowledge gets doubled when shared
"Ben" wrote:

> Hi
>
> We are using the code below to simulate a cursor using the Table Datatype
> but we are getting an error when inserting the records.
>
> Server: Msg 8101, Level 16, State 1, Line 19
> An explicit value for the identity column in table '@.tblImports' can only
be
> specified when a column list is used and IDENTITY_INSERT is ON.
> Server: Msg 8101, Level 16, State 1, Line 28
> An explicit value for the identity column in table '@.tblImports' can only
be
> specified when a column list is used and IDENTITY_INSERT is ON.
>
> Any help would be much appreciated
>
> Thanks
> B
>
> CREATE FUNCTION dbo.fcn_ImportDocs (@.Client VARCHAR(15), @.OrderNo INT, @.Ty
pe
> VARCHAR(55))
> RETURNS VARCHAR(8000) AS
> BEGIN
>
> DECLARE @.Output VARCHAR(8000)
> DECLARE @.Imports VARCHAR(355)
> DECLARE @.Description VARCHAR(355)
> DECLARE @.tblImports TABLE(dm_ImportDesc VARCHAR(355), [Description]
> VARCHAR(355), RowId INT IDENTITY(1, 1))
> DECLARE @.count INT
> DECLARE @.iRow INT
> SET @.Output = ''
>
> IF @.Type = 'All Items'
> BEGIN
> INSERT @.tblImports
> SELECT dm_ImportDesc, [Description]
> FROM Usr_Imports
> INNER JOIN
> TaskDB.dbo.DM_imports_friendlyName
> ON
> Usr_Imports.Import_Document collate database_default =
> TaskDB.dbo.DM_imports_friendlyName.dm_importdesc collate database_default
> ORDER BY Usr_Imports.ID
> END
> ELSE
> BEGIN
> INSERT @.tblImports
> SELECT dm_ImportDesc, [Description]
> FROM Usr_Imports
> INNER JOIN
> TaskDB.dbo.DM_imports_friendlyName
> ON
> Usr_Imports.Import_Document collate database_default =
> TaskDB.dbo.DM_imports_friendlyName.dm_importdesc collate database_default
> WHERE EntityRef = @.Client AND MatterNo
=
> @.OrderNo
> ORDER BY Usr_Imports.ID
> END
>
> /** Simulate Cursor
> ****************************************
****************************/
> SET @.count = @.@.ROWCOUNT
> SET @.iRow = 1
> WHILE @.iRow <= @.count
> BEGIN
> SELECT @.Imports = dm_ImportDesc, @.Description = [Description]
> FROM @.tblImports
> WHERE RowId = @.iRow
>
> SELECT @.Output = @.Output + CHAR(11) + CASE WHEN @.Type = 'All
> Items' THEN @.Description + CHAR(11) + CHAR(11) END + CHAR(11) + CHAR(11)
> SET @.iRow = @.iRow + 1
> END
>
> RETURN @.Output
> END
>
>|||David,
It's with yukon.
http://blogs.msdn.com/sqltips/archi.../20/441053.aspx
-oj
"David Portas"
> I recommend that you don't use ORDER BY in the INSERT statement. It is
> not necessarily guaranteed that the IDENTITY column will be populated
> in the order you specify.|||Thank you both for your input, it works perfectly now.
Regards
B
"R.D" <RD@.discussions.microsoft.com> wrote in message
news:6FEF31BA-887B-49DA-A0DD-2EA4B07C5A71@.microsoft.com...
> Ben
> error clearly states that you are trying to update an identity column in
the
> function( not in cucrsor)
> INSERT @.tblImports
> SELECT dm_ImportDesc, [Description]
> you can't update identity column explicitly unless SET INSERT_IDENTITY ON.
> Post DDL
> --
> Regards
> R.D
> --Knowledge gets doubled when shared
>
> "Ben" wrote:
>
Datatype
only be
only be
@.Type
database_default
database_default
MatterNo =
[Description]
'All|||> INSERT queries that use SELECT with ORDER BY to populate rows
> guarantees how identity values are computed but not the order
> in which the rows are inserted
Possibly, but the same claim is documented in a KB for 2000 and in that
case it is inaccurate. See the following link for Gert-Jan's repro.
Haven't tested this on 2005 but even if it works I'm not sure how
confident I would be about it, given the history and the kludgy nature
of this "feature".:
http://groups.google.co.uk/group/mi...bfd47d975aca778
Now that we have RECORD_NUMBER() the ORDER BY in an INSERT is
redundant. RECORD_NUMBER() should be preferred IMO.
David Portas
SQL Server MVP
--sql

Error in stored procedure

When I'm trying to execute my stored procedure I'm getting the following code Line 35: Incorrect syntax near'@.SQL'.

Here is my procedure. Could someone tell me what mistake I'm doing.

Alterprocedure [dbo].[USP_SearchUsersCustomers_New]

@.UserIDINT

,@.RepName VARCHAR(50)

,@.dlStatus VARCHAR(5)=''

as

Declare

@.Criteria VARCHAR(500)

,@.SQL VARCHAR(8000)

SELECT @.Criteria=''

SET NOCOUNTON

if(@.dlStatus<>'ALL'AND(LEN(@.dlStatus)>1))

BEGIN

if(@.dlStatus='ALA')

SET @.Criteria='AND dbo.tbl_Security_Users.IsActive=1'

else

SET @.Criteria='AND dbo.tbl_Security_Users.IsActive=0'

END

--If the user is an Admin, select from all users.

if(dbo.UDF_GetUsersRole(@.UserID)= 1)

BEGIN

@.SQL='SELECT U.UserID

--,U.RoleID

,ISNULL((Select TOP 1 R.RoleName From dbo.tbl_Security_UserRoles UR

INNER JOIN dbo.tbl_Security_Roles R ON R.RoleID = UR.RoleID

Where UR.UserID = U.UserID), 'Unassigned') as 'RoleName'

,U.UserName

,U.Name

,U.Email

,U.IsActive

,U.Phone

FROM dbo.tbl_Security_Users U

--INNER JOIN dbo.tbl_Security_Roles R ON U.RoleID = R.RoleID

WHERE U.NAME LIKE @.RepName

AND U.UserTypeID < 3'+ @.Criteria

END

In your dynamic sql string, you need to escape the single quote by using it twice, i.e.: 'RoleName' should be ''RoleName''.

Also, before you build this string. make sure you test out the actual query first.

|||

I tried it still I get the same error "Incorrect Syntax near @.SQL". The query works fine when I execute it alone.

Here is the code again.

if(dbo.UDF_GetUsersRole(@.UserID)= 1)

BEGIN

@.SQL='SELECT U.UserID

--,U.RoleID

,ISNULL((Select TOP 1 R.RoleName From dbo.tbl_Security_UserRoles UR

INNER JOIN dbo.tbl_Security_Roles R ON R.RoleID = UR.RoleID

Where UR.UserID = U.UserID), ''Unassigned'') as ''RoleName''

,U.UserName

,U.Name

,U.Email

,U.IsActive

,U.Phone

FROM dbo.tbl_Security_Users U

--INNER JOIN dbo.tbl_Security_Roles R ON U.RoleID = R.RoleID

WHERE U.NAME LIKE @.RepName

AND U.UserTypeID < 3'+ @.Criteria

END

|||

SET @.SQL = '-- code here'

|||

I did that. Now when I click compile and execute it doesn't show any error. But when I execute the stored procedure it shows an error "Must declare @.UserID". But it's already declared.

|||

You dont need to use dynamic SQL in your scenario. Try something like this with your regular SELECT statement:

ANDdbo.tbl_Security_Users.IsActive= (CASEWHEN@.dlStatus='ALL'THENdbo.tbl_Security_Users.IsActive

WHEN@.dlStatus='ALA'Then1ELSE0END )

|||

Thanks! It worked and I liked the simplicity of the code while achieving the desired task.

error in ssis package!

Hi,

[OLE DB Destination [1146]] Error: An OLE DB error has occurred. Error code: 0x80040E23. An OLE DB record is available. Source: "Microsoft OLE DB Provider for SQL Server" Hresult: 0x80040E23 Description: "Cursor operation conflict".

[DTS.Pipeline] Error: The ProcessInput method on component "OLE DB Destination" (1146) failed with error code 0xC0202009. The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running.

[DTS.Pipeline] Error: Thread "WorkThread0" has exited with error code 0xC0202009.

have any of u encountered this error?

package is working good on source side but destination is indicated in red with the above error,it was working good yesterday,not sure what happened today all of a sudden.

pls help!

Regards,

sg

That's an error returned from SQL Server so I would suggest the problem is in SQL Server rather than SSIS. That's not much help I know but hopefully it gives you some pointes about where to to investigate.

Google turned these up:

http://blogs.conchango.com/kristianwedberg/archive/2006/03/08/3045.aspx (from my friend and fellow Conchango-ite Kristian Wedberg)

http://support.microsoft.com/default.aspx/kb/324900

-Jmie

|||

Hi jamie,

Thanks a lot,ur information was valuable.The package is working.

|||

Cool. If it proved to answer your problem, please could you mark it as an answer.

Thanks.

sql

Error in sending mails !

I am trying to send email thro DTS using Activex Script.

Here is the code :

'**********************************************************************
' Visual Basic ActiveX Script
'************************************************************************

Function Main()
const SMTP_SERVER = "MPBAKOREX01.corp.mphasis.com"

set iMsg = CreateObject("CDO.Message")
set iConf = CreateObject("CDO.Configuration")
Set Flds = iConf.Fields

With Flds
.Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = cdoSendUsingPickup
.Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = SMTP_SERVER
.Item("http://schemas.microsoft.com/cdo/configuration/smtpconnectiontimeout") = 90
.Update
End With

With iMsg
Set .Configuration = iConf
.To = "ausrg@.yahoo.com"
.From = "shanmuga.r@.mphasis.com"
.Subject = "TEST"
.HTMLBody = "jfldsajfldk;sajf ;lksadjf;lkasdjlfkjasdlkfjlasdkj flkdsajflsadjf ljasdlf a"
.Send
End With

Main = DTSTaskExecResult_Success
End Function

When i am executing this , i am getting the following error :

Error Source : Microsoft Data Transformation Services (DTS) Package

Error Description : Error Code: 0

Error Source= CDO.Message.1

Error Description: The "SendUsing" configuration value is invalid.

Error on Line 27

How to solve it ?

Hi,

You question has nothing to do with SSIS (or DTS) and more about CDO programming in VBScript. This is SSIS group, I doubt you'll find many CDO experts here.

The best advice you'll get in this forum is to move to SSIS - there is a nice SMTP Mail Task that will likely do what you need.

Regards,
Michael.

|||

ohh Thanks michael

|||Try searching, this will have been answered before on the DTS newsgroup -http://groups.google.com/advanced_search?q=+group%3Amicrosoft.public.sqlserver.dts|||

Hi DarrenSQLIS,

Thanks. But i dont have access to the site which u mentioned. Can you plz details the article in that site ?

:)

Error in Query

The database scheme consists of four relations:

Product(maker, model, type)
PC(code, model, speed, ram, hd, cd, price)
Laptop(code, model, speed, ram, hd, screen, price)
Printer(code, model, color, type, price)

The relation "Product" shows the maker, model number, and type (pc,
laptop, or printer). It is assumed that model numbers are unique for
all the makers and product types. For each model number specifying pc
in the relation "PC", its listed speed (of the processor in MGz), total
RAM (in MGb), hd capacity (in Gb), CD ROM speed (for example, '4x'),
and the price. The relation "Laptop" is similar to that one of PCs
except for the CD ROM speed which is replaced by screen size (in
inches). For each printer model in the relation "Printer" it is pointed
whether the printer is color or not (color attribute is 'y' for color
printers; otherwise it is 'n'), printer type (laser, jet, or matrix),
and the price.

I need to write a query for Find printer makers.
Result set: maker.

My Query is

select distinct product.maker from product inner join printer on
product.model = printer.model

I get the message
Your query produced correct result set on main database, but it failed
test on second, checking database.
* Wrong number of records (less by 1)

This question is an SQL exercise in URL http://www.sql-ex.ru/

Can anyobe explain me "correct result set on main database," why "but
it failed test on second, checking database."
* Wrong number of records (less by 1)

Thank you very much,

MiksHi Miks

> Can anyobe explain me "correct result set on main database," why "but
> it failed test on second, checking database."
> * Wrong number of records (less by 1)

My guess (emphasis on "guess") is that it has something to do with the fact
that you don't need a second table for this query. The writers of this
website, must be doing some sort of parsing of your query, this is not a
standard SQL Server error message.

This query is sufficient

select distinct maker from Product where type = 'printer'

--
-Dick Christoph
"Miks" <akmeera2k4@.gmail.com> wrote in message
news:1142418307.696828.171550@.j33g2000cwa.googlegr oups.com...
> The database scheme consists of four relations:
> Product(maker, model, type)
> PC(code, model, speed, ram, hd, cd, price)
> Laptop(code, model, speed, ram, hd, screen, price)
> Printer(code, model, color, type, price)
> The relation "Product" shows the maker, model number, and type (pc,
> laptop, or printer). It is assumed that model numbers are unique for
> all the makers and product types. For each model number specifying pc
> in the relation "PC", its listed speed (of the processor in MGz), total
> RAM (in MGb), hd capacity (in Gb), CD ROM speed (for example, '4x'),
> and the price. The relation "Laptop" is similar to that one of PCs
> except for the CD ROM speed which is replaced by screen size (in
> inches). For each printer model in the relation "Printer" it is pointed
> whether the printer is color or not (color attribute is 'y' for color
> printers; otherwise it is 'n'), printer type (laser, jet, or matrix),
> and the price.
> I need to write a query for Find printer makers.
> Result set: maker.
> My Query is
> select distinct product.maker from product inner join printer on
> product.model = printer.model
>
> I get the message
> Your query produced correct result set on main database, but it failed
> test on second, checking database.
> * Wrong number of records (less by 1)
> This question is an SQL exercise in URL http://www.sql-ex.ru/
> Can anyobe explain me "correct result set on main database," why "but
> it failed test on second, checking database."
> * Wrong number of records (less by 1)
> Thank you very much,
> Miks|||homework?|||any place where you can find the answer of these exercies?

im stuck at exercse 10:

Exercise: 10
Find the printers having the highest price.
Result set: model, price.

my query:

select model, max(price)price from printer

http://www.sql-ex.ru/exercises.php#answer_ref

any place for the answers?|||Hi Daniel

Here is one answer (One SQL Query that works)

select Model, Price
from Printer
where price = (select max(price) from Printer)

--
-Dick Christoph
"Daniel" <dtukkers@.gmail.com> wrote in message
news:1143709387.198548.201780@.z34g2000cwc.googlegr oups.com...
> any place where you can find the answer of these exercies?
> im stuck at exercse 10:
> Exercise: 10
> Find the printers having the highest price.
> Result set: model, price.
> my query:
> select model, max(price)price from printer
> http://www.sql-ex.ru/exercises.php#answer_ref
> any place for the answers?

Error in Programming DTS using VB.net

Hi ,

i am using VS.NET 2003 for programming and SQL sever 2005 is my database.

while creating DTS packages,

i am using code as follows

Public Sub Task_Sub4(ByVal goPackage As Object)

Dim oTask As DTS.Task

Dim oLookup As DTS.Lookup

Dim oCustomTask4 As DTS.DataPumpTask2

oTask = CType(goPackage, DTS.Package).Tasks.New("DTSExecuteSQLTask")

oCustomTask4 = CType(oTask.CustomTask, DTS.DataPumpTask2)

oCustomTask4.Name = "Copy Data from contact_info to [content_shriya].[dbo].contact_info] Task"

oCustomTask4.Description = "Copy Data from contact_info to [content_shriya].[dbo].[contact_info] Task"

oCustomTask4.SourceConnectionID = 3

oCustomTask4.SourceSQLStatement = "select * from [Content_management].[dbo].[contact_info]"

oCustomTask4.DestinationConnectionID = 4

oCustomTask4.DestinationObjectName = "[content_shriya].[dbo].[contact_info]"

oCustomTask4.ProgressRowCount = 1000

oCustomTask4.MaximumErrorCount = 0

oCustomTask4.FetchBufferSize = 1

oCustomTask4.UseFastLoad = True

oCustomTask4.InsertCommitSize = 0

oCustomTask4.ExceptionFileColumnDelimiter = "|"

oCustomTask4.ExceptionFileRowDelimiter = vbCrLf

oCustomTask4.AllowIdentityInserts = False

oCustomTask4.FirstRow = 0

oCustomTask4.LastRow = 0

oCustomTask4.FastLoadOptions = 2

oCustomTask4.ExceptionFileOptions = 1

oCustomTask4.DataPumpOptions = 0

'Call oCustomTask4_Trans_Sub1(oCustomTask4)

goPackage.Tasks.Add(oTask)

oCustomTask4 = Nothing

oTask = Nothing

End Sub

But i am geeting error in line

oCustomTask4 = CType(oTask.CustomTask, DTS.DataPumpTask2)

giving error 'System.InvalidCastException'

Additional information: Specified cast is not valid.

i changed it with oCustomTask4 = oTask.CustomTask ,

then also similar error appear.

Please solve my problem, i need to create DTS Packages by using VS.NET 2003 and Database is SQL server 2005.

Thank you

You might have better success posting this in the DTS forum.

I'm not an expert in DTS programming, but since you created oTask as an ExecuteSQLTask, I don't think you can cast it to a DataPumpTask. You need to create it as a DataPumpTask.

|||

Thank you Jhon the code works.

|||Mark the response that was helpful as an answer, please. It helps with searching in the forum.

Error in Merging partitions in AS 2005 from DSO

I have the following code to incrementally process a cube (this is not the full code. I am omiting error handling and other less relevant parts). Basically, I clone the existing partition, process the clone, and then merge it back into the original partition:

Set dsoCube = m_dsoDatabase.MDStores.Item(cubeName)
For Each wrkDimension In dsoCube.Dimensions
m_dsoDatabase.Dimensions(wrkDimension.Name).process processDefault
Next
Set dsoPartition = dsoCube.MDStores.Item(cubeName)
Set dsoClonePartition = dsoCube.MDStores.AddNew(tmpPartition)
dsoPartition.Clone dsoClonePartition, cloneMinorChildren
dsoClonePartition.SourceTableFilter = "Tranno > GetMaxTranNo(cubeName)" ' simplification
dsoClonePartition.process processDefault
dsoPartition.Merge tmpPartition ' Error occurs in SA 2005
dsoPartition.SourceTableFilter = dsoClonePartition.SourceTableFilter
dsoPartition.Update

This works fine in AS 2000. In AS 2005, an error at the step of merging the two partitions is:
Partitions cannot be merged because the source and target partitions have a different number of aggregations.

Manually merging the temporary partition back into the main one from Management studio works without errors.

Any idea on what this might be?

Thanks,
Boris Zakharin, MCAD

Looks like a bug to me.
Please go ahead and file it using http://connect.microsoft.com/sql

I would also like to seize the opportunity and make a little plea for anyone trying to develop DSO applications against Analysis Services 2005:
Please , take a good look at your requirements and at your reasoning to why you are trying to invest in new application code that uses outdated object model. DSO running against AS2005 is there for supporting legacy applications mostly. I would highly discourage anyone from heavily investing in new development using DSO.
Take a look at .NET development using AMO. It is greatly superior to DSO.

HTH.

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

|||Done. I was just wondering, would there be a hotfix for this in the near future. If not, is there a workaround? We need to tell our customers why parts of our software are incompatible with SQL Server 2005.

As far as AMO goes, we must still support customers with SQL 2000 with the same version of our code, so AMO would seem to not be an option. Also, We have multiple utilities written in VB6 that deal with OLAP (In addition to our main ASP.NET web application), so porting to AMO would require rewriting large chunks of code in .NET including code that is unrelated to AS at all.

Thanks for your time,
Boris Zakharin, MCAD
P/A, Metavante Risk and Compliance|||

I am afraid I cannot speak of any dates or avaliablity of any particular fix.
If you have immideate need it is definitely better contact product support directly.

I completely understand the reasoning for this current DSO usage. Only... there should come a moment and you should be switching to AMO. In my humble opinion you should make all the effort and switch as soon as you can. Any application you developing applications using DSO is the the application you'll have to rebuild using AMO.

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

Error in loading big file

Hi All,
I have an application on WL8.1 with sp3, the database is SQL Server 2000. I
have code below to load local file into database. The data type in database
is image.
PreparedStatement pStatement = null;
InputStreammyStream myStream = new InputStream();
myStream.setEmbeddedStream( is );
pStatement.setBinaryStream( 1, myStream, -1 );
pStatement.executeUpdate();
pStatement.close();
pStatement = null;
is is InputStream from a local file and the sql statement is
insert into file_content(content) values(?)
This workes fine for the files with size less than 150M, but for those big
files (>150M), it doesn't work and I got error message as below:
<Feb 11, 2005 12:00:41 PM PST> <Notice> <EJB> <BEA-010014> <Error occurred
while attempting to rollback transaction:
javax.transaction.SystemException: Heuristic hazard:
(weblogic.jdbc.wrapper.JTSXAResourceImpl, HeuristicHazard,
(javax.transaction.xa.XAException: [BEA][SQLServer JDBC Driver]Object has
been closed.))
javax.transaction.SystemException: Heuristic hazard:
(weblogic.jdbc.wrapper.JTSXAResourceImpl, HeuristicHazard,
(javax.transaction.xa.XAException: [BEA][SQLServer JDBC Driver]Object has
been closed.))
at weblogic.transaction.internal.ServerTransactionImp l.internalRollback
(ServerTransactionImpl.java:396)
at weblogic.transaction.internal.ServerTransactionImp l.rollback
(ServerTransactionImpl.java:362)
Any body can help? Thanks in advance.
Message posted via http://www.sqlmonster.com
Fred Wang via SQLMonster.com wrote:

> Hi All,
> I have an application on WL8.1 with sp3, the database is SQL Server 2000. I
> have code below to load local file into database. The data type in database
> is image.
> PreparedStatement pStatement = null;
> InputStreammyStream myStream = new InputStream();
> myStream.setEmbeddedStream( is );
> pStatement.setBinaryStream( 1, myStream, -1 );
> pStatement.executeUpdate();
> pStatement.close();
> pStatement = null;
> is is InputStream from a local file and the sql statement is
> insert into file_content(content) values(?)
> This workes fine for the files with size less than 150M, but for those big
> files (>150M), it doesn't work and I got error message as below:
> <Feb 11, 2005 12:00:41 PM PST> <Notice> <EJB> <BEA-010014> <Error occurred
> while attempting to rollback transaction:
> javax.transaction.SystemException: Heuristic hazard:
> (weblogic.jdbc.wrapper.JTSXAResourceImpl, HeuristicHazard,
> (javax.transaction.xa.XAException: [BEA][SQLServer JDBC Driver]Object has
> been closed.))
> javax.transaction.SystemException: Heuristic hazard:
> (weblogic.jdbc.wrapper.JTSXAResourceImpl, HeuristicHazard,
> (javax.transaction.xa.XAException: [BEA][SQLServer JDBC Driver]Object has
> been closed.))
> at weblogic.transaction.internal.ServerTransactionImp l.internalRollback
> (ServerTransactionImpl.java:396)
> at weblogic.transaction.internal.ServerTransactionImp l.rollback
> (ServerTransactionImpl.java:362)
> Any body can help? Thanks in advance.
>
Hi. That means the DBMS choked on the submission and killed the whole
JDBC connection. Check your DBMS log for problems. You may be running
out of space in the DBMS's transaction log, which must have a separate
copy of your insert data, to prepare for DBMS commit or rollback.
Joe Weinstein at BEA
|||Thanks Joe. I do agree. So what shall I do? We don't have idea how big
could the file be. Is there any way we may cut file into smaller pieces,
ship the to db and reassembly there? Thanks a lot
Message posted via http://www.sqlmonster.com
|||Fred Wang via SQLMonster.com wrote:
> Thanks Joe. I do agree. So what shall I do? We don't have idea how big
> could the file be. Is there any way we may cut file into smaller pieces,
> ship the to db and reassembly there? Thanks a lot
Well, there might be some ugly hacks, but fundamentally you just want to
get some SQLServer DBA help to configure it so it will handle the type of
transaction you want to do. An RDBMS isn't usually ideal as a store for
huge blobs... Sort of like using the bank to deposit those huge polynesian
coral wheel money tokens. Even the islanders stopped moving them around.
One sunk in a bay while being transported by boat, so they just left it there,
and people just started agreeing on who owned it at any time... ;)
Joe Weinstein at BEA
|||| From: "Fred Wang via SQLMonster.com" <forum@.SQLMonster.com>
| Subject: Error in loading big file
| Date: Fri, 11 Feb 2005 23:13:31 GMT
| Organization: http://www.SQLMonster.com
| Message-ID: <2f0972878fc74c0f8e40521ba0ad6ae3@.SQLMonster.com >
| X-Abuse-Report: http://www.SQLMonster.com/Uwe/NB/Abuse.aspx
| Newsgroups: microsoft.public.sqlserver.jdbcdriver
| NNTP-Posting-Host: 178.67-18-207.reverse.theplanet.com 67.18.207.178
| Lines: 1
| Path:
TK2MSFTNGXA01.phx.gbl!cpmsftngxa06.phx.gbl!TK2MSFT NGP08.phx.gbl!tk2msftngp13
.phx.gbl
| Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.jdbcdriver:866
| X-Tomcat-NG: microsoft.public.sqlserver.jdbcdriver
|
| Hi All,
| I have an application on WL8.1 with sp3, the database is SQL Server 2000.
I
| have code below to load local file into database. The data type in
database
| is image.
|
| PreparedStatement pStatement = null;
| InputStreammyStream myStream = new InputStream();
| myStream.setEmbeddedStream( is );
| pStatement.setBinaryStream( 1, myStream, -1 );
| pStatement.executeUpdate();
| pStatement.close();
| pStatement = null;
|
| is is InputStream from a local file and the sql statement is
| insert into file_content(content) values(?)
|
| This workes fine for the files with size less than 150M, but for those big
| files (>150M), it doesn't work and I got error message as below:
|
| <Feb 11, 2005 12:00:41 PM PST> <Notice> <EJB> <BEA-010014> <Error occurred
| while attempting to rollback transaction:
| javax.transaction.SystemException: Heuristic hazard:
| (weblogic.jdbc.wrapper.JTSXAResourceImpl, HeuristicHazard,
| (javax.transaction.xa.XAException: [BEA][SQLServer JDBC Driver]Object has
| been closed.))
| javax.transaction.SystemException: Heuristic hazard:
| (weblogic.jdbc.wrapper.JTSXAResourceImpl, HeuristicHazard,
| (javax.transaction.xa.XAException: [BEA][SQLServer JDBC Driver]Object has
| been closed.))
| at weblogic.transaction.internal.ServerTransactionImp l.internalRollback
| (ServerTransactionImpl.java:396)
| at weblogic.transaction.internal.ServerTransactionImp l.rollback
| (ServerTransactionImpl.java:362)
|
| Any body can help? Thanks in advance.
|
| --
| Message posted via http://www.sqlmonster.com
|
You could insert the new data in chunks by calling UPDATETEXT multiple
times. From Java, you could iteratively call read() on a FileInputStream
object and populate a byte array buffer. For each iteration, you can
execute the stored procedure below and pass the buffer as an input
parameter. This operation may not be logged, depending on the recovery
model of your database (simple or bulk-logged). This may be the way to go
to avoid excessive transaction log usage. However, the downfall is that
you do not get the benefit of a transaction, so a failure along the way
would leave the BLOB data in an incomplete state.
T-SQL
======
CREATE TABLE FredWang([ID] INT PRIMARY KEY, [blob] IMAGE)
GO
INSERT INTO FredWang VALUES(1, CONVERT(VARBINARY(8000), ''))
GO
CREATE PROCEDURE usp_InsertBlob
(
@.row BIGINT,
@.insertOffset INT = NULL,
@.deleteLength INT = 0,
@.data VARBINARY(8000)
)
AS
BEGIN
DECLARE @.textPointer BINARY(16)
SELECT @.textPointer = TEXTPTR(blob) FROM FredWang WHERE [ID] = @.row
DECLARE @.query VARCHAR(4000)
UPDATETEXT FredWang.blob @.textPointer @.insertOffset @.deleteLength @.data
END
GO
Java
=====
import java.sql.*;
import java.io.*;
public class query
{
public static void main(String[] args) throws Exception
{
Connection conn = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
int chunkSize = 8000;
try
{
// Load the JDBC driver
Class.forName("com.microsoft.jdbc.sqlserver.SQLSer verDriver");
// Connect to SQL Server
String url =
"jdbc:microsoft:sqlserver://localhost:1433;DatabaseName=jdbc;";
conn = DriverManager.getConnection(url, "sa", "password");
// Load the file into memory
FileInputStream file = new FileInputStream("c:\\large_file.jpg");
// Insert the BLOB data in chunks
String sql = "EXEC usp_InsertBlob 1, DEFAULT, DEFAULT, ?";
pstmt = conn.prepareStatement(sql, ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_READ_ONLY);
byte [] buff = new byte[chunkSize];
while(file.read(buff) != -1)
{
pstmt.setBytes(1, buff);
pstmt.executeUpdate();
}
file.close();
}
catch (SQLException sqlEx)
{
while (sqlEx != null)
{
System.out.println("SQLState: " + sqlEx.getSQLState());
System.out.println("Message: " + sqlEx.getMessage());
System.out.println("Error code: " + sqlEx.getErrorCode());
sqlEx = sqlEx.getNextException();
System.out.println();
}
}
catch(Exception e)
{
e.printStackTrace();
}
finally
{
// Cleanup
if (rs != null)
rs.close();
rs = null;
if (pstmt != null)
pstmt.close();
pstmt = null;
if (conn != null)
conn.close();
conn = null;
}
}
}
Carb Simien, MCSE MCDBA MCAD
Microsoft Developer Support - Web Data
Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
Are you secure? For information about the Strategic Technology Protection
Program and to order your FREE Security Tool Kit, please visit
http://www.microsoft.com/security.

Error in LoadFromSqlserver

Hi All,

I have created a SSIS package and deployed it on Sql server. I need to load this package from my C# code. when i am trying to execute following code it is giving error "Cannot find folder \MSDB\DevSAE\MeyyDev1" .. whereas this is already present in side stored packages of server.

pkg = app.LoadFromSqlServer(@."\MSDB\DevSAE\MeyyDev1", "BLRKEC36570D", "sa", "SAEuser123", error);

what could be the reason for this ?

Thanks,

Anshu

Have you read through the books online topic for this?

Don't use MSDB in your path. I think you want: \\DevSAE\MeyyDev1

http://msdn2.microsoft.com/en-us/library/microsoft.sqlserver.dts.runtime.application.loadfromsqlserver.aspx|||

thanks Phil,

its working file after removing MSDB. Can u please tell me its reason?

|||

Anshu nautiyal wrote:

thanks Phil,

its working file after removing MSDB. Can u please tell me its reason?

MSDB is not needed because that's the only place to store packages inside SQL Server. So why require it? In effect, you were specifying \msdb\msdb\path\package|||

Phil,

Loading package was succesful but when i m executing this package from my C# code it is giving me error . I have done package level setting in code it self like package password and protection level.

i m reading connection string from config file of my application. The connection string inside app.config is

"Data Source=servername;Initial Catalog=DevSAE ;Provider=SQLNCLI;Integrated Security=SSPI;Auto Translate=false;"

protection level for this package is EncryptSensitiveWithPassword.

The package itself has same setting for password and protection level.

when i m trying to execute this package this is returning following error :

Microsoft.SqlServer.Dts.Runtime.Package/ : Failed to decrypt protected XML node "DTSassword" with error 0x8009000B "Key not valid for use in specified state.". You may not be authorized to access this information. This error occurs when there is a cryptographic error. Verify that the correct key is available

It seems that there is problem with connection string or user id. m not able to trace it. I m new to SSIS i may be doing some silly mistake. but i m not able to resolve it.

can u please give some idea about this?

Thanks

|||

That error is related to ProetctionLevel property of the package. It looks like you are using EncryptWithUSerKey; which means only the author of the apckage can executed. You can use package configuration as described in method 4 in this KB:

http://support.microsoft.com/kb/918760

|||

No i m using EncryptWithPassword protection level. So m trying to pass password to my package before executing it. but its giving me error

Error in Microsoft.SqlServer.Dts.Runtime.Package/ : Failed to decrypt an encrypted XML node because the password was not specified or not correct. Package load will attempt to continue without the encrypted information.

it seems that it is not taking this password. .... is it not pssoible this way ?

Actually i want to execute packages deployed on sql server from my .net code. i m using package object to execute them before that i m setting the password and protection level for them. but still m getting the same error.

Can please help me out on this?

|||how are you executing the package...it should be straight forward as you just need to provide the password in the command line

error in fetching data

hello i'm using visual studio 2005 and asp.net 2 i have an error in the code that fetching data from databse in grid here is the code :

Dim con As SqlConnection
con = New SqlConnection("Data Source=local;AttachDbFilename='D:\New Folder\horus\horus.mdf';Integrated Security=True")
Dim cmd As New String("SELECT TOP (10) serial, name, gender, dateofbirth FROM(dbo.students)ORDER BY RAND(CONVERT(varbinary(4), NEWID()))")
Dim cd As SqlDataAdapter
cd = New SqlDataAdapter(cmd, con)
Dim ds As New DataSet()
cd.Fill(ds, "students")
grid.DataSource = ds.Tables("students").DefaultView
grid.DataBind()

that code is selecting random records from the database and showing it in grid the error is in the cd.fill(ds,"students") line so can any one help me in that problem.thanksAre you sure your query (SQL) statement is valid? I've never seen RAND() specified in ORDER BY clause before. ORDER BY clause usually requires a list of column names. Did you try running the query in MS-SQL directly to see if it works?|||

No, the SQL statement isn't valid, there must be some kind of language barrier here because I've pointed that out twice before, but not because of the RAND(). You can't put your table names in parenthesis like that.

Wrong:

Dim cmd As New String("SELECT TOP (10) serial, name, gender, dateofbirth FROM(dbo.students)ORDER BY RAND(CONVERT(varbinary(4), NEWID()))")

Right:

Dim cmd As New String("SELECT TOP (10) serial, name, gender, dateofbirth FROM dbo.students ORDER BY RAND(CONVERT(varbinary(4), NEWID()))")

|||hello Motley that code is not showing anything as i said in the other topic, anyway i have another code that serch for name and also not working hope u tell me what is the error in that line :
("SELECT tserial,name,subjectname,stage,class FROM teachers WHERE name =' &TextBox1.Text'&", con)

thanks|||

You should change to use parameterized queries in order to avoid SQL Injection attacks.

This will "fix" your problem, but leave a huge security hole in your application:

("SELECT tserial,name,subjectname,stage,class FROM teachers WHERE name='" &TextBox1.Text&"'", con)

Note that that is name, equals, single quote, double quote, ampersand, Textbox1.text, ampersand, double quote, single quote, double quote.

|||hello, ok that code is ok i have another code problem i have two dropdownlist the first one has the name of the tables in my database the second one has a value in column in all these tables the problem is the server cant get the table name from the dropdownlist here is the code :
Dim con As New SqlConnection("Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\mydb.mdf;Integrated Security=True;User Instance=True")
Dim com As New SqlDataAdapter("SELECT * FROM " & ddl1.SelectedValue & "WHERE class = " & ddl2.SelectedValue, con)
Dim ds As New DataSet()
com.Fill(ds, ddl1.SelectedValue)
grid.DataSource = ds.Tables(ddl1.SelectedValue).DefaultView
grid.DataBind()

the table name is in the value of the dropdownlist by the way this a periods tables for a school and every stage has three classes so the column class is in all the tables, hope u can help thanks.|||no one can help?!!! i'm close to finish the site hope u can help.|||Try assigning the SELECT statement into a string variable to verify whether or not there is any syntax error. Also, try out the hard-coded string to make sure the SQL works in the first place. Finally, unless your "class" column is an integer type, you should quote the value using single quotes.|||hello jcasp i tried what u told me the sql statement is alright but the problem is in that dropdown list i typed its value as the name of the table but it doesnt work i tried to put single quotes in that line "WHERE class = " & 'ddl2.SelectedValue', con) i put ddl2.selectedvalue between the single quotes but it put a blue line under the & so hope u can help and by the way the class is not integer it's varchar, hope u can help me soon i'm really close to do it.|||hey guys i just need a little help to make it, i'm waiting ur replies.|||To enclose the value in single quotes, the syntax should be:

"WHERE class ='" & ddl2.SelectedValue & "'", con)
|||

Bad design. Move all the class tables into a single table with a column that has a class name column. Then do this:

Dim conn as new SqlCommand("{Connecting String here}")
conn.open
Dim cmd as new SqlCommand("SELECT * FROM MyClassTable WHEREClassName=@.ClassName AND Class=@.Class",conn)
cmd.parameters.add("@.ClassName",sqldbtype.varchar).Value=ddl1.Selectedvalue
cmd.parameters.add("@.Class",sqldbtype.varchar).Value=ddl2.SelectedValue
dim ds as new DataSet()
dim com as new sqldataadapter(cmd)
com.fill(ds,"MyClassTable")
grid.Datasource=ds.Tables(0).DefaultView
grid.Databind

|||hello Motley i guess i will do as u said and make them all in one table but i will do it with the datasource i just have a last question i want to add my tables to the aspnetdb the default database in visual studio so can u tell me how to do that thanks for ur endless help|||up!!!|||I have no magic way of making it easy. I use Management Studio, and it's not easy -- I run the export/import wizard to move as much as it will, then go back and apply my indexes, trigger, primary keys, etc.

Error in executing a DTS Package from Visual Basic

Hi! Good Day!

I am executing a DTS PAckage from Visual Basic. My code is this:

objPackage.LoadFromSQLServer "SERVER", , , _
DTSSQLStgFlag_UseTrustedConnection, , , , "DTSPackage1"
objPackage.Execute

objPackage.LoadFromSQLServer "SERVER", , , _
DTSSQLStgFlag_UseTrustedConnection, , , , "DTSPackage2"
objPackage.Execute

The first DTS package was executed successfully, but when it hit the second package, an error occurs:

Step 'DTSStep_DTSDataPumpTask_1' already exists in the collection.

Please help.
Thanks.hi

try this

dim objPackage as DTS.Package

set objPackage = new DTS.Package
objPackage.LoadFromSQLServer "SERVER", , , _
DTSSQLStgFlag_UseTrustedConnection, , , , "DTSPackage1"
objPackage.Execute

set objPackage = nothing

set objPackage = new DTS.Package

objPackage.LoadFromSQLServer "SERVER", , , _
DTSSQLStgFlag_UseTrustedConnection, , , , "DTSPackage2"
objPackage.Execute

hope this will solve the problem|||Hi baburajv,

My DTS packages are working well now.
Thank you so much for your help.

God bless :)

Error in Dynamic SQL.....help!!

Can someone please help me in troubleshooting the code below. I have a table called credit_app_table_status which is based on the following create statement:

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[credit_app_table_status]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[credit_app_table_status]
GO

CREATE TABLE [dbo].[credit_app_table_status] (
[table_name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[bill_period_start] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[bill_period_end] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

When I run the code below, it gives me the following error message. I have tried to change the data types of bill_period_start and bill_period_end apart from changing the code, but it doesn't work. Instead of @.bill_period_start and @.bill_period_end, if I have actual dates, it works. Can someone pleaseeeee help.

/* Error Message
Server: Msg 170, Level 15, State 1, Line 12
Line 12: Incorrect syntax near '1'.
Server: Msg 156, Level 15, State 1, Line 27
Incorrect syntax near the keyword 'and'.
*/

--Code

Declare
@.SQL VarChar(2000),
@.tablename varchar (20),
@.bill_period_start varchar (20),
@.bill_period_end varchar (20)

SELECT @.tablename = table_name from credit_app_table_status
SELECT @.bill_period_start = bill_period_start from credit_app_table_status
SELECT @.bill_period_end = bill_period_end from credit_app_table_status

SELECT @.SQL = 'insert into ' + @.TableName + ' (
bill_period_begin,
bill_period_end,
org_id,
bill_type_cn,
qty,
rate,
total_amt,
add_user_id,
add_date
)
select '
+ @.bill_period_start +
','
+ @.bill_period_end +
',
a.org_id,
14,
sum(a.orders)as qty,
-.75 as rate,
sum(a.orders) * -.75,
1493,
getdate()
from cph..tblPkgFlatDaily a
where a.priority_cn = 0
and a.org_id in
(select org_id
from csorg_ins_group where
group_type_cn not in (7,8,9,10)
)
and a.ship_date between '
+ @.bill_period_start +
' and '
+ @.bill_period_end +
'group by a.org_id order by a.org_id'

Exec ( @.SQL)

GO+ @.bill_period_end +
'group by a.org_id order by a.org_id'


Exec ( @.SQL)

GO
It looks like your group by does not have a space preceeding it.

In your testing, why don't you select @.sql and view it first, and then when it looks good try the execution?|||It looks like your group by does not have a space preceeding it.

In your testing, why don't you select @.sql and view it first, and then when it looks good try the execution?
Thanks for the information Tomh3. The problem that I saw was that
@.bill_period_start and @.bill_period_end didn't have the single quotes such as in:
'Sep 1 2004 12:00AM'. Can you please let me know how I can get single quotes in @.bill_period_start and @.bill_period_end.

Thank you so much!!

Below is the code I received using select @.sql:

insert into csorg_billing_flash
(bill_period_begin,
bill_period_end,
org_id,
bill_type_cn,
qty,
rate,
total_amt,
add_user_id,
add_date)
select
Sep 1 2004 12:00AM,
Oct 31 2004 12:00AM ,
a.org_id,
14,
sum(a.orders)as qty,
-.75 as rate,
sum(a.orders) * -.75,
1493,
getdate()
from cph..tblPkgFlatDaily a
where a.priority_cn = 0
and a.org_id in
(select org_id
from csorg_ins_group
where group_type_cn not in (7,8,9,10)
)
and a.ship_date between
Sep 1 2004 12:00AM and Oct 31 2004 12:00AM
group by a.org_id
order by a.org_id|||DECLARE @.bill_period_start datetime
SELECT @.bill_period_start = 'Sep 1 2004 12:00AM'
SELECT ''''+CONVERT(varchar(25),@.bill_period_start)+''''|||Thanks for the reply, Brett.

I think I am very close to getting query in shape. However, when I use
SELECT @.bill_period_start = ''''+CONVERT(varchar(25),@.bill_period_start)+''''

I receive the following error:
Server: Msg 241, Level 16, State 1, Line 24
Syntax error converting datetime from character string.

I will really appreciate if you could please help in troubleshooting this piece of code:

Declare
@.SQL VarChar(2000),
@.tablename varchar (20),
@.bill_period_start datetime,
@.bill_period_end datetime

SELECT @.tablename = table_name from credit_app_table_status
SELECT @.bill_period_start = ''''+CONVERT(varchar(25),@.bill_period_start)+'''' from credit_app_table_status
SELECT @.bill_period_end = ''''+CONVERT(varchar(25),@.bill_period_end)+'''' from credit_app_table_status

SELECT @.SQL = 'insert into ' + @.TableName + ' (
bill_period_begin,
bill_period_end,
org_id,
bill_type_cn,
qty,
rate,
total_amt,
add_user_id,
add_date
)
select '
+ @.bill_period_start +
', '
+ @.bill_period_end +
',
a.org_id,
14,
sum(a.orders)as qty,
-.75 as rate,
sum(a.orders) * -.75,
1493,
getdate()
from cph..tblPkgFlatDaily a
where a.priority_cn = 0
and a.org_id in
(select org_id
from csorg_ins_group where
group_type_cn not in (7,8,9,10)
)
and a.ship_date between '
+ @.bill_period_start +
'and '
+ @.bill_period_end +
'group by a.org_id order by a.org_id'

SELECT (@.SQL)

GO|||Thanks for the reply, Brett.

I think I am very close to getting query in shape. However, when I use
SELECT @.bill_period_start = ''''+CONVERT(varchar(25),@.bill_period_start)+''''

I receive the following error:
Server: Msg 241, Level 16, State 1, Line 24
Syntax error converting datetime from character string.

I will really appreciate if you could please help in troubleshooting this piece of code:

SELECT @.tablename = table_name from credit_app_table_status
SELECT @.bill_period_start = ''''+CONVERT(varchar(25),@.bill_period_start)+'''' from credit_app_table_status
SELECT @.bill_period_end = ''''+CONVERT(varchar(25),@.bill_period_end)+'''' from credit_app_table_status


Oh so close. Try this

SELECT @.bill_period_start = ''''+CONVERT(varchar(25),@.bill_period_start,120)+' ''' from credit_app_table_status
SELECT @.bill_period_end = ''''+CONVERT(varchar(25),@.bill_period_end,120)+''' ' from credit_app_table_status

That will force the string into yyyy-mm-dd format instead of the default presentation format you showed us in the previous output.

And don't forget to keep Brett's additional apostrophes.

FYI, when I do something like this I create a variable named @.apos and populate it with a single apostrophe. So my string concatenation would read like ... + @.apos + @.string_variable + @.apos + ... it makes it easier for me to separate double apostrophe ('') from quote(").|||Oh so close. Try this

SELECT @.bill_period_start = ''''+CONVERT(varchar(25),@.bill_period_start,120)+' ''' from credit_app_table_status
SELECT @.bill_period_end = ''''+CONVERT(varchar(25),@.bill_period_end,120)+''' ' from credit_app_table_status

That will force the string into yyyy-mm-dd format instead of the default presentation format you showed us in the previous output.

And don't forget to keep Brett's additional apostrophes.

FYI, when I do something like this I create a variable named @.apos and populate it with a single apostrophe. So my string concatenation would read like ... + @.apos + @.string_variable + @.apos + ... it makes it easier for me to separate double apostrophe ('') from quote(").
Sorry, but I am still getting the same error message. Also I tried to run it having an apostrophe around @.bill_period_start and @.bill_period_end and still it doesn't work.
For example:

+ ' @.bill_period_start ' +
'and '
+ ' @.bill_period_end ' +|||SELECT @.bill_period_start = ''''+CONVERT(varchar(25),@.bill_period_start)+''''

I receive the following error:
Server: Msg 241, Level 16, State 1, Line 24
Syntax error converting datetime from character string.

That doesn't make sense...

Can you post the DDL of the Table...

And do this as well...

SELECT * FROM cph..tblPkgFlatDaily
WHERE ISNULL(ship_date)=0

If you get anything back from that, you have data problems...|||--Here is the DDL for credit_app_table_status

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[credit_app_table_status]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[credit_app_table_status]
GO

CREATE TABLE [dbo].[credit_app_table_status] (
[table_name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[bill_period_start] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[bill_period_end] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

--Here is the DDL for tblPkgFlatDaily

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tblPkgFlatDaily]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tblPkgFlatDaily]
GO

CREATE TABLE [dbo].[tblPkgFlatDaily] (
[org_id] [int] NULL ,
[Orders] [int] NULL ,
[priority_cn] [int] NOT NULL ,
[ship_date] [datetime] NULL
) ON [PRIMARY]
GO|||SELECT @.bill_period_start = ''''+CONVERT(varchar(25),@.bill_period_start)+''''

I receive the following error:
Server: Msg 241, Level 16, State 1, Line 24
Syntax error converting datetime from character string.

That doesn't make sense...

Can you post the DDL of the Table...

And do this as well...

SELECT * FROM cph..tblPkgFlatDaily
WHERE ISNULL(ship_date)=0

If you get anything back from that, you have data problems...
--Here is the DDL for credit_app_table_status

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[credit_app_table_status]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[credit_app_table_status]
GO

CREATE TABLE [dbo].[credit_app_table_status] (
[table_name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[bill_period_start] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[bill_period_end] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

--Here is the DDL for tblPkgFlatDaily

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tblPkgFlatDaily]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tblPkgFlatDaily]
GO

CREATE TABLE [dbo].[tblPkgFlatDaily] (
[org_id] [int] NULL ,
[Orders] [int] NULL ,
[priority_cn] [int] NOT NULL ,
[ship_date] [datetime] NULL
) ON [PRIMARY]
GO

Error in DataAdapter.Fill

i have this code :
private void Page_Load(object sender, ...)
{
if(! IsPostBack)
{
string strConnection = "server=localhost; uid=sa;pwd=**secret**; database=northwind";
string strCommand = "Select * from Customers";

SqlDataAdapter dataAdapter = newSqlDataAdapter(strCommand, strConnection);

DataSet dataset = new DataSet();

dataAdapter.Fill(dataset, "Products");
SqlCommandBuilder bldr = newSqlCommandBuilder(dataAdapter);

DataTable dataTable = dataset.Tables[0];
dgCustomer.DataSource = dataTable;
dgCustomer.DataBind();
}
}

when i run this code, error like this appear :

Server Error in '/Registeration' Application.

---

SQL Server does not exist or access denied.

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: SQL Server does not exist or access denied.

Source Error:

Line 41: DataSet dataset = new DataSet();
Line 42:
Line 43: dataAdapter.Fill(dataset, "Products");
Line 44: SqlCommandBuilder bldr = new SqlCommandBuilder(dataAdapter);
Line 45:

Source File:e:\asp.net\registeration\register.aspx.cs Line:43

Stack Trace:

[SqlException: SQL Server does not exist or access denied.]
 System.Data.SqlClient.ConnectionPool.GetConnection(Boolean& isInTransaction)
 System.Data.SqlClient.SqlConnectionPoolManager.GetPooledConnection(SqlConnectionString options, Boolean& isInTransaction)
 System.Data.SqlClient.SqlConnection.Open()
 System.Data.Common.DbDataAdapter.QuietOpen(IDbConnection connection, ConnectionState& originalState)
 System.Data.Common.DbDataAdapter.FillFromCommand(Object data, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
 System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
 System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, String srcTable)
 Registeration.WebForm1.Page_Load(Object sender, EventArgs e) in e:\asp.net\registeration\register.aspx.cs:43
 System.Web.UI.Control.OnLoad(EventArgs e)
 System.Web.UI.Control.LoadRecursive()
 System.Web.UI.Page.ProcessRequestMain()

---

Version Information: Microsoft .NET Framework Version:1.1.4322.573; ASP.NET Version:1.1.4322.573

how can i solve this problem? thank you...

Repliedhere

Thanks

Error in Backup

Hai,
In our application, i want to take database backup through
application. So I return code like this, i got a backup in another
folder. But, when i attach that new backup mdf file i am getting error
like this.
Failed to retrieve data for this request. (Microsoft.SqlServer.SmoEnum)
C:\Documents and Settings\user\Desktop\bak\TkmDb.mdf is not a primary
database file. (Microsoft SQL Server, Error: 5171)
My code is
Private Sub BackUpSP()
Dim sQuery As String
Dim oServer As SQLDMO.SQLServer
Dim oCmd As New SqlCommand
Dim sCon As String
Dim sSqlCon As SqlConnection
sCon = "Data Source=(LOCAL);integrated security=SSPI;initial
catalog=Master;"
sSqlCon = New SqlConnection(sCon)
sSqlCon.Open()
MsgBox("Master opened")
sQuery = "EXEC sp_dropdevice 'mydiskdump'"
Call ConnectionExecute(sQuery)
sQuery = "EXEC sp_dropdevice 'mydiskdumplog'"
Call ConnectionExecute(sQuery)
sQuery = "EXEC sp_addumpdevice 'disk', 'mydiskdump', '" &
Trim(txtPath.Text) & "\TkmDb.mdf" & "'"
oCmd = New SqlCommand(sQuery, sSqlCon)
oCmd.ExecuteNonQuery()
oCmd.Dispose()
MsgBox("Device Created")
sQuery = "BACKUP DATABASE TkmDb TO mydiskdump with format"
oCmd = New SqlCommand(sQuery, sSqlCon)
oCmd.ExecuteNonQuery()
oCmd.Dispose()
MsgBox("backup db")
sQuery = "EXEC sp_addumpdevice 'disk', 'mydiskdumpLog', '" &
Trim(txtPath.Text) & "\TkmDb_Log.ldf" & "'"
Call ConnectionExecute(sQuery)
sQuery = "BACKUP LOG TkmDb TO mydiskdumpLog"
Call ConnectionExecute(sQuery)
MsgBox("Backup Log")
sQuery = "EXEC sp_dropdevice 'mydiskdump'"
Call ConnectionExecute(sQuery)
sQuery = "EXEC sp_dropdevice 'mydiskdumplog'"
Call ConnectionExecute(sQuery)
MsgBox("Backup completed successfully!")
End Sub
Please anybody give me the solution for this.
Regards,
Raj.
Attach? did you mean RESTORE? Can you issue just RESTORE command?
"raju" <ponnurajs@.gmail.com> wrote in message
news:1166502932.510647.68420@.48g2000cwx.googlegrou ps.com...
> Hai,
> In our application, i want to take database backup through
> application. So I return code like this, i got a backup in another
> folder. But, when i attach that new backup mdf file i am getting error
> like this.
>
> Failed to retrieve data for this request. (Microsoft.SqlServer.SmoEnum)
> C:\Documents and Settings\user\Desktop\bak\TkmDb.mdf is not a primary
> database file. (Microsoft SQL Server, Error: 5171)
>
> My code is
> --
> Private Sub BackUpSP()
> Dim sQuery As String
> Dim oServer As SQLDMO.SQLServer
> Dim oCmd As New SqlCommand
> Dim sCon As String
>
> Dim sSqlCon As SqlConnection
> sCon = "Data Source=(LOCAL);integrated security=SSPI;initial
> catalog=Master;"
> sSqlCon = New SqlConnection(sCon)
> sSqlCon.Open()
> MsgBox("Master opened")
> sQuery = "EXEC sp_dropdevice 'mydiskdump'"
> Call ConnectionExecute(sQuery)
> sQuery = "EXEC sp_dropdevice 'mydiskdumplog'"
> Call ConnectionExecute(sQuery)
>
> sQuery = "EXEC sp_addumpdevice 'disk', 'mydiskdump', '" &
> Trim(txtPath.Text) & "\TkmDb.mdf" & "'"
> oCmd = New SqlCommand(sQuery, sSqlCon)
> oCmd.ExecuteNonQuery()
> oCmd.Dispose()
> MsgBox("Device Created")
>
> sQuery = "BACKUP DATABASE TkmDb TO mydiskdump with format"
> oCmd = New SqlCommand(sQuery, sSqlCon)
> oCmd.ExecuteNonQuery()
> oCmd.Dispose()
> MsgBox("backup db")
> sQuery = "EXEC sp_addumpdevice 'disk', 'mydiskdumpLog', '" &
> Trim(txtPath.Text) & "\TkmDb_Log.ldf" & "'"
> Call ConnectionExecute(sQuery)
> sQuery = "BACKUP LOG TkmDb TO mydiskdumpLog"
> Call ConnectionExecute(sQuery)
> MsgBox("Backup Log")
> sQuery = "EXEC sp_dropdevice 'mydiskdump'"
> Call ConnectionExecute(sQuery)
> sQuery = "EXEC sp_dropdevice 'mydiskdumplog'"
> Call ConnectionExecute(sQuery)
> MsgBox("Backup completed successfully!")
> End Sub
>
> Please anybody give me the solution for this.
> Regards,
> Raj.
>
|||<snip>

> sQuery = "EXEC sp_addumpdevice 'disk', 'mydiskdump', '" &
> Trim(txtPath.Text) & "\TkmDb.mdf" & "'"
> oCmd = New SqlCommand(sQuery, sSqlCon)
> oCmd.ExecuteNonQuery()
> oCmd.Dispose()
<snip>
Did you look closely at your own logic? What is the actual filename you are
attempting to use as a dump device? Does the actual filename (and, most
notably, the extension) you are using shed any light on why you might be
experiencing problems? You did this same thing with the log file backup.
And why, exactly, do you need to create a dump device for a one time use?
Just backup the database directly to the file! Using an appropriate
filename and extension, of course.

Error in Backup

Hai,
In our application, i want to take database backup through
application. So I return code like this, i got a backup in another
folder. But, when i attach that new backup mdf file i am getting error
like this.
Failed to retrieve data for this request. (Microsoft.SqlServer.SmoEnum)
C:\Documents and Settings\user\Desktop\bak\TkmDb.mdf is not a primary
database file. (Microsoft SQL Server, Error: 5171)
My code is
--
Private Sub BackUpSP()
Dim sQuery As String
Dim oServer As SQLDMO.SQLServer
Dim oCmd As New SqlCommand
Dim sCon As String
Dim sSqlCon As SqlConnection
sCon = "Data Source=(LOCAL);integrated security=SSPI;initial
catalog=Master;"
sSqlCon = New SqlConnection(sCon)
sSqlCon.Open()
MsgBox("Master opened")
sQuery = "EXEC sp_dropdevice 'mydiskdump'"
Call ConnectionExecute(sQuery)
sQuery = "EXEC sp_dropdevice 'mydiskdumplog'"
Call ConnectionExecute(sQuery)
sQuery = "EXEC sp_addumpdevice 'disk', 'mydiskdump', '" &
Trim(txtPath.Text) & "\TkmDb.mdf" & "'"
oCmd = New SqlCommand(sQuery, sSqlCon)
oCmd.ExecuteNonQuery()
oCmd.Dispose()
MsgBox("Device Created")
sQuery = "BACKUP DATABASE TkmDb TO mydiskdump with format"
oCmd = New SqlCommand(sQuery, sSqlCon)
oCmd.ExecuteNonQuery()
oCmd.Dispose()
MsgBox("backup db")
sQuery = "EXEC sp_addumpdevice 'disk', 'mydiskdumpLog', '" &
Trim(txtPath.Text) & "\TkmDb_Log.ldf" & "'"
Call ConnectionExecute(sQuery)
sQuery = "BACKUP LOG TkmDb TO mydiskdumpLog"
Call ConnectionExecute(sQuery)
MsgBox("Backup Log")
sQuery = "EXEC sp_dropdevice 'mydiskdump'"
Call ConnectionExecute(sQuery)
sQuery = "EXEC sp_dropdevice 'mydiskdumplog'"
Call ConnectionExecute(sQuery)
MsgBox("Backup completed successfully!")
End Sub
Please anybody give me the solution for this.
Regards,
Raj.Attach? did you mean RESTORE? Can you issue just RESTORE command?
"raju" <ponnurajs@.gmail.com> wrote in message
news:1166502932.510647.68420@.48g2000cwx.googlegroups.com...
> Hai,
> In our application, i want to take database backup through
> application. So I return code like this, i got a backup in another
> folder. But, when i attach that new backup mdf file i am getting error
> like this.
>
> Failed to retrieve data for this request. (Microsoft.SqlServer.SmoEnum)
> C:\Documents and Settings\user\Desktop\bak\TkmDb.mdf is not a primary
> database file. (Microsoft SQL Server, Error: 5171)
>
> My code is
> --
> Private Sub BackUpSP()
> Dim sQuery As String
> Dim oServer As SQLDMO.SQLServer
> Dim oCmd As New SqlCommand
> Dim sCon As String
>
> Dim sSqlCon As SqlConnection
> sCon = "Data Source=(LOCAL);integrated security=SSPI;initial
> catalog=Master;"
> sSqlCon = New SqlConnection(sCon)
> sSqlCon.Open()
> MsgBox("Master opened")
> sQuery = "EXEC sp_dropdevice 'mydiskdump'"
> Call ConnectionExecute(sQuery)
> sQuery = "EXEC sp_dropdevice 'mydiskdumplog'"
> Call ConnectionExecute(sQuery)
>
> sQuery = "EXEC sp_addumpdevice 'disk', 'mydiskdump', '" &
> Trim(txtPath.Text) & "\TkmDb.mdf" & "'"
> oCmd = New SqlCommand(sQuery, sSqlCon)
> oCmd.ExecuteNonQuery()
> oCmd.Dispose()
> MsgBox("Device Created")
>
> sQuery = "BACKUP DATABASE TkmDb TO mydiskdump with format"
> oCmd = New SqlCommand(sQuery, sSqlCon)
> oCmd.ExecuteNonQuery()
> oCmd.Dispose()
> MsgBox("backup db")
> sQuery = "EXEC sp_addumpdevice 'disk', 'mydiskdumpLog', '" &
> Trim(txtPath.Text) & "\TkmDb_Log.ldf" & "'"
> Call ConnectionExecute(sQuery)
> sQuery = "BACKUP LOG TkmDb TO mydiskdumpLog"
> Call ConnectionExecute(sQuery)
> MsgBox("Backup Log")
> sQuery = "EXEC sp_dropdevice 'mydiskdump'"
> Call ConnectionExecute(sQuery)
> sQuery = "EXEC sp_dropdevice 'mydiskdumplog'"
> Call ConnectionExecute(sQuery)
> MsgBox("Backup completed successfully!")
> End Sub
>
> Please anybody give me the solution for this.
> Regards,
> Raj.
>|||Exactly when is the error occurring?
From the code you posted? If so, which line, and can you use Profiler to catch the actual TSQL
statement executed and try them from a query window?
Or are you trying to attach (sp_attach_db) a database backup file? No can do, a backup file need to
be restored.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"raju" <ponnurajs@.gmail.com> wrote in message
news:1166502932.510647.68420@.48g2000cwx.googlegroups.com...
> Hai,
> In our application, i want to take database backup through
> application. So I return code like this, i got a backup in another
> folder. But, when i attach that new backup mdf file i am getting error
> like this.
>
> Failed to retrieve data for this request. (Microsoft.SqlServer.SmoEnum)
> C:\Documents and Settings\user\Desktop\bak\TkmDb.mdf is not a primary
> database file. (Microsoft SQL Server, Error: 5171)
>
> My code is
> --
> Private Sub BackUpSP()
> Dim sQuery As String
> Dim oServer As SQLDMO.SQLServer
> Dim oCmd As New SqlCommand
> Dim sCon As String
>
> Dim sSqlCon As SqlConnection
> sCon = "Data Source=(LOCAL);integrated security=SSPI;initial
> catalog=Master;"
> sSqlCon = New SqlConnection(sCon)
> sSqlCon.Open()
> MsgBox("Master opened")
> sQuery = "EXEC sp_dropdevice 'mydiskdump'"
> Call ConnectionExecute(sQuery)
> sQuery = "EXEC sp_dropdevice 'mydiskdumplog'"
> Call ConnectionExecute(sQuery)
>
> sQuery = "EXEC sp_addumpdevice 'disk', 'mydiskdump', '" &
> Trim(txtPath.Text) & "\TkmDb.mdf" & "'"
> oCmd = New SqlCommand(sQuery, sSqlCon)
> oCmd.ExecuteNonQuery()
> oCmd.Dispose()
> MsgBox("Device Created")
>
> sQuery = "BACKUP DATABASE TkmDb TO mydiskdump with format"
> oCmd = New SqlCommand(sQuery, sSqlCon)
> oCmd.ExecuteNonQuery()
> oCmd.Dispose()
> MsgBox("backup db")
> sQuery = "EXEC sp_addumpdevice 'disk', 'mydiskdumpLog', '" &
> Trim(txtPath.Text) & "\TkmDb_Log.ldf" & "'"
> Call ConnectionExecute(sQuery)
> sQuery = "BACKUP LOG TkmDb TO mydiskdumpLog"
> Call ConnectionExecute(sQuery)
> MsgBox("Backup Log")
> sQuery = "EXEC sp_dropdevice 'mydiskdump'"
> Call ConnectionExecute(sQuery)
> sQuery = "EXEC sp_dropdevice 'mydiskdumplog'"
> Call ConnectionExecute(sQuery)
> MsgBox("Backup completed successfully!")
> End Sub
>
> Please anybody give me the solution for this.
> Regards,
> Raj.
>|||<snip>
> sQuery = "EXEC sp_addumpdevice 'disk', 'mydiskdump', '" &
> Trim(txtPath.Text) & "\TkmDb.mdf" & "'"
> oCmd = New SqlCommand(sQuery, sSqlCon)
> oCmd.ExecuteNonQuery()
> oCmd.Dispose()
<snip>
Did you look closely at your own logic? What is the actual filename you are
attempting to use as a dump device? Does the actual filename (and, most
notably, the extension) you are using shed any light on why you might be
experiencing problems? You did this same thing with the log file backup.
And why, exactly, do you need to create a dump device for a one time use?
Just backup the database directly to the file! Using an appropriate
filename and extension, of course.

Error in Backup

Hai,
In our application, i want to take database backup through
application. So I return code like this, i got a backup in another
folder. But, when i attach that new backup mdf file i am getting error
like this.
Failed to retrieve data for this request. (Microsoft.SqlServer.SmoEnum)
C:\Documents and Settings\user\Desktop\bak\TkmDb.mdf is not a primary
database file. (Microsoft SQL Server, Error: 5171)
My code is
--
Private Sub BackUpSP()
Dim sQuery As String
Dim oServer As SQLDMO.SQLServer
Dim oCmd As New SqlCommand
Dim sCon As String
Dim sSqlCon As SqlConnection
sCon = "Data Source=(LOCAL);integrated security=SSPI;initial
catalog=Master;"
sSqlCon = New SqlConnection(sCon)
sSqlCon.Open()
MsgBox("Master opened")
sQuery = "EXEC sp_dropdevice 'mydiskdump'"
Call ConnectionExecute(sQuery)
sQuery = "EXEC sp_dropdevice 'mydiskdumplog'"
Call ConnectionExecute(sQuery)
sQuery = "EXEC sp_addumpdevice 'disk', 'mydiskdump', '" &
Trim(txtPath.Text) & "\TkmDb.mdf" & "'"
oCmd = New SqlCommand(sQuery, sSqlCon)
oCmd.ExecuteNonQuery()
oCmd.Dispose()
MsgBox("Device Created")
sQuery = "BACKUP DATABASE TkmDb TO mydiskdump with format"
oCmd = New SqlCommand(sQuery, sSqlCon)
oCmd.ExecuteNonQuery()
oCmd.Dispose()
MsgBox("backup db")
sQuery = "EXEC sp_addumpdevice 'disk', 'mydiskdumpLog', '" &
Trim(txtPath.Text) & "\TkmDb_Log.ldf" & "'"
Call ConnectionExecute(sQuery)
sQuery = "BACKUP LOG TkmDb TO mydiskdumpLog"
Call ConnectionExecute(sQuery)
MsgBox("Backup Log")
sQuery = "EXEC sp_dropdevice 'mydiskdump'"
Call ConnectionExecute(sQuery)
sQuery = "EXEC sp_dropdevice 'mydiskdumplog'"
Call ConnectionExecute(sQuery)
MsgBox("Backup completed successfully!")
End Sub
Please anybody give me the solution for this.
Regards,
Raj.Attach? did you mean RESTORE? Can you issue just RESTORE command?
"raju" <ponnurajs@.gmail.com> wrote in message
news:1166502932.510647.68420@.48g2000cwx.googlegroups.com...
> Hai,
> In our application, i want to take database backup through
> application. So I return code like this, i got a backup in another
> folder. But, when i attach that new backup mdf file i am getting error
> like this.
>
> Failed to retrieve data for this request. (Microsoft.SqlServer.SmoEnum)
> C:\Documents and Settings\user\Desktop\bak\TkmDb.mdf is not a primary
> database file. (Microsoft SQL Server, Error: 5171)
>
> My code is
> --
> Private Sub BackUpSP()
> Dim sQuery As String
> Dim oServer As SQLDMO.SQLServer
> Dim oCmd As New SqlCommand
> Dim sCon As String
>
> Dim sSqlCon As SqlConnection
> sCon = "Data Source=(LOCAL);integrated security=SSPI;initial
> catalog=Master;"
> sSqlCon = New SqlConnection(sCon)
> sSqlCon.Open()
> MsgBox("Master opened")
> sQuery = "EXEC sp_dropdevice 'mydiskdump'"
> Call ConnectionExecute(sQuery)
> sQuery = "EXEC sp_dropdevice 'mydiskdumplog'"
> Call ConnectionExecute(sQuery)
>
> sQuery = "EXEC sp_addumpdevice 'disk', 'mydiskdump', '" &
> Trim(txtPath.Text) & "\TkmDb.mdf" & "'"
> oCmd = New SqlCommand(sQuery, sSqlCon)
> oCmd.ExecuteNonQuery()
> oCmd.Dispose()
> MsgBox("Device Created")
>
> sQuery = "BACKUP DATABASE TkmDb TO mydiskdump with format"
> oCmd = New SqlCommand(sQuery, sSqlCon)
> oCmd.ExecuteNonQuery()
> oCmd.Dispose()
> MsgBox("backup db")
> sQuery = "EXEC sp_addumpdevice 'disk', 'mydiskdumpLog', '" &
> Trim(txtPath.Text) & "\TkmDb_Log.ldf" & "'"
> Call ConnectionExecute(sQuery)
> sQuery = "BACKUP LOG TkmDb TO mydiskdumpLog"
> Call ConnectionExecute(sQuery)
> MsgBox("Backup Log")
> sQuery = "EXEC sp_dropdevice 'mydiskdump'"
> Call ConnectionExecute(sQuery)
> sQuery = "EXEC sp_dropdevice 'mydiskdumplog'"
> Call ConnectionExecute(sQuery)
> MsgBox("Backup completed successfully!")
> End Sub
>
> Please anybody give me the solution for this.
> Regards,
> Raj.
>|||Exactly when is the error occurring?
From the code you posted? If so, which line, and can you use Profiler to cat
ch the actual TSQL
statement executed and try them from a query window?
Or are you trying to attach (sp_attach_db) a database backup file? No can do
, a backup file need to
be restored.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"raju" <ponnurajs@.gmail.com> wrote in message
news:1166502932.510647.68420@.48g2000cwx.googlegroups.com...
> Hai,
> In our application, i want to take database backup through
> application. So I return code like this, i got a backup in another
> folder. But, when i attach that new backup mdf file i am getting error
> like this.
>
> Failed to retrieve data for this request. (Microsoft.SqlServer.SmoEnum)
> C:\Documents and Settings\user\Desktop\bak\TkmDb.mdf is not a primary
> database file. (Microsoft SQL Server, Error: 5171)
>
> My code is
> --
> Private Sub BackUpSP()
> Dim sQuery As String
> Dim oServer As SQLDMO.SQLServer
> Dim oCmd As New SqlCommand
> Dim sCon As String
>
> Dim sSqlCon As SqlConnection
> sCon = "Data Source=(LOCAL);integrated security=SSPI;initial
> catalog=Master;"
> sSqlCon = New SqlConnection(sCon)
> sSqlCon.Open()
> MsgBox("Master opened")
> sQuery = "EXEC sp_dropdevice 'mydiskdump'"
> Call ConnectionExecute(sQuery)
> sQuery = "EXEC sp_dropdevice 'mydiskdumplog'"
> Call ConnectionExecute(sQuery)
>
> sQuery = "EXEC sp_addumpdevice 'disk', 'mydiskdump', '" &
> Trim(txtPath.Text) & "\TkmDb.mdf" & "'"
> oCmd = New SqlCommand(sQuery, sSqlCon)
> oCmd.ExecuteNonQuery()
> oCmd.Dispose()
> MsgBox("Device Created")
>
> sQuery = "BACKUP DATABASE TkmDb TO mydiskdump with format"
> oCmd = New SqlCommand(sQuery, sSqlCon)
> oCmd.ExecuteNonQuery()
> oCmd.Dispose()
> MsgBox("backup db")
> sQuery = "EXEC sp_addumpdevice 'disk', 'mydiskdumpLog', '" &
> Trim(txtPath.Text) & "\TkmDb_Log.ldf" & "'"
> Call ConnectionExecute(sQuery)
> sQuery = "BACKUP LOG TkmDb TO mydiskdumpLog"
> Call ConnectionExecute(sQuery)
> MsgBox("Backup Log")
> sQuery = "EXEC sp_dropdevice 'mydiskdump'"
> Call ConnectionExecute(sQuery)
> sQuery = "EXEC sp_dropdevice 'mydiskdumplog'"
> Call ConnectionExecute(sQuery)
> MsgBox("Backup completed successfully!")
> End Sub
>
> Please anybody give me the solution for this.
> Regards,
> Raj.
>|||<snip>

> sQuery = "EXEC sp_addumpdevice 'disk', 'mydiskdump', '" &
> Trim(txtPath.Text) & "\TkmDb.mdf" & "'"
> oCmd = New SqlCommand(sQuery, sSqlCon)
> oCmd.ExecuteNonQuery()
> oCmd.Dispose()
<snip>
Did you look closely at your own logic? What is the actual filename you are
attempting to use as a dump device? Does the actual filename (and, most
notably, the extension) you are using shed any light on why you might be
experiencing problems? You did this same thing with the log file backup.
And why, exactly, do you need to create a dump device for a one time use?
Just backup the database directly to the file! Using an appropriate
filename and extension, of course.

error in as with rs samples

hi,
I'm using the sample RS code for my cube. I just copied
from the sample code and replaced the cube name with my
cube name ...its working fine with the code given by them
and for microsoft's reports. but when i run my reports its
giving an error msg like ... The expression for the
query 'Selection' contains an error: [BC30648] String
constants must end with a double quote.
here selection is my dataset name... i'm running this thru
report designer in vs.net... could any one tell me wht's
my wrong ...
=IIF(Parameters!pParamIn.Value="", "WITH MEMBER
MEASURES.DIMNAME AS 'NULL' MEMBER MEASURES.UNAME AS 'NULL'
MEMBER MEASURES.PUNAME AS 'NULL' MEMBER MEASURES.LABEL
AS 'NULL' MEMBER MEASURES.SYMBOL AS 'NULL' SELECT
{MEASURES.DIMNAME, MEASURES.UNAME, MEASURES.LABEL,
MEASURES.SYMBOL, MEASURES.PUNAME} ON 0 FROM [Purchase
Order]", "WITH MEMBER MEASURES.DIMNAME AS '" & Parameters!
pMember.Value & ".DIMENSION.NAME' MEMBER MEASURES.UNAME
AS '" & Parameters!pMember.Value
& ".DIMENSION.CURRENTMEMBER.UNIQUENAME' MEMBER
MEASURES.PUNAME AS '" & Parameters!pMember.Value
& ".DIMENSION.CURRENTMEMBER.PARENT.UNIQUENAME' MEMBER
MEASURES.SYMBOL AS 'IIF(" & Parameters!pMember.Value
& ".DIMENSION.CURRENTMEMBER IS " & Parameters!
pMember.Value & ", 1, IIF(COUNT(INTERSECT({" & Parameters!
pMember.Value & ".DIMENSION.CURRENTMEMBER}, {" &
Parameters!pMember.Value & ".SIBLINGS}))=1, 2, 3))' MEMBER
MEASURES.LABEL AS '" & Parameters!pMember.Value
& ".DIMENSION.CURRENTMEMBER.NAME' SELECT
{MEASURES.DIMNAME, MEASURES.UNAME, MEASURES.LABEL,
MEASURES.SYMBOL, MEASURES.PUNAME} ON 0, DRILLDOWNMEMBER({"
& Parameters!pMember.Value & ".SIBLINGS}, {" & Parameters!
pMember.Value & "}) ON 1 FROM [Purchase Order]")
Thanks in advance..You have a typo in the query around one of the IIFs: MEASURES.SYMBOL AS
'IIF(" & Parameters!pMember.Value
--
Ravi Mumulla (Microsoft)
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"alex" <anonymous@.discussions.microsoft.com> wrote in message
news:520501c48089$bbaa46b0$a401280a@.phx.gbl...
> hi,
> I'm using the sample RS code for my cube. I just copied
> from the sample code and replaced the cube name with my
> cube name ...its working fine with the code given by them
> and for microsoft's reports. but when i run my reports its
> giving an error msg like ... The expression for the
> query 'Selection' contains an error: [BC30648] String
> constants must end with a double quote.
> here selection is my dataset name... i'm running this thru
> report designer in vs.net... could any one tell me wht's
> my wrong ...
>
>
> =IIF(Parameters!pParamIn.Value="", "WITH MEMBER
> MEASURES.DIMNAME AS 'NULL' MEMBER MEASURES.UNAME AS 'NULL'
> MEMBER MEASURES.PUNAME AS 'NULL' MEMBER MEASURES.LABEL
> AS 'NULL' MEMBER MEASURES.SYMBOL AS 'NULL' SELECT
> {MEASURES.DIMNAME, MEASURES.UNAME, MEASURES.LABEL,
> MEASURES.SYMBOL, MEASURES.PUNAME} ON 0 FROM [Purchase
> Order]", "WITH MEMBER MEASURES.DIMNAME AS '" & Parameters!
> pMember.Value & ".DIMENSION.NAME' MEMBER MEASURES.UNAME
> AS '" & Parameters!pMember.Value
> & ".DIMENSION.CURRENTMEMBER.UNIQUENAME' MEMBER
> MEASURES.PUNAME AS '" & Parameters!pMember.Value
> & ".DIMENSION.CURRENTMEMBER.PARENT.UNIQUENAME' MEMBER
> MEASURES.SYMBOL AS 'IIF(" & Parameters!pMember.Value
> & ".DIMENSION.CURRENTMEMBER IS " & Parameters!
> pMember.Value & ", 1, IIF(COUNT(INTERSECT({" & Parameters!
> pMember.Value & ".DIMENSION.CURRENTMEMBER}, {" &
> Parameters!pMember.Value & ".SIBLINGS}))=1, 2, 3))' MEMBER
> MEASURES.LABEL AS '" & Parameters!pMember.Value
> & ".DIMENSION.CURRENTMEMBER.NAME' SELECT
> {MEASURES.DIMNAME, MEASURES.UNAME, MEASURES.LABEL,
> MEASURES.SYMBOL, MEASURES.PUNAME} ON 0, DRILLDOWNMEMBER({"
> & Parameters!pMember.Value & ".SIBLINGS}, {" & Parameters!
> pMember.Value & "}) ON 1 FROM [Purchase Order]")
>
> Thanks in advance..
>

Error in a Sinmple Function

Hi,
I am using the following Code in the Report Properties. This function
displays the manufactuere logo when brand parameter is null and when brand
is not null it should display the brand logo.
Function ImageDisp(byVal a as String)as String
Select Case a
case ""
return
"https://192.168.1.16/dev/ReportsImages/"&First(Fields!IMAGEORIG.Value,
"DataSet1");
case else
return
"https://192.168.1.16/dev/ReportsImages/"&First(Fields!BrandLogo.Value,
"DataSet1");
End Select
End Function
In Image2 value is put =Code.ImageDisp(Parameters!Brand.Value) . Now the
problem is that on previewing I am getting the error "There is an error in
line 33 of custom code. Name First is not declared."
Any help is appreciated.
Thanks
--
pmudI believe when the custom code is executed, you cannot refer to fields in the
dataset or use RDL functions. You will have to add a parameter to your
function to pass in the FIRST(Field) value.
"pmud" wrote:
> Hi,
> I am using the following Code in the Report Properties. This function
> displays the manufactuere logo when brand parameter is null and when brand
> is not null it should display the brand logo.
> Function ImageDisp(byVal a as String)as String
> Select Case a
> case ""
> return
> "https://192.168.1.16/dev/ReportsImages/"&First(Fields!IMAGEORIG.Value,
> "DataSet1");
> case else
> return
> "https://192.168.1.16/dev/ReportsImages/"&First(Fields!BrandLogo.Value,
> "DataSet1");
> End Select
> End Function
> In Image2 value is put =Code.ImageDisp(Parameters!Brand.Value) . Now the
> problem is that on previewing I am getting the error "There is an error in
> line 33 of custom code. Name First is not declared."
> Any help is appreciated.
> Thanks
> --
> pmud