Hi,
I am totally new to sql server and have got a few doubts.I couldnt find it in earlier posts.Sorry if it is a repetition
*Can we get all the table names of a database by Query?
*How to create a similar copy of a table by using AS..does this work in sql server 2000?(tried but showing error)
*can we modify the column properties(like ensuring NOT NULL) after the table is created?
Thanks in advance
Hi,
I am currently using SQL Server version 6.5 and am trying to execute the BCP command
from the command prompt to update my database. Right now, if the tables doesn't have
any primary key constraints, the BCP works fine and inserts all the rows to the database.
Suppose the table has some primary keys and the datafile to the BCP has some duplicate
records in it. It says, the following
Starting copy...
Msg 2627, Level 14, State 1:
Server 'DHRUVA', Line 1:
Violation of PRIMARY KEY constraint 'PK_docket_booking4_1__10':
Attempt to insert duplicate key in object 'docket_booking4'.
It does not insert any rows to the database. Is there anyway I can overcome this error and
proceed on with the BCP to copy data. THe datafile which I give will have duplicate records.
regards
Ramesh Seetharaman.
hi all,Please find below my queries, and help me to understand things..1. How to find the port number of a specific SQLServer instance?(I have heard that the different instance of the SQLServer runs in different port number)2. How to create a new instance for an existing SQLServer database? and how would i connect to it with my JDBC Connection string which usually reads as,jdbc:jtds:sqlserver://<Host_Name>:<Port_Number>/<DataBase_Name>Thanks in advanceShefu
View 7 Replies View Relatedi. If we have files and filegroups in place we have improved performance on backup and restore .Can we have any other improved perfortmance ?
2.If we are rebuilding or reorganising indexes on a table,which one will the degrades the performance more on the production server and which sits more on the transaction log.
3 how can we check the tableid in the database.
Which SP can be used to set or clear a stored procedure for automatic execution?
Which TSQL DDL statement cannot be at the database scope?
a)Create Database
b)Alter Database
c)Drop Contract
d)Create Certificate
What is schema binding?
Which Tsql extension in sql 2005 allows SP's to be exposed as Web Service?
Hello i attended one interview . I have some doubts
1.) Indexes decreases the performance for insert ,UPDATE commands.? what will u do to increase the performance with these statements.
2.).tell me the 5 steps do u consider while writing a stored procedure to increase the performance.?
4).Is truncate a DDL or DML operation ?
5.) How to delete duplicate rows from a table?
6).table1 has an identity coluimn with 5 rows.
delete opearation is performed on table 1.If user enters a new row what wiil be identity column no 1 or 6?
table2 has an identity coluimn with 5 rows.
truncate opearation is performed. .If user enters a new row what wiil be identity column no 1 or 6?
7.) when do we go for sub queries and when do we go for joins ?
8) could me tell the difference between sp and function.?
waiting for ur replies.
SRIPATHI
I am having this question in my mind from when I started using SSIS.. In a package I have an OleDB source and a flat file destination. and I hook up the source to destination in SSIS and run the package.. it works .. fine.. but internally what is done??? does SSIS performs BCP out ???
View 5 Replies View Related
Hi,
set @DN=''
set @DN=@DN+'AA9999'
select @ID = ID from TableA where status='A' and ColumnA like '%'+@DN
ColumnA is NonClusteredIndex
When I use = symbol it goes for Index Seek (like below query) otherwise its going for table scan. Could anyone advice me which index need to be used for getting 'Index Seek' when I use like Operator. Basically TableA has millions of records.
select @ID = ID from TableA where status='A' and Code = 'AA9999'
Regards
JMR
Hello Guys,
soon soon, í´ll have to devellop some procedures to read an ASCII file to supply MS SQL tables. As I´ve read some old post, I´ve understand that I have to use BULK INSERT , or else, BCP or DTS. I´d like to know the diference between this commands and witch of them is more powerful, faster and efficient. If you can give me some implementation tips, I will be very grateful.
thanx all
hi,
here i am inserting the total dates between 1983/08/26 to 2006/08/26.
i.e the table will have 36525 recoeds approximately. after executing the command i verified the table n i noticed that the last record in this table was 2025/xx/xx approximately. why? defualtly what is the capacity of a sql table. can i change the table properties to hold more num. of records. please help me on this.
One can never consent to creep,when one feels an impulse to soar
RAMMOHAN
Good morning,
I've to do a mining project and I intend to use the SSIS.
I've done a clustering plugin last year on analysis services and I also want to use it.
Let me try to explain the architecture of the process:
1) Receive data (read data from the database - these data are texts, actually)
2) Pre-process the data (transform the texts in a sparse matrix) using a new plugin
3) Call my clustering plugin and assign it to read the table created on the previous step
4) Call my KNN plugin to classify other pre-processed texts using the clusters found on the previous step as classes.
5) Show results.
Alright... It all running as a workflow on integration services
Here are my doubts:
A) How to view and use my plugin made for Analysis Services on Integration Services ? (is it possible or will I have to create another plugins from zero just to run on Integration Services ?)
B) Assuming the previous step is possible, how to modify my plugins to define inputs and outputs to do the correct communications between each plugin ? I think this is the most important question. Is it simple to do ? Is there any documented examples ?
Thanks a lot for your attention once more,
-Renan Souza
I have 25 columns in my excel which is the second row in the excel. I have merged column comprising of 3-4 columns so like wise 7 columns as the 1st row. How can I suggest SSIS that 2ns row in the header or column names? Is there a way to validate values in each cell in excel for their data types using SSIS?
Also is there any way using SSIS that I can refer to specific cell in the excel?
Hi All,
Here i am going to ask some questions in SSIS pls clarify my doubts...
1. Can i include a package with in another package? like If i have 20 packages..i need to run all packages at a time ..for this can i create a package and can i place all those packages in to a single package?
2. If i have 700 packages then how to run those packages all at a time?
3.Can i develop a Workflow or package in SSIS with out using Data Flow Tasks? Like with the help of only control Flow Tasks can i create a package or workflow?
Thanks & regards,
Maruthi...
I have a SQL cluster 2005 with 2 nodes. I involved a ETL (DTS) on SSIS.
I used OLEDB to get a connection ORACLE and INFORMIX.
I used a user and password to authenticate.
When I publish the package, to the Integration Services.
I publish it to the MSDB so in case that we have a failover that the package is the same.
However in case of switching server (failover) the package that I published on NODE 1 does not run on NODE 2.
And it fail:
OnError,EDVSQLCLUSTER02,DOMAIN-LISBOA
olo,Insert pt_fornsap,{F06434D9-431B-48B7-A3E6-F17E04A29488},{4FF70682-705E-4410-885F-36F25BC1EC8C},22-09-2006 2:00:01,22-09-2006 2:00:01,-1071636471,0x,An OLE DB error has occurred. Error code: 0x80040E14.
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "Statement(s) could not be prepared.".
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "Invalid object name 'pt_lfa1'.".
Could you help me
Thanks for advance
AG
hi all
i have doubts abt report deployment
if i am deploying report then what process exactly happens?
also is any entry made in IIS or is any new file created in IIS?
if i am deploying report on remote server then what process exactly happens?
what r the entries or files that r created on remote server?
plz help me so that i will understand this deployment process very clearly.
waiting for reply.
I have few more clarification regarding time series.
Firstly
In my model the month level product sales value represented across 1st day of every month. So that the key time column is of datetime datatype containing a sequence of dates representing the 1st day of every month of the year.
Eg: 2006-01-01, 2006-02-01€¦€¦€¦. etc. all in (yy-mm-dd) format
But
when I make prediction for next five months, though it makes monthly
predictions the date part for the months are random whereas I expect
the date part to be 1st of every month. What is the reason for this and how can I overcome it.
Secondly
Predicted
sales values for some time period are negative though I do not have any
negative value in the training data. What is the reason for this and
how can I rectify it?
Thirdly
In
one of your earlier posts you had said that the time series algorithm
does not have any built in time intelligence but uses the key time
column as a time sequence stamp. So If have to make predictions for a
particular time period where the time slice for each time period is 25
days or 50 days etc, then I understand that the input data used to
train the model should be in the same time sequence.
Or
Can I specify the span of the time period according to which the prediction needs to be made?
Basically
how can I use the same time series model to make monthly, yearly,
quarterly, daily or predictions or for custom time period like I have
mentioned above.
Hi friends,
I am currently using SQL Server 2000 server. I have run some BCP command to import data to SQL from CSV and Text files with the help of FORMAT files through Windows Scheduler.
My doubt is, if I swich to SQL Server 2005, whether the same BCP commands will supported? If no, What are the things I need to do to run the BCP commands in 2005 as in 2000?
I heard that the BCP utility is no longer support in 2005 and this can be done thru SSIS utility. Is it right...?
Is SQL Server 2005 support to use the BCP commands without going to SSIS utility?
Please help. Thanks in advance.
Regards,
Sethu.
Hi
My doubts on revoked users in MSSQL...please reply
I have following doubts:
1. Is there any stored procedure provided by MS SQL server for checking whether the user is revoked or not?
2. What is the query for checking whether the particular user is revoked or not in MS SQL?
3. Do all revoked users get stored in separate database in MSSQL ?
Please reply.
Hi
I have following doubts:
1. Is there any stored procedure provided by MS SQL server for checking whether the user is revoked or not?
2. What is the query for checking whether the particular user is revoked or not in MS SQL?
3. Do all revoked users get stored in separate database in MSSQL ?
Please reply.
Dear Expertise
Please give me a solution to my general question.
1. How can we Impliment the report builder in client machene.
2. What is the diffrense between Report Server and Report Builder.
This is probably a very silly question.I started learning ASP.net by following ASP.NET Unleashed. I am stuck where he wants me to open a connection to SQL Server database. I have just downloaded
MSDE. But I dont know where to type this code and how to run it..so as to connect to the database.
<%@ Import Namespace="System.Data.SqlClient" %>
<Script Runat="Server">
Sub Page_Load
Dim conPubs As SqlConnection
conPubs = New SqlConnection( "server=localhost;uid=webuser;pwd=secret;database=pubs" )
conPubs.Open()
End Sub
</Script>
Connection Opened!
Now do i have to change the uid to SA ? (i had to assign one when i downloaded and installed MSDE?
Thanks for the help.
Hi all,
am not very experienced in using DTS and really need your help. I have a dts package that i have scheduled to run every day. Here's what i want the package to do:
1. Check whether a value for a certain column in a certain row of a table in my database is 0 or 1. If it is 1, then
2. Run the dts task (which i have created and is working)
In other words, when the package is started, i want to execute a stored procedure or sql task or whatever, and if that returns 1 then i want to continue, if it returns 0 i want to finish the package without running the dts task. I'm sure there's a simple way to do this, but i could use your help...!
Thanks,
Elisabet
Hi All,
Can this be done and if so can you give a bullet list of the steps need to accomplish this.
I need to load a bunch of files into a stagging table. Need to loop through the files and load them.
Thanks,
Michael
Hi,
what this statement do?
does it add all the values or combine all the values.
REPLACE combine WITH lc_tran + lc_exp + lc_war + ll_boc
Regards
kk
I downloaded SQLExpress and Visual Studio Express to my home computer.
I built a simple database, adding data through theSQLexpress admin tool.
I built a web page using MS Studio. I connected to the database and used the webpage for a few days. Then I restarted the computer. Now the web page won't open, and MS Studio won't open the MDF file in the App_Data folder.
I can still see and work in the database through SQL server Express.
The web page and the MSStudio attempt to connect to the mdf file both fail with this message:
Cannot open user default database. Login failed.Login failed for user 'KAAAK/Administrator'.
So it seems to be trying to connect as the Windows user.
When I try to modify the connection to connect through a user/password I created in SQL manager, I get a message that the user is not a trusted SQL user.
from web.config:
<connectionStrings>
<add name="ConnectionString" connectionString="Data Source=.SQLEXPRESS;AttachDbFilename=|DataDirectory|info.mdf;Integrated Security=True;User Instance=True;User ID=Admin;Password=12345" providerName="System.Data.SqlClient"/>
</connectionStrings>
That was changed from the original string created automatically by MS Studio
<connectionStrings>
<add name="stocksConnectionString" connectionString="Data Source=.SQLEXPRESS;AttachDbFilename=|DataDirectory|stocks.mdf;Integrated Security=True;User Instance=True;" providerName="System.Data.SqlClient"/>
</connectionStrings>
I am sure this is some simple problem, but why would the system refuse to access an mdf file it had already been accessing.?
Thanks, Michael
Hi all, having trouble with my first sql communication. I've got hosted service with an SQL database i've populated with a row.
When it gets to the third line the page crashes with an error.
SqlConnection connection = new SqlConnection("Server=mydbserver.com;Database=db198704784;");// +"Integrated Security=True"); SqlCommand cmd = new SqlCommand("SELECT UserName FROM Users",connection); SqlDataReader reader = cmd.ExecuteReader();
is there somewhere i need to put in my username or password? or is this code just wrong
Many thanks burnside.
-- Edited by longhorn2005
not sure why I am having trouble here but I am using the following WHERE clause expecting to find all rows where any one of the the three keywords are present.
....WHERE Company.L_Keywords LIKE '%metal%' AND Company.L_Keywords LIKE '%tile%' AND Company.L_Keywords LIKE '%ceramic%'
however it appears to finding only the rows where all three words are present in the L_Keywords field
This is a very simple question. How would a select satement be formated in the following example.
SELECT Grade, Student_ID, First_Name, Last_Name FROM Scores WHERE (This is where I'm stuck and I know this is not the right formatting although I wish it were because it would make my life a little bit easier.) Student_ID = 115485, 115856, 568547, 965864, etc...
I may have up to 100 specific student ids to put in this one statement. I know I can use the "WHERE Student_ID = 115485 OR Student_ID = 115856, OR Student_ID = 568547" but that would be alot of waste. Seems like there should be an easier way than using the "OR Student_ID =" for every entry.
Can someone explain another way I can do this. Thanks in advance.
Hey, I have a pretty simple question.My query is throwing an error saying "Invalid column name 'subject'."The problem is that subject is a custom column I've made, well just look at the sql:SELECT a.ArticleID, subject=ISNULL((select subject from subjects),'') where subject='some subject'
View 8 Replies View RelatedI have already created package which loads a text file to database using the dts wizard in Enterprise Manager.How do I execute that package using visual basic?Please provide the Code!!!Thanks
View 2 Replies View RelatedHello,
I've just migrated my access database from access2000 to sql7.0. The wizard told me there was no problem. But a simple question:
How do i open my database? Where can i see tables, fields...?
Is there no interface like the one in access2000?
Thanks in advance!
Hi there.
I am trying to use MDX code to create a measure in ProClarity. Please help!!
A store can be one of a number of ‘Brands’. The MDX segment below gives me the Sales Value of a selected item, but for Store Brand 'Brand1' only. This works fine - but how do I add a brand? That is, how do I see the combined Sales Value for the selected item for 'Brand1' and 'Brand2'?
([Store Brand].[Brand1],[Measures].[Sales Value],[Item].CurrentMember)
B
Hoping someone could me with an ongoing indexing question that I have.
On my site, we have over the past 5 years developed what is emerging as a fairly complicated dbase structure, as features have been added to my site and relations have increased between different database tables, there has been a need to index fields in different ways, and in some instances field indexing has overlapped. For example we may have a table that has 5 fields (field1,field2,field3,field4,field5). A need to index field1 is requried because of a query that reads:
SELECT * From Table1 where field1=XXXXX
Additionally there may be a need to for another query that reads:
SELECT * From Table1 where field2=XXXXX
In this instance an index is placed on field2....
But, for example when there is the following query:
SELECT * From Table1 where field1=XXXXX and field2 = XXXXX
Is it necessary to set a new index on: field1,field2 ???
We have made the choice that yes, in fact there is...but now over time some of our tables have instances of single fields being indexed along with combinations of two single fields that have already been indexed, being indexed together. As tables have grown to over 1,000,000 records and having up to 15 or so indexes, we realize that the number of indexes maybe degrading performance. Also, indexes vary in type, e.g INT,BIGINT,Varchar fields... In the above instance, can we eliminate the multi-indexes and improve performance over all...?
On a second related question:
In the event that two tables are joined on a common field.
e.g. Select * from Table1,Table2 where Table1.field1=Table2.field1
Is it necessary to index both of these fields in tables: Table1 and Table2 ?
Hope someone can help, as we are looking to improve the efficiency of our tables as they continue to grow.