Executing SQL Query With Conditions

Aug 22, 2007

I have a Columns like

RegionAmount

Asia5600
Asia6500
Asia1000
US8900
US4400
US6700
Europe4500
Europe2400
Europe1600
Africa6200
Africa6400
Africa6900


i want to execute a query with conditions...like

if Region==Asia then compute percent of Amount
if Region==US then compute percent of Amount
if Region==Europe then compute percent of Amount
if Region==Africa then compute percent of Amount

the result set to be displayed something like

Column---Amount---PercentAmount

thanks a lot!


pavan

View 5 Replies


ADVERTISEMENT

Stored Procedure Executing Durations Are Different Between Executing From Application(web) And SQl Server Management Studio - Query Window

Jan 23, 2008

Hi,I have a web application using Stored Procedure (SP). I see that there's a SP taking long time to execute. I try to capture it by Profiler Tool, and find out that with the same SP on the same db with the same parameter. The duration of executing by my web app is far bigger than the duration of executing on SQl server management studio - query windowPlease see the image attached http://kyxao.net/127/ExecutionProblem.png Any ideas for this issue?Thanks a lot Jalijack 

View 2 Replies View Related

JOIN Efficiency Using Multiple ON Conditions Versus WHERE Conditions

Jan 10, 2008

My question is fairly simple. When I join between two tables, I always use the ON syntax. For example:


SELECT

*
FROM

Users

JOIN UserRoles

ON (Users.UserRoleId = UserRoles.UserRoleId)


No problems there. However, if I then decide to further filter the selection based on some trait of the UserRole, I have two options: I can add the condition as a WHERE statement, or I can add the condition within the ON block.

--Version 1:

SELECT

*
FROM

Users

JOIN UserRoles

ON (Users.UserRoleId = UserRoles.UserRoleId)
WHERE

UserRoles.Active = 'TRUE'


-- Version 2

SELECT

*
FROM

Users

JOIN UserRoles

ON (Users.UserRoleId = UserRoles.UserRoleId

AND UserRoles.Active = 'TRUE')


So, the question is, which is faster/better, if either? The Query Analyzer shows the two queries have the exact same execution plan, which makes sense, since they're both joining the same tables. However, I'm wondering if adding the condition in the ON statement results in fewer rows the JOIN statement initially needs to join up, thus reducing the overall initial size of the results table before the WHERE conditions are applied.

So is there a difference, performance wise? I imagine that if Users had a thousand records, and UserRoles had 10 records, then the JOIN would create a cartesian product of the two tables, resulting in 10,000 records in the table before the WHERE conditions are applied. However, if only three of the UserRoles is set to Active, would that mean that the resulting table, before applying WHERE conditions, would only contain 3000 records?

Thanks for whatever information you can provide.

View 7 Replies View Related

Order Of Conditions In A Query

Nov 6, 2006

I have a query with many (approximately, 30) conditions, such as:

select ....... from table1 join table2 on ( (table1.field1 = table2.field1 OR table1.filed1 IS NULL) AND (table1.field2 = table2.field2 OR table1.filed2 IS NULL) )

My question is:

In C++ or C#, when I write a condition like this, say, in an IF or WHILE, I know that I would be better off specifying the IS NULL (well, == null, to be precise) first, and use | instead of ||. In that case, the first condition (equality to null) is checked first, it's fast, and if it's not satisfied, the control flow goes to the next statement.

The question is, is there the same rule in T-SQL?

I mean, if I put the "... IS NULL" first, and then "OR ... = ...", will the query run faster than if I write it the other way around (that is, "... = ... OR ... IS NULL")?

This is very important to me, because most of those fields are VARCHAR, and due to some business rules, I can't change them to numerics etc, which would be compared much faster than text. So, even if I use full text search, I still need to find a way to optimize the query for performance...

By the way, I know that I can put those conditions in the WHERE clause, but as far as I know it won't make much of a difference for performance. So, my question is primarily about the order of conditions, in which SQL Server constructs its query plan.

[Edited:] In other words, what runs faster: comparing varchar to null or comparing varchars? And does it make a difference if I switch their places in my sql script?

We are using SQL Server 2000 SP4, Standard Edition. [Dev edition on the dev machine.]

Could someone kindly advise me on this, please?

Thank you ever so much.

