Read Table Name In Sql Databse
May 9, 2008hi
i want read table name in database in sql while loop statement
ADVERTISEMENT
Read Data From A Csv File And Insert It In A Sql Server Databse Table
May 25, 2007Hai Everbody,
for me in my project i want to read data from a csv file and insert it in a sql server databse table.The csv file may contain n number of columns,but i want only certain columns from that, and insert it in the database table.How to achieve this. Plz help me it is urgent. Thanks in advance.
Thanks and regards
Biju.S.G
SSMS Replacement So I Can Read A SSCE3.5 Databse In Real Time
Feb 18, 2008Has anyone come accorss such a beast or will I need to write one!
View 8 Replies View RelatedLink To Another Databse Table In Stored Procedure
Dec 11, 2007Hi I need to know if it is possible to write on a table from another database and this using a stored procedure. If yes what is the correct syntax to connect to the other database.
View 1 Replies View RelatedPerformance Of Joining Two Table In Diffrent Databse
Nov 19, 2007
I have two databses SIS and SIS_Pro. Users tables should be used in both of them because I have some relations between this table with other table in SIS and SIS_Pro. Users in SIS only have one column and it is the UserId which is the primary Key in both of them, but in SIS_Pro Users table have Firstname Lastname and... now. In my program I need some informatin from SIS and some from SIS_Pro so I create a view which is joining of forexample exam in SIS and Users in SIS_Parnian, becuase I don't have the firstname and lastname in a Users table which is in SIS_Pro databse.Does it reduce the performance?is it better to copy datas which are in Users in SIS to Users in SIS_Pro( I mean all columns firstname, lastname ,,.....)
Sincerely
Kianoosh
How To Get Permissions On A SQL Databse Table For A User Group Defined In AD
Jun 6, 2007Hi, i am trying to find permissions on SQL server database tables for a usergroup defined in Active Directory.
there is one function in SQL : €œSELECT * FROM fn_my_permissions('TableName', 'OBJECT')€?
This function get me the permission on TableName table for the current user. but i want that inforamtion for a user group defined in AD.
Is tehre any way to acheive that?
-Mani
Exporting Databse Table Data In SQL Server 6.5 Into Text Files
Mar 11, 1999Hi,
I have to export the table data from my databse into text files as I nedd to put it in Informix database using a sheel script. Is there a way by which I can do this.
Is there any other way by which I can put the data from SQL Server to Informix.
Any takers,
Thanking you in advance.
Bye for now,
Himauhu
Batch File - Read From One Table And Write To Another Database And Table
Nov 17, 2011Any easy way for a batch file or automated process to read from one db and table and what ever entry is missing out of another database + table it writes those missing entries to.
This is a simple table in one db that is filled with usernames, I want to see if there are missing usernames in another db and table and write those entries.
db1.usr_table.usr_name = jdoenew
If jdoenew is missing in the 2nd db I will need to write entries like:
db1.usr_table.usr_name = jdoenew
db1.usr_table.password = tmppassword
db1.usr_table.active = 1
Read The Table
Feb 12, 2008Hey ,
I have sql server 2000, and i am using Microsoft Application block.
I have Transaction Like...
SqlConnection conn = new SqlConnection(ASLConnectionString); conn.Open(); SqlTransaction Trans = conn.BeginTransaction(IsolationLevel.ReadUncommitted);
But If i have another process that try to read the Table if that old transaction running.. it wouldn't Allow me to read the DATA.
i tried Isolation level "snapshot" but that works under sql server 2005 only. so i can not use that. And i have some services that will run during day time.
So if anybody have solution to read the data from table...from sql server 2000 even if another transaction is running?
Please Give any guideline..Help will be appriciated.
thanks & regards,
Nirav Patel
Read-only Table
Aug 9, 2004Hello,
I was wondering how to make a table read-only. I want to make a script that creates a table, adds some data to it, then makes the table read-only to prevent someone from accidentally changing the values.
Thanks to anyone who can help,
Chris
Need A SP To Read Table A And Update Table B
May 11, 2004Hi
I need a SP to read table A (1000 records) and updates Table B. I think I have to use Cursor, but I'm not very good at writing SP. Any help would be appreciated. Thanks.
Read/Write To Same Table
Oct 17, 2001Hi, we're trying to read from a table and write back to the same table and are having a lot of trouble with blocking. What could we do to prevent our application from hanging due to blocking of this type?
View 1 Replies View RelatedRead Table By Index
Dec 7, 2014I need to read a db Table but sorted by index - is there a generic "select * from tableA sorted by index" where it just uses whatever index it finds (main index) or do i have to name the index?
View 2 Replies View RelatedRead Only Table In SQL Server?
Apr 7, 2004Is it possible to make a table (or set of tables) within a SQL Server database read only (still granting the db_datawriter fixed database role to database users) without dropping and recreating the db_datawriter database role?
View 1 Replies View RelatedLinked Table Is Read-only, But Why?
Apr 17, 2008I work on an MS Access project ( *.apd - file) that is linked with a
SQL-express server 2005 (local installed). On this server are 2 databases x
and y.
I linked some tables of database x to y and here's the problem: the linked
tables are read-only.
My authentication for the server is Windows authentication and I'm
administrator.
I say this because I think the problem is a security problem.
when I try to change data in the
linked table in Microsoft SQL Server Management Studio Express, I get now the follow error:
---------------------------
No row was updated.
The data in row 12 was not committed
Error source: .Net SqlClient Data provider.
Error Message: Transaction context in use by another session.
Correct the errors an retry or press ESC to cancel the change(s).
---------------------------------
All other programs are close, except MSE
thanks a lot!!!!!
Can somebody help me to make the linked tables are not read-only? What is
the problem, what did I wrong?
Thanks a lot!
How To Store And Read .doc File In A Table
May 10, 2006I would like to store and read .doc file in a table of sql server using C#.
Can some body help me on this? my id is padam76@gmail.com.
Thanks & Regard,Padam Kumar Tripathi
Read The Filename, Split It And Put It In A Table
Sep 21, 2006Hello
I'm working on a package which loops through each xml file in a folder.
The name of each xml file is put in variable.
The format of the filename is something like "part1_part2_part3.xml"
I need to store the 3 parts in three different columns of table A
The content of the xml file needs to be manipulated ("." needs to be replaced with ",", ....)and put in serveral columns in tableB
It's not clear to me yet how to start this but my main concern is read the three parts of the filename. I don't find any task in SSIS which could help me with that.
Could someone give me some pointers?
Many thanks!
Worf
Can I Read From Multiple Table At Run Time
Feb 15, 2007Hi,
My Issue is that, I have to create a package which should read from multiple table from a RDBMS Source.
Right now I have different Source adapters for each table. I want to reduce the number of source adapters.
Can I
1) Create a connection manager and set the connection string from a variable. Set the variable from a script task. Put both of these into for loop container (no of tables).
Issues
a) Most of the table has same schema, but few have different, so all tables which has same schema will work.
For different schema anyway I have to create different data source.
b) Will Destination column mapping work.
If any one has already worked on it let me know.
Thanks
Dharmbir
Last Read/write Time Of Table
Jul 14, 2007Is there a way to get the last read/write time of a table?
I want to have a few tables, but only allow them to exist if they have been used in the last 30 days. I want to set up a "purge" job to clear out any tables that have not been used in 30 days.
How To Read Table Then Export Out To A Textfile
Feb 26, 2008
Declare @cmd varchar(255)
select @cmd = 'bcp "select * from mytable" queryout c:cptest.txt -Uasdfasdfasdf -Pasdfasdf'
when i run this it says "The command(s) completed successfully." but i don't see that file anywhere at all. why is that? is this the right way to do? please help.
Fail To Read Entire Table In .NET
Sep 20, 2007The problem that I am having is that I am not getting the results from an entire table, just a small subset. The sql command is "Select * from login", which has always implied to me, get everything, to end of table, or, until I tell you to stop. I am getting exactly, 10 items as input, and then it stops. I am including the code below in the hopes that someone can spot what it is that I am doing wrong.
The purpose of the routine is to convert a user login database into a new, expanded format.
TIA, Tom
Imports Common.Utility.Utils
Partial Class UserDatabaseConversion
Inherits System.Web.UI.Page
Dim sqlConn As SqlConnection = getSQLConnection(ConfigurationManager.AppSettings("utilityDbName"))
Dim sqlConnN As SqlConnection = getSQLConnection(ConfigurationManager.AppSettings("utilityDbName"))
Dim sqlStringOld As String = "Select * from login"
Dim sqlStringNew As String = "usp_UT_AddNewUser"
Dim buildCnt As Integer = 0
Dim oldUserCnt As Integer = 0
Dim newUserCnt As Integer = 0
Dim oLocation As String = Nothing
Dim errors As Integer = 0
Dim mesg As String = Nothing
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
getOldUsers()
End Sub
Sub getOldUsers()
Dim odate As Date = Date.Today
Dim sqlCmdOld As SqlCommand = New SqlCommand()
sqlCmdOld.CommandText = sqlStringOld
sqlCmdOld.CommandType = CommandType.Text
sqlCmdOld.Connection = sqlConn
Dim dbReader As SqlDataReader = Nothing
Try
sqlConn.Open()
dbReader = sqlCmdOld.ExecuteReader()
If dbReader.HasRows Then
Do While dbReader.Read()
oldUserCnt += 1
Select Case IsDBNull(dbReader.Item("reg_date"))
Case True
buildNewUser(dbReader.Item("name"), dbReader.Item("password"), dbReader.Item("email"), dbReader.Item("type"), dbReader.Item("org"), dbReader.Item("occupation"), dbReader.Item("location"), odate)
Case False
buildNewUser(dbReader.Item("name"), dbReader.Item("password"), dbReader.Item("email"), dbReader.Item("type"), dbReader.Item("org"), dbReader.Item("occupation"), dbReader.Item("location"), dbReader.Item("reg_date"))
End Select
Loop
End If
Catch ex As Exception
Finally
sqlConn.Dispose()
End Try
If errors = 0 Then
lblMesg.Text = "Conversion completed succesfully.<br />Users In: " & oldUserCnt & " , Users Out:" & newUserCnt
Else
lblMesg.Text = "Conversion comleted with errors.<br />Users In: " & oldUserCnt & " , Users Out:" & newUserCnt & "<br />"
lblMesg.Text += mesg
End If
End Sub
Sub buildNewUser(ByVal oname As String, ByVal opassword As String, _
ByVal oemail As String, ByVal otype As String, _
ByVal oorg As String, ByVal oocc As String, _
ByVal oloc As String, ByVal oregdate As Date)
Dim sqlCmdNew As SqlCommand = New SqlCommand()
sqlCmdNew.CommandText = sqlStringNew
sqlCmdNew.CommandType = CommandType.StoredProcedure
sqlCmdNew.Connection = sqlConnN
Dim userName As String = Nothing
Dim firstName As String = Nothing
Dim middleName As String = Nothing
Dim lastName As String = Nothing
Dim title As String = Nothing
Dim emailAddress As String = Nothing
Dim passWord As String = Nothing
Dim userType As String = Nothing
Dim org As String = Nothing
Dim occ As String = Nothing
Dim loc As String = Nothing
Dim rdate As Date = Nothing
oLocation = oloc
' user name will be the first part of the email address
emailAddress = oemail
Dim uname() As String = oemail.Split("@")
' extend the length of the user name to at least 5 characters
Select Case uname(0).Length
Case Is > 4
userName = uname(0)
Case Is = 4
userName = uname(0) & "1"
Case Is = 3
userName = uname(0) & "12"
Case Is = 2
userName = uname(0) & "123"
Case Is = 1
userName = uname(0) & "1234"
End Select
' check for suffixes
Dim suffix() As String = oname.Split(",")
Select Case suffix.Length > 1
Case True
title = suffix(1)
End Select
' split names out
Dim names() As String = suffix(0).Split(" ")
' possibly 3 components to the name
Select Case names.Length
Case Is = 1
firstName = names(0)
Case Is = 2
firstName = names(0)
lastName = names(1)
Case Is = 3
firstName = names(0)
middleName = names(1)
lastName = names(2)
End Select
' setup password. must be at least 6 characters.
' if less, extend to 6.
Select Case opassword
Case Is > 5
passWord = opassword
Case Is = 5
passWord = opassword & "1"
Case Is = 4
passWord = opassword & "12"
Case Is = 3
passWord = opassword & "123"
Case Is = 2
passWord = opassword & "1234"
Case Is = 1
passWord = opassword & "12345"
End Select
' user type, organization and occupation
userType = otype
org = oorg
occ = oocc
' registration date
Select Case IsDBNull(oregdate) Or IsNothing(oregdate)
Case True
rdate = Today.Date
Case False
rdate = oregdate
End Select
' try to extract the location
' get the country
Dim country As String = getCountry(oloc)
' get the state
Dim state As String = getState(oloc)
' only thing left is the city
Dim city() As String = oloc.Split(",")
' add parameter values
sqlCmdNew.Parameters.AddWithValue("@NamePrefix", System.DBNull.Value)
sqlCmdNew.Parameters.AddWithValue("@FirstName", replaceSingleQuote(firstName))
Select Case IsNothing(middleName)
Case True
sqlCmdNew.Parameters.AddWithValue("@MiddleName", System.DBNull.Value)
Case False
sqlCmdNew.Parameters.AddWithValue("@MiddleName", replaceSingleQuote(middleName))
End Select
Select Case IsNothing(lastName)
Case True
sqlCmdNew.Parameters.AddWithValue("@LastName", System.DBNull.Value)
Case False
sqlCmdNew.Parameters.AddWithValue("@LastName", replaceSingleQuote(lastName))
End Select
sqlCmdNew.Parameters.AddWithValue("@UserName", replaceSingleQuote(userName))
Select Case IsNothing(title)
Case True
sqlCmdNew.Parameters.AddWithValue("@NameTitle", System.DBNull.Value)
Case False
sqlCmdNew.Parameters.AddWithValue("@NameTitle", title)
End Select
sqlCmdNew.Parameters.AddWithValue("@Password", replaceSingleQuote(passWord))
sqlCmdNew.Parameters.AddWithValue("@EmailAddress", emailAddress)
sqlCmdNew.Parameters.AddWithValue("@PasswordHint", userName)
Select Case isEmpty(org)
Case True
sqlCmdNew.Parameters.AddWithValue("@Organization", System.DBNull.Value)
Case False
sqlCmdNew.Parameters.AddWithValue("@Organization", replaceSingleQuote(org))
End Select
Select Case isEmpty(occ)
Case True
sqlCmdNew.Parameters.AddWithValue("@Occupation", System.DBNull.Value)
Case False
sqlCmdNew.Parameters.AddWithValue("@Occupation", replaceSingleQuote(occ))
End Select
sqlCmdNew.Parameters.AddWithValue("@Address1", System.DBNull.Value)
sqlCmdNew.Parameters.AddWithValue("@Address2", System.DBNull.Value)
Select Case isEmpty(city(0))
Case True
sqlCmdNew.Parameters.AddWithValue("@City", System.DBNull.Value)
Case False
sqlCmdNew.Parameters.AddWithValue("@City", replaceSingleQuote(city(0)))
End Select
sqlCmdNew.Parameters.AddWithValue("@ZipCode", System.DBNull.Value)
sqlCmdNew.Parameters.AddWithValue("@PostalCode", System.DBNull.Value)
Select Case IsNothing(state)
Case True
sqlCmdNew.Parameters.AddWithValue("@State", System.DBNull.Value)
Case False
sqlCmdNew.Parameters.AddWithValue("@State", state)
End Select
sqlCmdNew.Parameters.AddWithValue("@Other", System.DBNull.Value)
Select Case IsNothing(country)
Case True
sqlCmdNew.Parameters.AddWithValue("@Country", System.DBNull.Value)
Case False
sqlCmdNew.Parameters.AddWithValue("@Country", country)
End Select
sqlCmdNew.Parameters.AddWithValue("@GeneralEmail", False)
sqlCmdNew.Parameters.AddWithValue("@FundRaiseEmail", False)
sqlCmdNew.Parameters.AddWithValue("@GeneralUSPSMail", False)
sqlCmdNew.Parameters.AddWithValue("@FundRaiseUSPSMail", False)
sqlCmdNew.Parameters.AddWithValue("@DateAdded", rdate)
sqlCmdNew.Parameters.AddWithValue("@DateUpdated", rdate)
sqlCmdNew.Parameters.AddWithValue("@Active", True)
sqlCmdNew.Parameters.AddWithValue("@UserType", userType)
Dim iRows As Integer = 0
' try to insert the row into the table
Try
sqlConnN.Open()
iRows = sqlCmdNew.ExecuteNonQuery()
If iRows <> 1 Then
errors += 1
mesg += "<br />User: " & firstName & " " & lastName & " was not added.<br />"
End If
newUserCnt += 1
Catch ex As Exception
Dim exMsg As String = ex.Message.ToString()
writeApplicationLog(exMsg, ConfigurationManager.AppSettings("UtilityDBName"))
errors += 1
mesg += "<br />User: " & firstName & " " & lastName & " was not added due to " & exMsg & "<br />"
Finally
sqlConnN.Close()
sqlCmdNew.Dispose()
End Try
End Sub
Function getState(ByVal loc As String) As String
Dim state As String = Nothing
If loc.Contains("NY") Then
state = "NY"
ElseIf loc.Contains("WA") Then
state = "WA"
ElseIf loc.Contains("PA") Then
state = "PA"
ElseIf loc.Contains("AZ") Then
state = "AZ"
ElseIf loc.Contains("MI") Then
state = "MI"
ElseIf loc.Contains("WI") Then
state = "WI"
ElseIf loc.Contains("CT") Then
state = "CT"
ElseIf loc.Contains("NC") Then
state = "NC"
ElseIf loc.Contains("CA") Then
state = "CA"
ElseIf loc.Contains("NY") Then
state = "NJ"
End If
Return state
End Function
Function getCountry(ByVal cntry As String) As String
Dim country As String = "US"
If cntry.Contains("Canada") Then
country = "CA"
End If
Return country
End Function
End Class
Create And Read A Table From A Stored Procedure
Nov 25, 2001In a stored procedure, I am trying to create a table and then read it from within that SP or from another one (nestsed). When I run the below code (simplified version of my real SP), no records are returned even though I know that there is data. The table is created and records are inserted, I just cannot read the records from the SP or one that calls it with an EXEC.
Any ideas? My code is listed below. (When I get this to work, I plan to convert the code to manipulate a temporaty table).
Alter Procedure spWod_rptWoStatusSummary_9
As
BEGIN
CREATE TABLE tblStatusSummary (TckStaffCd_F Char(3), RecType Char(20))
INSERT INTO tblStatusSummary
SELECT TckStaffCd_F, RecType
END
BEGIN
Select * from tblStatusSummary
end
Read Table Description Into ADODB.Recordset.
Aug 20, 2003How do I read the Tables description into ADODB.Recordset and then recreate the Tables+description into a new database from the ADODB.Recordset.
View 1 Replies View RelatedDoes Cursor Convert Table To Read/write?
Apr 10, 2008Hello,
Any help here much appreciated.
I am using sql server 2000 to perform address cleansing. there is a point in my scripting when a table i pass values to becomes read/write.
i suspect this is when i run a cursor through the table.
Is anyone able to confirm for me whether running a cursor changes a table's properties?
Many thanks.
Tim
Ps as the table seems to be read/write it is harder to tell if NULLs are in the table and this is messing with joins I have further down the track.
Read, Modify Table (locking) Question
Feb 3, 2006I am quite new to MS SQL, and I want to read rows from a todo table, andwhen a row has been processed, I want to delete that row.Ages ago in MySQL I would probably have locked the table, select, processa row, delete a row, unlock the table.I have been reading through the documentation from MS SQL, but it's notclear what exactly I should do.Since I want to lock only one table in the select (the others just providedata, and are not modified), what's a good solution?--John MexIT: http://johnbokma.com/mexit/personal page: http://johnbokma.com/Experienced programmer available: http://castleamber.com/Happy Customers: http://castleamber.com/testimonials.html
View 2 Replies View RelatedStrange Issue With SQL Table Read Only In Access
Apr 21, 2006I have a data file that I converted from Access to SQL Server using theupsizing wizard. I then attached the tables to the Access front end using anODBC connection. I have one table that is read only for some reason. I'mnot sure how that happened, it went through the same procedures as the othertables in terms of upsizing and connecting. The original table was not readonly or hidden or anything else, it had full permissions.I've tried disconnecting and reconnecting the table but still the samething. In SQL Server, the permissions are the same as all the other tables.I can't seem to figure out what's making this one read only.I'm using SQL Server 2005 Express and Access 2003. Any ideas would begreatly appreciated.Thanks!
View 11 Replies View RelatedRead Oracle Sequeneces Into MSSQL Table
Jul 20, 2005Hi,I'm doing an Orcle to SQL port.Simply all I want to do is run a script / DTS package that reads Oraclesequences value and writes the current value of it in to a MSSQL table - ofSequenceName, ValueCan this be done ? If so how ? (Please provide example)Steve
View 2 Replies View RelatedIs It Possible To Read Data From A Table With Exclusive Lock ?
Sep 4, 2006Hello,
We currently use SQL Serv 2000 with an ERP application and VB applications.
Both system work with the same database & tables but the ERP application seems to put exclusive lock on all tables it needs during processes. So our VB applications are not able to read these data and we receive TIMEOUT error.
What can I do ?
thank you
Nico
SQL Security :: Public Roles Can Read All Table
May 18, 2015As far as i know all users that is assigned to "Public" database role by default cannot SELECT any tables which in the databases. But in my case i am restoring database from previous version of SQL Server 2005 to SQL Server 2008 R2.
The problem come when every new users that created can access all tables in the databases.Â
How to get "public" back to default permission settings?
How A Storied Procedure Can Read A Table In MS Access?
Sep 5, 2007Hi,
We currently use an access database to manipulate data in tables on a SQL server. The end user copies a list of ProjNo from a spreadsheet into an Access table. The Access table is used as the criteria for 5 Access queries. It is common that one of the queries does not get run.
I figured I would create a Storied Procedure on the SQL server, call the storied procedure from MS Access using the MS Access table as the criteria to run the Storied Procedure. I do not know how to provide the Storied Procedure the ProjNo in the MS Access table. The number of ProjNo records in that table can vary.
I've found info on how to add one record at a time but I need a more of a batch process approach.
Can you point me in the right direction?
Thanks,
Mary
Transact SQL :: Making Server Database Read / Write From Read Only
Jan 12, 2012i attached adventure works in sql server 2008 and it showing as read only ,make it read write or remove read only tag from database.
View 11 Replies View RelatedSQL Server 2008 :: Last Table READ And WRITE Dates
Feb 26, 2015How would I find the last read/write dates for all the tables within a database.
View 6 Replies View RelatedSQL Server 2008 :: Read A Value From Table And Increment It By 1 - Row Lock
May 12, 2015I have a requirement to read a value from table and increment it by 1. There can be multi-threads doing the same operation and would need a ROW LOCK so that read and write both are atomic. How can i put an exclusive lock on the row before I read the value from the table.
CREATE TABLE [dbo].[tblOnboardingSequence](
[OnboardingSequenceID] [uniqueidentifier] NOT NULL,
[Name] [nvarchar](255) NOT NULL,
[NextNumber] [bigint] NOT NULL,
CONSTRAINT [PK_OnboardingSequence] PRIMARY KEY CLUSTERED(
[OnboardingSequenceID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
My Stored Procedure has below logic
DECLARE @NextNumber BIGINT
----------- Acquire row lock here
SELECT @NextNumber = NextNumber FROM tblOnboardingSequence WHERE Name = 'TPO'
UPDATE tblOnboardingSequence SET NextNumber = @NextNumber + 1 WHERE Name = 'TPO'
----------- Release row lock here
I would like to have a row lock for the row having Name "TPO" before SELECT query and release after UPDATE query.
What si the best way to deal with this?