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
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.
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
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.
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
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
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.
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 RelatedHi!
I've a big problem by using the following query :
Code Snippet
public SqlCeResultSet selectRSQuery(String query)
{
SqlCeResultSet resultSet = initializeCommand(query).ExecuteResultSet(ResultSetOptions.Scrollable | ResultSetOptions.Updatable);
return resultSet;
}
SqlCeResultSet resultSet = sgb.selectRSQuery(
"SELECT p.pId, p.pLogin FROM Profiles p, ProfilesGroups pg, Groups g " +
"WHERE g.gId = pg.tpGroupId " +
"AND p.pId = pg.tpProfileId " +
"AND g.gProfileID = '" + app.Settings.Default.id + "'");
It return me this error :
Cannot generate an updatable cursor for the query because there is a non-standard join expression.
What can I do??
Thxx
While running query below on SQL Server 2005 (Build 3790: Service Pack 2):
SELECT DISTINCT pkg.PrimaryBarcode
FROM dbo.Package AS pkg (NOLOCK)
JOIN dbo.PackageCycle AS pc (NOLOCK)
ON pkg.PackageKey = pc.PackageKey
WHERE (pkg.BillCycleDateKey >= 20061201) OR
(pkg.BillStatusKey = 1)
I received a partial result set followed by
An error occurred while executing batch. Error message is: Couldn't replace text
I suspect this is a memory issue, but cannot find any reference to this particular msg on the Microsoft forums or the other 3rd party forums. PrimaryBarcode is a varchar(50)
I am not sure where to go from here. I would appreciate any ideas. Thanks in advance.
Cheers,
Mike Byrd
Got this error running a query with ssms2014
on SqlServer2014
but db has Compatibiliy level 100 (sql2008)
no sql error code in message and no error found in eventviewr (sqlserver log, windows log, application log ecc)when i run that query i got this message in "messages" tab, and in results tab i got a strange result, indeed the value returned doesn't exists in table.
ex table1.field1 possible values= 2,3
it returns 1113344
we got that error thru jdbc too...that query use a lot of joins, unfortunatley i can't post it here. today got this error, so i make a copy of 1 of involved table into another db in the same instance, re run the query on the original db, and it works well.
I am getting this error
An error occurred while executing batch. Error message is: The directory name is invalid."
in sql management studio.
i found that if i switch to results to text i don't get the error but with results to grid I get it no matter what i query in any database on the server.
Hi,
How to execute more than one query consecutively in SqlDataSource like the picture shown below.
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.
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
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
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
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
I am facing this error when using a SP called by a DTS package (using ADO connection).
The SP gets three parameter and looks like the following (just major part of it):
SET @STR = 'SET ROWCOUNT 10000' + ' WHILE 1 = 1 ' + 'BEGIN' + ' BEGIN TRAN'
SET @STR = @STR + ' DELETE FROM ' + @TableToBeCleaned + ' WHERE ' + @SELECTEDCOLUMN + ' <
DATEADD (m,' + ' -' + @KeepMonthsInDatabase + ', + GETDATE())'
SET @STR = @STR + 'IF @@ROWCOUNT = 0 ' + 'BREAK ' + 'COMMIT ' + 'END ' + 'SET ROWCOUNT 0'
EXEC (@str)
Following error appears:
Server: Msg 266, Level 16, State 2, Line 1
Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing.
Previous count = 4, current count = 5.
Server: Msg 266, Level 16, State 2, Procedure p_DBCleanerDeleteRows, Line 56
Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing.
Previous count = 4, current count = 5.
I use the SET ROWCOUNT to execute the delete in batches. When checking with SELECT @@TRANCOUNT it
returns me 5. Somehow it is clear to me that @@TRANCOUNT exited the SP with another value than when
entering it but there is commit in the dynamic SQL statement and I do not know what is wrong.
Is it neccessary to indicate BEGIN TRAN and COMMIT in the statement at all or can I ommit it?
Thanks
mipo
Perhaps this has already been answered. If so, I apologize. I'm anewbie at this stuff. I have a DTS package to import some VisualFoxpro data. Yes, I said Visual Foxpro. :) The DTS package worksbeautifully when I rightclick on the package and choose "execute." Andit works beautifully when I run dtsrun.... at the DOS prompt. But itdoesn't work when I try to execute it with code in Query Analyzer.I've tried exec master..xp_cmdshell 'dtsrun ...' And I've triedscheduling the package as a Job, disabling the Job and then usingmsdb..sp_start_job to run it. Neither of those work.I get a "DSN not found and no default driver specified."My DSN stuff looks OK to me, but perhaps some of that is wrong. Whatdoes the DSN have to look like in order for the code in Query Analyzerto find it? If the DSN is screwed up, why would it work OK whenexecuted from some places, but not in others? What am I missing?Any help would be much appreciated. :) thanks...-emily
View 3 Replies View Relateddear friends,
i started using Asp.net for developing webparts, web parts automatically connect to a database to be created and saved in the database.
i had already Sql Server 2005 Express Edition installed so my webpart page ran and automatically created the database "AspNetDb" in the "App_Data" folder.
but when i uninstalled Sql Server 2005 Express Edition and then installed the Sql Server 2005 Enterprise Edition, it gave me the following Error 26 as below :
--------------------------------------------------------- Beginning of Error Message --------------------------------------------------
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)
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. SQLExpress database file auto-creation error:
The connection string specifies a local Sql Server Express instance using a database location within the applications App_Data directory. The provider attempted to automatically create the application services database because the provider determined that the database does not exist. The following configuration requirements are necessary to successfully check for existence of the application services database and automatically create the application services database:
If the applications App_Data directory does not already exist, the web server account must have read and write access to the applications directory. This is necessary because the web server account will automatically create the App_Data directory if it does not already exist.
If the applications App_Data directory already exists, the web server account only requires read and write access to the applications App_Data directory. This is necessary because the web server account will attempt to verify that the Sql Server Express database already exists within the applications App_Data directory. Revoking read access on the App_Data directory from the web server account will prevent the provider from correctly determining if the Sql Server Express database already exists. This will cause an error when the provider attempts to create a duplicate of an already existing database. Write access is required because the web server accounts credentials are used when creating the new database.
Sql Server Express must be installed on the machine.
The process identity for the web server account must have a local user profile. See the readme document for details on how to create a local user profile for both machine and domain accounts.
--------------------------------------------------------- End of Error Message --------------------------------------------------
then when i checked the Machine.config file, i found this....
<connectionStrings> <add name="LocalSqlServer" connectionString="data source=.SQLEXPRESS;Integrated Security=SSPI;AttachDBFilename=|DataDirectory|aspnetdb.mdf;User Instance=true" providerName="System.Data.SqlClient" /> </connectionStrings>
then i thought it's becoz, of the datasource thing that the Machine.Config file supports by default the default free edition of Sql Server 2005 which is the Sql Server 2005 Express Edition.
but friend in my company we use Sql Server 2005 Enterprise Edition, so can anybody pls guide me and get me out of this soup on helping me to use Sql Server 2005 Enterprise Edition by modifying either 1) Machine.Config File and/or 2) Web.Config file and/or 3) any other way, but please help i want to do this, please......
regards and thanks
Gurjit Singh
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 Relatedhi,
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
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.
Hello,
I hope this forum is suitable for posting my question.
I have a site in asp and vb with an access database. It's work just fine. I want to convert my database from access to mssql express. So I convert it with Microsoft SQL Server Migration Assistant for Access. I also install mssql server on my computer, so I can test my site at home.
But now, I have trouble, I've received many errors like:
[Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near '#'.
(this appear when I try to insert date and time in database, witch in access database I have no problem)
ODBC driver does not support the requested properties
(this appears when I make a selection in database, where I use inner join).
And many more ...
Do I have to rewrite the code to all my files? It's so different working with MSSQL comparing with Access database?
From what I read, I understand that after converting the database from Access to MSSQL, I must change only the connection string and the site have no problem. But it seems it's not so easy. I'm pretty disappointed.
Can you give me an advice? Or some links where I can read something that can help me.
Thanks.
when i try to run a procedure i've been working on, i get the following Error:
Server: Msg 8144, Level 16, State 2, Line 0
Procedure or function has too many arguments specified.
execution code:
exec search_orders_1 @dwelltype=1
stored procedure:
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
ALTER PROCEDURE search_orders_1
@lowageint=null,
@highageint=null,
@highwealthint=null,
@lowwealthint=null,
@highpurpriceint=null,
@lowpurpriceint=null,
@lowmtgamtint=null,
@highmtgamtint=null,
@lowincomenvarchar(4000)=null,
@highincomenvarchar(4000)=null,
@marriedstatnvarchar(4000)=null,
@lowyearbuildnvarchar(4000)=null,
@highyearbuildnvarchar(4000)=null,
@mtgpresstatnvarchar(4000)=null,
@lenderpresstatnvarchar(4000)=null,
@ratetsnvarchar(4000)=null,
@loantsnvarchar(4000)=null,
@childstatnvarchar(4000)=null,
@homeownerstatnvarchar(4000)=null,
@lowpurdatenvarchar(4000)=null,
@highpurdatenvarchar(4000)=null,
@addresstype varchar(3)=null,
@SPSirvarchar(3)=null,
@debttypevarchar(3)=null,
@dwelltypevarchar(3)=null,
@zipselectnvarchar(4000)=null,
@acselectnvarchar(4000)=null,
@stselectnvarchar(4000)=null,
@cityselectnvarchar(4000)=null,
@counselectnvarchar(4000)=null,-- as
@debug bit = 0 AS
DECLARE @sql nvarchar(4000),
@paramlist nvarchar(4000)
SELECT @sql ='SELECT o.state_abbrv, count(o.state_abbrv) as kount FROM dbo.mortgage o WHERE 1 = 1'
IF @lowage<>0 and @lowage is not null
SELECT @sql = @sql + ' AND o.per1_age >= @xlowage'
IF @highage<>0 and @highage IS NOT null
SELECT @sql = @sql + ' AND o.per1_age <= @xhighage'
IF @lowincome<>'DF' and @lowincome IS NOT NULL
SELECT @sql = @sql + ' AND o.est_inc >= @xlowincome'
IF @highincome<>'DF' and @highincome IS NOT NULL
SELECT @sql = @sql + ' AND o.est_inc <= @xhighincome'
IF @highwealth<>0 and @highwealth IS NOT NULL
SELECT @sql = @sql + ' AND o.wealth_rating <= @xhighwealth'
IF @lowwealth<>0 and @lowwealth IS NOT NULL
SELECT @sql = @sql + ' AND o.wealth_rating >= @xlowwealth'
IF @highpurprice<>0 and @highpurprice IS NOT NULL
SELECT @sql = @sql + ' AND o.hm_purprice <= @xhighpurprice'
IF @lowpurprice<>0 and @lowpurprice IS NOT NULL
SELECT @sql = @sql + ' AND o.hm_purprice >= @xlowpurprice'
IF @lowyearbuild<>'DF' and @lowyearbuild IS NOT NULL
SELECT @sql = @sql + ' AND o.hm_year_build >= @xlowyearbuild'
IF @highyearbuild<>'DF' and @highyearbuild IS NOT NULL
SELECT @sql = @sql + ' AND o.hm_year_build <= @xhighyearbuild'
IF @lowmtgamt<>0 and @lowmtgamt IS NOT NULL
SELECT @sql = @sql + ' AND o.oo_mtg_amnt >= @xlowmtgamt'
IF @highmtgamt<>0 and @highmtgamt IS NOT NULL
SELECT @sql = @sql + ' AND o.oo_mtg_amnt <= @xhighmtgamt'
IF @lowpurdate<>'DF' and @lowpurdate IS NOT NULL
SELECT @sql = @sql + ' AND o.hm_purdate >= @xlowpurdate'
IF @highpurdate<>'DF' and @highpurdate IS NOT NULL
SELECT @sql = @sql + ' AND o.hm_purdate <= @xhighpurdate'
IF @marriedstat in ('M','S')
SELECT @sql = @sql + ' AND o.per1_ms = @xmarriedstat'
IF @ratets in ('V','F')
SELECT @sql = @sql + ' AND o.oo_mtg_rate_t = @xratets'
IF @homeownerstat in ('H','R')
SELECT @sql = @sql + ' AND o.homeowner = @xhomeownerstat'
IF @childstat in ('Y','N')
SELECT @sql = @sql + ' AND o.child_pres = @xchildstat'
IF @lenderpresstat ='Y'
SELECT @sql = @sql + ' AND o.oo_mtg_lender_name<>+ '' '''
IF @lenderpresstat ='N'
SELECT @sql = @sql + ' AND o.oo_mtg_lender_name=+ '' '''
IF @mtgpresstat='Y'
SELECT @sql = @sql + ' AND o.oo_mtg_amnt>0'
IF @mtgpresstat='N'
SELECT @sql = @sql + ' AND o.oo_mtg_amnt=0'
IF @loants<>'DF' and @loants IS NOT NULL
SELECT @sql = @sql + ' and o.oo_mtg_loan_t in ( ''' + REPLACE( @loants, N',', N''',''' ) + N''' )'
if @dwelltype='1'
SELECT @sql = @sql + ' and o.addr_unit_desnum='' '''
if @dwelltype='2'
SELECT @sql = @sql + ' and o.addr_unit_desnum<>'' '''
if @acselect <> 'DF' or @stselect <> 'DF' or @cityselect <> 'DF' or @counselect <> 'DF' or @zipselect <> 'DF'
SELECT @sql = @sql + ' and (1=2'
IF @acselect<>'DF' and @acselect IS NOT NULL
SELECT @sql = @sql + ' OR o.areacode in ( ''' + REPLACE( @acselect, N',', N''',''' ) + N''' )'
IF @stselect<>'DF' and @stselect IS NOT NULL
SELECT @sql = @sql + ' OR o.state_abbrv in ( ''' + REPLACE( @stselect, N',', N''',''' ) + N''' )'
IF @cityselect<>'DF' and @cityselect IS NOT NULL
SELECT @sql = @sql + ' OR o.citystate in ( ''' + REPLACE( @cityselect, N',', N''',''' ) + N''' )'
IF @counselect<>'DF' and @counselect IS NOT NULL
SELECT @sql = @sql + ' OR o.countystate in ( ''' + REPLACE( @counselect, N',', N''',''' ) + N''' )'
IF @zipselect<>'DF' and @zipselect IS NOT NULL
SELECT @sql = @sql + ' OR o.zip in ( ''' + REPLACE( @zipselect, N',', N''',''' ) + N''' )'
if @acselect <> 'DF' or @stselect <> 'DF' or @cityselect <> 'DF' or @counselect <> 'DF' or @zipselect <> 'DF'
SELECT @sql = @sql + ')'
SELECT @sql = @sql + ' group by o.state_abbrv ORDER BY o.state_abbrv '
IF @debug = 1
PRINT @sql
SELECT @paramlist = '@xlowageint,
@xhighageint,
@xlowincomenvarchar(4000),
@xhighincomenvarchar(4000),
@xmarriedstatnvarchar(4000),
@xhighwealthint,
@xlowwealthint,
@xhighpurpriceint,
@xlowpurpriceint,
@xlowyearbuildnvarchar(4000),
@xhighyearbuildnvarchar(4000),
@xlowmtgamtint,
@xhighmtgamtint,
@xmtgpresstatnvarchar(4000),
@xlenderpresstatnvarchar(4000),
@xratetsnvarchar(4000),
@xloantsnvarchar(4000),
@xchildstatnvarchar(4000),
@xhomeownerstatnvarchar(4000),
@xlowpurdatenvarchar(4000),
@xhighpurdatenvarchar(4000),
@xzipselectnvarchar(4000),
@xacselectnvarchar(4000),
@xstselectnvarchar(4000),
@xcityselectnvarchar(4000),
@xcounselectnvarchar(4000)'
EXEC sp_executesql @sql, @paramlist,
@lowage,
@highage,
@lowincome,
@highincome,
@marriedstat,
@highwealth,
@lowwealth,
@highpurprice,
@lowpurprice,
@lowyearbuild,
@highyearbuild,
@lowmtgamt,
@highmtgamt,
@mtgpresstat,
@lenderpresstat,
@ratets,
@loants,
@childstat,
@homeownerstat,
@lowpurdate,
@highpurdate,
@addresstype,
@SPSir,
@DebtType,
@DwellType,
@zipselect,
@acselect,
@stselect,
@cityselect,
@counselect
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
could someone help me figure out what's up? someone told me you can have 1,024 parameters for a stored procedure.... what am i missing?
do i have to many IF clauses?
Hi,
I get the following error while trying to execute a UDF.
Msg 6522, Level 16, State 1, Line 1
A .NET Framework error occurred during execution of user defined routine or aggregate 'test':
System.NullReferenceException: Object reference not set to an instance of an object.
System.NullReferenceException:
at SalesCentral_Database.UserDefinedFunctions.udfTest()
My function is defined as follows:
Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.SqlTypes
Imports Microsoft.SqlServer.Server
Partial Public Class UserDefinedFunctions
<Microsoft.SqlServer.Server.SqlFunction(DataAccess:=DataAccessKind.Read)> _
Public Shared Function udfTest() As SqlString
SqlContext.Pipe.Send(System.DateTime.Now().ToString())
Dim conn As New SqlConnection("context connection=true")
conn.Open()
Dim cmd As New SqlCommand("SELECT MAX(ACCTPERIOD) FROM TBL_OPPORTUNITY_HIS", conn)
Return New SqlString(cmd.ExecuteScalar().ToString())
End Function
End Class
SQL Server
CREATE ASSEMBLY SalesCentral_Database from '\isew5l6c ranfSFS_SalesSalesCentral_Database.dll' WITH PERMISSION_SET = SAFE
CREATE FUNCTION test() RETURNS NVARCHAR(10) AS EXTERNAL NAME [SalesCentral_Database].[SalesCentral_Database.UserDefinedFunctions].[udfTest]
SELECT dbo.test()
- Here I get the error mentioned above. Can anyone please tell me what am I missing.
Thanks.
This is the error I get if the login "reports" does not exist on the destination server
Error: The Execute method on the task returned error code 0x80131500
(ERROR : errorCode=-1073548784 description=Executing the query
"EXEC dbo.sp_grantdbaccess @loginame = N'reports', @name_in_db = N'reports' "
failed with the following error: "'reports' is not a valid login or you do not
have permission.". 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.
This is the error I get if the login "reports" does exist on the destination server
Error: The Execute method on the task returned error code 0x80131500
(ERROR : errorCode=-1073548784 description=Executing the query "EXEC
dbo.sp_grantdbaccess @loginame = N'reports', @name_in_db = N'reports' "
failed with the following error: "User, group, or role 'reports' 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.
What am I doing wrong ?
I'm trying execute DTSX-package
Code Block
exec master..xp_cmdshell '"C:Program Files (x86)Microsoft SQL Server90DTSBinndtexec.exe" /FILE ...'
But have got error
Code Block
'C:Program' is not recognized as an internal or external command,
for some reason it doesn't work!
Or xp_cmdshell doesn't work with long filenames?
Hello,
Today we succesfully integrated Report Services with Sharepoint services.After that I was trying to implement the reports we made in Report Services into the Sharepoint site.The first report was ok, everything went well, no errors and the report was succesfully executed.Then the second report, it is a bit a bigger and more advanced report.It also has to be executed at an other server.I am going to explain how the server setup is.We have 2 servers, a x32 and a x64 server.The x64 server is the "Live" server, and the x32 is our "Test" server.On the x32 server we have MS SQL Server 9.0.3200 (SP2), it is the same as on the x64 server.When i try to execute the report on the x32 server, I got an error.The error I get from reporting server is:
An internal error occurred on the report server. See the error log for more details.So I checked the error log, and then I got the following message:
EventType sql90exception, P1 w3wp.exe, P2 6.0.3790.3959, P3 45d6968e, P4 reportingservicesnativeclient.ni.dll, P5 2005.90.3042.0, P6 45cd6edb, P7 0, P8 00005283, P9 00000000, P10 NIL.For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.
After I had found the error, I had searched on Google what the message means.
I found some topics on other forums that said "Install SP2".
But that was not an option since we are on SP2.
Some additional information:
Just tried to run the first report on the x32 server, and it worked.
So the first report runs on the x32 as the x64 server.
Can someone tell me what to do?
Thanks,
Rens
Hello, I finally could upload the package, and from the management studio interface I ran the package and it worked perfectly.
When I created a job, with one step only to execute that package, the job fails.
When I go to history it doesnt give me any details of what failed on the package or in the job
Date 24/01/2007 12:30:28
Log Job History (Carga datos ACH)
Step ID 1
Server ATLANTESQL2005
Job Name Carga datos ACH
Step Name Carga de datos de ach
Duration 00:00:02
Sql Severity 0
Sql Message ID 0
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted 0
Message
Executed as user: ATLANTESYSTEM. The package execution failed. The step failed.
Maybe is the user that it tried to execute the package as?
How can I change it?
I wrote package using Integration Services and tried execute it on Visual Studio 2003 (Visual Basic). Package was loaded, but execute method has failed without any error messages. When I tried to run it on Visual Studio 2005, execution was successful.
Codes:
Dim App As DTSLib.IDTSApplication90 = New DTSLib.Application
Dim p As DTSLib.IDTSPackage90 = App.LoadPackage("d: empPackage.dtsx", True, Nothing)
p.Execute()
What can be the reason of the trouble?
Thanks,
Alexander
Hi All,
I created a Package that calls Three other Packages through the Execute package task.
I saved The main package and the sub-packages in Dontsavesensitive mode and Referred to the config file.
I checked the Package thru VSS and When iam running thru the Bat file iam getting the following error
Description: Error 0x80070002 while loading package file "C:Documents and SettingsUSERNAMEMy DocumentsVisual Studio 2005ProjectsImportFILEImportFILEPACKAGE.dtsx". The system cannot find the file specified.
Iam not sure why its asking for this path in the production server.
This is the initial path where i saved the package in BIDS environment.
Please help me.Help is greatly appreciated.
Thanks,
SVGP
.