Newbie With An Easy Compilation Error Question.

Couple Of Easy Questions, Newbie Here
I am using Merge Replication for my scenario (POS). I have made the publication (articles are a set of tables in DB ABC) and its subscription (in same DB and diff tables xyz1, xyz2). I have scheduled the push subscription as run continuously between Dates(e.g current date to 3 days ahead, just for testing). Now, Can I change this schedule? How can I edit/remove the artilce from the above publication?

View Replies !

---

Compilation Error
I'm trying to connect to an SQL database through my asp.net page and I'm getting an Compiler Error Message: BC30188: Declaration expected for the following codes:

DBConn= New OledbConnection("Provider=sqloledb;" _

DBInsert.Commandtext = "Insert Into GuestInfo" _

DBInsert.Connection =DBConn

DBInsert.Connection.Open

DBInsert ExecuteNonQuery()

What I'm trying to do is connect to the SQL database and input new information to the database.

This is the entire code for connecting and entering info into the database. The SQL Database's name is HMS. I'm stuck and I can't figure it out.

Dim DBConn as oledbConnection
Dim DBInsert As New oledbCommand
DBConn= New OledbConnection("Provider=sqloledb;" _
& "server=localhost;" _
& "Initial Catalog=HMS;" _
& "User id=sa;" _
& "Password=yourpassword;")
DBInsert.Commandtext = "Insert Into GuestInfo" _
& "(FirstName,Lastname,Address,City,State,Zipcode) values ('" _
&"'" & txtFirstName.Text & "', " _
&"'" & txtLastName.Text & "', " _
&"'" & txtAddress.Text & "', " _
&"'" & txtCity.Text &"', " _
&"'" & txtState.Text &"', " _
&"'" & txtZipCode.Text &"', ")"
DBInsert.Connection =DBConn
DBInsert.Connection.Open
DBInsert ExecuteNonQuery()

View Replies !

---

Compilation Error On Store Procedure
Hi all,Here is my error: Server: Msg 245, Level 16, State 1, Procedure NewAcctTypeSP, Line 10Syntax error converting the varchar value 'The account type is already exist' to a column of data type int.Here is my procedure:ALTER PROC NewAcctTypeSP(@acctType VARCHAR(20), @message VARCHAR (40) OUT)ASBEGIN  --checks if the new account type is already exist IF EXISTS (SELECT * FROM AcctTypeCatalog WHERE acctType = @acctType) BEGIN  SET @message = 'The account type is already exist'  RETURN @message END
 BEGIN TRANSACTION  INSERT INTO AcctTypeCatalog (acctType) VALUES (@acctType)  --if there is an error on the insertion, rolls back the transaction; otherwise, commits the transaction  IF @@error <> 0 OR @@rowcount <> 1   BEGIN    ROLLBACK TRANSACTION    SET @message = 'Insertion failure on AcctTypeCatalog table.'    RETURN @message       END  ELSE    BEGIN        COMMIT TRANSACTION   END
 RETURN @@ROWCOUNTENDGO
--execute the procedureDECLARE @message VARCHAR (40);EXEC NewAcctTypeSP 'CDs', @message;I am not quite sure where I got a type converting error in my code and anyone can help me solve it???(p.s. I want to return the @message value to my .aspx page)Thanks.

View Replies !

---

Function Returning Error During Compilation.....
Hi ,

I am creating a function which is going to return a table. The Code ofr the function is as follows...
===============================
Create function udf_qcard (@cg1 varchar(25)) returns @rec_card table (t_cusip varchar(10),t_data varchar(70))
AS
begin
declare @t1_sys char(10),@t1_all varchar(11)
declare @temp_qcard table (tdata varchar(11) collate SQL_Latin1_General_CP1_CS_AS)
if (substring(@cg1,1,2)='Q$')
set @cg1 = (select substring(@cg1,3,len(@cg1)) where substring(@cg1,1,2)='Q$')
DECLARE c1 SCROLL CURSOR FOR select groups_system, substring(groups_alldata,3,10) from tbl_groups
where groups_system = @cg1 and groups_alldata like 'Q$%' and groups_seq>=1 FOR READ ONLY
insert into @temp_qcard values(@cg1)
OPEN C1
FETCH NEXT FROM c1 INTO @t1_sys,@t1_all
WHILE @@FETCH_STATUS = 0
BEGIN

insert into @temp_qcard values(@t1_all)

declare @t2_sys char(10),@t2_all varchar(10)
DECLARE c2 SCROLL CURSOR FOR select groups_system, substring(groups_alldata,3,10) from tbl_groups
where groups_system = @t1_all and groups_alldata like 'Q$%' and groups_seq>=1 FOR READ ONLY

begin
OPEN C2
FETCH NEXT FROM c2 INTO @t2_sys,@t2_all
WHILE @@FETCH_STATUS = 0
BEGIN
insert into @temp_qcard values(@t2_all)

declare @t3_sys char(10),@t3_all varchar(10)
DECLARE c3 SCROLL CURSOR FOR select groups_system, substring(groups_alldata,3,10) from tbl_groups
where groups_system = @t2_all and groups_alldata like 'Q$%' and groups_seq>=1 FOR READ ONLY

begin

OPEN C3
FETCH NEXT FROM c3 INTO @t3_sys,@t3_all
WHILE @@FETCH_STATUS = 0
BEGIN
insert into @temp_qcard values(@t3_all)
FETCH NEXT FROM c3 INTO @t3_sys,@t3_all
end
end
close c3
deallocate c3
FETCH NEXT FROM c2 INTO @t2_sys,@t2_all
end
end
close c2
DEALLOCATE c2

FETCH NEXT FROM c1 INTO @t1_sys,@t1_all
END

