I want to get the list of names starting with a particular letter.this letter should be passed as parameter in the query.
I have used the following query,
Select Cust_Code,Cust_Name,Cust_Add1 + Cust_Add2 as Address From Customer Where Cust_Code Like @Cust_Code Order By Cust_Name ASC
I am not sure whether the above query is correct.
Has any of you ever had problems with the ROUND command?
I have this operation:
ROUND((FIELD1 / 360 * FIELD5),3) AS FIELD7
(I want the result to be rounded at the third decimal).
SQL makes mistakes in rounding. It seems to me that
the problem relies in the quantity of decimals taken
into account in any single step of the operation.
Thank you in advance.
Anna - Verona (Italy)
Hi all,
I have this store procedure as follows:
Create Proc UpdateProblem@ProblemID int,@CompanyName varchar (50),@Firstname varchar (50),@Lastname Varchar (50),@Address varchar (50),@Postcode varchar (50),@City varchar (50),@Phone varchar (50),@Cutype varchar (50),@ProDescript varchar (50),@Sol varchar (50),@Email varchar (50)
as Update Problemset CompanyName = @CompanyName,Firstname = @Firstname,Lastname = @Lastname,Address = @Address,PostCode = @Postcode,City = @City,Phone = @Phone,Cutype = @Cutype,ProDescript = @ProDescript,Sol = @Sol,Email = @Email
where ProblemID = @ProblemID
when I test the querry
exec UpdateProblem10004, 'Toro AS','Mike','Tullas','Togo Street','G34 5TT','New York','06582531','Private','Machine is dead','Replace motherboard','goo@ht.com'
what happen is that when I ran the querry instead of updating the specifc row of 1004 the querry will just update the whole rows in the table with the same data.
Please help. I have set the ProblemID as the Primary key.
I often run queries in Query analyzer. Today I accidentaly deleted some data from a table. Is their a way to undo the last SQL command executed?I have heard of the rollback command, but I don't know if that is applicable or not?
View 6 Replies View Relatedhow to get the quantity from output of sql query command for example, I could get the container name by below command
select container_name from sysibmadm.snapcontainer
get the container number by
select TBSP_NUM_CONTAINERS from SYSIBMADM.SNAPTBSP_PART
now I want to get the container number by below command output result select container_name from sysibmadm.snapcontainer..so what more command should I add on above command, I mean I want to get the container number by container name from the output of above command, not by 'join'.
Hi all,
I have a script to bulk copy data from my table into textfile. This bcp script will be executed by xp_cmdshell. When I try to execute the scipt inside query windows it returned an error : Error = [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified. But when I test the bcp script in command prompt it will execute successfully
Is there any misconfiguration ? (I'm using sqlexpress) thanks in advance.
Best regards,
Hery
sorry if when i had one table result like this ,
select changeprice,usetype from orderchange
changeprice usetype
-------------- ----------
450 a
650 b
650 c
how the sql query command i can use to be select result like this in one column line.
450a 650b 650c
best regard
I was wondering if there was a command in SQL that you could enter that would stop the execution of a query at a specified spot.
For instance if you were debugging what a batch of update queries were doing to a table and you wanted to stop it at a specific point. I realize you can select the code you want to run, but I would prefer to enter something at a point that stays whether I am selecting it or not. That way when I am looking at a 1000 lines of code I don't have to remember everytime how much to select .
So are there any commands like that? I was thinking something like 'Stop' in VBA but that doesn't work
Dear Friends,
I have an OLE DB Command inside an ETL. I want to execute a stored procedure that returns 2 fields, for example:
CREATE PROCEDURE sp_Name
AS
SELECT Field1, Field2 FROM Table
How can I continue the dataflow? How can I return the values from the query? Returns only a row...
Do you know of a way, in the OLE DB Command (Executing a sql command for each record), to specify a lock hint?
Example: in my component, I want to update a table but I want to specify a ROWLOCK or UPDLOCK hint.
When I try using this sql statement in the 'SqlCommand' property:
UPDATE DIM_Accounts WITH (UPDLOCK)
SET COL1 = ''
SSIS gives a syntax error.
Do you know any workarounds?
Thanks!
How do I execute below scripts using CMD:
Please Note: SQL server 2008r2, default instance with window Authentication.
1. Select * from Adventurewoks2008.Person.Person where Id = 5
2. Exec usp_getemployee (with parameter Manager_id = 100)
3. I have a folder named 'MyFolder' which contains 3 store procedures (usp_A, usp_B and usp_C).
How do I execute all 3 using CMD against AdventureWorks database.
How I can get the desired result using query. I don't want any
'@' variable in the result.
DECLARE @ST AS varchar(10)='AA'
DECLARE @desc AS int=8
DECLARE @STID AS int=4
DECLARE @PP AS int=63
DECLARE @SS AS int=22
/* Desired Result */
Update #RT Set ST='AA', desc=8, STID=4 Where PP=63 and SS=22
I have a dts that works from the command prompt, but hangs in Query Analyzer. Here's the code inside Query Analyzer:exec master..xp_cmdshell 'dtsrun /S BFHSQL4 /N vhl_dts_14144b /E'From the command prompt, it's just:dtsrun /S BFHSQL4 /N vhl_dts_14144b /EAny ideas what's wrong? Thanks for your help!
View 3 Replies View RelatedWhen my colleague makes any change to a query containing a derived table, the word 'WITH' followed by a lot of graphical characters appears after the alias, rendering the query unusable. We have tried this with various existing and new, simple queries, all to no avail. We are editing the queries in Visual Studio 2003, and he does not get this effect when using Visual Studio 2005. I can edit the query in 2003 on my laptop with no ill effects.
Help!
Karen
I would like to be able to view the complete SQL command that is sent from a Microsoft Access query which has linked SQL Server tables through ODBC, and the query also has some VBA functions manipulating and filtering data.I tried using the SQL Profiler to view the sql command send to SQL Server but all I see is the simple query format and some of the fields. I don't see the fields that have a VBA function such as trim(fieldname) etc in the column. And If I use a VBA fucntion in the Criteria then nothing shows up in SQL Profiled under the TextData column.How can I see the complete SQL command sent to SQL Server?
View 2 Replies View RelatedI need help understaning the first line of code in my sql query below. SELECT @PrimeRate = isnull(max(CredentialValue),0)
I don't have a clue as to what they are trying to do there. Can someone help me out?
SELECT @PrimeRate = isnull(max(CredentialValue),0)FROM Credential, CredentialType, #LocationsWHERE Credential.ResourceId = @PortalIdAND Credential.OfficeId = #Locations.OfficeIdAND Credential.CredentialTypeId = CredentialType.CredentialTypeId
i am using visual web developer 2005 and SQL 2005 with VB as the code behindi am using INSERT command like this Dim test As New SqlDataSource() test.ConnectionString = ConfigurationManager.ConnectionStrings("DatabaseConnectionString1").ToString() test.InsertCommandType = SqlDataSourceCommandType.Text test.InsertCommand = "INSERT INTO try (roll,name, age, email) VALUES (@roll,@name, @age, @email) " test.InsertParameters.Add("roll", TextBox1.Text) test.InsertParameters.Add("name", TextBox2.Text) test.InsertParameters.Add("age", TextBox3.Text) test.InsertParameters.Add("email", TextBox4.Text) test.Insert() i am using UPDATE command like this Dim test As New SqlDataSource() test.ConnectionString = ConfigurationManager.ConnectionStrings("DatabaseConnectionString").ToString() test.UpdateCommandType = SqlDataSourceCommandType.Text test.UpdateCommand = "UPDATE try SET name = '" + myname + "' , age = '" + myage + "' , email = '" + myemail + "' WHERE roll 123 " test.Update()but i have to use the SELECT command like this which is completely different from INSERT and UPDATE commands Dim tblData As New Data.DataTable() Dim conn As New Data.SqlClient.SqlConnection("Data Source=.SQLEXPRESS;AttachDbFilename=|DataDirectory|Database.mdf;Integrated Security=True;User Instance=True") Dim Command As New Data.SqlClient.SqlCommand("SELECT * FROM try WHERE age = '100' ", conn) Dim da As New Data.SqlClient.SqlDataAdapter(Command) da.Fill(tblData) conn.Close() TextBox4.Text = tblData.Rows(1).Item("name").ToString() TextBox5.Text = tblData.Rows(1).Item("age").ToString() TextBox6.Text = tblData.Rows(1).Item("email").ToString() for INSERT and UPDATE commands defining the command,commandtype and connectionstring is samebut for the SELECT command it is completely different. why ?can i define the command,commandtype and connectionstring for SELECT command similar to INSERT and UPDATE ?if its possible how to do ?please help me
View 2 Replies View RelatedHi All,
i am using a OLE DB Source in my dataflow component and want to select rows from the source based on the Name I enter during execution time. I have created two variables,
enterName - String packageLevel (will store the name I enter)
myVar - String packageLevel. (to store the query)
I am assigning this query to the myVar variable, "Select * from db.Users where (UsrName = " + @[User::enterName] + " )"
Now in the OLE Db source, I have selected as Sql Command from Variable, and I am getting the variable, enterName,. I select that and when I click on OK am getting this error.
Error at Data Flow Task [OLE DB Source [1]]: An OLE DB error has occurred. Error code: 0x80040E0C.
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E0C Description: "Command text was not set for the command object.".
Can Someone guide me whr am going wrong?
myVar variable, i have set the ExecuteAsExpression Property to true too.
Please let me know where am going wrong?
Thanks in advance.
Do somebody know how long (in chars) script(command) can be solved by SQL Command?
Thanks
reply.
View 1 Replies View RelatedHi. I am writing a program in C# to migrate data from a Foxpro database to an SQL Server 2005 Express database. The package is being created programmatically. I am creating a separate data flow for each Foxpro table. It seems to be doing it ok but I am getting the following error message at the package validation stage:
Description: An OLE DB Error has occured. Error code: 0x80040E0C.
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E0C Description: "Command text was not set for the command object".
.........
Description: "component "OLE DB Destination" (22)" failed validation and returned validation status "VS_ISBROKEN".
This is the first time I am writing such code and I there must be something I am not doing correct but can't seem to figure it out. Any help will be highly appreciated. My code is as below:
private bool BuildPackage()
{
// Create the package object
oPackage = new Package();
// Create connections for the Foxpro and SQL Server data
Connections oPkgConns = oPackage.Connections;
// Foxpro Connection
ConnectionManager oFoxConn = oPkgConns.Add("OLEDB");
oFoxConn.ConnectionString = sSourceConnString; // Created elsewhere
oFoxConn.Name = "SourceConnectionOLEDB";
oFoxConn.Description = "OLEDB Connection For Foxpro Database";
// SQL Server Connection
ConnectionManager oSQLConn = oPkgConns.Add("OLEDB");
oSQLConn.ConnectionString = sTargetConnString; // Created elsewhere
oSQLConn.Name = "DestinationConnectionOLEDB";
oSQLConn.Description = "OLEDB Connection For SQL Server Database";
// Add Prepare SQL Task
Executable exSQLTask = oPackage.Executables.Add("STOCK:SQLTask");
TaskHost thSQLTask = exSQLTask as TaskHost;
thSQLTask.Properties["Connection"].SetValue(thSQLTask, "oSQLConn");
thSQLTask.Properties["DelayValidation"].SetValue(thSQLTask, true);
thSQLTask.Properties["ResultSetType"].SetValue(thSQLTask, ResultSetType.ResultSetType_None);
thSQLTask.Properties["SqlStatementSource"].SetValue(thSQLTask, @"C:LPFMigrateLPF_Script.sql");
thSQLTask.Properties["SqlStatementSourceType"].SetValue(thSQLTask, SqlStatementSourceType.FileConnection);
thSQLTask.FailPackageOnFailure = true;
// Add Data Flow Tasks. Create a separate task for each table.
// Get a list of tables from the source folder
arFiles = Directory.GetFileSystemEntries(sLPFDataFolder, "*.DBF");
for (iCount = 0; iCount <= arFiles.GetUpperBound(0); iCount++)
{
// Get the name of the file from the array
sDataFile = Path.GetFileName(arFiles[iCount].ToString());
sDataFile = sDataFile.Substring(0, sDataFile.Length - 4);
oDataFlow = ((TaskHost)oPackage.Executables.Add("DTS.Pipeline.1")).InnerObject as MainPipe;
oDataFlow.AutoGenerateIDForNewObjects = true;
// Create the source component
IDTSComponentMetaData90 oSource = oDataFlow.ComponentMetaDataCollection.New();
oSource.Name = (sDataFile + "Src");
oSource.ComponentClassID = "DTSAdapter.OLEDBSource.1";
// Get the design time instance of the component and initialize the component
CManagedComponentWrapper srcDesignTime = oSource.Instantiate();
srcDesignTime.ProvideComponentProperties();
// Add the connection manager
if (oSource.RuntimeConnectionCollection.Count > 0)
{
oSource.RuntimeConnectionCollection[0].ConnectionManagerID = oFoxConn.ID;
oSource.RuntimeConnectionCollection[0].ConnectionManager = DtsConvert.ToConnectionManager90(oFoxConn);
}
// Set Custom Properties
srcDesignTime.SetComponentProperty("AccessMode", 0);
srcDesignTime.SetComponentProperty("AlwaysUseDefaultCodePage", true);
srcDesignTime.SetComponentProperty("OpenRowset", sDataFile);
// Re-initialize metadata
srcDesignTime.AcquireConnections(null);
srcDesignTime.ReinitializeMetaData();
srcDesignTime.ReleaseConnections();
// Create Destination component
IDTSComponentMetaData90 oDestination = oDataFlow.ComponentMetaDataCollection.New();
oDestination.Name = (sDataFile + "Dest");
oDestination.ComponentClassID = "DTSAdapter.OLEDBDestination.1";
// Get the design time instance of the component and initialize the component
CManagedComponentWrapper destDesignTime = oDestination.Instantiate();
destDesignTime.ProvideComponentProperties();
// Add the connection manager
if (oDestination.RuntimeConnectionCollection.Count > 0)
{
oDestination.RuntimeConnectionCollection[0].ConnectionManagerID = oSQLConn.ID;
oDestination.RuntimeConnectionCollection[0].ConnectionManager = DtsConvert.ToConnectionManager90(oSQLConn);
}
// Set custom properties
destDesignTime.SetComponentProperty("AccessMode", 2);
destDesignTime.SetComponentProperty("AlwaysUseDefaultCodePage", false);
destDesignTime.SetComponentProperty("OpenRowset", "[dbo].[" + sDataFile + "]");
// Create the path to link the source and destination components of the dataflow
IDTSPath90 dfPath = oDataFlow.PathCollection.New();
dfPath.AttachPathAndPropagateNotifications(oSource.OutputCollection[0], oDestination.InputCollection[0]);
// Iterate through the inputs of the component.
foreach (IDTSInput90 input in oDestination.InputCollection)
{
// Get the virtual input column collection
IDTSVirtualInput90 vInput = input.GetVirtualInput();
// Iterate through the column collection
foreach (IDTSVirtualInputColumn90 vColumn in vInput.VirtualInputColumnCollection)
{
// Call the SetUsageType method of the design time instance of the component.
destDesignTime.SetUsageType(input.ID, vInput, vColumn.LineageID, DTSUsageType.UT_READWRITE);
}
//Map external metadata to the inputcolumn
foreach (IDTSInputColumn90 inputColumn in input.InputColumnCollection)
{
IDTSExternalMetadataColumn90 externalColumn = input.ExternalMetadataColumnCollection.New();
externalColumn.Name = inputColumn.Name;
externalColumn.Precision = inputColumn.Precision;
externalColumn.Length = inputColumn.Length;
externalColumn.DataType = inputColumn.DataType;
externalColumn.Scale = inputColumn.Scale;
// Map the external column to the input column.
inputColumn.ExternalMetadataColumnID = externalColumn.ID;
}
}
}
// Add precedence constraints to the package executables
PrecedenceConstraint pcTasks = oPackage.PrecedenceConstraints.Add((Executable)thSQLTask, oPackage.Executables[0]);
pcTasks.Value = DTSExecResult.Success;
for (iCount = 1; iCount <= (oPackage.Executables.Count - 1); iCount++)
{
pcTasks = oPackage.PrecedenceConstraints.Add(oPackage.Executables[iCount - 1], oPackage.Executables[iCount]);
pcTasks.Value = DTSExecResult.Success;
}
// Validate the package
DTSExecResult eResult = oPackage.Validate(oPkgConns, null, null, null);
// Check if the package was successfully executed
if (eResult.Equals(DTSExecResult.Canceled) || eResult.Equals(DTSExecResult.Failure))
{
string sErrorMessage = "";
foreach (DtsError pkgError in oPackage.Errors)
{
sErrorMessage = sErrorMessage + "Description: " + pkgError.Description + "";
sErrorMessage = sErrorMessage + "HelpContext: " + pkgError.HelpContext + "";
sErrorMessage = sErrorMessage + "HelpFile: " + pkgError.HelpFile + "";
sErrorMessage = sErrorMessage + "IDOfInterfaceWithError: " + pkgError.IDOfInterfaceWithError + "";
sErrorMessage = sErrorMessage + "Source: " + pkgError.Source + "";
sErrorMessage = sErrorMessage + "Subcomponent: " + pkgError.SubComponent + "";
sErrorMessage = sErrorMessage + "Timestamp: " + pkgError.TimeStamp + "";
sErrorMessage = sErrorMessage + "ErrorCode: " + pkgError.ErrorCode;
}
MessageBox.Show("The DTS package was not built successfully because of the following error(s):" + sErrorMessage, "Package Builder", MessageBoxButtons.OK, MessageBoxIcon.Information);
return false;
}
// return a successful result
return true;
}
Hi,I'm using the sql TOP command to retrieve the top N number of results where N is a value passed into the stored proc...eg: select TOP(@N) table.*from table...etc..if @N is not passed into the stored proc then by default i want it to select every row from the table. e.g to achieve something similar to...select table.*from table...how can i do this with with as few lines of code possible? thanks!
View 4 Replies View RelatedHello, i have this sql command:
sqlcommand2.CommandText = "Select Count(UserIP) From InboundTraffic Where InboundURL Contains('" & SiteDomain(i).ToString & "') and DateTimeReceived > #" & Last30Days & "#"
My problem is that it is counting every field in the coulmn UserIp even though every field under Inboundurl currently contains 'a' and SiteDomain has a value of something like google.com. Should it not be returning nothing? Thanks!
Okay I have a column
Red
Red
Blue
Yellow
Blue
Blue
Blue
Blue
I want to return the value that appears most i.e. in this case Blue.
Thanks
Ben
Hi!
Suppose a company has ten branches and a total employees of 10,000 ones. At the employees' table, how may I calculate the difference between every employee's salary to the average salary of own branch and write to the other field of table, just with a SQL Command?
Employee:
ID | Branch | Salary | DifferenceToAverage
Regards,
M.Sadegh Samiei
There are 2 relevant fields in the table: SystemID & Description
For anything w/a SystemID of 1001, I want to add "ABC-" in FRONT of the description.
For example: If The description was XYZ and the systemID 1001, i want it to change to: ABC-XYZ
Thank you
I am using the bcp command to export a bulk text file into the database,
bcp elearning.dbo.BulkData in mobile.txt -c -t, -SZOHL-02 -Usa -P1234567890 -E
I have 6 fields in the table to which i am exporting data.One field is numeric and i have to set the identity to yes,It gives me an error string data trucncated. When i remove the identity field, i am able to export data.So, how do i tackle this prob?I used the -E attribute to keep the identity .But still i get the error. The text file has comma seperated fields.I am using sql server 2000
Hello, i'm a junior progammer,
I must use the BCP command for create a file that is needed to be used by another program.I have my template to use EX.:
ASKOFE00001ASQSQOPSAZ000123324AAJISQ
ASDAJDIOW78708AMXOPSAJSMA565876979AA
I've tried but my result was 1 line whith ascii character.
Please help me.
Massimo Nardi
Hi !
Does anyone know the sql-statement to check the actual length in a varchar2 columns.
For example in Oralce you can do this
select length(column_name) from table;
I want to check that a program hasn't been wriiten the whole column with spaces.
Does anyone know if there is any possibility to create a copy of a table in the database something like:
create table table_copy as select * from table;
I have found the backup table tool kind of unreliable !
Hi all,
I am trying to run a .bat file with this bcp command.
BCP "database.dbo.state" OUT "C:TEMPstate.dat" -SServerName -U"userid" -P"password" -m1 -n -a65536 -E -q
However, it is not producing me a file as I expected.
Is there any other configuration I need to set before it work?
Any help would appreciated.
im using bcp to export data from MyTable to MyTable.bcp file
the command is in a batchfile, ExportData.bat which i invoke from the command prompt.
ExportData.bat:
bcp MyDB.Dbo.MyTable out MyTable.bcp -N -U<user> -P<password> -S<Server>
is there any method to retrieve the number of rows exported by bcp ommand?
(apart from the messages printed in the command prompt)
what is the command to check whether the sql server is running 32 bit or 64 bit ?
View 4 Replies View Related