When I want to user variable in the name of the database, I have an error. What's wrong with my code ?
DECLARE @BASE_SOURCE varchar (30),@BASE_DESTI varchar(30),@TEST varchar(30)
set @BASE_SOURCE='BASE1'
set @BASE_DESTI='BASE2'
select * from @BASE_SOURCE.dbo.FOURNISS
Msg*170, Niveau*15, État*1, Ligne*4
Ligne 4 : syntaxe incorrecte vers '.'.
Hi All,
Recently in an SSIS package I am getting the following error for a particular Data flow task.
Error: 2008-01-25 12:01:48.58
Code: 0xC0202009
Source: Import Datasynapse Data User Events Source [3017]
Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x8000FFFF.
End Error
Error: 2008-01-25 12:01:48.73
Code: 0xC004701A
Source: Import Datasynapse Data DTS.Pipeline
Description: component "User Events Source" (3017) failed the pre-execute phase and returned error code 0xC0202009.
End Error
Our guess is when the data size of User Events table is more it throws this error. If we try to transfer small subset of data it succeeds. What could be reason for this error?
Since this is very urgent, immediate response would be very much appreciated.
Thanks & Regards,
Prakash Srinivasan
In my report, I have a table with a nested group. The inner most group contains a column with values that are dynamically calculated in the report. I would like the outer group to display the sum of the inner group values. However, the outer group does not have access to the inner group scope.
As a solution, I am attempting to use the method detailed at this site:
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1129743&SiteID=1
Essentially, the method is to declare a hash table in the embedded code where data can be stored. I slightly modified the code to the following:
Code Block
Public Dim hashTable as new System.Collections.HashTable
function setValue(key As Object, value As Object) As Object
If (Not hashTable.ContainsKey(key)) Then hashTable.Add(key, value)
Return value
end Function
function getValue(key As Object) As Object
If (hashTable.ContainsKey(key)) Then Return hashTable(key) Else Return Nothing
end Function
function sumRunningValue(key As Object, value As Object) As Object
If (Not hashTable.ContainsKey(key)) Then hashTable.Add(key, value) Else hashTable(key) = hashTable(key) + value
Return hashTable(key)
end Function
Inside the inner scope everything works great and I can even get the runnning value. But the reason I did this was to be able to access the value in the outer group. But, it doesn't work! When I call Code.getValue(key) I get Nothing back.
So, it seems this is a scope issue. Is anyone aware of a work around for this problem. Is it possible to declare the hashtable with a global scope, for instance? Is the code redeclared for every new group?
Thanks for any assistance,
Tyler
Hi
I am not able to access SSIS variables which are defined at Data Flow Task in a custom component. This custom component is developed by me in C#. How can i access these variables?
Please let me know if theres a way to access SSIS variables.
Thanks,
Vipul
Is there any way I can use a variable from my code behind file in the UpdateCommand of a sqlDataSource? I have tried
<%$ strUserGuid %>and<% strUserGuid %>
any help appreciated.Thanks
Dave
Hello,Does anybody know what is the documented and known behavior ofinserting/updating binary columns using host variables from a client to aserver which have different code pages? Will any code page / character setconversion take place? I am particulary interested in insert/update fromsubqueries.eg:insert into t1(binarycol) select :HV1 from t2versusinsert into t1(binarycol) select :HV1||charcol from t2update t1 set bytecol=:HV1versusupdate t1 set bytecol=:HV1||'abc'insert into t1 (bytecol) values(:HV1)versusinsert into t1 (bytecol) values(:HV1||'abc')Is the conversion dependent on the context?ThanksAakash
View 1 Replies View RelatedHello,
I'd like to use Globals!ReportName and User!Language in custom code like this:
Function Localize(ByVal key As String) As String
Return ReportUtil.Localizer.Localize(Globals!ReportName, key, User!Language)
End Function
If I preview the report I get an error message saying that there is error on line 2 of custom code.
Is it possible to reach report variables in custom code?
Thanks!
Werner
I cannot execute a package by using Execute Package task.
I supplied sa credentials to connection manager, and it shows the list of Packages on SQL Server but when running the task it says
Error 0xC0202009 while preparing to load the package. An OLE DB error has occurred. Error code: 0x%1!8.8X!.
Any clue ?
Thanks,
Fahad
I have 2 requests for desperate Hélio..
1) is there any way to run a query over a query without having to create a table with the results of the first query? (would drop table work? If so, how?
2) how can i define input variables the same way i do in excel? I am trying to run a couple of simulations based on 2 core inputs (in excel i would just do a data table)
Hi,I'm new to SQL Server, but an experienced .Net developer. I'm tryingto accomplish a query the most efficient way possible. My question isif you can define a temporary variable within a query to store tablesor fields. (Like the LET clause of LINQ) My query makes use ofsubqueries which filter my table (WHEREs, not SELECTs) in the sameexact way. I'd like to have a subquery at the beginning of my queryto filter the table(s) once, and then SELECT off it of later in thequery.Here is an (utterly poor) example. No, this is not from my project.My filter is a little more complex than 'c=@p'.('c' is a column/field, 't' is a table', '@p' is a parameter)SELECT *FROM (SELECT COUNT(c) FROM t WHERE c=@p GROUP BY c)CROSS JOIN (SELECT c FROM t WHERE c=@p)Bottom line, would something like the following be possible?@v = (SELECT c FROM t WHERE a=@p)SELECT *FROM (SELECT COUNT(c) FROM @v GROUP BY c)CROSS JOIN (SELECT c FROM @v)I'd like to know if this is possible within a query, but I can move toa Stored Procedure if I must. (I'll still need help then.)Thank you all
View 3 Replies View Related
Trying to update my local developer version of SQL Server 2005 SP1 to SP2. The setup program terminates with the following information dumped from the logs. Not sure if it is related, but I recently installed the SQL Server 2005 Compact Edition (tools, runtime, etc) on the same development machine. It is a little too deep for me to figure out
Darryl
Here are the last few lines of the HotFix.Log
04/24/2007 14:46:48.374 MSP Error: 29527 The setup has encountered an unexpected error in datastore. The action is RestoreSetupParams. The error is : Source File Name: datastorecachedpropertycollection.cpp
Compiler Timestamp: Wed Jun 14 16:27:59 2006
Function Name: CachedPropertyCollection::findProperty
Source Line Number: 138
----------------------------------------------------------
Failed to read property "InstallIds" {"MachineConfiguration", "", "DUTTONDARRYL1"} from cache
Source File Name: datastorecachedpropertycollection.cpp
Compiler Timestamp: Wed Jun 14 16:27:59 2006
Function Name: CachedPropertyCollection:etProperty
Source Line Number: 164
----------------------------------------------------------
Unable to write property into cache: IsClustered
Source File Name: datastoredatastorecacheschema.cpp
Compiler Timestamp: Wed Jun 14 16:28:00 2006
Function Name: DataStoreCacheSchema::writeProperty
Source Line Number: 115
----------------------------------------------------------
Unable
04/24/2007 14:46:48.694 MSP returned 1603: A fatal error occurred during installation.
04/24/2007 14:46:48.790 Registry: Opened registry key "SoftwarePoliciesMicrosoftWindowsInstaller"
04/24/2007 14:46:48.790 Registry: Cannot read registry key value "Debug"
04/24/2007 14:46:48.806 Copy Engine: Error, unable to install MSP file: c:f01fc31fd0988e3807HotFixSQLFilessqlrun_sql.msp
04/24/2007 14:46:48.822 The following exception occurred: Unable to install Windows Installer MSP file Date: 04/24/2007 14:46:48.822 File: depotsqlvaultstablesetupmainl1setupsqlsesqlsedllcopyengine.cpp Line: 800
******************************************************
Now the SQL9_Hotfix_KB921896_sqlrun_sql.msp.log shows this as the last few lines
MSI (s) (A4:38) [14:46:48:630]: Product: Microsoft SQL Server 2005 - Update 'Service Pack 2 for SQL Server Database Services 2005 ENU (KB921896)' could not be installed. Error code 1603. Additional information is available in the log file C:Program FilesMicrosoft SQL Server90Setup BootstrapLOGHotfixSQL9_Hotfix_KB921896_sqlrun_sql.msp.log.
MSI (s) (A4:38) [14:46:48:630]: Note: 1: 1729
MSI (s) (A4:38) [14:46:48:630]: Transforming table Error.
MSI (s) (A4:38) [14:46:48:630]: Note: 1: 2262 2: Error 3: -2147287038
MSI (s) (A4:38) [14:46:48:646]: Transforming table Error.
MSI (s) (A4:38) [14:46:48:646]: Transforming table Error.
MSI (s) (A4:38) [14:46:48:646]: Note: 1: 2262 2: Error 3: -2147287038
MSI (s) (A4:38) [14:46:48:646]: Transforming table Error.
MSI (s) (A4:38) [14:46:48:646]: Note: 1: 2262 2: Error 3: -2147287038
MSI (s) (A4:38) [14:46:48:646]: Transforming table Error.
MSI (s) (A4:38) [14:46:48:646]: Note: 1: 2262 2: Error 3: -2147287038
MSI (s) (A4:38) [14:46:48:646]: Transforming table Error.
MSI (s) (A4:38) [14:46:48:646]: Note: 1: 2262 2: Error 3: -2147287038
MSI (s) (A4:38) [14:46:48:662]: Transforming table Error.
MSI (s) (A4:38) [14:46:48:678]: Transforming table Error.
MSI (s) (A4:38) [14:46:48:678]: Note: 1: 2262 2: Error 3: -2147287038
MSI (s) (A4:38) [14:46:48:678]: Transforming table Error.
MSI (s) (A4:38) [14:46:48:678]: Note: 1: 2262 2: Error 3: -2147287038
MSI (s) (A4:38) [14:46:48:678]: Transforming table Error.
MSI (s) (A4:38) [14:46:48:678]: Note: 1: 2262 2: Error 3: -2147287038
MSI (s) (A4:38) [14:46:48:678]: Product: Microsoft SQL Server 2005 -- Configuration failed.
MSI (s) (A4:38) [14:46:48:678]: Attempting to delete file c:WINDOWSInstaller6749ee3.msp
MSI (s) (A4:38) [14:46:48:678]: Unable to delete the file. LastError = 32
MSI (s) (A4:38) [14:46:48:694]: Cleaning up uninstalled install packages, if any exist
MSI (s) (A4:38) [14:46:48:694]: MainEngineThread is returning 1603
MSI (s) (A4:2C) [14:46:48:694]: Destroying RemoteAPI object.
MSI (s) (A4:04) [14:46:48:694]: Custom Action Manager thread ending.
=== Logging stopped: 4/24/2007 14:46:48 ===
MSI (c) (58:E4) [14:46:48:694]: Decrementing counter to disable shutdown. If counter >= 0, shutdown will be denied. Counter after decrement: -1
MSI (c) (58:E4) [14:46:48:694]: MainEngineThread is returning 1603
=== Verbose logging stopped: 4/24/2007 14:46:48 ===
*************************************************************
The Summary.txt looks like this
Time: 04/24/2007 14:46:48.854
KB Number: KB921896
Machine: DUTTONDARRYL1
OS Version: Microsoft Windows XP Professional Service Pack 2 (Build 2600)
Package Language: 1033 (ENU)
Package Platform: x86
Package SP Level: 2
Package Version: 3042
Command-line parameters specified:
Cluster Installation: No
**********************************************************************************
Prerequisites Check & Status
SQLSupport: Failed
**********************************************************************************
Products Detected Language Level Patch Level Platform Edition
Database Services (MSSQLSERVER) ENU SP1 2005.090.2047.00 x86 DEVELOPER
Analysis Services (MSSQLSERVER) ENU SP1 2005.090.2047.00 x86 DEVELOPER
Reporting Services (MSSQLSERVER) ENU SP1 9.00.2047.00 x86 DEVELOPER
Notification Services ENU SP1 9.00.2047.00 x86 DEVELOPER
Integration Services ENU SP1 9.00.2047.00 x86 DEVELOPER
SQL Server Native Client ENU 9.00.2047.00 x86
Client Components ENU SP1 9.1.2047 x86 DEVELOPER
MSXML 6.0 Parser ENU 6.00.3890.0 x86
SQLXML4 ENU 9.00.2047.00 x86
Backward Compatibility ENU 8.05.1704 x86
Microsoft SQL Server VSS Writer ENU 9.00.2047.00 x86
**********************************************************************************
Products Disqualified & Reason
Product Reason
**********************************************************************************
Processes Locking Files
Process Name Feature Type User Name PID
**********************************************************************************
Product Installation Status
Product : Database Services (MSSQLSERVER)
Product Version (Previous): 2047
Product Version (Final) :
Status : In Progress
Log File : C:Program FilesMicrosoft SQL Server90Setup BootstrapLOGHotfixSQL9_Hotfix_KB921896_sqlrun_sql.msp.log
Error Number : 29527
I'm trying to create a simple Data transfermation. I have a flat file that came of a unix server.. it's 177 bytes wide.. thought it was 175, but when I created the flat file connector, I could see some extra characters on the end.
My output is going to be an excel spreadsheet, I only want two columns from the input. I created an oledb jet 4.0 connection. and followed instructions from here :
http://aspalliance.com/889_Extracting_Data_from_a_Flat_File_with_SQL_Server_2005_Integration_Services
to create my datafow.
On my first attempt to dataflow, I ran into unicode errors and had to do this:
ran into a problem with unicode errors. went to the source for the flat file. for the output column in question changed to Unicode string [DT_WSTR].
When I run , here are the errors I get:
[OLE DB Destination [513]] Error: An OLE DB error has occurred. Error code: 0x80040E09. [DTS.Pipeline]
Error: The ProcessInput method on component "OLE DB Destination" (513) failed with error code 0xC0202009. The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running.
[DTS.Pipeline] Error: Thread "WorkThread0" has exited with error code 0xC0202009.
[GanchoFileSource [1]] Information: The total number of data rows processed for file "\ammia01dev04D$JCPcpmgancho_venta_20070321.sal" is 19036.
[GanchoFileSource [1]] Error: Setting the end of rowset for the buffer failed with error code 0xC0047020.
[DTS.Pipeline] Error: The PrimeOutput method on component "GanchoFileSource" (1) returned error code 0xC0209017. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing.
[DTS.Pipeline] Error: Thread "SourceThread0" has exited with error code 0xC0047038.
Hi,My SQL Server 2005 won't start - can anyone provide some help? Thisseems to be the most significant error:- TDSSNIClient initialization failed with error 0x5, status code 0x51.There was some info here, but the reg key described looks okay:- http://blogs.msdn.com/sql_protocols/I am running SQL Server 2005 64-bit Standard Edition. The server inquestion is the first node of an Active/Active Cluster. This firststarted happening while I was trying to add a share as a clusteredresource of the other node (not to this node's resources).I have not yet reinstalled SQL Server 2005 because I am hoping for moreexplanation of the problem and a possible fix.Thanks,Tom
View 1 Replies View RelatedHi All,I want to catch the next MSSQL error in my SQL code with following continuecalculationsServer: Msg 17, Level 16, State 1, Line 1SQL Server does not exist or access denied.If REMOTE_SERVER_1 is inaccessible (as in (a) below) the executing of SQLwill not continue with (b) - I need the code in (b) to run despite whetherthe previous exec was successful or not - Any ideas?begin transaction(a) exec REMOTE_SERVER_1...bankinsert '1' , '1' , 1 , 0 , 0(b) print @@errorcommit transactionwhere REMOTE_SERVER_1 is link to server created byEXEC sp_addlinkedserver @server = 'REMOTE_SERVER_1', @srvproduct = '',@provider = 'SQLOLEDB', @datasrc = 'MYCOMP1', @catalog = 'mirror2'EXEC sp_addlinkedsrvlogin @rmtsrvname = 'REMOTE_SERVER_1', .....Exec sp_serveroption 'REMOTE_SERVER_1', 'data access', 'true'Exec sp_serveroption 'REMOTE_SERVER_1', 'rpc', 'true'Exec sp_serveroption 'REMOTE_SERVER_1', 'rpc out', 'true'Exec sp_serveroption 'REMOTE_SERVER_1', 'collation compatible', 'true'Any help will be greatly appreciated
View 1 Replies View Related
Hi all,
can anyone tell me if an oleDb connection (provider is Jet 4.0 to Access database) can be enlisted in a Distributed Transaction?
The goal is to copy data from SqlServer to Access within a transaction.
Pier
I have tired for this!
When I use SSIS for extract data from ssas, that means,I use mdx query.
then random error occured.
Hope some one can understand my poor English....
And the Error Info show below.
Code Snippet
Error: 0xC0202009 at Data Flow Task - For Individual User Tech Points, OLE DB Source 1 1 [31]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E05.
An OLE DB record is available. Source: "Microsoft OLE DB Provider for Analysis Services 2005" Hresult: 0x00000001 Description: "Error Code = 0x80040E05, External Code = 0x00000000:.".
Error: 0xC004701A at Data Flow Task - For Individual User Tech Points, DTS.Pipeline: component "OLE DB Source 1 1" (31) failed the pre-execute phase and returned error code 0xC0202009.
hello,
we have changed the name of MS SQL server 2005 from XYZ to ABC using
sp_dropserver <old_name>
GO
sp_addserver <new_name>, local
GO
Now our maitenance plan is getting failed we are not able to execute backup jobs we are getting following error
Date 03.05.2008 16:00:00
Log Job History (ADM_AdminDB_TP_Backup.Subplan_1)
Step ID 0
Server ABC
Job Name ADM_AdminDB_TP_Backup.Subplan_1
Step Name (Job outcome)
Duration 00:00:00
Sql Severity 0
Sql Message ID 0
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted 0
Message
The job failed. Unable to determine if the owner (XYZSQLServer) of job ADM_AdminDB_TP_Backup.Subplan_1 has server access (reason: Could not obtain information about Windows NT group/user 'XYZSQLServer', error code 0x534. [SQLSTATE 42000] (Error 15404)).
please help us in this issue
This error occurs when the ActiveX task tries to execute:
[ActiveX Script Task] Error: Retrieving the file name for a component failed with error code 0x001B6438.
Anybody know how to troubleshoot these errors? I can't find anything on this error code. The same script works in DTS.
Hello,
I am applying hourly differential backup to the backup server from production with the following command. This command makes the database on standby server into read only mode.
RESTORE DATABASE ARSYSTEM FROM DISK = 'E:SQL backup from productionsql_full_backup'
WITH MOVE 'arsystem' TO
'd:ardataarsystem.mdf' ,
MOVE 'arsystem_log' TO 'D:ARLOGARsystem' ,
STANDBY = 'E:SQL backup from productionSQL daily diff back up'
Now I want to run a command which will put the database in write mode. I have created a job which would make the datbase Write mode. This job runs successfully sometimes and fails sometimes. I need to ensure that the job always succeeds. When it fails, how do I troubleshoot and what is the possible fix?
Thanks in advance.
The error message is
Cannot apply the backup on device 'E:SQL backup from productionSQL daily diff back up' to database 'ARSYSTEM'. [SQLSTATE 42000] (Error 3136) RESTORE DATABASE is terminating abnormally. [SQLSTATE 42000] (Error 3013). The step failed.
The steps for the job are as follows with the failing step highlighted in bold.
copy /y "\172.31.9.12Remedy BackupackupSQL backupsql_full_backup" "E:SQL backup from productionsql_full_backup"
copy /y "\172.31.9.12Remedy BackupackupSQL backupSQL daily diff back up" "E:SQL backup from productionSQL daily diff back up"
xp_cmdshell 'net stop "bmc remedy action request system server"'
exec rp_kill_db_processes 'ARSYSTEM'
RESTORE DATABASE ARSYSTEM
FROM DISK = 'E:SQL backup from productionsql_full_backup'
WITH
MOVE 'arsystem' TO 'd:ardataarsystem.mdf' ,
MOVE 'arsystem_log' TO 'D:ARLOGARsystem' ,
NORECOVERY
Failing step
RESTORE DATABASE ARSYSTEM
FROM DISK = 'E:SQL backup from productionSQL daily diff back up'
WITH
MOVE 'arsystem' TO 'd:ardataarsystem.mdf' ,
MOVE 'arsystem_log' TO 'D:ARLOGARsystem' ,
RECOVERY
xp_cmdshell 'del /f "E:SQL backup from productionsql_full_backup"'
xp_cmdshell 'del /f "E:SQL backup from productionsql daily diff back up"'
xp_cmdshell 'net start "bmc remedy action request system server"'
I have scheduled the following hourly diffential restore job too which never fails.
RESTORE DATABASE ARSYSTEM FROM DISK = 'E:SQL backup from productionsql_full_backup'
WITH MOVE 'arsystem' TO
'd:ardataarsystem.mdf' ,
MOVE 'arsystem_log' TO 'D:ARLOGARsystem' ,
STANDBY = 'E:SQL backup from productionSQL daily diff back up'
EXEC MASTER..XP_CMDSHELL 'del /f "E:SQL backup from productionSQL daily diff back up"'
Hi,
I just installed SQL Server 2005 on Windows Vista.
I am getting an error "An unknown error occurred in the WMI
provider. Error Code 8000000A" when connecting to a web server. The
reporting services is running. Is there any other installation that is
missing. Please help.
Hello everyone.
I am trying to install Project Server, and i'm having issues with sharepoint, and connecting to SQL:
dataserver is running sbs2003 sql2003 and analsys services.
server2 is running server2003 is to be the application server for project.
ProjectDb is the database that i have setup in sql.
username is the account that can control everything as administrer.
in Sharepoint is asks for the database server: <<dataserver>>
SQL Server database name: <<ProjectDb>>
I'm using windoes authentication and then i click ok, and get the error message.
I've also see the error message can not find the SQL Server, and access denied. Under ODBC i have installed the sql server information under System DSN.
Any help would be great.
Thanks
Everett Buel
Hi I'm a n00b at ASP.Net and C# but the company I work for requires me to know this and develop their website using these languages.
Anyway I've been doing pretty good so far I got the page looking as it should etc and databases all set up, now I've hit my first real problem... I am using Visual Studio 2005 and in the Web Developer section. I want to make a SELECT query using the <asp:LoginName> control as the where clause.
e.g. if admin logs in there is a welcome message : Welcome Admin now I want to use the 'Admin' <asp:LoginName> as the SELECT in my query i.e. SELECT * FROM tbla WHERE ([fldCustomerCode] = 'LoginName' )
I was just really wondering if that is in the .aspx file or the aspx.cs code behind file?
Thanks for any help given
I’ve reconfigured Microsoft’s IBS Store shopping cart to function within a small e-commerce website. What I am trying to do is to modify the code slightly in order to use a third party credit card processing center. The situation is this: once the customer clicks the final "check out" button, a stored procedure writes all of the product ordering information into the database. I, then, capture what they're wanting to purchase with the following SQL statement:
Dim strSQL as String = "Select orderID, modelNumber from orderDetails" & _
"where CustomerID = " & User.Identity.Name & _
"And orderid = (SELECT MAX(orderid)FROM orderDetails" & _
"where CustomerID = " & User.Identity.Name & ")"
What I would like to do is assign specific values to variables based off of the above query. For example:
Dim orderItem as String = (all of the modelNumbers from the query)
Dim orderIdItem as String = (all of the orderIDs from the query)
How do I do this?? Any help is much appreciated! Thanks in advance.
Ron
Hey guys,
I was wondering if there was a way to set a local variable from the results of a query or stored procedure.
For example, I have a stored procedure or a select statement that returns 1 row, with columns "a" "b" "c" "d"
I want to set my variable to be the value in column "b"
Is there a way to do that? I'm new to tsql, so any code would help.
I tried this, but I know my syntax is messed up
declare @myvar as integer
set @myvar = (execute mystoredProcedure).b
thanks
I'm trying to create a stored procedure with a dynamic update statement depending on the variables received. I receive a sql error "Incorrect syntax near the keyword 'WHERE'." although the variable, @tempvar, appears correct. Any ideas?
----
DECLARE @tempvar varchar(1025), @SolutionDetail varchar(1000), @hours varchar(12), @id int
if @SolutionDetail <> " "
set @tempvar = "SolutionDetail = " + "'" +@SolutionDetail + "'"
IF @hours <> " "
set @tempVar = @tempvar + ", hours = " + "'" + @hours +"'"
UPDATE WorkOrderTbl SET @tempvar
WHERE workorderid = @id
Hi all,
I was wondering if anyone can help me figure out how to insert a
variable into a query using Visual Studio 2005 with the SqlDataSource
control. I cant seem to be able to enter a var into the query
parameters.
this is my SqlDataSource:
<asp: SqlDataSource ID="SqlDataSource1" runat="server"
ConnectionString="<%$ ConnectionStrings:imLookinLikeConnectionString
%>" DeleteCommand="DELETE FROM [tblDiaryEntries] WHERE [DiaryEntryID] = @DiaryEntryID"
SelectCommand="SELECT tblDiaryEntries.DiaryEntryID,
tblDiaryEntries.EntryDate, tblDiaryEntries.Subject,
tblDiaryEntries.DiaryEntry, aspnet_Users.UserName FROM tblDiaryEntries
INNER JOIN aspnet_Users ON tblDiaryEntries.UserID = aspnet_Users.UserId
WHERE UserName=@UserName ORDER BY tblDiaryEntries.EntryDate DESC"
UpdateCommand="UPDATE [tblDiaryEntries] SET [DiaryEntry] = @DiaryEntry,
[EntryDate] = @EntryDate, [Subject] = @Subject WHERE [DiaryEntryID] =
@DiaryEntryID" InsertCommand="INSERT INTO tblDiaryEntries(UserID,
EntryDate, Subject, DiaryEntry) VALUES (@UserId, GETDATE(), @Subject,
@DiaryEntry)"> <DeleteParameters> <asp: Parameter Name="DiaryEntryID" Type="Int32" /> </DeleteParameters> <UpdateParameters> <asp: Parameter Name="DiaryEntry" Type="String" /> <asp: Parameter Name="EntryDate" Type="String" /> <asp: Parameter Name="Subject" Type="String" /> <asp: Parameter Name="DiaryEntryID" Type="Int32" /> </UpdateParameters> <InsertParameters> <asp: Parameter Name="DiaryEntry" Type="String" /> <asp: Parameter Name="EntryDate" Type="String" /> <asp: Parameter Name="Subject" Type="String" /> <asp: ProfileParameter DefaultValue="Anonymous" Name="UserName" PropertyName="UserName" /> <asp: Parameter Name="UserId" /> </InsertParameters> <SelectParameters> <asp: ProfileParameter DefaultValue="Anonymous" Name="UserName" PropertyName="UserName" /> </SelectParameters> </asp: SqlDataSource>
What I want to do is to tell the SqlDataSource that @UserName = this.User.Identity.Name, but I only know how to do that in
code-behind, not sure how to insert it into the code above.Any ideas?
Is it possible to use variable name to dynamically define a query in a stored procedure? EX:
@Column = 'COUNT(*)'
@Category = 'Products'
@Table = 'Items. + @Category
SELECT @Column
FROM @Table
Hi all,
If I have a query string that is to be stored in a database, for example
Code:
SELECT prod_id, prod_name, prod_desc FROM products WHERE prod_id = 'variable'
how can I put a variable identifier into this string so that when I need to run the query I call it from the database and simply insert the relevant variable in the correct place.
Is there an appropriate way of doing this in MS SQL Server?
Thanks
Tryst
Hello,
I am wondering what is the difference between assigning variables in the following two ways.1.
Dim myVar11 As Variables
Dts.VariableDispenser.LockOneForWrite("LoopCount", myVar11)
myVar11("LoopCount").Value = 32.
Dts.Variables("LoopCount").Value = 3I received this error when processing SSIS Package.
Error: A deadlock was detected while trying to lock variables
"User::LoopCount" for read/write access. A lock cannot be acquired after 16 attempts. The locks timed out.
Your help is appreciated,
-Lawrence
Hi,
I think I'm just braindead or simply thick...since this shouldn't be that hard, but I'm stumped right now.
So, I'm trying to retrieve from a table, with a sql stored procedure with the sql like
"select height, width, depth from products where id=@idinput"
OK, so this part is easy, but if I wanted to say, return this to my code and assign height to a variable Ht, width to Wd and depth to Dp, how could I do that?
This is what I've got so far...
[code]
cmdSelect = New SqlCommand( "GetProd", connstr )
cmdSelect.CommandType = CommandType.StoredProcedure
dbcon.Open()
dbcon.Close()
[/code]
The main prob is just what to connect this record to in order to access the individual fields.
Thx :)
Hi!
Here is a snap form my code
declare @max_id int
declare @the_db varchar (30)
select @the_db = 'mydb'
exec ("select """ @max_id """ = max(id) from " + @the_db + "..mytable")
I got syntex error for the @max_id. The script is writen based on the sample given in MSSQL6.5 Book Online, chapter "Transaction-SQL Reference 6.0" section "C"->"Control-Flow Lang."->"Control-Flow Examples"
Can someone help me on how to assign a value to a local variable from a dynamic query.
Thank for any help in advance
Hank Lee
I am getting the error 'Incorrect syntax near the keyword set' with the following code snippet -
declare @dteTo Date
set @dteTo = CONVERT(date, GETDATE())
My intention is to set the variable @dteTo to the current date (no time component). If I run a SELECT CONVERT(date, GETDATE()) command I do get what I want, it is just assigning the value to the variable that isn't working as intended.
I am brand spankin new to stored procedures and don't even know if what I want to do is possible. From everything I've read it seems like it will be. I have a table, punchcards. In this table are all the punch in/out times for a week. I want to create a stored proc to calculate how many hours a punchcard entry is.
Thats the dream.
The reality is that I can't even get a tinyint from a table to load to a variable and be printed out. I am using sql server 8.
Here is what I have as of this moment for my sp.
ALTER PROCEDURE usp_CalculatePunchcard
AS
DECLARE @dtPP DateTime
SET @dtPP = (SELECT thursday_in1
FROM punchcards
WHERE (punchcard_id = 1))
/*
Also tried....
SELECT @dtPP=thursday_in1
FROM punchcards
WHERE (punchcard_id = 1)
*/
PRINT @dtPP
RETURN
/*
for some reason i can't use GO ... even though every
document i've read on stored procedures has used GO
and none use RETURN
*/
The only output this is producing is ' Running dbo."usp_CalculatePunchcard". '
Any help would be greatly appreciated as I am about to kick someone/something.
Thanks