CLOSE c1
DEALLOCATE c1
Insert @rec_card select groups_q+groups_cusip,groups_data from tbl_groups
where groups_system in (select tdata from @temp_qcard) and groups_seq>=1 and groups_alldata not like 'Q$%' order by groups_alldata

RETURN
END
==========================

While compiling this I am getting the Below error ....
==================
Server: Msg 1049, Level 15, State 1, Procedure udf_qcard, Line 10
Mixing old and new syntax to specify cursor options is not allowed.
Server: Msg 1049, Level 15, State 1, Procedure udf_qcard, Line 23
Mixing old and new syntax to specify cursor options is not allowed.
Server: Msg 1049, Level 15, State 1, Procedure udf_qcard, Line 35
Mixing old and new syntax to specify cursor options is not allowed.
=================

Can Anyone please help me how to resolve this issue...

Thanks with Regards.

-Mohit.

View Replies !

---

Newbie Here With A Newbie Error - Getting Database ... Already Exists.
Hi there
I sorry if I have placed this query in the wrong place.
I'm getting to grips with ASP.net 2, slowly but surely! 
When i try to access my site which uses a Sql Server 2005 express DB i am receiving the following error:

Server Error in '/jarebu/site1' Application.


Database 'd:hostingmemberasangaApp_DataASPNETDB.mdf' already exists.Could not attach file 'd:hostingmemberjarebusite1App_DataASPNETDB.MDF' as database 'ASPNETDB'.
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: Database 'd:hostingmemberasangaApp_DataASPNETDB.mdf' already exists.Could not attach file 'd:hostingmemberjarebusite1App_DataASPNETDB.MDF' as database 'ASPNETDB'.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:



[SqlException (0x80131904): Database 'd:hostingmemberasangaApp_DataASPNETDB.mdf' already exists.
Could not attach file 'd:hostingmemberjarebusite1App_DataASPNETDB.MDF' as database 'ASPNETDB'.]
System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +735075
System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +188
System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +1838
System.Data.SqlClient.SqlInternalConnectionTds.CompleteLogin(Boolean enlistOK) +33
System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance) +628
System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance) +170
System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection) +359
System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnection owningConnection, DbConnectionPool pool, DbConnectionOptions options) +28
System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject) +424
System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject) +66
System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject) +496
System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection) +82
System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory) +105
System.Data.SqlClient.SqlConnection.Open() +111
System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +121
System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +137
System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, String srcTable) +83
System.Web.UI.WebControls.SqlDataSourceView.ExecuteSelect(DataSourceSelectArguments arguments) +1770
System.Web.UI.DataSourceView.Select(DataSourceSelectArguments arguments, DataSourceViewSelectCallback callback) +17
System.Web.UI.WebControls.DataBoundControl.PerformSelect() +149
System.Web.UI.WebControls.BaseDataBoundControl.DataBind() +70
System.Web.UI.WebControls.GridView.DataBind() +4
System.Web.UI.WebControls.BaseDataBoundControl.EnsureDataBound() +82
System.Web.UI.WebControls.CompositeDataBoundControl.CreateChildControls() +69
System.Web.UI.Control.EnsureChildControls() +87
System.Web.UI.Control.PreRenderRecursiveInternal() +41
System.Web.UI.Control.PreRenderRecursiveInternal() +161
System.Web.UI.Control.PreRenderRecursiveInternal() +161
System.Web.UI.Control.PreRenderRecursiveInternal() +161
System.Web.UI.Control.PreRenderRecursiveInternal() +161
System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +1360



Version Information: Microsoft .NET Framework Version:2.0.50727.42; ASP.NET Version:2.0.50727.210
 
 This is the connection string that I am using:
 <connectionStrings>
<add name="ConnectionString" connectionString="Data Source=.SQLEXPRESS;AttachDbFilename=|DataDirectory|ASPNETDB.MDF;Integrated Security=True;Initial Catalog=ASPNETDB;User Instance=True" providerName="System.Data.SqlClient"/>
</connectionStrings>
 
The database is definitly in the folder that the error message relates to.
What I'm finding confusing is that the connection string seems to be finding "aranga"s database.
Is it something daft?
 
Many thanks.
James 

View Replies !

---

Prevent SP Compilation
Hi,I'm using SQL Server 2000 MSDE on a laptop running Windows XP.I have a couple of SP's that that quite some time to compile. So I waswondering: is there any way to have the database *not* recompile them everytime after a reboot?BOL says: "As a database is changed by such actions as adding indexes orchanging data in indexed columns, the original query plans used to accessits tables should be optimized again by recompiling them. This optimizationhappens automatically the first time a stored procedure is run afterMicrosoft® SQL ServerT 2000 is restarted."Now the SQL Server is restarted a lot, because laptops don't have endlessbatteries <g>Cheers,Bas

View Replies !

---

Avoiding Compilation
Using small stored procs or sp_executesql dramatically reduces the number ofrecompiles and increases the reuse of execution plans. This is evident fromboth the usecount in syscacheobjects, perfmon, and profiler. However I'm ata loss to determine what causes a compilation. Under rare circumstances theusecount for Compiled Plan does not increase as statements are run. Seemsto correspond to when there is no execution plan. It would seem to me thatcompilation is a resource intensive task that if possible (data and schemaare not changing) should be held to a minimum.How does one encourage the reuse of compile plans?Is this the same as minimizing compilation?Looks like some of this behavior is changing in SQL 2005....Thanks,Danny

View Replies !

---

Compilation / Re-build Issue
 
Hi,
 We are using .Net 2.0 for developing our application, All the file in this application are source safed, Whenever we  do modification in the code it take longer time to build  approax it takes around 2 min to display the default page (login page).
 
Please do send out your suggestions to reduce the time take for the build, is there any setting need to be done in IDE to make the build process much faster.
 
Regards
K.Karthik Doss

View Replies !

---

