Error In Using DTUTIL /Exists
Jan 29, 2007
hi group,
i have to check whether SSIS package is present in MSDB or not.
I am using DTUTIL for this perpose . Command goes like this.
dtutil /SQL Package_for_testing /Exists
Error(0xC0014014) while checking for the existence of package "Package_for_testing" on SQL server
this is giving the error "The specified package does not exist.
Description: The ExistsOnSQLServer method has encountered OLE DB error code 0x80
004005 (Login timeout expired). The SQL statement issued has failed.
Source:"
But the package is there in MSDB when i checked manually.
I want to know what this error signify? and how i can solve it.
With advance thanks
Srinivasa Mahendrakar
View 2 Replies
ADVERTISEMENT
Oct 8, 2007
i have restored site using stsadm, site get successfully restored.
all user of the site able to access the site but the unable to access from same machine on which it is restored.
i got the error
The file exists. (Exception from HRESULT: 0x80070050)
View 1 Replies
View Related
Oct 24, 2006
I am using a script to copy a number of packages (50+) from my desktop computer (file system) to a remote server (MSDB). The script works fine, but I can never get through the entire script without it hanging up. Some times only 3-4 packages get copied, sometimes a lot more. If I close the windows cmd window and restart at the point it hung, it continues, but usually hangs up again. If you repeat this 2 or 3 times, it eventually gets all of the packages copied. It does not always hang at the same point. I have tried doing the copies with "quiet" mode - overwriting the existing packages and I have tried first deleting the packages, then copying - doesn't make a difference. I have also tried moving the packages to a couple of different installations of SQL Server 2005 using the script - the remote one and the local one on my desktop - same thing on both.
If I have the script echo a message just after each copy, the message just after the last successful copy never appears (and I know the copy is successful, because I deleted all the packages first and have checked that the package is there after the script hangs).
The copy command I am using look like this:
dtutil /FILE %1 /Decrypt %3 /Encrypt SQL;%~n1;2;%3 /DestServer %4 /DestUser admincis /DestPassword %2 /quiet
ECHO Done deploying package %~n1
%1 is the file spec of the ".dtsx" file to be copied
%2 is the password of the user account on the server
%3 is the encryption password (same one used on both ends)
%4 is the name of the server being copied to
Any ideas???
Jane Gaby
View 1 Replies
View Related
Jan 27, 2006
Hi,
I have a 64 bit Itanium SQL Cluster that I'm trying to set up. The database engine seems fine, but I'm trying to install some SSIS packages using dtUtil and I keep getting a connection error. The error is as follows
"Login timeout expired
An error has occured 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 seetings SQL Server does not allow remote connections.
Named Pipes Provider: Could not open a connection to SQL Server [2}. (Microsoft SQL Native Client)"
Also, in Management studio, I can make a connection to my SSIS Server, but when I go down to the MSDB folder to look at packages I get the same connection error.
I think I was able to deploy a package using the Deployment Manifest created by VS, but I'm not sure because I can't see the package in Mgt Studio.
It almost seems like it may be related to the cluster. I'm connected in to one of the Servers using Terminal Services. But I am trying to connect to the Cluster name in Mgt Studio.
Any thoughts/ideas/Solutions greatly appreciated.
Thanks.
Andy
View 5 Replies
View Related
May 14, 2007
Hi,
Im loading the packages with a dutil-batch from the file-system into the sqlserver.
All the packages have xml-configuration-file.
for %%f in (*.dtsx) do dtutil /FILE %%f /COPY SQL;%%~nf /DestServer server /QUIET
When I try to run the packages in the sqlserver environment there is no configuration file found.
Is there a possibility to transfer also the link to the xml-configuration-file xml-configuration-file with dtutil?
Thanks Gerd
View 5 Replies
View Related
Jun 5, 2014
I am trying to deploy a package to a project in my SSIS catalog and am using the following command:
dtutil /FILE C:ChangesReceivedEDI.dtsx /DestS MARSQL12 /COPY DTS;TradingPartnersTradingPartnerReceivedEDI
Where the TradingPartners is the folder name, the TradingPartner is the project name and the ReceivedEDI is the package name.
I am getting an error of:
Error (0x800706BA) while checking for the existence of package "TradingPartners
TradingPartnerReceivedEDI" in the SSIS package store.
Error (0x800706BA) saving package "TradingPartnersTradingPartnerReceivedEDI" to the SSIS package store.
View 5 Replies
View Related
Nov 1, 2007
I'm having trouble running a statement like this:
dtutil /DTS MSDB/Budget/BUD_Production - dtsChart of Accounts Budget - Final /En FILE;BUD_Production - dtsChart of Accounts Budget - Final.dtsx;4
I've tried:
dtutil /DTS "MSDB/Budget/BUD_Production - dtsChart of Accounts Budget - Final" /En FILE;"BUD_Production - dtsChart of Accounts Budget - Final.dtsx";4
dtutil /DTS "MSDB/Budget/BUD_Production - dtsChart of Accounts Budget - Final" /En "FILE;BUD_Production - dtsChart of Accounts Budget - Final.dtsx;4"
Any suggestions? I'm no DOS pro, but I can normally get things going.
Thanks
Sam
View 4 Replies
View Related
Aug 29, 2006
how can i create a folder in the integration services-stored packages-MSDB folder using dtutil command line utility.
I could create a folder within a folder inside MSDB using
dtutil /FC SQ; est;temp
but not in the MSDB folder.
Thanks &Regards,
Vivek S
View 1 Replies
View Related
Jan 18, 2006
Hello,
I'm wondering how to use the dtUtil command to deploy an ssis package but also include any of the miscellaneous files, such as some dtsconfig files. I'd like to be able to use the dtUtil command to specify where to put any "dependencies" that I have. If I create a deployment utility from BI Studio, I have the option on the third screen to choose the folder for dependencies, how come I don't see any parameters for that on the dtUtil command? Is it possible, or do I HAVE to deploy using the deployment utility created for me by BI Studio?
Thanks in advance,
Andy
View 1 Replies
View Related
Nov 22, 2006
1) We are migrating data from SQL Server 2000 to 2005 DB.
2) We are developing SSIS packages in client, build, deploy and install and copying to SQL Server 2005 server to execute for migration.
3) For this we encrypt all packages with password, then decrypt all while executing in a batch file using dtexec. This works fine.
4) What does not work is executing a master execute task in which are individual execute tasks. This is so since the other packages are still encrypted.
5) We decrypted them separately first using dtutil using Decrypt and Copy/Move option but while executing error is thrown that these other packages are still protected by password.
6) Do we need to decrypt, then encrypt using default package protection level and then decrypt again.
7) How exactly to just execute a master package when packages are developed on client.
View 1 Replies
View Related
Aug 16, 2013
SQL 2012 - Convert BIDS project and DTUTIL cannot load the package I just converted a really simple 2008 BIDS project to 2012 and 2012 dtutil will not load it. Tried 32 bit and 64 bit dtutil.
Get the following error message.
'count not load package "c: empSSISPackage.dtsx" because of error 0x80131534. Description: the package failed to load due to error 0x80131534 "<null>". This occurs when CPackage::LoadfromXML fails.
I can Import the package in SQL Manager, and I can deploy it using the manifest and the deployment wizard. But DTUTIL chokes on it. It is the dtsx right from 2012 Bids build.
dtutil /DestS ficertx2x /FILE C:empRelease2012CreateSSISPackage.dtsx /COPY SQL;/MYTestPackage /QUIET
View 9 Replies
View Related
Feb 24, 2007
Hello, can anyone see a problem with this T-SQL? 1 set ANSI_NULLS ON
2 set QUOTED_IDENTIFIER ON
3 GO
4 ALTER PROCEDURE [dbo].[Logon_P]
5 @User_ID VARCHAR(50),
6 @User_Password VARCHAR(50)
7 AS
8
9 IF EXISTS(SELECT 1
10 FROM [User]
11 WHERE [User_Name] = @User_ID)
12 BEGIN
13 RETURN 1
14 IF ((SELECT User_Password FROM dbo.[User] WHERE [User_Name]) = @User_ID) = @User_Password
15 BEGIN
16 RETURN 2
17 END
18 END
19 ELSE
20 RETURN 0
21 Its returning the following error:Msg 4145, Level 15, State 1, Procedure Logon_P, Line 11An expression of non-boolean type specified in a context where a condition is expected, near ')'.
View 3 Replies
View Related
Feb 19, 2007
My goal is to monitor all failed login attempts to server. I have:
DECLARE @traceidnum INT, @on BIT, @file_path NVARCHAR(50), @maxsize bigint;
SET @on = 1; SET @maxsize =5; SET @file_path = 'c: race'; --Create trace EXEC sp_trace_create @traceid = @traceidnum OUTPUT, @options = 2, @tracefile = @file_path, @maxfilesize = @maxsize, @stoptime=null, @filecount =0; --trace stop EXEC sp_trace_setstatus @traceidnum, 0 --Set events EXEC sp_trace_setevent @traceidnum, 14, 1, @on --trace start EXEC sp_trace_setstatus @traceidnum, 1
this works fine, but the problem is when sql server restarts I loose trace but trace-file remains. When I want to create new trace with the same parameters I receive Error = 0x80070050(File Exists) .
Is there any way to reuse trace-file? Or to change trace's file path to indicate file already created?RegardsKonRi
View 4 Replies
View Related
Apr 6, 2007
I am receiving errors from a job that no longer exists. The databases created for this no longer exist also.
DATE/TIME: 4/6/2007 3:32:00 PM
DESCRIPTION: The log shipping secondary database LOG1.log_ship_test has restore threshold of 45 minutes and is out of sync. No restore was performed for 6004 minutes. Restored latency is 0 minutes. Check agent log and logshipping monitor information.
COMMENT: (None)
JOB RUN: (None)
I have gone into agent jobs and deleted those associated with it. I have also gone into the msdb database and deleted any lines associated with it in the log_shipping tables. But the errors keep coming. What do I need to do to clean this up?
Thanks
View 2 Replies
View Related
Mar 10, 2007
CREATE TABLE IF NOT EXISTS TempA (id int);
CREATE TABLE IF NOT EXISTS TempB (id int);
For some reason the above statements are giving me syntax errors?
I want to create table only if it does not already exist.
Also, can the same "if not exists" clause be applied to "DROP TABLE" and "TRUNCATE" ?
thx in advance .
View 3 Replies
View Related
Mar 6, 2008
I am getting this error, when I try to access a directory in the remote path. Though the directory exists, I get this error.
550 CWD CMD FAILED : EDC5129I NO SUCH FILE OR DIRECTORY.
When I try to access through the remote login, I am able to see the directory as well as the files there. How to overcome this error. Is there any workaround for this.
Thanks.
View 1 Replies
View Related
Aug 7, 2007
I am setting my error tables.What i want to do is.If i find any data within my error tables i want an email to be sent to me.
How can i achieve this??
Please let me know
View 7 Replies
View Related
Feb 9, 2000
SQL Server 7
i did a restore of a database, then tried to add the User login
to it, but when i select database access, i get the followinf error :-
"Error 15023, user or role already exists !
the user did exist on the Database, but when i select Database,Users or
Database,Roles the User doesn't exist !! so i can't drop it !
any ideas ??
View 2 Replies
View Related
Nov 7, 2007
Hi,
When i try to save my stored procedure.. i am getting the above error and this is my sproc
1 INSERT INTO Statement..ClientSources
2 (
3 ClientId,
4 ClientSourceId,
5 SourceName
6 )
7 Select Distinct
8 @ClientId,
9 SOURCE_NUM,
10 (Select CASE s.SOURCE_NUMWhen 1 Then SRC1NAME
11 WHEN 2 Then SRC2NAME
12 WHEN 3 THEN SRC3NAME
13 WHEN 4 THEN SRC4NAME
14 WHEN 5 THEN SRC5NAME
15 WHEN 6 THEN SRC6NAME
16 WHEN 7 THEN SRC7NAME
17 WHEN 8 THEN SRC8NAME
18 WHEN 9 THEN SRC9NAME
19 WHEN 10 THEN SRC10NAME
20 WHEN 11 THEN SRC11NAME
21 WHEN 12 THEN SRC12NAME
22 WHEN 13 THEN SRC13NAME
23 WHEN 14 THEN SRC14NAME
24 WHEN 15 THEN SRC15NAME
25 END
26 FROM
27 PlanDBF p
28 Where
29 p.PLAN_NUM = s.PLAN_NUM
30 ) as SourceName
31 FROM
32 SourceDBF s
33 Where
34 SOURCE_NUM NOT IN (
35 SELECT DISTINCT
36 ClientSourceId
37 --SourceName
38 FROM
39 Statement..ClientSources
40 Where
41 ClientId = @ClientId
42 )
I am getting the error in Line number 35 .. the inserts works fine... and if use * instead of the field name or use more than 1 field name i get this error
Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.
Any help will be appreciated.
Regards
Karen
View 4 Replies
View Related
Sep 20, 2001
I used a backup copy of our production DB (residing in our prod machine) to do a database RESTORE to our test DB (residing in our test machine). This step was successful.
However when I tried to access the test DB via Peoplesoft application, I am unable to logon. Only then did I notice that all the users, with the exception of "sa", were gone.
When I attempted to add a user via Enterprise Manager's Action - Add Database User, I get the message, "Error 15023: User or role '%' already exists in the current database.
What's the best way to fix this without resorting to copying the source server's master database (If i do this, I risk clobbering some other DB's that are present in the target server but not in source server)?
Any help you can provide will be greatly appreciated!!!
View 2 Replies
View Related
Jul 2, 2007
Hi
While trying to build SSIS Package Project, the following error is thrown. I have added configuration file recently to fetch the dynamic values such as connection string, output path etc.
I tried manually cleaned up the debug folder and tried build but still the error occurs.
Can any one help in suppressing this error?
Error 1 System.ApplicationException: Could not copy file "D:ExportExport.dtsConfig" to the deployment utility output directory "D:ExportinDebug". ---> System.IO.IOException: The file 'D:ExportinDebugExport.dtsConfig' already exists. at System.IO.__Error.WinIOError(Int32 errorCode, String maybeFullPath) at System.IO.File.InternalCopy(String sourceFileName, String destFileName, Boolean overwrite) at Microsoft.DataTransformationServices.Project.DataTransformationsProjectBuilder.CopyFiles(ICollection fileNames, String outputPath) --- End of inner exception stack trace --- at Microsoft.DataTransformationServices.Project.DataTransformationsProjectBuilder.CopyFiles(ICollection fileNames, String outputPath) at Microsoft.DataTransformationServices.Project.DataTransformationsProjectBuilder.CreateDeploymentUtility(IOutputWindow outputWindow) 0 0
Thanks in advance,
Prabha
View 1 Replies
View Related
Mar 9, 2006
Can't get over this error, and net searches reveal other postings similiar, but no answers.
SSIS database transfer task (with overwrite) from SQL 2k source to SQL 2k5 destination fails with:
Error: The Execute method on the task returned error code 0x80131500 (ERROR : errorCode=-1073548784 description=Executing the query "CREATE ROLE [RFRSH_USER] " failed with the following error: "User, group, or role 'RFRSH_USER' already exists in the current database.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly. helpFile= helpContext=0 idofInterfaceWithError={8BDFE893-E9D8-4D23-9739-DA807BCDC2AC}). The Execute method must succeed, and indicate the result using an "out" parameter.
The error seems the same regardless if the destination DB exists or not!
Anyone have a solution?
View 12 Replies
View Related
Sep 10, 2015
Due to a SQL 2014 cluster installation failure related to security while setting up the primary node, I had to remove the node and any related installation programs from the node and redo the installation again. However I am unable to use the machine name as the SQL network name during the instance configuration, I get the following:
Microsoft.SqlServer.Chainer.Infrastructure.InputSettingValidationException: The SQL Server failover cluster instance name "MachineName" already exists as a server on the network. Specify a different failover cluster instance name.I am still getting the same name since the node name "MachineName" is listed under the cluster name. I have used machine name as SQL network name without an issue. I do not have any existing SQL machine name in network using same machinename which I want to use for this installation.
View 9 Replies
View Related
Oct 26, 2007
I am trying to use a linked server and it works as long as I do not specify the sp_addlinkedserver @provstr parameter. If I specify that parameter I always get a 7416 "Access to the remote server is denied because no login-mapping exists" error. I have tried adding the logins various ways but it's very specific to the @provstr parameter, and it doesn't even matter what I put in that parameter. As soon as I put something in there whether it is valid or invalid, I get the error.
Anyone else seen this? There is an amazing lack of any discussion about the error when I search for it.
If I do this it works fine,
EXEC sp_addlinkedserver @server= 'linkedname', @srvproduct='', @provider='SQLNCLI', @datasrc='servername', @catalog='mydatabase'
EXEC sp_addlinkedsrvlogin 'linkedname', 'true', 'AppUser'
But as soon as I add the @provstr parameter, then I get the error if I try to use linkedserver,
EXEC sp_addlinkedserver @server= 'linkedname', @srvproduct='', @provider='SQLNCLI', @datasrc='servername', @catalog='mydatabase', @provstr='Failover Partner=otherservername'
EXEC sp_addlinkedsrvlogin @rmtsrvname='linkedname', @useself='true', @locallogin='AppUser'
It doesn't even make any difference what I put in the @provstr parameter - the sp_addlinkedserver statement always executes without an error, but running a query that uses the linked server generates the error.
View 12 Replies
View Related
Apr 19, 2006
I wanted to create a package to copy the objects from one database to another and replace those object if they already exist. Therefore, before the package executes you do not know whether all the objects exist on the target server or only some of them.
Using the 'Transfer SQL Server Objects Task' I have found that I cannot get this to execute cleanly by itself. If I set the 'DropObjectsFirst' to false then an error is thrown if the object exists and if I set it to true then an error is thrown if it does not exist.
In order to get round this I have had to create an 'Execute SQL Task' to list all the objects and then go through them dropping them on the target server in a for each loop before executing the 'Transfer SQL Server Objects Task' with 'Transfer SQL Server Objects Task' set to false.
However, is there a better way of achieving this or am I missing something in the 'Transfer SQL Server Objects Task'?
View 11 Replies
View Related
Feb 24, 2007
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 1 Replies
View Related
Sep 20, 2006
Hi,
This is on Sybase but I'm guessing that the same situation would happen on SQL Server. (Please confirm if you know).
I'm looking at these new databases and I'm seeing code similar to this all over the place:
if not exists (select 1 from dbo.t1 where f1 = @p1)
begin
select @errno = @errno | 1
end
There's a unique clustered in dex on t1.f1.
The execution plan shows this for this statement:
FROM TABLE
dbo.t1
EXISTS TABLE : nested iteration.
Table Scan.
Forward scan.
Positioning at start of table.
It's not using my index!!!!!
It seems to be the case with EXISTS statements. Can anybody confirm?
I also hinted to use the index but it still didn't use it.
If the existence check really doesn't use the index, what's a good code alternative to this check?
I did this and it's working great but I wonder if there's a better alternative. I don't really like doing the SET ROWCOUNT 1 and then SET ROWCOUNT 0 thing. SELECT TOP 1 won't work on Sybase, :-(.
SET ROWCOUNT 1
SELECT @cnt = (SELECT 1 FROM dbo.t1 (index ix01)
WHERE f1 = @p1
)
SET ROWCOUNT 0
Appreciate your help.
View 3 Replies
View Related
Jul 7, 2006
Hello,
I am trying to create a table if one with the same name does not exists. My code is:
Dim connectionString As String = "Data Source=.SQLEXPRESS;AttachDbFilename=|DataDirectory|PensionDistrict4.mdf;Integrated Security=True;User Instance=True"
Dim sqlConnection As SqlConnection = New SqlConnection(connectionString)
Dim newTable As String = "CREATE TABLE [" + titleString + "Comments" + "] (ID int NOT NULL PRIMARY KEY IDENTITY, Title varchar(100) NOT NULL, Name varchar(100) NOT NULL, Comment varchar(MAX) NOT NULL, Date datetime NOT NULL)"
sqlConnection.Open()
Dim sqlExists As String = "IF EXISTS (SELECT * FROM PensionDistrict4 WHERE name = '" + titleString + "Comments" + "')"
Dim sqlCommand As New SqlCommand(newTable, sqlConnection)
If sqlExists = True Then
sqlCommand.Cancel()
Else
sqlCommand.ExecuteNonQuery()
sqlConnection.Close()
End If
I keep getting a "Input String was incorrect format" for sqlExists? I am new to Transact-SQL statements, any help would be appreciated.
Thanks Matt
View 1 Replies
View Related
May 27, 2007
ok i have 2 tables---one table is name CourseInformation with a field named Instructor and the data in there looks like this 'John Doe'.
My other table Instructors has 3 fields InstructorName, LastName, FirstName.
I am grabbing the Instructor field from CourseInformation and breaking up the names and inserting them into my instructors table as follows..
Insert into Instructors(InstructorName, LastName, FirstName)
(Select Distinct
ltrim(SUBSTRING(Instructor,CHARINDEX(' ',Instructor)+1,len(Instructor)))+', '+
SUBSTRING(Instructor,1,CHARINDEX(' ',Instructor)-1),
ltrim(SUBSTRING(Instructor,CHARINDEX(' ',Instructor)+1,len(Instructor))) as LName,
SUBSTRING(Instructor,1,CHARINDEX(' ',Instructor)-1) as FNamefrom CourseInformation
where NOT EXISTS(Select * from instructors where LastName = LName and FirstName = FName)
AND Instructor is not null)
Only problem is, I cant get the where not exists clause to work right(of course that wont work what i have cuz the LName and FName columns dont exist, i just did that for demo purposes). I dont want duplicate instructors in there..how can i accomplish this..is there a better way to rewrite my query? Any help is appreciated.
View 3 Replies
View Related
Feb 7, 2008
HI All,Which is best among the two 1) NOT IN or 2) NOT EXISTS .If the query is Select col1 from tab1 where col2 NOT IN (Select col 3 from tab2 where cl3=0) OR Select col1 from tab1 where col2 NOT EXISTS (Select col 3 from tab2 where cl3=0)
View 2 Replies
View Related
Oct 12, 2001
Hi,
I have a question on the if not exists. Can you do something like this? If request not exists in table one then insert values into table two?
Thank you in advance,
Anne
View 3 Replies
View Related
Jul 15, 2004
Hi,
Is there any way to check whether a column is there in the table, if it is there i need to drop it through script.
i'm looking for the script, something like this..
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_Tbl_Product_Tbl_Products]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[Tbl_Product] DROP CONSTRAINT FK_Tbl_Product_Tbl_Products
GO
In the same way i need to check for a column and drop it through script.
Any help would be greatly appreciated.
Thanks in advance.
View 2 Replies
View Related
Jun 15, 2007
hi guys! is there anyway to retrieve the row from the result of EXISTS function aside from using the code below?
if EXISTS (select * from rcps_useraccount where User_login = 'daimous')
BEGIN
select * from rcps_useraccount where User_login = 'daimous'
END
View 3 Replies
View Related
