Dear Expert,
How can I reuse the column, instead of select the whole things again.
Example as below :
select
column1 as A,
column2 as B,
column3 * A as C
from dummy ;
in MS SQL I have to do like this,
select
column1 as A,
column2 as B,
column3 * column1 as C
from dummy ;
Thanks
Desmond
I am new comer to SQL server. I have one question: if you have some tables need need be reviewed (maybe use "select")website visitors, what is the best way to set the permission so that the visitors can only read from the table, not have the right to change it?
thanks for any kind information.
Dsz
NOTE: I am using MS-SQL 2K
I have a SQL script that needs to change from a particular DB to the Master DB (to grant EXECUTE permissions to a few SPs in the Master DB), then change back to the original DB. I will not know at design time what the DB name is.
What I am trying to do is something like this:
Use Master
GO
Grant EXECUTE on sp_OACreate TO C2SRolePowerUsers
GO
Declare @DBName varchar(32)
EXECUTE xp_instance_regread 'HKEY_LOCAL_MACHINE', 'SOFTWAREODBCODBC.iniMyDSNName', 'Database', @param = @DBName OUTPUT
Use @DBName
GO
The problems:
1) I have to do this in a single pass using ADO and the script. The reason is that it is part of a generic routine, looping through a list off commands, and cannot perform one command, change the DB reference via ADO, then change back in separate ADO commands.
2) It fails on the second USE command.
I have looked to see if there is some sort of "EVAL()" function, or a stored procedure to change the current DB. No luck.
Any suggestions?
Thanks,
__________________
Bob Rouse
Comsquared, Inc.
Can u briefly explain in which cases decimal,numeric,money datatypes is used
any difference between them and explain what is the precision and scale for those datatypes
and can u increase the precision and scale in money datatype
thank in advance
I'm doing a shopping cart using SQL Express and Visual Studio Web Developer on C#, ASP.NET
I recieved error when adding a order:
The variable name '@oid' has already been declared. Variable names must be unique within a query batch or stored procedure.
The codes are:
comm = new SqlCommand("SELECT IDENT_CURRENT('Orders') as NewOrderID ");
comm.Connection = conn;
comm.Transaction = myTrans;
OrderID = Convert.ToInt32(comm.ExecuteScalar());
foreach (CartItem i in o.ItemList)
{
comm.CommandText = "INSERT INTO OrderDetail(OrderID, ProductID, Quantity, UnitPrice)VALUES (@oid, @pid, @qty, @price)";
comm.Parameters.AddWithValue("@oid", OrderID);
comm.Parameters.AddWithValue("@pid", i.ProductID);
comm.Parameters.AddWithValue("@qty", i.Quantity);
comm.Parameters.AddWithValue("@price", i.UnitPrice);
comm.Connection = conn;
comm.Transaction = myTrans;
comm.ExecuteNonQuery();
}
It seems that i can't add records into database with multiple loop.
Thanks in advance.
hi ,
I am using ASP.NET with SQL Server. I have a function ABC() which creates,open ,and then dispose sqlserver's connection .
I am using ABC() twice in one .aspx page ,my friend who build this function argued that this function will open only one connection in everypage ,no matter how many times we call function ABC() in a single page ,while i denies .
Please tell me ,because our whole company database acces relies on this single ABC() function.
Thanks in Advance
Hello everybody,i have a advance question about a specific sql problem:My table A have for example 3 columns.in the third column are words seperated by ~.ID COL2 COL3--------------1 ab test~dummy~ddd2 cd testdata2~sjhfdg~sdf3 ef sd~test4 gh sd~cvNow i want two lists:1.) used Values for column 3:Values------testdummydddtestdata2sjhfdgsdfsdcv2.) used values plus IDValue ID----------test 1test 3sd 3sd 4cv 4dummy 1....Is it posible to produce such a list with nearly one SQL -Statement or withtemporaly tables ?Thanks in advanceT.KindermannDatabase Administrator----------------------------------------------------------------------------Thomas KindermannE-MAIL: Reply to TKINDER<x>@GMX.DE without <x>
View 2 Replies View RelatedHi,I'm wondering if there's any advance order by 'function'/workaround,which is reasonably efficient for MS SQL Server, without resorting tosome third party indexing/search engine to achieve the following.The mechanism is to record each instance of a pattern match and orderby rows with most matches first (DESC). Simplistic match but that's aseparate issue.Sample:create table tmp (col varchar(50));insert into tmpvalues ('a barking dog');insert into tmpvalues ('a dog and cat fights over dog food');insert into tmpvalues ('lovable dog is not barking dog=nice dog');The goal for the Sample is to return resultsets in the followingorder:lovable dog is not barking dog=nice dog -- 3 matchesa dog and cat fights over dog food -- 2 matchesa barking dog -- 1 matchThanks.
View 6 Replies View Related
Hello I am working on a sql express table and while configuring the steps after I select the data source and the selectment statement window shows, I want to use the advanced tab but it is greyed out. I want to be able to add edit and delete my data. I have administrator rights for this project and the workstation so thats not the issue. What I am tryng to accomplish is extending a website to manage it's content and users. Also the table has colums and the colums has test data within them I tested a query and the connection had a successful return. Maybe it's a configuration thing I am unaware of.
DKB
Does SQL Express Advanced allow remote connections or do I have to have SQL Express installed on each computer and attach the MDF in order for other people to use a database.
I have a database that is not large but the JET engine in Access runs too slow for the data that I have. The SQL Express engine is more robust and returns the queries much faster than Access, especially when I am dealing with tables that contain a million records. I don't want to get into using the resources of a server for this task. I installed SQL Express Adv and would like to use my computer as host for the database but I cannot connect to the instance even when I open the computer, grant permission etc...
I know that SQL Express typically does not allow remotes connections but I thought maybe the advance version may. Your assistance is greatly appreciated.
Hi
Any help with this would be greatly appreciated.
I have two tables
First Table is called "Team" see columns and data below
TeamId, TeamName, MemberId
1, White Team, 1
2, Grey Team, Null
Second Table is called "Members" see columns and data below
MemberId, Name
1, Jim Smith
I want to display both tables in a gridview as follows
TeamId, TeamName, MemberId, Name
1, White Team, 1, Jim Smith
2, White Team , Null, Null
I'm using the following sql procedure to do this
Select
Team.TeamId,
Team.TeamName,
Team.MemberId,
Meember.Name
From Team
Inner Join Members on Members.MemberId = Team.MemberId
My Problem is that this select statement returns the first row but not the second row. The reason for this is the second row's memberId is Null. However, I still need to display this row even if the data is some of the data is null.
Can anyone point out the correct sql statement for this?
Forbidden
You don't have permission to access /localhost:8080/sas/(S(b2vekk55equugyjebolothq0))/Main1.aspx on this server.
what is the solution ????
Hi All,
Does anyone know how to return a date the sql query analyser like (Aug 2, 2004)
Right now, the following statement returns (Aug 2, 2004 8:40PM). This is now good because I need to do a specific date search that doesn't include the time.
Many thanks in advance!!
Brad
----------------------------------------------
declare @today DateTime
Select @today = GetDate()
print @today
Below is an example of a pivot table from the help in SQL2005.
My question: Do you have to manually define the columns ([164], [198], etc.) for the pivot?
I would like to use this for a daily report where the columns would be the dates?
Thanks.
GO
SELECT VendorID, [164] AS Emp1, [198] AS Emp2, [223] AS Emp3, [231] AS Emp4, [233] AS Emp5
FROM
(SELECT PurchaseOrderID, EmployeeID, VendorID
FROM Purchasing.PurchaseOrderHeader) p
PIVOT
(
COUNT (PurchaseOrderID)
FOR EmployeeID IN
( [164], [198], [223], [231], [233] )
) AS pvt
ORDER BY VendorID
I am trying to speed up my update statements by removing inner selectstatements.Example:update orders set shipname = (select contactName fromcustomers where customerid = orders.customerID)I read some articles which said that I should be able to use an innerjoin on the update statement like the following:update orders set shipname = (select contactName from customers wherecustomerid = orders.customerID)But every time that I run this statement I get the follwing error:Server: Msg 156, Level 15, State 1, Line 1Incorrect syntax near the keyword 'inner'.Any Help will be greatly appreciated.Thank you.
View 6 Replies View RelatedHello,
I apologize in advance if this seems like a relative easy answer, however, I can't find it anywhere, and I can't figure it out personally, I am relatively new to SSIS and C#.
I am attempting to write in C# a simple program, where I am taking a table from an OLE DB Source and transfering it to a comma delimited flat file.
I have been trying to work through samples and other methods of help, but I continue to get stuck, and this is my latest problem.
When trying to excecute I get the error: No column was sepcified to allow the component to advance through the file, for my flat file destination.
I know what this means, I just do not know how to fix it.
Below is my code so far. I highly doubt my code is the best it can get for this type of example, so if you see anything that I do not need in the code for it to perform what I want it to perform, please let me know.
(The code to excecute this package is in a different file).
using System;
using Microsoft.SqlServer.Dts.Runtime;
using Microsoft.SqlServer.Dts.Pipeline;
using Microsoft.SqlServer.Dts.Pipeline.Wrapper;
namespace Microsoft.SqlServer.Dts.Samples
{
class Program
{
static void Main(string[] args)
{
// Create a package and add a Data Flow task.
Package package = new Package();
Executable e = package.Executables.Add("DTS.Pipeline.1");
TaskHost thMainPipe = e as TaskHost;
MainPipe dataFlowTask = thMainPipe.InnerObject as MainPipe;
// Create Application
Application app = new Application();
// Add an OLE DB connection manager to the package.
ConnectionManager conMgr = package.Connections.Add("OLEDB");
conMgr.ConnectionString = "Data Source=ROSIE\ROSIE2005;" +
"Initial Catalog=AdventureWorks;Provider=SQLNCLI;" +
"Integrated Security=SSPI;Auto Translate=false;";
conMgr.Name = "SSIS Connection Manager for OLE DB";
conMgr.Description = "OLE DB connection to the " +
"AdventureWorks database.";
// Create and configure an OLE DB source component.
IDTSComponentMetaData90 source =
dataFlowTask.ComponentMetaDataCollection.New();
source.ComponentClassID = "DTSAdapter.OLEDBSource.1";
// Create the design-time instance of the source.
CManagedComponentWrapper srcDesignTime = source.Instantiate();
// The ProvideComponentProperties method creates a default output.
srcDesignTime.ProvideComponentProperties();
// Assign the connection manager.
source.RuntimeConnectionCollection[0].ConnectionManager =
DtsConvert.ToConnectionManager90(conMgr);
// Set the custom properties of the source.
srcDesignTime.SetComponentProperty("AccessMode", 2);
srcDesignTime.SetComponentProperty("SqlCommand",
"Select * from HumanResources.EmployeePayHistory");
srcDesignTime.SetComponentProperty("OpenRowset", "[AdventureWorks].[HumanResources].[EmployeePayHistory");
// Need to set the ConnectionManagerID
if (source.RuntimeConnectionCollection.Count > 0)
{
source.RuntimeConnectionCollection[0].ConnectionManagerID =
conMgr.ID;
source.RuntimeConnectionCollection[0].ConnectionManager =
DtsConvert.ToConnectionManager90(conMgr);
}
// Connect to the data source,
// and then update the metadata for the source.
srcDesignTime.AcquireConnections(null);
srcDesignTime.ReinitializeMetaData();
srcDesignTime.ReleaseConnections();
// Add an flat file source connection manager to the package.
ConnectionManager conMgr2 = package.Connections.Add("FlatFile");
conMgr2.ConnectionString = "C:\Documents and Settings\ddoorn" +
"\My Documents\Visual Studio 2005\Projects\" +
"DennisSampleProgram1\EmployeePayHistory.txt";
conMgr2.Name = "SSIS Connection Manager for Flat File";
conMgr2.Description = "Flat File Destination Connection";
// Create Destination Component
IDTSComponentMetaData90 destination =
dataFlowTask.ComponentMetaDataCollection.New();
destination.Name = "Flat File Destination";
destination.ComponentClassID =
"DTSAdapter.FlatFileDestination.1";
CManagedComponentWrapper destDesignTime = destination.Instantiate();
destDesignTime.ProvideComponentProperties();
// Assign the connection manager.
destination.RuntimeConnectionCollection[0].ConnectionManager =
DtsConvert.ToConnectionManager90(conMgr2);
// Set Custom Properties
destDesignTime.SetComponentProperty("Overwrite", true);
// Assignment an ID to the ConnectionManager
if (destination.RuntimeConnectionCollection.Count > 0)
{
destination.RuntimeConnectionCollection[0].ConnectionManagerID =
conMgr2.ID;
destination.RuntimeConnectionCollection[0].ConnectionManager =
DtsConvert.ToConnectionManager90(conMgr2);
}
// Create the path from source to destination.
IDTSPath90 path = dataFlowTask.PathCollection.New();
path.AttachPathAndPropagateNotifications(source.OutputCollection[0],
destination.InputCollection[0]);
// Get the destination's default input and virtual input.
IDTSInput90 input = destination.InputCollection[0];
IDTSVirtualInput90 vInput = input.GetVirtualInput();
// Iterate through the virtual input column collection.
foreach (IDTSVirtualInputColumn90 vColumn
in vInput.VirtualInputColumnCollection)
{
// Call the SetUsageType method of the destination
// to add each available virtual input column as an input column.
destDesignTime.SetUsageType(
input.ID, vInput, vColumn.LineageID,
DTSUsageType.UT_READONLY);
}
//map external metadata to the inputcolumn
//int index = 0;
foreach (IDTSInputColumn90 inputColumn in input.InputColumnCollection)
{
IDTSExternalMetadataColumn90 exMetaColumn =
input.ExternalMetadataColumnCollection.New();
//(IDTSExternalMetadataColumn90)input.ExternalMetadataColumnCollection[index++];
exMetaColumn.CodePage = inputColumn.CodePage;
exMetaColumn.DataType = inputColumn.DataType;
exMetaColumn.Length = inputColumn.Length;
exMetaColumn.Name = inputColumn.Name;
inputColumn.ExternalMetadataColumnID = exMetaColumn.ID;
destDesignTime.MapInputColumn(input.ID, inputColumn.ID, exMetaColumn.ID);
}
// Verify that the columns have been added to the input.
// This is only really required for debugging purposes
Console.WriteLine("Below are the columns that have been added " +
"to the input. Press Enter to Verify");
foreach (IDTSInputColumn90 inputColumn in
destination.InputCollection[0].InputColumnCollection)
{
Console.WriteLine(inputColumn.Name);
}
Console.Read();
// Connect to the data source,
// and then update the metadata for the source.
destDesignTime.AcquireConnections(null);
destDesignTime.ReinitializeMetaData();
destDesignTime.ReleaseConnections();
// Save Package to XML
app.SaveToXml("C:\Documents and Settings\ddoorn\My Documents\" +
"Visual Studio 2005\Projects\DennisSampleProgram1\" +
"DennisSampleProgram1\DennisSampleProject1.xml",
package, null);
} // main
} // program
} // namespace
Hi, if I install SQL Server Express with Advance Services on top of my existing SQL Server Express installation, will it wipe out my existing database in my current SQL Server Express installation ?
Regards
Ash
strSQL = strSQL & ReturnFieldArgument("@DoNotDisplaySSN", Me.DO_NOT_DISPLAY_SSN.Value, False, True)
What would happen if I change one the False value near the end of the code to True?
I have this already existing query and want to add another field to it but its failing. Below is the current query
REPLACE(CONVERT(CHAR(10), A.BATDAT, 1), '/', '') + RIGHT('00000' + CONVERT(VARCHAR, A.BATSEQ), 5
RIGHT('000' + CONVERT(VARCHAR, A.SEQNBR), 3) AS CLIENT_TRACK_NO
I want to add a field from a table name APTTL and the filed name is DTYYY char (2)
Currently, i am planning to develop a small system for my client by using SQL database. However, i am not sure is it legal to use SQL Express 2005 in this project? I mean setup the SQL Express 2005 in their server then my application will access this database. Is it legal? or Do i need to purchase any license in order to do that? or I need to purchase a SQL Server 2005 Standard or Enterprise version?
View 3 Replies View RelatedCode Snippet
SELECT 3308 / 15104
The above division will result with: 0
Code Snippet
SELECT 3308 / (15104 * 1)
The above division will result with: 0
Code Snippet
SELECT 3308 / (15104 * 1.0)
The above division will result with: 0.219014830
MY MAIN GOAL is to produce a result of: 0.2
I was thinking of using ROUND, then with some combination of RIGHT. Your explanation and advice is greatly appreciated! Thanks again!
I have a data flow and inside the data flow , i have a ole db source and ole db command task to execute an insert transaction ( SP).. i would like to save the error output if the insert didn;t happend into a error log table..
but when I darg an error output line ( red) to another ole db command to insert an error log , i can only see two columns( error code and error column) are available in OLEDB command advance editor related to errors.. this doesn;t tell you much information about the error.how can i grap the error reson(desc) as the error output and store into a erro log table? so that i can see what the problem is?
Hi peoples...
I am a total newbie when it comes to SQL databases.
I currently work for a company developing software. We use a VB Development environment and connect to Dbase III database files using DAO... ewwwww...
We are looking to upgrade our development environment to VB.net and our database to either MSSQL or mySQL...
My question is - most of our users are on standalone machines. Our software is NOT internet based, nor do we want it that way at the moment.
Is there a way to connect to a MSSQL database (ie the .mdf file) without having a server running? Or are we better advised to stick to an MS Access database if this is the case....
Basically the reasoning behind upgrading is better speed and record locking. Any advice would be greatly appreciated.
I am sorry to sound so dumb...
Cheers,
Kev
My backups are failing sometimes.My db size is around 400 GB and we are taking backup to the remote server. Free size available on the disk is showing 600 GB but my database full backup run more than 10 hrs and failed. The failure reason is there is not enough space on the disk.
What could be the possible failure reasons? It has more than the the database size on the backup server but why it is showed that msg on the job failures. I noticed same thing occurred couple of times in the past.
Is there any way to find how much the backup file will be generate before we run the backup job?
i.e. If we run the full backup of test1 database now, it will generate ....bak file for that test1 db
currently we are using maintenance plan and with compression (2008R2) and the database has TDE enabled
Hi,
I was unable to install the CTP version of SS Express Edition SP1 with Advance Services. The installation failed with the following error message:
TITLE: Microsoft SQL Server 2005 Setup
------------------------------
SQL Server Setup could not connect to the database service for server configuration. The error was: [Microsoft][SQL Native Client][SQL Server]Login failed for user 'sa'. Refer to server error logs and setup logs for more information. For details on how to view setup logs, see "How to View Setup Log Files" in SQL Server Books Online.
------------------------------
However, I was able to install and run the standard CTP version of SS Express Edition SP1 without any problems, even though the above error message indicated I may have login problem. First let me confess that my computer does not have IIS installed, which as I understand is only needed for running the Report Services. Is it necessary for IIS to be up and running while installing SSEE with Advance Services?
The main reason I want to install SSEE with Advance Services is to use the full text search capability. I do not need the Report Services, and was hoping there is an option to turn it off and not require IIS. Is this possible?
Mike
Hi I had installed SQL Server 2005 Express Avanced and busines intellegence development studio express. in my windows XP profesional
I have ready my first report, and When I want deploy , the system say: Reporting Services Loign: User name & Password.
I don't know that user and password must write!!!
Please can someone help me!!!
thanks folks
david
spain
We need to use SQL Server Authentication for some reason and would like to enforce Password Policy with 90-day expiration period. I found "Change password" dialogue appears when I first logged in with the new user, but don't know
(1)what happens when the user failed to change the password before it's expired or
(2)how a user can change his password in advance of the expiration date with no particular server-level permission.
Hi,
I am trying to model data in analysis services with the Advance Create Mining Model function in the excel addin. I am having trouble creating an association model that works like the Associate button above the Advanced button.
The format of my data is like this
OrderID Product
100 Bike
100 Helmet
100 Shoes
200 Helmet
200 basketball
200 Bat
300 Shoes
300 Socks
The associate button works perfectly since it asks me which column is the transaction id (orderid) and which column I am trying to predict (product). The advanced create mining model asks me to determine what the columns are...
OrderID=key Product=Input+Predict?
When I run the advance create mining model associate, I get a browser that gives me no rules and the support for only one item itemset (each product but no combination of products).
Does anyone know what I have to do to get it to work like the associate button?
I am trying to install microsoft sql server 2000 enterprise edition on a windows 2000 advance server. I keep getting error message
A previous program installation created pending file operations on the installation machine. You must restart the computer before running . I have restarted machine and no change.
What does error message mean and what is solution?
Currently I have following things installed on my Computer
1. SQL Server Express 2005
2. SQL Server Management Studio Express 2005 CTP
I need to install following things
A. Microsoft SQL Server 2005 Express Edition with Advanced Services
B. Microsoft SQL Server 2005 Express Edition Toolkit
Do I need to uninstall any any of 1 or 2?
What should be my path to upgrade these software.