SP Compilation Confirmation Message?
How can we say whether the SP is successfully compiled or not if we are compiling it on the server as a part of the TSQL script since it does not throw any message like ORACLE does.

In oracle, system will let you know whether the the procedure is successfully complied or not?

Thanks/

View Replies !

---

SQL Compilation And Execution Plan
Hi all,

I€™m having a test regarding to the image data type. The test program is written with sql native api and just update the image data type column, but I looked the SQL Compilations/sec and Batch Requests/sec counters in SQLServer:QL Statistics using Perfmon, both values are almost the same. It seemed whenever the stored procedure is called, SQLServer compiles it and makes execution plan again. But when I had a test without image data type, SQL Compilation/sec was 0. SQL version is Microsoft SQL Server 2005 - 9.00.3054.00 (Intel X86) (Build 2600: Service Pack 2).

Is SQL server working the way expected or am I missing something?

View Replies !

---

Compilation Of Stored Procs
 Hi,

I would like to know if the execution plans of stored procs also get migrated when we do migration to 2005 from 2000 using attachdetach method or we will need to re-run the stored procs?

The thing is when I am running the Stored procs in 2005, its performing  really slow in first run.

Any help in his regard is highly appreciated.

Thanks,
Ritesh

 

 

 

 

 

View Replies !

---

DTS Table From Access To SQL, Identity Column Error. Should Be Easy, But I Can't Figure It Out.
 

I am trying to move data from Access to SQL Server 2000 using DTS.
 
I have an Access Source and SQL Server Desitination, My destination table has a field called tableID that is not in the source.  TableID is a Primary Key and an Identity column.
 
I have Enable Identity Insert checked in the options of the Transform Data Task. 
 
When I execute ythe task, I get the error
 
"Cannot insert the value NULL into column 'TableID'.  Does not allow nulls.  Insert Fails.
 
Does anyone know why this simple task would fail?
 
Mike
 
 
 

View Replies !

---

C++ Ole DB Stack Overflow During Sql Server Compilation
hi,when i execute :CCommand<CManualAccessor, CBulkRowset, CNoMultipleResults> rs;rs.SetRows(100);HRESULT code_resultat = rs.Open(session, requete, &propset, NULL,DBGUID_DBSQL, FALSE);with a requete with length = 13000, it works perfectlybut when my requete length is 200000 (example : SELECT * FROM myTABLEWHERE id_table IN("lot of number : more then 30000 number"))i have code_resultat = DB_E_ERRORSINCOMMAND (= 0x80040e14)and when i explore the IErrorInfo message, i have :minor = 565 and the message issource :Microsoft OLE DB Provider for SQL Serverserveur has made a stack overflow during compilation...Is there a solution to extract to data ?in a fast way ...thanks in advance ...Mike

View Replies !

---

SSIS Package Compilation And Execution
I am wondering something, once we've created a job that executes a package at a given time interval, does that package get recompiled each time the job spins up and executes the package? Or is the package compiled once and then that compiled code is executed each run after the first run?

What I'm seein is this; I have a package that reads data from flat text files and then dumps that data into the database. The package will take 3 minutes to execute when executing on a single file, but when it's looping through ~50 files, it will take ~30 minutes to execute, that is less than a minute per file. Why is this?

Hopefully I'm just forgetting something and not setting a checkbox or radio button somewhere. The job is set up as an SSIS job, not as a command line job.

Thanks in advance for any help you can give me.

Wayne E. Pfeffer
Sr. Systems Analyst
Hutchinson Technolgy Inc.

View Replies !

---

Delaying Compilation Real Time
Hi,
I would like to find out about SSIS compilation. Can you mention anything regarding this issue or can you point me out to a website for this topic please?

Thanks

View Replies !

---

Newbie Need Help. Export Error, Null Value Error.
Hey all,

I'm currently using a shopping cart software called .netCart, which is in ASP.NET and VB. I have been trying to import my local "fully working" database to my remote server using Enterprise Manager via the DTS Export/Import Wizard, but then the exported remote database is not working with the software. Unfortunately, I have no prior experiences in MS SQL database at all, nor do I know anything about the scripts they used with the software I purchased.

Error message when attempting to run part of the software (website) with the exported database -

"Cannot insert the value NULL into column 'CustomerID', table 'tablename.dbo.Customers'; column does not allow nulls. INSERT fails. The statement has been terminated. [Customer Table]"

I simply used the DTS wizard to export the database. All settings are left "default" because I don't know what I should do about them....

1) At Specify Table Copy or Quert, "Copy table(s) and view(s) from the source database is selected.
2) Selected all tables. At the "Column Mappings and Transformations" window for all tables, "Create detination table" is SELECTED and "Enable identify insert" is UNCHECKED.
3) At one of the error table (i.e. Customers), the CustomerID int is NOT NULL.

Sorry, I do not know what's wrong with it, and I don't know how to explain it better technically. The software company do not support this as well.

I believe the problem is with the settings in the DTS wizard when I try to export the database. Something is not set right, but I don't know how to do it.

Can anyone please try to solve this problem for me? Thank you very much.

Temjin

View Replies !

---

Ignore Compilation Errors For Creation Of Stored Procedures
I have an application that is moving from an home made full text search engine to using the full text indexing engine of SQL 2005.  I have a stored procedure that I want to behave as:
check documents table to determine whether a full text index for SQL's full text engine has been created.
If it has not, query the documentText table (which is the table for my in-house full text search)
If it has, use the full text indexing engine 
 
My problem is that compilation of the TSQL to create the stored procedure fails when the full text index has not already been created with the followign error:
 

Msg 7601, Level 16, State 2, Procedure My_FullTextSearch, Line 0

Cannot use a CONTAINS or FREETEXT predicate on table or indexed view 'Documents' because it is not full-text indexed.
 