View 4 Replies View Related

Multiple Conditions In A Query

Feb 2, 2008



Hi,


I've a table with a field named 'TypeOfProd' that has an ID for the various types of products: ex.:
1 - product A
2 - product B
3- product C
4 - product D
....
10 - product J
and so on

I need to create a stored procedure that querys only the product types selected by the user.

The user can select 1, 3, 5, 10 or 1, 3 or 3 or 0 for all or some other combination.

For the first user selection a have something like this
SELECT Prod FROM tableProd WHERE TypeOfProd = 1 OR TypeOfProd = 3 or TypeOfProd = 5 OR TypeOfProd = 10
For the second,
SELECT Prod FROM tableProd WHERE TypeOfProd = 1 OR TypeOfProd = 3

Is it possible to have a stored procedures that runs a query with this random scenario?

please help

Thanks

JPP

View 8 Replies View Related

Breaking Query On Basis Of Conditions

Mar 20, 2008

 On a webpage, there are filters to choose from. Like date, amount, SSN (multiple filters can be choosen).I have a single query so far.   SqlCommand cmd = new SqlCommand("SELECT [column1], [column2], [column3],  [column4], [column5] FROM [table] WHERE [column4] = 'condition4'  AND [column5] = @total_bill AND [last_change] >= @txtStartDate AND [last_change] <= @txtEndDate  ", Conn) ;          cmd.Parameters.Add(new SqlParameter("@total_bill", total_bill1.Text));         cmd.Parameters.Add(new SqlParameter("@txtStartDate", txtStartDate.Text));           cmd.Parameters.Add(new SqlParameter("@txtEndDate", txtEndDate.Text));  I want to break the query so that it executes on the basis of different sets of conditions (filters). If I dont select date filter, then the above query will not execute properly.Please help. 

View 2 Replies View Related

Transact SQL :: Query For Multiple Optional Conditions?

Jun 1, 2015

I have following query. Now I want to change this in such a way that if Condition 1 satisfies then it should not go to next condition ,if not then should go to next. Similarly the same format till condition 4.

select * from table where
IND = 'Y'
and
(
(STATE = @STATE and LOB =@LOB and COMPANY=@COMPANY)--Condition 1
OR
(STATE = @STATE and LOB =@LOB)--Condition 2
OR
(STATE = @STATE and COMPANY=@COMPANY)--Condition 3
OR
(STATE = @STATE)--Condition 4
)

View 4 Replies View Related

T-SQL (SS2K8) :: Select Query With Multiple Where Conditions And OR Operator

Feb 6, 2015

I have the got below T SQL query to fetch the matching record

DECLARE @MappingTable TABLE
(
Productname nvarchar(10),
identification_key1 int,
identification_key2 int,
identification_key3 int

[Code] .....

-- result - 'Orange'

This is an exact matching record and straight forward

Is it possible to identify the record using T SQL query based on the following scenarios

1) return the record - If all the three where conditions match
2) if record not found check and return the record where 2 columns values in the where condition match

-- Expected Result for below query: 'Orange', because 2 of the columns in where condition have matching values

SELECT Productname
FROM @MappingTable where identification_key1=1 or identification_key2 =2 or identification_key3 =1

-- result - 'Orange'

View 4 Replies View Related

SQL Server 2014 :: Query To Display Unique Comments Based On Certain Conditions

Jul 1, 2015

TABLE DEFINITION

Column NameComments
CustomerIDINT
SequenceNoINT
CommentsVARCHAR(MAX)

CUSTOMER TABLE

CustomerIDSequenceNoComments
11ABC D
12CDE
13ABC
14ABC D
15CDE
16abc
27ABC DEF
28
29ABC DEF
210DEF
211XYZ 123
212ABC
313PQ RST

OUTPUT

CustomerIDSequenceNoComments
13ABC
14ABC D
15CDE
16abc
28
29ABC DEF
210DEF
211XYZ 123
212ABC
313PQ RST

Records should be filtered by

1.Display only Unique Comments from Customer Table for all the customers,
2.If Comments are same then display the row which has maximum SequenceNo

View 2 Replies View Related

Executing More Than One Query

Jun 16, 2007

Hi,
How to execute more than one query consecutively in SqlDataSource like the picture shown below.
 

View 3 Replies View Related

