Skip to main content

Excel BD Row

Sub ConnectSqlServer() Dim conn As ADODB.Connection Dim rs As ADODB.Recordset Dim sConnString As String Dim squery As String ' Create the connection string. sConnString = "Provider=SQLOLEDB;Data Source=RCHNLPT136\SQLEXPRESS2014;" & _ "Initial Catalog=ExpenseMgmt;" & _ "user id=sa;password=Welcome@123;" & _ "Integrated Security=SSPI;" ' Create the Connection and Recordset objects. Set conn = New ADODB.Connection Set rs = New ADODB.Recordset ' Open the connection and execute. conn.Open sConnString squery = "select " & _ " tb.TABLE_SCHEMA as TableSchema, tb.TABLE_NAME as TableName," & _ " sum(Case When DATA_TYPE = 'int' then 4 else 0 end + Case When DATA_TYPE = 'nvarchar' and CHARACTER_MAXIMUM_LENGTH > 0 then CHARACTER_MAXIMUM_LENGTH * 2 When DATA_TYPE = 'nvarchar' and CHARACTER_MAXIMUM_LENGTH < 0 then" & _ " 8000 else 0 end + Case When DATA_TYPE = 'varchar' then CHARACTER_MAXIMUM_LENGTH else 0 end + Case When DATA_TYPE = 'Char' then CHARACTER_MAXIMUM_LENGTH else 0 end + Case When DATA_TYPE = 'nchar' then CHARACTER_MAXIMUM_LENGTH * 2 else 0 end + Case When DATA_TYPE = 'decimal' then 8 else 0 end + Case When DATA_TYPE = 'bit' then 1 else 0 end + Case When DATA_TYPE = 'Tinyint' then 1 else 0 end + Case When DATA_TYPE = 'Smallint' then 2 else 0 end + Case When DATA_TYPE = 'Smallint' then 4 else 0 end + Case When DATA_TYPE = 'Smalldatetime' then 4 else 0 end + Case When DATA_TYPE = 'Smallmoney' then 4 else 0 end + Case When DATA_TYPE = 'Bigint' then 8 else 0 end + Case When DATA_TYPE = 'Datetime' then 8 else 0 end + Case When DATA_TYPE = 'Datetime2' then 8 else 0 end + Case When DATA_TYPE = 'Float' then 8 else 0 end + Case When DATA_TYPE = 'Money' then 8 else 0 end + Case When DATA_TYPE = 'Numeric' and Numeric_Precision > 18 then 16 else 0 end +" & _ " Case When DATA_TYPE = 'Numeric' and Numeric_Precision <= 18 then 8 else 0 end + Case When DATA_TYPE = 'Time' then 8 else 0 end + Case When DATA_TYPE = 'TimeStamp' then 0 else 0 end + Case When DATA_TYPE = 'Uniqueidentifier' then 16 else 0 end ) Size_of_Row from INFORMATION_SCHEMA.COLUMNS c,INFORMATION_SCHEMA.TABLES tb where c.TABLE_NAME =tb.TABLE_NAME group by tb.TABLE_NAME,tb.TABLE_SCHEMA" Set rs = conn.Execute(squery) ' Check we have data. If Not rs.EOF Then ' Transfer result. Sheets(1).Range("A1").CopyFromRecordset rs ' Close the recordset rs.Close Else MsgBox "Error: No records returned.", vbCritical End If ' Clean up If CBool(conn.State And adStateOpen) Then conn.Close Set conn = Nothing Set rs = Nothing End Sub

Comments

Post a Comment

Popular posts from this blog

Athouk & Mohinga

Atho or Athouk . These consist of boiled noodles cooked with spices, shredded cabbage, onions, chili flakes, roasted channa dal powder, tamarind juice, bejo, garlic oil, coriander leaves and etc are the ingredients of Athouk. To give the dish a crunchy feel. This food also eaten with the accompaniment of Mohinga,a soup made with fish broth & plantain stem, onion, ginger, garlic, chili, turmeric and rice flour. It is an awesome combination. Mohinga , which is considered the unofficial Burmese national food. It is essentially rice noodles served in fish broth with fried onions, garlic, ginger and sliced tender core of plantain stem. If you ask for it, it will also be served with boiled eggs. It is essentially a soup meal and a delightful one at that. History: Many Tamils were settled in Burma then and slowly emigrated back here after Independence.Burmese people started migrating to India. Most of them arrived at the Chennai Port and used to leave for other smaller cities by
3 comments
Read more

World Environment Day

This entry is in response to the Conserve, Recycle and Discover contest organised by blogadda.com in association with Pringoo ! World Environment Day, commemorated on 5 June since 1972, is one of the ways focuses world attention on the environment and encourages political action. People from countries all over the world have mobilized for individual and organized environmental action. Activities involve all sectors of society, governments, non- and inter-governmental organizations, businesses, industries, civil society, media and schools. Please take the following Pledge to save our environment. 1, I pledge to Recycle more, use less water and electricity. 2, I pledge to smoke less in 2010. 3, I pledge to send more electronic documents instead of using paper. 4, I pledge to continue using my bicycle to go to work (instead of my motorbike). 5, wash with full loads it is more energy efficient than doing several smaller ones. 6, Incite of dryer I will use hang dry for energy sav
1 comment
Read more

GridView Alphabet Paging

Introduction: GridView paging feature allow us to display fixed number of records on the page and browse to the next page of records. Although paging is a great feature but sometimes we need to view all the items alphabetically. The idea behind this article is to provide a user with a list of all the alphabets and when the user clicks on a certain alphabet then all the records starting with that alphabet will be populated in the GridView control. Populating the GridView Control: The first task is to populate the GridView control. I will be using the Northwind database in my article which, is installed by default for SQL SERVER 2000 and SQL SERVER 7 databases. The code below is used to populate the GridView control. private void BindData() { string connectionString = "Server=localhost;Database=Northwind;Trusted_Connection=true"; SqlConnection myConnection = new SqlConnection(connectionString); SqlDataAdapter ad = new SqlDataAdapter("SELECT P
1 comment
Read more