In my test lab, I tried:
1. creating the full text index
2. creating the stored procedure 
3. deleting the ful text index
which gets me to the desired end result of having a stored procedure that can determine whether or not the full text index has been created yet (the procedure works in this state).  But I creating this index as part of this stored procedure creation in production is not an option.
 
My question - Can I somehow tell SQL to ignore the compilation errors it encounters while creating this stored procedure?  If not, is there some other way to create this "smart" stored procedure?
 
Here's a code snippet stripped down to the bare minimum to generate the error:
 
 

CREATE PROCEDURE [My_FullTextSearch]

@Term VarChar(1000)

AS

BEGIN

SET NOCOUNT ON;

IF NOT OBJECTPROPERTY(OBJECT_ID('Documents'), 'TableHasActiveFulltextIndex')=1

BEGIN

Select [DocumentID]

from [DocumentText]

where [Term] like '%' + LTRIM(@Term) + '%'

END

ELSE

BEGIN

Select [key] from FREETEXTTABLE(Documents, Contents, @Term)

END

END

 

View Replies !

---

Upgraded Express To Developer Edition Is Not As Easy As Advertised; Error When Adding A Database To Website
In trying to upgrade my sql express install from express to developer edition, I ended up having to delete all things express from my computer and do a clean install of developer edition (as i installed developer edition initially over sql express but this didnt seem to work;  express edition still appeared in add remove programs etc).  Anyway so i blew away the install and have a clean install of developer edition.  Now when I create a new website in Visual Studio 2005 and try to add a database to the project, it gives me an error stating that i need to have sql server express 2005 installed to function properly...  Do i need to have express AND developer edition of sql installed to do this?  I would think developer edition of sql would enable me this functionality...http://img277.imageshack.us/img277/4228/sqlerrorie2.jpgThanks!It is also worth noting that I get the dreaded remote connection error when trying to connect to a db that I used to be able to connect to when I was using sql express (the aspnetdb database that is created when you user their membership stuff)"An error has occurred while establishing a connection to the server.  When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified) "However, I did the following successfully:1. create a database using the sql management studio2. added a table and some data to it3. threw a dataset into the website4. added a gridview to a page in the website, pulling data from the dataset5. and the website runs successfully populating the page with data...fyi: My sql services: http://img158.imageshack.us/img158/2020/sqlserviceshj6.jpg

View Replies !

---

Stored Procedure Compilation Question: Doing Disparate Things In Aproc
To minimize the very large number of stored procedures typicallyassociated with an application, I have gotten in the habit ofcombining a select, insert, update, and delete all in one procedure,and passing an argument to indicate which to use. (I use defaultvalues for all input params to avoid having to declare them forselects and deletes.) So I'll have just one PersonAdmin proc insteadof PersonGet, PersonInsert, PersonUpdate, and PersonDelete procsWhile this is nice for housekeeping, I wonder what the compiler doeswith such an architecture,and I fear the worst. The select returns arecordset; the others don't.Is this a bad idea?If it is, I really wish SQL would permit some sort of user folderstructure in the proc list.

View Replies !

---

Newbie Error
Hi guys,
Your help please.
First off, I have searched the Forum for answers using the keywords and tried those clues first but with no luck. This one for example: http://support.microsoft.com/default.aspx?scid=kb;EN-US;Q282254

I had a copy of MSDE installed on my machine about 12 month ago but never used it so I uninstalled it. Then I downloaded and installed SQL Server 120-day Evaluation edition a few days ago. When I tried to run SQL Server I get
An error 1069 – (The service did not start due to logon failure)…

I’m using Windows XP.

How can I find out what the password is or how can I reset it so the SQL Server logon works?

Any ideas?

Thanks Guys


FB

View Replies !

---

Error Handling Newbie
I have several stored procedures which are running correctly, but which do not have
any error checking within them. I have tried using the @@error variable to
detect errors and rollback the transaction, yet it always returns a successfull
zero despite errors I've been forcing to happen. Any words of wisdom or
reference resources about how error handling is done in SQL 6.5 will be most appreciated.


thanks in advance...

View Replies !

---

Function Error (newbie)
eh guys ma kind of newbie n tis ... k like wrote a function like

CREATE FUNCTION [dbo].[GetDetailText]
{
@PackingID int,
@PackingType int
}
RETURNS varchar(100)


AS

BEGIN

DECLARE @DetailText varchar(100)

IF (@PackingType = 1)
BEGIN


