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 have managed to get the Forms Authentication sample to work bu there is a hitch. I am running Report Server and Report Manager on the same(test) PC. I can logon to the Report Server without a problem but when I try logon from the UILogon.aspx page for Report Manager I receive the following error:
An error occurred while attempting to get the ReportServer Url. The RPC server is unavailable. (Exception from HRESULT: 0x800706BA)
An error occurred while attempting to get the ReportServer Url. The RPC server is unavailable. (Exception from HRESULT: 0x800706BA)An error occured while attempting to get the ReportServer Url. The RPC server is unavailable.(Exception from HRESULT: 0x800706BA).
I can register users which suggests that the page can connect to the database.
Any help???
I've just installed SQL Server Express and attempted to create my first database and received this error message :-(
Maybe this is a long shot, but can someone tell me what I've done wrong? Do I have a bad install or what?
Essentially I followed the steps below to create the database:
1. Started MS SQL Server Management studio Express
2. Right clicked on the Database folder
3. Selected "New Database..."
4. New Database panel displayed
5. Entered name of database - pip
6. Clicked "OK"
7. Receieved error dialog with followig message:
TITLE: Microsoft SQL Server Management Studio Express
------------------------------
Create failed for Database 'pip'. (Microsoft.SqlServer.Express.Smo)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.2047.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Create+Database&LinkId=20476
------------------------------
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.Express.ConnectionInfo)
------------------------------
Could not obtain exclusive lock on database 'model'. Retry the operation later.
CREATE DATABASE failed. Some file names listed could not be created. Check related errors. (Microsoft SQL Server, Error: 1807)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.2047&EvtSrc=MSSQLServer&EvtID=1807&LinkId=20476
------------------------------
BUTTONS:
OK
------------------------------
Here is the error:
TITLE: SQL Server Import and Export Wizard
------------------------------
An error occurred which the SQL Server Integration Services Wizard was not prepared to handle.
------------------------------
ADDITIONAL INFORMATION:
Exception has been thrown by the target of an invocation. (mscorlib)
------------------------------
The connection type "EXCEL" specified for connection manager "{55E5636D-F2A9-48D6-9723-867C9F241F5B}" is not recognized as a valid connection manager type. This error is returned when an attempt is made to create a connection manager for an unknown connection type. Check the spelling in the connection type name.
({F390DBA3-1B52-43F4-A624-9E71D273B4D7})
I really need to get this data into SQL server... why do I have to go through SQL 2000?
Regards,
I have a log table with no indexes, triggers, or keys. During the course of development, I will clean out the entries by selecting all the rows (in Database Explorer) and hitting the delete key.
View 6 Replies View RelatedHi,
We have a little problem for you:
We have 2 servers both running sqlexpress2005, both have service pack 1 installed, both have the same db (same tables etc.) both have the same users with the same passwords. Everything is identical.
What I want to do is move the db. I am attempting to do this by making a backup of the db and restoring it into the second machine. (I am not sure of a more practical way to do this without stopping the SQL server).
This is the problem, the backup process works fine, without error, but when restoring the copy to the other server ,it loses the dbowner property and all default users when attempting to connect recieve a 4064 error even though all the password for users are there. Very strange.
I tried to restore the copy to to the same server that I made the copy and everything works, this only happens when attempting to restore the copy on another machine.
Do you know if this is a possible bug? I have tried to do further test on 4 different machines now and the same happens.
The objective is to be able to send the data from sqlexpress server to another by making a backup and without the need t stop the sql server. Any suggestion would be appreciated.
Adam
i'm getting the following error message when i attempt to configure my server as a local distributor:
TITLE: Connect to Server
------------------------------
Cannot connect to 9A6375C3046246B.
------------------------------
ADDITIONAL INFORMATION:
Failed to connect to server 9A6375C3046246B. (Microsoft.SqlServer.ConnectionInfo)
------------------------------
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: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server) (Microsoft SQL Server, Error: 53)
this error is occuring despite the fact that i have the server configured to allow remote connections. can someone please help me troubleshoot this error? thanks.
In SQL Server 2005, via the GUI, I wish to backup a database to an additional disk file (there is already an existing backup disk file for this database), so that I can have more than one backup. I've added the new disk file name, highlighted it, and clicked OK.
I get an immediate error (see below). Note, the 2nd error message is specifying the existing backup disk file, not the new one I'm attempting to create.
"Backup failed for Server 'WCS-DEV-TPA'. (Microsoft.SqlServer.Smo)"
"System.Data.SqlClient.SqlError: The volume on device 'D:Program FilesMicrosoft SQL ServerMSSQLBACKUPWCS_ADV_Longmont.bak' is not part of a multiple family media set. BACKUP WITH FORMAT can be used to form a new media set. (Microsoft.SqlServer.Smo)"
Does anyone know what causes this and how to correct it?
I am trying to save user data to a sql table but keep getting the following error;
Unable to cast object of type 'System.Boolean' to type 'System.Data.SqlClient.SqlParameter'.
I am using VWD Express Edition with .NET 2.0 and a SQL 2000 database. The code that generates the error is as follows;
Public Function chkUser(ByVal strUser As String) As Stringdim MyConnection As SqlConnectionDim MyCommand As SqlCommandDim ReturnString As StringDim params As SqlParameterDim SelectCmd As String = "wm_CheckUser"MyConnection = New SqlConnection(myConnectionString)MyCommand = New SqlCommand(SelectCmd, MyConnection)MyCommand.CommandType = CommandType.StoredProcedure
Tryparams = MyCommand.Parameters.Add("@parUser", SqlDbType.VarChar, 100).Value = strUserMyCommand.Connection.Open()MyCommand.ExecuteNonQuery()ReturnString = "Record Exists!"Catch Exp As SqlExceptionReturnString = ReturnError(Exp.Number, "Users", Exp.Message)End TryMyCommand.Connection.Close()Return ReturnString
End Function
Basically, I'm traying to check to see if a user id already exists in the database before saving the data the user entered. If the email address entered by the user is already in the database I want a message to be shown to the user. If the email address does not exist then the data entered by the user is saved and the form goes to step two (2) of the user registration process.
Any help with this would be greately appreciated. I can't seem to see what is wrong here. Please someone help.
Thanks,
Jaime
I've opened port 1433 in the Config Manager (error: The request failed or the service did not respond in a timely fashion.)
My Shared Memory Protocol is enabled, I've confirmed that the Server is allowed in Windows Firewall, I've configured the SQL Server to allow remote connections, I've started the SQL Browser with success....
I also receive the error message that (The MSSQL$SQLEXPRESS service on .... started and then stopped (Object Explorer)).
I uninstalled and reinstalled SQL Server Express 2012 and get the following failures:
Database Engine Services
SQL Server Replication
I think this the the core of my issue. how to remedy this?
Hi,
I'm trying to install Microsoft SQL Server 2005 Express Edition (x86) on my laptop when a setup failure message would show up at the end. When I clicked for more details it has the followings:
Product Version 9.00.3042.00
Message Source setup.rll
Message ID 50000
EvtType sharedsetupsharedsetupactions.cpp
And later when I tried to uninstall it from Add/Remove Program the screen would simply flash when I click but nothing happens.
Please let me know what do I need to install the piece correctly, thanks!
Andrew
I successfully installed SQL Server 2005 SP1 on one PC. When I attempt to install it on a second PC, however, I get the following obscure error:
Unexpected Error Occurred
The following unexpected error occurred:
That's right, there's nothing else listed, other an OK button. D'oh! When I press the OK button, the following error report dialog box appears:
Hotfix.exe
A recently applied update, NULL, failed to install.
There are no pending updates that I'm aware of, as I just booted the PC. I sent the error report.
I notice on this particular PC that the SP1 install EXE is expanding/writing to an external SCSI drive; would that make a difference? I don't see a way to specify a different (i.e., internal ATA drive).
Any other ideas?
TITLE: Microsoft Visual Studio
------------------------------
Error at Data Flow Task [XML Source [55]]: There was an error setting up the mapping. The root element of a W3C XML Schema should be <schema> and its namespace should be 'http://www.w3.org/2001/XMLSchema'.
------------------------------
ADDITIONAL INFORMATION:
Pipeline component has returned HRESULT error code 0xC02090CF from a method call. (Microsoft.SqlServer.DTSPipelineWrap)
------------------------------
BUTTONS:
OK
------------------------------
The error pasted above arose when I attempted to create a XML source based on a Crystal Reports XI report exported as XML. Is there any special modification to make to the XML exported from Crystal to allow SSIS to read it properly?
Thanks for your help,
Sid
I am running the following script to attempt a restore of a differential backup:
RESTORE DATABASE AdventureWorks
FROM DISK='C:SQL2005_BackupsAutoBackupsAdventureWorks.bak'
WITH
NORECOVERY
GO
RESTORE DATABASE AdventureWorks
FROM DISK='C:SQL2005_BackupsAutoBackupsAdventureWorksDiff.bak'
WITH RECOVERY
GO
I thought this was the way to do it. It does restore the full backup, but on the attempt to restore the differential backup, I get the following error:
Msg 3136, Level 16, State 1, Line 1
This differential backup cannot be restored because the database has not been restored to the correct earlier state.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
Does anyone know what this means? Do I have to use "with recovery" on the first restore? (The sample I took this from used "with norecovery")
The original backups were done with SQL Agent scheduled jobs. The script for the full backup is:
BACKUP DATABASE AdventureWorks
TO DISK='C:SQL2005_BackupsAutoBackupsAdventureWorks.bak'
The script for the differential backup is:
BACKUP DATABASE AdventureWorks
TO DISK='C:SQL2005_BackupsAutoBackupsAdventureWorksDiff.bak'
WITH DIFFERENTIAL, INIT
All I can say is, it's a good thing I am testing this out with non-critical data, because I obviously don't know what I am doing. (Sorry, I'm primarily a programmer, not a DBA) Can anyone help?
Thanks
I'm unable to use SSIS packages after installing SQL Server 2005. Specifically when I first loaded a new Integration Services project in SQL Server Business Intelligence Development Studio the default SSIS package could not load, I get Package.dtsx in my tabbed document view but in the body of this window is a error message: "Microsoft Visual Studio is unable to load this document. QI for IEnumVARIANT failed on the unmanaged server."
If I try to use the SQL Server Import and Export Wizard shortly after the window to select your data source I get a pop-up error message:
This wizard will close because it encountered the following error:
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%u00ae+Visual+Studio%u00ae+2005&ProdVer=8.0.50727.42&EvtSrc=Microsoft.SqlServer.Management.UI.WizardFrameworkErrorSR&EvtID=UncaughtException&LinkId=20476
------------------------------
ADDITIONAL INFORMATION:
QI for IEnumVARIANT failed on the unmanaged server. (CustomMarshalers)
Anyone have any ideas on how I can resolve this?
Since a couple days, we are getting this message the errorlog of one of our SQL2012 server
LogEntry: Error [36, 17, 145] occurred while attempting to drop allocation unit ID 451879652360192 belonging to worktable with partition ID 451879652360192.
(version Microsoft SQL Server 2012 - 11.0.5058.0 (X64))
I am wondering what is the best way trying to troubleshoot this issues? I do not know from which of out database this is coming.
Hi all!
I´m having some trouble with a SSIS package that I´ve been using five months or so now.
I have a package scheduled to run every 2 minutes to load data into my SQL Server instance
from our mobile computer systems. This data comes is read as flat file sources. My package runs 24/7.
My problem is that this package randomly returns "An error occurred while attempting to perform a type cast". This message is then returned during maybe 3 or 4 executions of the package. The error then vanishes into thin air, without me doing anything what so ever, and execution continues. The very same file that previously returned the errors is now processed without any problems.
I have no clue why this is, but I get the feeling that there has to be a reason for these random errors. To me, the type cast error seems to indicate problems with the data source (the flat file), but since the same file suddenly get processed without errors I have no clue where to start looking.
Anyone having the same issue?
Regards,
Daniel
I'm working through the SQL2005 Reporting Services Tutorial. When I try to deploy, I get the error "Connection could not be made to the report server." It appears that I do not have permissions to get to localhost/reportserver (I get a permissions error when just trying to get there directly from "run"). In another forum I saw several people having the same issue. One contributor said that the problem was solved for him by uninstalling SQL2005 and reinstalling using the local system account rather than a windows domain account. I tried that, but am getting the same error. Anyone know how to fix this?
I receive the following error when trying to add a local database to my project.
"The data provider required to connect to the local data file could not found. The file will be added to the project but the typed DataSet associated with the file will not be generated."
I've reinstalled VS2k8, SQL Compact 3.1 and SQL Compact 3.5 on my machine and still receive the error.
Does anyone have any suggestions? I saw another similar thread about this in the VB2k8Express forum, but there were no helpful answers there. (for the curious: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2769854&SiteID=1)
I haven't used the SSCE before and so it's possible I've mistakenly omitted some required items.
Any help is greatly appreciated.
I have two string variables each has Varchar(8000)
Declare @VariableA varchar(8000)
Declare @VariableB varchar(8000)
How can I concatenate these two variables without converting them to varchar(max) ?
If try select @VariableA + @VariableB , I only got 7999 characters…
I am attempting to run an SSIS package that, among other things, imports a spreadsheet from excel into a database table. The package runs without any issues within Visual Studio. I have tried executing the package through both, the MSDB run package and through dtexec (trying to kick of the package through a stored procedure) and I get 2 different behaviors.
Using dtexec (the method I really need to use): The package will run successfully...up to the point when the spreadsheet is imported at which time it fails with Description: The AcquireConnection method call to the connection manager "Excel Connection Manager" failed with error code 0xC0202009. Here is the code:
exec xp_cmdshell 'dtexec /sq PopulateTRTLStationandtRTLUnitMapping /ser SERVERNAME
Running it through the MSDB Run Package UI...It will also make it up to the point where the Excel spreadsheet is imported but errors with: The Product level is insufficient for the component "Lookup Station and Account Type: (1894) ...and 1 line with that same error for every single task in that dataflow. Here is the code it runs.
/DTS "MSDBPopulateTRTLStationandtRTLUnitMapping" /SERVER "SERVERNAME" /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING V
The machine is running 32 bit OS Windows Server 2003 SP1 and Db SQL Server 2005 32 bit. I found one forum posting that suggested turning the Delay Validation property to True...but that did not fix the issue. I did create the package with my username with a ProtectionLevel of EncryptSensitiveWithUserKey. I don't think it is related to the account however because all of the tasks (serveral work tables are created) up to the Excel import will execute.
I really need to get this working as soon as possible so am open to any solutions someone can present.
When attempting to save an SSIS package in Visual Studion I receive the following error message detailed below. If I attempt to "Save As" to another location, I then receive an insufficient storage error. The development machine has over 1.5 GB of available physical memory and several GB of disk space availabe to save my 16 MB package. I have checked the event log and have found no related messages in the Application or Server logs.
Any suggestions on how to determine the cause or resolution of this error message would be greatly appreciated.
Failure saving package. (Microsoft Visual Studio)
Insufficient memory to continue the execution of the program. (Microsoft.SqlServer.ManagedDTS)
Advanced Error Message Details
Failure saving package. (Microsoft Visual Studio)
------------------------------
Program Location:
at Microsoft.DataTransformationServices.Design.Serialization.DtrDesignerSerializer.SerializePackage(IDesignerSerializationManager manager, Package package, TextWriter textWriter)
at Microsoft.DataTransformationServices.Design.Serialization.DtrDesignerSerializer.SerializeComponent(IDesignerSerializationManager manager, IComponent component, Object serializationStream)
at Microsoft.DataWarehouse.Serialization.DesignerComponentSerializer.Serialize(IDesignerSerializationManager manager, Object value)
at Microsoft.DataWarehouse.VsIntegration.Designer.Serialization.DataWarehouseDesignerLoader.Serialize()
at Microsoft.DataWarehouse.VsIntegration.Designer.Serialization.BaseDesignerLoader.Flush(Boolean forceful)
at Microsoft.DataWarehouse.VsIntegration.Designer.Serialization.BaseDesignerLoader.Flush()
at Microsoft.DataWarehouse.VsIntegration.Designer.Serialization.DataWarehouseContainerManager.OnBeforeSave(UInt32 docCookie)
===================================
Insufficient memory to continue the execution of the program. (Microsoft.SqlServer.ManagedDTS)
------------------------------
Program Location:
at Microsoft.SqlServer.Dts.Runtime.Package.SaveToXML(String& packageXml, IDTSEvents events)
at Microsoft.DataTransformationServices.Design.Serialization.DtrDesignerSerializer.SerializePackage(IDesignerSerializationManager manager, Package package, TextWriter textWriter)
Please... any ideas? Is this a footprint config issue?
TITLE: Microsoft Visual Studio
There was an error displaying the preview.
ADDITIONAL INFORMATION:
Could not load file or assembly
'Microsoft.SqlServer.SQLTaskConnectionsWrap, Version=9.3.242.0,
Culture=neutral, PublicKeyToken=89845dcd8080cc91' or one of its
dependencies. The system cannot find the file specified.
(Microsoft.DataTransformationServices.Design)
BUTTONS:
OK
===================================
There was an error displaying the preview. (Microsoft Visual Studio)
===================================
Could not load file or assembly
'Microsoft.SqlServer.SQLTaskConnectionsWrap, Version=9.3.242.0,
Culture=neutral, PublicKeyToken=89845dcd8080cc91' or one of its
dependencies. The system cannot find the file specified.
(Microsoft.DataTransformationServices.Design)
------------------------------
Program Location:
at
Microsoft.DataTransformationServices.Design.PipelineUtils.ShowDataPreview(String
sqlStatement, ConnectionManager connectionManager, Control
parentWindow, IServiceProvider serviceProvider,
IDTSExternalMetadataColumnCollection90 externalColumns)
at
Microsoft.DataTransformationServices.DataFlowUI.DataFlowConnectionPage.previewButton_Click(Object
sender, EventArgs e)
Hi all:
We currently have a production server (prodserver) and backup server (backupserver) in the same domain, and I am attempting to backup dbs from the production box on to the backup box using the following:
backup database db to disk = '\backupservere$ackupdb.bak' with init
when executing from SSMS, I receive the following error:
Msg 3201, Level 16, State 1, Line 1
Cannot open backup device '\backupservere$ackupdb.bak' Operating system error 1326 (Logon failure: unknown user name or bad password)
Here's where the issue lies for me:
Both the production and backup box are running services under the same domain accounts, let's call it "domainsqlagent"
domainsqlagent has domain admin rights on backupserver and prodserver
I can login to both the production and backup servers using the domain account, map drives, hit administrative shares, drag and drop files, etc between the servers
If I use xp_cmdshell to try and simply move a file between prodserver and backupserver, I receive the above error as well. However, if I open an cmd window and execute the move command from DOS, it will successfully move.
There is a development server on the domain as well (devserver) using domainsqlagent to run the service accounts, and I can successfully backup databases from dev to prod, dev to backup, backup to dev, backup to prod, prod to dev, but NOT prod to backup
Finally, if I create a maintenance plan in SSMS, I cannot create a connection to the backup server (error reads the connection may not be configured correctly or you may not have the right permissions on this connection)
The backup server was changed to a different domain (domain2) before I arrived here, but has been switched back now, rebooted, service accounts changed via Configuration Manager, services restarted (and rebooted again for good measure).
Does SSMS use a different security context to create the connections to remote servers? From all my tests, the domainsqlagent account has all the correct rights and is working OK. Could this be a network issue? The network admin and I have been banging our heads against a wall for the past couple of days, so if anyone has seen something like this, I'd appreciate any help.
Thanks,
Craig
When attemping to install SQL 2005 Express using the following command line arguments
/qb UPGRADE=SQL_Engine INSTANCENAME=MY_INSTANCE
or
/qb UPGRADE=SQL_Engine INSTANCENAME=MY_INSTANCE SECURITYMODE=SQL SAPWD=MY_PASSWORD
the following error occurs.
Your upgrade is blocked. For more information about upgrade support, see the "Version and Edition Upgrades" and "Hardware and Software Requirements" topics in SQL Server 2005 Setup Help or SQL Server 2005 Books Online.
Edition check:
Your upgrade is blocked because of edition upgrade rules. For more information about edition upgrades, see the Version and Edition Upgrades topic in SQL Server 2005 Setup Help or SQL Server 2005 Books Online.
The following version and editions have been verified.
1. .NET 2.0 installed
2. Windows XP SP2
3. MSDE 8.00.2039(SP4)
4. all MSDE databases are owned by sa
5. Instance and SQLAgent running under user that is member of Administrators
What are the possible reasons this error is occurring?
I have used the copy database wizard, but I realized I had forgotten to shrink the transaction log file. So I canceled the wizard. My database, detached by the wizard, has now disappeared. The mdf file is still there, but when I try to attach it manually I get the "create file encountered operating system error 5 while attempting to open the physical file..." error.
Any way I can recover it?
Thanks.
I have the following stored procedure to test scope of variables
alter proc updatePrereq
@pcntr int,
@pmax int
as
begin
[Code] ....
In the above script @i is declare in the if block only when the @pcntr value is 1. Assume the above stored procedure is called 5 times from this script
declare @z int
set @z = 1
declare @max int
set @max = 5
while @z <= @max
begin
exec dbo.updatePrereq @z, @max
set @z = @z + 1
end
go
As i said earlier `@i` variable exists only when `@pcntr` is `1`. Therefore when i call the stored procedure for the second time and so forth the control cannot enter the if block therefore @i variable wouldn't even exist. But the script prints the value in `@i` in each iteration, How comes this is possible should it throw an error saying `@i` variable does not exist when `@pcntr` values is greater than `1`?
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
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 '.'.
Sorry seem to be havign no end of trouble with SQL today. I need to create a query that will rank tickets using the row count of another table which has a relationship with the primarykey of that ticket table. I decided to spilt it into two querys, one which gets the count each ticketid has within a date peroid and relating to a os:: SELECT COUNT(*) AS count, Tickets.TicketID, Systems.OSFROM Rating INNER JOIN Tickets ON Rating.TicketID = Tickets.TicketID INNER JOIN Systems ON Tickets.SystemID = Systems.SystemIDWHERE (Rating.DateVoted >= @date)GROUP BY Tickets.TicketID, Systems.OSHAVING (Systems.OS = @osid) This works, BUT when I put in the date using: command.CommandText = "SELECT COUNT(*) AS count, Tickets.TicketID, Systems.OS FROM Rating INNER JOIN Tickets ON Rating.TicketID = Tickets.TicketID INNER JOIN Systems ON Tickets.SystemID = Systems.SystemID WHERE (Rating.DateVoted >=" + Datetime.Now.Adddays(-7) + ") GROUP BY Tickets.TicketID, Systems.OS HAVING (Systems.OS =" + 1 + ")"; And it throws up an error saying there was a syntax error at '22' (the hour I am currently on). So: Any idea how I can fix thisAny ideas on creating a query that will do the above, but also return the ticket informationHow do you link querys (I might have to do this if I can't work out 2)Please, If anyone has any ideas. This is driving me crazy
View 5 Replies View RelatedI am trying to run a query where my Table and my request are variables(Will be used in Stored Procedure)Declare @EmailVARCHAR(100)Declare @TableVARCHAR(50)Declare @Count VARCHAR(8)DECLARE @cmd VARCHAR(500)set @Table = 'tblManager'set @Email = 'cauer@tampabay.rr.com'Set @cmd = 'Select count(*) as Count from ' + @Table + ' WHERE Email= ' + @Emailexec(@cmd)The error I get isServer: Msg 207, Level 16, State 3, Line 7Invalid column name 'cauer@tampabay.rr.com'.I have worked on this for a while, any help would be greatlyappreciated.Chris Auer
View 2 Replies View RelatedHi,
I have encountered some strange output when testing an SSIS package I've been working on.
Please see this thread for background information: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1590716&SiteID=1. The previous thread was resolved and i have used the tips listed here to create a package with Data Flow Tasks that use expressions to create 'dynamic' queries.
The Data Flow tasks use two package-scope variables to determine the date range that they're operating on - this allows me to script the from / to dates (usually 'last week' range) or, in event of error, 'back date' the job easily without having to update each individual components.
However, something weird is happening - when I have two data flow tasks (DFT) in the same Sequence Container (tasks that are not 'connected' or configured to operate in a flow) only one DFT is picking up the package variable & returning the rows. The other DFT is not returning any rows & seems to be using the default value of the variables.
I have tested this by simply enabling DFT A and disabling DFT B - debugging shows only one DFT executes correctly. The same point is illustrated when I disable DFT A and enable DFT B. However, if I configure DFT A to run after DFT B then both pick up the correct variable values and return the correct row counts!!
I want Data Flow Tasks that pull data from the same remote source to be the same sequence containers so when an error occurs I can get container level transaction support.
Note:
I have thoroughly checked the 'evaluated' expressions in IDE and in Management Studio - this is an SSIS specific quirk (or me!).
Questions:
What's happening here, have I made a conceptual error of how this should work?
On the same variable topic: is there a way to set values for package level variables at execute time .e.g via command line execution / dtsexecui??
Let me know if you need any extra explaination / diagrams etc to understand this, the other (closed) thread I have listed above should provide more context for my problem
Thanks for your support!!
Hi All,
Been doing some testing with SQLCMD and variables for installation scripts, enclosed below is some source code.
The one problem, I've been getting is the following error:
Incorrect syntax near 'certification'
This has been tested only in SSMS utilising the run in SQLCMD mode.
This error only occurs on the SQLCMD variable replacement, where it replaces $(Cert_Schema) for the word Certification.
Running the CREATE Synonym with the word Certification hard-coded into the script works.
Any ideas?
Microsoft SQL Server 2005 - 9.00.3054.00 (Intel X86) Mar 23 2007 16:28:52 Copyright (c) 1988-2005 Microsoft Corporation Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 2)
Best Regards,
D
USE [Testing]
GO
/****** Object: Table [certification].[Table_1] Script Date: 01/03/2008 11:22:48 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [certification].[Table_1](
[PKId] [smallint] NOT NULL,
[Name] [nchar](10) NULL,
CONSTRAINT [PK_Table_1] PRIMARY KEY CLUSTERED
(
[PKId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
--- SQLCMD Script below:
-- line below should read, colon setvar cert_schema 'certification'
etvar Cert_Schema 'certification'
-- The line below fails to work for some reason
CREATE SYNONYM crt_asa FOR $(Cert_Schema).Table_1
DROP SYNONYM crt_asa
-- Hard-coded equivilant line works
CREATE SYNONYM crt_asa FOR certification.Table_1
DROP SYNONYM crt_asa