Query Not Executing.

Mar 13, 2008



hello,

Here is the query.

select Count(*)

from

(select Datepart(yyyy,Activitydate) [Year], Month(ActivityDate) [Month] ,DataCenter, Farm

from Daily

where DATEPART(yyyy, ActivityDate) = DATEPART(yyyy, getdate()) and Month(ActivityDate) = 3

Group By Datepart(yy,Activitydate), Month(ActivityDate), DataCenter, Farm

) A

INNER JOIN

(SELECT *

from Monthly

where Year = DATEPART(yyyy, getdate())
) B

ON

A.Year = B.Year and

A.Month = B.Month and

A.DataCenter = B.DataCenter and

A.Farm = B.Farm


Above query executes fine giving output as 3519; that too in 1sec.
However when i try to apply criteria on Monthly table of Month=3, query just keeps executing for hours..


Following query does not work.


select Count(*)

from

(select Datepart(yyyy,Activitydate) [Year], Month(ActivityDate) [Month] ,DataCenter, Farm

from Daily

where DATEPART(yyyy, ActivityDate) = DATEPART(yyyy, getdate()) and Month(ActivityDate) = 3

Group By Datepart(yy,Activitydate), Month(ActivityDate), DataCenter, Farm

) A

INNER JOIN

(SELECT *

from Monthly

where Year = DATEPART(yyyy, getdate()) and [Month] = 3

) B

ON

A.Year = B.Year and

A.Month = B.Month and

A.DataCenter = B.DataCenter and

A.Farm = B.Farm



I am confused on what could be the problem.
Please suggest.

Thanks
Sandeep.

View 6 Replies View Related

Problem With Executing Sql-query

Apr 27, 2004

When I execute this query, I get the error: 'The colum prefix 'ClassifiedAd does not match with a table'. But the table does exist. Does anyone know what's wrong?

select ca.ItemID,
ca.ModuleID,
'CreatedByUser' = u1.FirstName + ' ' + u1.LastName,
ClassifiedAd.CreatedDate,
'UpdatedByUser' = u2.FirstName + ' ' + u2.LastName,
ca.UpdatedDate,
ca.AdTitle,
ca.AdText,
ca.AdHasImage,
ca.AdPOCName,
ca.AdPOCEmail,
ca.AdPOCPhone
from ClassifiedAd ca
left outer join Users u1 on ca.CreatedByUser = u1.UserID
left outer join Users u2 on ca.UpdatedByUser = u2.UserID
where ca.ItemID = 1
and ca.ModuleId = 1

View 1 Replies View Related

Executing Sql Query From File

Aug 26, 2006

Hi everybody,
I need a syntax in SQL Server 7.0. By using which I need to excecute a query from file. By using xp_cmdshell I am getting it. But whole results comes in a single column. I need the result set as it appears while executing the query. Can you help me in this. Any help will be appreciated.

Thanks in advance,

Gopakumar N.Kurup

View 1 Replies View Related

SQL CE Query Keep Executing On Join

May 18, 2007

The following query runs fine
1)

Select cd.*

from TaxByPackage p join TaxOverrideReason tor

on tor.TaxId = p.TaxId

and p.PackageId in (10, 11)

join CalcData cd on cd.CalcTaxOverrideID = tor.TaxOverrideID



2)

but if I add following Join to above query then query does produce results and keep saying 'executing' for hours
"join TaxRate tr on tr.CalcDataID = cd.CalcDataID


Select cd.*

from TaxByPackage p join TaxOverrideReason tor

on tor.TaxId = p.TaxId

and p.PackageId in (10, 11)

join CalcData cd on cd.CalcTaxOverrideID = tor.TaxOverrideID

join TaxRate tr on tr.CalcDataID = cd.CalcDataID





3)

If I insert the data produce by first join 1) to a table and then join the new table data result to

Taxrate table then result come fine



insert into c1

select cd.*

from TaxByPackage p join TaxOverrideReason tor

on tor.TaxId = p.TaxId

and p.PackageId in (10, 11)

join CalcData cd on cd.CalcTaxOverrideID = tor.TaxOverrideID



select * from c1

join TaxRate tr on tr.CalcDataID = c1.CalcDataID





FYI : Version SQL CE is 3.0.5300

View 1 Replies View Related