SET @DetailText = (SELECT
'Tickness' + ':' + CONVERT(varchar, dbo.KF_PackMaterial.thickness) + '' + CONVERT(varchar, KF_Unit_3.Symbol)
+ ',' + 'Width' + ': ' + CONVERT(varchar, dbo.KF_PackMaterial.width) + '' + CONVERT(varchar, KF_Unit_1.Symbol)
+ ',' + 'InnerDia' + ':' + CONVERT(varchar, dbo.KF_PackMaterial.india) + '' + CONVERT(varchar, KF_Unit_5.Symbol)
+ ',' + 'OuterDia' + ':' + CONVERT(varchar, dbo.KF_PackMaterial.outdia) + '' + CONVERT(varchar, KF_Unit_6.Symbol)
+ ',' + 'Recycle' + ':' + CONVERT(varchar, dbo.KF_PackMaterial.recycle) + ', ' + 'Shade' + ':' + CONVERT(varchar, dbo.KF_PackMaterial.shade)
+ ',' + 'Weight' + ':' + CONVERT(varchar, dbo.KF_PackMaterial.weight) + '' + CONVERT(varchar, KF_Unit_4.Symbol)
+ ',' + 'Perforation' + ':' + CONVERT(varchar, dbo.KF_PackMaterial.perforation) + ' (' + CONVERT(varchar, dbo.KF_PackingMaterial.PackingTypeName)
+ ')'
FROM dbo.KF_CartonImageList RIGHT OUTER JOIN
dbo.KF_PackMaterial INNER JOIN
dbo.KF_PackType ON dbo.KF_PackMaterial.type = dbo.KF_PackType.PackID INNER JOIN
dbo.KF_Unit KF_Unit_1 ON dbo.KF_PackMaterial.widthunit = KF_Unit_1.UnitID INNER JOIN
dbo.KF_PackingMaterial ON dbo.KF_PackMaterial.packmaterialid = dbo.KF_PackingMaterial.Packingid LEFT OUTER JOIN
dbo.KF_Color ON dbo.KF_PackMaterial.ColorId = dbo.KF_Color.ColorId LEFT OUTER JOIN
dbo.KF_Unit KF_Unit_6 ON dbo.KF_PackMaterial.outdiaunit = KF_Unit_6.UnitID LEFT OUTER JOIN
dbo.KF_Unit KF_Unit_5 ON dbo.KF_PackMaterial.indiaunit = KF_Unit_5.UnitID LEFT OUTER JOIN
dbo.KF_Unit KF_Unit_4 ON dbo.KF_PackMaterial.weightunit = KF_Unit_4.UnitID LEFT OUTER JOIN
dbo.KF_Unit KF_Unit_3 ON dbo.KF_PackMaterial.thicknessunit = KF_Unit_3.UnitID LEFT OUTER JOIN
dbo.KF_Unit KF_Unit_2 ON dbo.KF_PackMaterial.lengthunit = KF_Unit_2.UnitID LEFT OUTER JOIN
dbo.KF_Unit KF_Unit_7 ON dbo.KF_PackMaterial.heightunit = KF_Unit_7.UnitID ON
dbo.KF_CartonImageList.CDImageId = dbo.KF_PackMaterial.CDImageId
WHERE (dbo.KF_PackMaterial.id=@PackingID AND dbo.KF_PackMaterial.packmaterialid=@PackingType))
END

return @DetailText
END


buts its throwing syntax error like
"[Microsoft][ODBC SQL Server Driver]Syntax error or access violation
"

... ne idea ????????

View Replies !

---

SqlDataSource, DataView, CType Function && Page_Load-Compilation ErrorBC30451: Name 'SqlDataSource3' Is Not Declared.
Hi all,
In my VWD 2005 Express, I created a website "AverageTCE" that had Default.aspx, Default.aspx.vb and App_Code (see the attached code) for configurating a direct SqlDataSource connection to the dbo.Table "LabData" of  my SQL Server 2005 Express "SQLEXPRESS" via SqlDataSource, DataView, CType Function and the Page_Load procedure. I executed the website "AverageTCE" and I got Compilation ErrorBC30451: Name 'SqlDataSource3' is not declared:

Server Error in '/AverageTCE' Application.


Compilation Error
Description: An error occurred during the compilation of a resource required to service this request. Please review the following specific error details and modify your source code appropriately. Compiler Error Message: BC30451: Name 'SqlDataSource3' is not declared.Source Error:






Line 8: <DataObjectMethod(DataObjectMethodType.Select)> _
Line 9: Public Shared Function SelectedConcentration() As ConcDB
Line 10: Dim dv As DataView = CType(SqlDataSource3.Select(DataSourceSelectArguments.Empty), DataView)
Line 11: dvConcDB.RowFilter = "Concentration = '" & ddlLabData.SelectedValue & "'"
Line 12:
Source File: C:Documents and Settingse1enxshcMy DocumentsVisual Studio 2005WebSitesAverageTCEApp_CodeConcDB.vb    Line: 10 //////////--Default.aspx--//////////////////////////
<%@ Page Language="VB" AutoEventWireup="false" CodeFile="Default.aspx.vb" Inherits="_Default" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
<title>SQL DataSource</title>
</head>
<body>
<form id="form1" runat="server">
 
<div>
Average TCE<br />
<asp:DropDownList ID="DropDownList1" runat="server" AutoPostBack="True" DataSourceID="SqlDataSource2"
DataTextField="SampleID" DataValueField="SampleID">
</asp:DropDownList><asp:SqlDataSource ID="SqlDataSource2" runat="server" ConnectionString="<%$ ConnectionStrings:ChemDatabaseConnectionString2 %>"
SelectCommand="SELECT [SampleID] FROM [LabData]"></asp:SqlDataSource>
<br />
<br />
<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataKeyNames="SampleID"
DataSourceID="SqlDataSource1">
<Columns>
<asp:BoundField DataField="SampleID" HeaderText="SampleID" ReadOnly="True" SortExpression="SampleID" />
<asp:BoundField DataField="SampleName" HeaderText="SampleName" SortExpression="SampleName" />
<asp:BoundField DataField="AnalyteName" HeaderText="AnalyteName" SortExpression="AnalyteName" />
<asp:BoundField DataField="Concentration" HeaderText="Concentration" SortExpression="Concentration" />
</Columns>
</asp:GridView>
<asp:SqlDataSource ID="ddlLabData" runat="server" ConnectionString="<%$ ConnectionStrings:ChemDatabaseConnectionString %>"
SelectCommand="SELECT * FROM [LabData] WHERE ([SampleID] = @SampleID)">
<SelectParameters>
<asp:ControlParameter ControlID="DropDownList1" DefaultValue="3" Name="SampleID"
PropertyName="SelectedValue" Type="Int32" />
</SelectParameters>
</asp:SqlDataSource>
<br />
<asp:SqlDataSource ID="SqlDataSource3" runat="server" ConnectionString="<%$ ConnectionStrings:ChemDatabaseConnectionString3 %>"
SelectCommand="SELECT * FROM [LabData]"></asp:SqlDataSource>
<br />
<br />
LabData-Analyte:&nbsp;
<asp:TextBox ID="TextBox1" runat="server"></asp:TextBox><br />
<br />
LabData-Conc:
<asp:TextBox ID="TextBox2" runat="server"></asp:TextBox><br />
<br />
Average values: &nbsp;
<asp:Label ID="Label1" runat="server" Text="lblAverageValue"></asp:Label><br />
<br />
<br />
<br />
 
