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
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
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
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
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
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
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
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
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
Jun 16, 2007
Hi,
How to execute more than one query consecutively in SqlDataSource like the picture shown below.
View 3 Replies
View Related
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