Error While Executing Simple Query :(

Dec 21, 2007

Hi,

I am using this query to execute but it didn't

declare @AccountID numeric(3,0)
set @AccountID = 101
select * from AccountTest
WHERE AccountID IN
(CASE
WHEN @AccountID = 101 THEN (100,101)
ELSE @AccountID
END)

I m getting error like

Msg 102, Level 15, State 1, Line 9
Incorrect syntax near ','.

Pls help me out

T.I.A

View 3 Replies View Related

Returning Results While The Query Is Executing

Jul 20, 2005

Hi,I wanted to know if this is possible and if so, how do Ido it. Say, I have a query "SELECT * FROM Table WHEREColumn="some_value". This executes on a very large dataset and I would like to return the results as they queryexecutes rather than wait for the whole query to execute.Basically, I want to get the results as they are preparedby the database. Any way to do this?Regards,San

View 8 Replies View Related

Executing Multiple Query At One Time

Feb 27, 2007

hi,

i am making a n application which in between deletes the multiple tables from the sql database.

for that i have written the following code:

SqlCommand cmd = new SqlCommand();

cmd.CommandText = "delete from " + dbConstt.DBSchema + ".PicassoSelectivityLog where QTID=" + qtid;

cmd.ExecuteNonQuery();

cmd.CommandText = "delete from " + dbConstt.DBSchema + ".PicassoSelectivityLog where QTID=" + qtid;

cmd.ExecuteNonQuery();

in this way, many more tables are to be deleted.

is there any need to create the new SQLCommand object again and agin for each and every query. can iot be done like the given above or can there be some better method?



thanz in advance..

divya

View 1 Replies View Related

Executing Multiple Query Returned

Feb 24, 2008

Hi,

I am executing following script which return more than 1 query/row as there are more than 1 log backup :
select @Query = 'RESTORE LOG ' + @dbName + ' FROM DISK=' + '''' +@path+''''+ ' WITH' + ' NORECOVERY, FILE= ' + convert(varchar(10),Position) from #temp where BackupName = 'Log_Backup' and BackupType = 2 and Position> @Diff_Position EXEC(@QUERY)

[Above script restores database with latest Differential backup file and latest log files. ]
Now I want to execute this, but @Query don't work for more than one row returned . Can you please suggest me how to execute such query?



Thanks,
Dipak.

View 1 Replies View Related

Stored Procedure Executing Durations Are Different Between Executing From Application(web) And SQl Server Management Studio - Qu

Jan 24, 2008



Hi,

I have a web application using Stored Procedure (SP). I see that there's a SP taking long time to execute. I try to capture it by Profiler Tool, and find out that with the same SP on the same db with the same parameter. The duration of executing by my web app is far bigger than the duration of executing on SQl server management studio - query window

Please see the image through this url http://kyxao.net/127/ExecutionProblem.png


Any ideas for this issue?

Thanks a lot

View 1 Replies View Related

Error Executing Non Query: Timeout Expired

May 23, 2007

I was having some errors from the webpage accessing the OLlinks table in the database.
Error executing non query: Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.
StackTrace:   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)   at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)   at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)   at System.Data.SqlClient.SqlDataReader.ConsumeMetaData()   at System.Data.SqlClient.SqlDataReader.get_MetaData()   at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)   at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)   at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)   at System.Data.SqlClient.SqlCommand.ExecuteReader()   at admin_admOLEditLinks.selectData(String strID) in e:wwwroothomeadminadmOLEditLinks.aspx.cs:line 101DateTime:5/23/2007 1:14:10 PMSource:http://www.myDomain.comiAdmin/admOLEditLinks.aspx?ID=3ErrorMessage:Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.
I kept getting the above error so then I try to access the table directly inside of MS SQL Server Management Studio and then I recieved the following error:
SQL Execution Error.
Executed SQL statement: SELECT lnkID, linkFromID, linkToID FROM OLlinks
Error Source: .Net SqlClient Data Provider
Error Message: Timeout expired. The timeout period elapsed prior to completion of the opration or the server is not responding.
 
Open any other table works fine except this table only. Any help is much appreciated.

View 7 Replies View Related

Problem With Executing Query Or Stored Procedure

May 8, 2002