</div>
</form>
</body>
</html>
///////////--Default.aspx.vb--////////////////////////////////
Partial Class _Default
Inherits System.Web.UI.Page
End Class
////////////////--App_Code/ConcDB.vb--//////////////////////
Imports Microsoft.VisualBasic
Imports System.ComponentModel
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.SqlTypes
<DataObject(True)> Public Class ConcDB
<DataObjectMethod(DataObjectMethodType.Select)> _
Public Shared Function SelectedConcentration() As ConcDB
Dim dv As DataView = CType(SqlDataSource3.Select(DataSourceSelectArguments.Empty), DataView)
dvConcDB.RowFilter = "Concentration = '" & ddlLabData.SelectedValue & "'"
Dim dvRow As DataRowView = dvConcDB(0)
Dim ConcDB As New ConcDB
ConcDB.SelectedConcentration = CDec(0)("Concentration")
Return ConcDB
End Function
Call AverageValue (Conc1)
Public Shared Function AverageValue(ByVal Conc1 As Decimal)
Dim AverageConc As Decimal
AverageConc = (Conc1 + 22.0) / 2
Return AverageConc
End Function
End Class
**************************************************************
I have 2 questions to ask:
1)  How can I fix this Compilation Error BC30451: Name 'SqlDataSource3' is not declared? 
2) I just read MSDN Visual Studio 2005 Technical Article "Data Access in ASP.NET 2.0" and I saw the following thing:
    Types of Data Sources:
      SqlDataSouirce:   The configuration of a SqlDataSoure is more complex then that of the AccessDataSource, and is intended
                                      for enterprise applications that require the features provided by a true database management system
                                       (DBMS).
    I am using the website application in VWD 2005 Express to do the task of extracting data values from the Tables of SQL Server 2005 Express via .NET Framwork, ASP.NET 2.0 and VB 2005 programming.  Can VWD 2005 Express be configured to SQL Server 2005 Express (SQLEXPESS) for the SqlDataSource connection and do the data-extraction task via DataView, CType Function and the Page-Load procedure?
Please help, respond and answer the above-mentiopned 2 questions.
Many Thanks,
Scott Chang 

View Replies !

---

Newbie Help: SQL Server Does Not Exist Error
I've seen this question asked when I searched, but I didn't see an answer that looked like it applied to me, so I'll ask again.....

Making the transition from VB 6 to VB.NET and decided to take a stab at database programming while I'm at it. I bought "Database Programming with Visual Basic.NET and ADO.NET" by Sams Publishing and ran into problems in the first chapter. I've posted some questions on the microsoft.public.vb.database newsgroup and got some help, but it's still not working.

I'll skip some of the boring stuff and dive right in with what I've found out so far:

From MSSQL$VSDOTNETLOGERRORLOG

2004-11-19 02:29:34.71 spid3 SQL global counter collection task is created.
2004-11-19 02:29:34.76 spid3 Warning: override, autoexec procedures skipped.
2004-11-19 02:29:58.87 spid51 Error: 15457, Severity: 0, State: 1
2004-11-19 02:29:58.87 spid51 Configuration option 'allow updates' changed from 0 to 1. Run the RECONFIGURE statement to install..
2004-11-19 02:29:59.01 spid51 Error: 15457, Severity: 0, State: 1
2004-11-19 02:29:59.01 spid51 Configuration option 'allow updates' changed from 1 to 0. Run the RECONFIGURE statement to install..
2004-11-19 02:29:59.46 spid3 SQL Server is terminating due to 'stop' request from Service Control Manager.

The last suggestion was to uninstall and reinstall. I uninstalled VS and SQL and reinstalled getting the same error message in the log, but the message box said that installation was complete.

On the Server Explorer of the IDE, it shows my computers name (programmer) under Servers. When I expand that and expand SQL Servers, I see PROGRAMMERVSDOTNET. When I try to expand that, I get the SQL Server Login window with Server textbox disabled saying PROGRAMMERVSDOTNET and the Database blank. Under the Login it has James (my sign in name) and asks for a password. When I did the MS-DOS CLI installation, I used "password" as the SAPWD. I tried using password, and tried using the password to log on as James, and get the same error message: [DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied.

Any help would be appreciated, as long as you remember that I am a COMPLETE newbie at this. I'm fairly computer literate, but I don't know the first thing about databases.....

Thanks again,

James

View Replies !

---

Newbie Error? Crosstable Query
I'm struggling with the problem which feels like it shouldn't be taking me this long! Any help would be gratefully received. Simply, there are two tables: Users: userid | username Links: sourceUserId | destUserId sourceUserId and destUserId are both in the users table. I'm trying to write a SP which will output the names of the linked users. eg: Bob | Alice Alice | Geoffrey Peter | Bob Any help gratefully received! Thanks in advance -- Chris

View Replies !

---

Newbie: Error-message Question
I got the error message: "The metadata of the following output columns does not match the metadata of the external columns with which the output columns are associated". What does that mean and how am I supposed to fix the problem?

 

TIA,

Barker

P.S. The SSIS UI seems extremely slow and sluggish (opening, creating connections).   Any performance tweaks known? (I have 9GB of RAM on my box and plenty of HD space so I don't think that is it. I also have sql 2005, sp1 installed.)

 

 

View Replies !

---

Newbie To SQL. Error Connecting To Server
hy.
i am a newbie to SQL
 
i downloaded and installed SQL SERVER 2005 EXPRESS.
i tried to convert an "ACCESS" database to sql and got the next error:
 
: "AN ERROR OCCURED
WHILE ESTABLIDHING A CONNECTION TO THE SERVER. WHEN CONNECTING TO SQL 2005,
THIS FALIURE MAY BE COUSED BY THE FACT THAT UNDER THE DEFAULT SETTINGS SQL
SERVER DOES NOT ALLOW REMOTE CONNECTIONS. PROVIDOR NAMED PIPES ERROR 40 -
COULD NOT OPEN A CONNECTION TO SQL SERVER".

any help apreaciated.
 

View Replies !

---

Newbie Question: Error 9002
I'm getting the following msg.

 

Server: Msg 9002, Level 17, State 2, Procedure spPFW_Get_Financial_Data, Line 145
[Microsoft][ODBC SQL Server Driver][SQL Server]The log file for database 'tempdb' is full. Back up the transaction log for the database to free up some log space.

 

When I use the tools/backup database it will not let me select backup transaction log or fails the bacup of the tempdb database with a msg saying : 'Backup and restore operations are not allowed on database tempdb'

 

What should I do? 

View Replies !

---

Newbie - Sa Password Error After ACT! Install - Need Pro's Help!!!
Hello,
I am admittedly new to this, especially sql server and am in need of a pro's assistance.. I installed Microsoft Small Business accounting, which in turn installed and configured a sql server. When I installed ACT! 2005 (v7.0) and tried to create a database, it states that it cannot find the master file.... so I tried to install sql server from Microsofts site - downloaded all the packages, unzipped them and ran them... the error I get is that the sql server needs to have an sa password and to use some switch to change it... for the life of me I am lost here. I can't use ACT! at this point and I have not ideas of how to remove, reinstall or repair the sql server.... Any help you can provide would be greatly appreicated!

View Replies !

---

I'm Sure This Is An Easy One...Error Trap To Skip Over A &"bad&" Object.
Hello, I have the following code to iterate through each view in a SQLServer and call the "sp_refreshview" command against it. It worksgreat until it finds a view that is damaged, or otherwise cannot berefreshed. Then the whole routine stops working.Can someone please help me re-write this code so that any views thatfail the "sp_refreshview" command get skipped. I'm sure it's just amatter of putting some basic error trapping into the loop, but I've hada few goes at it and failed.Many thanks.DECLARE @DatabaseObject varchar(255)DECLARE ObjectCursor CURSORFOR SELECT table_name FROM information_schema.tables WHERE table_type ='view'OPEN ObjectCursorFETCH NEXT FROM ObjectCursor INTO @DatabaseObjectWHILE @@FETCH_STATUS = 0BEGINEXEC sp_refreshview @DatabaseObjectPrint @DatabaseObject + ' was successfully refreshed.'FETCH NEXT FROM ObjectCursor INTO @DatabaseObjectENDCLOSE ObjectCursorDEALLOCATE ObjectCursorGO

View Replies !

---

C# Sql Easy Help
if i have the following code, how do i know if the transaction was actually successful, so basically where in this code can i write TRANSACTION SUCCESFUL?

try
{
//Open up the connection
conn.Open();
//Setup the Transaction
trans = conn.BeginTransaction();

//First query in transaction
pre_query = "Delete from Menu where spec_name="
+ "'"
+ spec_name_array[i].Text.ToString()
+ "'" ;

//Second query in transaction
query = "INSERT into Menu VALUES ('"
+ Calendar1.SelectedDate
+"','"
+ spec_name_array[i].Text.ToString()
+"','"
+ spec_desc_array[i].Text.ToString()
+"','"
+spec_price_array[i].Text.ToString()
+"',1)";
SqlCommand comm = new SqlCommand(pre_query,conn);
//Setup the command to handle transaction
comm.Transaction = trans;
//Execute first query
comm.ExecuteNonQuery();
//Add in second query
comm.CommandText = query;
//Execute second query
comm.ExecuteNonQuery();
trans.Commit();
}
catch(SqlException ex)
{
com_label.Text = "Submission Not complete, did you forget the date or something else?";
//Undo all queries
trans.Rollback();
}
finally
{
conn.Close();
}

View Replies !

---

I Know This Is Easy But...
I'm banging my head against the wall. It's probably pretty simple so here goes...

I have a form that supposed to add records to a table on the database. I'm pretty sure I have the dataAdapter and Dataset set right but when I add a record, it complains that the primary key can’t be null. I’ve tried different things, including removing those fields and variables from the stored Proc, changing the Identity Properties of the Table, changing the AutoIncrement settings on the Dataset for that field, but all give me different errors. Nothing actually makes it work. This database has many records in it and the Primary key is a decimal field that starts at 5, then next was 10, then 28, then it started getting consecutive after 110. (111, 112, 113, etc.) (I'm porting over an old database to a new web site, so I'm somewhat contrained by the database.)

I guess my question is for adding records in to a database that has a primary unique key constraint. Certainly this is a pretty common thing. Obviously I do not want users to enter that in themselves, I thought it should simply autocreate/autonumber. So what is the best procedure and gotcha’s I need to watch out for?

View Replies !

---

This Should Be Easy But It's Not.
I have written 2 custom connection mgr€™s.
One connects the data from an oracle source
One connects so I can put the data on a sql server.
These both work well and it makes it a lot easier across development, test, production.
 
But now I would like a good way to do this
 
I have 10 tables to copy from oracle.  The only difference is just the name of the table. 
 
static string[] g_tables =
        {
            "table_name_1",
            "table_name_2",
            "table_name_3",
     €¦
        };
 
foreach (string str_table in g_tables)
{
Oracle.DataAccess.Client.OracleCommand OracleCommand = new Oracle.DataAccess.Client.OracleCommand
       (
              "select * from " + str_table,
        OracleConnection
       );
Oracle.DataAccess.Client.OracleDataReader OracleDataReader =
OracleCommand.ExecuteReader();
 
// dump out to raw file destination
 
}
 
I understand there are some pieces left out but that is the basic idea.  It seems like I€™m going to have write custom pieces for each step.
 
It does not seem like there is any advantage to using SSIS.  Should I give up now and stick to writing windows services for stuff like this?
 
I could just write a specific piece for each table but the only difference is the table name.
 

View Replies !

---

Hopefully An Easy One
 

I need to determine the top 20 most frequent occurences of a value in a specific field using SQL.

View Replies !

---

Should Be Easy...Someone Has To Have Done This Already
This may not be the right place for this post...but my head is hurting and I can't think right now.

I have a SQL table of users. Each record has a required 'Birthdate' field. I'd like to order this by the days left till the give birthday, keeping in mind that the year may be 1980 or their birthday might have been 2 weeks ago.

Can someone cure my headache?

View Replies !

---

Easy ....If Else
 

I completely forgot how to do this. I want one of my groups in the matrix to take its week number (1-7) as Monday-Sunday.
 
 
so it would kind of be like this:
 

=IIF(Fields!DayOfWeek.Value=2,"Mon",

ELSE(Fields!DayOfWeek.Value=3,"Tues",

Else(Fields!DayOfWeek.Value=4,"Wed",

etc etc
 
Am i doing this right, or am i off??

View Replies !

---

Has To Be Easy!
 

I have a column in my report that i want to show percentage change from the previous year to this year. So i enter all kinds of different type of formats for the formula, and keep getting the wrong answers or error messeges. Here's what i have tried recently:
 

=SUM((Fields!LYTD_Amount.Value - Fields!YTD_Amount.Value) (Fields!YTD_Amount.Value*100))

Its pretty much Last year to date Minues Year to Date , Divided by Year to Date, Multiplied by 100.
 
any suggestions? Everytime i try to put an aggregate in the box, its always tricky.

View Replies !

---

What Is The Easy Way To Do This?.
My table structure...
enddt   datetime(8)
status  varchar(20)
 
when enddt time equal to server time.
 
I want to change status field to expired.
 
What is the easy way to do this?.
 
 
enddt                                 status
8/22/2005 7:00:00 PM         expired
9/22/2005 8:30:00 PM         expired
10/30/2005 7:30:00 PM       live
11/22/2005 9:30:00 PM       live
 

View Replies !

---

I Think This Is An Easy One...
If I have a 64 bit version of SQL 2005 installed, will "select @@version" indicate it is a 64 bit version?  If not, how do I determine such?  Thanks in advance.

View Replies !

---

Another Easy One: Views
 scenario I have 2 tables one is called "Site" which contains columns ID, Address1, Address2,City,StateID,ZipCode and the second table is called "State" which contains columns ID,Name,Abbreviation.  I am looking to make a view that contains all the sites with the state inner joined on stateid to make my all my other queries easier. That is so I can just select from the view without having to do the inner join on the state.  My question is when a new record is added to table Site do I have "recreate"/update the view?

View Replies !

---

An Easy One: The Need For Relationships?
 Ok - I am still a bit weak on SQL.  My understanding of FK relationships in a database are to reinforce the data integrity - Correct?  For example, does it make sure that the id given for SiteID does indeed exists.  Can it link tables like JOIN in select statements? If not, is there any gains by creating them, such as performance?

View Replies !

---

Easy Question
What is the equivalent of Double for SQL Server? 

View Replies !

---

SQL Query (ought To Be Easy)
hi,
I have this simple sql query - it should be pretty obvious what I'm trying to achieve but this syntax isn't accepted on SQL 2005, any suggestions?
 SELECT Name FROM Users WHERE UsersID IN (EXEC dbo.ReturnDataByModule 'Groups',1200)

View Replies !

---

Easy Question??
New to .NET and worse I only briefly toyed with 1.1 and am now using 2.0.I created a sqldatasource that uses a very simple stored procedure that inserts a record into a sql database using a parameter field from a text box. The stored proc and sqldatasource work fine and was real easy to set up.Here's the dumb question.I have a text box and a button on a form. I'd like to click the button and trigger the sqldatasource stored proc.I'm assuming I need some code in the button_click Sub???? or is there another way to do it?What does that code need to look like?Thanks in advance for any help. 

View Replies !

---

Seems Like An Easy Task. :(
OK, I have two cols of data:
COL1 COL2
test ing
tams ert
test pan
ted ted
hom jis
ted sam
tams dom
test ut

I need to simply pull one row of data from the given rows. Whats in COL2 is not relevant. So an example would be:
I want to retrieve a row(any row, but only one) where COL1 = 'test'
An ecceptable result woould be:
COL1 COL2
test ing

or

COL1 COL2
test pan

or

COL1 COL2
test ut

I would appreciate any help. This is driving me nuts. Seems like it would be easy.
TIA,
STue

View Replies !

---

Easy SQL Question
I have a simple SQL table. The data looks like this:

products_ID----category----product_local

1----CORE----0

2----BASE----0

3----BRANCH----1

4----LEAF----3

I need to create a SQL statement that produces all category items where that ROW's products_ID is not found in ANY product_local in the table. So in the table above only the category BASE and LEAF would be printed because their products_ID are not found in any product_local in the table.

TIA as I am completely stuck!

View Replies !

---

DataSet - Should Be Easy But I Don't Know How
I am working on a WebMatrix ASP project. I have a query that returns a System.Data.DataSet but I don't know how to assign the DataSet to a variable so that I can run some validation tests on it.

This is what I have so far...What I want to do is assign the dataset to a variable and to see if it is NULL or Not. I don't how??? Any help would be awesome.


Sub Button1_Click(sender As Object, e As EventArgs)
???? = MyQueryMethod(txtPhone.Text)


Thanks,
Matt

View Replies !

---