Hi all
i am facing problems in executing stored procedures.
Sometimes the connection to server fails.
all the query, sp working fine few minutes back fails.
the sp executes and executes and does not get results but hangs later a get an error.

Any ideas please let me know it is quite urgent. i am at the point of testing
and only now facing such problems

thanks
with regards
roopa

View 3 Replies View Related

Executing An Oracle SP From SQL Server Query Analyzer

May 3, 2000

Does anybody know the syntax for Executing an Oracle SP from SQL Server Query Analyzer? The Oracle database is linked to the SQL Server. Thanks.

View 1 Replies View Related

Executing Dynamic Query And Storing Resultset

Apr 13, 2007

Hi All,

I am really in a great trouble. My requirement is quite complex, as I feel, it may be quite simple for some of you.
Here is my problem -
Actually I am doing a project, where client has a specific requirement. i.e. he want's to build a query on runtime for selecting particular record he wants, so that we have provided a user interface where he can select any datasource e.g. SQL, Oracle, Excel etc. He also specifies the database name. He is displayed all the tables and columns under those tables. He selects columns from those table boxes and write the query he wants, with where clause, if required.
I am saving these query in a table, storing column names in another table where we map those oringinal column names with columns of another table, wehre we want to store actual result set of the prepared query by the user.
for examaple ..
if user preapare query like - 'SELECT CUST_ID, CUST_NAME FROM CUSTOMER
WHERE CUST_ID = 10'
In case of above query I will store CUST_ID in column COL1 of table TAB1 and CUST_NAME in COL2 of table TAB1, like that we have such fifty columns in that table. Now question is here every thing is dynamic data provider, database, tables, columns and where clause, then how can get the result set out of those queries and store that query output in the that storage table with columns col1, col2 and so on, upto 50 columns.
Please help me on this, as it is so urgent.
I will be very much thankful to you people.





Thanks & regards,

Praveen Kadam

View 3 Replies View Related

Error Executing Dynamic Select Query

Sep 29, 2007

Hi,

i have problem executing the dynamic query.

I have straight forward query as below and works fine


declare @count bigint

declare @varcount varchar(max)

set @varcount ='5'

If convert(bigint, @varcount) <> 4

print ' not 4 '

else

print 'Its 4'



Here is my dynamic query. The number of records (@No ) and the table name ( @table ) will be available for me as parameters to my stoped proc



declare @count bigint

declare @varcount varchar(max)

declare @tempTable varchar(max)

declare @vsSql varchar(max)

declare @No bigint

set @No = 5

set @table = 'sam'

set @varcount = ''

select @vsSql = 'declare @varcount varchar(max); select @varcount = count(*) from ' + @table + '; If convert(bigint,@varcount) <> ' + @No + ' raiserror(' +'mismatch, 11,1' +')'



When executed it throws the follwing error


Msg 8114, Level 16, State 5, Line 10

Error converting data type varchar to bigint.



Can anyone point out what to change in the query to work

~mohan

View 1 Replies View Related

Different Results When Executing From .NET Component Compare To Executing From SQL Management Studio

Oct 10, 2006

Hi all,I am facing an unusual issue here. I have a stored procedure, that return different set of result when I execute it from .NET component compare to when I execute it from SQL Management Studio. But as soon as I recompile the stored procedure, both will return the same results.This started to really annoying me, any thoughts or solution? Thanks very much guys

View 2 Replies View Related

SQL Server 2008 :: Executing Query / SPs Through Command Line

Sep 23, 2015

How do I execute below scripts using CMD:

Please Note: SQL server 2008r2, default instance with window Authentication.

1. Select * from Adventurewoks2008.Person.Person where Id = 5

2. Exec usp_getemployee (with parameter Manager_id = 100)

3. I have a folder named 'MyFolder' which contains 3 store procedures (usp_A, usp_B and usp_C).

How do I execute all 3 using CMD against AdventureWorks database.

View 9 Replies View Related

Error While Executing A A Query String Using EXEC Statement

Sep 18, 2007

Hi,
I have written a stored proc to bulk insert the data from a data file.
I have a requirement that i need to insert the data into a table of which the name is not known. I mean to say that the table name will be passed as a parameter to the stored proc. And also i need to insert the date that will also be passed as the parameter to the stored proc

The follwing statement works fine if i give the table name directly in the query



Code Snippet

DECLARE @LastUpdate varchar(20)

SET @LastUpdate = 'Dec 11 2007 1:20AM'

INSERT INTO Category
SELECT MSISDN, @LastUpdate FROM OPENROWSET( BULK '\remotemachinedatafile.txt',
FORMATFILE = '\remotemachineFormatFile.fmt',
FIRSTROW = 2) AS a



To satisfy my requirement ( i.e passing the table name dynamically , and the date) , i have formed the query string ( exact one as above ) and passing it to EXEC statement. But its failing as explained below






Code Snippet

@Category - Will be passed as a parameter to the stored proc


DECLARE @vsBulkSQL VARCHAR(MAX)

DECLARE @LastUpdate varchar(20)

SET @LastUpdate = 'Dec 11 2007 1:20AM'

SELECT @vsBulkSQL ='INSERT INTO '+ @Category + ' SELECT MSISDN, ''' + @LastUpdate +''' FROM OPENROWSET ' + '( BULK ' + '''' + '\remotemachinedatafile.txt'+ ''''+ ' ,' +
+ ' FORMATFILE ' + '=' + ''''+ '\remotemachineFormatFile.fmt'+ ''''+ ',' +
' FIRSTROW ' + '=' + '2' + ')' + ' AS a'

Print @vsBulkSQL - This prints the folliwing statement


INSERT INTO Category SELECT MSISDN, 'Dec 11 2007 1:20AM' FROM OPENROWSET ( BULK '\remotemachineDataFile.txt' , FORMATFILE ='\remotemachineFormatFile.fmt', FIRSTROW =2) AS a


Exec @vsBulkSQL - This statement gives the following error

The name 'INSERT INTO Sports SELECT MSISDN, 'Dec 11 2007 1:20AM' FROM OPENROWSET ( BULK '\remotemachineSecond.txt' , FORMATFILE ='\remotemachineFormatFile.fmt', FIRSTROW =2) AS a' is not a valid identifier.






Can any one please point out where am i doing wrong? Or do i need to do anything else to achive the same

~Mohan

View 4 Replies View Related

Executing An SSIS Package Containing A Data Mining Query Task From A SQL Job

Mar 28, 2007

Hi, I'm new to this forum, so please bare with me.

I've created a mining model, i've tweaked it etc and i'm now happy with the results its producing. I'm now looking to automate the processing and exporting of the results of the model i've done this simply by creating an SSIS package with two tasks, one task being to process the model the other task is a Data Mining Query task.

This package works fine in visual studio and when i deploy it to the server.

The problem i'm having is when i then try to execute the package from a job, after a bit of investigating i have tracked it down to the Encryption of "sensitive" properties. By default the encryption is based on UserKey which is why the package works for me when i execute it from VS or even the server, but when the job trys to execute the package running under the sql agent account it fails.

Looking at the security options i have for packages, i can either DontSaveSensitive, EncryptSensitiveWithUserKey or EncryptSensitiveWithPassword plus a few others.

DontSaveSenstive is clearly not an option as this just creates an unusable package.

EncrptSensitiveWithUserKey doesn't seem to be an option as the job runs under the SQL Agent account (also i'm thinking that the UserKey that the encryption is based on also incorporates other factors related to my profile that i can't impersonate? i might be wrong though)

EncryptSensitveWithPassword seems to be an option except that i can't get this to work either, there doesn't seem to be anyware in the job step to give it the password information.

Its frustrating me now because i've fallen at the very last hurdle, if anyone else has experienced this problem and knows how to resolve it that would great.

Thanks

Bob.























View 3 Replies View Related

Transact SQL :: Error Converting VARCHAR To BIGINT When Executing Query

Jun 4, 2015

DECLARE @i BIGINT
SET @i = 20150315
DECLARE @S VARCHAR(MAX)
SET @S = ''
SELECT @S = @S + '
DECLARE @Count BIGINT
SET @Count = '+@i+' + 1

SELECT @Count'
EXEC(@S)

I am trying to execute the above query but it is throwing me an error.

Msg 8114, Level 16, State 5, Line 6
Error converting data type varchar to bigint.

View 16 Replies View Related

Execute SQL Task: Executing The Query Exec (?) Failed With The Following Error: Syntax Error Or Access Violation. Possible F

Jan 23, 2008

Hi,
I'm having an SSIS package which gives the following error when executed :

Error: 0xC002F210 at Create Linked Server, Execute SQL Task: Executing the query "exec (?)" failed with the following error: "Syntax error or access violation". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.Task failed: Create Linked Server

The package has a single Execute SQL task with the properties listed below :

General Properties
Result Set : None

ConnectionType : OLEDB
Connection : Connected to a Local Database (DB1)
SQLSourceType : Direct Input
SQL Statement : exec(?)
IsQueryStorePro : False
BypassPrepare : False

Parameter Mapping Properties

variableName Direction DataType ParameterName

User::AddLinkSql Input Varchar 0


'AddLinkSql' is a global variable of package scope of type string with the value
Exec sp_AddLinkedServer 'Srv1','','SQLOLEDB.1',@DataSrc='localhost',@catalog ='DB1'

When I try to execute the Query task, it fails with the above error. Also, the above the sql statement cannot be parsed and gives error "The query failed to parse. Syntax or access violation"

I would like to add that the above package was migrated from DTS, where it runs without any error, eventhough
it gives the same parse error message.

I would appreciate if anybody can help me out of this issue by suggeting where the problem is.

Thanks in Advance.

View 12 Replies View Related

Management Studio Express &&amp; SQLcmd: Syntax Errors In Executing A MS-DOS Batch File &&amp; A Microsoft SQL Server Query File?

Nov 2, 2007

Hi all,
I have the "Northwind" database in my Sql Server Management Studio Express.

In my C:ProSSEAppsSamplesForChapter02Chapter02 folder, I have the following 2 files:
(1) ListColumnValues (MS-DOS Batch File)
sqlcmd -S .sqlexpress -v DBName = "Northwind" CName = "CompanyName" TName =
"Shippers" -i c:prosseappschapter02ListListColumnVales.sql -o
c:prosseappschapter02ColumnValuesOut.rpt
(2) ListColumnValues (Microsoft SQL Server Query File)
USE $(Northwind)
GO
SELECT $(CompanyName) FROM $(Shippers)
GO
When I ran the following SQLcmd:
C:ProSSEAppsSamplesForChapter02Chapter02>ListColumnValues.bat
I got the following "ColumnValuesOut.rpt" with error messages:

'Northwind' scripting variable not defined.
Msg 102, Level 15, State 1, Server L1P2P3SQLEXPRESS, Line 1
Incorrect syntax near '$'.
'CompanyName' scripting variable not defined.
'Shippers' scripting variable not defined.
Msg 102, Level 15, State 1, Server L1P2P3SQLEXPRESS, Line 1
Incorrect syntax near 'CompanyName'.

I copied these T-SQL statements from a book and I do not know how to correct them.
Please help and tell me how to correct these errors.

Thanks in advance,
Scott Chang




View 3 Replies View Related

Error H4000 Error While Executing The Query ???????????

Apr 3, 2008



Hi all
my problem is as follows : when i try to configure an ODBC DataAdapter using the configuration wizard

i use the query builder to select some fields from multiple tables , the displayed info depends in a user input , when i define the input parameter by writing(= ?) in the criteria column of the input field and continues , it displays an error

Error H4000 Error while executing the query , Error near ')' near character (214 for example).

so please if any one knows the solution to this error reply soon

Thanks a lot

Mina Samy

View 3 Replies View Related

Conditions, Expressions

Aug 7, 2007

I have a table
CREATE TABLE [dbo].[CmnLanguage]( [Id] [char](2) NOT NULL CONSTRAINT PkCmnLanguage_Id PRIMARY KEY, [EnglishName] [varchar](26) NOT NULL, [NativeName] [nvarchar](26) NOT NULL, [DirectionType] [smallint] NOT NULL, [IsVisible] [bit] NOT NULL, [CreatedDateTime] [datetime] NOT NULL DEFAULT GETDATE(), [ModifiedDateTime] [datetime] NULL)
We will use these 3 queries
select * from CmnLanguage where IsVisible = 0select * from CmnLanguage where IsVisible = 1select * from CmnLanguage
I want to make a method which handles these queries.
But at the back end on Stored Procedures
We have to write 3 queries
Which I don't want to do.
I want to minimize the queries and conditions
and want to just write one for these 3
Can any one do it?

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved